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

Published April 27, 2020, 7:05 a.m. by admin

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

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

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

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

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

  set oracle_sid = DBRAC  
  create spfile from pfile='/home/oraarp/file.ora';  
  startup nomount

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

  cd /arp/oraarp/admin/dbrac/                                             
  mkdir -p adump bdump cdump udump 
  mkdir -p /arp/oraarp/oradata/dbrac/                                      
  mkdir -p /arp/oraarp/flash_recovery_area 
  rman target /                                                          
  RMAN>set dbid=715992163                                              
  RMAN>RESTORE CONTROLFILE FROM '/arp/backup/wangzhan44/ctl_t818731703_s1634_20130622_p1';   
  RMAN>alter database mount 

2.3 注册备份文件到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'
  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;
  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 恢复成功后的后续工作处理

  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行。
  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;
  --查看表空间的默认使用状况
  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;  
  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文件

  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'

同类文章

【RMAN】REPORT命令

【RMAN】RMAN验证validate命令

【RMAN】RMAN的动态视图-查看RMAN的备份情况

【RMAN】在Oracle11gR2下基于RMAN恢复schema的数据

2 次评论

Comment 1 by sdzhang May 6, 2020, 5:46 a.m.

测试评论

Comment 2 by sdzhang May 6, 2020, 5:46 a.m.

测试评论

添加一条评论