控制文件全部損壞的解決方案
數(shù)據(jù)庫(kù)：Oracle11.2.0.4
系統(tǒng)：CentOS7.9
實(shí)驗(yàn)?zāi)繕?biāo)：模擬控制文件全部損壞，并提供解決方案.
1、控制文件損壞前數(shù)據(jù)
SQL>show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /data/orcl/control01.ctl, /u01
/app/oracle/fast_recovery_area
/orcl/control02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>select b.thread#,a.group#,a.member,bytes/1024/1024,b.members from v$logfilea,v$log b where a.group#=b.group#
THREAD# GROUP# MEMBER BYTES/1024/1024 MEMBERS
-------------------- ------------------------------ --------------- ----------
1 2 /data/orcl/redo02.log 50 2
1 2 /data/orcl/redo02b.log 50 2
1 1 /data/orcl/redo01.log 50 2
1 1 /data/orcl/redo01b.log 50 2
2 3 /data/orcl/redo03.log 50 2
2 3 /data/orcl/redo03b.log 50 2
2 4 /data/orcl/redo04.log 50 2
2 4 /data/orcl/redo04b.log 50 2
8 rowsselected.
SQL>select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/data/orcl/system.256.1107818145
/data/orcl/sysaux.257.1107818147
/data/orcl/undotbs1.258.1107818147
/data/orcl/users.259.1107818147
/data/orcl/undotbs2.267.1107818231
/data/orcl/system.319.1110101255
6 rowsselected.
SQL>select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp02.dbf
SQL>archive log list;
Databaselog mode Archive Mode
Automaticarchival Enabled
Archivedestination USE_DB_RECOVERY_FILE_DEST
Oldestonline log sequence 22
Next logsequence to archive 23
Currentlog sequence 23
SQL>select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
2、模擬控制文件損壞
2.1、關(guān)閉數(shù)據(jù)庫(kù)
SQL>shutdown immediate
Databaseclosed.
Database dismounted.
ORACLEinstance shut down.
2.2、刪除所有控制文件
[oracle@leoorcl]$ mv /data/orcl/control01.ctl /data/orcl/control01bak.ctl
[oracle@leoorcl]$ mv /u01/app/oracle/fast_recovery_area/orcl/control02.ctl/u01/app/oracle/fast_recovery_area/orcl/control02bak.ctl
啟動(dòng)數(shù)據(jù)庫(kù)報(bào)錯(cuò)
SQL>startup
ORACLEinstance started.
TotalSystem Global Area 3006406656 bytes
FixedSize 2257032 bytes
VariableSize 704647032 bytes
DatabaseBuffers 2281701376 bytes
Redo Buffers 17801216 bytes
ORA-00205:error in identifying control file, check alert log for more info
說(shuō)明：此處成功模擬出控制文件全部丟失無(wú)法啟動(dòng)數(shù)據(jù)庫(kù)的異常情況.
3、解決方案：
說(shuō)明：如果在數(shù)據(jù)庫(kù)還未關(guān)閉的的情況下，發(fā)現(xiàn)控制文件損壞，可以使用命令將重建控制文件的腳本輸出到trace文件中，方便控制文件的重建.
語(yǔ)句：alterdatabase backup controlfile to trace;
重建控制文件
SQL>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 192
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 32
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '/data/orcl/redo01.log',
10 '/data/orcl/redo01b.log'
11 )SIZE 50M BLOCKSIZE 512,
12 GROUP 2 (
13 '/data/orcl/redo02.log',
14 '/data/orcl/redo02b.log'
15 )SIZE 50M BLOCKSIZE 512,
16 GROUP 3 (
17 '/data/orcl/redo03.log',
18 '/data/orcl/redo03b.log'
19 )SIZE 50M BLOCKSIZE 512,
20 GROUP 4 (
21 '/data/orcl/redo04.log',
22 '/data/orcl/redo04b.log'
23 )SIZE 50M BLOCKSIZE 512
24 DATAFILE
25 '/data/orcl/system.256.1107818145',
26 '/data/orcl/sysaux.257.1107818147',
27 '/data/orcl/undotbs1.258.1107818147',
28 '/data/orcl/users.259.1107818147',
29 '/data/orcl/undotbs2.267.1107818231',
30 '/data/orcl/system.319.1110101255'
31 CHARACTER SET WE8MSWIN1252
32 ;
Controlfile created.
SQL>select status from v$database;
STATUS
------------
MOUNTED
SQL>alter database open;
Databasealtered.
SQL>select name from v$tempfile;
no rowsselected
SQL>create temporary tablespace temp tempfile '/data/orcl/temp01.dbf' size 50mautoextend on;
Tablespacecreated.
SQL>show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /data/orcl/control01.ctl, /u01
/app/oracle/fast_recovery_area
/orcl/control02.ctl
系統(tǒng)層面查看控制文件，如下所示：控制文件已恢復(fù)到原目錄.
[oracle@leoorcl]$ ll
total3654896
-rw-r-----1 oracle asmadmin 18530304 Jul 28 16:22control01bak.ctl
-rw-r-----1 oracle asmadmin 18825216 Jul 28 16:34control01.ctl
[oracle@leoorcl]$ ll /u01/app/oracle/fast_recovery_area/orcl/
total36480
-rw-r-----1 oracle asmadmin 18530304 Jul 28 16:22 control02bak.ctl
-rw-r-----1 oracle asmadmin 18825216 Jul 28 16:37 control02.ctl
說(shuō)明：如果控制文件存在備份，那么最好使用備份的控制文件進(jìn)行恢復(fù).此處用的是數(shù)據(jù)庫(kù)未關(guān)閉時(shí)生成的控制文件進(jìn)行的重建.
試想一下，如果數(shù)據(jù)庫(kù)處于關(guān)閉狀態(tài)，而此時(shí)發(fā)現(xiàn)控制文件全部丟失，作為DBA應(yīng)該怎么辦？這時(shí)就需要去數(shù)據(jù)文件、redo文件相應(yīng)的目錄下，每個(gè)文件進(jìn)行比對(duì)編寫(xiě)重建控制文件的腳本.
