rman 基本命令

1. catalog

grant recover_catalog_owner to rman;

rman catalog rman/rman

RMAN>create catalog tablespace ora_bak;

$rman target / catalog rman/rman@rmanbak

RMAN>register database;

RMAN>crosscheck backup;

RMAN>resync catalog;

RMAN>delete obsolete;

RMAN>delete expired backup;

RMAN>delete backupset 234;

2. rman

rman>connect target sys/oracle@stcstest –remote db

rman>connect catalog rman/rman123

rman>register database

3. backup database or tablespace

RMAN>backup format ‘XXXXX’ database;

RMAN>backup tablespace users;

RMAN>backup current controlfile;

RMAN>list backup of tablespace users;

4. backup archivelog

RMAN> list backup of archivelog all;
列出所有archive log

RMAN> list backup of archivelog from logseq 100 until logseq 120;
列出archive log從100到120

RMAN> list backup of archivelog sequence between 100 and 110;
列出archive log從100到120
–說明:between……and只能使用sequence，而不能使用logseq

RMAN> list backup of archivelog from logseq 100;
列出seq大於等於100的archive log

RMAN> list backup of archivelog low logseq 120;
列出seq大於等於120的archive log

RMAN> list backup of archivelog sequence 100;
列出seq為100的archive log
–說明:在對於rman中關於archivelog的操作中logseq與sequence作用相同，但是建議儘量使用sequence

RMAN> list backup of archivelog logseq 85;
列出seq為85的archive log

RMAN> list backup of archivelog until logseq 85;
列出seq小於等於85的archive log

RMAN> list backup of archivelog high logseq 40;
列出seq小於等於40的archive log

RMAN> list backup of archivelog from time ‘sysdate-7′;
列出7天以前的archive log

RMAN> run {
2> set archivelog destination to ‘/opt/oracle/oradata/test/newlog’;
3> restore archivelog low logseq 40;
4> }
從seq為40開始，到/opt/oracle/oradata/test/newlog中
–說明:list backup of archivelog中限定日誌的位置也適合restore archivelog

RMAN> backup archivelog sequence between 100 and 110 format ‘/tmp/text_test.rman’ delete input;
備份seq為100至110的archive log
–說明:list backup of archivelog中限定日誌的位置也適合backup archivelog

RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘SYSDATE-7′;
刪除7天前archive log

RMAN>DELETE ARCHIVELOG low logseq 40;
刪除seq大於等於40的archive log

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
刪除無效archive log
–說明:DELETE ARCHIVELOG中限定日誌的位置也適合restore archivelog

RMAN>backup format ‘XXXX’ archivelog all delete all;

RMAN>list backup of archivelog all;

RMAN>backup archivelog from sequence 1104;

RMAN>restore archivelog

RMAN> RUN

{

SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';

RESTORE ARCHIVELOGfrom sequence 1104;

}

5. 不完全恢復

RMAN>run{

set until scn 89298373;或

Set until time=”to_date(‘2007-12-22 18:00:00’,’yyyy-mm-dd hh24:mi:ss’)”;

Or set until sequence=1551; (歸檔日誌編號)

Restore database;

Recover database;

Alter database open resetlogs;}

RMAN> recover tablespace test2 until time "to_date('2011-10-31 13:23:54','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/ora105/oradata';

RMAN> RUN

{

SET ARCHIVELOG DESTINATION TO '/oracle/temp_restore';

RESTORE ARCHIVELOG ALL;

}

6. restore tablespace until ..

利用全備可以恢復某個tablespace，進行不完全恢復,但是不能在本機上恢復，因為controlfile中記錄的資料檔案的SCN號是最新的。當進行restore tablespace的時候，還是會將datafile恢復到最新的。只能在auxiliary database中進行TSPIRT。

RMAN>startup mount;

RMAN>restore tablespace users until logseq 1234;

RMAN>recover tablespace users;

RMAN>alter database open;

