AI智能
改变未来

ORACLE RAC异机rman恢复

1、源端数据库全备

export DBNAME=orclexport ORACLE_SID=orcl1export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1export PATH=$ORACLE_HOME/bin:$HOME/bin:$PATHexport BACPATH=/backup/export LGNAME=rman_backup_`date \"+%Y-%m-%d\"`.logrman target / > ${BACPATH}/${LGNAME} << EOFrun{CONFIGURE RETENTION POLICY TO REDUNDANCY 1;CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT \'${BACPATH}/${DBNAME}_ful_%U\';ALLOCATE CHANNEL c1 DEVICE TYPE DISK;BACKUP FUll DATABASE format \'${BACPATH}/${DBNAME}_ful_data_file_%d_%T_%s_%p_%u\';sql \'alter system archive log current\';backup archivelog all format \'${BACPATH}/${DBNAME}_arc_%U\';backup current controlfile format \'${BACPATH}/${DBNAME}_ctl_%U\';backup spfile format \'${BACPATH}/${DBNAME}_spf_%U\';release channel c1;}quitEOF

2、源端根据spfile创建pfile文件

sqlplus / as sysdbaSQL> create pfile=\'/home/oracle/init0703.ora\' from spfile=\'+DATA/orcl/spfileorcl.ora\';File created.cat pfile=\'/home/oracle/init0703.ora\'orcl2.__db_cache_size=507879882752orcl1.__db_cache_size=498216206336......orcl2.undo_tablespace=\'UNDOTBS2\'orcl1.undo_tablespace=\'UNDOTBS1\'
  1. 目标创建必要的目录
    – 在 oracle 用户下创建(所有节点执行)
$ su - oracle$ mkdir -p /u01/app/oracle/admin/orcl/adump

– 在目标端 grid 用户下在共享设备创建必要的目录(单节点执行即可)

[grid@adg2 ~]$ asmcmdASMCMD> lsDATA/FRA/OCR/ASMCMD> cd DATAASMCMD>lsASMCMD> mkdir orclASMCMD> cd orclASMCMD> lsASMCMD> mkdir CONTROLFILEASMCMD> mkdir DATAFILEASMCMD> mkdir ONLINELOGASMCMD> mkdir PARAMETERFILEASMCMD> cd FRAASMCMD> lsASMCMD> mkdir orclASMCMD> cd orclASMCMD> lsASMCMD> mkdir ARCHIVELOGASMCMD
  1. 参数文件修改
    将源端参数文件传到在目标端并编辑 /home/oracle/init0703.ora (参数根据实际情况进行配置)
$ vi /home/oracle/init0703.oraorcl2.__db_cache_size=507879882752orcl1.__db_cache_size=498216206336......orcl2.undo_tablespace=\'UNDOTBS2\'orcl1.undo_tablespace=\'UNDOTBS1\'

#这里增加时间格式,按照时间点进行还原时会用到
*.nls_date_format=‘yyyymmddhh24:mi:ss’

  1. 创建spfile
    在目标端用上述编辑的 pfile.ora 在共享设备上创建spfile
$ sqlplus / as sysdbaSQL> create spfile=\'+DATA/orcl/spfileorcl.ora\' from pfile=\'/home/oracle/pfile_0702.ora\';File created.

– 在所有节点上创建 pfile 内容指向共享设备上的spfile文件
节点1:

$[oracle@adg1 ~]$ echo \"SPFILE=\'+DATA/orcl/spfileorcl.ora\' \" > /u01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl1.ora[oracle@adg1 ~]$ cat /u01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl1.oraSPFILE=\'+DATA/orcl/spfileorcl.ora\'

节点2:

$[oracle@adg1 ~]$ echo \"SPFILE=\'+DATA/orcl/spfileorcl.ora\' \" > /u01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl2.ora[oracle@adg2 ~]$ cat /u01/app/oracle/product/11.2.0.4/db_1/dbs/initorcl2.oraSPFILE=\'+DATA/orcl/spfileorcl.ora\'
  1. 创建口令文件
节点1:$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=oracle节点2$ orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=oracle
  1. 还原控制文件
    (在一个节点上执行)
    – 数据库启动到 nomount
$ sqlplus / as sysdbaSQL> startup nomountORACLE instance started.Total System Global Area 6062931968 bytesFixed Size                  2264376 bytesVariable Size            3305112264 bytesDatabase Buffers         2734686208 bytesRedo Buffers               20869120 bytes
  1. 查看监听状态
[oracle@adg1 ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-JUL-2020 01:43:39Copyright (c) 1991, 2013, Oracle.  All rights reserved.Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUS of the LISTENER------------------------Alias                     LISTENERVersion                   TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date                03-JUL-2020 08:21:08Uptime                    0 days 17 hr. 22 min. 33 secTrace Level               offSecurity                  ON: Local OS AuthenticationSNMP                      OFFListener Parameter File   /u01/app/11.2.0.4/grid/network/admin/listener.oraListener Log File         /u01/app/grid/diag/tnslsnr/adg1/listener/alert/log.xmlListening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.29.143)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.29.145)(PORT=1521)))Services Summary...Service \"+ASM\" has 1 instance(s).Instance \"+ASM1\", status READY, has 1 handler(s) for this service...Service \"orcl\" has 1 instance(s).Instance \"orcl1\", status BLOCKED, has 1 handler(s) for this service...The command completed successfully[oracle@adg1 ~]$

