ORACLE 只读数据文件备份与恢复

  1. 云栖社区>
  2. 博客>
  3. 正文

ORACLE 只读数据文件备份与恢复

小麦苗 2015-02-03 16:47:35 浏览673
展开阅读全文

 

 

 

第一章 只读数据文件备份与恢复

BLOG文档结果图:

wpsFFD2.tmp 

 

 

只读数据文件是只读表空间的数据文件,其数据块包括文件头在内不允许更改(少数管理性命令除外)。

将表空间设置为只读状态的命令:

SQL> alter tablespace TBS_READ read only;

 

Tablespace altered.

 

将表空间重新设置为常规的读/写状态的命令:

SQL> alter tablespace TBS_READ read write;

 

Tablespace altered.

 

 

获得只读表空间及其数据文件的sql语句:

SQL> set line 9999

SQL> col file_name format a50

SQL> select t.TABLESPACE_NAME, d.FILE_ID, d.FILE_NAME

  2  from dba_tablespaces t, dba_data_files d

  3   WHERE t.TABLESPACE_NAME = d.TABLESPACE_NAME

  4  and t.STATUS = 'READ ONLY'  ;

 

TABLESPACE_NAME   FILE_ID FILE_NAME

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

TBS_READ 5 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf

 

SQL>

 

 

1  只读表空间的特性

  使用只读表空间避免对静态数据的频繁备份

  当使用alter tablespace tbs read only时,数据文件会执行检查点进程(将所有脏缓冲区的内容写至磁盘),当前的SCN号会被标注,同时存储了SCN的数据文件头部被冻结.控制文件内也会记录该数据文件的冻结信息。

  可以清除只读表空间的对象

 

2  只读文件损坏的后果

当一个表空间从读/写状态更改为只读状态时,其数据文件中的脏数据块必须由DBWn进程悉数写回磁盘,完成一次不完整的完全检查点,该表空间内数据文件即称为只读数据文件,其数据块及文件头信息包括检查点在内从此均不再更新,每次打开数据库实例也不会在乎只读文件头的检查点SCN是否和其他数据文件的活在线日志同步,但各种错误(ORA-01116,ORA-01110,ORA-01578,ORA-01157等等错误)仍然会发生。以下图片为从电子书上截取过来的:

wpsFFF2.tmp 

 

 

不像其他类型的数据文件,在只读文件头损坏后,在发生检查点时,所有进程视其为无物,实例不会崩溃(关键数据文件头损坏的后果),文件也不会自动下线(普通数据文件头损坏的后果),总体上只读文件安然无恙,只是当执行需要访问头部的操作时才在告警日志和追踪文件中留下痕迹而已,比如:

SQL> select checkpoint_change# from v$datafile where file#=5;

 

CHECKPOINT_CHANGE#

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

   1865187

但告警日志报错:

ORA-19563: datafile header validation failed for file /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
ORA-01251: Unknown File Header Version read for file number 5
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf'

 

此刻其内部的所有队形还是可以查询的(只要对应的数据块没有损坏),但是 alter tablspace ... read write 和 alter tablespace ... offline 之后的online回报ora-01210数据文件头损坏错误。

 

3  只读表空间的备份

由于只读数据文件内没有一个数据块能够被修改,所以,一般情况下,只读表空间只需要进行一次备份,尤其是当只读数据文件占用很大空间的时候,这样做可以节省备份数据库的时间。即当表空间状态发生改变时应立即进行备份。可以使用OS系统cp命令来备份或RMAN进行备份只读表空间。备份其他类型数据文件的方式均适用于只读数据文件,比如:

v backup as backupset tablespace TBS_READ;

v backup as copy tablespace TBS_READ;

v backup as backupset datafile 5;

 

  使用RMAN时建议启用备份优化选项,具体保留几份备份由备份保留策略决定:使用冗余度时保留数量为冗余度加1,使用恢复窗口时保留数量为1.

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

以下例子演示备份优化功能,初始状态下是没有任何备份的:

首先启用备份优化:

[oracle@rhel6_lhr dbs]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 3 10:02:05 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: lilove (DBID=888888)

 

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

 

