ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 只读表空间的恢复问题--完全恢复

只读表空间的恢复问题--完全恢复

原创 Oracle 作者：xuqingwei 时间：2010-06-24 21:30:56 0 删除 编辑

2 完全恢复

这里我们直接使用RMAN来对只读表空间进行完全恢复。实现完全恢复的前提是当前系统的控制文件、在线日志文件、归档日志文件均是可用的。

2.1 要恢复的表空间一直处于只读状态

sys@STUDY>alter tablespace users readonly;

Tablespace altered.

sys@STUDY>select btype_key, id1, id2, creation_time from v$backup_controlfile_details;

BTYPE_KEY ID1 ID2 CREATION_TIME

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

83 722539113 88 2008-04-22 16:50:16

然后我们使用RMAN备份users表空间。我们将RMAN配置为CONFIGURE CONTROLFILE AUTOBACKUP ON

RMAN> backup tablespace users;

Starting backup at 2010-06-24 18:44:46

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=00004 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF

channel ORA_DISK_1: starting piece 1 at 2010-06-24 18:44:46

channel ORA_DISK_1: finished piece 1 at 2010-06-24 18:44:47

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T184446_626FRYD6_.BKP tag=TAG20100624T184446 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2010-06-24 18:44:47

Starting Control File and SPFILE Autobackup at 2010-06-24 18:44:47

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYAUTOBACKUP2010_06_24O1_MF_S_722544287_626FS0M1_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 2010-06-24 18:44:51

下面，我们模拟USERS01.dbf数据文件被破坏，需要进行恢复。

sys@STUDY>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@STUDY>host del C:oracleproduct10.2.0oradatastudyusers01.dbf

sys@STUDY>startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1248552 bytes

Variable Size 104858328 bytes

Database Buffers 171966464 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF'

下面，我们使用RMAN来进行完全恢复。

RMAN> restore tablespace users;

Starting restore at 2010-06-24 18:52:34

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF

channel ORA_DISK_1: reading from backup piece C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T184446_626FRYD6_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T184446_626FRYD6_.BKP tag=TAG20100624T184446

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2010-06-24 18:52:36

由于表空间一直处于只读状态，所以不需要使用任何重做信息。我们无需进行recover database就可以将数据库打开。

RMAN> alter database open;

database opened

2.2 要恢复的表空间处于读写状态，但最后一次备份时为只读状态

首先，我们检查当前表空间的状态：

sys@STUDY>select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS READ ONLY

EXAMPLE ONLINE

RMAN ONLINE

PERFSTATS ONLINE

8 rows selected.

然后，我们使用RMAN对Users表空间进行备份。由于设置了Controlfile Autobackup，系统同时将保存该时刻的控制文件。

RMAN> backup tablespace users;

Starting backup at 2010-06-24 19:02:30

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=00004 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF

channel ORA_DISK_1: starting piece 1 at 2010-06-24 19:02:31

channel ORA_DISK_1: finished piece 1 at 2010-06-24 19:02:32

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T190230_626GT77B_.BKP tag=TAG20100624T190230 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2010-06-24 19:02:32

Starting Control File and SPFILE Autobackup at 2010-06-24 19:02:32

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYAUTOBACKUP2010_06_24O1_MF_S_722545352_626GT9JM_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 2010-06-24 19:02:36

下面我们将Users表空间变为read write状态，由上文我们知道，在状态的转换过程中，系统将在状态转换完成后自动备份控制文件。

sys@STUDY>alter tablespace users read write;

Tablespace altered.

sys@STUDY>select btype_key, id1, id2, creation_time from v$backup_controlfile_details;

BTYPE_KEY ID1 ID2 CREATION_TIME

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

83 722539113 88 2008-04-22 16:50:16

85 722544287 90 2008-04-22 16:50:16

87 722545352 92 2008-04-22 16:50:16

88 722545529 93 2008-04-22 16:50:16

我们可以看到第87条记录，722545352号控制文件是RMAN在备份Users表空间的过程中保存的。第88条记录，722545529号控制文件则是我们改变Users表空间状态为read write后，系统自动备份的。

下面，我们在Users表空间中写入一些数据，并进行日志切换，以模拟正常数据库的工作。

sys@STUDY>create table test tablespace users as select * from dba_objects;

Table created.

sys@STUDY>alter system switch logfile;

System altered.

sys@STUDY>/

System altered.

下面，我们模拟USER01.DBF文件被破坏。

sys@STUDY>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@STUDY>host del C:oracleproduct10.2.0oradatastudyusers01.dbf

sys@STUDY>startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1248552 bytes

Variable Size 109052632 bytes

