Database and Middleware Knowledge Sharing: A step-by-step guide with commands for configuring Oracle 19c Data Guard Physical Standby

Copy the Oracle 19c binary from Prod server to the Standby server:

Source:

[oracle@itauchtdsuat-a-db dbhome_1]$ pwd

/apps/oracle/product/19.3.0/dbhome_1

[oracle@itauchtdsuat-a-db dbhome_1]$ scp LINUX.X64_193000_db_home.zip oracle@10.111.64.35:/apps/oracle/product/19.3.0/dbhome_1

The authenticity of host '10.111.64.35 (10.111.64.35)' can't be established.

ECDSA key fingerprint is SHA256:GPEfgpSZtvYkuGNcnTAHYUpoVS7f/w9qOO6ugqf7Jk4.

ECDSA key fingerprint is MD5:0c:5f:48:cf:d2:b4:f2:6c:58:1a:e6:90:8a:92:3a:bd.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '10.111.64.35' (ECDSA) to the list of known hosts.

oracle@10.111.64.35's password:

LINUX.X64_193000_db_home.zip 100% 2918MB 161.2MB/s 00:18

Target:

[oracle@itauchtdslive-b-db product]$ pwd

/apps/oracle/product

[oracle@itauchtdslive-b-db product]$ mkdir -p 19.3.0

[oracle@itauchtdslive-b-db product]$ cd 19.3.0

[oracle@itauchtdslive-b-db 19.3.0]$ mkdir -p dbhome_1

[oracle@itauchtdslive-b-db 19.3.0]$ cd dbhome_1/

[oracle@itauchtdslive-b-db dbhome_1]$ ls -ltr

total 0

[oracle@itauchtdslive-b-db dbhome_1]$ pwd

/apps/oracle/product/19.3.0/dbhome_1

[oracle@itauchtdslive-b-db dbhome_1]$ df -h .

Filesystem Size Used Avail Use% Mounted on

/dev/sdc1 630G 430G 169G 72% /apps

[oracle@itauchtdslive-b-db dbhome_1]$ ls -ltr

total 2988000

-rw-r--r--. 1 oracle oinstall 3059705302 Jul 24 14:28 LINUX.X64_193000_db_home.zip

[oracle@itauchtdslive-b-db dbhome_1]$ pwd

/apps/oracle/product/19.3.0/dbhome_1

[oracle@itauchtdslive-b-db dbhome_1]$

Unzip the Oracle binary to install it in the home directory.

[oracle@itauchtdslive-b-db dbhome_1]$ unzip LINUX.X64_193000_db_home.zip

Once the unzip process completed then we could initiate the software installation.

[oracle@itauchtdslive-b-db dbhome_1]$ pwd

/apps/oracle/product/19.3.0/dbhome_1

[oracle@itauchtdslive-b-db dbhome_1]$ ls -ltr *run*

-rwxr-x---. 1 oracle oinstall 1783 Mar 8 2017 runInstaller

[oracle@itauchtdslive-b-db dbhome_1]$

Install the 19c binary on the standby server.

Note: We've planned to rename the mount point on the standby server, which is similar to the production server, to make the sync of the datafile catch up easily. [/oradata2 to /oradata19c]

[root@itauchtdslive-b-db ~]# df -h

Filesystem Size Used Avail Use% Mounted on

devtmpfs 63G 0 63G 0% /dev

tmpfs 63G 0 63G 0% /dev/shm

tmpfs 63G 4.0G 59G 7% /run

tmpfs 63G 0 63G 0% /sys/fs/cgroup

/dev/mapper/rootvg-rootlv 2.0G 81M 2.0G 4% /

/dev/mapper/rootvg-usrlv 10G 1.8G 8.3G 18% /usr

/dev/mapper/rootvg-varlv 25G 8.2G 17G 33% /var

/dev/mapper/rootvg-optlv 2.0G 733M 1.3G 36% /opt

/dev/mapper/rootvg-tmplv 2.0G 33M 2.0G 2% /tmp

