Oracle搭建ADG环境步骤(RAC to RAC)

长河的笔记 2020-10-16

2758

本文介绍如何利用raman的duplicate命令搭建物理standby环境，主备库都是RAC环境。

实验环境：

RedHat 7.x

Oracle Database 12.1.0.2

前提条件：

1. 主库实例是RAC环境，数据文件存放在ASM磁盘组中。

2. 主库已经配置归档模式。

3. 备库已经安装好软件，并且ASM磁盘组名称与主库相同。

环境规划

主库Primary

备库Standby

主机名

host name

dm01db01

dm01db02

dm02db01

dm02db02

数据库唯一名称

db unique name

pcdbn

pcdbns

实例名

instance name

pcdbn1

pcdbn2

pcdbns1

pcdbns2

创建物理Standby

1. 主库创建Standby redo logs

推荐standby redo log的数量应该比每组thread 的 redo log多一个, 本例中主库为2节点有2个thread, 每个thread下有2组redo log, 如下：

SYS@pcdbn1(dm01db01)> select group#,thread#,sequence#,bytes,members,status from v$log;


    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ---------- ----------------
         1          1         65   52428800          1 CURRENT
         2          1         64   52428800          1 INACTIVE
         3          2         43   52428800          1 INACTIVE
         4          2         44   52428800          1 CURRENT


SYS@pcdbn1(dm01db01)> alter database add standby logfile thread 1
group 5 ('+RECO') size 50M,
group 6 ('+RECO') size 50M,
group 7 ('+RECO') size 50M;  2    3    4


Database altered.


SYS@pcdbn1(dm01db01)> alter database add standby logfile thread 2
group 8 ('+RECO') size 50M,
group 9 ('+RECO') size 50M,
group 10 ('+RECO') size 50M;  2    3    4
Database altered.


SYS@pcdbn1(dm01db01)> select group#,thread#,sequence#,bytes,status from v$standby_log;


    GROUP#    THREAD#  SEQUENCE#      BYTES STATUS
---------- ---------- ---------- ---------- ----------
         5          1          0   52428800 UNASSIGNED
         6          1          0   52428800 UNASSIGNED
         7          1          0   52428800 UNASSIGNED
         8          2          0   52428800 UNASSIGNED
         9          2          0   52428800 UNASSIGNED
        10          2          0   52428800 UNASSIGNED


6 rows selected.

2. 主库开启force logging

SYS@pcdbn1(dm01db01)> select force_logging from v$database;


FORCE_LOGGING
---------------------------------------
NO


SYS@pcdbn1(dm01db01)> ALTER DATABASE FORCE LOGGING;


Database altered.


SYS@pcdbn1(dm01db01)> select force_logging from v$database;


FORCE_LOGGING
---------------------------------------
YES

#关闭force logging使用alter database no force logging

3. 备库临时创建静态监听，用于rman duplicate复制主库。

在dm02db01的ORACLE_HOME/network/admin/listener.ora中添加如下内容, 之后启动监听.

DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dm02db01.bmwbrill.cn)(PORT = 1999))
  )


SID_LIST_DUP =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pcdbns)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/dbhome_1)
      (SID_NAME = pcdbns1)
    )
  )


ADR_BASE_DUP = /u01/app/oracle


[oracle@dm02db01 admin]$ lsnrctl start DUP
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dm02db01.bmwbrill.cn)(PORT=1999)))
Services Summary...
Service "pcdbns" has 1 instance(s).
  Instance "pcdbns1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

在主库tnsnames.ora中创建如下别名：

DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST=dm02db01)(PORT = 1999))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = pcdbns1)
    )
  )


[oracle@dm01db01 admin]$ tnsping DUP
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL = TCP)(HOST=dm02db01)(PORT = 1999))) (CONNECT_DATA = (SERVER = DEDICATED) (SID = pcdbns1)))
OK (0 msec)

4. 备库创建口令文件

[oracle@dm02db01 dbs]$ orapwd file=orapwpcdbns1 password=welcome1 force=y
[oracle@dm02db01 dbs]$ ls -l orapwpcdbns*
-rw-r----- 1 oracle oinstall 7680 Oct 10 14:35 orapwpcdbns1

