oracle 11g 生产环境dg配置全过程

环境介绍：
数据库版本：11.2.0.4
服务器操作系统：主库 centos6
备库 centos7
操作方案
生产环境，因为涉及到重启数据库问题，时间安排在凌晨，为了减少凌晨工作量，决定操作方案：
备库服务器装好数据库软件，利用主库rman备份先进行恢复，然后白天追归档日志，凌晨之前追齐归档日志，完成参数修改，凌晨之后，重启主库，打开备库，打开日志应用，完成dg配置。

主库rman脚本：

export ORACLE_SID=orclexport ORACLE_HOME=/appl/oracle/product/11.2.0.4/db_1DATE=`date +%Y%m%d`mkdir -p backup_database/rmanbk/$DATERMAN=$ORACLE_HOME/bin/rmanRMAN_LOG_FILE=/backup_database/rmanbk/$DATE/backup_db_level0.`date +%Y%m%d%H%M%S`.log$RMAN target   msglog $RMAN_LOG_FILE append << EOFrun{allocate channel cha1 type disk;allocate channel cha2 type disk;crosscheck archivelog all;delete noprompt expired archivelog all;crosscheck backup;delete noprompt expired backup;sql 'alter system archive log current';backupincremental level 0database format '/backup_database/rmanbk/$DATE/db_level0_%d_%s_%p_%T' plus archivelog format '/backup_database/rmanbk/$DATE/arch_%d_%s_%p_%T' delete all input;backup current controlfile format '/backup_database/rmanbk/$DATE/cntrl_%d_%I_%s_%p_%T';report obsolete;delete noprompt obsolete;release channel cha1;release channel cha2;}exitEOFecho `date` >> $RMAN_LOG_FILE
复制

主库操作：
1 数据库强制归档开启，添加standby log

SQL> ALTER DATABASE FORCE LOGGING;SQL>alter database set standby database to maximize availability;SQL>alter database add standby logfile group x ('/xxxx/xxxx/xxxx/xxxx.log') size 500M;
复制

standby log需要比主库日志组多一组

2 生成pfile文件

SQL> create pfile from spfile;
复制

3 修改pfile文件

vim $ORACLE_HOME/dbs/initorcl.ora
复制

修改log_archive_dest_1

*.log_archive_dest_1='LOCATION=/oracle/archlog/ valid_for=(all_logfiles,all_roles)'
复制

添加以下内容

*.db_unique_name=orcl*.fal_server=orcl*.fal_client=orcldg*.standby_file_management=auto*.db_file_name_convert='/oracle/datafile/orcl/','/oracle/oracle/oradata/orcl/'*.log_file_name_convert='/oracle/datafile/orcl/onlinelog/','/oracle/oracle/oradata/orcl/'*.log_archive_config='dg_config=(orcl,orcldg)'*.log_archive_dest_2='service=orcldg LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=orcldg'*.log_archive_dest_state_2='ENABLE'*.log_archive_dest_state_1='ENABLE'
复制

4 修改$ORACLE_HOME/network/admin/tnsname.ora

ORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = ip1)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )ORCLDG =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = ip2)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcldg)    )  )
复制

ip1为主库服务器ip,ip2为备库服务器ip

5 将主库$ORACLE_HOME/dbs和$ORACLE_HOME/network/admin下所有文件同步到备库环境相应目录。

备库：
1.根据备库自身目录环境修改参数文件

vim $ORACLE_HOME/dbs/initorcl.ora
复制

其中和dg相关的修改完后的内容

*.db_unique_name=orcldg*.fal_server='orcl'*.fal_client='orcldg'*.standby_file_management=auto*.db_file_name_convert='/oracle/datafile/orcl/','/oracle/oracle/oradata/orcl/'*.log_file_name_convert='/oracle/datafile/orcl/onlinelog/','/oracle/oracle/oradata/orcl/'*.log_archive_config='dg_config=(orcl,orcldg)'*.log_archive_dest_2='service=orcl LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=orcl'*.log_archive_dest_state_2='ENABLE'*.log_archive_dest_state_1='ENABLE
复制

主要是参数dg_unique_name和log_archive_dest_2

2 修改listener.ora

SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (ORACLE_HOME = oracle/oracle/product/11.2.0/db_1)      (SID_NAME = orcl)    )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))    )    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )  )ADR_BASE_LISTENER = oracle/oracle
复制

oracle_home，host和ADR_BASE_LISTENER 根据实际情况修改

3 利用参数文件将数据库启动至nomount

SQL> startup pfile='/appl/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora' nomount;
复制

4 利用主库备份恢复控制文件

 rman target ; restore standby controlfile from '/xxx/xxx/xxx/cntrl_ORCL_1397393707_12984_1_20180321'; mount database;
复制

5 恢复备库数据库
参照http://www.cndba.cn/asker/article/317 四~七恢复过程

6 追归档日志
将主库在备份之后新生成的归档日志同步到备库的归档目录中
备库，追齐归档

RMAN> recover database until sequence 40326;
复制

凌晨操作：
主库，利用新的参数文件重启数据库
备库，添加standby log

SQL> alter database add standby logfile;SQL> alter database recover managed standby database cancel;SQL> alter database open;SQL> alter database recover managed standby database using current logfile disconnect from session;
复制

查看主备库alert日志，检查有没有报错，主库创建测试表,插入删除数据，验证备库是不是正常同步。
配置备库归档删除脚本

export ORACLE_SID=orclexport ORACLE_HOME=/oracle/oracle/product/11.2.0/db_1RMAN=$ORACLE_HOME/bin/rmanRMAN_LOG_FILE=/oracle/scripts/delete_archivelog/delete_archivelog.`date +%Y%m%d%H%M%S`.log$RMAN  target   msglog $RMAN_LOG_FILE append << EOFrun{crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate - 1/24';
}
exit
EOF
echo `date` >> $RMAN_LOG_FILE
复制