• 博客访问: 6437258
  • 博文数量: 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-04-25 12:29:49

http://www.itpub.net/thread-977662-1-1.html

 

我的解决方法:

现在的控制文件恢复方法:
-- 1.备份
RMAN> backup full database format 'd:ackupackup_%U';

Starting backup at 25-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:ORACLEORADATAPUBTESTSYSTEM01.DBF
input datafile fno=00004 name=D:ORACLEORADATAPUBTESTTOOLS01.DBF
input datafile fno=00002 name=D:ORACLEORADATAPUBTESTUNDOTBS01.DBF
input datafile fno=00003 name=D:ORACLEORADATAPUBTESTINDX01.DBF
input datafile fno=00005 name=D:ORACLEORADATAPUBTESTUSERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-APR-08
channel ORA_DISK_1: finished piece 1 at 25-APR-08
piece handle=D:BACKUPBACKUP_0AJEMSD4_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 25-APR-08

Starting Control File and SPFILE Autobackup at 25-APR-08
piece handle=D:ORACLEORA92DATABASEC-796553641-20080425-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-08

RMAN> exit

Cocuments and SettingsPaul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 25 11:02:15 2008

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


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

SQL> select count(*) from tmp;

  COUNT(*)
----------
        32

SQL> alter system switch logfile;

System altered.

SQL> insert into tmp select * from tmp;

32 rows created.
SQL> select count(*) from tmp;

  COUNT(*)
----------
        64

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            Dracleora92RDBMS
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

模拟删除所有数据文件  控制文件和最新日志文件都在

Cocuments and SettingsPaul Yi>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     101785252 bytes

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

RMAN> restore database;

Starting restore at 25-APR-08

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
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:ORACLEORADATAPUBTESTSYSTEM01.DBF
restoring datafile 00002 to D:ORACLEORADATAPUBTESTUNDOTBS01.DBF
restoring datafile 00003 to D:ORACLEORADATAPUBTESTINDX01.DBF
restoring datafile 00004 to D:ORACLEORADATAPUBTESTTOOLS01.DBF
restoring datafile 00005 to D:ORACLEORADATAPUBTESTUSERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACKUPBACKUP_0AJEMSD4_1_1 tag=TAG20080425T105740 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 25-APR-08

根据现在的控制文件来恢复:
查询系统表scn值
PHP code:


C
<img src="images/smilies/29.gif" smilieid="205" border="0" alt="" />ocuments and SettingsPaul Yi>sqlplus &quot;/as sysdba&quot;



SQL*PlusRelease 9.2.0.4.0 Production on Fri Apr 25 11:04:50 2008