using target database control file instead of recovery catalog

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

设置备份保留策略,使用恢复窗口3天:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

 

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

new RMAN configuration parameters are successfully stored

 

RMAN> show retention policy;

 

RMAN configuration parameters for database with db_unique_name LILOVE are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

 

使用backup database 命令备份数据库内所有的数据文件,注意有tbs_read01.dbf文件:

RMAN> backup database;

 

Starting backup at 2015-02-03 10:03:31

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=264 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/utf8test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/utf8test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/utf8test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/utf8test/users01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/utf8test/tbs_read01.dbf

channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:03:32

 

 

channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:05:18

piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1 tag=TAG20150203T100332 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:05:20

channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:05:21

piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1ppubaau_1_1 tag=TAG20150203T100332 comment=NONE

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

Finished backup at 2015-02-03 10:05:21

 

RMAN>

当第二次执行backup database命令时,输出中是找不到tbs_read01.dbf文件的,rman认为没有必要反复备份只读文件:

RMAN> backup database;

 

Starting backup at 2015-02-03 10:08:48

using channel ORA_DISK_1

skipping datafile 5; already backed up 1 time(s)

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/utf8test/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/utf8test/sysaux01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/utf8test/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/utf8test/users01.dbf

channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:08:48

channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:10:13

piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1qpubahg_1_1 tag=TAG20150203T100848 comment=NONE

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

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:10:15

channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:10:16

piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1rpubak6_1_1 tag=TAG20150203T100848 comment=NONE

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

Finished backup at 2015-02-03 10:10:16

若保留策略是冗余度3,则需要等到第5次执行backup database 时才会不带tbs_read01.dbf 文件。另外,如果备份时使用backup tablespace 或 backup datafile命令显式备份只读数据文件,那么RMAN将忽略优化策略。

备份只读数据文件除了可以使用以上备份其他数据文件的方法之外,还可以无需任何准备工作直接使用操作系统的cp复制命令备份,比如:

[oracle@rhel6_lhr dbs]$ cp /u01/app/oracle/oradata/utf8test/tbs_read01.dbf /tmp/tbs_read01.bak

  另外:只读表空间不支持热备

SQL> alter tablespace tbs_read begin backup;

alter tablespace tbs_read begin backup

*

ERROR at line 1:

ORA-01642: begin backup not needed for read-only tablespace 'TBS_READ'

SQL> 

 

需要特别注意的是,当一个表空间从只读(READ ONLY)状态改变为读/写(READ WRITE)状态后,应该立即备份其数据文件及当时(成为READ WRITE状态后)的控制文件,否则将来若控制文件连同原来的只读数据文件同时损坏,在恢复流程中可能出现“ORA-01152:数据文件不够旧”的错误。

 

4  只读表空间的还原与恢复

其他类型的数据文件恢复过程均包括2个必要步骤:还原(restore)和恢复(recover),对于不可能有更改操作的只读文件来说,重做日志是没有意义的,当然也就没有恢复的必要的,因此,所谓的恢复实际上只有一个步骤:还原。

在mount状态下低可用性恢复策略的步骤如下:

① startup mount

② rman的restore 或switch命令还原数据文件

③ alter database open

  在open状态下高可用性恢复策略的步骤如下:

① alter database datafile xx offline

② rman的restore force 或switch命令还原数据文件。

③ alter database datafile xx online

4.1  还原前的准备

只读数据文件的恢复可以采用低可用性恢复策略和高可用性策略,前者是在数据库mount状态下进行,或者是在数据库open状态下进行。低可用性恢复策略要求参数文件和控制文件必须就位,高可用性策略额外要求数据文件必须就位,若不满足条件必须先进行相应的恢复。

4.2  控制文件无损情况下的恢复

控制文件无损情况下的恢复指:只读数据文件损坏时控制文件没有损坏

 

场景1:只读数据文件tbs_read01.dbf 丢失,数据库无法正常启动,停留在mount状态,错误号“ORA-01157cannot identify/locak data file 5 -see DBWR trace file.

