同一环境下新建Standby RAC库

需求：在同一个环境下新建Standby RAC库，即和Primary RAC在相同的磁盘组。
说明：生产环境一般不建议这样配置DG，因为存储层面是相同磁盘组，灾备的实际意义不大。我这里是用作读写分离。

基本信息：
db_name: jyzhao
Primary RAC db_unique_name：jyzhao
Standby RAC db_unique_name：jyzhaodg
Standby RAC instance_name: jyzhaodg1, jyzhaodg2
版本：GI 11.2.0.4 + DB 11.2.0.4

第一章 准备工作

• 1.1 ASM存储
• 1.2 配置tnsnames.ora
• 1.3 密码文件

第二章 源数据库备份

第三章 参数文件

• 3.1 修改主库参数文件
• 3.2 修改Standby RAC 参数
• 3.3 在ASM中创建standby的spfile
• 3.4 Standby RAC启动到nomount

第五章 rman恢复数据库
第六章 备库开启日志应用
第七章 创建standby log
第八章 检查资源状态

第一章 准备工作

1.1 ASM存储

确认RAC Standby存储是在和RAC Primary相同的ASM磁盘组内（PS：灾备效果不明显），创建RAC Standby存储目录（根据db_unique_name）

mkdir +DATA/JYZHAODGmkdir +FRA/JYZHAODG

1.2 配置tnsnames.ora

cd $ORACLE_HOME/network/admin/
cat tnsnames.ora
添加主库备库的连接信息（所有节点）：

JYZHAO =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-scan)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = jyzhao)    )  )jyzhaodg =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-scan)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = jyzhaodg)    )  )

1.3 密码文件

节点1:

export ORACLE_SID=jyzhaodg1密码文件；cd $ORACLE_HOME/dbsorapwd file=orapwjyzhaodg1 password=oracle entries=5或者直接copy之前的密码文件，然后mv重命名:cp orapwjyzhao1 orapwjyzhaodg1

节点2:

export ORACLE_SID=jyzhaodg2密码文件；cd $ORACLE_HOME/dbsorapwd file=orapwjyzhaodg2 password=oracle entries=5或者直接copy之前的密码文件，然后mv重命名:cp orapwjyzhao2 orapwjyzhaodg2

最后测试相互连接可用

sqlplus sys/oracle@jyzhao as sysdbasqlplus sys/oracle@jyzhaodg as sysdba

第二章 源数据库备份

vi backup.sh
备份脚本如下：

rman target / <<EOFrun {allocate channel c1 device type disk;allocate channel c2 device type disk;backup as compressed backupset database filesperset 1 format '/u01/orabak/salehrdb_%d_%T_%s.bak';backup current controlfile format '/u01/orabak/control.bak';release channel c1;release channel c2;}EOF

后台执行备份任务：

nohup sh backup.sh &

注意：如果使用backup as copy database format方案,就不用再备份到磁盘后再恢复了,可以节省时间。

backup as copy 方案备份脚本 backupcp.sh内容如下：

rman target / <<EOFrun {allocate channel c1 device type disk;allocate channel c2 device type disk;backup as copy database format '+DATA/JYZHAODG/DATAFILE/%u.dbf';release channel c1;release channel c2;}EOF

注意：这种方式，路径包含的目录需手动创建。

mkdir +DATA/JYZHAODG/DATAFILE

第三章 参数文件

3.1 修改主库参数文件

确认当前环境ORACLE_SID：

echo $ORACLE_SIDexport ORACLE_SID=jyzhao1

根据spfile文件创建pfile：
create pfile='/tmp/pfile.ora' from spfile;

Primary RAC 添加参数

--为不停止primary RAC，所以尽可能动态修改参数：show parameter log_archive_configshow parameter db_file_name_convertshow parameter log_file_name_convertshow parameter fal_clientshow parameter fal_servershow parameter log_archive_dest_3alter system set log_archive_config='dg_config=(jyzhao,jyzhaodg)';alter system set db_file_name_convert='+DATA/jyzhaodg','+DATA/jyzhao' scope=spfile;alter system set log_file_name_convert='+DATA/jyzhaodg','+DATA/jyzhao' scope=spfile;alter system set fal_client='jyzhao';alter system set fal_server='jyzhaodg';alter system set log_archive_dest_3='service=jyzhaodg valid_for=(online_logfiles,primary_role) db_unique_name=jyzhaodg';--暂时defer传输链路，防止此时主库告警生成相关错误SQL> alter system set log_archive_dest_state_3=defer;

3.2 修改Standby RAC 参数

根据主库之前导出的参数文件修改备库的参数文件：
cp /tmp/pfile.ora /tmp/pfile_std.ora
vi /tmp/pfile_std.ora

