[oracle]RMAN Backup-Recovery

Erny ♦ 四月 10, 2007 ♦ 3 則迴響

文章來源::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

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;

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

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

Like this:

Be the first to like this post.

Posted in: oracle

3 則迴響

Darpmelerma

十月 2, 2007 – 4:41 上午

Sklep komputerowy z tanimi czesciami komputerowymi:
sklep komputerowy

回覆
weraexpaveNEr

十一月 19, 2007 – 4:13 下午

Follow these guidelines and you will build that new home with little, or no, problems. cedar siding can help…

回覆
htlee

九月 29, 2009 – 9:10 下午

thanks for sharing
my case is difference,
the drive containing oracle817 system crashed. however, the control files and the database files are stored in different drives, so they still can be reached. I have re-install the oracle817 to different drive, the question is: is it possible to recover my original data from the control files and the dbf files?
How? could you show me the general steps?

回覆

發表迴響取消回覆

Enter your comment here...

Fill in your details below or click an icon to log in:

電子郵件 (必) (Address never made public)

名稱 (必)

個人網站

You are commenting using your WordPress.com account. ( Log Out / 變更 )

You are commenting using your Twitter account. ( Log Out / 變更 )

You are commenting using your Facebook account. ( Log Out / 變更 )

取消

連結到 %s

	秋葉原-Blog Search"秋葉原" on 2011 冬 - 再訪東京 (秋葉原 / 和幸豬排)
	Serega Faf on contact ::
	htlee on [oracle]RMAN Backup-Recov…
	Erny on rman backup step
	阿亮 on rman backup step

Blog->Erny