ORACLE RAC异机rman恢复

脑中藏真书万卷

已于 2024-06-18 10:55:25 修改

阅读量1k

点赞数

分类专栏： rac oracle 文章标签：数据库 oracle linux

于 2020-07-07 11:38:21 首次发布

本文链接：https://blog.csdn.net/diandiandididi/article/details/107177898

版权

oracle 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

rac

3 篇文章 0 订阅

订阅专栏

1、源端数据库全备

export DBNAME=orcl
export ORACLE_SID=orcl1 
export ORACLE_BASE=/u01/app/oracle 
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1 
export PATH=$ORACLE_HOME/bin:$HOME/bin:$PATH 
export BACPATH=/backup/
export LGNAME=rman_backup_`date "+%Y-%m-%d"`.log
rman target / > ${BACPATH}/${LGNAME} << EOF 
run{ 
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;
}
quit
EOF

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

sqlplus / as sysdba
SQL> create pfile='/home/oracle/init0703.ora' from spfile='+DATA/orcl/spfileorcl.ora';
File created.



cat pfile='/home/oracle/init0703.ora'

orcl2.__db_cache_size=507879882752
orcl1.__db_cache_size=498216206336
......
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOTBS1'

目标创建必要的目录
– 在 oracle 用户下创建(所有节点执行)

$ su - oracle
$ mkdir -p /u01/app/oracle/admin/orcl/adump

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

[grid@adg2 ~]$ asmcmd
ASMCMD> ls
DATA/
FRA/
OCR/
ASMCMD> cd DATA
ASMCMD>ls
ASMCMD> mkdir orcl
ASMCMD> cd orcl  
ASMCMD> ls
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir DATAFILE
ASMCMD> mkdir ONLINELOG
ASMCMD> mkdir PARAMETERFILE

ASMCMD> cd FRA
ASMCMD> ls
ASMCMD> mkdir orcl
ASMCMD> cd orcl
ASMCMD> ls             
ASMCMD> mkdir ARCHIVELOG
ASMCMD

参数文件修改
将源端参数文件传到在目标端并编辑 /home/oracle/init0703.ora (参数根据实际情况进行配置)

$ vi /home/oracle/init0703.ora
orcl2.__db_cache_size=507879882752
orcl1.__db_cache_size=498216206336
......
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOTBS1'

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

创建spfile
在目标端用上述编辑的 pfile.ora 在共享设备上创建spfile

$ sqlplus / as sysdba
SQL> 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.ora 
SPFILE='+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.ora
SPFILE='+DATA/orcl/spfileorcl.ora'

创建口令文件

节点1：
$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=oracle
节点2
$ orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=oracle

还原控制文件
（在一个节点上执行）
– 数据库启动到 nomount

$ sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.
Total System Global Area 6062931968 bytes
Fixed Size                  2264376 bytes
Variable Size            3305112264 bytes
Database Buffers         2734686208 bytes
Redo Buffers               20869120 bytes

查看监听状态

