recover through resetlogs

最新推荐文章于 2021-04-07 13:41:02 发布

fjmingyang

最新推荐文章于 2021-04-07 13:41:02 发布

阅读量1.8k

点赞数

分类专栏： Oracle 文章标签： database archive sql oracle 数据库 system

Oracle 专栏收录该内容

16 篇文章

订阅专栏

本文详细记录了一次使用Oracle RMAN进行数据库恢复的过程。包括从全备份恢复、控制文件故障处理、通过resetlogs恢复数据库等多个步骤，并分享了在特定情况下恢复数据库所需的技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

拷贝CP 的全文

1.时间点A，我做了一个全备份，

[ora9i@database rmanarch]$ rman target / nocatalog

Recovery Manager: Release 9.2.0.1.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 353440004 bytes

Fixed Size 450820 bytes
Variable Size 150994944 bytes
Database Buffers 201326592 bytes
Redo Buffers 667648 bytes

RMAN> run{allocate channel c3 type disk; backup database format '/backup/oracle/rmanarch/ora9i%t%s.rman';}

allocated channel: c3
channel c3: sid=13 devtype=DISK

Starting backup at 20021130 21:48:59
channel c3: starting full datafile backupset
channel c3: specifying datafile(s) in backupset
including current controlfile in backupset
input datafile fno=00001 name=/backup/oracle/oradata/system.dbf
input datafile fno=00002 name=/backup/oracle/oradata/undo1.dbf
input datafile fno=00003 name=/backup/oracle/oradata/data.dbf
channel c3: starting piece 1 at 20021130 21:49:00
channel c3: finished piece 1 at 20021130 21:49:45
piece handle=/backup/oracle/rmanarch/ora9i4793393402.rman comment=NONE
channel c3: backup set complete, elapsed time: 00:00:45
Finished backup at 20021130 21:49:45
released channel: c3

RMAN> exit

Recovery Manager complete.

由于我没有备份的recovery catalog,我单独备份了这个时候的控制文件，通过OS的拷贝命令。

2。时间点B，我做了一些transaction,
21:50:05 SQL> create table tt tablespace data as select * from dba_objects;

Table created.

Elapsed: 00:00:00.51
21:50:19 SQL> select count(*) from tt;

COUNT(*)
----------
5782

Elapsed: 00:00:00.01
21:50:23 SQL> alter system switch logfile;

System altered.

Elapsed: 00:00:00.04
21:50:27 SQL> /

System altered.

Elapsed: 00:00:02.54
21:50:30 SQL> /

System altered.

Elapsed: 00:00:05.13
这个时候磁盘崩溃，数据库崩溃。
21:50:37 SQL> shutdown abort

我restore database,recover database, resetlogs到时间点B之前的B':
[ora9i@database rmanarch]$ rman target / nocatalog

Recovery Manager: Release 9.2.0.1.0 - Production

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

connected to target database: ORACLE9I (DBID=3143519835)
using target database controlfile instead of recovery catalog

RMAN> run{allocate channel c3 type disk;restore database;recover database until time '20021130 21:50:05';}

allocated channel: c3
channel c3: sid=14 devtype=DISK

Starting restore at 20021130 21:54:34

channel c3: starting datafile backupset restore
channel c3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/oracle/oradata/system.dbf
restoring datafile 00002 to /backup/oracle/oradata/undo1.dbf
restoring datafile 00003 to /backup/oracle/oradata/data.dbf
channel c3: restored backup piece 1
piece handle=/backup/oracle/rmanarch/ora9i4793393402.rman tag=TAG20021130T214859 params=NULL
channel c3: restore complete
Finished restore at 20021130 21:55:11

Starting recover at 20021130 21:55:11

starting media recovery

archive log thread 1 sequence 22 is already on disk as file /backup/oracle/product/9.2.0/dbs/arch1_22.dbf
archive log filename=/backup/oracle/product/9.2.0/dbs/arch1_22.dbf thread=1 sequence=22
media recovery complete
Finished recover at 20021130 21:55:12
released channel: c3

RMAN> alter database open resetlogs;

database opened

RMAN> exit

我检查在时间点B的事务：表TT，已经看不到了：
21:53:32 SQL> select status from v$instance;

STATUS
------------------------
OPEN

Elapsed: 00:00:00.04
21:55:41 SQL> select count(*) from tt;
select count(*) from tt
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.06
21:55:47 SQL> create table ttt tablespace data as select * from dba_objects;

Table created.

Elapsed: 00:00:00.44
21:56:05 SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /backup/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
我做了一些事务，比如创建表TTT，
这个时候，我还在做online备份没有完成，或者没有做备份，但是磁盘再次崩溃，丢失了一些数据文件：

我修改initsid.ora文件，把controlfile重新指到A时间点的时候备份的控制文件。
然后我restore database, recover database到B'时间点（就是上次B崩溃的时候，我恢复到的时间点）

RMAN> run{allocate channel c3 type disk;restore database;recover database until time '20021130 21:50:05';}

allocated channel: c3
channel c3: sid=13 devtype=DISK

Starting restore at 20021130 21:57:38

channel c3: starting datafile backupset restore
channel c3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/oracle/oradata/system.dbf
restoring datafile 00002 to /backup/oracle/oradata/undo1.dbf
restoring datafile 00003 to /backup/oracle/oradata/data.dbf
channel c3: restored backup piece 1
piece handle=/backup/oracle/rmanarch/ora9i4793393402.rman tag=TAG20021130T214859 params=NULL
channel c3: restore complete
Finished restore at 20021130 21:58:15

Starting recover at 20021130 21:58:15

starting media recovery

archive log thread 1 sequence 22 is already on disk as file /backup/oracle/product/9.2.0/dbs/arch1_22.dbf
archive log filename=/backup/oracle/product/9.2.0/dbs/arch1_22.dbf thread=1 sequence=22
media recovery complete
Finished recover at 20021130 21:58:16
released channel: c3

RMAN> shutdown

database dismounted
Oracle instance shut down
这个时候，我再次修改initsid.ora文件里面的controlfile,重新指回到C时间点的控制文件，
我企图打开数据库：
[ora9i@database oradata]$ sql

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Nov 30 21:58:58 2002

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

Connected to an idle instance.

21:58:58 SQL> startup
ORACLE instance started.

Total System Global Area 353440004 bytes
Fixed Size 450820 bytes
Variable Size 150994944 bytes
Database Buffers 201326592 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/backup/oracle/oradata/system.dbf'

但是报错。
我恢复数据库：

21:59:17 SQL> recover database;
Media recovery complete.
22:00:42 SQL> alter database open;

Database altered.

OK，数据库打开。我检查我在时间点C的做的事务：

Elapsed: 00:00:01.64

22:00:52 SQL> select count(*) from ttt;

COUNT(*)
----------
5782

Elapsed: 00:00:00.03
我们可以看到，C时间点我创建的表TTT现在还在。

recover through resetlogs 的关键，在于把数据库先恢复到B'的时间点的状态，然后再根据当前的控制文件的状态来进行恢复。

虽然这样还是再某些条件下可以恢复数据库，但是我们一般都建议，再做了resetlogs之后，马上对数据库做一个全备份。因为recover through resetlogs 还是需要一定的条件和技巧的。不是推荐的标准的备份恢复手段。