Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【RMAN】某数据库RMAN异机恢复演练,(RAC异地成单实例)

01 Feb 2019 » RMAN

1. 异地恢复数据库的先决条件

  1. 保证有某天的全备以及后续的归档日志备份集,并测试其备份的可用性(本操作已经把备份集文件放置在目录/arp/backup/wangzhan44/下)
  2. 保证Oracle的版本与需要恢复数据库的版本一致(10.2.0.4.0版本)

有上述的先决条件,才能保证数据库能正确的恢复。

2. 异地恢复数据库的步骤

2.1 恢复数据库dbrac实例的spfile文件。

  • 创建DBRAC实例

    set oracle_sid = DBRAC  
    
  • 创建DBRAC的spfile(相关的pfile文件写到附录1中)

    create spfile from pfile='/home/oraarp/file.ora';  
    
  • 启动数据库至nomount状态

    startup nomount
    

2.2 恢复数据库dbrac实例的控制文件

  • 创建审计目录(如有,可不建)

    cd /arp/oraarp/admin/dbrac/                                             
    mkdir -p adump bdump cdump udump 
    
  • 创建数据目录和recovery_area目录(如有,可不建)

    mkdir -p /arp/oraarp/oradata/dbrac/                                      
    mkdir -p /arp/oraarp/flash_recovery_area 
    
  • 恢复数据库dbrac的控制文件

    rman target /                                                          
    RMAN>set dbid=715992163                                              
    RMAN>RESTORE CONTROLFILE FROM '/arp/backup/wangzhan44/ctl_t818731703_s1634_20130622_p1';   
    
  • 启动数据库至mount状态

    RMAN>alter database mount 
    

2.3 注册备份文件到rman

  • 注册rman目录

    RMAN> catalog start with '/arp/backup/wangzhan44';
    RMAN>CROSSCHECK BACKUP;  //确认备份是否存在,将不存在的记录标记过期
    RMAN>DELETE EXPIRED BACKUP; //删除标记为expired的记录
    RMAN>list backup ; 列出存在的备份,即我们刚添加的记录。
    

2.4 查看原来的数据文件(为新数据库的数据库文件指定新位置)

  • 查看原来的数据库文件

    select file#,name from v$datafile;
      
             1 +ORCL_DATA1/dbrac/datafile/system.259.754926001
             2 +ORCL_DATA1/dbrac/datafile/undotbs1.260.754926011
             3 +ORCL_DATA1/dbrac/datafile/sysaux.261.754926015
             4 +ORCL_DATA1/dbrac/datafile/undotbs2.263.754926023
             5 +ORCL_DATA1/dbrac/datafile/users.264.754926027
             6 +ORCL_DATA1/dbrac/datafile/trswcm_infogate.ora
             7 +ORCL_DATA1/dbrac/datafile/trswcm_poll.ora
             8 +ORCL_DATA1/dbrac/datafile/trswcm_comment.ora
             9 +ORCL_DATA1/dbrac/datafile/trsvideo.ora
            10 +ORCL_DATA1/dbrac/datafile/trswcmv6.ora
            11 +ORCL_DATA1/dbrac/datafile/e_trswcmv6.ora
            12 +ORCL_DATA1/dbrac/datafile/wcmlog2010.ora
            13 +ORCL_DATA1/dbrac/datafile/trswcmv65_mobile.ora
            14 /var/web/oracle/product/10.2.0/db_1/dbs/TRSMASSUB.ora
            15 /var/web/oracle/product/10.2.0/db_1/dbs/TRSWCMPLUGINS65SUB.ora
            16 +ORCL_DATA1/dbrac/datafile/trswcmv65_sub.ora
            17 +ORCL_DATA1/dbrac/datafile/trsmas_sub.ora
            18 +ORCL_DATA1/dbrac/datafile/trswcmv65plugins_sub.ora
    

