oracle 控制檔案的重建

標籤：rman   use   back   end   online   mat   roc   closed   完全恢複   

目錄

• oracle 控制檔案的重建
  • NORESETLOGS
  • RESETLOGS

oracle 控制檔案的重建

不到最後時刻，如三個控制檔案都已損壞，又沒有控制檔案的備份。還是不要重建控制檔案，處理不好就會有資料丟失。

NORESETLOGS

Set #1. NORESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "KYEUPDB" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 4096    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘  SIZE 200M BLOCKSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE ‘/oracle/app/oracle/product/11.2.0/dbs/arch1_1_978829161.dbf‘;-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE-- Database can now be opened normally.ALTER DATABASE OPEN;-- Files in normal offline tablespaces are now named.ALTER DATABASE RENAME FILE ‘MISSING00006‘  TO ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs01.dbf‘;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘     SIZE 1024M REUSE AUTOEXTEND OFF;-- End of tempfile additions.

操作：

[BEGIN] 2018/6/22 21:28:55SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area 3858223104 bytesFixed Size          2259080 bytesVariable Size         788531064 bytesDatabase Buffers     3053453312 bytesRedo Buffers           13979648 bytesCREATE CONTROLFILE REUSE DATABASE "KYEUPDB" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 4096    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘  SIZE 200M BLOCKSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512DATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK 21  ;Control file created.SQL> recover database;ORA-00283: recovery session canceled due to errorsORA-00264: no recovery requiredSQL> alter database open;Database altered.SQL> select * from dba_temp_files;no rows selectedALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘  2       SIZE 1024M REUSE AUTOEXTEND OFF;Tablespace altered.SQL> select * from dba_temp_files;FILE_NAME--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   FILE_ID TABLESPACE_NAME             BYTES     BLOCKS STATUS  RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS---------- ------------------------------ ---------- ---------- ------- ------------ --- ---------- ---------- ------------ ---------- -----------+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379     1 TEMP               1073741824     131072 ONLINE         1 NO            0          0 1072693248      130944[END] 2018/6/22 21:49:09

RESETLOGS

--     Set #2. RESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 4096    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘  SIZE 200M BLOCKSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512-- STANDBY LOGFILEDATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE ‘/oracle/app/oracle/product/11.2.0/dbs/arch1_1_978829161.dbf‘;-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE USING BACKUP CONTROLFILE-- Database can now be opened zeroing the online logs.ALTER DATABASE OPEN RESETLOGS;-- Files in normal offline tablespaces are now named.ALTER DATABASE RENAME FILE ‘MISSING00006‘  TO ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs01.dbf‘;-- Commands to add tempfiles to temporary tablespaces.-- Online tempfiles have complete space information.-- Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘     SIZE 1024M REUSE AUTOEXTEND OFF;-- End of tempfile additions.

操作如下：

[BEGIN] 2018/6/22 21:58:29SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing options[[email protected] ~]$ sqlplus ‘/as sysdba‘SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 22 21:58:41 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Automatic Storage Management, OLAP, Data Miningand Real Application Testing optionsSQL> archive log list;Database log mode          No Archive ModeAutomatic archival         DisabledArchive destination        /oracle/app/oracle/product/11.2.0/dbs/archOldest online log sequence     10Current log sequence           14SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 4096    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘  SIZE 200M BLOCKSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512DATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK 21  ;CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG*ERROR at line 1:ORA-01034: ORACLE not availableProcess ID: 75347Session ID: 386 Serial number: 5SQL> CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOGSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512DATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 4096    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘  SIZE 200M BLOCKSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512DATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK 21  ;CREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG*ERROR at line 1:ORA-01034: ORACLE not availableProcess ID: 75347Session ID: 386 Serial number: 5SQL> startup nomount;ORACLE instance started.Total System Global Area 3858223104 bytesFixed Size          2259080 bytesVariable Size         788531064 bytesDatabase Buffers     3053453312 bytesRedo Buffers           13979648 bytesCREATE CONTROLFILE REUSE DATABASE "KYEUPDB" RESETLOGS  NOARCHIVELOG    MAXLOGFILES 160M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512DATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK    MAXLOGMEMBERS 3    MAXDATAFILES 4096    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 ‘+DGSYSTEM/kyeupdb/onlinelog/group_1.266.978829167‘  SIZE 200M BLOCKSIZE 512,  GROUP 2 ‘+DGSYSTEM/kyeupdb/onlinelog/group_2.267.978829169‘  SIZE 200M BLOCKSIZE 512,  GROUP 3 ‘+DGSYSTEM/kyeupdb/onlinelog/group_3.268.978829173‘  SIZE 200M BLOCKSIZE 512,  GROUP 4 ‘+DGSYSTEM/kyeupdb/onlinelog/group_4.269.978829183‘  SIZE 200M BLOCKSIZE 512,  GROUP 5 ‘+DGSYSTEM/kyeupdb/onlinelog/group_5.270.978829193‘  SIZE 200M BLOCKSIZE 512DATAFILE  ‘+DGSYSTEM/kyeupdb/datafile/system.271.978829205‘,  ‘+DGSYSTEM/kyeupdb/datafile/sysaux.272.978829265‘,  ‘+DGSYSTEM/kyeupdb/datafile/undotbs1.273.978829323‘,  ‘+DGSYSTEM/kyeupdb/datafile/users.275.978829391‘,  ‘+DGSYSTEM/kyeupdb/datafile/ts_example.277.978855421‘,  ‘+DGSYSTEM/kyeupdb/datafile/kye_tbs02.dbf‘CHARACTER SET ZHS16GBK 21  ;Control file created.SQL> ALTER DATABASE OPEN RESETLOGS;Database altered.ALTER TABLESPACE TEMP ADD TEMPFILE ‘+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379‘  2       SIZE 1024M REUSE AUTOEXTEND OFF;Tablespace altered.SQL> select * from dba_temp_files;FILE_NAME--------------------------------------------------------------------------------   FILE_ID TABLESPACE_NAME             BYTES     BLOCKS STATUS---------- ------------------------------ ---------- ---------- -------RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS------------ --- ---------- ---------- ------------ ---------- -----------+DGSYSTEM/kyeupdb/tempfile/temp.274.978829379     1 TEMP               1073741824     131072 ONLINE       1 NO       0      0        0 1072693248      130944SQL> archive log list;Database log mode          No Archive ModeAutomatic archival         DisabledArchive destination        /oracle/app/oracle/product/11.2.0/dbs/archOldest online log sequence     1Current log sequence           1SQL> [END] 2018/6/22 22:10:06

如果使用 resetlogs 開啟的資料庫，就需要對 DB 做一次備份。
resetlogs 命令表示一個資料庫邏輯生存期的結束和另一個資料庫邏輯生存期的開始， 每次使用 resetlogs 命令的時候，SCN 不會被重設，不過 oracle 會重設記錄序號， 而且會重設聯機重做日誌內容.
這樣做是為了防止不完全恢複後日誌序列會發生衝突（因為現有日誌和資料檔案間有 了時間差）。
在上面RESETLOGS重建控制檔案的時候，歸檔是關閉的，如果是開啟的，在開啟RESETLOG之前，需要執行RECOVER DATABASE USING BACKUP CONTROLFILE

oracle 控制檔案的重建