備份及應用控制檔案

1、在sqlplus下面備份控制檔案
SQL> alter database backup controlfile to trace;

資料庫已更改。

SQL> alter database backup controlfile to 'd:controlbak.ctl';

資料庫已更改。

2、關閉資料庫，然後刪除已有的控制檔案，再開啟，發現不能正常開啟
SQL> shutdown immediate;
資料庫已經關閉。
已經卸載資料庫。
ORACLE 常式已經關閉。
SQL> startup;
ORACLE 常式已經啟動。

Total System Global Area 319888364 bytes
Fixed Size 453612 bytes
Variable Size 192937984 bytes
Database Buffers 125829120 bytes
Redo Buffers 667648 bytes
ORA-00205: ?????????????????????

SQL> alter session set nls_language=american;

Session altered.

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info

--查看記錄檔有
Fri May 26 00:11:53 2006
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri May 26 00:12:48 2006
alter database mount
Fri May 26 00:12:48 2006
ORA-00202: controlfile: 'D:oracleoradatadb1control01.ctl'
ORA-27041: unable to open file
OSD-04002: 無法開啟檔案
O/S-Error: (OS 2) 系統找不到指定的檔案。

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

3、啟動到nomount重建控制檔案
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 319888364 bytes
Fixed Size 453612 bytes
Variable Size 192937984 bytes
Database Buffers 125829120 bytes
Redo Buffers 667648 bytes

--找到trace檔案，執行裡面的建立控制檔案的指令碼，建立控制檔案
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
user_dump_dest string D:oracleadmindb1udump
--到上面這個目錄裡把剛才備份的trace檔案找到，然後開啟會發現以下重建控制檔案指令碼

SQL> CREATE CONTROLFILE REUSE DATABASE "DB1" RESETLOGS ARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 'D:ORACLEORADATADB1REDO01.LOG' SIZE 100M,
10 GROUP 2 'D:ORACLEORADATADB1REDO02.LOG' SIZE 100M,
11 GROUP 3 'D:ORACLEORADATADB1REDO03.LOG' SIZE 100M
12 -- STANDBY LOGFILE
13 DATAFILE
14 'D:ORACLEORADATADB1SYSTEM01.DBF',
15 'D:ORACLEORADATADB1UNDOTBS01.DBF',
16 'D:ORACLEORADATADB1TEST01.DBF',
17 'D:ORACLEORADATADB1RCVCAT01.DBF',
18 'D:ORACLEORADATADB1DATA01.DBF'
19 CHARACTER SET ZHS16GBK
20 ;

控制檔案已建立

--查看控制檔案資訊
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string D:oracleoradatadb1control0
1.ctl, D:oracleoradatadb1c
ontrol02.ctl, D:oracleoradat
adb1control03.ctl

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

5、進行恢複，然後開啟資料庫到正常運行狀態
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--需要加resetlogs進行恢複，因為應用舊的控制檔案
SQL> alter database open resetlogs;

Database altered.

--查看日誌資訊，發現應用到的改變有5998333
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 5998333
Resetting resetlogs activation ID 0 (0x0)

SQL> select name,created,dbid,log_mode,checkpoint_change# from v$database;

NAME CREATED DBID LOG_MODE CHECKPOINT_CHANGE#
--------- --------- ---------- ------------ ------------------
DB1 26-MAY-06 1173694693 ARCHIVELOG 5998335

SQL>