5. 备库创建参数文件和相应目录, 并启动至nomount状态

备库主要修改参数如下：
[oracle@dm02db01 dbs]$ vi initpcdbns1.ora
*.audit_file_dest='/u01/app/oracle/admin/pcdbns/adump'
*.control_files='+DATA/PCDBNS/CONTROLFILE/pcdbns.ctl'
*.db_name='pcdbn'
*.db_unique_name='pcdbns'
*.memory_target=0
*.sga_target=4g
*.parallel_max_servers=10
*.remote_login_passwordfile='exclusive'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATA/PCDBN/DATAFILE','+DATA/PCDBNS/DATAFILE'
*.fal_client='pcdbns'
*.fal_server='pcdbn'
*.log_archive_config='DG_CONFIG=(pcdbns,pcdbn)'
*.log_archive_dest_1='location=+reco VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pcdbns'
*.log_archive_dest_2='SERVICE=pcdbn ASYNC REOPEN=10 NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pcdbn'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+data/pcdbn/onlinelog','+data/pcdbns/onlinelog'
*.standby_file_management='AUTO'


[oracle@dm02db01 dbs]$ mkdir -p /u01/app/oracle/admin/pcdbns/adump


[oracle@dm02db01 dbs]$ export ORACLE_SID=pcdbns1
[oracle@dm02db01 dbs]$ sqlplus / as sysdba
SYS@pcdbns1>startup nomount
ORACLE instance started.


Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size            1325400168 bytes
Database Buffers         2936012800 bytes
Redo Buffers               30621696 bytes

6. 主备库tnsnames.ora设置

PCDBN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL= TCP)(HOST=dm01-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pcdbn)
    )
  )


PCDBNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST=dm02-scan)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pcdbns)
    )
  )


--备库2个节点要分别有自己的local_listener，主库就不需要了
  PCDBNS_LOCAL_LISTENER = 
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL = TCP)(HOST=dm0201-vip)(PORT = 1521))
    )
  )

7. 临时RESET参数cluster_interconnects

SQL> alter system reset cluster_interconnects scope=spfile sid='pcdbn1';
SQL> alter system reset cluster_interconnects scope=spfile sid='pcdbn2';

8. 主库运行RMAN的duplicate命令创建备库

rman <<EOF
connect target sys/welcome1@dm01-scan:1521/pcdbn;
connect auxiliary sys/welcome1@dup;
run {
duplicate target database for standby nofilenamecheck from active database
spfile
parameter_value_convert 'pcdbn','pcdbns'
set db_unique_name='pcdbns'
set cluster_database='FALSE'
set local_listener='PCDBNS_LOCAL_LISTENER'
set remote_listener='dm02-scan:1521';
}
EOF

9. 还原参数cluster_interconnects

本例cluster_interconnects参数原本没有值，所以无需还原。

10. 停止并移除第3步中创建的DUP监听和 DUP别名

从库中从listener.ora中移除DUP监听,主库中移除dup别名。

11. 备库copy口令文件至ASM

su - grid
$asmcmd -p
ASMCMD [+] > cd +DATA
AMSCMD [+DATA] >mkdir pcdbn/password
ASMCMD [+DATA] >pwcopy /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwpcdbns1 +DATA/PCDBNS/PASSWORD/orapwpcdbns
ASMCMD [+DATA] > exit


--Remove the original password file.
su - oracle
$rm /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwpcdbns1

12. 将备库修改成cluster模式

--添加下列参数至备库参数文件，使其支持RAC
*.cluster_database=TRUE
pcdbns2.instance_number=2
pcdbns1.instance_number=1
pcdbns2.thread=2
pcdbns1.thread=1
pcdbns2.undo_tablespace='UNDOTBS2'
pcdbns1.undo_tablespace='UNDOTBS1'




create spfile='+DATA/pcdbns/spfilepcdbns.ora' from pfile='/tmp/pcdbns.ora'
[oracle@dm02db01 dbs]$ cat initpcdbns1.ora
pfile='+DATA/pcdbns/spfilepcdbns.ora'
--所有standby节点的pfile文件都需要修改
[oracle@dm02db02 dbs]$ cat initpcdbns2.ora
pfile='+DATA/pcdbns/spfilepcdbns.ora'

