控制文件全部损坏的解决方案

数据库：Oracle11.2.0.4

系统：CentOS7.9

实验目标：模拟控制文件全部损坏，并提供解决方案.

1、控制文件损坏前数据

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、关闭数据库

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

启动数据库报错

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

说明：此处成功模拟出控制文件全部丢失无法启动数据库的异常情况.

3、解决方案：

说明：如果在数据库还未关闭的的情况下，发现控制文件损坏，可以使用命令将重建控制文件的脚本输出到trace文件中，方便控制文件的重建.

语句：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

系统层面查看控制文件，如下所示：控制文件已恢复到原目录.

[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

说明：如果控制文件存在备份，那么最好使用备份的控制文件进行恢复.此处用的是数据库未关闭时生成的控制文件进行的重建.

试想一下，如果数据库处于关闭状态，而此时发现控制文件全部丢失，作为DBA应该怎么办？这时就需要去数据文件、redo文件相应的目录下，每个文件进行比对编写重建控制文件的脚本.