通过Snapshot Control File 恢复控制文件

大家好!
今天AningDBA和大家分享的是通过rman 快照控制文件恢复控制文件的方法:
我今天测试使用的oracle版本是10.2.01 由于版本问题,实验结果可能会存在差异。
 

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.4Snapshot 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.4Snapshot 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>
上一篇:[MySQL 版本差异] 丢失frm文件之后drop database的不同结果分析


下一篇:开源实时流处理系统小结