Copyright 
(c19822002Oracle Corporation.  All rights reserved.





Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 Production

With the Partitioning
OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 
Production



SQL
select checkpoint_change# from v$database;



CHECKPOINT_CHANGE#

------------------

            
436941



SQL
select checkpoint_change# from v$datafile;



CHECKPOINT_CHANGE#

------------------

            
436941

            436941

            436941

            436941

            436941



SQL
select checkpoint_change#,last_change# from v$datafile;



CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

            
436941       436941

            436941       436941

            436941       436941

            436941       436941  

            436941       436941     



SQL
select checkpoint_change# from v$datafile_header;



CHECKPOINT_CHANGE#

------------------

            
436794

            436794

            436794

            436794

            436794



SQL
>

可以看到v$database 的checkpoint_change#与 v$datafile的checkpoint_change#值相同 这是控制文件是最新的
而且是正常关闭 触发全量检查点 更新控制文件和数据文件和数据文件头的scn值 保持一致,但这个时候数据文件是备份来恢复的
所以这时的数据文件头scn比最新控制文件中database scn要旧 三者值不相等
这时 要进行介质恢复+归档日志和在线日志进行前滚


RMAN> recover database;

Starting recover at 25-APR-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file D:ORACLEORA92RDBMS
ARC00001.001
archive log thread 1 sequence 2 is already on disk as file D:ORACLEORA92RDBMS
ARC00002.001
archive log thread 1 sequence 3 is already on disk as file D:ORACLEORA92RDBMS
ARC00003.001
archive log thread 1 sequence 4 is already on disk as file D:ORACLEORA92RDBMS
ARC00004.001
archive log thread 1 sequence 5 is already on disk as file D:ORACLEORA92RDBMS
ARC00005.001
archive log thread 1 sequence 6 is already on disk as file D:ORACLEORA92RDBMS
ARC00006.001
archive log filename=D:ORACLEORA92RDBMSARC00001.001 thread=1 sequence=1
archive log filename=D:ORACLEORA92RDBMSARC00002.001 thread=1 sequence=2
archive log filename=D:ORACLEORA92RDBMSARC00003.001 thread=1 sequence=3
archive log filename=D:ORACLEORA92RDBMSARC00004.001 thread=1 sequence=4
media recovery complete
Finished recover at 25-APR-08

RMAN> alter database open;

database opened

RMAN>

SQL> select count(*) from tmp;

  COUNT(*)
----------
        64
至此完全恢复


备份的控制文件恢复方法
-- 1.备份
RMAN> backup full database format 'd:ackupackup_%U';

Starting backup at 25-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:ORACLEORADATAPUBTESTSYSTEM01.DBF
input datafile fno=00004 name=D:ORACLEORADATAPUBTESTTOOLS01.DBF
input datafile fno=00002 name=D:ORACLEORADATAPUBTESTUNDOTBS01.DBF
input datafile fno=00003 name=D:ORACLEORADATAPUBTESTINDX01.DBF
input datafile fno=00005 name=D:ORACLEORADATAPUBTESTUSERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-APR-08
channel ORA_DISK_1: finished piece 1 at 25-APR-08
piece handle=D:BACKUPBACKUP_0AJEMSD4_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 25-APR-08

Starting Control File and SPFILE Autobackup at 25-APR-08
piece handle=D:ORACLEORA92DATABASEC-796553641-20080425-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-08   --控制文件生成的备份文件名

Cocuments and SettingsPaul Yi>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 25 11:29:55 2008

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


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

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

删除控制文件和数据文件 保留最新的日志文件
Cocuments and SettingsPaul Yi>rman target /

Recovery Manager: Release 9.2.0.4.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     101785252 bytes

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

RMAN> restore controlfile from 'Dracleora92databaseC-796553641-20080425-01
';    --恢复控制文件

Starting restore at 25-APR-08

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:ORACLEORADATAPUBTESTCONTROL01.CTL
output filename=D:ORACLEORADATAPUBTESTCONTROL02.CTL
output filename=D:ORACLEORADATAPUBTESTCONTROL03.CTL
Finished restore at 25-APR-08

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 25-APR-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:ORACLEORADATAPUBTESTSYSTEM01.DBF
restoring datafile 00002 to D:ORACLEORADATAPUBTESTUNDOTBS01.DBF
restoring datafile 00003 to D:ORACLEORADATAPUBTESTINDX01.DBF
restoring datafile 00004 to D:ORACLEORADATAPUBTESTTOOLS01.DBF
restoring datafile 00005 to D:ORACLEORADATAPUBTESTUSERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:BACKUPBACKUP_0AJEMSD4_1_1 tag=TAG20080425T105740 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 25-APR-08

RMAN>


查询系统表SCN 可以看到控制文件是旧的 database scn小 三者值也不相等 要进行实例恢复

PHP code:


C
<img src="images/smilies/29.gif" smilieid="205" border="0" alt="" />ocuments and SettingsPaul Yi>sqlplus &quot;/as sysdba&quot;



SQL*PlusRelease 9.2.0.4.0 Production on Fri Apr 25 11:33:40 2008



Copyright 
(c19822002Oracle Corporation.  All rights reserved.





Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 Production

With the Partitioning
OLAP and Oracle Data Mining options

JServer Release 9.2.0.4.0 
Production



SQL
select checkpoint_change# from v$database;



CHECKPOINT_CHANGE#

------------------

            
436587



SQL
select checkpoint_change#,last_change# from v$datafile;



CHECKPOINT_CHANGE# LAST_CHANGE#

------------------ ------------

            
436794

            436794

            436794

            436794

            436794



SQL
select checkpoint_change# from v$datafile_header;



CHECKPOINT_CHANGE#

------------------

            
436794

            436794

            436794

            436794

            436794



SQL
>

RMAN> recover database;

Starting recover at 25-APR-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file D:ORACLEORADATAPUB
TESTREDO01.LOG
archive log thread 1 sequence 6 is already on disk as file D:ORACLEORADATAPUB
TESTREDO03.LOG
archive log thread 1 sequence 7 is already on disk as file D:ORACLEORADATAPUB
TESTREDO02.LOG
archive log filename=D:ORACLEORA92RDBMSARC00001.001 thread=1 sequence=1
archive log filename=D:ORACLEORA92RDBMSARC00002.001 thread=1 sequence=2
archive log filename=D:ORACLEORA92RDBMSARC00003.001 thread=1 sequence=3
archive log filename=D:ORACLEORA92RDBMSARC00004.001 thread=1 sequence=4
archive log filename=D:ORACLEORADATAPUBTESTREDO01.LOG thread=1 sequence=5
archive log filename=D:ORACLEORADATAPUBTESTREDO03.LOG thread=1 sequence=6
archive log filename=D:ORACLEORADATAPUBTESTREDO02.LOG thread=1 sequence=7
media recovery complete
Finished recover at 25-APR-08

RMAN> alter database open resetlogs;

database opened

RMAN>

            437438

SQL> select count(*) from tmp;

  COUNT(*)
----------
        64

阅读(3204) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册