Oracle rman 各種恢複

標籤：add   歸檔   com   members   spfile   建立   auto   lin   read   

--恢複整個資料庫
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 不報錯
[email protected] 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‘

[email protected] test11g> alter system switch logfile;
System altered.

[email protected] test11g> alter database drop logfile member ‘/u01/app/oracle/oradata/test11g/redo01a.log‘;
Database altered.

[email protected] 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）
[email protected] 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‘

[email protected] test11g> startup mount
[email protected] 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‘

[email protected] test11g>  recover database until cancel;
Media recovery complete.
[email protected] 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的時候會報錯
[email protected] 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;
或
[email protected] test11g> recover database until cancel using backup controlfile;
先 auto 再 cancel

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

System altered.

[email protected] 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

[email protected] test11g> alter database open resetlogs;

Database altered.

[email protected] test11g> alter system reset "_allow_resetlogs_corruption" scope=spfile;

System altered.

[email protected] test11g> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[email protected] 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.

Oracle rman 各種恢複