二、RMAN恢復操作
RMAN完全恢復
1、恢復
1）、所有資料檔案被刪除
c:\rman target sys/oracel@test nocatalog
RMAN>startup force mount
RMAN> run {
restore databse ;
recover database;
‘alter database open’ ;
}
2)、資料檔案所在磁碟出現硬體故障
RMAN> run {
startup force mount;
set newname for datafile 1 to ‘c:\demo\system01.dbf’;
set newname for datafile 2 to ‘c:\demo\dundotbs01.dbf’;
set newname for datafile 3 to ‘c:\demo\sysaux01.dbf’;
set newname for datafile 4 to ‘c:\demo\users01.dbf’;
set newname for datafile 5 to ‘c:\demo\example01.dbf’;
set newname for datafile 6 to ‘c:\demo\test0.dbf’;
restore database;
switch datafile all;
recover database;
sql ‘alter database open ‘;
}
2、恢復SYSTEM表空間的資料檔案
1）、SYSTEM表空間的資料檔案被誤刪除
RMAN>run {
startup force mount;
restore datafile 1;
recover datafile 1;
sql ‘alter database open’ ;
}
2)、SYSTEM表空間資料檔案所在的磁碟出現故障
RMAN> run {
startup force mount;
set newname for datafile 1 to ‘c:\demo\system01.dbf’;
restore datafile 1;
switch datafile 1;
recover datafile 1;
sql ‘alter database open’;
}
3、在OPEN狀態下恢復關閉後意外丟失資料檔案
1）、資料檔案被刪除
RMAN>run {
startup force mount;
sql ‘alter database datafile 4 offline’;
sql ‘alter database open ‘;
restore datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;
}
2)、資料檔案所在的磁碟出現損壞
RMAN>run {
startup force mount;
sql ‘alter database datafile 4 offline’;
sql ‘alter database open ‘;
set newname for datafile 4 to ‘c:\demo\user01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;
}
4、在OPEN狀態下恢復開啟時意外丟失的資料檔案
1）、資料檔案被誤刪除
RMAN>run {
sql ‘alter database datafile 4 offline’;
restore datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;
}
2)、資料檔案所在磁碟出現故障
RMAN>run {
sql ‘alter database datafile 4 offline’;
set newname for datafile 4 to ‘c:\demo\user01.dbf’;
restore datafile 4;
switch datafile 4 ;
recover datafile 4;
sql ‘alter database datafile 4 online’;
}
5、在OPEN狀態下恢復未備份的資料檔案（創造新資料檔案後沒有進行過備份）
1）、資料檔案被誤
RMAN>run {
startup force mount;
sql ‘alter database datafile 7 offline ‘;
sql ‘alter database open’;
restore datafile 7;
recover datafile 7;
sql ‘alter database datafile 7 online’;
}
2)、資料檔案所在磁碟出現故障
RMAN>run {
startup force mount;
sql ‘alter database datafile 7 offline ‘;
sql ‘alter database open’;
set newname for datafile 7 to ‘c:\demo\user04.dbf’;
restore datafile 7;
switch datafile 7;
recover datafile 7;
sql ‘alter database datafile 7 online’;
}

6、恢復表空間
1）、表空間被刪除
RMAN>run {
sql ‘alter tablespace users offline for recover’;
restore tablespace users;
recover tablespace users;
sql ‘alter tablespace users online’;
}
2)、表空間的資料檔案所在磁碟出現故障
RMAN>run {
sql ‘alter tablespace users offline for recover’;
set newname for datafile 4 to ‘c:\demo\user01.dbf’;
restore tablespace users;
switch tablespace users;
recover tablespace users;
sql ‘alter tablespace users online’;
}
7、資料塊介質恢復
RMAN>blockrecover device type disk datafile 5 block 20,21,100 ;

