rman基本命令

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;
sql ‘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: ORACLE 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 data 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
正确导出之后，再将该table drop重新import,并：
SQL> alter system set events ‘10231 trace name context off’;
如果没有数据：利用rman恢复
如果有rman 的备份，可以针对某个坏块进行恢复

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14385647/viewspace-748476/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14385647/viewspace-748476/