/dev/mapper/rootvg-homelv 1014M 430M 585M 43% /home

/dev/sda2 494M 122M 373M 25% /boot

/dev/sda1 500M 9.9M 490M 2% /boot/efi

/dev/sdc1 630G 440G 159G 74% /apps

/dev/sde1 2.0T 94G 1.8T 5% /oradata2

/dev/sdg1 2.0T 1.8T 113G 95% /oradata

/dev/sdd1 2.0T 17G 1.9T 1% /backupdrive

/dev/sdb1 591G 73M 561G 1% /mnt

tmpfs 13G 0 13G 0% /run/user/1000

tmpfs 13G 0 13G 0% /run/user/54321

[root@itauchtdslive-b-db ~]# pwd

/root

[root@itauchtdslive-b-db ~]# fuser -cu /oradata2

/oradata2: 4107(oracle) 4109(oracle) 15251(oracle) 15253(oracle) 15255(oracle) 15257(oracle) 15259(oracle) 15261(oracle) 15263(oracle) 15265(oracle) 15267(oracle) 15269(oracle) 15271(oracle) 15273(oracle) 15275(oracle) 15277(oracle) 15279(oracle) 15281(oracle) 15283(oracle)

[root@itauchtdslive-b-db ~]# kill -9 4107

[root@itauchtdslive-b-db ~]# kill -9 15273

-bash: kill: (15273) - No such process

[root@itauchtdslive-b-db ~]# kill -9 15251

-bash: kill: (15251) - No such process

[root@itauchtdslive-b-db ~]# kill -9 4109

-bash: kill: (4109) - No such process

[root@itauchtdslive-b-db ~]# fuser -cu /oradata2

[root@itauchtdslive-b-db ~]# umount /dev/sde1

[root@itauchtdslive-b-db ~]# umount /oradata2

umount: /oradata2: not mounted

[root@itauchtdslive-b-db ~]# mkdir -p /oradata19c

[root@itauchtdslive-b-db ~]# mount /dev/sde1 /oradata19c

[root@itauchtdslive-b-db ~]# mount -l

sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,relatime,seclabel)

proc on /proc type proc (rw,nosuid,nodev,noexec,relatime)

devtmpfs on /dev type devtmpfs (rw,nosuid,seclabel,size=65919520k,nr_inodes=16479880,mode=755)

securityfs on /sys/kernel/security type securityfs (rw,nosuid,nodev,noexec,relatime)

tmpfs on /dev/shm type tmpfs (rw,nosuid,nodev,seclabel)

devpts on /dev/pts type devpts (rw,nosuid,noexec,relatime,seclabel,gid=5,mode=620,ptmxmode=000)

tmpfs on /run type tmpfs (rw,nosuid,nodev,seclabel,mode=755)

tmpfs on /sys/fs/cgroup type tmpfs (ro,nosuid,nodev,noexec,seclabel,mode=755)

cgroup on /sys/fs/cgroup/systemd type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,xattr,release_agent=/usr/lib/systemd/systemd-cgroups-agent,name=systemd)

pstore on /sys/fs/pstore type pstore (rw,nosuid,nodev,noexec,relatime)

cgroup on /sys/fs/cgroup/hugetlb type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,hugetlb)

cgroup on /sys/fs/cgroup/memory type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,memory)

cgroup on /sys/fs/cgroup/perf_event type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,perf_event)

cgroup on /sys/fs/cgroup/cpu,cpuacct type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,cpuacct,cpu)

cgroup on /sys/fs/cgroup/freezer type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,freezer)

cgroup on /sys/fs/cgroup/blkio type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,blkio)

cgroup on /sys/fs/cgroup/cpuset type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,cpuset)

cgroup on /sys/fs/cgroup/pids type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,pids)

cgroup on /sys/fs/cgroup/devices type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,devices)

cgroup on /sys/fs/cgroup/net_cls,net_prio type cgroup (rw,nosuid,nodev,noexec,relatime,seclabel,net_prio,net_cls)

