DG：11.2.0.4 RAC在线duplicate恢复DG
1.环境介绍
测试环境， 在一个双节点的RAC上使用duplicate搭建DG，使用在线的方式搭建
|
主机 |
IP |
操作系统 |
实例 |
db_name |
db_unique_name |
db_version |
配置 |
|
rac1 |
192.168.163.4 |
centos7.8 |
orcl1 |
orcl |
orcl |
11.2.0.4 |
4c4g50G |
|
rac2 |
192.168.163.5 |
centos7.8 |
orcl2 |
orcl |
orcl |
4c4g50G |
|
|
rac-dg |
192.168.163.27 |
centos7.8 |
orcl_stb |
orcl |
orcl_stb |
4c4g50G |
2. 主库检查
2.1 检查归档
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +CRSDG
Oldest online log sequence 76
Next log sequence to archive 77
Current log sequence 77
2.2主备参数检查
set linesize 500 pages 0
col value for a90
col name for a50
select name,value from v$parameter where name in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2','log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert','log_file_name_convert','standby_file_management');
db_file_name_convert
log_file_name_convert
log_archive_dest_1 LOCATION=+CRSDG
log_archive_dest_2 service=orcl_stb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_stb
log_archive_dest_state_1 enable
log_archive_dest_state_2 ENABLE
fal_server orcl_stb
log_archive_config DG_CONFIG=(orcl,orcl_stb)
log_archive_format %t_%s_%r.dbf
log_archive_max_processes 4
standby_file_management AUTO
remote_login_passwordfile EXCLUSIVE
db_name orcl
db_unique_name orcl
14 rows selected.
2.3 检查日志归档文件位置
--归档较多的话，建议输出到文本中，一般归档都会存储在相同位置下，如有需要可以进行查询
方法一：
select name,sequence#,to_char(completion_time,'yyyy-mm-dd hh24:mi:ss') "completion_time" from v$archived_log order by sequence# ;
方法二：
rman{}
crosscheck archivelog all;
--查询redo文件的位置，如果给备库建立了standby log，需要为redo文件进行转储
select group#,thread#,sequence#,bytes/1024/1024 mb,archived,status from V$STANDBY_LOG;
2.4 检查数据文件位置
--需要判断临时文件数据文件，是否在相同目录，如果不通，都需要对目录进行转储
select file_name from dba_temp_files;
select file_name from dba_data_files;
2.5检查监听
[oracle@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2021 11:21:59
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 24-AUG-2021 09:12:44
Uptime 0 days 2 hr. 9 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u1/db/11.2.0/grid/network/admin/listener.ora
Listener Log File /u1/db/grid/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.4)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.163.7)(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 READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
2.6检查tnsname
[oracle@rac1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u1/db/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
3.主库配置
3.1修改归档设置
alter system set log_archive_config='DG_CONFIG=(orcl,orcl_stb)' sid='*';
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(orcl,orcl_stb)
3.2配置归档路径
alter system set log_archive_dest_2='service=orcl_stb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_stb' scope=both sid='*';
alter system set log_archive_dest_state_2='enable' sid='*';
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=orcl_stb lgwr async va
lid_for=(online_logfiles,prima
ry_role) db_unique_name=orcl_s
tb
3.3设置fal_server
alter system set fal_server='orcl_stb' scope=both sid='*';
SQL> show parameter fal_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string orcl_stb
3.4生成pfile文件
create pfile='/tmp/initorcl.ora' from spfile;
传输pfile文件到备库指定目录
scp /tmp/initorcl.ora root@192.168.163.27:$ORACLE_HOME/dbs
3.5 standby_file_management
注意修改standby_file_management的生成方式，如果是MANUAL，改成AUTO
3.6拷贝密码文件
rac搭建DG，需要把密码文件一节点->传到二节点->二节点传到备库，统一密码文件
scp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapworcl oracle@rac2:$ORACLE_HOME/dbs
scp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/orapworcl oracle@192.168.163.27:$ORACLE_HOME/dbs
备库
mv orapworcl orapworcl_stb
--如果需要重新生成密码文件或者修改密码
alter user sys identified by oracle;
orapwd file=$ORACLE_HOME/dbs/orapworcl_stb password=oracle entries=100 ignorecase=y
3.7修改tnsname
双节点修改tnsnames.ora
[oracle@rac1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u1/db/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl_stb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.27)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_stb)
)
)
4.备库配置
192.168.163.27，提前给备库服务器安装好数据库软件，不用新建实例，测试服务器跟主库服务器配置都相同
4.1修改pfile文件
修改ora文件名称
mv initorcl.ora initorcl_stb.ora
编辑pfile文件
- 注释有关集群相关参数
- 给控制文件新生成一个位置
- 修改fal_client和fal_server的名称
- 转储数据和日志文件路径
- 给一个归档的路径
[oracle@rac-dg dbs]$ vim initorcl_stb.ora
orcl2.__db_cache_size=603979776
orcl1.__db_cache_size=603979776
orcl2.__java_pool_size=16777216
orcl1.__java_pool_size=16777216
orcl2.__large_pool_size=33554432
orcl1.__large_pool_size=33554432
orcl2.__oracle_base='/u1/db/oracle'#ORACLE_BASE set from environment
orcl2.__pga_aggregate_target=637534208
orcl1.__pga_aggregate_target=637534208
orcl2.__sga_target=956301312
orcl1.__sga_target=956301312
orcl2.__shared_io_pool_size=0
orcl1.__shared_io_pool_size=0
orcl2.__shared_pool_size=285212672
orcl1.__shared_pool_size=285212672
orcl2.__streams_pool_size=0
orcl1.__streams_pool_size=0
*.audit_file_dest='/u1/db/oracle/admin/orcl/adump'
*.audit_trail='none'
#*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.control_files='/u1/db/oracle/product/11.2.0/db_1/dbs/orcl_stb.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u1/db/oracle/product/11.2.0/db_1/dbs'
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='orcl_stb'
*.diagnostic_dest='/u1/db/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='orcl_stb'
*.fal_server='orcl'
#orcl1.instance_number=1
#orcl2.instance_number=2
*.log_archive_config='DG_CONFIG=(orcl,orcl_stb)'
*.log_archive_dest_1='LOCATION=/arch'
#*.log_archive_dest_2='service=orcl_stb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_stb'
*.db_file_name_convert='+FRADG/orcl/datafile','/u1/db/oracle/product/11.2.0/db_1/dbs','+FRADG/orcl/tempfile','/u1/db/oracle/product/11.2.0/db_1/dbs'
*.log_file_name_convert='+CRSDG/ORCL/ARCHIVELOG','/arch','+CRSDG/rac-cluster/ocrfile','/arch'
*.memory_target=1589641216
*.open_cursors=300
*.processes=150
#*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
#orcl2.thread=2
#orcl1.thread=1
orcl2.undo_tablespace='UNDOTBS2'
orcl1.undo_tablespace='UNDOTBS1'
4.2创建目录并授权
创建initorcl_stb.ora中需要目录并授权
mkdir -p /u1/db/oracle/admin/orcl/adump
mkdir -p /arch
mkdir -p /u1/db/oracle
chown -R oracle.oinstall /arch
chown -R oracle.oinstall /u1/db/oracle/admin/orcl/adump
chown -R oracle.oinstall /u1/db/oracle
4.3配置监听
配置静态监听，LISTENER在前，SID_LIST_LISTENER在后
[oracle@rac-dg admin]$ cat listener.ora
LISTENER =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.27)(PORT=1521)
)
)
SID_LIST_LISTENER=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= orcl_stb)
(ORACLE_HOME=/u1/db/oracle/product/11.2.0/db_1)
(SID_NAME = orcl_stb)
)
)
ADR_BASE_LISTENER = /u1/db/oracle
4.4启动监听
[oracle@rac-dg admin]$ lsnrctl start
4.5配置tnsname
在备库中如果使用了主机名代替IP，就先将rac中的/etc/hosts 解析的主机名写进到备库的hosts文件里
[oracle@rac-dg admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.9)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.4)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcl_stb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.27)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl_stb)
)
)
4.6 修改备库密码文件名称
4.7主备互相验证tns
主：
[oracle@rac1 ~]$ tnsping orcl_stb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2021 15:03:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.27)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_stb)))
OK (0 msec)
[oracle@rac1 ~]$ tnsping orcl1
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2021 15:03:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
备：
[oracle@rac-dg admin]$ tnsping orcl_stb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2021 15:03:26
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.27)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl_stb)))
OK (0 msec)
[oracle@rac-dg admin]$ tnsping orcl1
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-AUG-2021 15:03:39
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.163.4)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
--注意此时主备两个库的状态是否正确，DBID等信息，避免生产事故
[oracle@rac-dg admin]$ rman target sys/oracle@orcl1 auxiliary sys/oracle@orcl_stb
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Aug 24 15:05:50 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1608598914)
connected to auxiliary database: ORCL (not mounted)
5. 恢复DG
5.1启动到nomount状态
SQL> startup nomount pfile from 'initorcl_stb.ora';
5.2恢复数据库
--如果数据量过大，尽量使用脚本后台恢复，很多生产的前台连接会话时间都很短
[oracle@PEKPBSDIHDBP01 ~]$ cat duplicate.sh
rman target sys/oracle@orcl auxiliary sys/oracle@orcl_stb log=/tmp/dup.log <<EOF
run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
duplicate target database for standby from active database nofilenamecheck;
}
exit;
EOF
chmod +x duplicate.sh
执行日志：
[oracle@rac-dg tmp]$ cat dup.log
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 23 19:17:55 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1608598914)
connected to auxiliary database: ORCL (not mounted)
RMAN> 2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=77 instance=orcl1 device type=DISK
allocated channel: p2
channel p2: SID=143 instance=orcl1 device type=DISK
allocated channel: s1
channel s1: SID=134 device type=DISK
allocated channel: s2
channel s2: SID=10 device type=DISK
Starting Duplicate Db at 23-AUG-21
contents of Memory Script:
{
backup as copy reuse
targetfile '/u1/db/oracle/product/11.2.0/db_1/dbs/orapworcl1' auxiliary format
'/u1/db/oracle/product/11.2.0/db_1/dbs/orapworcl_stb' ;
}
executing Memory Script
Starting backup at 23-AUG-21
Finished backup at 23-AUG-21
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u1/db/oracle/product/11.2.0/db_1/dbs/orcl_stb.ctl';
}
executing Memory Script
Starting backup at 23-AUG-21
channel p1: starting datafile copy
copying standby control file
output file name=/u1/db/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f tag=TAG20210823T191758 RECID=30 STAMP=1081365479
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-AUG-21
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/temp.263.1080818181";
switch clone tempfile all;
set newname for datafile 1 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/system.256.1080818101";
set newname for datafile 2 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/sysaux.257.1080818101";
set newname for datafile 3 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/undotbs1.258.1080818101";
set newname for datafile 4 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/users.259.1080818101";
set newname for datafile 5 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/example.264.1080818183";
set newname for datafile 6 to
"/u1/db/oracle/product/11.2.0/db_1/dbs/undotbs2.265.1080818283";
backup as copy reuse
datafile 1 auxiliary format
"/u1/db/oracle/product/11.2.0/db_1/dbs/system.256.1080818101" datafile
2 auxiliary format
"/u1/db/oracle/product/11.2.0/db_1/dbs/sysaux.257.1080818101" datafile
3 auxiliary format
"/u1/db/oracle/product/11.2.0/db_1/dbs/undotbs1.258.1080818101" datafile
4 auxiliary format
"/u1/db/oracle/product/11.2.0/db_1/dbs/users.259.1080818101" datafile
5 auxiliary format
"/u1/db/oracle/product/11.2.0/db_1/dbs/example.264.1080818183" datafile
6 auxiliary format
"/u1/db/oracle/product/11.2.0/db_1/dbs/undotbs2.265.1080818283" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u1/db/oracle/product/11.2.0/db_1/dbs/temp.263.1080818181 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 23-AUG-21
channel p1: starting datafile copy
input datafile file number=00001 name=+FRADG/orcl/datafile/system.256.1080818101
channel p2: starting datafile copy
input datafile file number=00002 name=+FRADG/orcl/datafile/sysaux.257.1080818101
output file name=/u1/db/oracle/product/11.2.0/db_1/dbs/system.256.1080818101 tag=TAG20210823T191805
channel p1: datafile copy complete, elapsed time: 00:00:07
channel p1: starting datafile copy
input datafile file number=00005 name=+FRADG/orcl/datafile/example.264.1080818183
output file name=/u1/db/oracle/product/11.2.0/db_1/dbs/sysaux.257.1080818101 tag=TAG20210823T191805
channel p2: datafile copy complete, elapsed time: 00:00:07
channel p2: starting datafile copy
input datafile file number=00003 name=+FRADG/orcl/datafile/undotbs1.258.1080818101
output file name=/u1/db/oracle/product/11.2.0/db_1/dbs/undotbs1.258.1080818101 tag=TAG20210823T191805
channel p2: datafile copy complete, elapsed time: 00:00:01
channel p2: starting datafile copy
input datafile file number=00006 name=+FRADG/orcl/datafile/undotbs2.265.1080818283
output file name=/u1/db/oracle/product/11.2.0/db_1/dbs/example.264.1080818183 tag=TAG20210823T191805
channel p1: datafile copy complete, elapsed time: 00:00:02
channel p1: starting datafile copy
input datafile file number=00004 name=+FRADG/orcl/datafile/users.259.1080818101
output file name=/u1/db/oracle/product/11.2.0/db_1/dbs/undotbs2.265.1080818283 tag=TAG20210823T191805
channel p2: datafile copy complete, elapsed time: 00:00:01
output file name=/u1/db/oracle/product/