Oct 14 2019

Oracle 19c R3 Active Data Guard Installation on Oracle Linux 7.7

Category: DatabasesFatih Acar @ 10:49

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_home

Enter 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_home

Enter 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/tfactl

Note :
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
ARCHIVELOG

SQL> 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 ORA19C

sqlplus / 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.0

Copyright (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.0

Copyright (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 archived

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format ‘/oracle/db/19.3.0/db_home/dbs/orapwORA19CD1’ ;
}
executing Memory Script

Starting 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-19

contents 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 Script

sql 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_1

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: 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-19

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql 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 database

contents 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 Script

executing 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_1

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 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-19

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service ‘PRI_ORA19C’
archivelog from scn 3053451;
switch clone datafile all;
}
executing Memory Script

Starting restore at 14-OCT-19
using channel ORA_AUX_DISK_1

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=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-19

datafile 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.1021629617

contents of Memory Script:
{
set until scn 3055123;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-OCT-19
using channel ORA_AUX_DISK_1

starting 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-19

contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script

released 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 objects

Finished 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.0

Copyright (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.0

SQL> 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

276 total views, 2 views today

Tags: Oracle 19c, Oracle 19c Data Guard, Oracle Administration

facebook comments:

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.