Oracle-通过DBCA方式部署DataGuard

前言:

Oracle在12.2版本之后，推出了部署ADG的新方式DBCA，通过DBCA静默duplicate方式快速的部署DG备库，简化了部署备库DG的操作步骤。

测试环境:

	主库	备库
数据库版本	19.3.0.0	19.3.0.0
架构	单实例	单实例
db_unique_name	testdb	testdg
ip	192.168.2.101	192.168.2.201

部署步骤:

1    检查数据库开启归档以及force_logging

---数据库开启归档模式
archive log list 
---检查开启force_logging
select force_logging from v$database;
alter database force logging;

2    检查并开启SYS远程登录

---确认参数remote_login_passwordfile为EXCLUSIVE
show parameter remote_login_passwordfile

3    配置主备TNS

---配置主备TNS
#主库tns
testdb =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.101)(PORT = 1521))
  (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
   ) 
#备库tns
testdg=
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.2.201)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdg)
     )
  )

4    配置主库参数

---设置主库参数  
alter system set log_archive_config='dg_config=(testdb,testdg)' scope=both  sid='*';
alter system set log_archive_dest_2=
'service=testdg LGWR ASYNC NOAFFIRM delay=0 optional 
 compression=disable max_failure=0 max_connections=1 reopen=30 
 net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
 db_unique_name=testdg' scope=both  sid='*';
alter system set log_archive_dest_state_2=defer;
alter system set fal_client=testdb scope=both sid='*'; 
alter system set fal_server=testdg scope=both sid='*'; 
alter system set standby_file_management=AUTO scope=both sid='*';

5    主库添加standby log

---添加standby_log
#查询当前log的设置
select thread#,group#,bytes/1024/1024 SIZE_IN_GB, status from v$log;
#创建需要保持一致
alter database ADD standby logfile thread 1 group 21 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo01.log') size 200M ;
alter database ADD standby logfile thread 1 group 22 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo02.log') size 200M ; 
alter database ADD standby logfile thread 1 group 23 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo03.log') size 200M ; 
alter database ADD standby logfile thread 1 group 24 ('/u01/app/oracle/oradata/TESTDB/onlinelog/redo04.log') size 200M ;

6    在备库通过dbca创建备库

#-createDuplicateDB Command to Duplicate a database.
#-gdbName <Global database name>
#-sid standby database oracle_sid
#-sysPassword primary database sys password
#-primaryDBConnectionString <EZCONNECT string to connect to primary database for example "host:port/servicename">
#-initParams [-initParams <Comma separated list of name=value pairs>]
      [-initParamsEscapeChar <Specify escape character for comma when a specific initParam has multiple values.If the escape character is not specified backslash is the default escape character>]
#-createAsStandby <Option to create a standby database>] [-dbUniqueName <db_unique_name for standby db>]
---通过dbca duplicate搭建备库
dbca -silent -createDuplicateDB -gdbName testdb -sid testdg -sysPassword oracle -primaryDBConnectionString 192.168.2.101:1521/testdb  -initParams db_create_file_dest=/u01/app/oracle/oradata -createAsStandby -dbUniqueName testdg

7    检查输出日志，确认执行完成

oracle@rac19a dbs]$ dbca -silent -createDuplicateDB -gdbName testdb -sid testdg -sysPassword oracle -primaryDBConnectionString 192.168.2.101:1521/testdb  -initParams db_create_file_dest=/u01/app/oracle/oradata -createAsStandby -dbUniqueName testdg
Prepare for db operation
22% complete
Listener config step
44% complete
Auxiliary instance creation
67% complete
RMAN duplicate
89% complete
Post duplicate database operations
100% complete
​
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/testdg/testdg20.log" for further details.

8    执行完成之后，会通过duplicate复制一个备库

#会复制出一个open read only备库
SQL> select database_role,open_mode from v$database;
​
DATABASE_ROLE   OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

9    密码文件，spfile文件也会复制过来

[oracle@rac19a dbs]$ ls -rlth
total 219M
-rw-r--r-- 1 oracle oinstall 3.1K May 14  2015 init.ora
-rw-r----- 1 oracle oinstall 2.0K Oct 31 15:12 orapwtestdg
-rw-r----- 1 oracle oinstall   24 Oct 31 15:12 lkTESTDG
-rw-r----- 1 oracle oinstall 2.5K Oct 31 15:13 inittestdg.ora
-rw-rw---- 1 oracle oinstall 1.6K Oct 31 15:14 hc_testdg.dat
-rw-r----- 1 oracle oinstall 3.5K Oct 31 15:14 spfiletestdg.ora

10    备库配置参数

alter system set log_archive_dest_1='location=/u01/app/oracle/arch';
alter system set log_archive_config='dg_config=(testdb,testdg)' scope=both  sid='*';
alter system set log_archive_dest_2=
'service=testdb LGWR ASYNC NOAFFIRM delay=0 optional 
 compression=disable max_failure=0 max_connections=1 reopen=30 
 net_timeout=300 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) 
 db_unique_name=testdb' scope=both  sid='*';
alter system set fal_client=testdg scope=both sid='*'; 
alter system set fal_server=testdb scope=both sid='*'; 
alter system set standby_file_management=AUTO scope=both sid='*';

11    主库开启链路

alter system set log_archive_dest_state_2=enable;
alter system switch logfile;

12    备库开启实时同步，DG部署完成

#备库开启mrp进程
alter database recover managed standby database using current logfile disconnect from session;
#实时同步
 1* select * FROM V$DATAGUARD_STATS
​
SOURCE_DBID SOURCE_DB_UNIQUE_NAME       NAME            VALUE             UNIT
----------- -------------------------------- -------------------------------- -------------------------------------------------- ------------------------------
TIME_COMPUTED           DATUM_TIME        CON_ID
------------------------------ ------------------------------ ----------
 2901301221 testdb           transport lag          +00 00:00:00           day(2) to second(0) interval
10/31/2022 16:54:00         10/31/2022 16:53:59           0
​
 2901301221 testdb           apply lag            +00 00:00:00           day(2) to second(0) interval
10/31/2022 16:54:00         10/31/2022 16:53:59           0
​
 2901301221 testdb           apply finish time                   day(2) to second(3) interval
10/31/2022 16:54:00                   0
​
    0             estimated startup time        13             second
10/31/2022 16:54:00                   0

总结:

        通过DBCA方式进行备库DG的部署，可以提高备库数据初始化的速度以及简化部署的步骤，适合在测试环境进行DG快速部署。