[A good article recommended by ACDU] Teach you how to build Oracle ADG with DBCA

Click the "blue word" above

Follow us and enjoy more dry goods!

Preface

Oracle Data Guard is a member of Oracle MAA (Maximum Availability Architecture). The concept of STANDBY DATABASE has been introduced since the Oracle 7i version, and it has gradually been welcomed by everyone. With the change of Oracle database version, there are many ways to build a backup database. Today, we will introduce a new way to create a physical standby database, launched from the 12C version: use the DBCA command line.

dbca -createDuplicateDB -createAsStandby -dbUniqueName

Advantages and disadvantages: convenient and fast, and the construction speed is faster. But it will affect the performance of the main library, and it is inconvenient to troubleshoot errors during execution.

For specific commands, please refer to:

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]

More detailed fine reference parameters :. The createDuplicateDB command creates a duplicate of an Oracle database ( click to read the description acquisition hoplinks)

12.2.0.1 began to support DBCA to create a physical standby database:

The main library must be a stand-alone environment, non-RAC database.

The main library must be a non-CDB environment.

After 18c, the above restrictions have been removed, and the main library supported is CDB and RAC environments.

1. Environmental preparation

Ignore the environment installation process, you can refer to: 30 minutes! One-click deployment of Oracle 19C stand-alone CDB+PDB (click to read the original text for the jump link)

This test tries to simulate as far as possible in accordance with the upgrade of the production environment:

nodeHost versionCPU nameInstance nameOracle versionIP address
rac01rhel7.9rac01cdb19c19.3.0 (Patch 29585399)10.211.55.100
rac02rhel7.9rac02orcl+cdb19c19.3.0 (Patch 29585399)10.211.55.101
Backup databaserhel7.9dbca_stbyDo not create an instance19.3.0 (Patch 29585399)10.211.55.110

note:

1. The db_unique_name main and standby databases cannot be the same.

2. The db_name must be consistent between the primary and standby databases.

3. The DB versions of the main and standby databases must be consistent.

Oracle test environment installation:

Refer to: DBA operation and maintenance gospel: 10 minutes, Linux one line command to install Oracle database (click to read the original text for the jump link)

One-click installation of the main library RAC environment:

./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`

One-click installation of standby database: (Only ORACLE software is installed for standby database, no database is built)

./OracleShellInstall.sh -i 10.211.55.110 -n dbca_stby -o cdb19c -opa 32545013 -w Y

2. ADG construction preparation

a. Configure the hosts file

Main library:

cat <<EOF >> /etc/hosts##FOR DG BEGIN10.211.55.110 dbca_stby##FOR DG ENDEOF

Backup database:

cat <<EOF >> /etc/hosts##FOR DG BEGIN10.211.55.100 rac0110.211.55.101 rac0210.211.55.105 rac-scan##FOR DG ENDEOF
b. Configure static monitoring and TNS

listener.ora adds static monitoring:

Grid user executes:

rac01

cat <<EOF >> $TNS_ADMIN/listener.ora##FOR DG BEGINSID_LIST_LISTENER =	(SID_LIST =		(SID_DESC =			(GLOBAL_DBNAME = cdb19c)			(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)			(SID_NAME = cdb19c1)		)	)##FOR DG ENDEOF

rac02

cat <<EOF >> $TNS_ADMIN/listener.ora##FOR DG BEGINSID_LIST_LISTENER =	(SID_LIST =		(SID_DESC =			(GLOBAL_DBNAME = cdb19c)			(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db)			(SID_NAME = cdb19c2)		)	)##FOR DG ENDEOF

Backup database

cat <<EOF >>$TNS_ADMIN/listener.ora##FOR DG BEGINSID_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 ENDEOF

Restart monitor

#主库RAC重启监听srvctl stop listenersrvctl start listener ##备库重启监听lsnrctl start

tnsnames.ora adds TNS:

The oracle user executes:

cat <<EOF >> $TNS_ADMIN/tnsnames.ora##FOR DG BEGINCDB19C =(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 BEGINEOF

tnsping test connectivity:

tnsping cdb19ctnsping cdb19c1tnsping cdb19c2tnsping cdb19c_stby
c. Main library configuration parameters
SQL> select force_logging,log_mode,cdb from gv$database; FORCE_LOGGING		LOG_MODE     CDB--------------------------------------- YES					ARCHIVELOG   YES

If the mandatory log is not enabled:

alter database force logging;

If the archive log is not turned on:

shutdown immediatestartup mountalter database archivelog;alter database open;alter pluggable database all open;
d. Add the stanby log file to the main library
set line222col member for a60select 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;
--需要注意:--1.stanby log日志大小至少要和redo log日志一样大小,不能小于--2.stanby log数量:standby logfile=(1+logfile组数)=(1+2)=3组,每个thread需要加3组standby logfile.--3.thread要与redo log保持一致,如果是rac,需要增加多个thread对应的standby log 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 2group 8 ('+DATA') SIZE 120M,group 9 ('+DATA') SIZE 120M,group 10 ('+DATA') SIZE 120M;
select t2.thread#,t1.group#,t1.member,t2.bytes/1024/1024 from v$logfile t1,v$standby_log t2 where t1.group#=t2.group# order by 1,2;

Three, DBCA creates a physical standby database

The oracle user executes:

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'

Fourth, configure the main database + standby database DG parameters

--主库设置DG参数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='*';

Note: RAC modification parameters need to be added  sid='*'to modify multiple instances.

--备库设置DG参数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;

View OMF parameter configuration:

show parameter db_create_file_dest

Note: If the OMG and DB_FILE_NAME_CONVERT parameters are set at the same time, the OMF parameter will take precedence.

Five, open 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='*';

Six, test synchronization

The main library creates test data:

alter session set container=orcl;


sqlplus lucifer/[email protected]


Check whether the standby database is synchronized:

At this point, ADG has been set up.

Reference article:

  • Official document 12c: Using DBCA to Create a Data Guard Standby 12C
  • Official document 19c: Using DBCA to Create a Data Guard Standby 19C
  • Official document 21c: Using DBCA to Create a Data Guard Standby 21C
  • MOS文档:Creating a Physical Standby database using DBCA duplicate (Doc ID 2283697.1)

About the author

Liu Pengcheng is a member of China DBA Alliance (ACDU). Currently engaged in Oracle DBA work, and once engaged in Oracle database development work, mainly serving manufacturing, auto finance and other industries. Now has Oracle OCP, OceanBase OBCA certification, good at Oracle database operation and maintenance development, backup and recovery, installation and migration, Linux automatic operation and maintenance script writing, etc.

END

Recommended reading: 267 pages! 2020 Annual Database Technology Annual

Recommended download: 2020 Data Technology Carnival PPT download


Nearly 50 PPT downloads and video playbacks of the 2020 Data Technology Carnival have been uploaded to the Motianlun platform, which can be obtained by replying to the keyword " 2020DTC " on the " Data and Cloud " official account !

The new video program "Three Minutes of Data" jointly produced by ACDU (China DBA Alliance) and Mo Tianlun has been released for multiple issues. You can quickly learn about the dynamics of the data industry and pay attention to our video number! ↓↓↓

Click the picture below to see more ↓

Yunhe Enmo Lecture Hall| A place to share and communicate

Long press to identify the QR code and join the 10,000-person exchange community

Please note: Yunhe Enmo Lecture Hall

Click "Looking"

Your likes will be seen ❤