Blog->Erny

文章來源::http://www.odba.idv.tw/rman1.htm

RMAN Backup/Recovery 測試範例

這是我backup/recovery的測試，測試環境如下：

DB: Oracle 9.2.0.5

Media Manager: Veritas NetBackup 4.5

Catalog DB: Oracle 9.2.0.5


#########################################################


[Restore spfile]

scenario

@using catalog
@using veritas

rman target / catalog rman_us/rman_us@catdb
startup nomount;
restore spfile from autobackup;
shutdown immediate

會自動到磁帶找出auto backup spfile



#########################################################
[Restore control file]

1.rman target / catalog rman_us/rman_us@catdb
2.startup nomount;
3.restore controlfile from autobackup;
4.alter database mount;
5.recover database;
6.alter database open resetlogs;

#########################################################

[Restore control file no archivelog]

1.rman target / catalog rman_us/rman_us@catdb
2.startup nomount;
3.restore controlfile from autobackup;
4.alter database mount;
5.recover database noredo;
6.alter database open resetlogs;

#########################################################

[Recover NOARCHIVELOG database NO CATALOG DB]

1.set oracle_sid=test
2.rman target /
3.configure controlfile autobackup on;
4.shutdown immediate;
5.startup mount;
6.backup database;
7.shutdown immediate;
RENAME ALL DATABASE FILE AND CONTROLFILE WITH REDO LOG FILES
8.startup nomount;
9.set DBID = 234432434
10.restore controlfile from autobackup;
11.alter database mount;
12.restore database;
13.recover database noredo;
如果online redo log你沒有移除或改檔名，改用recover database;)
14.alter database open resetlogs;
15.shutdown immediate;
16.startup mount;
17.backup database;

#########################################################
[Recover ARCHIVELOG database WITH CATALOG DB]

1.set oracle_sid=test
2.rman target / catalog rman_us/rman_us@catdb
3.configure controlfile autobackup on;
4.backup database plus archivelog delete input;
5.shutdown immediate
6.rename all datafile and control file Do not rename redo logs!
7.start nomount
8.restore controlfile from autobackup;
9.alter database mount;
10.restore database;
11.recover database;
12.alter database open resetlogs;
13.shutdown immediate
14.startup mount;
15.backup database;

#########################################################

[Recover tablespace online in ARCHIVE MODE]

1.open database
2.delete users tablespace's datafile
3.rman target / catalog rman_us/rman_us@catdb
4.sql "alter tablespace users offline immediate";
5.restore tablespace users;
6.recover tablespace users;
7.sql "alter tablespace users online";


[Time-Base Recovery]

16:52:00 drop a table
SQL> drop table zmi.help;

Table dropped.

bash-2.05$ rman target / catalog rman_user/rman_user@catdb

Recovery Manager: Release 9.2.0.5.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ELIFE02 (DBID=1107820195)
connected to recovery catalog database

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;
Oracle instance started
database mounted

Total System Global Area 337086968 bytes

Fixed Size 731640 bytes
Variable Size 318767104 bytes
Database Buffers 16777216 bytes
Redo Buffers 811008 bytes

RMAN> run
2> {
3> set until time "to_date(?/02/04 16:50:00','mm/dd/yy hh24:mi:ss')";
4> restore database;
5> recover database;
6> alter database open resetlogs;
7> }

executing command: SET until clause

Starting restore at 02-SEP-04

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=13 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 4.5GA (00000000)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/elife02/system01.dbf
restoring datafile 00002 to /oradata/elife02/undotbs01.dbf
restoring datafile 00009 to /oradata/elife02/users01.dbf
restoring datafile 00012 to /opt/oracle/9.2.0/dbs/oradataelife02user03.dbf
restoring datafile 00014 to /oradata/elife02/users02.dbf
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=bk_263_1_535825462 tag=HOT_DB_BK_LEVEL0 params=NULL
channel ORA_SBT_TAPE_1: restore complete
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oradata/elife02/cwmlite01.dbf
restoring datafile 00004 to /oradata/elife02/drsys01.dbf
restoring datafile 00007 to /oradata/elife02/odm01.dbf
restoring datafile 00011 to /oradata/elife02/rman_tbs01.dbf
restoring datafile 00015 to /oradata/elife02/xyz.dbf
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=bk_264_1_535825527 tag=HOT_DB_BK_LEVEL0 params=NULL
channel ORA_SBT_TAPE_1: restore complete
Finished restore at 02-SEP-04

Starting recover at 02-SEP-04
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 02-SEP-04

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

SQL> select count(*) from zmi.help;

COUNT(*)
----------
918

可以看到help這個table救回來了。

#########################################################

