read only打开不完全恢复的数据库的限制...

最新推荐文章于 2025-05-21 00:02:30 发布

转载最新推荐文章于 2025-05-21 00:02:30 发布 · 381 阅读

文章标签：

有人说不完全恢复后无法使用read only打开数据库查看恢复结果，必须使用resetlog打开。如果使用resetlog打开，redo会被重置，以前的备份也会失效，也就无法继续恢复了。
其实，不完全恢复是可以用read only打开的，只不过有点限制.

下面来做个实验:
1.建立表mydb.rotest
2.drop mydb.rotest

那么假如我想恢复rotest，就需要做不完全恢复到scn599679。（当然不完全恢复的方法有许多...这里讨论一下用全库的不完全恢复）Z:\>sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 26 09:27:54 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS AS SYSDBA> conn mydb
Enter password:
Connected.
MYDB > select current_scn from v$database;

CURRENT_SCN
-----------
   599655

Elapsed: 00:00:00.09
MYDB > create table rotest as select * from v$version;

Table created.

Elapsed: 00:00:00.65
MYDB > select current_scn from v$database;

CURRENT_SCN
-----------
   599679

Elapsed: 00:00:00.04
MYDB > drop table rotest;

Table dropped.

Elapsed: 00:00:01.51
MYDB > select current_scn from v$database;

CURRENT_SCN
-----------
   599698

Elapsed: 00:00:00.01
SYS AS SYSDBA> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS AS SYSDBA> startup mount
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                1296260 bytes
Variable Size             92276860 bytes
Database Buffers       180355072 bytes
Redo Buffers             2895872 bytes
Database mounted.
SYS AS SYSDBA>
SYS AS SYSDBA> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

方法一:直接不完全恢复Z:\>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 26 09:35:36 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=1977886605, not open)

RMAN> list backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size    Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1    26.92M    DISK       00:00:02    26-FEB-09
      BP Key: 1 Status: AVAILABLE  Compressed: NO  Tag: TAG20090226T092546
      Piece Name: D:\ORACLE\ORA102\DATABASE\01K8ANKR_1_1

  List of Archived Logs in backup set 1
  Thrd Seq    Low SCN Low Time  Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
  1 9    138764    25-FEB-09 599586    26-FEB-09

BS Key  Type LV Size    Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2    Full 249.92M DISK       00:00:28    26-FEB-09
      BP Key: 2 Status: AVAILABLE  Compressed: NO  Tag: TAG20090226T092551
      Piece Name: D:\ORACLE\ORA102\DATABASE\02K8ANKV_1_1
  List of Datafiles in backup set 2
  File LV Type Ckp SCN Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    Full 599594    26-FEB-09 D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
  2    Full 599594    26-FEB-09 D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
  3    Full 599594    26-FEB-09 D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF
  4    Full 599594    26-FEB-09 D:\ORACLE\ORADATA\TEST\USERS01.DBF

BS Key  Type LV Size    Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3    Full 6.80M    DISK       00:00:02    26-FEB-09
      BP Key: 3 Status: AVAILABLE  Compressed: NO  Tag: TAG20090226T092551
      Piece Name: D:\ORACLE\ORA102\DATABASE\03K8ANM3_1_1
  Control File Included: Ckp SCN: 599605    Ckp time: 26-FEB-09
  SPFILE Included: Modification time: 26-FEB-09

BS Key  Size    Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4    4.00K    DISK       00:00:02    26-FEB-09
      BP Key: 4 Status: AVAILABLE  Compressed: NO  Tag: TAG20090226T092630
      Piece Name: D:\ORACLE\ORA102\DATABASE\04K8ANM6_1_1

  List of Archived Logs in backup set 4
  Thrd Seq    Low SCN Low Time  Next SCN Next Time
  ---- ------- ---------- --------- ---------- ---------
  1 10    599586    26-FEB-09 599610    26-FEB-09

