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 Agent
LISTENER_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 Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
ENABLE_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 nocatalog
duplicate target database for standby from active database nofilenamecheck;

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

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

#/bin/sh
export ORACLE_SID=EGMMDR
rman target sys/AE8pfChcG0BBGlL73DW0@EGAA1 auxiliary sys/AE8pfChcG0BBGlL73DW0@EGAADR nocatalog log '/home/oracle/rman.log' <<EOF
run {
allocate channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database for standby from active database;
}
EOF

后台执行脚本 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 1
GROUP 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=EGAADR
egaa1.FAL_CLIENT=egaa1
egaa2.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 ASYNC
VALID_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 ASYNC
VALID_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;