數(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ě)重建控制文件的腳本.