Shulou(Shulou.com)06/01 Report--

This article shows you what is SYSTEM tablespace management and how to backup and restore, the content is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article, I hope you can get something.

SYSTEM Tablespace Management and backup recovery

SYSTEM table space is the most important table space in Oracle database, which stores some information generated by DDL language, as well as PL/SQL packages, views, functions, procedures and so on, which is called data dictionary.

Therefore, the tablespace also has its particularity. The related characteristics and backup and recovery of SYSTEM tablespace are described below.

I. Management of SYSTEM tablespaces

1. It is recommended not to store user data to avoid user errors leading to unavailability of system tablespaces.

Default tablespaces should be set for the system to avoid using system tablespaces when creating them.

ALTER DATABASE DEFAULT TABLESPACE tablespace_name

SQL > col property_value format A30

SQL > select property_name,property_value from database_properties

2 where property_name like 'DEFAULT%'

PROPERTY_NAME PROPERTY_VALUE

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESPACE USERS-this should be a non-SYSTEM tablespace

DEFAULT_TBS_TYPE SMALLFILE

2.SYSTEM tablespace characteristics

Cannot go offline offline

Cannot be set to read-only read only

Cannot be renamed

Cannot delete

-- Demo cannot be offline

SQL > alter tablespace system offline

Alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL > alter database datafile 1 offline

Alter database datafile 1 offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

-- cannot be made read-only

SQL > alter tablespace system read only

Alter tablespace system read only

*

ERROR at line 1:

ORA-01643: system tablespace can not be made read only

-- cannot be renamed

SQL > alter tablespace system rename to system2

Alter tablespace system rename to system2

*

ERROR at line 1:

ORA-00712: cannot rename system tablespace

-- cannot be deleted

SQL > drop tablespace system

Drop tablespace system

*

ERROR at line 1:

ORA-01550: cannot drop system tablespace

SQL > drop tablespace system including contents and datafiles

Drop tablespace system including contents and datafiles

*

ERROR at line 1:

ORA-01550: cannot drop system tablespac

Summary: system tablespaces are not owned by system tablespaces, such as renaming, deletion, read-only and offline.

3. Space management

Ensure that the space is available, generally storing a single data file. Set to auto extend

If the SYSTEM tablespace data file is large, consider using bigfile

Use the following view to get the relevant state of the tablespace, usage space, and so on

Dba_data_files

Dba_tablespaces

Dba_free_space

V$datafiles

V$tablespace

-- check the size and used size of the tablespace

SQL > select tablespace_name,bytes/1024/1024 cur_size,user_bytes/1024/1024 as user_bytes,status,online_status

2 from dba_data_files

TABLESPACE_NAME CUR_SIZE USER_BYTES STATUS ONLINE_

USERS 5 4.9375 AVAILABLE ONLINE

SYSAUX 250 249.9375 AVAILABLE ONLINE

UNDOTBS1 35 34.9375 AVAILABLE ONLINE

SYSTEM 500 499.9375 AVAILABLE SYSTEM

EXAMPLE 100 99.9375 AVAILABLE ONLINE

-- View the remaining space in the tablespace

SQL > select tablespace_name,sum (bytes/1024/1024)

2 from dba_free_space group by tablespace_name

TABLESPACE_NAME SUM (BYTES/1024/1024)

UNDOTBS1 11.6875

SYSAUX 2.125

USERS 2

SYSTEM 10.125

EXAMPLE 31.75

-- View the percentage of space used

Select a.tablespace_name, round ((sizea-sizeb), 2) as used_space, round (sizeb,2) | | 'MB' as free_space

Round ((sizea-sizeb) / sizea*100,2) | |'%'as used_percent

From

(select tablespace_name,bytes/1024/1024 sizea from dba_data_files) a

Inner join

(select tablespace_name,sum (bytes/1024/1024) sizeb

From dba_free_space group by tablespace_name) b

On a.tablespace_name = b.tablespace_name

Order by a.tablespace_name

II. Cold backup and recovery of SYSTEM tablespace

For cold backup of database, please refer to: Oracle cold backup

For more information on how to switch archive mode, please refer to: Oracle Archive Log

