前言:
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快速部署。