数据库：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文件相应的目录下，每个文件进行比对编写重建控制文件的脚本.
?
?