This is the 9th day of my participation in the More text Challenge. For details, see more text Challenge

About the author:

LuciferLiu is a member of China DBA alliance (ACDU). Currently, I am working as Oracle DBA and used to be engaged in the development of Oracle database, mainly serving the manufacturing, auto finance and other industries. Now he has Oracle OCP and OceanBase OBCA certifications. He is good at Oracle database operation and maintenance development, backup and recovery, installation and migration, Linux automatic operation and maintenance script writing, etc.

preface

Oracle Data Guard is a member of the Oracle MAA(Maximum Availability Architecture). The concept of STANDBY DATABASE was introduced with Oracle 7I and gradually gained popularity. As the Oracle database version changes, the standby database can be set up in various ways. Today we introduce a new way to create a physical standby library, starting with version 12C: using the DBCA command line.

dbca -createDuplicateDB -createAsStandby -dbUniqueName

Advantages and disadvantages: convenient and fast, build fast. However, the performance of the primary database is affected, and it is difficult to troubleshoot errors reported during the execution.

For details, see:

dbca -silent -createDuplicateDB
    -gdbName global_database_name
    -primaryDBConnectionString easy_connect_string_to_primary
    -sid database_system_identifier
    [-createAsStandby 
        [-dbUniqueName db_unique_name_for_standby]]
    [-customScripts scripts_list]
Copy the code

The createDuplicateDB Command creates a duplicate of an Oracle database.

12.2.0.1 Starting to Support DBCA To Create a Physical Standby Database:

The primary database must be a single-machine, non-RAC database.

The primary repository must be a non-CDB environment.

After 18C, the above restrictions have been removed and CDB and RAC environments are supported as primary libraries.

First, environmental preparation

The environment installation process is ignored. Please refer to:

30 minutes! One-click Oracle 19C single-node CDB+PDB deployment

This test is simulated according to the production environment upgrade as far as possible:

node The host version The host name The instance name Oracle version The IP address
rac01 rhel7.9 rac01 cdb19c 19.3.0 (Patch 29585399) 10.211.55.100
rac02 rhel7.9 rac02 orcl+cdb19c 19.3.0 (Patch 29585399) 10.211.55.101
For the library rhel7.9 dbca_stby Not creating an instance 19.3.0 (Patch 29585399) 10.211.55.110

Note:

1. Db_unique_name The primary and standby databases must be different.

2. The db_name of the active and standby databases must be the same.

3. The versions of the ACTIVE and standby databases must be the same.

Oracle Test environment installation:

For details, see DBA Operation and Maintenance Gospel: 10 minutes, install the Oracle database using a single Command on Linux

Primary database RAC environment one-click installation:

/ oracleshellinstall. sh -i 10.211.55.100 '#Public IP' \ -n rac '# hostname' \ -c TRUE '# CDB' \ -pb orcl '# PDB' \ -o cdb19c  `# oraclesid`\ -rs oracle `# root password`\ -op oracle `# oracle password`\ -gp oracle `# grid password`\ -b /u01/app '# install basedir' \ -s AL32UTF8 '# Characterset' \ -pb1 10.211.55.100 -pb2 10.211.55.101 '# node public IP' \ -vi1 10.211.55.102-vi2 10.211.55.103 '# node virtual IP' \ -pi1 1.1.1.1 -pi2 1.1.1.2 '# node private IP' \ -si 10.211.55.105 `# scan ip`\ -dd /dev/sdc `# asm data disk`\ -od /dev/sdb `# asm ocr disk`\ -or EXTERNAL `# asm ocr redundancy`\ -dr EXTERNAL `# asm data redundancy`\ -on OCR `# asm ocr diskgroupname`\ -dn DATA `# asm data diskgroupname`\ -puf eth0 -prf Eth1 '# network fcname' \ -tsi 10.211.55.200 '# timeserver' \ -gpa 32545008 '# Grid PSU NUMBER'Copy the code



One-click installation for the standby database :(only ORACLE software is installed for the standby database. No database is created.)

/ oracleshellinstall. sh -i 10.211.55.110 -n dbCA_stby -o cdb19c-opa 32545013 -w YCopy the code

2. Preparation for ADG construction

A. Configure the hosts file

The main library:

Cat <<EOF >> /etc/hosts ##FOR DG BEGIN 10.211.55.110 dbca_stby ##FOR DG END EOFCopy the code



For library:

Cat <<EOF >> /etc/hosts ##FOR DG BEGIN 10.211.55.100 rac01 10.211.55.101 rac02 10.211.55.105 rac-scan ##FOR DG END EOFCopy the code

B. Configure static listening and TNS

Added static listener to listener.ora:

Grid user execution:

rac01

cat <<EOF >> $TNS_ADMIN/listener.ora ##FOR DG BEGIN SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb19c) (ORACLE_HOME = / u01 / app/oracle/product / 19.3.0 / db) (SID_NAME = cdb19c1))) # # FOR DG END EOFCopy the code



rac02

cat <<EOF >> $TNS_ADMIN/listener.ora ##FOR DG BEGIN SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cdb19c) (ORACLE_HOME = / u01 / app/oracle/product / 19.3.0 / db) (SID_NAME = cdb19c2))) # # FOR DG END EOFCopy the code