13. 将备库注册至CRS，重启备库至mount状态

[oracle@dm02db01 dbs]$ srvctl add database -db pcdbns -oraclehome /u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@dm02db01 dbs]$ srvctl add instance -db pcdbns -instance pcdbns1 -node dm02db01
[oracle@dm02db01 dbs]$ srvctl add instance -db pcdbns -instance pcdbns2 -node dm02db02
[oracle@dm02db01 dbs]$ srvctl modify database -db pcdbns -role physical_standby -spfile '+DATA/pcdbns/spfilepcdbns.ora' -pwfile '+DATA/PCDBNS/PASSWORD/orapwpcdbns'
[oracle@dm02db01 dbs]$ srvctl config database -d pcdbns
Database unique name: pcdbns
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/pcdbns/spfilepcdbns.ora
Password file: +DATA/PCDBNS/PASSWORD/orapwpcdbns
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: pcdbns1,pcdbns2
Configured nodes: dm02db01,dm02db02
Database is administrator managed


[oracle@dm02db01 dbs]$ srvctl start database -d pcdbns -o mount

14. 主库配置dataguard相关参数

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(pcdbn,pcdbns)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+reco VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pcdbn';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=pcdbns LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pcdbns';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set standby_file_management='AUTO';
alter system set db_file_name_convert='+data/pcdbns/datafile','+data/pcdbn/datafile' scope=spfile;
alter system set log_file_name_convert='+data/pcdbns/onlinelog','+data/pcdbn/onlinelog' scope=spfile;
alter system set FAL_SERVER=pcdbns;
alter system set FAL_CLIENT=pcdbn;

15. 测试数据同步，开启ADG

SYS@pcdbns1>alter database recover managed standby database disconnect;
SYS@pcdbns1>select (sysdate-(sysdate-TO_DSINTERVAL(value)))*86400 as lag_seconds from v$dataguard_stats where name = 'apply lag';
LAG_SECONDS
-----------
          0


SYS@pcdbns1>select process,status from v$managed_standby where process ='MRP0';
PROCESS   STATUS
--------- ------------
MRP0      APPLYING_LOG


SYS@pcdbns1>alter database recover managed standby database cancel;


Database altered.


SYS@pcdbns1>alter database open;


Database altered.


SYS@pcdbns1>alter database recover managed standby database disconnect;


Database altered.

16. 主备库端配置读/写，只读服务

adg环境搭建好之后, 可以利用service来提供不同角色的服务。

workload服务用于提供正常的读写服务，当数据库为主库时workload服务自动启动。

reports服务用于提供只读服务，当数据库角色为备库时reports服务自动启动。

--主库端
srvctl add service -d pcdbn -s workload -r pcdbn1,pcdbn2 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl add service -d pcdbn -s reports -r pcdbn1,pcdbn2 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl start service -d pcdbn -s reports
srvctl stop service -d pcdbn -s reports


--备库端
srvctl add service -d pcdbns -s workload -r pcdbns1,pcdbns2 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl add service -d pcdbns -s reports -r pcdbns1,pcdbns2 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
srvctl start service -d pcdbns -s reports

17. 客户端tnsnames.ora配置

PRI_PCDBN =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (FAILOVER = ON)
       (LOAD_BALANCE = OFF)
       (ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = dm02-scan)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = workload)
     )
  )


STBY_PCDBN =
  (DESCRIPTION =
     (ADDRESS_LIST =
       (FAILOVER = ON)
       (LOAD_BALANCE = OFF)
       (ADDRESS = (PROTOCOL = TCP)(HOST = dm02-scan)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = dm01-scan)(PORT = 1521))
     )
    (CONNECT_DATA =
       (SERVICE_NAME = reports)
    )
  )

参考:

Creating a Physical Standby using RMAN Duplicate (RAC or Non-RAC) (Doc ID 1617946.1)

希望以上内容能够对您有所帮助。

END

oracle

文章转载自长河的笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle搭建ADG环境步骤(RAC to RAC)

环境规划

创建物理Standby

评论