configfs on /sys/kernel/config type configfs (rw,relatime)

/dev/mapper/rootvg-rootlv on / type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

/dev/mapper/rootvg-usrlv on /usr type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

selinuxfs on /sys/fs/selinux type selinuxfs (rw,relatime)

systemd-1 on /proc/sys/fs/binfmt_misc type autofs (rw,relatime,fd=35,pgrp=1,timeout=0,minproto=5,maxproto=5,direct,pipe_ino=20824)

debugfs on /sys/kernel/debug type debugfs (rw,relatime)

hugetlbfs on /dev/hugepages type hugetlbfs (rw,relatime,seclabel)

mqueue on /dev/mqueue type mqueue (rw,relatime,seclabel)

binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,relatime)

/dev/mapper/rootvg-varlv on /var type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

/dev/mapper/rootvg-optlv on /opt type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

/dev/mapper/rootvg-tmplv on /tmp type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

/dev/mapper/rootvg-homelv on /home type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

/dev/sda2 on /boot type xfs (rw,relatime,seclabel,attr2,inode64,noquota)

/dev/sda1 on /boot/efi type vfat (rw,relatime,fmask=0077,dmask=0077,codepage=437,iocharset=ascii,shortname=winnt,errors=remount-ro)

/dev/sdc1 on /apps type ext4 (rw,relatime,seclabel,data=ordered)

/dev/sdg1 on /oradata type ext4 (rw,relatime,seclabel,data=ordered)

/dev/sdd1 on /backupdrive type ext4 (rw,relatime,seclabel,data=ordered)

sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw,relatime)

/dev/sdb1 on /mnt type ext4 (rw,relatime,seclabel,data=ordered)

tmpfs on /run/user/1000 type tmpfs (rw,nosuid,nodev,relatime,seclabel,size=13186304k,mode=700,uid=1000,gid=1000)

tmpfs on /run/user/54321 type tmpfs (rw,nosuid,nodev,relatime,seclabel,size=13186304k,mode=700,uid=54321,gid=1000)

/dev/sde1 on /oradata19c type ext4 (rw,relatime,seclabel,data=ordered)

[root@itauchtdslive-b-db ~]# cat /etc/fstab

# /etc/fstab

# Created by anaconda on Wed Dec 2 07:37:51 2020

# Accessible filesystems, by reference, are maintained under '/dev/disk'

# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info

/dev/mapper/rootvg-rootlv / xfs defaults 0 0

UUID=2f20ed98-e644-4277-83fc-413bf6401c51 /boot xfs defaults 0 0

UUID=44EE-0DB1 /boot/efi vfat defaults,uid=0,gid=0,umask=0077,shortname=winnt 0 0

/dev/mapper/rootvg-homelv /home xfs defaults 0 0

/dev/mapper/rootvg-optlv /opt xfs defaults 0 0

/dev/mapper/rootvg-tmplv /tmp xfs defaults 0 0

/dev/mapper/rootvg-usrlv /usr xfs defaults 0 0

/dev/mapper/rootvg-varlv /var xfs defaults 0 0

UUID=5e22ac3e-8fee-41c3-b1b6-d9cf714a0636 /apps ext4 defaults,nofail 1 2

UUID=2a419e4f-301e-42d4-893e-6bcf4ec7da63 /backupdrive ext4 defaults,nofail 1 2

UUID=4a3d2a30-3e67-4626-bd0a-c5ee99a93c4b /oradata ext4 defaults,nofail 1 2

UUID=9436b758-cca3-4baf-805f-b9c8b0d7b039 /oradata2 ext4 defaults,nofail 1 2

/backupdrive/resource/swapfile swap swap defaults 0 0

//itauchbackupa.file.core.windows.net/tap-tds-backups /mnt/tap-tds-backups cifs nofail,credentials=/etc/smbcredentials/itauchbackupa.cred,dir_mode=0777,file_mode=0777,serverino,nosharesock,actimeo=30