For the library

cat <<EOF >>$TNS_ADMIN/listener.ora ##FOR DG BEGIN SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = Cdb19c_stby) (ORACLE_HOME = / u01 / app/oracle/product / 19.3.0 / db) (SID_NAME = cdb19c_stby))) # # FOR DG END EOFCopy the code



Restart the monitor

SRVCTL stop listener SRVCTL start listener ## Restart the listener LSNRCTL startCopy the code





Tnsnames. ora add TNS:

Oracle user:

cat <<EOF >> $TNS_ADMIN/tnsnames.ora ##FOR DG BEGIN CDB19C = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cdb19c) ) ) CDB19C1 = (DESCRIPTION = (ADDRESS_LIST  = (ADDRESS = (PROTOCOL = TCP)(HOST = rac01)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cdb19c) ) ) CDB19C2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rac02)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =  cdb19c) ) ) CDB19C_STBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dbca_stby)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = cdb19c_stby) ) ) ##FOR DG BEGIN EOFCopy the code

Tnsping tests connectivity:

tnsping cdb19c
tnsping cdb19c1
tnsping cdb19c2
tnsping cdb19c_stby
Copy the code







C. Configure parameters for the primary database
SQL> select force_logging,log_mode,cdb from gv$database;

FORCE_LOGGING		LOG_MODE     CDB
--------------------------------------- 
YES					ARCHIVELOG   YES
Copy the code

If mandatory logging is not enabled:

alter database force logging;
Copy the code

If archive logging is not enabled:

shutdown immediate
startup mount
alter database archivelog;
alter database open;
alter pluggable database all open;
Copy the code

D. Add stanby log files to the primary library
set line222 col member for a60 select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from v$logfile t1,v$log t2 where T1. Group# = t2. Group# order by 1, 2;Copy the code

The stanby log must be at least the same size as the redo log. 3. Standby logfile=(1+logfile group)=(1+ logfile group)=(2 +logfile group) ALTER DATABASE ADD standby LOGFILE thread 1 group 5 ('+DATA') SIZE 120M, group 6 ('+DATA') SIZE 120M, group 7 ('+DATA') SIZE 120M; ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 8 ('+DATA') SIZE 120M, group 9 ('+DATA') SIZE 120M, group 10 ('+DATA') SIZE 120M;Copy the code

select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from v$logfile t1,v$standby_log t2 where t1.group#=t2.group# The order by 1, 2;Copy the code

DBCA creates a physical standby database

Oracle user:

dbca -silent -createDuplicateDB \ -gdbName cdb19c \ -sid cdb19c \ -sysPassword oracle \ -primaryDBConnectionString 10.211.55.105:1521 / cdb19c \ - nodelist dbca_stby \ - databaseConfigType SINGLE \ - createAsStandby - dbUniqueName cdb19c_stby \ -datafileDestination '/oradata'Copy the code



The DBCA physical DG is created successfully.

4. Configure DG parameters of the active and standby databases

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB19C,CDB19C_STBY)' sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB19C,CDB19C_STBY)' sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB19C' sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=cdb19c_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB19C_STBY' sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE sid='*'; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4 sid='*'; ALTER SYSTEM SET FAL_SERVER=CDB19C_STBY sid='*'; ALTER SYSTEM SET FAL_CLIENT=CDB19C sid='*'; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='+DATA','/oradata/CDB19C_STBY' SCOPE=SPFILE sid='*'; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='+DATA','/oradata/CDB19C_STBY' SCOPE=SPFILE sid='*'; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO sid='*';Copy the code

Note: The RAC modification parameter needs to be addedsid='*'To modify multiple instances.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(CDB19C_STBY,CDB19C)'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CDB19C_STBY'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=CDB19C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CDB19C'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4; ALTER SYSTEM SET FAL_SERVER=CDB19C; ALTER SYSTEM SET FAL_CLIENT=CDB19C_STBY; ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/oradata/CDB19C_STBY','+DATA' SCOPE=SPFILE; ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/oradata/CDB19C_STBY','+DATA' SCOPE=SPFILE; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;Copy the code

View OMF parameters:

show parameter db_create_file_dest
Copy the code



Note: If both OMG and DB_FILE_NAME_CONVERT are set, OMF takes precedence.

5. Start the log application

Alter database recover managed standby database using current logfile disconnect from session; Alter system set log_archive_dest_state_2=enable sid='*';Copy the code



Six, test synchronization

Create test data from primary database:

alter session set container=orcl;



sqlplus lucifer/lucifer@orcl



Standby database check whether synchronization is performed:

At this point, the ADG is set up. Thanks for eating ~

Reference article:

12c: Using DBCA to Create a Data Guard Standby 12c

19c: Using DBCA to Create a Data Guard Standby 19c

21c: Using DBCA to Create a Data Guard Standby 21c

MOS document: Creating a Physical Standby Database using DBCA Duplicate (Doc ID 2283697.1)

If you think the article is helpful to you, please like, collect, follow, comment, and support with four keys. Your support is the biggest motivation for my creation. Thank you