[oracle@adg1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 04-JUL-2020 01:43:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                03-JUL-2020 08:21:08
Uptime                    0 days 17 hr. 22 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.4/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/adg1/listener/alert/log.xml
Listening 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 2020

Copyright (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-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/orcl/controlfile/current.257.1044841499
Finished restore at 04-JUL-20

RMAN>

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

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_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_1
File Name: /backup/orcl_spf_08v4c9ks_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /backup/orcl_ctl_07v4c9kn_1_1
File Name: /backup/orcl_spf_08v4c9ks_1_1

RMAN> 

-- 如不能正常CATALOG 备份集，按照如下方式手工CATALOG
RMAN> catalog backuppiece '/backup/orcl_arc_06v4c9jt_1_1';

cataloged backup piece
backup piece handle=/backup/orcl_arc_06v4c9jt_1_1 RECID=6 STAMP=1044841679

RMAN> catalog backuppiece '/backup/orcl_ctl_07v4c9kn_1_1';

cataloged backup piece
backup piece handle=/backup/orcl_ctl_07v4c9kn_1_1 RECID=7 STAMP=1044841685

RMAN> catalog backuppiece '/backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8';

cataloged backup piece
backup piece handle=/backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8 RECID=8 STAMP=1044841744

RMAN> catalog backuppiece '/backup/orcl_spf_08v4c9ks_1_1';

cataloged backup piece
backup piece handle=/backup/orcl_spf_08v4c9ks_1_1 RECID=9 STAMP=1044841756

RMAN>

11.恢复数据文件

RMAN> restore database;

Starting restore at 04-JUL-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/orcl/datafile/system.257.1038822139
channel ORA_DISK_1: restoring datafile 00002 to +DATA/orcl/datafile/sysaux.256.1038822141
channel ORA_DISK_1: restoring datafile 00003 to +DATA/orcl/datafile/undotbs1.260.1038822141
channel ORA_DISK_1: restoring datafile 00004 to +DATA/orcl/datafile/users.261.1038822141
channel ORA_DISK_1: restoring datafile 00005 to +DATA/orcl/datafile/undotbs2.266.1038822341
channel ORA_DISK_1: restoring datafile 00006 to +DATA/orcl/datafile/xttstest1.dbf
channel ORA_DISK_1: reading from backup piece /backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8
channel ORA_DISK_1: piece handle=/backup/orcl_ful_data_file_ORCL_20200703_4_1_04v4c9h8 tag=TAG20200703T095800
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 04-JUL-20

RMAN> recover database;

Starting recover at 04-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 instance=orcl1 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=17
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=18
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=39
channel ORA_DISK_1: reading from backup piece /backup/orcl_arc_06v4c9jt_1_1
channel ORA_DISK_1: piece handle=/backup/orcl_arc_06v4c9jt_1_1 tag=TAG20200703T095924
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_1_seq_38.268.1044842597 thread=1 sequence=38
archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_2_seq_17.267.1044842597 thread=2 sequence=17
archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_2_seq_18.266.1044842599 thread=2 sequence=18
archived log file name=+FRA/orcl/archivelog/2020_07_04/thread_1_seq_39.265.1044842599 thread=1 sequence=39
unable to find archived log
archived log thread=1 sequence=40
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/04/2020 02:03:24
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 40 and starting SCN of 2836216

RMAN> recover database until scn 2836216;       

Starting recover at 04-JUL-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 04-JUL-20

12.打开数据库

[oracle@adg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:04:59 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS2i' does not exist or of wrong type
Process ID: 25160
Session ID: 576 Serial number: 21
（参数错误，修改参数）

[oracle@adg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:11:51 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1553305600 bytes
Fixed Size		    2253544 bytes
Variable Size		 1392512280 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    7544832 bytes
Database mounted.
SQL> create pfile='/home/oracle/pfile0707.ora' from spfile;

File created.


[oracle@adg1 ~]$ vim pfile0707.ora 
[oracle@adg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:14:07 2020

Copyright (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 bytes
Fixed Size		    2253544 bytes
Variable Size		 1392512280 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    7544832 bytes
Database 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 sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 4 02:22:13 2020

Copyright (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 bytes
Fixed Size		    2253544 bytes
Variable Size		 1392512280 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    7544832 bytes
SQL> create spfile from pfile='/home/oracle/pfile0707.ora';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup  
ORACLE instance started.

Total System Global Area 1553305600 bytes
Fixed Size		    2253544 bytes
Variable Size		 1392512280 bytes
Database Buffers	  150994944 bytes
Redo Buffers		    7544832 bytes
Database 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 racdb1
srvctl remove database -d racdb -i racdb1
查看数据库配置
[oracle@adg2 ~]$ srvctl config database -d orcladg
Database unique name: orcladg
Database name: 
Oracle home: /u01/app/oracle/product/11.2.0.4/db_1/
Oracle user: oracle
Spfile: /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileorcl2.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcladg
Database instances: orcl1,orcl2
Disk Groups: 
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

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

[grid@adg2 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.FRA.dg
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.LISTENER.lsnr
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.OCR.dg
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.asm
               ONLINE  ONLINE       adg1                     Started             
               ONLINE  ONLINE       adg2                     Started             
ora.gsd
               OFFLINE OFFLINE      adg1                                         
               OFFLINE OFFLINE      adg2                                         
ora.net1.network
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
                                       
ora.registry.acfs
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       adg1                                         
ora.adg1.vip
      1        ONLINE  ONLINE       adg1                                         
ora.adg2.vip
      1        ONLINE  ONLINE       adg2                                         
ora.cvu
      1        ONLINE  ONLINE       adg1                                         
ora.oc4j
      1        ONLINE  ONLINE       adg1                                         
ora.orcladg.db
      1        OFFLINE OFFLINE                                                   
      2        OFFLINE OFFLINE                                                   
ora.scan1.vip
      1        ONLINE  ONLINE       adg1                                         
[grid@adg2 ~]$ srvctl status database -d orcladg
Instance orcl1 is not running on node adg1
Instance 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 orcladg
Instance orcl1 is running on node adg1
Instance orcl2 is running on node adg2
[grid@adg2 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.FRA.dg
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.LISTENER.lsnr
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.OCR.dg
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
ora.asm
               ONLINE  ONLINE       adg1                     Started             
               ONLINE  ONLINE       adg2                     Started             
ora.gsd
               OFFLINE OFFLINE      adg1                                         
               OFFLINE OFFLINE      adg2                                         
ora.net1.network
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
                                         
ora.registry.acfs
               ONLINE  ONLINE       adg1                                         
               ONLINE  ONLINE       adg2                                         
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       adg1                                         
ora.adg1.vip
      1        ONLINE  ONLINE       adg1                                         
ora.adg2.vip
      1        ONLINE  ONLINE       adg2                                         
ora.cvu
      1        ONLINE  ONLINE       adg1                                         
ora.oc4j
      1        ONLINE  ONLINE       adg1                                         
ora.orcladg.db
      1        ONLINE  ONLINE       adg1                     Open                
      2        ONLINE  ONLINE       adg2                     Open                
ora.scan1.vip
      1        ONLINE  ONLINE       adg1