RMAN> run {
2> set until scn 599679;
3> restore database;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 26-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\ORA102\DATABASE\02K8ANKV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORA102\DATABASE\02K8ANKV_1_1 tag=TAG20090226T092551
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 26-FEB-09

Starting recover at 26-FEB-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 26-FEB-09

RMAN> exit

Recovery Manager complete.

Z:\>sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 26 09:37:25 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS AS SYSDBA> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

Elapsed: 00:00:00.18

这时候发现无法readonly打开数据库，为什么呢？
SYS AS SYSDBA> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
         599999    599999
         599999    599999
         599999    599999
         599999    599999

Elapsed: 00:00:00.01
SYS AS SYSDBA> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
         599685
         599685
         599685
         599685

Elapsed: 00:00:00.14
SYS AS SYSDBA> select checkpoint_change# ,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
         599999          0

Elapsed: 00:00:00.00

可以看到controlfile中记录的数据文件checkpoint_change#和数据文件头的checkpoint_change#无法对应，这是因为使用的是原controlfile,而数据文件是通过备份恢复出来的，当你做read only open的时候，怀疑oracle为了冻结数据文件的scn会对比这两个值，当他们不相同的时候,oracle会认为这个不不一致的数据库，所以无法read only opn.

既然是因为这个，我们重新作恢复，只不过这次用备份的controlfile.
SYS AS SYSDBA> startup force nomount
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                1296260 bytes
Variable Size             92276860 bytes
Database Buffers       180355072 bytes
Redo Buffers             2895872 bytes
SYS AS SYSDBA> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Z:\>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 26 09:51:00 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (not mounted)

RMAN> run {
2> restore controlfile from 'D:\ORACLE\ORA102\DATABASE\03K8ANM3_1_1';
3> mount database;
4> set until scn 599679;
5> restore database;
6> recover database;
7> }

Starting restore at 26-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
output filename=D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
output filename=D:\ORACLE\ORADATA\TEST\CONTROL03.CTL
Finished restore at 26-FEB-09

database mounted
released channel: ORA_DISK_1

executing command: SET until clause

Starting restore at 26-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 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:\ORACLE\ORADATA\TEST\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\TEST\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\ORA102\DATABASE\02K8ANKV_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORA102\DATABASE\02K8ANKV_1_1 tag=TAG20090226T092551
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 26-FEB-09

Starting recover at 26-FEB-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 10 is already on disk as file D:\ORACLE\ORADATA\TEST\REDO01.LOG
archive log thread 1 sequence 11 is already on disk as file D:\ORACLE\ORADATA\TEST\REDO02.LOG
archive log filename=D:\ORACLE\ORADATA\TEST\REDO01.LOG thread=1 sequence=10
archive log filename=D:\ORACLE\ORADATA\TEST\REDO02.LOG thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 26-FEB-09

RMAN> exit

Recovery Manager complete.

Z:\>sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 26 09:52:47 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS AS SYSDBA> select checkpoint_change#,last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
         599685
         599685
         599685
         599685

Elapsed: 00:00:00.04
SYS AS SYSDBA> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
         599685
         599685
         599685
         599685

Elapsed: 00:00:00.07
SYS AS SYSDBA> select checkpoint_change# ,current_scn from v$database;

CHECKPOINT_CHANGE# CURRENT_SCN
------------------ -----------
         599586          0

Elapsed: 00:00:00.01
SYS AS SYSDBA> alter database open read only;

Database altered.

Elapsed: 00:00:02.32

由于控制文件来自于备份，在做recover的时候，控制文件也一起被recover,所以两个地方存储的checkpoint_change#就一致了，也就可以read only打开了，这个时候，也就可以读取mydb.rotest了
SYS AS SYSDBA> select * from mydb.rotest;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0    Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Elapsed: 00:00:00.09
SYS AS SYSDBA> startup force mount;
ORACLE instance started.

Total System Global Area  276824064 bytes
Fixed Size                1296260 bytes
Variable Size             92276860 bytes
Database Buffers       180355072 bytes
Redo Buffers             2895872 bytes
Database mounted.
SYS AS SYSDBA> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

下面可以把mydb.rotest exp出来，然后继续作恢复，到结束，这样数据库，就恢复到了最新状态，不会丢数据(虽然必须用resetlogs打开)
Z:\>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Feb 26 09:56:06 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=1977886605, not open)

RMAN> recover database;

Starting recover at 26-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 11 is already on disk as file D:\ORACLE\ORADATA\TEST\REDO02.LOG
archive log filename=D:\ORACLE\ORADATA\TEST\REDO02.LOG thread=1 sequence=11
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-FEB-09

RMAN> exit

Recovery Manager complete.

Z:\>sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 26 09:56:28 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS AS SYSDBA> alter database open read only;

Database altered.

Elapsed: 00:00:02.12
SYS AS SYSDBA> select * from mydb.rotest;
select * from mydb.rotest
               *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.03
SYS AS SYSDBA>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14876437/viewspace-557892/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14876437/viewspace-557892/