nomount监听状态为BLOCKED
9. 恢复控制文件

[oracle@adg1 ~]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 4 01:44:09 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCL (not mounted)RMAN> restore controlfile from \'/backup/orcl_ctl_07v4c9kn_1_1\';Starting restore at 04-JUL-20using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:03output file name=+DATA/orcl/controlfile/current.257.1044841499Finished restore at 04-JUL-20RMAN>

注:不知道哪个文件还原控制文件,可以在源数据库中查看 list backup of controlfile;

RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1
  1. CATALOG 备份集
    – 指定备份集所在的文件目录,进行CATALOG
RMAN> catalog start with \'/backup/\';searching for all files that match the pattern /backup/List of Files Unknown to the Database=====================================File Name: /backup/orcl_ctl_07v4c9kn_1_1File Name: /backup/orcl_spf_08v4c9ks_1_1Do you really want to catalog the above files (enter YES or NO)? YEScataloging files...cataloging doneList of Cataloged Files=======================File Name: /backup/orcl_ctl_07v4c9kn_1_1File Name: /backup/orcl_spf_08v4c9ks_1_1RMAN>-- 如不能正常CATALOG 备份集,按照如下方式手工CATALOGRMAN> catalog backuppiece \'/backup/orcl_arc_06v4c9jt_1_1\';cataloged backup piecebackup piece handle=/backup/orcl_arc_06v4c9jt_1_1 RECID=6 STAMP=1044841679RMAN> catalog backuppiece \'/backup/orcl_ctl_07v4c9kn_1_1\';cataloged backup piecebackup piece handle=/backup/orcl_ctl_07v4c9kn_1_1 RECID=7 STAMP=1044841685RMAN> catalog backuppiece \'/backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8\';cataloged backup piecebackup piece handle=/backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8 RECID=8 STAMP=1044841744RMAN> catalog backuppiece \'/backup/orcl_spf_08v4c9ks_1_1\';cataloged backup piecebackup piece handle=/backup/orcl_spf_08v4c9ks_1_1 RECID=9 STAMP=1044841756RMAN>

11.恢复数据文件

RMAN> restore database;Starting restore at 04-JUL-20allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.257.1038822139channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.256.1038822141channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.260.1038822141channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.261.1038822141channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/datafile/undotbs2.266.1038822341channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/datafile/xttstest1.dbfchannel ORA_DISK_1: reading from backup piece /backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8channel ORA_DISK_1: piece handle=/backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8 tag=TAG20200703T095800channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 04-JUL-20RMAN> recover database;Starting recover at 04-JUL-20using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISKstarting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=38channel ORA_DISK_1: restoring archived logarchived log thread=2 sequence=17channel ORA_DISK_1: restoring archived logarchived log thread=2 sequence=18channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=39channel ORA_DISK_1: reading from backup piece /backup/orcl_arc_06v4c9jt_1_1channel ORA_DISK_1: piece handle=/backup/orcl_arc_06v4c9jt_1_1 tag=TAG20200703T095924channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:07archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_1_seq_38.268.1044842597 thread=1 sequence=38archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_2_seq_17.267.1044842597 thread=2 sequence=17archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_2_seq_18.266.1044842599 thread=2 sequence=18archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_1_seq_39.265.1044842599 thread=1 sequence=39unable to find archived logarchived log thread=1 sequence=40RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 07/04/2020 02:03:24RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 40 and starting SCN of 2836216RMAN> recover database until scn 2836216;Starting recover at 04-JUL-20using channel ORA_DISK_1starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 04-JUL-20

12.打开数据库