遇到以上情况只要使用rman执行restore(合适备份集)或switch(合适镜像复制)命令还原数据文件,然后打开数据库即可:

RMAN> restore datafile 5;

 

Starting restore at 2015-02-03 11:19:45

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/utf8test/tbs_read01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/1tpubeef_1_1

channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1tpubeef_1_1 tag=TAG20150203T111527

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 2015-02-03 11:19:46

 

RMAN> alter database open;

Database altered.

 

 

场景2:只读数据文件tbs_read01.dbf 在实例运行时丢失,导致数据无法访问,错误如下:

SQL>  select * from aabbcc;

select * from aabbcc

               *

ERROR at line 1:

ORA-01116: error in opening database file 5

ORA-01110: data file 5: '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

该情况可以将数据文件下线后通过restore 或switch命令还原,然后上线:

run{

sql 'alter database datafile 5 offline';

restore datafile 5;

sql 'alter database datafile 5 online';

}

 

 

场景3:运行时只读数据文件tbs_read01.dbf内部数据块损坏,导致数据无法访问,但是文件依然存在,错误:

SQL> select * from tst;

select * from tst

              *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 131)

ORA-01110: data file 5: '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf'

以上情况还原时需要加force关键字,否则不能正确还原数据文件(若在mount状态下执行restore命令则无需force关键字):

run{

sql 'alter database datafile 5 offline';

restore datafile 5 force;

sql 'alter database datafile 5 online';

}

使用镜像复制的switch命令不必使用force关键字:

run{

sql 'alter database datafile 5 offline';

switch datafile 5 to datafilecopy '/tmp/ol_mf_exam.dbf';

sql 'alter database datafile 5 online';

}

 

 

场景4:起先数据文件tbs_read01.dbf备份时是只读的,后来修改表空间为读写,但没有备份,现在该文件损坏了:

run{

sql 'alter database datafile 5 offline';

restore datafile 5;

recover datafile 5;

sql 'alter database datafile 5 online';

}

 

 

场景5:起先数据文件tbs_read01.dbf备份时是读写状态,后来其所在的表空间为只读状态,但没有备份,现在数据文件损坏了:

run{

sql 'alter database datafile 5 offline';

restore datafile 5 force;

recover datafile 5;

sql 'alter database datafile 5 online';

}

 

 

 

4.3  控制文件损坏情况下的恢复

 

所谓控制文件损坏情况下的恢复是指只读数据文件和控制文件一并损坏时的恢复,一般步骤包括恢复控制文件和只读数据文件,步骤如下:

(一)将数据库启动到nomount状态

(二)用restore命令还原控制文件

(三)将数据库启动至mount状态

(四)用restore还原只读数据文件

(五)用recover命令恢复整个数据库

(六)用resetlogs方式打开数据库

 

场景1:只读数据文件tbs_read01.dbf连同控制文件一并损坏和丢失,启动实例只能停留在nomount状态,错误报告:“ORA-00205error in identifying control file,check alert log for more info

假设拥有控制文件的自动备份,则:

run{

restore controlfile from autobackup;

mount database;

restore datafile 5;

recover database;

alter database open resetlogs;

}

 

实验,此实验室首先备份整个数据库,然后修改只读表空间属性,总之就是经过一大堆的修改操作,然后再进行还原:

删除控制文件和只读文件:

[root@rhel6_lhr utf8test]# ll

total 1597788

-rw-r----- 1 oracle asmadmin  10076160 Feb  3 16:09 control01.ctl

-rw-r----- 1 oracle asmadmin  10076160 Feb  3 16:09 control02.ctl

-rw-r----- 1 oracle asmadmin  52429312 Feb  3 09:27 redo01.log

-rw-r----- 1 oracle asmadmin  52429312 Feb  3 15:57 redo02.log

-rw-r----- 1 oracle asmadmin  52429312 Feb  3 16:09 redo03.log

-rw-r----- 1 oracle asmadmin 608182272 Feb  3 16:05 sysaux01.dbf

-rw-r----- 1 oracle asmadmin 775954432 Feb  3 16:05 system01.dbf

