SYSTEM表空間管理及備份恢復

1、system表空間管理及備份恢復system table space management and backup recoverypersonal classification: oracle backup &recoversystem table space management and backup recoverythe system table space is one of the most important oracle database table space, storage of the ddl language information and pl/sql pac

2、kages, views, functions, procedures, etc， called the data dictionary,this table space also has its own particularity, which describes the relevant features and backup and recovery of system table spacesmanagement of system table spacesit is recommended that no user data be stored to avoid user error

3、s causing the system table space to be unavailableyou should set the default default table space for the system to prevent user creation from using system table spacesalterdatabasedefaulttablespace tablespace namesql > col property value format a30sql > selectproperty_nameproperty_valuefromdat

4、abase_propertieswhereproperty_namelike 'default %'property_name property_valuedefault_temp_tablespace tempdefault_per1anent_tablespace users this should be a non-system table spacedefault_tbs_type smallfilesystem table space featuresno offline offlineno read-only read onlycannot renamecan，t

5、deletethe demo can't be offlinesql > altertablespace system offline;altertablespace system offline*the error at line 1:ora 01541: system tablespace cannot be brought offline; shutdownifnecessarysql > alterdatabasedatafile 1 offline;alterdatabasedataf ile 1 offline*the error at line 1:ora 0

6、1541: system tablespace cannot be brought offline; shutdownifnecessarycannot be read-onlysql > altertablespace systemreadonly;altertablespace systemreadonly*the error at line 1:ora 一 01643: system in tablespace cannotbe madereadonlyno renamesql > altertablespace system renametosystem2;altertab

7、lespace system renametosystem2*the error at line 1:ora - 00712: always rename system in tablespaceyou can，t delete itsql > droptablespace system;droptablespace system*the error at line 1:ora - 01550: cannotdropsystem in tablespacesql > droptablespace system o contentsanddatafiles:droptablespac

8、e system o contentsanddatafilesthe error at line 1:ora - 01550: cannotdropsystem tablespacsummary: the system table space is a sentenee, the name of a common table space has the name change, delete, read-only, and unmachine for system table space3. the space managementensure that the space is availa

9、ble, typically storing a single data file set to automatic extensionif the system table space data file is large, you can consider using bigfileuse the following view to get the relevant state of the table space, using space, and so ondba_data_filesdba_tablespacesdba_free_spacev sdatafilesv $in tabl

10、espace look at the size and size of the table spacesql > selecttablespace_name, bytes / 1024/1024 cursize, user_bytes / 1024/1024asuser_bytes, status, online_status2 fromdba_data_files;tablespace_name cur_size user_bytes status online.users 5 4.9375 available onlinesysaux 250 249.9375 available o

11、nlineundotbs1, 3534.9375 available onlinesystem 500 499.9375 available systemexample 10099.9375 available online- view the remaining space in the table spacesql > selecttablespace_name, sum (bytes / 1024/1024)2 fromdba_free_spacegroupbytablespace_name;tablespace_name sum (bytes / 1024/1024)undotb

12、s1 11.6875sysaux 2. 125users 2system 10.125example 31. 75一- look at the percentage of the used spaceselecta tab1espace_name, round (sizea-sizeb), 2) asused_space, round (sizeb, 2) | | j asfree_space,(sizea-sizeb)/sizea * 100, 2) | | ' %' asused_percentthe from(selecttablespace_name,bytes / 1

13、024/1024 sizeafromdba_data_fi1es) ainnerjoin (selecttablespace_name, sum (bytes / 1024/1024) sizebfromdba_free_spacegroupbytablespace_name) bona. tablespace_name 二 b t ablespace_nameorderbya tablespace_name;the cold preparation and recovery of system table spacefor cold backup of the database, refer

14、 to: oracle cold backupfor how to switch archive mode, please refer: oracle archive logbecause only the backup and restore of system table spaces is involved, the system table space is only backed up herebackup and restore in non-filing mode一一 view the archive modesql > selectlog_modefromv $datab

