oracle用備份的控制檔案恢復後不用resetlogs開啟方式的恢復

在一般恢復備份的控制檔案,開啟資料庫都要用resetlogs 重置日誌這種方式,

這是因為備份的控制檔案恢復後,沒有最新的當前線上日誌資訊 ,而重建控制檔案

就可以用 NORESETLOGS  這種方式開啟

這是因為:

備份的控制檔案裡面有一個 截止 SCN
正常關閉的資料庫的控制檔案裡面也有一個截止scn
正常開啟的資料庫的控制檔案截止scn是無窮大(crash 也是)
重新建立控制檔案的截止SCN也是無窮大，也就是apply日誌檔案直到scn達到截止scn，於是這就導致你應用完了所有歸檔的日誌還沒有達到這無窮大，open的時候資料庫認為是非正常關閉進行崩潰恢復，自動應用了所有聯機日誌

操作方法:

使用舊的控制檔案mount 然後 alter database  backup  controlfile to  trace ，restore database  然後手工建立控制檔案，使用 reuse database  pubtest  noresetlogs .這樣就可以 recover database 自動恢復並open database 而不用 resetlogs 了

以下是測試過程:

9.2.0.4版本

1.先用rman備份資料資料

rman>backup database format 'e:\testbk\%U.bak';

2.插入資料

SQL> create table test tablespace users as select rownum id from dba_objects;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
      6441

SQL> alter system switch logfile;

System altered.

SQL> insert into test select * from test;

6441 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)
----------
     12882

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Aug 12 10:17:33 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

C:\Documents and Settings\Paul Yi>rman target /

Recovery Manager: Release 9.2.0.8.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     101785012 bytes

Fixed Size                      454068 bytes
Variable Size                 75497472 bytes
Database Buffers              25165824 bytes
Redo Buffers                    667648 bytes

RMAN> set dbid=799229701

executing command: SET DBID

RMAN> restore controlfile from 'd:\backup\C-799229701-20080812-00';

Starting restore at 12-AUG-08

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE\ORADATA\PUBTEST\CONTROL01.CTL
output filename=D:\ORACLE\ORADATA\PUBTEST\CONTROL02.CTL
output filename=D:\ORACLE\ORADATA\PUBTEST\CONTROL03.CTL
Finished restore at 12-AUG-08

RMAN> restore database;

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 12-AUG-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\PUBTEST\EXAMPLE01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\TESTBK\4HJNRFDE_1_1.BAK tag=TAG20080812T101302 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 12-AUG-08

RMAN> recover database;

Starting recover at 12-AUG-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file D:\ORACLE\ORADATA\PU
BTEST\REDO3_01.LOG
archive log thread 1 sequence 11 is already on disk as file D:\ORACLE\ORADATA\PU
BTEST\REDO1_02.LOG
archive log filename=D:\ORACLE\ORADATA\PUBTEST\REDO3_01.LOG thread=1 sequence=10

archive log filename=D:\ORACLE\ORADATA\PUBTEST\REDO1_02.LOG thread=1 sequence=11

media recovery complete
Finished recover at 12-AUG-08

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/12/2008 10:21:12
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> exit;   --需要用restlogs 開啟

Recovery Manager complete.

C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Aug 12 10:21:40 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  101785012 bytes
Fixed Size                   454068 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  101785012 bytes
Fixed Size                   454068 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "PUBTEST" NORESETLOGS  ARCHIVELOG
  2  --  SET STANDBY TO MAXIMIZE PERFORMANCE
  3      MAXLOGFILES 5
  4      MAXLOGMEMBERS 3
  5      MAXDATAFILES 100
  6      MAXINSTANCES 1
  7      MAXLOGHISTORY 226
  8  LOGFILE
  9    GROUP 1 (
 10      'D:\ORACLE\ORADATA\PUBTEST\REDO1_02.LOG',
 11      'D:\ORACLE\ORADATA\PUBTEST\REDO1_01.LOG'
 12    ) SIZE 100M,
 13    GROUP 2 (
 14      'D:\ORACLE\ORADATA\PUBTEST\REDO2_01.LOG',
 15      'D:\ORACLE\ORADATA\PUBTEST\REDO2_02.LOG'
 16    ) SIZE 100M,
 17    GROUP 3 (
 18      'D:\ORACLE\ORADATA\PUBTEST\REDO3_01.LOG',
 19      'D:\ORACLE\ORADATA\PUBTEST\REDO3_02.LOG'
 20    ) SIZE 100M
 21  -- STANDBY LOGFILE
 22  DATAFILE
 23    'D:\ORACLE\ORADATA\PUBTEST\SYSTEM01.DBF',
 24    'D:\ORACLE\ORADATA\PUBTEST\UNDOTBS01.DBF',
 25    'D:\ORACLE\ORADATA\PUBTEST\EXAMPLE01.DBF',
 26    'D:\ORACLE\ORADATA\PUBTEST\INDX01.DBF',
 27    'D:\ORACLE\ORADATA\PUBTEST\TOOLS01.DBF',
 28    'D:\ORACLE\ORADATA\PUBTEST\USERS01.DBF'
 29  CHARACTER SET ZHS16GBK
 30  ;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            d:\archpaul
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12
SQL> select count(*) from test;

  COUNT(*)
----------
     12882

SQL>

可以看到日誌序列不用重置