导航：首页 > 数据库 >

DG实施方案（主库为双节点rac）--rman duplicate方式

发表于：2024-11-12 作者：千家信息网编辑

千家信息网最后更新 2024年11月12日，环境信息：操作系统版本： AIX6.1数据库版本： ORACLE 11.2.0.3（psu5）主库为双节点rac1.检查数据库是否支持Data Guard（只有企业版才支持DG）SQL> select

千家信息网最后更新 2024年11月12日DG实施方案（主库为双节点rac）--rman duplicate方式

环境信息：
操作系统版本： AIX6.1
数据库版本： ORACLE 11.2.0.3（psu5）
主库为双节点rac

1.检查数据库是否支持Data Guard（只有企业版才支持DG）
SQL> select * from v$option where parameter = 'Managed Standby';

2.修改主库为归档模式及force logging状态
1）SQL> archive log list;
如果未开归档，开启归档模式

alter system set log_archive_dest_2='location=/archlog/egaa';alter system set log_archive_format='egaa_%t_%s_%r.arch' scope=spfile; --静态参数，重启后生效shutdown immediate;startup mount;alter database archivelog;alter database open;archive log list;

2）打开force logging

SQL> alter database force logging;Database altered.

3.创建备库pfile文件
在主库上创建pfile，修改，并添加DG备库所有参数，然后传至备库
SQL> create pfile='/data01/pfileegaa' from spfile;
1）备库必须要添加的参数
DB_UNIQUE_NAME;LOG_ARCHIVE_DEST_1;FAL_SERVER;FAL_CLIENT;STANDBY_FILE_MANAGEMENT=AUTO;DB_FILE_NAME_CONVERT;LOG_FILE_NAME_CONVERT
2）根据pfile中涉及到路径需要提前在备库主机上建好（）
如主库*.audit_file_dest='/apps/oracle/admin/egaadr/adump'
我们在备库需要建 mkdir -p /apps/oracle/admin/egaadr/adump
cd /apps/oracle/admin
chown -R oracle:oinstall egaadr
chmod -R 775 egaadr

修改前参数文件（此处就不列了）

修改后参数文件

*.audit_file_dest='/apps/oracle/admin/egaadr/adump'*.audit_trail='NONE'*.compatible='11.2.0.0.0'*.control_files='+DATA1/egaadr/controlfile/control01.ctl','+DATA1/egaadr/controlfile/control02.ctl'#Set by RMAN*.core_dump_dest='/apps/oracle/diag/rdbms/egaadr/egaadr/cdump'*.db_block_size=8192*.db_cache_size=17179869184*.db_create_file_dest='+DATA1'*.db_domain=''*.db_file_name_convert='+DATA2/EGAADB/DATAFILE','+DATA1/EGAADR/DATAFILE','+DATA2/EGAADB/TEMPFILE','+DATA1/EGAADR/TEMPFILE'*.log_file_name_convert='+DATA2/EGAADB/ONLINELOG','+DATA1/EGAADR/ONLINELOG','+RECODG/egAAdb/onlinelog','+DATA1/EGAADR/ONLINELOG1'*.db_name='EGAA'*.db_recovery_file_dest='+DATA1'*.db_recovery_file_dest_size=307000M*.db_unique_name='EGAADR'*.deferred_segment_creation=FALSE*.diagnostic_dest='/apps/oracle/'*.fal_client='EGAADR'*.fal_server='EGAADB1,EGAADB2'*.instance_name='egaadr'*.large_pool_size=536870912*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.48.81)(PORT=1521))))'*.log_archive_config='dg_config= (EGAADB,EGAADR)'*.log_archive_dest_1='LOCATION=+DATA1/egaadr/archlog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EGAADR'*.log_archive_dest_state_1='ENABLE'*.log_archive_dest_state_2='ENABLE'*.log_archive_format='egaadr_%t_%s_%r.arc'*.log_buffer=48857088# log buffer update*.open_cursors=1000*.optimizer_dynamic_sampling=2*.optimizer_mode='ALL_ROWS'*.parallel_max_servers=480*.pga_aggregate_target=12884901888*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora*.processes=2000*.query_rewrite_enabled='TRUE'*.remote_listener='drdb-scan:1521'*.remote_login_passwordfile='EXCLUSIVE'*.result_cache_max_size=52448K*.sessions=3072*.sga_max_size=25769803776*.sga_target=25769803776*.shared_pool_size=4294967296*.skip_unusable_indexes=TRUE*.standby_file_management='AUTO'*.undo_management='AUTO'*.undo_retention=5400*.undo_tablespace='UNDOTBS1'##注意参数文件中指定的目录在备库要存在如：db_file_name_convert和log_file_name_convert参数指定的路径要存在##去掉rac数据库的相关参数