15、ase;log_modenoarchivelogclose the database backup copy systemo1. dbf file to the specified backup directorysql > shutdownimmediate;sql > ho cp $oracle_base/oradata/orc1/systemo1. dbf$oracle_base/coolbak /create table tb based on non-system table spacessql > startupsql > createtabletb tab

16、lespace usersasselect * fromscott.sql > selectcount fromtb (1);count (1)14-switch logs multiple times until the log group is clearedsql > altersystem switch logfile;delete the systemol. dbf filesql > ho rm $0racle_base/oradata/orcl/system01. dbf一一 error prompted after mandatory startupsql &

17、gt; startup force;oracle instance is started.databasemounted.ora 01157: cannot identify/lock datafilel - see dbwr tracefileora ohio: datafilel: '/uol / app/oracle/orcl/systemol dbf，-restore system table space data filessql > ho cp $0racle_base/coolbak/system01 dbf $oracle_base/oradata/orc1 /-

18、 based on the renouncing recovery systemo1. dbfsql > recoverdatabaseuntil cancel;0ra-00279: change 677850 generated at 08/15/2010 14:00:36 neededforthread 10re-00289: suggestion: / uol /app/oracle/flash_recovery_area/0rcl/archivelog / 2010_08_15/ol_mf_10_ arc0ra-00280: change 677850forthread lisi

19、nsequence # 10specifylog: ret > 二 _| f訂ename | auto | cancelcancel - the system prompts a successful recovery of the database using resetlogswarning: recover succeeded butopenresetlogs in terror belowthe ora-01194: file2 needs more recoverytobe consistent - indicates that the data file needs to p

20、erform consistent recovery, and some data is lost in the undo table spaceora ohio: datafile2: '/ uol /app/orac1e/orac1e/orc1/undotbso1 dbf'ora - 01112: media recoverynotstartedbecause there is no backup datafile 2, the hidden parameter _allow_resetlogs_corruption is enabledsql > altersyst

21、emset ，_allow_resetlogs_corruption，/ 二 true scope 二 spfile: this parameter is a static parameter, and you need to set the scopesql > startup force;oracle instance is starteddatabasemounted.ora - 01589: mustuseresetlogsornoresetlogsoptionfordatabaseopenthe system terminates when the database is op

22、ened using resetlogssql > alterdatabaseopenresetlogs;alterdatabaseopenresetlogs*the error at line 1:ora 一 00603: oracleserversession terminatedbyfatal errorsql > exit; 0utonce you log in, you can start againoracle robinson 、 ssqlplus/assysdbasql > startuporacle instance is started.databasem

23、ounted.databaseopenedclose the hidden parameter _allow_resetlogs_corruptionsql > altersystem reset _allow_resetlogs_coituptiort scope 二 spfile sid 二*'；the submitted transaction is cleared for logging and data islostsql > selectcount fromtb (1);fromtb selectcount (1)*the error at line 1:ora

24、 - 00942: tableorviewdoesnotexistbackup and restore in archive modeafter obtaining the path of the system table space, the database is closed and the system table space data file is copied to the backup pathsql > colnameformat a55sql > selectnamefromv $datafi1ewherefi1e# 二 1;the namesql > h

25、o cp $oracle_base/oradata/orc1/systemo1. dbf $oracle_base/coolbak / - view the log archive modesql > selectlogjnodefromv $database;log_modearchivelog一一 delete systemol dbfsql > ho rm $0racle_base/oradata/orcl/system01.after the dbf file is lost, the data in the users table space can be queried

26、sql > selectcount (1) fromscott emp;count (1)14- creating tables based on system table spaces and inserting data,the data file that prompts the system table space is lost and cannot be createdsql > createtabletb_test tablespace systemasselect * fromdba_segments;createtabletb_testasselect * fr、

