用备份的控制文件恢复后不用resetlogs打开方式的恢复

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:26

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>
可以看到日志序列不用重置

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】