4）根据修改后的pfile创建备库spfile
export ORACLE_SID=egaadr
sqlplus / as sysdba
create spfile from pfile;
--用新生成的spfile看是否能够成功启动实例

4.生成备库的密码文件
scp主库密码文件到备库，并改名
如果主库没有密码文件，需要新建
orapwd file=/oracle/app/oracle/product/v11.2.0.3/db_1/dbs/orapwegaa password=oracle entries=5 ignorecase=y
--如果新建后连接时报密码错误，我们可以把主库其中一个节点密码文件scp到另一个节点和备库并改名使用

5.配置主备库监听及net服务

1）listener

一般建库后都会配置监听我们无需再配置
--备库（因为安装了cluster，所以用的是cluster的监听）
lsnrctl status 查看监听文件位置，并在监听文件中加入如下类容（注意两个节点都进行配置）

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by AgentLISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by AgentLISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by AgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by AgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by AgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by AgentENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent##注意集群安装完毕以后，上面部分内容在监听中已经存在SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1)(PROGRAM = extproc))(SID_DESC =(ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1)(SID_NAME = egaa1)))LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.1)(PORT = 1521)(IP = FIRST))))

2）tns
主备库tnsnames.ora文件中加入如下部分

EGAA1 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.230)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = egaa) (UR = A)))EGAA2 =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.231)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = egaa) (UR = A)))EGAADR =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.81)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = egaa) (UR = A)))--也可以在主库的两个节点只配一个连接串，如下：EGAA =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.230)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.231)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = egaa) (UR = A)))

6. 测试网络连接
1）查看监听状态是否正常
lsnrctl status
2) 测试连接串是否能正常连接到指定数据库
tnsping EGaaDR
tnsping EGaa1
tnsping EGaa2
sqlplus sys/AE8pfChcG0BBGlL73DW0@egaa1 as sysdba
sqlplus sys/AE8pfChcG0BBGlL73DW0@egaa2 as sysdba
sqlplus sys/AE8pfChcG0BBGlL73DW0@egaadr as sysdba

7. 复制备库

有两种1:rman在线复制 2:rman备份异机恢复
此处因为库比较小，我们第一种方法，这种方法比较简单。

方法1：rman在线复制（不需要备份主库）
此种方式仅适用于ORACLE 11G，可以自动备份datafile,control等文件到备库，在复制过程主库仍可正常运行，但复制过程时间较长，会占用一定的网络资源。
1）将备库启动到nomount状态
export ORACLE_SID=egaadr
sqlplus / as sysdba
startup nomount;

2)在备库上执行如下命令

rman target sys/FWNgTA4XlcUuDXDiQAdT@egaa1 auxiliary sys/FWNgTA4XlcUuDXDiQAdT@EGAADR nocatalogduplicate target database for standby from active database nofilenamecheck;

--如果主备库文件路径不变，要加nofilenamecheck（否则会报错）

--我们可以写一个脚本放在后台运行，在预计需要时间过后查看相关日志复制是否成功就行，脚本如下：

