Oracle RAC+ADG集群配置

主库配置：
hostname node1.neves.com node2.neves.com
Public IP 80.8.29.1 80.8.29.2
Virtual IP 10.20.89.1 10.20.89.2
Instance racdb1 racdb2
DB name racdb
DATA file +DATA/racdb/datafile/
Control file +DATA/racdb/controlfile/
+FRA/racdb/controlfile/
Redo Log file +DATA/racdb/onlinelog/
+FRA/racdb/onlinelog/
db_unique_name racdb
service_names racdb.neves.com
Oracle_Version 11.2.0.4.0

Standby 端:
hostname adg.neves.com
IP 80.8.29.50
Oracle_version 11.2.0.4.0
Instance stddb
DB name racdb
DB_unique_name stddb
service_names Stddb.neves.com
DATA file /u01/app/oracle/oradata/stddb/
Control file /u01/app/oracle/oradata/stddb/
Redo Log file /u01/app/oracle/oradata/stddb/
/u01/app/oracle/oradata/arclog/
此arclog不是存放归档日志的，
只是按照RAC中两个不同的日志组路径，对应设置的。

查看集群资源情况：
[grid@node1 ~]$ crsctl status res -t
确保资源online状态。

SQL> show parameter spfile;

NAME TYPE VALUE

spfile string +DATA/racdb/spfileracdb.ora

修改RAC参数文件：
SQL> create pfile=’/home/oracle/racdbpfile’ from spfile=’+DATA/racdb/spfileracdb.ora’

RAC主库参数文件：
racdb1.__db_cache_size=276824064
racdb2.__db_cache_size=289406976
racdb1.__java_pool_size=4194304
racdb2.__java_pool_size=4194304
racdb1.__large_pool_size=8388608
racdb2.__large_pool_size=8388608
racdb1.__pga_aggregate_target=348127232
racdb2.__pga_aggregate_target=335544320
racdb1.__sga_target=486539264
racdb2.__sga_target=499122176
racdb1.__shared_io_pool_size=0
racdb2.__shared_io_pool_size=0
racdb1.__shared_pool_size=188743680
racdb2.__shared_pool_size=188743680
racdb1.__streams_pool_size=0
racdb2.streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/racdb/adump’
*.audit_trail=‘DB’
*.cluster_database=true
*.compatible=‘11.2.0.0.0’
*.control_files=’+DATA/racdb/controlfile/current.260.1037590513’,’+FRA/racdb/controlfile/current.256.1037590515’
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=‘neves.com’
*.db_name=‘racdb’
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=4558159872
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=racdbXDB)’
*.event=’’
racdb1.instance_number=1
racdb2.instance_number=2
*.log_archive_format=’%t%s%r.dbf’
*.memory_target=834666496
*.open_cursors=300
*.processes=150
*.remote_listener=‘rac-scan.neves.com:1521’
*.remote_login_passwordfile=‘EXCLUSIVE’
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace=‘UNDOTBS1’
racdb2.undo_tablespace=‘UNDOTBS2’

db_unique_name=racdb
log_archive_config=‘DG_CONFIG=(racdb,stddb)’
log_archive_dest_1 = ‘LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb’
log_archive_dest_2 = ‘SERVICE=stddb LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stddb’
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert=’+DATA/racdb/datafile/’,’/u01/app/oracle/oradata/stddb/’
log_file_name_convert=’+DATA/racdb/onlinelog/’,’/u01/app/oracle/oradata/stddb/’
standby_file_management=AUTO
FAL_SERVER=stddb
racdb1.fal_client=racdb1
racdb2.fal_client=racdb2

关闭RAC两节点实例：
[oracle@node1 ~]$ srvctl stop database -d racdb

SQL>create spfile=’+DATA/racdb/spfileracdb.ora’ from pfile=’/home/oracle/racdbpfile’

启动RAC两节点实例：
[oracle@node1 ~]$ srvctl start database -d racdb

查看关键参数是否生效：
log_file_name_convert
db_file_name_convert
FAL_SERVER
log_archive_dest_1
log_archive_dest_2
Service_name

备库参数：
stddb.__db_cache_size=352321536
stddb.__java_pool_size=4194304
stddb.__large_pool_size=8388608
stddb.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
stddb.__pga_aggregate_target=335544320
stddb.__sga_target=503316480
stddb.__shared_io_pool_size=0
stddb.__shared_pool_size=125829120
stddb.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/stddb/adump’
*.audit_trail=‘db’
*.compatible=‘11.2.0.4.0’
*.control_files=’/u01/app/oracle/oradata/stddb/control01.ctl’,’/u01/app/oracle/oradata/stddb/control02.ctl’
*.db_block_size=8192
*.db_domain=‘neves.com’
*.db_name=‘racdb’
*.db_recovery_file_dest=’/u01/app/oracle/fast_recovery_area’
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest=’/u01/app/oracle’
*.memory_target=838860800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE’
*.sessions=170
*.undo_tablespace=‘UNDOTBS1’

