同一环境下新建Standby RAC库

共计 14058 个字符，预计需要花费 36 分钟才能阅读完成。

需求：在同一个环境下新建 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/JYZHAODG
mkdir +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/dbs
orapwd file=orapwjyzhaodg1 password=oracle entries=5
或者直接 copy 之前的密码文件，然后 mv 重命名:
cp orapwjyzhao1 orapwjyzhaodg1

节点 2:

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

最后测试相互连接可用

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

第二章 源数据库备份

vi backup.sh
备份脚本如下：

rman target / <<EOF
run {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 / <<EOF
run {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_SID
export ORACLE_SID=jyzhao1

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

Primary RAC 添加参数

-- 为不停止 primary RAC，所以尽可能动态修改参数：show parameter log_archive_config
show parameter db_file_name_convert
show parameter log_file_name_convert
show parameter fal_client
show parameter fal_server
show parameter log_archive_dest_3

alter 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=2
jyzhaodg1.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=2
jyzhaodg1.thread=1
jyzhaodg2.undo_tablespace='UNDOTBS2'
jyzhaodg1.undo_tablespace='UNDOTBS1'
#add
db_unique_name='jyzhaodg'
log_archive_config='dg_config=(jyzhao,jyzhaodg)'#db_unique_name
db_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg'
log_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg'
standby_file_management=auto
fal_client='jyzhaodg'#tnsnames.ora
fal_server='jyzhao'#salehrdb
log_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_SID
export ORACLE_SID=jyzhaodg1
create spfile='+DATA/jyzhaodg/spfilejyzhaodg.ora' from pfile='/tmp/pfile_std.ora';
--  
cat initjyzhaodg1.ora 
SPFILE='+DATA/jyzhaodg/spfilejyzhaodg.ora'

节点 2：

echo $ORACLE_SID
export ORACLE_SID=jyzhaodg2
cat initjyzhaodg2.ora 
SPFILE='+DATA/jyzhaodg/spfilejyzhaodg.ora'

创建 adump 目录（所有节点）

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

3.4 Standby RAC 启动到 nomount

节点 1:

echo $ORACLE_SID
export ORACLE_SID=jyzhaodg1
startup nomount

节点 2:

echo $ORACLE_SID
export ORACLE_SID=jyzhaodg2
startup nomount

第四章 rman 恢复控制文件

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

echo $ORACLE_SID
export ORACLE_SID=jyzhao1
SQL> alter database create standby controlfile as '/tmp/control01.ctlbak';

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

echo $ORACLE_SID
export ORACLE_SID=jyzhaodg1

restore controlfile from '/tmp/control01.ctlbak';
alter database mount;
crosscheck backupset;
-- 如果是之前 copy 到磁盘组的备份没加载到控制文件中，可以手动 catalog
catalog start with '+data/jyzhaodg/DATAFILE';
-- 同样，如果是之前的备份集没加载到控制文件中，一样手动 catalog
catalog 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.937515173

6 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.931879029

8 rows selected.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/jyzhaodg/tempfile/temp.263.931878661

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/jyzhaodg/controlfile/current.288.937645851
+FRA/jyzhaodg/controlfile/current.275.937645851

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

SQL> show parameter convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DATA/jyzhao, +DATA/jyzhaodg
log_file_name_convert                string      +DATA/jyzhao, +DATA/jyzhaodg

SQL> alter system set log_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg','+FRA/jyzhao','+FRA/jyzhaodg' scope=spfile;

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2252824 bytes
Variable Size             222302184 bytes
Database Buffers           83886080 bytes
Redo Buffers                4718592 bytes
Database 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.931879029

8 rows selected.

第五章 rman 恢复数据库

Standby RAC 节点 1:

确定 ORACLE_SID 变量：

echo $ORACLE_SID
export ORACLE_SID=jyzhaodg1

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

rman target / <<EOF! > db_restore.log
run {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 catalog
datafile 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_SID
export ORACLE_SID=jyzhao1

SQL> alter system set log_archive_dest_state_3=enable;

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

echo $ORACLE_SID
export ORACLE_SID=jyzhaodg1

SQL> 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+14


SQL> col member for a70
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

8 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.937648789

20 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 NONE

SQL> set lines 1000
SQL> 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:13
apply lag                        +00 00:09:38                                                     day(2) to second(0) interval   03/03/2017 10:03:20            03/03/2017 10:03:13
apply finish time                                                                                 day(2) to second(3) interval   03/03/2017 10:03:20
estimated 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 NONE

SQL> alter system archive log current;

System altered.


-- 再次在 Standby RAC 上查看 DG 同步状态：SQL> r
  1* select * from v$dataguard_stats

NAME                             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:44
apply lag                        +00 00:00:00                                                     day(2) to second(0) interval   03/03/2017 10:04:45            03/03/2017 10:04:44
apply finish time                                                                                 day(2) to second(3) interval   03/03/2017 10:04:45
estimated 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，备库的路径也要反复确认无误再操作。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141306.htm