dbf如何导入oracle_Oracle搭建ADG环境步骤(RAC to RAC)

本文介绍如何利用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 CURRENTSYS@pcdbn1(dm01db01)> alter database add standby logfile thread 1group 5 ('+RECO') size 50M,group 6 ('+RECO') size 50M,group 7 ('+RECO') size 50M;  2    3    4Database altered.SYS@pcdbn1(dm01db01)> alter database add standby logfile thread 2group 8 ('+RECO') size 50M,group 9 ('+RECO') size 50M,group 10 ('+RECO') size 50M;  2    3    4Database 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 UNASSIGNED6 rows selected.

2. 主库开启force logging

SYS@pcdbn1(dm01db01)> select force_logging from v$database;FORCE_LOGGING---------------------------------------NOSYS@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 DUPListening 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 DUPUsed TNSNAMES adapter to resolve the aliasAttempting 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 sysdbaSYS@pcdbns1>startup nomountORACLE instance started.Total System Global Area 4294967296 bytesFixed Size                  2932632 bytesVariable Size            1325400168 bytesDatabase Buffers         2936012800 bytesRedo 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 <connect target sys/welcome1@dm01-scan:1521/pcdbn;connect auxiliary sys/welcome1@dup;run {duplicate target database for standby nofilenamecheck from active databasespfileparameter_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 -pASMCMD [+] > cd +DATAAMSCMD [+DATA] >mkdir pcdbn/passwordASMCMD [+DATA] >pwcopy /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwpcdbns1 +DATA/PCDBNS/PASSWORD/orapwpcdbnsASMCMD [+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=TRUEpcdbns2.instance_number=2pcdbns1.instance_number=1pcdbns2.thread=2pcdbns1.thread=1pcdbns2.undo_tablespace='UNDOTBS2'pcdbns1.undo_tablespace='UNDOTBS1'create spfile='+DATA/pcdbns/spfilepcdbns.ora' from pfile='/tmp/pcdbns.ora'[oracle@dm02db01 dbs]$ cat initpcdbns1.orapfile='+DATA/pcdbns/spfilepcdbns.ora'--所有standby节点的pfile文件都需要修改[oracle@dm02db02 dbs]$ cat initpcdbns2.orapfile='+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 pcdbnsDatabase unique name: pcdbnsDatabase name:Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1Oracle user: oracleSpfile: +DATA/pcdbns/spfilepcdbns.oraPassword file: +DATA/PCDBNS/PASSWORD/orapwpcdbnsDomain:Start options: openStop options: immediateDatabase role: PHYSICAL_STANDBYManagement policy: AUTOMATICServer pools:Disk Groups:Mount point paths:Services:Type: RACStart concurrency:Stop concurrency:OSDBA group: dbaOSOPER group: operDatabase instances: pcdbns1,pcdbns2Configured nodes: dm02db01,dm02db02Database 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-----------          0SYS@pcdbns1>select process,status from v$managed_standby where process ='MRP0';PROCESS   STATUS--------- ------------MRP0      APPLYING_LOGSYS@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 150srvctl add service -d pcdbn -s reports -r pcdbn1,pcdbn2 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150srvctl start service -d pcdbn -s reportssrvctl 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 150srvctl add service -d pcdbns -s reports -r pcdbns1,pcdbns2 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150srvctl 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