Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Oracle Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Oracle Data Guard maintains these standby databases as copies of the production database.Then, if the production database becomes unavailable because of a planned or an unplanned outage, Oracle Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage. Oracle Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.
With Oracle Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.
Types of standby databases are Physical standby database, Logical standby database and Snapshot standby database.
I will demonstrate Physical standby database as working real time apply at this document. Most used type is Physical standby database type. You can investigate other types of standby database from Oracle docs.
I used Oracle RAC database as primary side and I used single instance database with asm file system as secondary side (Data Guard).
Firstly, you have to adjust system parameters of operating system on secondary side and create asm disks. After, you can start to install of grid infrastructure. You can create disk groups for DATA and FRA disk groups after install grid. After grid installation, you can install Oracle Data Guard database on grid infrastructure and ASM disks as software only. Finally you can configure parameter and listeners on both sides and start apply. I divide the stages of installation four steps.
You can find primary side (Oracle 19c R3 RAC) installation steps here. I will use this infrastructure as primary side.
First Step : Configure Operation System on Standby Server
1. Upgrade All Packages
yum upgrade
2. Selinux Disable or Permissive
You can do disabled or permissive
vi /etc/selinux/config
SELINUX=permissive
3. Firewall Stop and Disable
You can open firewall after installation, but you have to give permission to necessary ports and services.
systemctl stop firewalld.service
systemctl disable firewalld.service
4. Chrony NTP Configuration
Chrony plugin came with Oracle Linux 7 version for ntp sync. You can write your ntp server information in /etc/chrony.conf file to time sync.
on each node
vi /etc/chrony.conf
server yourntpaddress iburst
systemctl restart chronyd.service
systemctl enable chronyd.service
5. Package Installation
on each node
yum install oracle-database-preinstall-19c
yum install oracleasm-support
reboot
6. Create Oracle and Grid Users
When you run “yum install oracle-database-preinstall-19c”, oracle user created. You can see oracle user info with “id oracle” command. uid and gid have to be the same on both servers.
id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)Also I will create new groups to grid user. And then I will create grid user with adding to new groups on both servers.
groupadd -g 54333 asmdba
groupadd -g 54334 asmoper
groupadd -g 54335 asmadmin
useradd -m -u 54341 -g oinstall -G dba,asmadmin,asmdba,asmoper,racdba -d /home/grid -s /bin/bash grid
passwd oracle
passwd grid
oracle user add to asmdba,asmadmin group.
usermod -a -G asmdba,asmadmin oracle
passwd oracle
passwd grid
7. Create Directories
mkdir -p /oracle/grid/19.3.0/grid_home
mkdir -p /oracle/grid/gridbase/
mkdir -p /oracle/db/19.3.0/db_home
chown -R oracle.oinstall /oracle/
chown -R grid.oinstall /oracle/grid/
chmod -R 775 /oracle/
8. Configure Bash Profile of Users.
Add below parameters to bash_profiles.
Oracle user bash_profile
vi .bash_profile
# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=oracle19cdg1.yourdomain
export ORACLE_UNQNAME=ORA19CD1
export ORACLE_BASE=/oracle/db/19.3.0
export DB_HOME=$ORACLE_BASE/db_home
export ORACLE_HOME=$DB_HOME
export ORACLE_SID=ORA19CD1
export ORACLE_TERM=xterm
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
Grid user bash_profile
vi .bash_profile
# Grid Settings
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=oracle19cdg1.yourdomain
export ORACLE_BASE=/oracle/grid/gridbase
export ORACLE_HOME=/oracle/grid/19.3.0/grid_home
export GRID_BASE=/oracle/grid/gridbase
export GRID_HOME=/oracle/grid/19.3.0/grid_home
export ORACLE_SID=+ASM
export ORACLE_TERM=xterm
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
9. Configure Hosts File
vi /etc/hosts
11.12.13.15 oracle19c1 oracle19c1.yourdomain
11.12.13.16 oracle19c2 oracle19c2.yourdomain
11.12.13.17 oracle19c1-vip oracle19c1-vip.yourdomain
11.12.13.18 oracle19c2-vip oracle19c2-vip.yourdomain
11.12.13.19 oracle19c-scan oracle19c-scan.yourdomain
11.12.13.20 oracle19c-scan oracle19c-scan.yourdomain
11.12.13.21 oracle19c-scan oracle19c-scan.yourdomain
11.12.13.21 oracle19cdg1 oracle19cdg1.yourdomain
10. Configure Passwordless SSH Connection
You have to configure ssh passwordless connection for oracle and grid user.
You can find how to do this step here.
11. DNS Register
You can configure dns information of nodes in /etc/resolf.conf file.
12. Configure ASM Disks
Your disk name can be different from I used. I configured sdc, sdd,sde disks for asm on my test suite. These sd* disks can be different on your system.
Configure oracleasm with root user on both servers.
oracleasm configure -i
Answers of Questions;
grid
asmadmin
y
y
After finish configuration you can make init with below command.
oracleasm init
Create partition and asm disk on only first server.
fdisk /dev/sdc
fdisk /dev/sdd
fdisk /dev/sde
oracleasm createdisk DATA1 /dev/sdb1
oracleasm createdisk FRA1 /dev/sdc1
oracleasm createdisk OCR_VOTING1 /dev/sdd1
You can use scandisks and listdisks to control on both servers.
oracleasm scandisks
oracleasm listdisks
Second Step : Install Grid Infrastructure
You have to copy your grid installation files to grid_home. I recommend that you copy grid zip file to grid home and change owner of zip file, and unzip with grid user.
1. Copy and Unzip Grid Files
cp LINUX.X64_193000_grid_home.zip /oracle/grid/19.3.0/grid_home/
chown grid.oinstall LINUX.X64_193000_grid_home.zip
su – grid
unzip LINUX.X64_193000_grid_home.zip
2. Display Config and Start Grid Installation
su –
export DISPLAY=:0.0
xhost +
su – grid
export DISPLAY=:0.0
xhost +
cd /$GRID_HOME/
./grid_Setup.sh
3. Grid Infrastructure Installation Steps
[root@oracle19cdg1 ~]# /oracle/grid/19.3.0/grid_home/root.sh
Performing root user operation.The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /oracle/grid/19.3.0/grid_homeEnter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /oracle/grid/19.3.0/grid_home/crs/install/crsconfig_params
The log of current session can be found at:
/oracle/grid/gridbase/crsdata/oracle19cdg1/crsconfig/roothas_2019-10-11_03-46-04PM.log
LOCAL ADD MODE
Creating OCR keys for user ‘grid’, privgrp ‘oinstall’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
CRS-4664: Node oracle19cdg1 successfully pinned.
2019/10/11 15:46:22 CLSRSC-330: Adding Clusterware entries to file ‘oracle-ohasd.service’oracle19cdg1 2019/10/11 15:49:16 /oracle/grid/gridbase/crsdata/oracle19cdg1/olr/backup_20191011_154916.olr 724960844
2019/10/11 15:49:17 CLSRSC-327: Successfully configured Oracle Restart for a standalone server
4. ASM Disk Configuration
Start ASMCA
su –
export DISPLAY=:0.0
xhost +
su – grid
export DISPLAY=:0.0
xhost +
asmca
Third Step : Install Database Software
You have to copy your database installation files to oracle_home. I recommend that you copy database zip file to oracle home and change owner of zip file, and unzip with oracle user.
1. Copy and Unzip Database Files
cp LINUX.X64_193000_db_home.zip /oracle/db/19.3.0/db_home/
chown oracle.oinstall LINUX.X64_193000_db_home.zip
su – oracle
unzip LINUX.X64_193000_db_home.zip
2. Display Config and Start Database Installation
su –
export DISPLAY=:0.0
xhost +
su – oracle
export DISPLAY=:0.0
xhost +
cd /$ORACLE_HOME/
./runInstaller.sh
[root@oracle19cdg1 ~]# /oracle/db/19.3.0/db_home/root.sh
Performing root user operation.The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/db/19.3.0/db_homeEnter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The contents of “oraenv” have not changed. No need to overwrite.
The contents of “coraenv” have not changed. No need to overwrite.Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Oracle Trace File Analyzer (TFA – Standalone Mode) is available at :
/oracle/db/19.3.0/db_home/bin/tfactlNote :
1. tfactl will use TFA Service if that service is running and user has been granted access
2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed
Fourth Step : Configure Parameters and Listeners
Primary Side
Node 1
Add new listener and configure tnsnames.ora for data guard connection
su – oracle
cd $ORACLE_HOME/network/admin
vi listener.ora
listener_for_dataguard =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = oracle19c1-vip.sm.gov.tr)(Port = 1541))
)SID_LIST_LISTENER_FOR_DATAGUARD =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/db/19.3.0/db_home)
(SID_NAME = ORA19C1)
)
)
vi tnsnames.ora
ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)ORA19C1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)ORA19C2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)PRI_ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c1-vip)(PORT = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)DG_ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19cdg1)(PORT = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19CD1)
)
)[oracle@oracle19c1 admin]$ lsnrctl start listener_for_dataguard
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 11-OCT-2019 16:26:30
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /oracle/db/19.3.0/db_home/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /oracle/db/19.3.0/db_home/network/admin/listener.ora
Log messages written to /oracle/db/19.3.0/diag/tnslsnr/oracle19c1/listener_for_dataguard/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.9.17)(PORT=1541)))Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=oracle19c1-vip.sm.gov.tr)(Port=1541))
STATUS of the LISTENER
————————
Alias listener_for_dataguard
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 11-OCT-2019 16:26:31
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/db/19.3.0/db_home/network/admin/listener.ora
Listener Log File /oracle/db/19.3.0/diag/tnslsnr/oracle19c1/listener_for_dataguard/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.9.17)(PORT=1541)))
Services Summary…
Service “ORA19C1” has 1 instance(s).
Instance “ORA19C1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
Node 2
su – oracle
cd $ORACLE_HOME/network/admin
vi listener.ora
listener_for_dataguard =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = oracle19c2-vip.sm.gov.tr)(Port = 1541))
)SID_LIST_LISTENER_FOR_DATAGUARD =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/db/19.3.0/db_home)
(SID_NAME = ORA19C2)
)
)vi tnsnames.ora
ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)ORA19C1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)ORA19C2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)PRI_ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c2-vip)(PORT = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19C)
)
)DG_ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19cdg1)(PORT = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19CD1)
)
)[oracle@oracle19c2 admin]$ lsnrctl start listener_for_dataguard
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 11-OCT-2019 16:27:02
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /oracle/db/19.3.0/db_home/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /oracle/db/19.3.0/db_home/network/admin/listener.ora
Log messages written to /oracle/db/19.3.0/diag/tnslsnr/oracle19c2/listener_for_dataguard/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.9.18)(PORT=1541)))Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=oracle19c2-vip.sm.gov.tr)(Port=1541))
STATUS of the LISTENER
————————
Alias listener_for_dataguard
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 11-OCT-2019 16:27:02
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/db/19.3.0/db_home/network/admin/listener.ora
Listener Log File /oracle/db/19.3.0/diag/tnslsnr/oracle19c2/listener_for_dataguard/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.1.9.18)(PORT=1541)))
Services Summary…
Service “ORA19C2” has 1 instance(s).
Instance “ORA19C2”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
Standby Side
Add new listener and configure tnsnames.ora for data guard connection
su – oracle
cd $ORACLE_HOME/network/admin
vi listener.ora
listener_for_dataguard =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = oracle19cdg1)(Port = 1541))
)SID_LIST_LISTENER_FOR_DATAGUARD =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/db/19.3.0/db_home)
(SID_NAME = ORA19CD1)
)
)vi tnsnames.ora
PRI_ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19c1-vip)(PORT = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = ORA19C1)
)
)DG_ORA19C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle19cdg1)(PORT = 1541))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA19CD1)
)
)[oracle@oracle19cdg1 admin]$ lsnrctl start listener_for_dataguard
LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 11-OCT-2019 16:50:59
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /oracle/db/19.3.0/db_home/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 19.0.0.0.0 – Production
System parameter file is /oracle/db/19.3.0/db_home/network/admin/listener.ora
Log messages written to /oracle/db/19.3.0/diag/tnslsnr/oracle19cdg1/listener_for_dataguard/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19cdg1)(PORT=1541) ))Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=oracle19cdg1)(Port=1541))
STATUS of the LISTENER
————————
Alias listener_for_dataguard
Version TNSLSNR for Linux: Version 19.0.0.0.0 – Production
Start Date 11-OCT-2019 16:50:59
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/db/19.3.0/db_home/network/admin/listener.ora
Listener Log File /oracle/db/19.3.0/diag/tnslsnr/oracle19cdg1/listener_for_dataguard/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle19cdg1)(PORT=1541)))
Services Summary…
Service “ORA19CD1” has 1 instance(s).
Instance “ORA19CD1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
Primary Side
Configure parameters
Database has to be archivelog mode
sqlplus / as sysdba
SQL> select log_mode from gv$database;
LOG_MODE
————
ARCHIVELOG
ARCHIVELOGSQL> alter database force logging;
Database altered.
SQL> alter system set db_file_name_convert=’+DATA/ORA19CD1/DATAFILE’,’+DATA/ORA19C/DATAFILE’,’+FRA/ORA19CD1/DATAFILE’,’+FRA/ORA19C/DATAFILE’ scope=spfile;
System altered.
SQL> alter system set log_file_name_convert=’+DATA/ORA19CD1/ONLINELOG’,’+DATA/ORA19C/ONLINELOG’,’+FRA/ORA19CD1/ONLINELOG’,’+FRA/ORA19C/ONLINELOG’ scope=spfile;
System altered.
SQL> alter system set log_archive_config=’DG_CONFIG=(ORA19C,ORA19CD1)’;
System altered.
SQL> alter system set log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA19C’;
System altered.
SQL> alter system set log_archive_dest_2=’SERVICE=DG_ORA19C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA19CD1′;
System altered.
SQL> alter system set log_archive_dest_state_2=’ENABLE’;
System altered.
SQL> alter system set log_archive_format=’%t_%s_%r.arc’ scope=spfile;
System altered.
SQL> alter system set fal_client=’PRI_ORA19C’;
System altered.
SQL> alter system set fal_server=’DG_ORA19C’;
System altered.
SQL> alter system set standby_file_management=’AUTO’;
System altered.
SQL> alter system set remote_login_passwordfile=’exclusive’ scope=spfile;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> alter database add standby logfile size 200M;
Database altered.
SQL> exit
srvctl stop database -d ORA19C
srvctl start database -d ORA19Csqlplus / as sysdba
SQL> create pfile=’/tmp/pfile.ora’ from spfile;
File created.
more /tmp/pfile.ora
ORA19C1.__data_transfer_cache_size=0
ORA19C2.__data_transfer_cache_size=0
ORA19C1.__db_cache_size=2214592512
ORA19C2.__db_cache_size=2214592512
ORA19C1.__inmemory_ext_roarea=0
ORA19C2.__inmemory_ext_roarea=0
ORA19C1.__inmemory_ext_rwarea=0
ORA19C2.__inmemory_ext_rwarea=0
ORA19C1.__java_pool_size=0
ORA19C2.__java_pool_size=0
ORA19C1.__large_pool_size=16777216
ORA19C2.__large_pool_size=16777216
ORA19C1.__oracle_base=’/oracle/db/19.3.0’#ORACLE_BASE set from environment
ORA19C2.__oracle_base=’/oracle/db/19.3.0’#ORACLE_BASE set from environment
ORA19C1.__pga_aggregate_target=1023410176
ORA19C2.__pga_aggregate_target=1023410176
ORA19C1.__sga_target=3070230528
ORA19C2.__sga_target=3070230528
ORA19C1.__shared_io_pool_size=134217728
ORA19C2.__shared_io_pool_size=134217728
ORA19C1.__shared_pool_size=687865856
ORA19C2.__shared_pool_size=687865856
ORA19C1.__streams_pool_size=0
ORA19C2.__streams_pool_size=0
ORA19C1.__unified_pga_pool_size=0
ORA19C2.__unified_pga_pool_size=0
*.audit_file_dest=’/oracle/db/19.3.0/admin/ORA19C/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’19.0.0′
*.control_files=’+DATA/ORA19C/CONTROLFILE/current.261.1021367051′,’+FRA/ORA19C/CONTROLFILE/current.256.1021367053′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_file_name_convert=’+DATA/ORA19CD1/DATAFILE’,’+DATA/ORA19C/DATAFILE’,’+FRA/ORA19CD1/DATAFILE’,’+FRA/ORA19C/DATAFILE’
*.db_name=’ORA19C’
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=20000m
*.diagnostic_dest=’/oracle/db/19.3.0′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19CXDB)’
*.fal_client=’PRI_ORA19C’
*.fal_server=’DG_ORA19C’
family:dw_helper.instance_mode=’read-only’
ORA19C1.instance_number=1
ORA19C2.instance_number=2
*.local_listener=’-oraagent-dummy-‘
*.log_archive_config=’DG_CONFIG=(ORA19C,ORA19CD1)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA19C’
*.log_archive_dest_2=’SERVICE=DG_ORA19C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA19CD1′
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/ORA19CD1/ONLINELOG’,’+DATA/ORA19C/ONLINELOG’,’+FRA/ORA19CD1/ONLINELOG’,’+FRA/ORA19C/ONLINELOG’
*.nls_language=’AMERICAN’
*.nls_territory=’AMERICA’
*.open_cursors=300
*.pga_aggregate_target=974m
*.processes=320
*.remote_login_passwordfile=’exclusive’
*.sga_target=2922m
*.standby_file_management=’AUTO’
ORA19C2.thread=2
ORA19C1.thread=1
ORA19C1.undo_tablespace=’UNDOTBS1′
ORA19C2.undo_tablespace=’UNDOTBS2′
Create password file
orapwd file=$ORACLE_HOME/dbs/orapwORA19C
Copy password file and pfile to standby with scp
Standby Side
cd $ORACLE_HOME/dbs
mv orapwORA19C orapwORA19CD1
Configure pfile like below.
vi /tmp/pfile.ora
*.__data_transfer_cache_size=0
*.__db_cache_size=2231369728
*.__inmemory_ext_roarea=0
*.__inmemory_ext_rwarea=0
*.__java_pool_size=0
*.__large_pool_size=16777216
*.__oracle_base=’/oracle/db/19.3.0’#ORACLE_BASE set from environment
*.__pga_aggregate_target=1023410176
*.__sga_target=3070230528
*.__shared_io_pool_size=134217728
*.__shared_pool_size=671088640
*.__streams_pool_size=0
*.__unified_pga_pool_size=0
*.audit_file_dest=’/oracle/db/19.3.0/admin/ORA19CD1/adump’
*.audit_trail=’db’
*.cluster_database=false
*.compatible=’19.0.0′
*.control_files=’+DATA/ORA19CD1/CONTROLFILE/current.261.1021367051′,’+FRA/ORA19CD1/CONTROLFILE/current.256.1021367053′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_file_name_convert=’+DATA/ORA19C/DATAFILE’,’+DATA/ORA19CD1/DATAFILE’,’+FRA/ORA19C/DATAFILE’,’+FRA/ORA19CD1/DATAFILE’
*.db_name=’ORA19C’
*.db_unique_name=’ORA19CD1′
*.db_recovery_file_dest=’+FRA’
*.db_recovery_file_dest_size=20000m
*.diagnostic_dest=’/oracle/db/19.3.0′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA19CD1XDB)’
*.fal_client=’DG_ORA19C’
*.fal_server=’PRI_ORA19C’
family:dw_helper.instance_mode=’read-only’
*.local_listener=’-oraagent-dummy-‘
*.log_archive_config=’DG_CONFIG=(ORA19C,ORA19CD1)’
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA19CD1′
*.log_archive_dest_2=’SERVICE=PRI_ORA19C ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA19C’
*.log_archive_dest_state_2=’DEFER’
*.log_archive_format=’%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/ORA19C/ONLINELOG’,’+DATA/ORA19CD1/ONLINELOG’,’+FRA/ORA19C/ONLINELOG’,’+FRA/ORA19CD1/ONLINELOG’
*.nls_language=’AMERICAN’
*.nls_territory=’AMERICA’
*.open_cursors=300
*.pga_aggregate_target=974m
*.processes=320
*.remote_login_passwordfile=’exclusive’
*.sga_target=2922m
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
mkdir -p /oracle/db/19.3.0/admin/ORA19CD1/adump
[oracle@oracle19cdg1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Oct 14 09:57:24 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile=’/tmp/pfile.ora’;
File created.
SQL> startup nomount;
ORACLE instance started.Total System Global Area 3070227080 bytes
Fixed Size 8901256 bytes
Variable Size 687865856 bytes
Database Buffers 2365587456 bytes
Redo Buffers 7872512 bytes
SQL> exit;
Initialize Data Guard
[oracle@oracle19cdg1 dbs]$ rman target sys@PRI_ORA19C auxiliary sys@DG_ORA19C
Recovery Manager: Release 19.0.0.0.0 – Production on Mon Oct 14 09:58:56 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORA19C (DBID=1074555083)
auxiliary database Password:
connected to auxiliary database: ORA19C (not mounted)RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 14-OCT-19
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=390 device type=DISK
current log archivedcontents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format ‘/oracle/db/19.3.0/db_home/dbs/orapwORA19CD1’ ;
}
executing Memory ScriptStarting backup at 14-OCT-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 instance=ORA19C1 device type=DISK
Finished backup at 14-OCT-19contents of Memory Script:
{
sql clone “alter system set control_files =
”+DATA/ORA19CD1/CONTROLFILE/current.256.1021629589”, ”+FRA/ORA19CD1/CONTROLFILE/current.256.1021629589” comment=
”Set by RMAN” scope=spfile”;
restore clone from service ‘PRI_ORA19C’ standby controlfile;
}
executing Memory Scriptsql statement: alter system set control_files = ”+DATA/ORA19CD1/CONTROLFILE/current.256.1021629589”, ”+FRA/ORA19CD1/CONTROLFILE/current.256.1021629589” comment= ”Set by RMAN” scope=spfile
Starting restore at 14-OCT-19
using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ORA19CD1/CONTROLFILE/current.258.1021629593
output file name=+FRA/ORA19CD1/CONTROLFILE/current.258.1021629593
Finished restore at 14-OCT-19contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Scriptsql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/ORA19C/TEMPFILE/temp.264.1021367069 conflicts with a file used by the target databasecontents of Memory Script:
{
set newname for tempfile 1 to
“+DATA”;
switch clone tempfile all;
set newname for datafile 1 to
“+DATA”;
set newname for datafile 3 to
“+DATA”;
set newname for datafile 4 to
“+DATA”;
set newname for datafile 5 to
“+DATA”;
set newname for datafile 7 to
“+DATA”;
restore
from nonsparse from service
‘PRI_ORA19C’ clone database
;
sql ‘alter system archive log current’;
}
executing Memory Scriptexecuting command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 14-OCT-19
using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-OCT-19sql statement: alter system archive log current
current log archivedcontents of Memory Script:
{
restore clone force from service ‘PRI_ORA19C’
archivelog from scn 3053451;
switch clone datafile all;
}
executing Memory ScriptStarting restore at 14-OCT-19
using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=14
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=7
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=8
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service PRI_ORA19C
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=9
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-OCT-19datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=1021629632 file name=+DATA/ORA19CD1/DATAFILE/system.259.1021629601
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1021629632 file name=+DATA/ORA19CD1/DATAFILE/sysaux.260.1021629607
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1021629632 file name=+DATA/ORA19CD1/DATAFILE/undotbs1.261.1021629615
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=1021629632 file name=+DATA/ORA19CD1/DATAFILE/undotbs2.262.1021629617
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=1021629632 file name=+DATA/ORA19CD1/DATAFILE/users.263.1021629617contents of Memory Script:
{
set until scn 3055123;
recover
standby
clone database
delete archivelog
;
}
executing Memory Scriptexecuting command: SET until clause
Starting recover at 14-OCT-19
using channel ORA_AUX_DISK_1starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file +FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_1_seq_13.259.1021629627
archived log for thread 1 with sequence 14 is already on disk as file +FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_1_seq_14.260.1021629627
archived log for thread 2 with sequence 8 is already on disk as file +FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_8.262.1021629631
archived log for thread 2 with sequence 9 is already on disk as file +FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_9.263.1021629631
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_1_seq_13.259.1021629627 thread=1 sequence=13
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_8.262.1021629631 thread=2 sequence=8
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_9.263.1021629631 thread=2 sequence=9
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_1_seq_14.260.1021629627 thread=1 sequence=14
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-OCT-19contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Scriptreleased channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 instance=ORA19C1 device type=DISK
deleted archived log
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_1_seq_13.259.1021629627 RECID=1 STAMP=1021629626
deleted archived log
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_1_seq_14.260.1021629627 RECID=2 STAMP=1021629627
deleted archived log
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_7.261.1021629629 RECID=3 STAMP=1021629629
deleted archived log
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_8.262.1021629631 RECID=4 STAMP=1021629630
deleted archived log
archived log file name=+FRA/ORA19CD1/ARCHIVELOG/2019_10_14/thread_2_seq_9.263.1021629631 RECID=5 STAMP=1021629631
Deleted 5 objectsFinished Duplicate Db at 14-OCT-19
RMAN> exit;
Start Data Guard as Active Data Guard
[oracle@oracle19cdg1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Oct 14 10:01:44 2019
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
Activate DML Redirect
Primary Side
SQL> alter system set adg_redirect_dml=true scope=both;
Standby Side
SQL> alter system set adg_redirect_dml=true scope=both;
You can test DML operations on standby side. You have to connect standby side with username password otherwise you get an error just as ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed
Source : Oracle Docs