• 博客访问: 6437251
  • 博文数量: 902
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-11 14:32
  • 认证徽章:
个人简介

oracle 10g ocm oracle 10g/11g/12c ocp aix 6.1 administrator,ogg expert,ITSS 技术交流群 201703254 微信公众号 paulyibin 探讨技术,开心工作 电话 13719354869 ,深入研究数据库和开始研究big data

文章分类

全部博文(902)

文章存档

2016年(23)

2015年(32)

2014年(41)

2013年(29)

2012年(25)

2011年(83)

2010年(220)

2009年(136)

2008年(248)

2007年(65)

分类: Linux操作系统

2008-08-12 10:32:49

在一般恢复备份的控制文件,打开数据库都要用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>

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

阅读(5784) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~

Fung9202010-08-22 13:30:20

这样的话,之前的备份是否还有用?

评论热议
请登录后评论。

登录 注册