-rw-r----- 1 oracle asmadmin  10493952 Feb  3 16:05 tbs_read01.dbf

-rw-r----- 1 oracle asmadmin  20979712 Feb  3 10:27 temp01.dbf

-rw-r----- 1 oracle asmadmin  52436992 Feb  3 16:05 undotbs01.dbf

-rw-r----- 1 oracle asmadmin  10493952 Feb  3 16:05 users01.dbf

[root@rhel6_lhr utf8test]# rm -rf control0*

[root@rhel6_lhr utf8test]# rm tbs_read01.dbf

rm: remove regular file `tbs_read01.dbf'? y

[root@rhel6_lhr utf8test]# ll

total 1567860

-rw-r----- 1 oracle asmadmin  52429312 Feb  3 09:27 redo01.log

-rw-r----- 1 oracle asmadmin  52429312 Feb  3 15:57 redo02.log

-rw-r----- 1 oracle asmadmin  52429312 Feb  3 16:10 redo03.log

-rw-r----- 1 oracle asmadmin 608182272 Feb  3 16:10 sysaux01.dbf

-rw-r----- 1 oracle asmadmin 775954432 Feb  3 16:10 system01.dbf

-rw-r----- 1 oracle asmadmin  20979712 Feb  3 10:27 temp01.dbf

-rw-r----- 1 oracle asmadmin  52436992 Feb  3 16:10 undotbs01.dbf

-rw-r----- 1 oracle asmadmin  10493952 Feb  3 16:05 users01.dbf

 

重启数据库:

 

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  501059584 bytes

Fixed Size     2229744 bytes

Variable Size   348129808 bytes

Database Buffers   142606336 bytes

Redo Buffers     8093696 bytes

ORA-00205: error in identifying control file, check alert log for more info

 

 

进行rman恢复:

[oracle@rhel6_lhr dbs]$ rman target /

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 3 16:11:52 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: LILOVE (not mounted)

 

RMAN> restore controlfile from autobackup;

 

Starting restore at 2015-02-03 16:11:57

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=10 device type=DISK

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 02/03/2015 16:11:57

RMAN-06495: must explicitly specify DBID with SET DBID command

 

RMAN> set dbid 888888

 

executing command: SET DBID

 

RMAN> restore controlfile from autobackup;

 

Starting restore at 2015-02-03 16:12:25

using channel ORA_DISK_1

 

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150203

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150202

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150201

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150131

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150130

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150129

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150128

channel ORA_DISK_1: no AUTOBACKUP in 7 days found

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 02/03/2015 16:12:27

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

 

RMAN> restore controlfile from '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_LIHUARONG.f';

 

Starting restore at 2015-02-03 16:14:40

using channel ORA_DISK_1

 

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/utf8test/control01.ctl

output file name=/u01/app/oracle/oradata/utf8test/control02.ctl

Finished restore at 2015-02-03 16:14:42

 

RMAN> mount database;

 

database mounted

released channel: ORA_DISK_1

 

RMAN> restore datafile 5;

 

Starting restore at 2015-02-03 16:15:06

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=237 device type=DISK

 

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/utf8test/tbs_read01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1

channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1 tag=TAG20150203T100332

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 2015-02-03 16:15:07

 

RMAN> recover database;

 

Starting recover at 2015-02-03 16:15:15

using channel ORA_DISK_1

datafile 5 not processed because file is read-only

 

starting media recovery

 

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/utf8test/redo02.log

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/utf8test/redo03.log

archived log file name=/u01/app/oracle/oradata/utf8test/redo02.log thread=1 sequence=2

archived log file name=/u01/app/oracle/oradata/utf8test/redo03.log thread=1 sequence=3

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 5 was not restored from a sufficiently old backup

ORA-01110: data file 5: '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf'

 

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

Finished recover at 2015-02-03 16:15:17

 

 

注意:上边的恢复过程中提示:datafile 5 not processed because file is read-only,ORA-01152: file 5 was not restored from a sufficiently old backup ,造成此问题的原因是数据文件头和控制文件内信息一致,5号文件没有被列入恢复的范畴,所以有:datafile 5 not processed because file is read-only,但是随着恢复的进行,在重做日志中发现有将5号文件置为读/写状态的redo记录,所以在recover命令结束后抛出错误:ORA-01152: file 5 was not restored from a sufficiently old backup,因此解决办法也很简单就是再次执行recover database

 

经过以上分析,我们再次执行recover database

 

 

RMAN> recover database;

 

Starting recover at 2015-02-03 16:15:46

using channel ORA_DISK_1

applied offline range to datafile 00005

offline range RECID=2 STAMP=870711316

 

starting media recovery

 

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/oradata/utf8test/redo02.log

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/utf8test/redo03.log

archived log file name=/u01/app/oracle/oradata/utf8test/redo02.log thread=1 sequence=2

archived log file name=/u01/app/oracle/oradata/utf8test/redo03.log thread=1 sequence=3

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

Finished recover at 2015-02-03 16:15:47

 

RMAN> alter database open resetlogs;

 

database opened

 

RMAN>

 

5  只读表空间的控制文件trace不同

下面对表空间TBS_READ置为只读后对比前后生成的重建控制文件的脚本   

wps4.tmpwps5.tmp

wps6.tmp 

wps17.tmp 

 

对比两者可以发现,设置只读属性后,脚本中并没有列出只读表空间的数据文件。

因此:

  1.使用create controlfile命令时,datafile中未列出只读表空间的数据文件

  2.成功创建控制文件并打开后,使用alter database rename file命令重命名只读表空间的数据文件

  3.使用alter tablespace readonly_tablespacename online 将只读表空间联机

 

6  只读表空间上的对象可以删除  

  SQL> select file#,name,enabled from v$datafile where file#=6;            

  FILE# NAME                     ENABLED           

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

  6 /u01/app/oracle/oradata/orcl/tbs01.dbf    READ ONLY            

  SQL> select segment_name,segment_type,tablespace_name,owner from dba_segments where 

  2 tablespace_name='TBS1' and segment_name='TB2';                 

  SEGMENT_NAME     SEGMENT_TYPE    TABLESPACE_NAME        OWNER    

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

  TB2         TABLE       TBS1              SCOTT    

  SQL> drop table scott.tb2;                             

  Table dropped.                                   

 

7  小结

? 1. 表空间置为只读后将减少数据的备份量

? 2. 表空间置为只读后,不能对其中的对象执行任何DML操作

? 3. 只读表空间内的对象可以被清除,因为drop命令更新了数据字典,而不更新对象本身

? 4. 当表空间的状态发生变化时,应立即备份该表空间,以减少恢复工作,一旦表空间从只读状态更改为读/写状态,应该立即对其进行备份。

? 5. 对于状态多次发生改变且未及时备份的情况,日志未损坏时,可以使用联机重做、归档日志来进行恢复

  使用下列命令来实现:

  删除受损的数据文件(rm dbfile.dbf)

  重建受损的数据文件(alter database create datafile n)

  进行介质恢复(recover datafile n)

  使受损的数据文件联机(alter database datafile n online)

? 6. 演示多为在mount状态下来恢复,生产环境中多在open状态下恢复,可以按下列步骤实现

  先将受损的只读表空间(数据文件)脱机(offline)

  使用备份的表空间(数据文件)来还原(restore)

  使用归档、联机日志进行介质恢复(recover)

  使恢复成功的表空间(数据文件)联机(online)

? 7. 对于原始介质受损,不能恢复到原始位置的情况下,使用下面的命令实现转移

  alter database rename file '' to '';

 

? 建议启用控制文件自动备份功能,这样在“alter tablespace ...  read only”和“alter tablespace ... read write”后,控制文件可以自动备份。

? 当控制文件和数据文件(只读或曾经处于只读)同时损坏,一个保险的run块应该这样写(假设控制文件自动备份已启用,只读文件为5):

run{

  startup force nomount;

  restore controlfile from autobackup;

  mount database;

  restore datafile 5;

  recover database;

  recover database;

  alter database open resetlogs;

}

 

网友评论

登录后评论
0/500
评论
小麦苗
+ 关注