oracle 还原归档,ORACLE 11Gr2 control file还原恢复之redo完整但归档部分丢失

ctl文件丢失且current的redo文件也丢失的话，可能需要进行不完全恢复，与单纯恢复控制文件是不一样的概念。

ctl文件丢失，且归档部分丢失，那么我们肯定无法从备份的ctl文件还原到instance crash前一时刻的数据库状态，因为在apply日志时，会遇到

日志找不到报错，此时，datafile，redo file的都是正常的，我们可以通过重建控制文件来恢复。

重建ctl文件的会遇到两个问题:

1.了解database物理结构

2.DB open后需要重建temporary tablespace

场景：丢失部分归档、且创建表及其插入数据(此时的归档没有丢失)，current redo文件正常

删除归档之后,我们进行了数据数据删除插入.

SQL> truncate table trsen;

Table truncated.

SQL> insert into trsen select owner,object_name,object_id,object_type from dba_objects where rownum< =2;

2 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from trsen;

COUNT(1)

———-

2

从备份中还原ctl文件并进行恢复,发现报错

RMAN> restore controlfile from ‘/oradata/dbbackup/TRSEN_db_66qqmo3k’;

Starting restore at 06-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=169 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/oradata/TRSENDB/controlfile/ctl_01.ctl

output file name=/oradata/TRSENDB/controlfile/ctl_02.ctl

Finished restore at 06-JAN-16

RMAN> startup mount;

database is already started

database mounted

released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 06-JAN-16

Starting implicit crosscheck backup at 06-JAN-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=169 device type=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 06-JAN-16

Starting implicit crosscheck copy at 06-JAN-16

using channel ORA_DISK_1

Crosschecked 4 objects

Finished implicit crosscheck copy at 06-JAN-16

searching for all files in the recovery area

cataloging files…

cataloging done

List of Cataloged Files

=======================

File Name: /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_6_c8sbklwx_.arc

File Name: /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_2_c8sbcro3_.arc

File Name: /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_3_c8sbcshl_.arc

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_2_c8sbcro3_.arc

archived log for thread 1 with sequence 3 is already on disk as file /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_3_c8sbcshl_.arc

archived log for thread 1 with sequence 5 is already on disk as file /oradata/TRSEN/onlinelog/o1_mf_2_c6wp4qcz_.log

archived log for thread 1 with sequence 6 is already on disk as file /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_6_c8sbklwx_.arc

archived log for thread 1 with sequence 7 is already on disk as file /oradata/TRSEN/onlinelog/o1_mf_1_c6wp4q4n_.log

archived log file name=/oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_2_c8sbcro3_.arc thread=1 sequence=2

archived log file name=/oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_3_c8sbcshl_.arc thread=1 sequence=3

unable to find archived log

archived log thread=1 sequence=4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/06/2016 13:48:26

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 3241696============>>>>>4和5号归档已经被我手工删除，现在在recover ctl文件时发现归档丢失

如果这里按照它所提示做until scn恢复的话，就是不完全恢复，但我们的datafile文件没有损害，我们没有必要这样做。这里选择重建控制文件

SQL> alter database backup controlfile to trace;

Database altered.

SQL> select value from v$diag_info where value like ‘%.trc’;

VALUE

——————————————————————————–

/u01/app/oracle/diag/rdbms/trsen/trsen/trace/trsen_ora_10752.trc

[oracle@gpsdb controlfile]$ sed -n ‘/CREATE CONTROLFILE.*NORESETLOGS/,/;/p’ /u01/app/oracle/diag/rdbms/trsen/trsen/trace/trsen_ora_10752.trc

CREATE CONTROLFILE REUSE DATABASE “TRSEN” NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘/oradata/TRSEN/onlinelog/o1_mf_1_c6wp4q4n_.log’ SIZE 50M BLOCKSIZE 512,

GROUP 2 ‘/oradata/TRSEN/onlinelog/o1_mf_2_c6wp4qcz_.log’ SIZE 50M BLOCKSIZE 512,

GROUP 3 ‘/oradata/TRSEN/onlinelog/o1_mf_3_c6wp4qm5_.log’ SIZE 50M BLOCKSIZE 512

— STANDBY LOGFILE

DATAFILE

‘/oradata/TRSENDB/datafile/o1_mf_system_c6nty8kw_.dbf’,

‘/oradata/TRSENDB/datafile/o1_mf_sysaux_c6ntydjv_.dbf’,

‘/oradata/TRSENDB/datafile/o1_mf_undotbs1_c6ntyh03_.dbf’,

‘/oradata/TRSENDB/datafile/o1_mf_users_c6ntyp7q_.dbf’,

‘/oradata/trsen.dbf’,

‘/oradata/trsen2’,

‘/oradata/trsen3’,

‘/oradata/exp_his_06_1.dbf’,

‘/oradata/exp_his_06_2dbf’,

‘/oradata/exp_his_06_3.dbf’,

‘/oradata/t_ctl.dbf’

CHARACTER SET AL32UTF8

;

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/oradata/TRSENDB/datafile/o1_mf_system_c6nty8kw_.dbf’

SQL> recover database;==========>>>>>>注意这里的操作，若在rman下做会遇到什么情况呢?

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> conn trsen/trsen

Connected.

SQL> select count(1) from trsen;====>>>验证，顺利open数据库，并且归档丢失后的操作还是保留

COUNT(1)

———-

2

如果ctl文件后，在rman下进行recover database 则出现RMAN-06054错误

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘/oradata/TRSENDB/datafile/o1_mf_system_c6nty8kw_.dbf’

RMAN> recover database;

Starting recover at 06-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=169 device type=DISK

starting media recovery

archived log for thread 1 with sequence 48 is already on disk as file /oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_48_c8sdofxh_.arc

archived log file name=/oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_48_c8sdofxh_.arc thread=1 sequence=48

unable to find archived log

archived log thread=1 sequence=49

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/06/2016 14:28:08

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 49 and starting SCN of 3263107============>>>>>>这里的所提示的归档是current redo日志，

SQL> select member,l.status,sequence# from v$log l ,v$Logfile f where f.group#=l.group#;

MEMBER

——————————————————————————–

STATUS SEQUENCE#

—————- ———-

/oradata/TRSEN/onlinelog/o1_mf_1_c6wp4q4n_.log==================>>>>>

CURRENT 49

/oradata/TRSEN/onlinelog/o1_mf_3_c6wp4qm5_.log

INACTIVE 48

/oradata/TRSEN/onlinelog/o1_mf_2_c6wp4qcz_.log

INACTIVE 47

SQL> recover database using backup controlfile;

ORA-00279: change 3263107 generated at 01/06/2016 14:22:37 needed for thread 1

ORA-00289: suggestion :

/oradata/db_recovery_dest/TRSEN/archivelog/2016_01_06/o1_mf_1_49_%u_.arc

ORA-00280: change 3263107 for thread 1 is in sequence #49

Specify log: {=suggested | filename | AUTO | CANCEL}

/oradata/TRSEN/onlinelog/o1_mf_1_c6wp4q4n_.log

Log applied.

Media recovery complete.

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;=========>>>>>>>>>>此时需要resetlogs才能开启，虽然我在create ctl文件指定为noresetlogs

Database altered.