Since only the backup and recovery of SYSTEM table spaces are involved, only SYSTEM table spaces are backed up here

1. Backup and recovery in non-archived mode

-- View the archive mode

SQL > select log_mode from v$database

LOG_MODE

-

NOARCHIVELOG

-- back up system01.dbf files to the specified backup directory after shutting down the database

SQL > shutdown immediate

SQL > ho cp $ORACLE_BASE/oradata/orcl/system01.dbf $ORACLE_BASE/coolbak/

-- create table tb based on non-system tablespace

SQL > startup

SQL > create table tb tablespace users as select * from scott.emp

SQL > select count (1) from tb

COUNT (1)

-

fourteen

-- switch logs several times until the log group is emptied

SQL > alter system switch logfile

-- Delete system01.dbf files

SQL > ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

-- an error occurs after forced startup

SQL > startup force

ORACLE instance started.

Database mounted.

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1:'/ u01 *

-- restore the data files of the system tablespace

SQL > ho cp $ORACLE_BASE/coolbak/system01.dbf $ORACLE_BASE/oradata/orcl/

-restore system01.dbf based on abandonment

SQL > recover database until cancel

ORA-00279: change 677850 generated at 08/15/2010 14:00:36 needed for thread 1

ORA-00289: suggestion: / u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_08_15/o1_mf_1_10_%u_.arc

ORA-00280: change 677850 for thread 1 is in sequence # 10

Specify log: {= suggested | filename | AUTO | CANCEL}

Cancel-the system prompts you to use RESETLOGS to open the database for successful recovery

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent-- prompts that data files need to perform a consistent recovery, and some data is lost in undo tablespaces

ORA-01110: data file 2:'/ u01 *

ORA-01112: media recovery not started

-- since datafile 2 is not backed up, the hidden parameter _ allow_resetlogs_corruption is enabled

SQL > alter system set "_ allow_resetlogs_corruption" = true scope = spfile;-- this parameter is static and needs to be set scope

SQL > startup force

ORACLE instance started.

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- the system terminates abnormally when opening a database using resetlogs

SQL > alter database open resetlogs

Alter database open resetlogs

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

SQL > exit;-- exit

-- you can start normally after re-logging in

[oracle@robinson ~] $sqlplus / as sysdba

SQL > startup

ORACLE instance started.

Database mounted.

Database opened.

-- turn off the hidden parameter _ allow_resetlogs_corruption

SQL > alter system reset "_ allow_resetlogs_corruption" scope = spfile sid ='*'

-- the committed transaction is lost because the log is emptied.

SQL > select count (1) from tb

Select count (1) from tb

*

ERROR at line 1:

ORA-00942: table or view does not exist

two。 Backup and recovery in Archive Mode

-- after obtaining the path to the system tablespace, consistently close the database and copy the system tablespace data file to the backup path

SQL > col name format A55

SQL > select name from v$datafile where file# = 1

NAME

/ u01/app/oracle/oradata/orcl/system01.dbf

SQL > shutdown immediate

SQL > ho cp $ORACLE_BASE/oradata/orcl/system01.dbf $ORACLE_BASE/coolbak/

-- View the log archive mode

SQL > select log_mode from v$database

LOG_MODE

-

ARCHIVELOG

-- Delete system01.dbf

SQL > ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

After the system01.dbf file is missing, you can query the data of the users tablespace as follows

SQL > select count (1) from scott.emp

COUNT (1)

-

fourteen

Create a table based on the system table space and insert data, indicating that the data file of the system table space is missing and cannot be created

SQL > create table tb_test tablespace system as select * from dba_segments

Create table tb_test as select * from dba_segments

*

ERROR at line 1:

ORA-01116: error in opening database file 1

ORA-01110: data file 1:'/ u01 *

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

-- you can create objects based on non-system tables, such as creating table tb_test in the users tablespace

SQL > create table tb_test tablespace users as select * from scott.emp

SQL > select count (1) from tb_test

COUNT (1)

-

fourteen

-- the following received a missing system01.dbf error when unlocking the account because the account information is stored in the system tablespace

SQL > alter user hr account unlock

