恢复控制文件后如何避免resetlogs打开数据库?

恢复控制文件后,数据库使用resetlogs方式打开,在线日志的序列被重置。

SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log;

    GROUP#  SEQUENCE# ARC STATUS          FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --- ---------------- ------------- ------------
        1          1 NO  CURRENT                1014415  2.8147E+14
        2          0 YES UNUSED                      0            0
        3          0 YES UNUSED                      0            0

如果没有被resetlog截断的话当前日志的序列号应该是7,8,9
SYS@practice >select recid,sequence#,first_change#,next_change# from v$log_history;

    RECID  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
        1          1        925702      955284
        2          2        955284      955847
        3          3        955847      971151
        4          4        971151      987800
        5          5        987800      997957
        6          6        997957      1010981
        7          1      1010981      1013463
        8          2      1013463      1013471
        9          3      1013471      1014415

如果恢复完控制文件,不使用resetlogs是打不开数据库的。

我们可以采取手工创建控制文件的方法打开数据库,保持联机日志的sequence#连续。下面的实验来演示具体操作过程。

在实验之前先手工切换3次日志,使得在线日志的序列号产生变化。在恢复结束后作为参考。
SYS@practice >alter system archive log current;

SYS@practice >alter system archive log current;

SYS@practice >alter system archive log current;

切换后当前连接日志状态如下
SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log;

    GROUP#  SEQUENCE# ARC STATUS          FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --- ---------------- ------------- ------------
        1          4 NO  CURRENT                1030130  2.8147E+14
        2          2 YES INACTIVE              1030109      1030117
        3          3 YES ACTIVE                1030117      1030130

1,恢复控制文件
RMAN> startup force nomount;

Oracle instance started

Total System Global Area    580395008 bytes

Fixed Size                    2255392 bytes
Variable Size                402654688 bytes
Database Buffers            171966464 bytes
Redo Buffers                  3518464 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 06-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/fast_recovery_area
database name (or database unique name) used for search: PRACTICE
channel ORA_DISK_1: AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20141006
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/practice/control01.ctl
output file name=/u01/fast_recovery_area/practice/control02.ctl
Finished restore at 06-OCT-14

2,启动数据库到mount状态
RMAN> mount database;

database mounted
released channel: ORA_DISK_1

3,恢复数据库
RMAN> recover database;

Starting recover at 06-OCT-14
Starting implicit crosscheck backup at 06-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 06-OCT-14

Starting implicit crosscheck copy at 06-OCT-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-OCT-14

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc
File Name: /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arc
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251717_b35s05gm_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251162_b35rgt84_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860251158_b35rgpms_.bkp
File Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860246909_b35n9x55_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/oradata/practice/redo01.log
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_1_b362kndr_.arc thread=1 sequence=1
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_2_b362l34q_.arc thread=1 sequence=2
archived log file name=/u01/fast_recovery_area/PRACTICE/archivelog/2014_10_06/o1_mf_1_3_b362lc83_.arc thread=1 sequence=3
archived log file name=/u01/oradata/practice/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-OCT-14

4,备份控制文件到trace
SYS@practice >select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SYS@practice >alter database backup controlfile to trace;


SYS@practice >select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/diag/rdbms/practice/practice/trace/practice_ora_1185.trc

5,重启实例到nomount状态
RMAN> startup force nomount;

Oracle instance started

Total System Global Area    580395008 bytes

Fixed Size                    2255392 bytes
Variable Size                402654688 bytes
Database Buffers            171966464 bytes
Redo Buffers                  3518464 bytes

6,执行重建控制文件命令进入到mount状态
vi /home/oracle/create_controlfile.sql

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRACTICE" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/practice/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/practice/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/practice/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/oradata/practice/system01.dbf',
  '/u01/oradata/practice/sysaux01.dbf',
  '/u01/oradata/practice/undotbs01.dbf',
  '/u01/oradata/practice/users01.dbf',
  '/u01/oradata/practice/example01.dbf'
CHARACTER SET AL32UTF8
;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/practice/temp01.dbf' REUSE;

在sqlplus下执行创建控制文件脚本,数据库会启动到open状态并添加临时表空间文件。

SYS@practice >@create_controlfile.sql

Control file created.

PL/SQL procedure successfully completed.

Media recovery complete.

System altered.

Database altered.

Tablespace altered.

此时数据库已经处于read write状态也就是open状态了
SYS@practice >select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

查看当前日志的序列号,没有被重置。
SYS@practice >select group#,sequence#,archived,status,first_change#,next_change# from v$log;

    GROUP#  SEQUENCE# ARC STATUS          FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- --- ---------------- ------------- ------------
        1          4 YES INACTIVE              1030130      1050296
        2          5 NO  CURRENT                1050296  2.8147E+14
        3          3 YES INACTIVE              1030117      1030130

查看临时表数据文件也被创建出来了。
SYS@practice >select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/oradata/practice/temp01.dbf


7,重新识别控制文件的备份信息和配置信息

此时的控制文件中没有之前备份过控制文件的信息
RMAN> list backup of controlfile;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

从闪回恢复区重新注册备份信息
RMAN> catalog db_recovery_file_dest;

从执行过数据库全备份的地址注册备份信息
RMAN> catalog start with '/home/oracle/';

再次列出备份过的控制文件,可以看到已经全部注册成功
RMAN> list backup of controlfile;

List of Backup Sets
===================

BS Key  Type LV Size      Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1      Full    9.67M      DISK        00:00:00    06-OCT-14
        BP Key: 1  Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T175610
        Piece Name: /u01/fast_recovery_area/PRACTICE/autobackup/2014_10_06/o1_mf_s_860262970_b363zt2x_.bkp
  Control File Included: Ckp SCN: 1051644      Ckp time: 06-OCT-14

BS Key  Type LV Size      Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3      Full    9.33M      DISK        00:00:00    06-OCT-14
        BP Key: 3  Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T132827
        Piece Name: /u01/fast_recovery_area/PRACTICE/backupset/2014_10_06/o1_mf_ncnnf_TAG20141006T132827_b35n9w39_.bkp
  Control File Included: Ckp SCN: 1005439      Ckp time: 06-OCT-14

BS Key  Type LV Size      Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7      Full    9.33M      DISK        00:00:00    06-OCT-14
        BP Key: 7  Status: AVAILABLE  Compressed: NO  Tag: TAG20141006T143909
        Piece Name: /home/oracle/full_PRACTICE_9_20141006_1.bak
  Control File Included: Ckp SCN: 1013438      Ckp time: 06-OCT-14

本次实验到此结束。

相关推荐