/dev/disk/cloud/azure_resource-part1 /mnt auto defaults,nofail,x-systemd.requires=cloud-init.service,comment=cloudconfig 0 2

[root@itauchtdslive-b-db ~]# vi /etc/fstab – [Modify the new disk directory name from old disk directory as required]

[oracle@itauchtdslive-b-db ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

devtmpfs 63G 0 63G 0% /dev

tmpfs 63G 0 63G 0% /dev/shm

tmpfs 63G 9.2M 63G 1% /run

tmpfs 63G 0 63G 0% /sys/fs/cgroup

/dev/mapper/rootvg-rootlv 2.0G 80M 2.0G 4% /

/dev/mapper/rootvg-usrlv 10G 1.8G 8.3G 18% /usr

/dev/mapper/rootvg-optlv 2.0G 733M 1.3G 36% /opt

/dev/mapper/rootvg-varlv 25G 6.9G 19G 28% /var

/dev/mapper/rootvg-homelv 1014M 430M 585M 43% /home

/dev/mapper/rootvg-tmplv 2.0G 33M 2.0G 2% /tmp

/dev/sda2 494M 122M 373M 25% /boot

/dev/sda1 500M 9.9M 490M 2% /boot/efi

/dev/sdc1 630G 440G 159G 74% /apps

/dev/sde1 2.0T 94G 1.8T 5% /oradata19c

/dev/sdd1 2.0T 17G 1.9T 1% /backupdrive

/dev/sdg1 2.0T 1.8T 113G 95% /oradata

/dev/sdb1 591G 73M 561G 1% /mnt

tmpfs 13G 0 13G 0% /run/user/54321

[oracle@itauchtdslive-b-db ~]$

On the PROD Server:

We need to take a precautionary backup of the database before we can initiate the activity.

We need to confirm the archive mode and enable force logging if it is not enabled.

Configure log shipping on the primary database

We must follow the steps on the Primary site.

SQL> select log_mode from v$database;

SQL> select force_logging from v$database;

SQL> ALTER DATABASE FORCE LOGGING;

SQL> alter system set log_archive_config='dg_config=(TDS19C,TDS19C_stdby,TDS19C_stdby1)';

SQL> alter system set log_archive_dest_2='service=TDS19C_stdby noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=TDS19C_stdby';

SQL> alter system set log_archive_dest_state_2='ENABLE';

SQL> alter system set log_archive_dest_3='service=TDS19C_stdby1 noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=TDS19C_stdby1';

SQL> alter system set log_archive_dest_state_3='ENABLE';

SQL> alter system set fal_server=

SQL> alter system set standby_file_management=auto;

Adding Redologfile for standby database, Create a dedicated directory and provide the required privileges to accommodate the standby redolog files.

cd /oradata19c/TDS19C/

df -h .

mkdir -p standbylogs

chmod 777 standbylogs

cd standbylogs

/oradata19c/TDS19C/standbylogs

SQL> alter database add standby logfile ('/oradata19c/TDS19C/standbylogs/redo1.log') size 200m;

SQL> alter database add standby logfile ('/oradata19c/TDS19C/standbylogs/redo2.log') size 200m;

SQL> alter database add standby logfile ('/oradata19c/TDS19C/standbylogs/redo3.log') size 200m;

SQL> alter database add standby logfile ('/oradata19c/TDS19C/standbylogs/redo4.log') size 200m;

[oracle@itauchtdslive-a-db admin]$ cp tnsnames.ora tnsnames.ora.`date '+%Y%m%d'`

[oracle@itauchtdslive-a-db admin]$ cp listener.ora listener.ora.`date '+%Y%m%d'`

TDS19C =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = itauchtdslive-a-db.internal.cloudapp.net)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TDS19C)

(UR=A)

)

TDSPDB19C =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = itauchtdslive-a-db.internal.cloudapp.net)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TDSPDB19C)

)

TDS19C_stdby =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.111.64.35)(PORT = 1542))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TDS19C_STDBY)

(UR=A)

)