2.5 恢复数据文件

  • 恢复数据文件

    run{
    set newname for datafile  1 to '/arp/oraarp/oradata/dbrac/system.ora';
    set newname for datafile  2 to '/arp/oraarp/oradata/dbrac/undotbs1.dbf';
    set newname for datafile  3 to '/arp/oraarp/oradata/dbrac/sysaux.dbf';
    set newname for datafile  4 to '/arp/oraarp/oradata/dbrac/undotbs2.dbf';
    set newname for datafile  5 to '/arp/oraarp/oradata/dbrac/users.dbf';
    set newname for datafile  6 to '/arp/oraarp/oradata/dbrac/trswcm_infogate.dbf';
    set newname for datafile  7 to '/arp/oraarp/oradata/dbrac/trswcm_poll.dbf';
    set newname for datafile  8 to '/arp/oraarp/oradata/dbrac/trswcm_comment.dbf';
    set newname for datafile  9 to '/arp/oraarp/oradata/dbrac/trsvideo.dbf';
    set newname for datafile 10 to '/arp/oraarp/oradata/dbrac/trswcmv6.dbf';
    set newname for datafile 11 to '/arp/oraarp/oradata/dbrac/e_trswcmv6.dbf';
    set newname for datafile 12 to '/arp/oraarp/oradata/dbrac/wcmlog2010.dbf';
    set newname for datafile 13 to '/arp/oraarp/oradata/dbrac/trswcmv65_mobile.dbf';
    set newname for datafile 14 to '/arp/oraarp/oradata/dbrac/TRSMASSUB.dbf';
    set newname for datafile 15 to '/arp/oraarp/oradata/dbrac/TRSWCMPLUGINS65SUB.dbf';
    set newname for datafile 16 to '/arp/oraarp/oradata/dbrac/trswcmv65_sub.dbf';
    set newname for datafile 17 to '/arp/oraarp/oradata/dbrac/trsmas_sub.dbf';
    set newname for datafile 18 to '/arp/oraarp/oradata/dbrac/trswcmv65plugins_sub.dbf';
    restore database;
    switch datafile all; 
    switch tempfile all;
    recover database;
    }
    

2.6 重写控制文件,来生成所需的相应文件

  • 转存目前数据库控制文件,根据该文件重新创建控制文件

    SQL>alter database backup controlfile to trace as '/home/oraarp/controlfile.sql'
    
  • 关闭数据库启动到nomount阶段,重写控制文件

    SQL>SHUTDOWN IMMEDIATE
    SQL>STARTUP NOMOUNT
      
      
    SQL>CREATE CONTROLFILE REUSE DATABASE "DBRAC" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 192
        MAXLOGMEMBERS 3
        MAXDATAFILES 1024
        MAXINSTANCES 32
        MAXLOGHISTORY 1780
    LOGFILE
      GROUP 1 (
        '/arp/oraarp/oradata/dbrac/onlinelog/redo_1_11.log',
        '/arp/oraarp/flash_recovery_area/redo_1_12.log'
      ) SIZE 500M,
      GROUP 2 (
        '/arp/oraarp/oradata/dbrac/onlinelog/redo_1_21.log',
        '/arp/oraarp/flash_recovery_area/redo_1_22.log'
      ) SIZE 500M,
      GROUP 3 (
        '/arp/oraarp/oradata/dbrac/onlinelog/redo_1_31.log',
        '/arp/oraarp/flash_recovery_area/redo_1_32.log'
      ) SIZE 500M
    DATAFILE
      '/arp/oraarp/oradata/dbrac/system.ora',
      '/arp/oraarp/oradata/dbrac/undotbs1.dbf',
      '/arp/oraarp/oradata/dbrac/sysaux.dbf',
      '/arp/oraarp/oradata/dbrac/undotbs2.dbf',
      '/arp/oraarp/oradata/dbrac/users.dbf',
      '/arp/oraarp/oradata/dbrac/trswcm_infogate.dbf',
      '/arp/oraarp/oradata/dbrac/trswcm_poll.dbf',
      '/arp/oraarp/oradata/dbrac/trswcm_comment.dbf',
      '/arp/oraarp/oradata/dbrac/trsvideo.dbf',
      '/arp/oraarp/oradata/dbrac/trswcmv6.dbf',
      '/arp/oraarp/oradata/dbrac/e_trswcmv6.dbf',
      '/arp/oraarp/oradata/dbrac/wcmlog2010.dbf',
      '/arp/oraarp/oradata/dbrac/trswcmv65_mobile.dbf',
      '/arp/oraarp/oradata/dbrac/TRSMASSUB.dbf',
      '/arp/oraarp/oradata/dbrac/TRSWCMPLUGINS65SUB.dbf',
      '/arp/oraarp/oradata/dbrac/trswcmv65_sub.dbf',
      '/arp/oraarp/oradata/dbrac/trsmas_sub.dbf',
      '/arp/oraarp/oradata/dbrac/trswcmv65plugins_sub.dbf'
    CHARACTER SET ZHS16GBK;
    
  • 使数据库至mount状态,由于是rac的数据库,再编写线程2的在线日志,重置数据库

  SQL>startup mount
  
  SQL> ALTER DATABASE ADD LOGFILE THREAD 2
    GROUP 4 (
      '/arp/oraarp/oradata/dbrac/onlinelog/redo_2_21.log',
      '/arp/oraarp/flash_recovery_area/redo_2_22.log'
    ) SIZE 500M REUSE,
    GROUP 5 (
      '/arp/oraarp/oradata/dbrac/onlinelog/redo_2_11.log',
      '/arp/oraarp/flash_recovery_area/redo_2_12.log'
    ) SIZE 500M REUSE,
    GROUP 6 (
      '/arp/oraarp/oradata/dbrac/onlinelog/redo_2_31.log',
      '/arp/oraarp/flash_recovery_area/redo_2_32.log'
    ) SIZE 500M REUSE;
    
  SQL> ALTER DATABASE OPEN RESETLOGS;

