Oracle rman 各種恢復

--恢復整個資料庫
run {
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}

--恢復表空間users
run {
sql 'alter tablespace users offline';
restore tablespace users;
recover tablespace users;
sql 'alter tablespace data online';
}

--恢復資料檔案
適用所有表空間資料檔案
run{
shutdown immediate;
startup mount;
restore datafile '/u01/app/oracle/oradata/data01.dbf';
recover datafile '/u01/app/oracle/oradata/data01.dbf';
alter database open;
}

非system表空間的資料檔案，也可以直接在open狀態下restore和recover
run {
sql 'alter database datafile 6 offline';
restore datafile 6;
recover datafile 6;
sql 'alter database datafile 6 online';
}

--臨時表空間損壞
重新建立一個臨時表空間
SQL> create temporary tablespace temp1
tempfile '/u01/app/oracle/oradata/test10g/temp101.dbf' size 200M ;

將建好的TEMP1表空間設定為資料庫預設的臨時表空間
SQL> alter database default temporary tablespace temp1;

DROP掉舊的TEMP的表空間　
SQL> drop tablespace temp including contents and datafiles;

--全部控制檔案損壞
run {
shutdown immediate;
startup nomount;
set dbid=1187100180;
restore controlfile from '/u01/backup/20131202_TEST11G_64.bak';
alter database mount;
recover database;
alter database open resetlogs;
}
開啟後對資料庫做一次全備份

--spfile損壞
run {
shutdown immediate;
startup nomount;
set dbid=1176041295;
restore spfile from '/u01/backup/20131202_TEST11G_64.bak';
shutdown immediate;
startup;
}

--非當前日誌成員損壞
SQL> startup 不報錯

SQL> select status,member from v$logfile;
SQL> alter database drop logfile member '/u01/app/oracle/oradata/test11g/redolog3a.log';
SQL> alter database add logfile member '/u01/app/oracle/oradata/test11g/redolog3a.log' to group 1;

--當前日誌成員損壞
SQL> startup 不報錯
SYS@ test11g> alter database drop logfile member '/u01/app/oracle/oradata/test11g/redo01a.log';
alter database drop logfile member '/u01/app/oracle/oradata/test11g/redo01a.log'
*
ERROR at line 1:
ORA-01609: log 1 is the current log for thread 1 - cannot drop members
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log'

SYS@ test11g> alter system switch logfile;
System altered.

SYS@ test11g> alter database drop logfile member '/u01/app/oracle/oradata/test11g/redo01a.log';
Database altered.

SYS@ test11g> alter database add logfile member '/u01/app/oracle/oradata/test11g/redo01a.log' to group 1;
Database altered.

--備用
SQL> alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-00350: log 1 of instance orcl (thread 1) needs to be archived
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log'

失敗的原因是group 1還沒有歸檔，需要加上"unarchived"引數；
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> alter database open;
Database altered.

--非當前日誌組損壞
SQL> startup
Errors in file /u01/app/oracle/diag/rdbms/test11g/test11g/trace/test11g_lgwr_16823.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test11g/redolog3a.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/test11g/redolog3.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

SQL> startup mount;
SQL> alter database clear logfile group 3;
SQL> alter database open;

--當前日誌組損壞（CURRENT）
SYS@ test11g> startup
Errors in file /u01/app/oracle/diag/rdbms/test11g/test11g/trace/test11g_ora_20114.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log'

SYS@ test11g> startup mount
SYS@ test11g>  alter database drop logfile group 1;
 alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance test11g (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test11g/redo01a.log'

SYS@ test11g>  recover database until cancel;
Media recovery complete.
SYS@ test11g>  alter database open resetlogs;
Database altered.

做一次rman全備份

--恢復歸檔日誌
RMAN> run
2> {
3> SET ARCHIVELOG DESTINATION TO '/u02/archive';
4> restore archivelog sequence between 65 and 67;
5> }

--不完全恢復(until scn/time/sequence)
mount下
set until time 'MAR 21 2005 06:00:00'
set until scn 1000
set until sequence 9923

RMAN> startup mount;
RMAN> run
2> {
3>   set until time "to_date('20131129 11:29:05','yyyymmdd hh24:mi:ss')";
4>   restore database;
5>   recover database;
6> }
RMAN> alter database open resetlogs;
做一次rman全備份

或
RMAN> run {
2> restore database from tag=TAG20131209T153042;
3> recover database from tag=TAG20131209T153042;
4> }
做一次rman全備份

如果有rman資料檔案備份和控制檔案備份，但丟失歸檔日誌，recover的時候會報錯
SYS@ test11g> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 2 needs more recovery to be consistent
ORA-01110: data file 2: '/u01/app/oracle/oradata/test11g/sysaux01.dbf'

RMAN> startup nomout;
RMAN> restore controlfile from '/u01/backup/20131209_TEST11G_93.bak';
RMAN> alter database mount;
RMAN> restore database from tag=TAG20131209T153042;

1、如果有部分歸檔
這個時候我們只能使用丟失的歸檔日誌前的日誌進行恢復。
RMAN> recover database until sequence=2 thread=1;
RMAN> alter database open resetlogs;
或
SYS@ test11g> recover database until cancel using backup controlfile;
先 auto 再 cancel

2、如果沒有歸檔
mount狀態下執行：
SYS@ test11g> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SYS@ test11g> startup force;
ORACLE instance started.

Total System Global Area  301322240 bytes
Fixed Size                  1336092 bytes
Variable Size             251661540 bytes
Database Buffers           41943040 bytes
Redo Buffers                6381568 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ test11g> alter database open resetlogs;

Database altered.

SYS@ test11g> alter system reset "_allow_resetlogs_corruption" scope=spfile;

System altered.

SYS@ test11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ test11g> startup
ORACLE instance started.

Total System Global Area  301322240 bytes
Fixed Size                  1336092 bytes
Variable Size             251661540 bytes
Database Buffers           41943040 bytes
Redo Buffers                6381568 bytes
Database mounted.
Database opened.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27633655/viewspace-1080646/，如需轉載，請註明出處，否則將追究法律責任。