ORACLE RMAN 各种场景恢复

比眉伴天荒 2022-04-18 01:16 216阅读 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.