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

首页 > 数据库 > Oracle > Oracle 11g RMAN恢复-只读表空间的恢复

Oracle 11g RMAN恢复-只读表空间的恢复

原创 Oracle 作者：LuiseDalian 时间：2014-05-07 07:57:46 0 删除 编辑

--0. 场景模拟

--0.1 查看表空间，创建一个表，并将其中一个表空间置为只读

sys@TESTDB11>select tablespace_name, file_name from dba_data_files;

 

TABLESPACE_NAME                FILE_NAME

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

EXAMPLE                        /oradata/example01.dbf

USERS                          /oradata/users01.dbf

UNDOTBS1                       /oradata/undotbs01.dbf

SYSAUX                         /oradata/sysaux01.dbf

SYSTEM                         /oradata/system01.dbf

NEWTS                          /oradata/newts01.dbf

FBTBS                          /oradata/fbtbs01.dbf

MYNEWTS                        /oradata/mynewts01.dbf

USERS                          /oradata/users02.dbf

 

9 rows selected.

--0.2 在选择的表空间下创建一个表

scott@TESTDB11>create table tab_mynewts tablespace mynewts as select * from dept;

 

Table created.

--0.3 查看数据

scott@TESTDB11>select * from tab_mynewts;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

       

--0.4 将表空间改为只读

sys@TESTDB11>alter tablespace mynewts read only;

 

Tablespace altered.

 

--题外话，开始

--打开备份优化,开一下并行

RMAN> show all;

 

RMAN configuration parameters for database with db_unique_name TESTDB11 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   '/pooldisk02/backup03/%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   '/pooldisk02/backup04/%U';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_TestDB11.f'; # default

--打开优化

RMAN> CONFIGURE BACKUP OPTIMIZATION on;

 

new RMAN configuration parameters:

CONFIGURE BACKUP OPTIMIZATION ON;

new RMAN configuration parameters are successfully stored

--配置并行

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

 

old RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

released channel: ORA_DISK_1

--备份数据库

RMAN> backup database;

 

Starting backup at 15-AUG-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=44 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=/oradata/system01.dbf

input datafile file number=00003 name=/oradata/undotbs01.dbf

input datafile file number=00008 name=/oradata/users02.dbf

input datafile file number=00009 name=/oradata/mynewts01.dbf

input datafile file number=00007 name=/oradata/fbtbs01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-13

channel ORA_DISK_2: starting full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00002 name=/oradata/sysaux01.dbf

input datafile file number=00005 name=/oradata/example01.dbf

input datafile file number=00006 name=/oradata/newts01.dbf

input datafile file number=00004 name=/oradata/users01.dbf

channel ORA_DISK_2: starting piece 1 at 15-AUG-13

channel ORA_DISK_1: finished piece 1 at 15-AUG-13

piece handle=/pooldisk02/backup03/3vohba2h_1_1 tag=TAG20130815T064849 comment=NONE

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

channel ORA_DISK_2: finished piece 1 at 15-AUG-13

piece handle=/pooldisk02/backup04/40ohba2h_1_1 tag=TAG20130815T064849 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:01:27

Finished backup at 15-AUG-13

 

Starting Control File and SPFILE Autobackup at 15-AUG-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823503016_90sj3sph_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 15-AUG-13

 

RMAN> report schema;

 

Report of database schema for database with db_unique_name TESTDB11

 

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

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

1    750      SYSTEM               ***     /oradata/system01.dbf

2    740      SYSAUX               ***     /oradata/sysaux01.dbf

3    290      UNDOTBS1             ***     /oradata/undotbs01.dbf

4    6        USERS                ***     /oradata/users01.dbf

5    345      EXAMPLE              ***     /oradata/example01.dbf

6    20       NEWTS                ***     /oradata/newts01.dbf

7    200      FBTBS                ***     /oradata/fbtbs01.dbf

8    50       USERS                ***     /oradata/users02.dbf

9    20       MYNEWTS              ***     /oradata/mynewts01.dbf

 

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

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

1    500      TEMP                 500         /oradata/temp01.dbf

--查看当前的保留策略为冗余度为1

RMAN> show retention policy;

 

RMAN configuration parameters for database with db_unique_name TESTDB11 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

 

--因为目前已经有2个备份了，满足冗余度1，所以再做备份,就会忽略对只读表空间和脱机数据文件的备份

RMAN> backup database;

 

Starting backup at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

skipping datafile 9; already backed up 2 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=00002 name=/oradata/sysaux01.dbf

input datafile file number=00005 name=/oradata/example01.dbf

input datafile file number=00006 name=/oradata/newts01.dbf

input datafile file number=00007 name=/oradata/fbtbs01.dbf

channel ORA_DISK_1: starting piece 1 at 15-AUG-13

channel ORA_DISK_2: starting full datafile backup set

channel ORA_DISK_2: specifying datafile(s) in backup set

input datafile file number=00001 name=/oradata/system01.dbf

input datafile file number=00003 name=/oradata/undotbs01.dbf

input datafile file number=00008 name=/oradata/users02.dbf

input datafile file number=00004 name=/oradata/users01.dbf

channel ORA_DISK_2: starting piece 1 at 15-AUG-13

channel ORA_DISK_1: finished piece 1 at 15-AUG-13

piece handle=/pooldisk02/backup03/45ohbalv_1_1 tag=TAG20130815T065911 comment=NONE

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

channel ORA_DISK_2: finished piece 1 at 15-AUG-13

piece handle=/pooldisk02/backup04/46ohbalv_1_1 tag=TAG20130815T065911 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:02:05

Finished backup at 15-AUG-13

 

Starting Control File and SPFILE Autobackup at 15-AUG-13

piece handle=/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823503676_90sjrff6_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 15-AUG-13

 

--删除所有的备份,重新做全库非一致备份，清理一下空间

RMAN> delete backup;

RMAN> backup database;

 

--题外话结束

 

--0.5 只读表空间下的数据文件损坏

[oracle@S1011:/export/home/oracle]$ rm /oradata/mynewts01.dbf

 

--0.6 查询数据,发现文件丢失

sys@TESTDB11>alter system flush buffer_cache;

 

System altered.

 

scott@TESTDB11>select * from tab_mynewts;

select * from tab_mynewts

*

ERROR at line 1:

ORA-01116: error in opening database file 9

ORA-01110: data file 9: '/oradata/mynewts01.dbf'

ORA-27041: unable to open file

Solaris-AMD64 Error: 2: No such file or directory

Additional information: 3

 

--1. 使用rman解决问题

RMAN> run {

2> sql 'alter database datafile 9 offline';

3> restore datafile 9;

4> sql 'alter database datafile 9 online';

5> }

 

sql statement: alter database datafile 9 offline

 

Starting restore at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

 

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 00009 to /oradata/mynewts01.dbf

channel ORA_DISK_1: reading from backup piece /pooldisk02/backup03/4cohbbvc_1_1

channel ORA_DISK_1: piece handle=/pooldisk02/backup03/4cohbbvc_1_1 tag=TAG20130815T072116

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 15-AUG-13

 

sql statement: alter database datafile 9 online

 

--2. 验证数据

scott@TESTDB11>select * from tab_mynewts;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

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

0
0
上一篇： Oracle 11g RMAN恢复-用户误删除表空间
下一篇： Oracle 11g RMAN恢复-只读表空间的恢复（备份是在表空间只读状态下做的)
请登录后发表评论 登录
全部评论
LuiseDalian

注册时间：2012-02-06

  • 博文量
    1986
  • 访问量
    6040882

最新文章

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

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

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

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