trace檔案備份控制檔案並執行恢復

trace中的控制檔案備份執行恢復，這種透過檢視trace檔案的記錄，檢視資料庫開啟過程中
執行控制檔案的情況。

---以sysdba身份登入sqlplus，將控制檔案轉儲到trace檔案中:

sys@PROD>alter database backup controlfile to trace;

Database altered.

--找到轉儲trace檔案:

[oracle@enmo trace]$ ls -lrt

... ...

-rw-r----- 1 oracle oinstall   8293 Nov  7 15:33 PROD_ora_15666.trc

-rw-r----- 1 oracle oinstall    109 Nov  7 15:53 PROD_mmon_15614.trm

-rw-r----- 1 oracle oinstall   1425 Nov  7 15:53 PROD_mmon_15614.trc

-rw-r----- 1 oracle oinstall    119 Nov  7 15:53 PROD_ora_15629.trm

-rw-r----- 1 oracle oinstall   8167 Nov  7 15:53 PROD_ora_15629.trc

-rw-r----- 1 oracle oinstall 176417 Nov  7 15:53 alert_PROD.log

[oracle@enmo trace]$

 #按時間排序，找到位置最後的trace檔案。

---檢視最新生成的trace檔案，可以清晰的看到建立控制檔案的指令碼:

[oracle@enmo trace]$ more PROD_ora_15629.trc

Trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_15629.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

... ...

--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/PROD/system01.dbf',

  '/u01/app/oracle/oradata/PROD/sysaux01.dbf',

  '/u01/app/oracle/oradata/PROD/undotbs01.dbf',

  '/u01/app/oracle/oradata/PROD/users01.dbf',

  '/u01/app/oracle/oradata/PROD/example01.dbf',

  '/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf',

  '/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'

CHARACTER SET AL32UTF8

;

#這部分就是開啟資料庫過程中啟用控制檔案的過程。
 

---刪除控制檔案並關閉資料庫：

sys@PROD>!rm /u01/app/oracle/oradata/PROD/control01.ctl

sys@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

 

sys@PROD>!ls /u01/app/oracle/oradata/PROD/control01.ctl

ls: /u01/app/oracle/oradata/PROD/control01.ctl: No such file or directory

sys@PROD>!ls /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

ls: /u01/app/oracle/fast_recovery_area/PROD/control02.ctl: No such file or directory

 

---嘗試關閉資料庫：

sys@PROD>shutdown immediate;

Database closed.

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/PROD/control01.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

#由於沒有控制檔案，正常關庫關不了。

#需要強行關庫。

---強制性關庫:

sys@PROD>shutdown abort;

ORACLE instance shut down.

sys@PROD>

 

---按照轉儲trace檔案中的步驟，恢復控制檔案，第一步：啟動到nomount狀態:

sys@PROD>startup nomount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2257840 bytes

Variable Size             507513936 bytes

Database Buffers          322961408 bytes

Redo Buffers                2371584 bytes

sys@PROD>

 

---建立控制檔案：

sys@PROD>CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS  ARCHIVELOG

  2   MAXLOGFILES 16

  3  MAXLOGMEMBERS 3

  4   MAXDATAFILES 100

  5  MAXINSTANCES 8

  6  MAXLOGHISTORY 292

  7  LOGFILE

  8  GROUP 1 '/u01/app/oracle/oradata/PROD/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9  GROUP 2 '/u01/app/oracle/oradata/PROD/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10  GROUP 3 '/u01/app/oracle/oradata/PROD/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13  '/u01/app/oracle/oradata/PROD/system01.dbf',

 14  '/u01/app/oracle/oradata/PROD/sysaux01.dbf',

 15  '/u01/app/oracle/oradata/PROD/undotbs01.dbf',

 16  '/u01/app/oracle/oradata/PROD/users01.dbf',

 17  '/u01/app/oracle/oradata/PROD/example01.dbf',

 18  '/u01/app/oracle/oradata/PROD/ts_xxf_01.dbf',

 19  '/u01/app/oracle/oradata/PROD/ts_ctl01.dbf'

 20  CHARACTER SET AL32UTF8

 21  ;

Control file created.

sys@PROD>

#控制檔案已經建立成功。

---檢視資料庫例項的狀態：

sys@PROD>select status from v$instance;

STATUS

------------

MOUNTED

#建立控制檔案之後，自動變換到mount狀態。

--系統檢視控制檔案的路徑：

sys@PROD>!ls /u01/app/oracle/oradata/PROD/control01.ctl

/u01/app/oracle/oradata/PROD/control01.ctl

 

sys@PROD>!ls /u01/app/oracle/fast_recovery_area/PROD/control02.ctl

/u01/app/oracle/fast_recovery_area/PROD/control02.ctl

#已經生產新的控制檔案。

 

---嘗試恢復資料庫並開啟資料庫：

sys@PROD>RECOVER DATABASE;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

 

sys@PROD>ALTER SYSTEM ARCHIVE LOG ALL;

ALTER SYSTEM ARCHIVE LOG ALL

*

ERROR at line 1:

ORA-01649: operation not allowed with a backup control file

 

sys@PROD>ALTER DATABASE OPEN;

ALTER DATABASE OPEN

*

ERROR at line 1:

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

 

sys@PROD>ALTER DATABASE OPEN RESETLOGS;

Database altered.

#資料庫是以RESETLOGS方式開啟。

 

--新增臨時資料檔案：

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD/temp01.dbf' SIZE 100M  REUSE AUTOEXTEND ON NEXT 10  MAXSIZE 200M;

sys@PROD>ALTER TABLESPACE TEMP ADD TEMPFILE

  2  '/u01/app/oracle/oradata/PROD/temp01.dbf'

  3  SIZE 100M  REUSE AUTOEXTEND ON NEXT 10  MAXSIZE 200M;

Tablespace altered.

sys@PROD>

 

sys@PROD>select FILE_NAME from dba_temp_files;

FILE_NAME

--------------------------------------------------------------------------

/u01/app/oracle/oradata/PROD/temp01.dbf

sys@PROD>

#控制檔案恢復完成。