rac-rac setup dg 具体步骤
1 创建备库日志
alter database add standby logfile thread 1 group 5 ('+ASMVG1' ) size 50m,group 6 ('+ASMVG1' ) size 50m;
alter database add standby logfile thread 2 group 7 ('+ASMVG1' ) size 50m,group 8 ('+ASMVG1' ) size 50m;
2 强制记录日志
aler database force logging;
3 备库添加静态监听（grid用户）
静态监听文件
SID_LIST_LISTENER_DUP =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcldg)
(ORACLE_HOME = /oracle/db/product/11.2)
(SID_NAME= orcldg1)
)
)
LISTENER_DUP =
(DESCRIPTION=
(address_list=
(ADDRESS =(PROTOCOL=TCP)(HOST=192.168.56.107)(PORT=1521))
)
)
看监听状态，lsnrctl status listener_dup
实例orcldg1监听状态应该是unknown
4 主备Oracle用户tnsnames.ora文件增加如下内容
这里都使用scan-IP，防止一个实例失败，无法获取日志。这里用作主备之间的service传递传递日志log_archive_dest_2=‘service=orcl …’;
ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.105)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
orcldg=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)
########dg-service-configuration############ rman duplicate时 使用如下service，这里地址都是公网地址，不能是can-IP
ORCLp=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.105)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
dup=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
(UR=A)
)
)
测试监听连通性
tnsping orcl / orcldg / dup
5 备库两个节点修改Oracle用户和Grid用户参数环境变量.bash_profile
参数文件
oracle:
export ORACLE_BASE=/oracle/db
export ORACLE_HOME=$ORACLE_BASE/product/11.2
export ORACLE_SID=orcldg1
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
ORA_NLS33=$ORACLE_HOME/nls/admin/data
export ORA_NLS33
grid:
export ORACLE_BASE=/oracle/grid
export ORACLE_HOME=/oracle/asm
export ORACLE_SID=+ASM1
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
ORA_NLS33=$ORACLE_HOME/nls/admin/data
export ORA_NLS33
6 为备库修改参数文件，基于主库修改
###开始rman复制库时的两个别名，主库别名中地址应该用SCAN-IP，备库用新建的静态监听地址
修改对应目录，以对应sid(orcl—orcldg)，基于主备ASM存储磁盘组名称形同，asmvg1-数据库，arch—归档
修改：
*.audit_file_dest='/oracle/db/admin/orcldg/adump'
*.control_files='+ASMVG1/orcldg/control.ctl'
*.remote_listener='racdg-scan:1521'
修改：
orcldg2.thread=2
orcldg1.thread=1
orcldg2.undo_tablespace='UNDOTBS2'
orcldg1.undo_tablespace='UNDOTBS1'
orcldg1.instance_number=1
orcldg2.instance_number=2
新增
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'
log_archive_dest_1='location=/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
log_archive_dest_2='service=orcl lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
db_unique_name='orcldg'
db_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'
log_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'
fal_server='orcl'
fal_client='orcldg'
standby_file_management=auto
后备库参数文件如下：
orcldg2.__db_cache_size=637534208
orcldg1.__db_cache_size=637534208
orcldg1.__java_pool_size=16777216
orcldg2.__java_pool_size=16777216
orcldg1.__large_pool_size=16777216
orcldg2.__large_pool_size=16777216
orcldg1.__oracle_base='/oracle/db'#ORACLE_BASE set from environment
orcldg2.__oracle_base='/oracle/db'#ORACLE_BASE set from environment
orcldg1.__pga_aggregate_target=419430400
orcldg2.__pga_aggregate_target=419430400
orcldg1.__sga_target=1258291200
orcldg2.__sga_target=1258291200
orcldg1.__shared_io_pool_size=0
orcldg2.__shared_io_pool_size=0
orcldg2.__shared_pool_size=570425344
orcldg1.__shared_pool_size=570425344
orcldg1.__streams_pool_size=0
orcldg2.__streams_pool_size=0
*.audit_file_dest='/oracle/db/admin/orcldg /adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+ASMVG1/orcldg/controlfile.ctl'
*.db_block_size=8192
*.db_create_file_dest='+ASMVG1'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/oracle/db'
orcldg1.instance_number=1
orcldg2.instance_number=2
*.log_archive_dest_1='location=+ARCH'
*.open_cursors=300
*.pga_aggregate_target=414187520
*.processes=2500
*.remote_listener='racdg-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=2755
*.sga_target=1242562560
orcldg2.thread=2
orcldg1.thread=1
orcldg2.undo_tablespace='UNDOTBS2'
orcldg1.undo_tablespace='UNDOTBS1'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)'
log_archive_dest_1='location='+ARCH' valid_for=(all_logfiles,all_roles) db_unique_name=orcldg'
log_archive_dest_2='service=orcl async valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
db_unique_name='orcldg'
db_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'
log_file_name_convert='+ASMVG1/orcl','+ASMVG1/orcldg'
fal_server='orcl'
fal_client='orcldg'
standby_file_management=auto
如果主库中有非OMF创建的文件，需要指定映射路径 ‘+DATA/orcl/datafile ‘,’+DATA/orcldg/datafile’ 备库要提前创建好目录
7 在备库创建所需目录，
oracle:
mkdir -p /oracle/db/admin/orcldg/adump
grid:asmcmd
mkdir +ASMVG1/orcldg/
mkdir +ARCH/orcldg/
根据实际需要创建
9 启动备库实例
内存要一致，否则需要修改内存参数如SGA
startup pfile='/home/oracle/pfileorcldg1.ora' nomount;
然后创建spfile
create spfile from pfile='/home/oracle/pfileorcldg1.ora'
再使用spfile启动备库
startup nomount;
10 RMAN步骤duplicate 这里主库集群节点1 的orcl中地址修改为本地地址如公网地址rac1,(后面可以再改回来）
在主库
[oracle@rac1 admin]$ rman target sys/oracle@orclp auxiliary sys/oracle@dup
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 14 12:58:31 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1533381003)
connected to auxiliary database: ORCL (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
11 要停止并删除静态监听，否则后面节点一的网络资源处于intermediate状态。
此时静态监听的状态为
[oracle@racdg1 ~]$ lsnrctl status listener_dup
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-APR-2020 13:17:43
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.107)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-APR-2020 12:20:45
Uptime 0 days 0 hr. 56 min. 57 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/asm/network/admin/listener.ora
Listener Log File /oracle/grid/diag/tnslsnr/racdg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.107)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.109)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "orcldg" has 1 instance(s).
Instance "orcldg1", status READY, has 1 handler(s) for this service...
The command completed successfully
停止该监听，并删除配置 <<<报错
TNS-01190: The user is not authorized to execute the requested listener command
关闭数据库后，杀进程，删除配置再
12 查询此时的数据库状态
13 创建spfile到ASM磁盘组，并修改两个节点的$ORACLE_HOME/dbs/initorcldg1.ora ,$ORACLE_HOME/dbs/initorcldg2.ora为到ASM磁盘组中参数文件的连接
从处于mount状态的数据库创建pfile.此时这个pfile会知道创建好的controlfile位置。
create pfile=‘/home/oracle/pfilenew.ora’ from spfile;
从当前运行的数据库中的spfile创建一个pfile,再将该pfile创建为ASM 上的spfile
SQL> create spfile='+ASMVG1/orcldg/spfileorcldg.ora' from pfile='/home/oracle/pfilenew.ora';
File created.
vi $ORACLE_HOME/dbs/spfileorcldg1.ora 增加如下内容，两个节点都做
spfile='+ASMVG1/orcldg/spfileorcldg.ora'
14 关闭数据库实例（节点1）并注册数据库和实例资源到CRS，修改集群中数据属性为physical_standby
oracle:
srvctl add database -d orcldg –o /oracle/db/product/11.2
root:
srvctl add instance -d orcldg -i orcldg1 -n racdg1
srvctl add instance -d orcldg -i orcldg2 -n racdg2
srvctl modify database -d orcldg -r physical_standby -p '+ASMVG1/orcldg/spfileorcldg.ora'
（如果是主备switchover 切换，新主库需要改为primary角色srvctl modify database -d db_unique_name -r primary
查看集群的数据库配置信息
[root@racdg1 ~]# srvctl config database -d orcldg
Database unique name: orcldg
Database name:
Oracle home: /oracle/db/product/11.2
Oracle user: oracle
Spfile: +ASMVG1/orcldg/spfileorcldg.ora
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: orcldg
Database instances: orcldg1,orcldg2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
15 重启数据库
srvctl start database -d orcldg -o mount
crsctl stat res -t <<<<<数据库资源状态如下所示
ora.orcldg.db
1 ONLINE INTERMEDIATE racdg1 Mounted (Closed)
2 ONLINE INTERMEDIATE racdg2 Mounted (Closed)
16 修改主库参数
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)’;
alter system set log_archive_dest_1='location=+ARCH valid_for=(all_logfiles,all_roles) db_unique_name=orcl';
alter system set log_archive_dest_2='service=orcldg async valid_for=(online_logfiles,primary_role) db_unique_name=orcldg';
db_unique_name='orcl' <<<该参数主库默认为db_name
alter system set db_file_name_convert='+ASMVG1/orcldg' , '+ASMVG1/orcl' scope=spfile
alter system set log_file_name_convert='+ASMVG1/orcldg' , '+ASMVG1/orcl' scope=spfile
alter system set fal_server='orcldg'
alter system set fal_client='orcl'
用日志
未应用日志时备库日志
ASMCMD> ls
thread_1_seq_44.258.1037714057
thread_1_seq_45.256.1037714035
thread_1_seq_46.257.1037714039
应用如下指令的日志
ASMCMD> ls
thread_1_seq_44.258.1037714057
thread_1_seq_45.256.1037714035
thread_1_seq_46.257.1037714039
thread_2_seq_30.259.1037714345
thread_2_seq_31.260.1037714355
thread_2_seq_32.261.1037714365
SQL> alter database recover managed standby database disconnect from session; <<<此时会自动向主库要缺少的日志
Database altered.
SQL> alter database recover managed standby database cancel;
在节点1打开备库：
SQL> alter database open;
Database altered.
在一个节点启动mrp进程
启动日志应用
SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.
[oracle@racdg1 ~]$ ps -ef | grep mrp
oracle 13614 1 0 14:01 ? 00:00:00 ora_mrp0_orcldg1
oracle 13703 13264 0 14:02 pts/0 00:00:00 grep mrp
在节点2打开备库
SQL> alter database open;
Database altered.
查询此时集群中数据库状态
crsctl stat res -t
ora.orcldg.db
1 ONLINE ONLINE racdg1 Open
2 ONLINE ONLINE racdg2 Open
SQL> select open_mode,database_role,protection_mode from v$database;
OPEN_MODE DATABASE_ROLE PROTECTION_MODE
-------------------- ---------------- --------------------
READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE
测试与验证
问题：主库节点2数据库告警日志报错
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.
Tue Apr 14 14:39:24 2020
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
orcl, orcldg使用scan地址。(在使用RMAN复制使用的service要指定公网地址，需要具体到一个节点，rman duplicate会报错。
mrp只能在一个节点启动，否则报错，mrp在一个节点启动后，在两个节点的修改都可以在备库应用。
SQL> alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database using current logfile disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
