ITPub博客

首页 > 数据库 > Oracle > Oracle rman 各种恢复

Oracle rman 各种恢复

原创 Oracle 作者:j04212 时间:2014-02-13 10:55:11 0 删除 编辑


--恢复整个数据库

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-10-23

  • 博文量
    94
  • 访问量
    249242