#/bin/shexport ORACLE_SID=EGMMDRrman target sys/AE8pfChcG0BBGlL73DW0@EGAA1 auxiliary sys/AE8pfChcG0BBGlL73DW0@EGAADR nocatalog log '/home/oracle/rman.log' <后台执行脚本 nohup rman.sh &
8. 添加standby log
--为了日后切换，建议为主库也添加standby log
--注意stnadby log的大小（同主库redolog相同大小）
--注意添加合适数量的standby log
--standy log 的推荐数目为=（每个线程的日志文件的最大数目＋1）×线程最大数目
--ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 3('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;
--alter database drop logfile group 7;--删除日志组
alter database add standby logfile thread 1GROUP 26('+data1') SIZE 500M,GROUP 27('+data1') SIZE 500M,GROUP 28('+data1') SIZE 500M,GROUP 29('+data1') SIZE 500M,GROUP 30('+data1') SIZE 500M,GROUP 31('+data1') SIZE 500M,GROUP 32('+data1') SIZE 500M,GROUP 33('+data1') SIZE 500M,GROUP 34('+data1') SIZE 500M;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
检查是否成功创建
9.配置主库DG参数
主库需要配置的参数为DB_UNIQUE_NAME;LOG_ARCHIVE_CONFIG;LOG_ARCHIVE_DEST_2;REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
--为了方便以后切主库也应该添加备库所需要的参数
*.DB_UNIQUE_NAME=EGAA*.FAL_SERVER=EGAADRegaa1.FAL_CLIENT=egaa1egaa2.FAL_CLIENT=egaa2*.STANDBY_FILE_MANAGEMENT=AUTO*.DB_FILE_NAME_CONVERT='+DATA1/EGAADR/DATAFILE','+DATA2/EGAA/DATAFILE','+DATA1/EGAADR/TEMPFILE','+DATA2/EGAA/TEMPFILE'*.LOG_FILE_NAME_CONVERT='+DATA1/EGAADR/ONLINELOG','+DATA2/EGAA/ONLINELOG','+DATA1/EGAADR/ONLINELOG1','+RECODG/EGAA/onlinelog'*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(EGAA,EGAADR)'*.log_archive_dest_1='location=+RECODG/egaa/archivelog'*.log_archive_dest_2='SERVICE=EGAADR LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=EGAADR'alter system set fal_server='EGAADR';alter system set fal_client='EGAA1' sid='EGAA1';alter system set fal_client='EGAA2' sid='EGAA2';alter system set standby_file_management=auto;alter system set db_file_name_convert='+DATA1/EGAADR/DATAFILE','+DATA2/EGAA/DATAFILE','+DATA1/EGAADR/TEMPFILE','+DATA2/EGAA/TEMPFILE' scope=spfile;alter system set log_file_name_convert='+DATA1/EGAADR/ONLINELOG','+DATA2/EGAA/ONLINELOG','+DATA1/EGAADR/ONLINELOG1','+RECODG/EGAA/onlinelog' scope=spfile;alter system set log_archive_config='DG_CONFIG=(EGAA,EGAADR)';alter system set log_archive_dest_2='SERVICE=EGAADR LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=EGAADR';
10.在备库上启动恢复
启动恢复
alter database recover managed standby database using current logfile disconnect;alter database recover managed standby database cancel;
11.检查DG状态是否正常
--主库切换日志，观察DG能否正常应用日志****切换前检查主备库所有参数
1）在备库查看 data guard 为哪种日志接受方式，以及当前被应用的日志
select process,client_process,sequence#,status from v$managed_standby;
2）在备库查看新的归档日志有没有正常传输过来，并被应用
select THREAD#,SEQUENCE#,ARCHIVED,APPLIED,DELETED,STATUS from v$archived_log order by 1,2;
3）查看主备库的alert日志，是否正常
4）查看延时
alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';
select name,value from v$dataguard_stats where name in ('apply lag','apply finish time');
5)查看日志是否有gap
select * from V$ARCHIVE_GAP;
--注意：
过程中遇到主库不往log_archive_dest_2传日志，alert里也没有告警
后来在主库上执行如下命令后，恢复日志传送
alter system set log_archive_dest_state_2 = 'defer' sid='*' scope=both;
alter system set log_archive_dest_state_2='enable' sid='*' scope=both;