Oracle 11g RAN恢復-表空間在只讀時做了資料庫的備份

--0. 場景模擬

--0.1 修改表空間mynewts進入read only狀態

sys@TESTDB11>alter tablespace mynewts read only;

Tablespace altered.

--0.2 刪除原有備份,進行全庫非一致性備份

RMAN> backup database;

Starting backup at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

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/4hohbfcm_1_1 tag=TAG20130815T081933 comment=NONE

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

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

piece handle=/pooldisk02/backup04/4iohbfcm_1_1 tag=TAG20130815T081933 comment=NONE

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

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_823508469_90sog6kp_.bkp comment=NONE

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

--0.3 單獨備份控制檔案，將它的備份片的資訊複製出來

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

RMAN> backup current controlfile;

Starting backup at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

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

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

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

piece handle=/pooldisk02/backup03/4kohbfg2_1_1 tag=TAG20130815T082122 comment=NONE

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

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_823508484_90sogo3t_.bkp comment=NONE

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

--0.4 將表改為read/write

sys@TESTDB11>alter tablespace mynewts read write;

Tablespace altered.

--0.5 做些資料的修改

scott@TESTDB11>select * from tab_mynewts;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 DNameC         CHICAGO

scott@TESTDB11>delete from tab_mynewts where deptno = 30;

1 row deleted.

scott@TESTDB11>commit;

Commit complete.

--0.6 關庫，控制檔案和資料檔案丟失（注意控制檔案是2個)

sys@TESTDB11>shutdown abort;

ORACLE instance shut down.

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

[oracle@S1011:/export/home/oracle]$ rm /u01/app/oracle/oradata/TestDB11/control01.ctl

[oracle@S1011:/u01/app/oracle/fast_recovery_area/TestDB11]$ rm /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

--0.7 rman恢復之前先開啟預警日誌

[oracle@S1011:/u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace]$ tail -f alert_TestDB11.log

--1. 嘗試按原來方法用rman解決問題,即只進行一次恢復

RMAN> run {

2> startup nomount;                   --1.1 啟動到nomount狀態

    --從備份還原資料檔案

3> restore controlfile from '/u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823508484_90sogo3t_.bkp';

4> mount database;       --1.2 啟動到mount狀態

5> restore database;     --1.3 還原

6> recover database;     --1.4 恢復

7> sql 'alter database open resetlogs';  --1.5開庫

8> }

Oracle instance started

Total System Global Area     855982080 bytes

Fixed Size                     2230792 bytes

Variable Size                742393336 bytes

Database Buffers             109051904 bytes

Redo Buffers                   2306048 bytes

Starting restore at 15-AUG-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: restoring control file

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

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

output file name=/u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

Finished restore at 15-AUG-13

database mounted

released channel: ORA_DISK_1

Starting restore at 15-AUG-13

Starting implicit crosscheck 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=19 device type=DISK

Crosschecked 4 objects

Finished implicit crosscheck backup at 15-AUG-13

Starting implicit crosscheck copy at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

Finished implicit crosscheck copy at 15-AUG-13

searching for all files in the recovery area

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823462563_90r8mmsm_.bkp

File Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_14/o1_mf_s_823460342_90r6g6dq_.bkp

File Name: /u01/app/oracle/fast_recovery_area/TESTDB11/autobackup/2013_08_15/o1_mf_s_823508484_90sogo3t_.bkp

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 00001 to /oradata/system01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /oradata/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /oradata/fbtbs01.dbf

channel ORA_DISK_1: restoring datafile 00008 to /oradata/users02.dbf

channel ORA_DISK_1: restoring datafile 00009 to /oradata/mynewts01.dbf

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

channel ORA_DISK_2: starting datafile backup set restore

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

channel ORA_DISK_2: restoring datafile 00002 to /oradata/sysaux01.dbf

channel ORA_DISK_2: restoring datafile 00004 to /oradata/users01.dbf

channel ORA_DISK_2: restoring datafile 00005 to /oradata/example01.dbf

channel ORA_DISK_2: restoring datafile 00006 to /oradata/newts01.dbf

channel ORA_DISK_2: reading from backup piece /pooldisk02/backup04/4iohbfcm_1_1

channel ORA_DISK_2: piece handle=/pooldisk02/backup04/4iohbfcm_1_1 tag=TAG20130815T081933

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:02:05

channel ORA_DISK_1: piece handle=/pooldisk02/backup03/4hohbfcm_1_1 tag=TAG20130815T081933

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:25

Finished restore at 15-AUG-13

Starting recover at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

datafile 9 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/TestDB11/redo01.log

archived log file name=/u01/app/oracle/oradata/TestDB11/redo01.log thread=1 sequence=1

Oracle Error:

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

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

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

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

Finished recover at 15-AUG-13

sql statement: alter database open resetlogs

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

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

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

RMAN-03009: failure of sql command on default channel at 08/15/2013 09:07:00

RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs

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

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

--2. 出錯,此時檢視資料檔案中的檢查點的資訊,發現只有mynewts01的比較老

idle>col name for a50

idle>select name, checkpoint_change# from v$datafile_header;

NAME                                               CHECKPOINT_CHANGE#

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

/oradata/system01.dbf                                         2817855

/oradata/sysaux01.dbf                                         2817855

/oradata/undotbs01.dbf                                        2817855

/oradata/users01.dbf                                          2817855

/oradata/example01.dbf                                        2817855

/oradata/newts01.dbf                                          2817855

/oradata/fbtbs01.dbf                                          2817855

/oradata/users02.dbf                                          2817855

/oradata/mynewts01.dbf                                        2817092

9 rows selected.

--3. 再次進行恢復

RMAN> run {

2> recover database;

3> sql 'alter database open resetlogs';

4> }

Starting recover at 15-AUG-13

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/oradata/TestDB11/redo01.log

archived log file name=/u01/app/oracle/oradata/TestDB11/redo01.log thread=1 sequence=1

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

Finished recover at 15-AUG-13

sql statement: alter database open resetlogs

--4. 驗證資料

scott@TESTDB11>select * from tab_mynewts;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

--5. 刪除之前的備份，重新進行備份