[oracle@adg1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:04:59 2020Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedORA-30012: undo tablespace \'UNDOTBS2i\' does not exist or of wrong typeProcess ID: 25160Session ID: 576 Serial number: 21(参数错误,修改参数)[oracle@adg1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:11:51 2020Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup mountORACLE instance started.Total System Global Area 1553305600 bytesFixed Size		    2253544 bytesVariable Size		 1392512280 bytesDatabase Buffers	  150994944 bytesRedo Buffers		    7544832 bytesDatabase mounted.SQL> create pfile=\'/home/oracle/pfile0707.ora\' from spfile;File created.[oracle@adg1 ~]$ vim pfile0707.ora[oracle@adg1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:14:07 2020Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup pfile=\'/home/oracle/pfile0707.ora\';ORACLE instance started.Total System Global Area 1553305600 bytesFixed Size		    2253544 bytesVariable Size		 1392512280 bytesDatabase Buffers	  150994944 bytesRedo Buffers		    7544832 bytesDatabase mounted.Database opened.SQL> create spfile from pfile=\'/home/oracle/pfile0707.ora\';File created.SQL>

13.节点2的恢复
把pfile从节点1 scp到节点二,然后通过pfile启动至nomount,创建spfile,打开数据库。

[oracle@adg1 ~]$ scp pfile0707.ora adg2:/home/oracle/pfile0707.ora                                                                                                                                                                        100% 1312     1.3KB/s   00:00[oracle@adg2 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:22:13 2020Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to an idle instance.SQL> startup nomount pfile=\'/home/oracle/pfile0707.ora\';ORACLE instance started.Total System Global Area 1553305600 bytesFixed Size		    2253544 bytesVariable Size		 1392512280 bytesDatabase Buffers	  150994944 bytesRedo Buffers		    7544832 bytesSQL> create spfile from pfile=\'/home/oracle/pfile0707.ora\';File created.SQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 1553305600 bytesFixed Size		    2253544 bytesVariable Size		 1392512280 bytesDatabase Buffers	  150994944 bytesRedo Buffers		    7544832 bytesDatabase mounted.Database opened.SQL>

14.添加注册信息

[oracle@adg2 ~]$ srvctl add database -d orcladg -o /u01/app/oracle/product/11.2.0.4/db_1/ -p /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileorcl2.ora[oracle@adg2 ~]$ srvctl add instance -d orcladg -i orcl1 -n adg1[oracle@adg2 ~]$ srvctl add instance -d orcladg -i orcl2 -n adg2[oracle@adg2 ~]$其中,各项参数代表-d db_unique_name-o oracle_home;-p spfile_path-i instance_name-n node_name如果要删除一个实例或数据库srvctl remove instance -d racdb -i racdb1srvctl remove database -d racdb -i racdb1查看数据库配置[oracle@adg2 ~]$ srvctl config database -d orcladgDatabase unique name: orcladgDatabase name:Oracle home: /u01/app/oracle/product/11.2.0.4/db_1/Oracle user: oracleSpfile: /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileorcl2.oraDomain:Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICServer pools: orcladgDatabase instances: orcl1,orcl2Disk Groups:Mount point paths:Services:Type: RACDatabase is administrator managed

15.启动两个节点的数据库

[grid@adg2 ~]$ crsctl status res -t--------------------------------------------------------------------------------NAME           TARGET  STATE        SERVER                   STATE_DETAILS--------------------------------------------------------------------------------Local Resources--------------------------------------------------------------------------------ora.DATA.dgONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.FRA.dgONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.LISTENER.lsnrONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.OCR.dgONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.asmONLINE  ONLINE       adg1                     StartedONLINE  ONLINE       adg2                     Startedora.gsdOFFLINE OFFLINE      adg1OFFLINE OFFLINE      adg2ora.net1.networkONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.registry.acfsONLINE  ONLINE       adg1ONLINE  ONLINE       adg2--------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.LISTENER_SCAN1.lsnr1        ONLINE  ONLINE       adg1ora.adg1.vip1        ONLINE  ONLINE       adg1ora.adg2.vip1        ONLINE  ONLINE       adg2ora.cvu1        ONLINE  ONLINE       adg1ora.oc4j1        ONLINE  ONLINE       adg1ora.orcladg.db1        OFFLINE OFFLINE2        OFFLINE OFFLINEora.scan1.vip1        ONLINE  ONLINE       adg1[grid@adg2 ~]$ srvctl status database -d orcladgInstance orcl1 is not running on node adg1Instance orcl2 is not running on node adg2#	虽然添加了database信息,但是database的状态是错误的# 集群显示db没有online,实际db是启动的# 重新启动[grid@adg2 ~]$ srvctl start database -d orcladg[grid@adg2 ~]$ srvctl status database -d orcladgInstance orcl1 is running on node adg1Instance orcl2 is running on node adg2[grid@adg2 ~]$ crsctl status res -t--------------------------------------------------------------------------------NAME           TARGET  STATE        SERVER                   STATE_DETAILS--------------------------------------------------------------------------------Local Resources--------------------------------------------------------------------------------ora.DATA.dgONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.FRA.dgONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.LISTENER.lsnrONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.OCR.dgONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.asmONLINE  ONLINE       adg1                     StartedONLINE  ONLINE       adg2                     Startedora.gsdOFFLINE OFFLINE      adg1OFFLINE OFFLINE      adg2ora.net1.networkONLINE  ONLINE       adg1ONLINE  ONLINE       adg2ora.registry.acfsONLINE  ONLINE       adg1ONLINE  ONLINE       adg2--------------------------------------------------------------------------------Cluster Resources--------------------------------------------------------------------------------ora.LISTENER_SCAN1.lsnr1        ONLINE  ONLINE       adg1ora.adg1.vip1        ONLINE  ONLINE       adg1ora.adg2.vip1        ONLINE  ONLINE       adg2ora.cvu1        ONLINE  ONLINE       adg1ora.oc4j1        ONLINE  ONLINE       adg1ora.orcladg.db1        ONLINE  ONLINE       adg1                     Open2        ONLINE  ONLINE       adg2                     Openora.scan1.vip1        ONLINE  ONLINE       adg1
赞(0) 打赏
未经允许不得转载:爱站程序员基地 » ORACLE RAC异机rman恢复