Oracle控制檔案丟失恢復歸檔模式方法

SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oradata/orcl/control01.ctl, /
                                                 u01/app/oracle/fast_recovery_a
                                                 rea/orcl/control02.ctl
[oracle@orcl:/oradata/orcl]$ ll /oradata/orcl/control01.ctl 
-rw-r----- 1 oracle oinstall 9748480 Apr 15 10:01 /oradata/orcl/control01.ctl
[oracle@orcl:/oradata/orcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
-rw-r----- 1 oracle oinstall 9748480 Apr 15 10:03 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
--確保開啟歸檔
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /archivelog
Oldest online log sequence     10
Current log sequence           12
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from props$;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 801
--控制檔案內容
--生成控制檔案
SQL> alter database backup controlfile to trace as '/home/oracle/ctlbak.ctl';
Database altered.
--檢視trace內容
[oracle@orcl:/home/oracle]$ cat ctlbak.ctl 
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/archivelog'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     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 PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf',
  '/oradata/orcl/sysaux01.dbf',
  '/oradata/orcl/undotbs01.dbf',
  '/oradata/orcl/users01.dbf',
  '/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;
-- 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 '/archivelog/1_1_1069941729.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;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
--     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 PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf',
  '/oradata/orcl/sysaux01.dbf',
  '/oradata/orcl/undotbs01.dbf',
  '/oradata/orcl/users01.dbf',
  '/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;
-- 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 '/archivelog/1_1_1069941729.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;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--

[oracle@orcl:/oradata/orcl]$ rm -rf /u01/app/oracle/fast_recovery_area/orcl/control02.ctl 
[oracle@orcl:/oradata/orcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/control02.ctl     
ls: cannot access /u01/app/oracle/fast_recovery_area/orcl/control02.ctl: No such file or directory
--開一個新的session，此時資料庫已經處於報錯狀態，無論什麼操作都會報錯，此時強制關閉資料庫
SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file:
'/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> 

--複製控制檔案
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ cp /oradata/orcl/control01.ctl control02.ctl
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ls
control02.ctl
--開啟資料庫
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
Database mounted.
Database opened.

--通過備份控制檔案獲取建立控制檔案指令碼
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf',
  '/oradata/orcl/sysaux01.dbf',
  '/oradata/orcl/undotbs01.dbf',
  '/oradata/orcl/users01.dbf',
  '/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8;
--開始恢復
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/oradata/orcl/system01.dbf',
 14    '/oradata/orcl/sysaux01.dbf',
 15    '/oradata/orcl/undotbs01.dbf',
 16    '/oradata/orcl/users01.dbf',
 17    '/oradata/orcl/example01.dbf'
 18  CHARACTER SET AL32UTF8;
Control file created.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf';
Tablespace altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

RMAN> backup current controlfile format '/home/oracle/ctrl.ora';
Starting backup at 15-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-APR-21
channel ORA_DISK_1: finished piece 1 at 15-APR-21
piece handle=/home/oracle/ctrl.ora tag=TAG20210415T104457 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-APR-21
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.64M      DISK        00:00:01     15-APR-21      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20210415T104457
        Piece Name: /home/oracle/ctrl.ora
  Control File Included: Ckp SCN: 1219869      Ckp time: 15-APR-21

[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ rm control02.ctl 
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ rm /oradata/orcl/control01.ctl 
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ll
total 0
[oracle@orcl:/u01/app/oracle/fast_recovery_area/orcl]$ ll /oradata/orcl/control01.ctl
ls: cannot access /oradata/orcl/control01.ctl: No such file or directory
SQL> alter tablespace USERS add datafile;
alter tablespace USERS add datafile
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Process ID: 2333
Session ID: 1 Serial number: 9
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
ORA-00205: error in identifying control file, check alert log for more info

--開啟到nomount狀態
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size                  2253664 bytes
Variable Size             452988064 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7319552 bytes
--rman恢復控制檔案
RMAN> restore controlfile from '/home/oracle/ctrl.ora';
Starting restore at 15-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 15-APR-21
--開啟資料庫到mount狀態
SQL> alter database mount;
Database altered.
--恢復資料庫
RMAN> recover database;
Starting recover at 15-APR-21
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 15-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Finished implicit crosscheck backup at 15-APR-21
Starting implicit crosscheck copy at 15-APR-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-APR-21
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database: 
ORA-19922: there is no parent row with id 0 and level 1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /oradata/orcl/redo03.log
archived log file name=/oradata/orcl/redo03.log thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 15-APR-21

SQL> alter database open resetlogs;
Database altered.

SQL> select open_mode from v$database;
select open_mode from v$database
                      *
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.

--通過spfile或者pfile檔案獲取資訊
--1.db_name
[oracle@orcl:/home/oracle]$ grep "db_name" pfile.ora 
*.db_name='orcl'
--2.字元集（通過dd if檢視system01資料檔案）
dd if=system01.dbf of=lucifer bs=8192 skip=801
--3.獲取資料檔案和紀錄檔檔名稱
[oracle@orcl:/oradata/orcl]$ ll
total 2083264
-rw-r-----. 1 oracle oinstall 328343552 Apr 15 15:14 example01.dbf
-rw-r-----. 1 oracle oinstall 125829632 Apr 15 15:14 redo01.log
-rw-r-----. 1 oracle oinstall 125829632 Apr 15 15:14 redo02.log
-rw-r-----. 1 oracle oinstall 125829632 Apr 15 15:17 redo03.log
-rw-r-----. 1 oracle oinstall 545267712 Apr 15 15:14 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 786440192 Apr 15 15:14 system01.dbf
-rw-r-----. 1 oracle oinstall  30416896 Apr 15 15:14 temp01.dbf
-rw-r-----. 1 oracle oinstall  89137152 Apr 15 15:14 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Apr 15 15:14 users01.dbf
--重建控制檔案（這裡不需要加臨時檔案，開啟資料庫之後需要reuse）
--開啟資料庫到nomount
STARTUP NOMOUNT;
--建立控制檔案
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
  GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/orcl/system01.dbf',
  '/oradata/orcl/sysaux01.dbf',
  '/oradata/orcl/undotbs01.dbf',
  '/oradata/orcl/users01.dbf',
  '/oradata/orcl/example01.dbf'
CHARACTER SET AL32UTF8
;
--恢復資料庫
RECOVER DATABASE;
--執行過程
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata/orcl/redo01.log'  SIZE 120M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/orcl/redo02.log'  SIZE 120M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/orcl/redo03.log'  SIZE 120M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/oradata/orcl/system01.dbf',
 14    '/oradata/orcl/sysaux01.dbf',
 15    '/oradata/orcl/undotbs01.dbf',
 16    '/oradata/orcl/users01.dbf',
 17    '/oradata/orcl/example01.dbf'
 18  CHARACTER SET AL32UTF8
 19  ;
Control file created.
SQL> RECOVER DATABASE;
Media recovery complete.

SQL> ALTER DATABASE OPEN;
Database altered.
--這裡需要將臨時檔案重用
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/orcl/temp01.dbf' REUSE;
Tablespace altered.