[SCN-Base Recovery]

SQL>create table test (id number);

SQL>insert into test values (1);
SQL>insert into test values (2);
SQL>insert into test values (3);
SQL>commit;

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
57167890

SQL>delete test where id = 3;
SQL>commit;

SQL> shutdown immediate;

進入RMAN

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 337086968 bytes

Fixed Size 731640 bytes
Variable Size 318767104 bytes
Database Buffers 16777216 bytes
Redo Buffers 811008 bytes

RMAN> restore database until scn 57167890;

Starting restore at 02-SEP-04

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=13 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 4.5GA (00000000)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata/elife02/system01.dbf
restoring datafile 00002 to /oradata/elife02/undotbs01.dbf
restoring datafile 00009 to /oradata/elife02/users01.dbf
restoring datafile 00012 to /opt/oracle/9.2.0/dbs/oradataelife02user03.dbf
restoring datafile 00014 to /oradata/elife02/users02.dbf
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=bk_273_1_535828361 tag=HOT_DB_BK_LEVEL0 params=NULL
channel ORA_SBT_TAPE_1: restore complete
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /oradata/elife02/example01.dbf
restoring datafile 00006 to /oradata/elife02/indx01.dbf
restoring datafile 00008 to /oradata/elife02/tools01.dbf
restoring datafile 00010 to /oradata/elife02/xdb01.dbf
restoring datafile 00013 to /oradata/elife02/oem_repository.dbf
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=bk_274_1_535828826 tag=HOT_DB_BK_LEVEL0 params=NULL
channel ORA_SBT_TAPE_1: restore complete
channel ORA_SBT_TAPE_1: starting datafile backupset restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oradata/elife02/cwmlite01.dbf
restoring datafile 00004 to /oradata/elife02/drsys01.dbf
restoring datafile 00007 to /oradata/elife02/odm01.dbf
restoring datafile 00011 to /oradata/elife02/rman_tbs01.dbf
restoring datafile 00015 to /oradata/elife02/xyz.dbf
channel ORA_SBT_TAPE_1: restored backup piece 1
piece handle=bk_275_1_535828891 tag=HOT_DB_BK_LEVEL0 params=NULL
channel ORA_SBT_TAPE_1: restore complete
Finished restore at 02-SEP-04

RMAN> recover database until scn 57167890;

Starting recover at 02-SEP-04
using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 02-SEP-04

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

回到SQL*PLUS

SQL> select * from test;

ID
----------
1
2
3

我們看到資料救回來了，完成這次的SCN-Base Recovery。

#########################################################

[Recover Corrupted Data Blocks]

如果在查詢資料時出現
ORA-01578: ORACLE data block corrupted (file # 6,block # 23)
ORA-01110: data file 6: '/oradata/test/users01.dbf'

這是告訴你有一個block在users tablespace損壞了，當你查詢到儲存在這block
data時，就會跳出這個錯誤訊息。

使用BMR來復原損毀的block

RMAN>blockrecover datafile 6 block 23;

or

RMAN>blockrecover datafile 6 block 23,46,123;

or

RMAN>blockrecover datafile 6 block 23 datafile 13 block 233;

檢查是否有corruption block

RMAN>backup validate database;
Starting backup at 03-SEP-04
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=14 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: VERITAS NetBackup for Oracle - Release 4.5GA (00000000)
channel ORA_SBT_TAPE_1: starting full datafile backupset
channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/oradata/elife02/users01.dbf
input datafile fno=00012 name=/opt/oracle/9.2.0/dbs/oradataelife02user03.dbf
input datafile fno=00014 name=/oradata/elife02/users02.dbf
input datafile fno=00001 name=/oradata/elife02/system01.dbf
input datafile fno=00002 name=/oradata/elife02/undotbs01.dbf
input datafile fno=00005 name=/oradata/elife02/example01.dbf
input datafile fno=00006 name=/oradata/elife02/indx01.dbf
input datafile fno=00013 name=/oradata/elife02/oem_repository.dbf
input datafile fno=00010 name=/oradata/elife02/xdb01.dbf
input datafile fno=00008 name=/oradata/elife02/tools01.dbf
input datafile fno=00003 name=/oradata/elife02/cwmlite01.dbf
input datafile fno=00004 name=/oradata/elife02/drsys01.dbf
input datafile fno=00007 name=/oradata/elife02/odm01.dbf
input datafile fno=00011 name=/oradata/elife02/rman_tbs01.dbf
input datafile fno=00015 name=/oradata/elife02/xyz.dbf
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:45
Finished backup at 03-SEP-04

查詢 V$DATABASE_BLOCK_CORRUPTION 來確認是否有損壞的BLOCK