db_unique_name=‘stddb’
log_archive_config=‘DG_CONFIG=(racdb,stddb)’
log_archive_dest_1 = ‘LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stddb’
log_archive_dest_2 = ‘SERVICE=racdb1 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb’
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
db_file_name_convert=’+DATA/racdb/datafile/’,’/u01/app/oracle/oradata/stddb/’
log_file_name_convert=’+DATA/racdb/onlinelog/’,’/u01/app/oracle/oradata/stddb/’,’+FRA/racdb/onlinelog/’,’/u01/app/oracle/oradata/arclog/’
standby_file_management=AUTO
FAL_SERVER=‘racdb1’,‘racdb2’
fal_client=‘stddb’

主库.备库tnsnames.ora：
racdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 80.8.29.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb.neves.com)
)
)
racdb2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 80.8.29.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb.neves.com)
)
)
stddb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 80.8.29.50)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stddb.neves.com)
)
)

备库需要配置静态注册：
nomount为block状态下，只有配置静态注册可以连接。
Listener.ora

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stddb.neves.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_home1)
(SID_NAME = stddb)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adg.neves.com)(PORT = 1521))
)

将备库启动为nomount状态
SYS@stddb>Start nomout

在主库添加standby redolog：
SQL> select group#,thread#,members,bytes from v$log;
GROUP# THREAD# MEMBERS BYTES
1 1 2 52428800
2 1 2 52428800
3 2 2 52428800
4 2 2 52428800

在主库添加standby redolog：
alter database add standby logfile thread 1 group 5 ‘+DATA/racdb/onlinelog/group_5.1’ size 50M

alter database add standby logfile thread 1 group 6 ‘+DATA/racdb/onlinelog/group_6.1’ size 50M

alter database add standby logfile thread 1 group 7 ‘+DATA/racdb/onlinelog/group_7.1’ size 50M

alter database add standby logfile thread 2 group 8 ‘+DATA/racdb/onlinelog/group_8.1’ size 50M

alter database add standby logfile thread 2 group 9 ‘+DATA/racdb/onlinelog/group_9.1’ size 50M

alter database add standby logfile thread 2 group 10 ‘+DATA/racdb/onlinelog/group_10.1’ size 50M

SQL> select group#,status,type from v$logfile;

GROUP# STATUS  TYPE

 1	   ONLINE1	   ONLINE2	   ONLINE2	   ONLINE3	   ONLINE3	   ONLINE4	   ONLINE4	   ONLINE5	   STANDBY6	   STANDBY7	   STANDBYGROUP# STATUS  TYPE

8	   STANDBY9	   STANDBY10	   STANDBY

SQL> select group#,thread# from v$standby_log;

GROUP#    THREAD#

5	        16	        17	        18	        29	        210	        2

在主库添加standby database的原因：
1.在duplicate命令复制到standby database后，所有的standby redo会被复制到备库。
2.角色切换时，需要在主库生成standby log，也是为了减少角色切换的时间。

进行主库备份：
[oracle@node1 ~]$ rman target /
connected to target database: RACDB (DBID=1035207281)

RMAN> backup as compressed backupset database include current controlfile for standby;

[oracle@node1 ~]$ rman target sys/oracle@racdb1 auxiliary sys/oracle@stddb

RMAN> duplicate target database for standby from active database nofilenamecheck

查看主库归档信息：
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 110
Next log sequence to archive 111
Current log sequence 111

查看备库归档信息：
SYS@stddb>select instance_name,status from v$instance;

INSTANCE_NAME STATUS

stddb MOUNTED

SYS@stddb>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 111
Next log sequence to archive 0
Current log sequence 111

进行主库日志切换，查看备库是否归档。
SYS@stddb>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 111
Next log sequence to archive 0
Current log sequence 112

启动日志应用服务：
SYS@stddb>alter database recover managed standby database disconnect from session;

SYS@stddb>alter database recover managed standby database using current logfile disconnect from session;

SYS@stddb>select group#,thread#,status,sequence# from v$standby_log;

GROUP#    THREAD# STATUS	  SEQUENCE#

5	    1 ACTIVE		1196	    1 UNASSIGNED	  07	    1 UNASSIGNED	  08	    2 ACTIVE		 579	    2 UNASSIGNED	  010	    2 UNASSIGNED	  0

SYS@stddb>select process,client_process,status,sequence# from v$managed_standby;

PROCESS CLIENT_P STATUS SEQUENCE#

ARCH ARCH CLOSING 56
ARCH ARCH CLOSING 117
ARCH ARCH CONNECTED 0
ARCH ARCH CLOSING 118
RFS ARCH IDLE 0
RFS ARCH IDLE 0
RFS UNKNOWN IDLE 0
RFS UNKNOWN IDLE 0
RFS UNKNOWN IDLE 0
RFS UNKNOWN IDLE 0
RFS LGWR IDLE 57

PROCESS CLIENT_P STATUS SEQUENCE#

RFS LGWR IDLE 119
MRP0 N/A WAIT_FOR_LOG 57

select dest_id,dest_name,target,status from v$archive_Dest;
DEST_ID DEST_NAME TARGET STATUS

1 LOG_ARCHIVE_DEST_1	PRIMARY VALID2 LOG_ARCHIVE_DEST_2	STANDBY VALID

RAC集群双节点搭建DG与单实例的主要区别：
日志线程从单线程变为双线程
会同时接收以及应用两个节点的重做日志信息。

与DG相关的动态性能视图：
V$ archived_log
V$ archive_dest_status
V$ archive_dest
V$ managed_standby
V$ standby_log
V$ database
V$ dataguard_status