RAC dg搭建

1、主库预先设置

startup nomount
alter database archivelog;
alter system set db_recovery_file_dest_size=500G;
alter system set db_recovery_file_dest='+ARCH';
alter database open;

2、主库开启强制附加日志

SQL>select name , open_mode, log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
--------------------------------------------
RACDB READ WRITE ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL>select name , open_mode, log_mode,force_logging from gv$database;
NAME OPEN_MODE LOG_MODE FOR
--------------------------------------------
RACDB READ WRITE ARCHIVELOG YES

3、主库全备数据库

--RAC主节点和备节点设置共享文件系统，/home/oracle/rman

[oracle@racnode1 rman]$ rman target /
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup database format '/home/oracle/rman/FULL_%U.bak';
backup archivelog all format '/home/oracle/rman/ARC_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

4、主库上创建备库standby控制文件

alter database create standby controlfile as'/home/oracle/rman/standby.ctl';

5、主库创建物理备库初始化参数文件

create pfile ='/home/oracle/rman/initphyracdb.ora'from spfile;

6、主库拷贝密码文件到备库

scp orapwracdb1 oracle@172.16.1.21:/u01/oracle/product/11.2.0/db_1/dbs/orapwracdg1
scp orapwracdb1 oracle@172.16.1.22:/u01/oracle/product/11.2.0/db_1/dbs/orapwracdg2

7、备库创建日志目录

mkdir -p /u01/app/oracle/admin/racdb/adump
cd /u01/app/
chown -R oracle:oinstall oracle

8、拷贝主库上刚创建的初始化参数文件到备库

racdg2.__db_cache_size=272629760
racdg1.__db_cache_size=322961408
racdg2.__java_pool_size=4194304
racdg1.__java_pool_size=4194304
racdg2.__large_pool_size=4194304
racdg1.__large_pool_size=8388608
racdg1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdg2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdg2.__pga_aggregate_target=335544320
racdg1.__pga_aggregate_target=289406976
racdg2.__sga_target=499122176
racdg1.__sga_target=545259520
racdg2.__shared_io_pool_size=0
racdg1.__shared_io_pool_size=0
racdg2.__shared_pool_size=209715200
racdg1.__shared_pool_size=201326592
racdg2.__streams_pool_size=0
racdg1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+DATA/racdg/controlfile/control.ctl'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/racdb/','+DATA/racdg/'
*.db_name='racdb'
*.db_recovery_file_dest_size=1073741824
*.db_recovery_file_dest='+ARCH'
*.db_unique_name='racdg'
*.fal_client='racdg'--指向自己
*.fal_server='racdb'--指向对端
racdg1.instance_number=1
racdg2.instance_number=2
*.log_archive_config='dg_config=(racdb,racdg)'
*.log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'--本地归档
*.LOG_ARCHIVE_DEST_2='SERVICE=racdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'--远程归档
*.log_archive_format='ARC_%t_%S_%r.arc'
*.log_file_name_convert='+DATA/racdb/','+DATA/racdg/'
*.db_file_name_convert='+DATA/racdb/','+DATA/racdg/'
*.open_cursors=300
*.processes=150
*.remote_listener='scan:1521'
*.remote_login_passwordfile='exclusive'
*.service_names='racdg'
*.standby_file_management='auto'
racdg2.thread=2
racdg1.thread=1
racdg2.undo_tablespace='UNDOTBS2'
racdg1.undo_tablespace='UNDOTBS1'

 

9、主备库2节点均需配置tnsname.ora，添加如下

RACDB =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.3)(PORT =1521))
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.4)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
racdb1 =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.3)(PORT =1521))
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.4)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
racdb2 =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.4)(PORT =1521))
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.3)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
racdg =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.23)(PORT =1521))
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.24)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg)
)
)
racdg1 =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.23)(PORT =1521))
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.21)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg)
)
)
racdg2 =
(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.24)(PORT =1521))
(ADDRESS =(PROTOCOL = TCP)(HOST =172.16.1.22)(PORT =1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg)
)
)

10、备库启动到nomount

startup pfile='/home/oracle/rman/initphydb.ora' nomount;

11、备库恢复控制文件

restore standby controlfile from '/home/oracle/rman/standby.ctl';

12、备库启动到mount

alter database mount;

13、恢复备库

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database ;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

14、添加备库standby日志文件，建议比日志文件多一组

alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M,group 7 size 50M;
alter database add standby logfile thread 2 group 8 size 50M,group 9 size 50M,group 10 size 50M;

15、主库参数调整

alter system set log_archive_config='dg_config=(racdb,racdg)';
alter system set log_archive_dest_1='LOCATION=+ARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'；
alter system set log_archive_dest_2='SERVICE=racdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdg';
alter system set log_archive_dest_state_2=enable;

16、备库应用日志

alter database recover managed standby database using current logfile disconnect from session;

17、开启ADG

alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

18、配置主备切换

alter system set log_archive_config='DG_CONFIG(racdb,racdg)';
alter system set fal_client='racdb';--指向自己
alter system set fal_server='racdg';--指向对端
alter database add standby logfile thread 1 group 5 size 50M,group 6 size 50M,group 7 size 50M;
alter database add standby logfile thread 2 group 8 size 50M,group 9 size 50M,group 10 size 50M;
alter system set db_file_name_convert='+DATA/racdg/','+DATA/racdb/' sid='*' scope=spfile;
alter system set log_file_name_convert='+DATA/racdg/','+DATA/racdb/' sid='*' scope=spfile;
set linesize 600
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

19、主备切换测试

主备切换，RAC主备切换需要停止实例2

主库节点2：[grid@dm02db01 ~]$ srvctl stop instance -d racdg -i racdg1
备库节点2：[grid@dm01db01 ~]$ srvctl stop instance -d racdb -i racdb1
--主库执行
lsnrctl stop listener
alter database commit to switchover to physical standby with session shutdown;--会关闭实例
startup
alter database recover managed standby database using current logfile disconnect from session;
--备库执行
lsnrctl stop listener
alter database recover managed standby database cancel;alter database commit to switchover to primary with session shutdown;
alter database open;
lsnrctl start listener

--检查
set linesize 600
select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

 

20、DG相关查询视图

select*from v$archive_gap;
select process, client_process, sequence#, status from v$managed_standby;
select sequence#, first_time, next_time, applied from v$archived_log;
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;
select thread#, max (sequence#) from v$log_history group by thread#;
select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;