Alter user hr account unlock

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 2

ORA-01116: error in opening database file 1

ORA-01110: data file 1:'/ u01 *

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Open another session and connect, but the following error occurs

SQL > conn scott/tiger

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01116: error in opening database file 1

ORA-01110: data file 1:'/ u01 *

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

-- database shutdown cannot be consistent

SQL > shutdown immediate

ORA-01116: error in opening database file 1

ORA-01110: data file 1:'/ u01 *

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL > shutdown abort;-- force database shutdown

ORACLE instance shut down.

-- restore the data files of the system tablespace

SQL > ho cp $ORACLE_BASE/coolbak/system01.dbf $ORACLE_BASE/oradata/orcl/

-- restore data files for system tablespaces

SQL > recover datafile 1

Media recovery complete.

SQL > alter database open

Committed transactions remain consistent because the logs are not lost

SQL > select count (1) from tb_test

COUNT (1)

-

fourteen

Summary:

a. In non-archive mode, due to the recycling rules of online redo logs, some data information that has been written to the log but has not been updated to the data file in time will be lost after the failure.

Therefore, it can only be restored to the time when the tablespace (in this case, the system tablespace) is backed up, while in archive mode it can be restored to the specified or latest time.

b. When system tablespaces are lost, data dictionaries involving system tablespaces are not available and objects cannot be added to system tablespaces

c. Use resetlogs to reset the log file. It is recommended to complete the database immediately after the database is closed.

III. Hot backup and recovery of SYSTEM tablespaces

Because the non-archived mode is easy to cause data loss, and the production database generally uses the archived mode, the backup and recovery processing in the non-archived mode is no longer considered.

-- View the log archive mode

SQL > select log_mode from v$database

LOG_MODE

-

ARCHIVELOG

-- start the hot backup mode of system tablespaces in session1

SQL > alter tablespace system begin backup

-- create table tb_seg in session2

SQL > show user

USER is "LION"

SQL > create table tb_seg tablespace users as select * from dba_segments

-- Hot backup to the specified path in session1

SQL > ho cp $ORACLE_BASE/oradata/orcl/system01.dbf $ORACLE_BASE/hotbak/

-- clear the tb_ Segment table you just created in session2

SQL > delete from tb_seg

SQL > commit

-- turn off hot standby mode in session1 and delete system01.dbf

SQL > alter tablespace system end backup

SQL > ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

-- continue the session in session2 and do the following

SQL > insert into tb_seg select * from dba_segments where rownum commit

SQL > select count (1) from tb_seg

COUNT (1)

-

five

-- Force startup data

SQL > startup force

ORACLE instance started.

Database mounted.

ORA-01157: cannot identify/lock data file 1-see DBWR trace file

ORA-01110: data file 1:'/ u01Universe oracle.oradataUniverse orclUniple system01.dbf'

-- restore the data files of the system tablespace

SQL > ho cp $ORACLE_BASE/hotbak/system01.dbf $ORACLE_BASE/oradata/orcl/

-- perform media recovery

SQL > recover datafile 1

Media recovery complete.

SQL > alter database open

-- verify the recovery. The data in table tb_seg is correct.

SQL > select count (1) from lion.tb_seg

COUNT (1)

-

five

IV. Backup and recovery based on RMAN,SYSTEM tablespace

1. When using RMAN to back up tablespaces and data files, it is logically equivalent to a hot backup using Oracle, except that RMAN packages the tablespaces and data files into a backup.

In a collection of multiple backup films. In fact, backing up system tablespaces is the same as backing up regular tablespaces, but when RMAN is used to back up system tablespaces, RMAN automatically backs up the control files

And spfile files. RMAN uses backup datafile 1 (backup database) as a command instead of putting tablespaces on hot standby and copying files manually

two。 For the restore and recovery of system table space under RMAN, unlike cold backup and hot backup mode, it is not necessary to manually copy backup files to the original location. Once

When the restore instruction is issued, RMAN automatically looks for the latest backup data in the tablespace and completes the restore operation. Secondly, the recover instruction is used to complete the media recovery.

What is the above content of SYSTEM tablespace management and how to backup and restore? have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.