*._high_priority_processes='LMS*'*.audit_file_dest='/u01/app/oracle/admin/jyzhaodg/adump'*.audit_trail='db'*.cluster_database=true*.compatible='11.2.0.4.0'*.control_files='+DATA/jyzhaodg/controlfile/current.260.931878631','+FRA/jyzhaodg/controlfile/current.256.931878631'*.db_block_size=8192*.db_create_file_dest='+DATA'*.db_domain=''*.db_name='jyzhao'*.db_recovery_file_dest='+FRA'*.db_recovery_file_dest_size=4621074432*.diagnostic_dest='/u01/app/oracle'*.dispatchers='(PROTOCOL=TCP) (SERVICE=jyzhaoXDB)'jyzhaodg2.instance_number=2jyzhaodg1.instance_number=1*.log_archive_format='%t_%s_%r.dbf'*.memory_target=313286272*.open_cursors=300*.processes=150*.remote_listener='oradb-scan:1521'*.remote_login_passwordfile='exclusive'jyzhaodg2.thread=2jyzhaodg1.thread=1jyzhaodg2.undo_tablespace='UNDOTBS2'jyzhaodg1.undo_tablespace='UNDOTBS1'#adddb_unique_name='jyzhaodg'log_archive_config='dg_config=(jyzhao,jyzhaodg)'#db_unique_namedb_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg'log_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg'standby_file_management=autofal_client='jyzhaodg'#tnsnames.orafal_server='jyzhao'#salehrdblog_archive_dest_3='service=jyzhao valid_for=(online_logfiles,primary_role) db_unique_name=jyzhao'

主要是注意后面#add之后的内容。

3.3 在ASM中创建standby的spfile

在ASM中创建standby的spfile,并确定各节点的参数文件内容指向磁盘中的spfile。
节点1:

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg1create spfile='+DATA/jyzhaodg/spfilejyzhaodg.ora' from pfile='/tmp/pfile_std.ora';--  cat initjyzhaodg1.ora SPFILE='+DATA/jyzhaodg/spfilejyzhaodg.ora'

节点2：

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg2cat initjyzhaodg2.ora SPFILE='+DATA/jyzhaodg/spfilejyzhaodg.ora'

创建adump目录（所有节点）

mkdir -p /u01/app/oracle/admin/jyzhaodg/adump

3.4 Standby RAC启动到nomount

节点1:

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg1startup nomount

节点2:

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg2startup nomount

第四章 rman恢复控制文件

在Primary RAC上创建备库使用的控制文件：

echo $ORACLE_SIDexport ORACLE_SID=jyzhao1SQL> alter database create standby controlfile as '/tmp/control01.ctlbak';

在Standby RAC的节点1上恢复控制文件并启动到mount：

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg1restore controlfile from '/tmp/control01.ctlbak';alter database mount;crosscheck backupset;--如果是之前copy到磁盘组的备份没加载到控制文件中，可以手动catalogcatalog start with '+data/jyzhaodg/DATAFILE';--同样，如果是之前的备份集没加载到控制文件中，一样手动catalogcatalog start with '/u01/orabak/';

查看此时standby记录的各文件路径是否符合预期：

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------+DATA/jyzhaodg/datafile/system.256.931878537+DATA/jyzhaodg/datafile/sysaux.257.931878537+DATA/jyzhaodg/datafile/undotbs1.258.931878537+DATA/jyzhaodg/datafile/users.259.931878537+DATA/jyzhaodg/datafile/undotbs2.264.931878827+DATA/jyzhaodg/datafile/dbs_d_jingyu.268.9375151736 rows selected.SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------+DATA/jyzhaodg/onlinelog/group_2.262.931878637+FRA/jyzhao/onlinelog/group_2.258.931878639+DATA/jyzhaodg/onlinelog/group_1.261.931878635+FRA/jyzhao/onlinelog/group_1.257.931878637+DATA/jyzhaodg/onlinelog/group_3.265.931879021+FRA/jyzhao/onlinelog/group_3.259.931879023+DATA/jyzhaodg/onlinelog/group_4.266.931879027+FRA/jyzhao/onlinelog/group_4.260.9318790298 rows selected.SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------+DATA/jyzhaodg/tempfile/temp.263.931878661SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------+DATA/jyzhaodg/controlfile/current.288.937645851+FRA/jyzhaodg/controlfile/current.275.937645851

发现日志文件有不符合预期的路径，进行修正：

