Oracle 11G DG之Duplicate方式搭建

千家信息网最后更新 2025年01月21日Oracle 11G DG之Duplicate方式搭建DG搭建
1.DG之RMAN Duplicate方式

环境：

IP DB_NAME DB_UNIQUE_NAME ORACLE_SID DB PORT
主库：192.168.1.69 hsidb hsidbpr hsidb 1525
备库：192.168.1.70 hsidb hsidbsd hsidb 1525

#Active Database Duplicate步骤
a.根据主库设置参数后的PFILE,备库根据主库的PFILE,设置参数值,生成备库SPFILE.
b.根据主库的密码文件,生成备库的密码文件.
c.把备库启动到nomount状态.
d.RMAN同时连接主库与备库,执行duplicate命令.

###主库

1.1 主/备库安装Oracle Software及静态监听及TNS,主库DBCA建库.

cat /etc/hosts
192.168.1.70 rrfuwu-29.beidou rrfuwu-29
192.168.1.69 rrfuwu-28.beidou rrfuwu-28

1.2 主库查看归档模式
SQL> archive log list;

1.3 开启force_logging
SQL>select NAME,FORCE_LOGGING from v$database;
SQL>shutdown immediate;
SQL>startup mount
SQL>alter database force logging;
SQL>alter database open;


1.4 密码文件
查看主库是否存在,密码文件,如果存在,scp到备份,因为此处主备库ORACLE_SID相同,故备库可以直接使用.
注:主备库密码文件密码一定要相同.


[oracle@rrfuwu-28 dbs]$scp -rp orapwhsidb 192.168.1.70:/u01/app/oracle/product/11.2.0/db_1/dbs


1.5 主库添加standby logfile
standby logfile=(1+logfile组数)*thread=(1+3)*1=4组,需要加4组standby logfile.

查看主库logfile

主库添加standby logfile

SQL>alter database add standby logfile 'x' size 300M;

1.6 主库创建pfile
SQL> create pfile from spfile;


主库inithsidb.ora备份, 可进行DB原参数值还原.

vim inithsidb.ora 加入如下参数
注： 主备库数据文件与日志文件路径相同, 做 rman duplicate时参数db_file_name_convert 与log_file_name_convert 也需要设置,如果两个参数不设置,做duplicate时会报
"ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log N thread P:' xxxxx';" (N为log日志组号,P为thread号,xxxxx为日志路径代替).

*.db_unique_name=hsidbpr
*.log_archive_config='DG_CONFIG=(hsidbpr,hsidbsd)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hsidbpr'
*.log_archive_dest_2='SERVICE=hsidbsd LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hsidbsd'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.FAL_SERVER=hsidbsd
*.FAL_CLIENT=hsidbpr
*.db_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.log_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.standby_file_management=AUTO


关闭实例,根据修改后的pfile,创建spfile.

1.7 主库scp pfile到备库
注: 主备库密码文件特权用户密码要相同.

[oracle@rrfuwu-28 dbs]$ scp -rp inithsidb.ora 192.168.1.70:/u01/app/oracle/product/11.2.0/db_1/dbs


###备库

2.1 根据专到备库的主库pfile,进行相关DG参数修改.

*.db_unique_name=hsidbsd
*.log_archive_config='DG_CONFIG=(hsidbpr,hsidbsd)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=hsidbsd'
*.log_archive_dest_2='SERVICE=hsidbpr LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hsidbpr'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.FAL_SERVER=hsidbpr
*.FAL_CLIENT=hsidbsd
*.db_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.log_file_name_convert='/u01/app/oracle/oradata/hsidb','/u01/app/oracle/oradata/hsidb'
*.standby_file_management=AUTO

2.2 备库创建相关目录
根据pfile中的目录信息,在备库创建相关目录
[oracle@rrfuwu-29 ~]$ mkdir -p /u01/app/oracle/admin/hsidb/adump
[oracle@rrfuwu-29 ~]$ mkdir -p /u01/app/oracle/oradata/hsidb/
[oracle@rrfuwu-29 ~]$ mkdir -p /u01/app/oracle/arch


2.3 备库startup nomount
SQL> startup nomount pfile=?/dbs/inithsidb.ora;

2.4 RMAN DUPLICATE

[oracle@rrfuwu-29 ~]$ rman target sys/SIGasmlib@HSIDBPR auxiliary sys/SIGasmlib@HSIDBSD
RMAN>duplicate target database for standby from active database nofilenamecheck dorecover;
......中间过程省略................


2.5 logfile应用
SQL>alter database open;
SQL>alter database recover managed standby database using current logfile disconnect from session;
SQL>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;

备库启动到open状态.


DB在进行alter database recover managed standby database using current logfile disconnect from session后产生MRP进程,进行logfile恢复. RFS进程为接受主库日志功能.


现在为止查看主库状态, 保护模式为最大性能模式, DB角色为PRIMARY

SQL>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;

查看备状态,保护模式为最大性能模式, DB角色为PHYSICAL STANDBY.

此处我们要搭建DG如果保护模式为最大可用性模式,故下面需要做DG模式转换.

2.6 查看主备库日志是否同步.

sql>archive log list;
sql>select unique(thread#),max(sequence#) over(partition by thread#) from v$archived_log;
切归档之前---主库日志sequence
切归档之前----备库日志sequence, 查询主备库日志sequence在切归档前同步.

手动切归档测试.
切归档之后-----主库日志sequence
切归档之后----备库日志sequence

主/备库日志是同步的.


2.7 最大可用性模式
SQL>alter database set standby database to maximize availability;
主库进行切换.

查看备库已经由最大性能模式切换为最大可用性模式.

2.8 主备库switchover切换测试.
SQL>alter database commit to switchover to physical standby;
SQL>shutdown immediate;
SQL>startup
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE disconnect from session;

备库切主库.
SQL>alter database recover managed standby database cancel;
SQL>alter database commit to switchover to primary;
SQL>shutdown immediate;
SQL>startup
SQL>select NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,FORCE_LOGGING,DATABASE_ROLE,DB_UNIQUE_NAME from v$database;

注:
做RMAN DUPLICATE时,主/备库数据库目录结构即使相同,参数.db_file_name_convert与 log_file_name_convert也需要配置, 如果不配置,最后做rman duplicate时会报错 "ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed ORA-00312: online log N thread P:' xxxxx';" (N为log日志组号,P为thread号,xxxxx为日志路径代替).
主/备库duplicate后, 备库TNSNAMES.ORA中多了一个LISTENER_HSIDB主库的监听信息,rrfuwu-28为主库的HOSTNAME,故此处可以写成主库IP,或把rrfuwu-28的域名解析写到备库/etc/hosts中.此处如果忘记修改,备库做主备库切换,startup时会报错. "ORA-00119: invalid specification for system parameter LOCAL_LISTENER "

SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=rrfuwu-28)(PORT=1525))'