Oracle 11g RMAN恢復-只讀表空間的恢復

--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