Oracle RAC+ADG集群配置

最新推荐文章于 2024-07-31 09:43:48 发布

DBhanG

最新推荐文章于 2024-07-31 09:43:48 发布

阅读量3.2k

点赞数 1

文章标签：数据库 linux 运维 oracle centos

本文链接：https://blog.csdn.net/baoyuhang0/article/details/105884783

版权

主库配置：

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

备库配置:

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='/u01/app/oracle/oradata/stddb/','+DATA/racdb/datafile/'
log_file_name_convert='/u01/app/oracle/oradata/stddb/','+DATA/racdb/onlinelog/','/u01/app/oracle/oradata/arclog/',
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	   ONLINE
	 1	   ONLINE
	 2	   ONLINE
	 2	   ONLINE
	 3	   ONLINE
	 3	   ONLINE
	 4	   ONLINE
	 4	   ONLINE
	 5	   STANDBY
	 6	   STANDBY
	 7	   STANDBY

    GROUP# STATUS  TYPE
---------- ------- -------
	 8	   STANDBY
	 9	   STANDBY
	10	   STANDBY





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

    GROUP#    THREAD#
---------- ----------
	 5	        1
	 6	        1
	 7	        1
	 8	        2
	 9	        2
	10	    2
在主库添加standby database的原因：
1.在duplicate命令复制到standby database后，备库发现控制中存在standby log,会自动执行clearing操作。
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
方式1：
RMAN> duplicate target database for standby database nofilenamecheck
方式2：
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		119
	 6	    1 UNASSIGNED	  0
	 7	    1 UNASSIGNED	  0
	 8	    2 ACTIVE		 57
	 9	    2 UNASSIGNED	  0
	10	    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 VALID
	 2 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

DBhanG

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