Database Buffers 167772160 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF'

使用RMAN进行恢复

RMAN> restore tablespace users;

Starting restore at 2010-06-24 19:18:31

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF

channel ORA_DISK_1: reading from backup piece C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T190230_626GT77B_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T190230_626GT77B_.BKP tag=TAG20100624T190230

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2010-06-24 19:18:34

这里，我们使用到了先前备份的O1_MF_NNNDF_TAG20100624T190230_626GT77B_.BKP备份集。由于Users表空间在722545529后状态变为read write，所以RMAN还要应用表空间变为read write以来的重做信息进行恢复。

RMAN> recover tablespace users;

Starting recover at 2010-06-24 19:22:39

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 2010-06-24 19:22:42

恢复工作完成后，我们才能将数据库正常打开。

RMAN> alter database open;

database opened

2.3 要恢复的表空间处于只读状态，但最后一次备份时为读写状态

这个实验是完全恢复只读表空间操作中最复杂的一个。

首先，我们还是先检查一下当前Users表空间的状态。

sys@STUDY>select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

RMAN ONLINE

PERFSTATS ONLINE

8 rows selected.

下面，我们使用RMAN对Users表空间进行备份。同样的，RMAN同时备份此刻的控制文件。

RMAN> backup tablespace users;

Starting backup at 2010-06-24 19:34:12

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=00004 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF

channel ORA_DISK_1: starting piece 1 at 2010-06-24 19:34:12

channel ORA_DISK_1: finished piece 1 at 2010-06-24 19:34:13

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T193412_626JONPY_.BKP tag=TAG20100624T193412 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2010-06-24 19:34:13

Starting Control File and SPFILE Autobackup at 2010-06-24 19:34:13

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYAUTOBACKUP2010_06_24O1_MF_S_722547253_626JOPX9_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 2010-06-24 19:34:26

然后，我们将Users表空间置为read only状态。在状态改变后，系统将自动备份控制文件。

sys@STUDY>alter tablespace users read only;

Tablespace altered.

sys@STUDY>select btype_key, id1, id2, creation_time from v$backup_controlfile_details;

BTYPE_KEY ID1 ID2 CREATION_TIME

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

90 722545529 95 2008-04-22 16:50:16

92 722547253 97 2008-04-22 16:50:16

93 722547427 98 2008-04-22 16:50:16

我们可以看到，722547253号控制文件为RMAN自动备份产生的；而722547427号控制文件则是在将Users表空间转换为read only后系统自动备份的。

注意：在日常工作中，将表空间由read write转变为read only后一定要对该表空间进行一次备份。这里，我们先测试如果没有备份，该如何进行恢复。

我们模拟USERS01.DBF文件被破坏的情况：

sys@STUDY>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

sys@STUDY>host del C:oracleproduct10.2.0oradatastudyusers01.dbf

sys@STUDY>startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1248552 bytes

Variable Size 113246936 bytes

Database Buffers 163577856 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF'

我们使用RMAN进行恢复

RMAN> restore tablespace users;

Starting restore at 2010-06-24 19:44:42

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATASTUDYUSERS01.DBF

channel ORA_DISK_1: reading from backup piece C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T193412_626JONPY_.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_24O1_MF_NNNDF_TAG20100624T193412_626JONPY_.BKP tag=TAG20100624T193412

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2010-06-24 19:44:45

这里需要注意的是，由于我们在使用RMAN对Users表空间进行备份时，Users表空间处于read write的状态。在恢复的过程中，RMAN使用当前的控制文件对Users表空间进行恢复，因此restore后，Users表空间处于read only状态。

sys@STUDY>select file#, status, enabled from v$datafile;

FILE# STATUS ENABLED

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

1 SYSTEM READ WRITE

2 ONLINE READ WRITE

3 ONLINE READ WRITE

4 ONLINE READ ONLY

5 ONLINE READ WRITE

7 ONLINE READ WRITE

8 ONLINE READ WRITE

7 rows selected.

我们知道，当表空间处于read only状态时，系统无法写入文件头。因此，RMAN执行recover的操作仅仅应用前半段的重做信息到Users表空间中，将Users由备份点恢复到表空间变为只读状态的时刻。

RMAN> recover tablespace users;

Starting recover at 2010-06-24 19:54:41

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 2010-06-24 19:54:44

RMAN> alter database open;

database opened

[@more@]

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

0
0
上一篇： 只读表空间的恢复问题--预备知识
下一篇： OCM 考察知识点
请登录后发表评论 登录
全部评论
xuqingwei

注册时间：2010-04-19

  • 博文量
    14
  • 访问量
    64291

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593