SQL> show parameter convertNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_name_convert                 string      +DATA/jyzhao, +DATA/jyzhaodglog_file_name_convert                string      +DATA/jyzhao, +DATA/jyzhaodgSQL> alter system set log_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg','+FRA/jyzhao','+FRA/jyzhaodg' scope=spfile;SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area  313159680 bytesFixed Size                  2252824 bytesVariable Size             222302184 bytesDatabase Buffers           83886080 bytesRedo Buffers                4718592 bytesDatabase mounted.SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------+DATA/jyzhaodg/onlinelog/group_2.262.931878637+FRA/jyzhaodg/onlinelog/group_2.258.931878639+DATA/jyzhaodg/onlinelog/group_1.261.931878635+FRA/jyzhaodg/onlinelog/group_1.257.931878637+DATA/jyzhaodg/onlinelog/group_3.265.931879021+FRA/jyzhaodg/onlinelog/group_3.259.931879023+DATA/jyzhaodg/onlinelog/group_4.266.931879027+FRA/jyzhaodg/onlinelog/group_4.260.9318790298 rows selected.

第五章 rman恢复数据库

Standby RAC节点1:

确定ORACLE_SID变量：

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg1

a. 如果是使用从备份集恢复的方式
vi restore.sh

rman target / <<EOF! > db_restore.logrun {allocate channel d1 type disk;allocate channel d2 type disk;restore database;release channel d1;release channel d2;}exit;EOF!

nohup sh restore.sh &

b. 如果是直接使用copy到磁盘组的
直接switch database to copy即可。

RMAN> switch database to copy;using target database control file instead of recovery catalogdatafile 1 switched to datafile copy "+DATA/jyzhaodg/datafile/0eru6m4s.dbf"datafile 2 switched to datafile copy "+DATA/jyzhaodg/datafile/0fru6m4s.dbf"datafile 3 switched to datafile copy "+DATA/jyzhaodg/datafile/0hru6meo.dbf"datafile 4 switched to datafile copy "+DATA/jyzhaodg/datafile/0iru6mg5.dbf"datafile 5 switched to datafile copy "+DATA/jyzhaodg/datafile/0jru6mg6.dbf"datafile 6 switched to datafile copy "+DATA/jyzhaodg/datafile/0gru6men.dbf"

第六章 备库开启日志应用

确认Primary RAC的日志传输链路打开：

echo $ORACLE_SIDexport ORACLE_SID=jyzhao1SQL> alter system set log_archive_dest_state_3=enable;

Standby RAC节点1在mount状态下开启日志应用：

echo $ORACLE_SIDexport ORACLE_SID=jyzhaodg1SQL> alter database recover managed standby database disconnect from session;

第七章 创建standby log

停止备库应用：

SQL> alter database recover managed standby database cancel;                   

查看日志信息：

SQL> select * from v$Log;    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ ------------ ------------         1          1         69   52428800        512          2 YES CURRENT                2450934 03-MAR-17      2.8147E+14         2          1          0   52428800        512          2 YES UNUSED                 2440706 03-MAR-17         2450934 03-MAR-17         3          2          0   52428800        512          2 YES UNUSED                 2440817 03-MAR-17         2450939 03-MAR-17         4          2         36   52428800        512          2 YES CURRENT                2450939 03-MAR-17      2.8147E+14SQL> col member for a70SQL> select group#, type, member from v$logfile;    GROUP# TYPE    MEMBER---------- ------- ----------------------------------------------------------------------         2 ONLINE  +DATA/jyzhaodg/onlinelog/group_2.298.937648563         2 ONLINE  +FRA/jyzhaodg/onlinelog/group_2.278.937648565         1 ONLINE  +DATA/jyzhaodg/onlinelog/group_1.297.937648559         1 ONLINE  +FRA/jyzhaodg/onlinelog/group_1.279.937648561         3 ONLINE  +DATA/jyzhaodg/onlinelog/group_3.299.937648567         3 ONLINE  +FRA/jyzhaodg/onlinelog/group_3.389.937648569         4 ONLINE  +DATA/jyzhaodg/onlinelog/group_4.300.937648573         4 ONLINE  +FRA/jyzhaodg/onlinelog/group_4.390.9376485738 rows selected.

根据检查结果，合理为数据库添加standby logfile：

alter database add standby logfile thread 1 group 11 ('+DATA','+FRA') size 52428800;alter database add standby logfile thread 1 group 12 ('+DATA','+FRA') size 52428800;alter database add standby logfile thread 1 group 13 ('+DATA','+FRA') size 52428800;alter database add standby logfile thread 2 group 21 ('+DATA','+FRA') size 52428800;alter database add standby logfile thread 2 group 22 ('+DATA','+FRA') size 52428800;alter database add standby logfile thread 2 group 23 ('+DATA','+FRA') size 52428800;

添加完再次查看：