RMAN不完全恢復
1、基本時間恢復
c:\set nls_date_format=yyyy-dd-mm hh24:mi:ss
c:\rman target sys/dddddd@train nocatalog
RMAN>run{
startup force mount;
set until time=’2010-09-09 13:00:00′;
restore database;
recover database;
sql ‘alter database open resetlogs’;
}
2、基於SCN恢復
RMAN>run {
startup force mount;
set until scn=123456;
restore database;
recover database;
sql ‘alter database open resetlogs’;
}
3、基於日誌序列號恢復
RMAN>run {
startup force mount;
set until sequence=58;
restore database;
recover database;
sql ‘alter database open resetlogs’;
}
4、基於備份控制檔案恢復
c:\set nls_date_format=yyyy-dd-mm hh24:mi:ss
c:\rman target sys/oracle@test nocatalog
RMAN>startup force nomount;
RMAN>set dbid=1113606269;
RMAN>restore controlfile from autobackup maxseq 6;
RMAN>alter database mount;
RMAN>run {
set until time=’2010-09-5 12:00:08′;
restore database;
recover database;
sql ‘alter database open resetlogs;
}
當執行了上述4種不完全恢復之後，建議刪除早期的所有備份，並重新備份資料庫
RMAN>run {
delete noprompt backup;
delete noprompt copy;
backup database format=’c:\backup\%d_%s.bak’;
sql ‘alter system archive log current’;
}

7. Database不完全恢復

Database的不完全恢復恢復，當使用alter database open resetlogs之後

再次對資料進行不完全恢復，可以進行斷點恢復，即可以利用resetlogs之前的備份資料，再次對資料庫恢復。也就是說resetlogs之前的全備還是可以用

EG:

Rman target / catalog rman/rman@STCSMES

RMAN>backup database;

Shutdown abort;

恢復資料：

Rman target / catalog rman/rman@STCSMES

RMAN>start nomount;

RMAN>restore controlfile;

RMAN>alter database mount;

RMAN>run{

Set untl time="to_date('2011-9-15 13:20:00','yyyy-mm-dd hh24:mi:ss')";

Restore database;

Recover database;

}

恢復之後出現資訊：

unable to find archive log

archive log thread=1 sequence=8

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

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

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

RMAN-03002: failure of recover command at 09/15/2011 13:31:06

RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 2816759

但是之後：

RMAN>alter database open resetlogs; ---沒有報錯，正常啟動

原因：有資料的丟失，因為是利用shutdown abort,日誌並沒有歸檔，對日誌8的資料都丟失了，只能恢復到日誌8之前的資料

GO ON！

對資料庫進行一系列的操作，然後再次模擬故障，OS下刪除一個資料檔案，進行不完全恢復

恢復步驟同上，發現可以恢復到resetlogs後的時間點，但是不能恢復到resetlogs之前的時間點：

RMAN> run {

2> set until time="to_date('2011-9-15 13:20:00','yyyy-mm-dd hh24:mi:ss')";

3> restore database;

4> recover database;

5> }

executing command: SET until clause

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

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

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

RMAN-03002: failure of set command at 09/15/2011 13:44:12

RMAN-06004: error from recovery catalog database: RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

8. Datafile發生損壞現象

如果有rman備份：利用rman恢復資料檔案

RMAN>alter database datafile 119 offline;

RMAN>restore datafile 119;

RMAN>recover datafile 119;

RMAN>sql ‘altler database da tafile 119 online’;

但是這個會導致整個datafile不可用，對業務會造成一定的影響，利用

dbv查詢壞塊資訊

首先：

$dbv file=test2.dbf blocksize=8192 (dbv help=y)

利用exp匯出資料

EXP-00056: ORACLE error 1578 encountered

ORA-01578: ORACLE block corrupted (file # 118, block # 11)

ORA-01110: data file 118: '/ora105/oradata/STCSSMT/test1.dbf'

然後：確定存在壞塊的物件是什麼

Select tablespace_name,segment_name,segment_type,owner from dba_extents where file_id=119 and 11(block_id) between block_id and block_id+blocks-1;

如果壞塊是有資料，

則：SQL>alter system set events ‘10231 trace name context forever,level 10’;--不進行塊一致性的驗證

$exp test1/test1 file=a.dmp tables=t1

正確匯出之後，再將該 drop重新import,並：

SQL> alter system set events ‘10231 trace name context off’;

如果沒有資料：利用rman恢復

如果有rman 的備份，可以針對某個壞塊進行恢復