2.7 恢复成功后的后续工作处理

  • 通过v$log知道,线程2,group 4,5,6需要删除

    SQL> select * from v$log;
      
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
    ---------- ---------- ---------- ---------- ---------- --- ----------------
    FIRST_CHANGE# FIRST_TIME
    ------------- --------------
             1          1          1  524288000          2 NO  CURRENT
       1.0147E+13 01-7 -13
      
             2          1          0  524288000          2 YES UNUSED
                0
      
             3          1          0  524288000          2 YES UNUSED
                0
      
      
        GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
    ---------- ---------- ---------- ---------- ---------- --- ----------------
    FIRST_CHANGE# FIRST_TIME
    ------------- --------------
             4          2          1  524288000          2 NO  CURRENT
       1.0147E+13 01-7 -13
      
             5          2          0  524288000          2 YES UNUSED
                0
      
             6          2          0  524288000          2 YES UNUSED
                0
      
      
    已选择6行。
    
  • 禁用线程2的归档日志进程和删除相关的在线日志文件

    SQL> alter database disable thread 2; 
    SQL> alter database drop logfile group 4; 
    SQL> alter database drop logfile group 5;
    SQL> alter database drop logfile group 6;
    
  • 删除undo表空间(UNDOTBS2)

    --查看表空间的默认使用状况
    SQL> show parameter undo_tablespace    
      
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    undo_tablespace                      string      UNDOTBS1
      
    SQL> select name from v$tablespace where name like 'UNDO%';
      
    NAME
    ------------------------------
    UNDOTBS1
    UNDOTBS2
      
    --进行undo表空间删除
    SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
    
  • 重建临时表空间

    ALTER TABLESPACE TEMP ADD TEMPFILE '/arp/oraarp/oradata/dbrac/tempfile/temp.dbf' size 1000M REUSE;  
    
  • 重建监听(根据实际情况具体ip和端口设置)

    vi /arp/oraarp/product/10.2.0/network/admin/listener.ora
      
    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
          (SID_NAME=PLSExtProc)
          (ORACLE_HOME=/arp/oraarp/product/10.2.0)
          (PROGRAM=extproc)
        )
       (SID_DESC=
          (SID_NAME=dbrac)                 --注意是小写,要不然会报错
          (ORACLE_HOME=/arp/oraarp/product/10.2.0)
        )
      ) 
      
    LISTENER=
      (DESCRIPTION_LIST=
        (DESCRIPTION=
          (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))
          (ADDRESS=(PROTOCOL=TCP)(HOST=172.31.0.201)(PORT=1521))
        )
      )
      
    启动数据库
    lsnrctl start
    查看监听
    lsnrctl status
    

到这里,数据库恢复完毕!~

附录一:单实例的pfile文件

  • pfile文件如下:

    dbrac.__db_cache_size=1677721600
    dbrac.__java_pool_size=16777216
    dbrac.__large_pool_size=16777216
    dbrac.__shared_pool_size=419430400
    dbrac.__streams_pool_size=0
    *.audit_file_dest='/arp/oraarp/admin/dbrac/adump'
    *.background_dump_dest='/arp/oraarp/admin/dbrac/bdump'
    *.compatible='10.2.0.4.0'
    *.control_files='/arp/oraarp/oradata/dbrac/control01.ctl','/arp/oraarp/oradata/dbrac/control02.ctl'#Restore Controlfile
    *.core_dump_dest='/arp/oraarp/admin/dbrac/cdump'
    *.db_block_size=8192
    *.db_create_file_dest='/arp/oraarp/oradata/dbrac'
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='dbrac'
    *.db_recovery_file_dest='/arp/oraarp/flash_recovery_area'
    *.db_recovery_file_dest_size=214748364800
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=dbracXDB)'
    *.job_queue_processes=10
    *.open_cursors=300
    *.pga_aggregate_target=1G
    *.processes=500
    *.remote_login_passwordfile='exclusive'
    *.sga_target=2G
    *.undo_management='AUTO'
    *.undo_tablespace='UNDOTBS1'
    *.user_dump_dest='/arp/oraarp/admin/dbrac/udump'
    *.utl_file_dir='/arp/oraarp/oradata'