scp orapwTDS19C oracle@10.111.64.35:/apps/oracle/product/19.3.0/dbhome_1/dbs

cd /backupdrive

mkdir -p TDSLIVESTDBY

chmod 777 TDSLIVESTDBY

/backupdrive/TDSLIVESTDBY

vi tdsstdby_dbbuild.rcv

chmod 777 tdsstdby_dbbuild.rcv

Modify the pfile based on the required in the standby and primary.

[oracle@itauchtdslive-b-db dbs]$ cat initstandby.ora

*._optimizer_autostats_job=TRUE

*.audit_file_dest='/apps/oracle/admin/TDS19C_STDBY/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/oradata19c/TDS19C/controlfile/control01.ctl','/backupdrive/fast_recovery_area/TDS19C_STDBY/controlfile/control02.ctl'

*.db_block_size=8192

*.db_name='TDS19C'

*.db_unique_name='TDS19C_STDBY'

*.db_create_online_log_dest_1='/oradata19c/TDS19C/'

*.db_recovery_file_dest='/backupdrive/fast_recovery_area/TDS19C_STDBY/FRA'

*.db_recovery_file_dest_size=751619276800

*.diagnostic_dest='/apps/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TDS19C_STDBYXDB)'

*.enable_pluggable_database=true

*.local_listener='(ADDRESS=(PROTOCOl=TCP)(HOST=10.111.64.35)(PORT=1542))'

*.log_archive_config='dg_config=(TDS19C,TDS19C_stdby)'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=5000

*.pga_aggregate_target=21474836480

*.processes=1500

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=5000

*.sga_target=30000m

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

startup nomount pfile=/apps/oracle/product/19.3.0/dbhome_1/dbs/initstandby.ora;

Check the connections using RMAN:

rman target sys/'Oracle3$$'@TDS19C nocatalog auxiliary sys/'Oracle3$$'@TDS19C_stdby

[oracle@itauchtdslive-b-db TDSLIVESTDBY]$ ls -ltr

total 4

-rwxrwxrwx. 1 oracle oinstall 990 Jul 25 16:08 tdsstdby_dbbuild.rcv

[oracle@itauchtdslive-b-db TDSLIVESTDBY]$ pwd

/backupdrive/TDSLIVESTDBY

[oracle@itauchtdslive-b-db TDSLIVESTDBY]$

[oracle@itauchtdslive-b-db TDSLIVESTDBY]$ cat tdsstdby_dbbuild.rcv

run

{

ALLOCATE CHANNEL C1 type disk;

ALLOCATE CHANNEL C2 type disk;

ALLOCATE CHANNEL C3 type disk;

ALLOCATE CHANNEL C4 type disk;

ALLOCATE CHANNEL C5 type disk;

ALLOCATE AUXILIARY CHANNEL A1 type disk;

ALLOCATE AUXILIARY CHANNEL A2 type disk;

ALLOCATE AUXILIARY CHANNEL A3 type disk;

ALLOCATE AUXILIARY CHANNEL A4 type disk;

ALLOCATE AUXILIARY CHANNEL A5 type disk;

duplicate target database for standby from active database dorecover nofilenamecheck;

RELEASE CHANNEL C1;

RELEASE CHANNEL C2;

RELEASE CHANNEL C3;

RELEASE CHANNEL C4;

RELEASE CHANNEL C5;

RELEASE CHANNEL A1;

RELEASE CHANNEL A2;

RELEASE CHANNEL A3;

RELEASE CHANNEL A4;

RELEASE CHANNEL A5;

}

[oracle@itauchtdslive-b-db TDSLIVESTDBY]$

Enable Broker

At this point we have a primary database and a standby database, so now we need to start using the Data Guard Broker to manage them. Connect to both databases (primary and standby) and issue the following command.

SQL> sho parameter dg_broker_start

alter system set dg_broker_start=true;

sho parameter dg_brok

On the primary server, issue the following command to register the primary server with the broker.

dgmgrl sys/'Oracle3$$'@TDS19C