RAC+DG的环境搭建部署（第二篇)
【转载声明】本文为转载文章，原作者「李华荣」，版权归原作者所有。 原文链接：https://mp.weixin.qq.com/s/EY9TMlt7–M2dHliSUvX2g
公众号：杨建荣的学习笔记 · 作者：李华荣 · 发布：2016-02-24 22:35:48 · 原文链接
1.1.1 主库前期准备
1.1.1.1 force logging + archivelog
1. 创建一个用户lhr用于测试
2. 修改主库为force logging模式
3. 修改主库为归档模式
[ZFXXX3:oracle]:/oracle>export ORACLE_SID=DGPRI1
[ZFXXX3:oracle]:/oracle>sqlplus / as sysdba
OracleDatabase 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
SYS@DGPRI1>create user lhr identified by lhr;
Usercreated.
SYS@DGPRI1>grant dba to lhr;
Grantsucceeded.
SYS@DGPRI1>set line 9999
SYS@DGPRI1>select name , open_mode,log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
—————————– ———— — —————- ——————–
TESTDG READ WRITE NOARCHIVELOG NO PRIMARY NOT ALLOWED
TESTDG READ WRITE NOARCHIVELOG NO PRIMARY NOT ALLOWED
SYS@DGPRI1>alter database force logging;
Databasealtered.
SYS@DGPRI1>select name , open_mode,log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
—————————– ———— — —————- ——————–
TESTDG READ WRITE NOARCHIVELOG YESPRIMARY NOT ALLOWED
TESTDG READ WRITE NOARCHIVELOG YESPRIMARY NOT ALLOWED
alter system set log_archive_dest_1=’LOCATION=/arch’scope=spfile sid=’DGPRI1′;
alter system set log_archive_dest_1=’LOCATION=/arch’scope=spfile sid=’DGPRI2′;
alter system set log_archive_dest_1=’LOCATION=/arch’scope=spfile sid=’*’;
host srvctl stop database -d TESTDG -o immediate
host srvctl status database -d TESTDG
host srvctl start database -d TESTDG -o mount
alter database archivelog;
修改rac主库为归档模式：
SYS@DGPRI1>alter system set log_archive_dest_1=’LOCATION=/arch’ scope=spfile sid=’DGPRI1′;
Systemaltered.
SYS@DGPRI1>alter system set log_archive_dest_1=’LOCATION=/arch’ scope=spfilesid=’DGPRI2′;
Systemaltered.
SYS@DGPRI1>host srvctl stop database -d TESTDG -o immediate
SYS@DGPRI1>host srvctl status database -d TESTDG
InstanceDGPRI1 is not running on node zfXXX3
InstanceDGPRI2 is not running on node zfXXX4
SYS@DGPRI1>host srvctl start database -d TESTDG -o mount
SYS@DGPRI1>archive log list;
ORA-03135:connection lost contact
SYS@DGPRI1>conn / as sysdba
Connected.
SYS@DGPRI1>alter database archivelog;
Databasealtered.
SYS@DGPRI1>set line 9999
SYS@DGPRI1>select name , open_mode,log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
—————————– ———— — —————- ——————–
TESTDG MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
SYS@DGPRI1>archive log list;
Databaselog mode Archive Mode
Automaticarchival Enabled
Archivedestination /arch
Oldestonline log sequence 5
Nextlog sequence to archive 6
Currentlog sequence 6
SYS@DGPRI1>alter database open;
Databasealtered.
SYS@DGPRI1> select name , open_mode,log_mode,force_logging,DATABASE_ROLE,switchover_status from gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
—————————– ———— — —————- ——————–
TESTDG READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG MOUNTED ARCHIVELOG YES PRIMARY NOT ALLOWED
启动第二个节点：
[ZFXXX4:root]:/>ORACLE_SID=DGPRI2
[ZFXXX4:root]:/>su- oracle
[ZFXXX4:oracle]:/oracle>ORACLE_SID=DGPRI2
[ZFXXX4:oracle]:/oracle>sqlplus/ as sysdba
SYS@DGPRI2>alter database open;
Databasealtered.
SYS@DGPRI2>set line 9999
SYS@DGPRI2>select name , open_mode, log_mode,force_logging,DATABASE_ROLE,switchover_statusfrom gv$database;
NAME OPEN_MODE LOG_MODE FOR DATABASE_ROLE SWITCHOVER_STATUS
—————————– ———— — —————- ——————–
TESTDG READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
TESTDG READ WRITE ARCHIVELOG YES PRIMARY NOT ALLOWED
SYS@DGPRI2>archive log list;
Databaselog mode Archive Mode
Automaticarchival Enabled
Archivedestination /arch
Oldestonline log sequence 1
Nextlog sequence to archive 1
Currentlog sequence 1
1.1.1.1 为主库添加standbyredo log
–为主库添加standbyredo log，简要描述一下standbyredo log的作用
–实际上就是与主库接收到的重做日志相对应，也就是说备库调用RFS进程将从主库接收到的重做日志按顺序写入到standby logfile
–在主库创建standbylogfile是便于发生角色转换后备用
–sandby redo log创建原则：
–a)、确保standbyredo log的大小与主库online redolog的大小一致
–b)、如主库为单实例数据库：standbyredo log组数=主库日志组总数+1
–c)、如果主库是RAC数据库：standby redo log组数=(每线程的日志组数+1)*最大线程数
–d)、不建议复用standbyredo log，避免增加额外的I/O以及延缓重做传输
单实例:
alter database add standby logfile group 4(‘/u01/app/oracle/oradata/oralg/standby_redo04.log’) size 50m;
alter database add standby logfile group 5(‘/u01/app/oracle/oradata/oralg/standby_redo05.log’) size 50m;
alter database add standby logfile group 6(‘/u01/app/oracle/oradata/oralg/standby_redo06.log’) size 50m;
alter database add standby logfile group 7(‘/u01/app/oracle/oradata/oralg/standby_redo07.log’) size 50m;
rac下：
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 ;
SYS@DGPRI1>select * from v$standby_log;
norows selected
SYS@DGPRI1>select group#,THREAD#,MEMBERS from v$log ;
GROUP# THREAD# MEMBERS
——————– ———-
1 1 2
2 1 2
3 2 2
4 2 2
SYS@DGPRI1>col member format a100
SYS@DGPRI1>select GROUP# ,STATUS , TYPE , MEMBER from v$logfile;
GROUP# STATUS TYPE MEMBER
—————– ———————————————————————————————————–
2 ONLINE +DATA/testdg/onlinelog/group_2.260.904043421
2 ONLINE +DATA/testdg/onlinelog/group_2.263.904043421
1 ONLINE +DATA/testdg/onlinelog/group_1.262.904043421
1 ONLINE +DATA/testdg/onlinelog/group_1.261.904043421
3 ONLINE +DATA/testdg/onlinelog/group_3.303.904043623
3 ONLINE +DATA/testdg/onlinelog/group_3.304.904043623
4 ONLINE +DATA/testdg/onlinelog/group_4.305.904043623
4 ONLINE +DATA/testdg/onlinelog/group_4.306.904043623
8rows selected.
SYS@DGPRI1>alter database add standby logfile thread 1 group 5 size 50M ,group 6size 50M ,group 7 size 50M ;
Databasealtered.
SYS@DGPRI1>alter database add standby logfile thread 2 group 8 size 50M ,group 9size 50M ,group 10 size 50M ;
Databasealtered.
SYS@DGPRI1> selectGROUP#,DBID,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS fromv$standby_log;
GROUP# DBID THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
————————————————– ———- ———- ——————– ———- — ———-
5 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
6 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
7 UNASSIGNED 1 0 52428800 512 0 YES UNASSIGNED
8 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
9 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
10 UNASSIGNED 2 0 52428800 512 0 YES UNASSIGNED
6rows selected.
1.1.1.1 修改主库参数文件
–使用下面的命令修改主库参数(此时主库应当使用spfile启动参数)
LOG_ARCHIVE_CONFIG = ‘DG_CONFIG ( db_unique_name,db_unique_name, … )’ 主库与备库端采用相同设置。
LOG_ARCHIVE_DEST_n =’SERVICE=。。。。。 SERIVCE: 用于指定备用数据库的TNSNAMES描述符
db_file_name_convert、 log_file_name_convert 参数值为路径，可以直接写db_unique_name，如果使用ASM，可以设置为*.db_file_name_convert=(‘+DATA’,’+RECOVERY’)
fal_server、 fal_client 参数值为TNSNAMES描述符
alter system set db_unique_name=’TESTDG’ scope=spfile;
alter system setlog_archive_config=’DG_CONFIG=(TESTDG,TESTDGPHY)’ sid=’*’;
alter system set log_archive_dest_1=’LOCATION=/archdb_unique_name=TESTDG valid_for=(ALL_LOGFILES,ALL_ROLES)’ sid=’*’;
alter system set log_archive_dest_2=’SERVICE=TNS_DGPHY LGWRASYNC db_unique_name=TESTDGPHY valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)’sid=’*’;
alter system set log_archive_dest_state_1=enablesid=’*’;
alter system set log_archive_dest_state_2=enablesid=’*’;
alter system set log_archive_max_processes=4 sid=’*’;
alter system set remote_login_passwordfile=’EXCLUSIVE’scope=spfile;
–Add below item when DB turn to standby role
alter system set db_file_name_convert=’TESTDGPHY’,’TESTDG’scope=spfile;
alter system set log_file_name_convert=’TESTDGPHY’,’TESTDG’scope=spfile;
alter system set standby_file_management=’AUTO’ sid=’*’;
alter system set fal_server=’TNS_DGPHY’ sid=’*’;
alter system set fal_client=’TNS_DGPRI’ sid=’*’;
SYS@DGPRI1>show parameter spfile
NAME TYPE VALUE
———————————————– ——————————
spfile string +DATA/testdg/spfiledgpri.ora
SYS@DGPRI1>