27、onidba_seginents*the error at line 1:ora - 01116: errorinopeningdatabasefilelora ohio: datafilel: '/uol / app/oracle/orcl/systemol dbf，ora - 27041: unabletoopenfilelinux error: 2: nosuchfileordirectoryadditional information: 3- you can create objects based on the non-system table, creating table

28、 tb_test in the users table space as followssql > createtabletb_test tablespace usersasselect * fromscottcount (1)14-the error that was lost when the account was unlocked, because the account information is stored in the system table spacesql > alteruserhr account unlockalteruserhr account unl

29、ock*the error at line 1:0ra-00604: error occurred at recursive level 2ora - 01116: errorinopeningdatabasefilelora ohio: datafilel: '/uol / app/oracle/orcl/systemol dbf，ora - 27041: unabletoopenfilelinux error: 2: nosuchfileordirectoryadditional information: 3start another session, which can be c

30、onnected, but there are the following error messagessql > conn scott/tiger;error:0ra-00604: error occurred at recursive sqllevellora - 01116: errorinopeningdatabasefilelora ohio: datafilel: '/uol / app/oracle/orcl/systemol dbf，ora - 27041: unabletoopenfilelinux error: 2: nosuchfileordirectory

31、additional information: 3一- unable to close the database consistentlysql > shutdownimmediate:ora - 01116: errorinopeningdatabasefilelora ohio: datafilel: '/uol / app/oracle/orcl/systemol dbf，ora - 27041: unabletoopenfilelinux error: 2: nosuchfileordirectoryadditional information: 3oracle inst

32、anee is shut down.一 restore system table space data filessql > ho cp $0racle_base/coolbak/system01 dbf $oracle_base/oradata/orc1 /- restore data files for the system table spacesql > recover datafile 1;media recovery is completesql > alterdatabaseopen;because the log is not lost, the submit

33、ted transaction remainsconsistentsql > selectcount fromtb_test (1);count (1)14conclusion:a. the archive mode, as a result of the online redo logs cycle use rules, some have already written to the log but not yet more to the data file data information in time after the failure willbe losttherefore

34、, it is only possible to restore to the table space (the system table space) backup, while the archive mode can be restored to the specified or the latest timewhen the system table space is lost, the data dictionary involving the system table space will not be available, nor will it be added to the

35、system table spacec. using resetlogs to reset the log file, it is recommended that the database be fully prepared when the database is closed3, the heat preparation and recovery of system table spacedue to the archive mode is easy to cause the loss of data, and the production database generally adop

36、ts the archive mode, so in this no longer consider the file backup and recovery processing mode- view the log archive modesql > selectlogjnodefromv $database;l0g_m0dearchivelog-start the system table space hot standby mode in sessionlsql > altertablespace systembeginbackup;- create table tb_se

37、g in session2sql > showuser;useris li0nsql > createtabletb_seg tablespace usersasselect * fromdba_segments;-heat in the sessionl to specify the pathsql > ho cp $oracle_base/oradata/orc1/systemo1. dbf $oracle_base/hotbak /- empty the tb_seg table you just created in session2sql > deletefr

38、omtb_seg;sql > commit;close the hot standby mode in sessionl and delete the systemol.dbfsql > altertablespace systemendbackup;sql > ho rm $0racle_base/oradata/orcl/system01. dbf一一 continue the session in session2 and perform the followingactionssql > insertintotb_segselect * from dba_seg

39、mentswhererownum <6;sql > commit;sql > selectcount fromtb_seg (1);count (1)5- force the data to be startedsql > startup force;oracle instance is starteddatabasemountedora 01157: cannot ident辻y/lock datafilel - see dbwr tracefileora ohio: datafilel: v uol / oracle/orcl/systemol. dbf，一 restore system table space data filessql > ho cp $oracle_base/hotbak/systemol dbfsoracle base/oradata/orc1 / perform media recoverysql > recover datafile 1;medi