1.1. 查看Snapshot Control File位置
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_PROD.f'; # default
RMAN>
1.2. 检查Snapshot Control File是否存在
[oracle@secdb1 dbs]$
[oracle@secdb1 dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@secdb1 dbs]$ ll -h snapcf_PROD.f
-rw-r----- 1 oracle oinstall 6.6M Jan 8 23:21 snapcf_PROD.f
[oracle@secdb1 dbs]$
1.3. Snapshot Control File的官方解释
When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file. RMAN needs a snapshot control file only when resynchronizing with the recovery catalog or when making a backup of the current control file.
当RMAN需要重新同步，版本控制文件的读一致性，它会创建一个临时快照控制文件。只有在重新同步恢复目录或备份当前控制文件的时候，RMAN需要一个快照控制文件。
1.4. 模拟控制文件丢失
1.4.1查看控制文件位置
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/PROD/d
isk1/control01.ctl, /u01/app/o
racle/oradata/PROD/disk2/contr
ol02.ctl, /u01/app/oracle/orad
ata/PROD/disk3/control03.ctl
SQL>
1.4.2 删除当前控制文件
SQL>!rm -rf /u01/app/oracle/oradata/PROD/disk1/control01.ctl
SQL>!rm -rf /u01/app/oracle/oradata/PROD/disk2/control02.ctl
SQL>!rm -rf /u01/app/oracle/oradata/PROD/disk3/control03.ctl
这里我们分两种情况分别处理，第一种情况是在控制文件丢失数据库重启后恢复，第二种是控制文件丢失数据库没有重启恢复
1.5控制文件丢失数据库重启后恢复
1.5.1关闭数据库
我们发现不能正常关闭，使用abort关闭
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL>
1.5.2启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 75498896 bytes
Database Buffers 234881024 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
1.5.3检查alert日志文件
这里报找不到控制文件
Wed Jan 23 17:16:22 2013
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Wed Jan 23 17:16:22 2013
ORA-205 signalled during: ALTER DATABASE MOUNT...
1.5.4把Snapshot Control File 复制到控制文件目录
[oracle@secdb1 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk1/control01.ctl
[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk2/control02.ctl
[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk3/control03.ctl
[oracle@secdb1 dbs]$
1.5.5 mount数据库
SQL> alter database mount;
Database altered.
SQL>
1.5.6 open数据库
open数据库报错，需要恢复数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk1/system01.dbf'
SQL>
1.5.7恢复数据库
SQL> recover database using backup controlfile;
ORA-00279: change 185863 generated at 01/08/2013 23:21:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_11_%u_.arc
ORA-00280: change 185863 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo04_a.log
ORA-00328: archived log ends at change 185845, need later change 185863
ORA-00334: archived log: '/u01/app/oracle/oradata/PROD/disk1/redo04_a.log'
SQL> recover database using backup controlfile;
ORA-00279: change 185863 generated at 01/08/2013 23:21:15 needed for thread 1
ORA-00289: suggestion :
/home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_11_%u_.arc
ORA-00280: change 185863 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo05_a.log
Log applied.
Media recovery complete.
SQL>
1.5.8 重新open数据库
SQL> alter database open resetlogs;
Database altered.
SQL> SQL> SQL>
1.6 控制文件丢失数据库没有重启恢复
1.6.1执行1.4模拟控制文件丢失
SQL> !rm -rf /u01/app/oracle/oradata/PROD/disk1/control01.ctl
SQL> !rm -rf /u01/app/oracle/oradata/PROD/disk2/control02.ctl
SQL> !rm -rf /u01/app/oracle/oradata/PROD/disk3/control03.ctl
SQL>
1.6.2检查控制文件是否存在
[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk1/control01.ctl
ls: /u01/app/oracle/oradata/PROD/disk1/control01.ctl: No such file or directory
[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk2/control02.ctl
ls: /u01/app/oracle/oradata/PROD/disk2/control02.ctl: No such file or directory
[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk3/control03.ctl
ls: /u01/app/oracle/oradata/PROD/disk3/control03.ctl: No such file or directory
[oracle@secdb1 dbs]$
1.6.3插入测试数据
控制文件丢失还可以插入数据
SQL> select * from test;
no rows selected
SQL> insert into test values(1);
1 row created.
SQL> select * from test;
ID
----------
1
SQL> commit;
Commit complete.
1.6.4把Snapshot Control File 复制到控制文件目录
[oracle@secdb1 dbs]$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk1/control01.ctl
[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk2/control02.ctl
[oracle@secdb1 dbs]$ cp snapcf_PROD.f /u01/app/oracle/oradata/PROD/disk3/control03.ctl
1.6.5检查控制文件恢复状态
[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk1/control01.ctl
-rw-r----- 1 oracle oinstall 6832128 Jan 23 17:42 /u01/app/oracle/oradata/PROD/disk1/control01.ctl
[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk2/control02.ctl
-rw-r----- 1 oracle oinstall 6832128 Jan 23 17:42 /u01/app/oracle/oradata/PROD/disk2/control02.ctl
[oracle@secdb1 dbs]$ ll /u01/app/oracle/oradata/PROD/disk3/control03.ctl
-rw-r----- 1 oracle oinstall 6832128 Jan 23 17:42 /u01/app/oracle/oradata/PROD/disk3/control03.ctl
[oracle@secdb1 dbs]$
1.6.6测试当前控制文件是否可用
查询视图控制文件可用，但是alert报错
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select dbid from v$database;
DBID
----------
215959243
1.6.7停止数据库
SQL> shutdown immediate;
ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []
SQL>
查看alert信息：
Wed Jan 23 21:22:11 2013
Error: Controlfile was changed externally while mounted
Please check if another Oracle database is running
and accessing the same controlfile
Wed Jan 23 21:22:11 2013
Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:
ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []
Wed Jan 23 21:22:12 2013
Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:
ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []
Wed Jan 23 21:22:12 2013
Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:
ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []
Wed Jan 23 21:22:12 2013
Errors in file /u01/app/oracle/admin/PROD/udump/prod_ora_5636.trc:
ORA-00600: internal error code, arguments: [2141], [217309148], [0], [], [], [], [], []
1.6.8 强制停止数据库
SQL> shutdown abort
ORACLE instance shut down.
SQL>
1.6.9 启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL>
1.6.10使用resetlogs启动数据库
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk1/system01.dbf'
1.6.11 recovery数据库
SQL> recover database using backup controlfile;
ORA-00279: change 185863 generated at 01/08/2013 23:21:15 needed for thread 1
ORA-00289: suggestion : /home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_11_%u_.arc
ORA-00280: change 185863 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo05_a.log
ORA-00279: change 207533 generated at 01/08/2013 23:42:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_12_%u_.arc
ORA-00280: change 207533 for thread 1 is in sequence #12
ORA-00278: log file '/u01/app/oracle/oradata/PROD/disk1/redo05_a.log' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo01.log
ORA-00310: archived log contains sequence 9; sequence 12 required
ORA-00334: archived log: '/u01/app/oracle/oradata/PROD/disk1/redo01.log'
SQL> recover database using backup controlfile;
ORA-00279: change 207533 generated at 01/08/2013 23:42:43 needed for thread 1
ORA-00289: suggestion : /home/oracle/flash/PROD/archivelog/2013_01_23/o1_mf_1_12_%u_.arc
ORA-00280: change 207533 for thread 1 is in sequence #12
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/PROD/disk1/redo02.log
Log applied.
Media recovery complete.
SQL>
1.6.12 重新open数据库
SQL> alter database open resetlogs;
Database altered.
SQL>