SQL> select group#, type, member from v$logfile;    GROUP# TYPE    MEMBER---------- ------- ----------------------------------------------------------------------         2 ONLINE  +DATA/jyzhaodg/onlinelog/group_2.298.937648563         2 ONLINE  +FRA/jyzhaodg/onlinelog/group_2.278.937648565         1 ONLINE  +DATA/jyzhaodg/onlinelog/group_1.297.937648559         1 ONLINE  +FRA/jyzhaodg/onlinelog/group_1.279.937648561         3 ONLINE  +DATA/jyzhaodg/onlinelog/group_3.299.937648567         3 ONLINE  +FRA/jyzhaodg/onlinelog/group_3.389.937648569         4 ONLINE  +DATA/jyzhaodg/onlinelog/group_4.300.937648573         4 ONLINE  +FRA/jyzhaodg/onlinelog/group_4.390.937648573        11 STANDBY +DATA/jyzhaodg/onlinelog/group_11.301.937648773        11 STANDBY +FRA/jyzhaodg/onlinelog/group_11.391.937648775        12 STANDBY +DATA/jyzhaodg/onlinelog/group_12.302.937648777    GROUP# TYPE    MEMBER---------- ------- ----------------------------------------------------------------------        12 STANDBY +FRA/jyzhaodg/onlinelog/group_12.392.937648779        13 STANDBY +DATA/jyzhaodg/onlinelog/group_13.303.937648779        13 STANDBY +FRA/jyzhaodg/onlinelog/group_13.393.937648781        21 STANDBY +DATA/jyzhaodg/onlinelog/group_21.304.937648783        21 STANDBY +FRA/jyzhaodg/onlinelog/group_21.394.937648783        22 STANDBY +DATA/jyzhaodg/onlinelog/group_22.305.937648785        22 STANDBY +FRA/jyzhaodg/onlinelog/group_22.395.937648787        23 STANDBY +DATA/jyzhaodg/onlinelog/group_23.306.937648787        23 STANDBY +FRA/jyzhaodg/onlinelog/group_23.396.93764878920 rows selected.

继续开启备库应用，确定恢复完成日志没报错信息后取消日志应用，打开数据库，开启ADG：

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

查看DG同步状态：

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S-------------------- ---------------- -------------------- --- -------- -------READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED          NO  DISABLED NONESQL> set lines 1000SQL> select * from v$dataguard_stats;NAME                             VALUE                                                            UNIT                           TIME_COMPUTED                  DATUM_TIME-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------transport lag                    +00 00:09:37                                                     day(2) to second(0) interval   03/03/2017 10:03:20            03/03/2017 10:03:13apply lag                        +00 00:09:38                                                     day(2) to second(0) interval   03/03/2017 10:03:20            03/03/2017 10:03:13apply finish time                                                                                 day(2) to second(3) interval   03/03/2017 10:03:20estimated startup time           40                                                               second                         03/03/2017 10:03:20--可以在Primary RAC上归档当前日志模拟业务切换归档：SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S-------------------- ---------------- -------------------- --- -------- -------READ WRITE           PRIMARY          TO STANDBY           NO  DISABLED NONESQL> alter system archive log current;System altered.--再次在Standby RAC上查看DG同步状态：SQL> r  1* select * from v$dataguard_statsNAME                             VALUE                                                            UNIT                           TIME_COMPUTED                  DATUM_TIME-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------transport lag                    +00 00:00:00                                                     day(2) to second(0) interval   03/03/2017 10:04:45            03/03/2017 10:04:44apply lag                        +00 00:00:00                                                     day(2) to second(0) interval   03/03/2017 10:04:45            03/03/2017 10:04:44apply finish time                                                                                 day(2) to second(3) interval   03/03/2017 10:04:45estimated startup time           40                                                               second                         03/03/2017 10:04:45

至此，已完成RAC Standby库在同环境下的创建。

第八章 检查资源状态

我们可以将RAC Standby也加入到crs资源中：

[oracle@oradb23 ~]$ srvctl add database -d jyzhaodg -o $ORACLE_HOME -i jyzhaodg1 -n jyzhao[oracle@oradb23 ~]$ srvctl add instance -d jyzhaodg -i jyzhaodg1 -n oradb23[oracle@oradb23 ~]$ srvctl add instance -d jyzhaodg -i jyzhaodg2 -n oradb24--启动数据库[oracle@oradb23 ~]$ srvctl start database -d salehrdg--查看资源状态：[grid@oradb23 ~]$ crsctl stat res -t

总结：同环境下搭建Standby RAC，最重要的注意事项就是一定要细心，操作前确保自己操作的是正确的ORACLE_SID，备库的路径也要反复确认无误再操作。