OracleDBWorld

How to Create Physical Standby Database in Oracle 19c

Posted on by N Namrata

Hey Reader, today we will be covering how to create physical standby database in oracle 19c. This won’t be a regular request coming into your email unless you work in Project team where you create Primary database for new application and based on business request you will create it’s Physical standby database. I will try to cover all the pre-requisites and actionable from oracle dba end.

Lets Start how to Create Physical Standby Database in Oracle 19c

My Environment

Primary Database – Single instance, Filesystem
RDBMS Version – 19.8.0.0
OS Version – RHEL 7.8
IP – 192.168.1.10
Port 1521
DBName – oracledb

Primary Database – Single instance, Filesystem
RDBMS Version – 19.8.0.0
OS Version – RHEL 7.8
IP – 192.168.1.11
Port 1521
DBName – oracledr

Step By Step How to Create Physical Standby Database

Pre-Checks With Respect to Environment

  1. You have to make sure System Administrator has provided same OS version and Same flavor of UNIX machine, Since in my case it’s a Test machine, hence I made a copy of the my same virtual box and rename the hostname to srv2.
  2. Decide the port number on which your Primary database and Physical standby database will communicate. For instance, I took 1521 (default port) for log shipment. Please note each company have their own way to chose the port number. So it’s better to check with Network/Firewall team and your teammates.
  3. Get the port opted for log shipment opened both directional on Firewall. So that you can telnet the specific port. From source to destination and vice versa.
    telnet <IP> <port>
    For Example – On my source server (srv1/192.168.1.10) I did –> telnet 192.168.1.11 1521 and On destination Server (srv2/192.168.1.11) I did –> telnet 192.168.1.10 1521
  4. Additional Mount point for Archive log and Backup on both servers (Primary and DR)

Pre-Checks With Respect to Database

  1. Install Oracle19c binaries, apply the patch same as Production server on DR server.
  2. Put Production database in Archive log Mode. ( In most cases it’s in Archive log mode, but if you are unlucky, then you have to ask business for 15 mins Downtime to Put database in archive log mode. Make sure you have archive partition available with you.
  3. Enable Force logging mode for production database. ( Oracle Recommended.)

How to add Oracle database 19c in Archive Log Mode.

Once all prechecks related to environment are met. We will work on Prechecks with respect to database. Check the primary database status and few parameters like LOG_ARCHIVE_DEST_1, db_recovery_file_dest and log_archive_format ( Format with which archive will be stored. You can modify it. I usually do it since this was test system I didn’t change the log_archive_format)

SQL> select name,open_mode,database_role,flashback_on,force_logging,log_mode from v$database;

NAME	 |OPEN_MODE |DATABASE_ROLE|FLASHBACK_ON |FORCE_LOGGING |LOG_MODE
---------|----------|-------------|-------------|--------------|------------
ORACLEDB |READ WRITE|PRIMARY	  |NO		|NO	       |NOARCHIVELOG

SQL> select dest_id,dest_name,status,error from v$archive_dest where status!='INACTIVE';

   DEST_ID|DEST_NAME				   |STATUS   |ERROR
----------|----------------------------------------|---------|--------------
	 1|LOG_ARCHIVE_DEST_1			   |VALID    |

SQL> show parameter LOG_ARCHIVE_DEST_1

NAME				    |TYPE	|VALUE
------------------------------------|-----------|---------------------------
log_archive_dest_1		    |string	|
log_archive_dest_10		    |string	|
log_archive_dest_11		    |string	|
log_archive_dest_12		    |string	|
log_archive_dest_13		    |string	|
log_archive_dest_14		    |string	|
log_archive_dest_15		    |string	|
log_archive_dest_16		    |string	|
log_archive_dest_17		    |string	|
log_archive_dest_18		    |string	|
log_archive_dest_19		    |string	|
SQL> show parameter db_recover

NAME				    |TYPE	|VALUE
------------------------------------|-----------|------------------------------
db_recovery_file_dest		    |string	|/u01/app/oracle/fast_recovery_
				    |	        |area
db_recovery_file_dest_size	    |big integer|8256M

Enable on destination on production database which will be pointing to archive partition /diskgroup you took. In our case, I have used db_recovery_file_dest only.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL> show parameter dest_1

NAME				    |TYPE	|VALUE
------------------------------------|-----------|------------------------------
db_create_online_log_dest_1	    |string	|
log_archive_dest_1		    |string	|LOCATION=USE_DB_RECOVERY_FILE_
				    |	        |DEST       
log_archive_dest_10		    |string	|
log_archive_dest_11		    |string	|
log_archive_dest_12		    |string	|
log_archive_dest_13		    |string	|
log_archive_dest_14		    |string	|
log_archive_dest_15		    |string	|
log_archive_dest_16		    |string	|
log_archive_dest_17		    |string	|
log_archive_dest_18		    |string	|
log_archive_dest_19		    |string	|
SQL>

If you want to give a partition / diskgroup then command will be

SQL> alter system set log_archive_dest_1='LOCATION=/arch';

or

SQL> alter system set log_archive_dest_1='LOCATION=+ARCH';

Now during downtime, take bounce of database and put db in Archive log mode and enable Force logging mode.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@srv1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 31 21:30:10 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1795159104 bytes
Fixed Size		    8897600 bytes
Variable Size		  419430400 bytes
Database Buffers	 1358954496 bytes
Redo Buffers		    7876608 bytes
Database mounted.
SQL> 
SQL> alter database archivelog;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> select name,open_mode,database_role,flashback_on,force_logging,log_mode from v$database;

NAME	 |OPEN_MODE|DATABASE_ROLE |FLASHBACK_ON	|FORCE_LOGGING   |LOG_MODE
---------|---------|--------------|-------------|----------------|------------
ORACLEDB |MOUNTED  |PRIMARY	      |NO	|YES		 |ARCHIVELOG
 
SQL> alter database open;

Database altered.

SQL>

Network Related Changes – Listener.ora and Tnsnames.ora

We might have created the listener and have the tns-entry for the production database. But for new DR server, you might need to create the listener service and make tns-entry.

Location of listener.ora and Tnsnames.ora will be $ORACLE_HOME/network/admin or environmental variable $TNS_ADMIN if that’s defined on your server.

Listener On Production

[oracle@srv1 dbs]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-AUG-2021 16:03:15

Copyright (c) 1991, 2020, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-JUL-2021 16:16:48
Uptime                    0 days 22 hr. 46 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/srv1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracledb" has 1 instance(s).
  Instance "oracledb", status READY, has 1 handler(s) for this service...
Service "oracledbXDB" has 1 instance(s).
  Instance "oracledb", status READY, has 1 handler(s) for this service...
The command completed successfully

Tns entry for production will look like -> 

ORACLEDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracledb)
    )
  )

Listener on DR server

[oracle@srv2 dbs]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-AUG-2021 16:03:15

Copyright (c) 1991, 2020, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                31-JUL-2021 16:26:48
Uptime                    0 days 23 hr. 46 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/srv2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oracledr" has 1 instance(s).
  Instance "oracledr", status READY, has 1 handler(s) for this service...
The command completed successfully

Tns entry for dr database will look like -> 

ORACLEDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oracledr)
    )
  )

Add both TNS ENTRIES on both Primary and DR server’s Tnsnames.ora

Once you copied the both tns-entry on both Primary and DR server, you should do tnsping to check if all looks good.

Production Server

[oracle@srv1 dbs]$ tnsping oracledb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-AUG-2021 16:00:09

Copyright (c) 1997, 2020, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracledb)))
OK (0 msec)
[oracle@srv1 dbs]$ tnsping oracledr

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-AUG-2021 16:00:17

Copyright (c) 1997, 2020, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracledr)))
OK (0 msec)
[oracle@srv1 dbs]$

DR Server 

[oracle@srv2 dbs]$ tnsping oracledb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-AUG-2021 16:10:09

Copyright (c) 1997, 2020, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracledb)))
OK (0 msec)
[oracle@srv2 dbs]$ tnsping oracledr

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-AUG-2021 16:20:17

Copyright (c) 1997, 2020, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oracledr)))
OK (0 msec)
[oracle@srv2 dbs]$

Oracle 19c Database Backup And Restore

Now it’s time to take the backup of the database and restore it on New server. There are various ways to do it. I will be using traditional way which will involve following –

  1. Take RMAN backup of Primary database on a Mount Point.
  2. Create the backup Parameter file
  3. Transfer backup from Primary Server to DR site. ( Server on which database is in read write mode we call it Primary Server and server on which database is in mounted/read only mode then we call it DR server/site.)
  4. Edit the Backup Parameter file.
  5. Restore RMAN Backup on DR server.

Lets Begin –

Database Full Backup

Discuss with application team, and chose lean period for your Primary/Production server. And based on that you can schedule and initiate the RMAN backup.

You can write a run block and fire the backup -> 

run
{
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup as compressed backupset database format '/u01/app/oracle/oradata/ORACLEDR/BACKUP/%U';
backup current controlfile for standby format '/u01/app/oracle/oradata/ORACLEDR/BACKUP/controlforstandby.ctl';
backup archivelog all format '/u01/app/oracle/oradata/ORACLEDR/BACKUP/arch_%U';
release channel ch01;
release channel ch02;
}

The Run Block comes handy when you have a big database and you want to run it in background. Add the above run block make sure you change the location to your available location in rman.rcv. Then your command will be like –

echo $ORACLE_SID
nohup rman target / @rman.rcv > rman_full_backup_<date>.log &

Or Single statement at a time.

Since my database was a empty database and newly created one, hence I fired it on session itself and not in Background.

Datafile backup – 

[oracle@srv1 BACKUP]$ echo $ORACLE_SID
oracledb
[oracle@srv1 BACKUP]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jul 31 21:43:23 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORACLEDB (DBID=3697200825)

RMAN> backup as compressed backupset database format '/u01/app/oracle/oradata/ORACLEDR/BACKUP/%U';

Starting backup at 31-JUL-2021 21:44:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORACLEDB/datafile/o1_mf_system_jjbk0dnw_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORACLEDB/datafile/o1_mf_undotbs1_jjbk2mbk_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORACLEDB/datafile/o1_mf_sysaux_jjbk1t1d_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORACLEDB/datafile/o1_mf_users_jjbk2ngm_.dbf
channel ORA_DISK_1: starting piece 1 at 31-JUL-2021 21:45:01
channel ORA_DISK_1: finished piece 1 at 31-JUL-2021 21:46:06
piece handle=/u01/app/oracle/oradata/ORACLEDR/BACKUP/0105c8ut_1_1 tag=TAG20210731T214501 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 31-JUL-2021 21:46:06

Starting Control File and SPFILE Autobackup at 31-JUL-2021 21:46:06
piece handle=/u01/app/oracle/fast_recovery_area/ORACLEDB/autobackup/2021_07_31/o1_mf_s_1079387167_jjc32zd7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JUL-2021 21:46:08

RMAN>

Control file backup

RMAN> backup current controlfile for standby format '/u01/app/oracle/oradata/ORACLEDR/BACKUP/controlforstandby.ctl';

Starting backup at 31-JUL-2021 21:57:21
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 31-JUL-2021 21:57:22
channel ORA_DISK_1: finished piece 1 at 31-JUL-2021 21:57:23
piece handle=/u01/app/oracle/oradata/ORACLEDR/BACKUP/controlforstandby.ctl tag=TAG20210731T215721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-2021 21:57:23

Starting Control File and SPFILE Autobackup at 31-JUL-2021 21:57:23
piece handle=/u01/app/oracle/fast_recovery_area/ORACLEDB/autobackup/2021_07_31/o1_mf_s_1079387843_jjc3r45v_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JUL-2021 21:57:24

RMAN>

Archive log Backup

RMAN> backup archivelog all format '/u01/app/oracle/oradata/ORACLEDR/BACKUP/arch_%U';

Starting backup at 31-JUL-2021 22:04:17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=1 STAMP=1079388258
channel ORA_DISK_1: starting piece 1 at 31-JUL-2021 22:04:18
channel ORA_DISK_1: finished piece 1 at 31-JUL-2021 22:04:19
piece handle=/u01/app/oracle/oradata/ORACLEDR/BACKUP/arch_0505ca32_1_1 tag=TAG20210731T220418 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-2021 22:04:19

Starting Control File and SPFILE Autobackup at 31-JUL-2021 22:04:19
piece handle=/u01/app/oracle/fast_recovery_area/ORACLEDB/autobackup/2021_07_31/o1_mf_s_1079388259_jjc4545c_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JUL-2021 22:04:20

RMAN>

Create Parameter file from Production Server

Parameter file will be created, edited and used on DR server to create the instance on Dr site.

[oracle@srv1 BACKUP]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 31 22:04:55 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show parameter spfile

NAME				                 TYPE	     VALUE
------------------------------------ ----------- ------------------------------
spfile				                 string	     /u01/app/oracle/product/19.0.0
						                         /db_1/dbs/spfileoracledb.ora
SQL> 

SQL> create pfile='/tmp/pfile_backup.ora' from spfile;

File created.

SQL>

Transfer the Backup and Backup Parameter File on DR server ( Destination server)

It will be tough sometimes, you can take help of system administrator to scp the backup files to DR server. Syntax will be as follows –

scp <location/filename> <destination_user_name>@<IP>:<Location on Destination server>

In my case backup pieces were at /u01/app/oracle/oradata/ORACLEDR/BACKUP/ location and destination server was 192.168.1.11 and path on destination server I created was same as well ie /u01/app/oracle/oradata/ORACLEDR/BACKUP/ 

scp /u01/app/oracle/oradata/ORACLEDR/BACKUP/* oracle@192.168.1.11:/u01/app/oracle/oradata/ORACLEDR/BACKUP/
scp /tmp/pfile_backup.ora oracle@192.168.1.11:/u01/app/oracle/oradata/ORACLEDR/BACKUP/

Edit the Parameter file on DR server

Here I have changed the directory structure, db_unique_name and control file location. And added log_file_name_convert and db_file_name_convert 

[oracle@srv2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@srv2 dbs]$ vi initoracledr.ora
*.audit_file_dest='/u01/app/oracle/admin/oracledr/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/ORACLEDR/controlfile/o1_mf_jjbk3sdy_.ctl','/u01/app/oracle/fast_recovery_area/ORACLEDR/controlfile/o1_mf_jjbk3sgr_.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='oracledb'
*.db_unique_name='oracledr'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oracledrXDB)'
*.local_listener='LISTENER_ORACLEDR'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=569m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1707m
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/ORACLEDB/','/u01/app/oracle/oradata/ORACLEDR/'
*.log_file_name_convert='/u01/app/oracle/oradata/ORACLEDB/','/u01/app/oracle/oradata/ORACLEDR/','/u01/app/oracle/fast_recovery_area/ORACLEDB/','/u01/app/oracle/fast_recovery_area/ORACLEDR/'
*.sga_target=1g
*.sga_max_size=1g

Create Directory Structure on DR Server

Based on Parameter file we created, we will create all the directory structure mentioned in the PFILE. So that instance startup doesn’t get end of file communication

[oracle@srv2 BACKUP]$ mkdir -p /u01/app/oracle/admin/oracledr
[oracle@srv2 BACKUP]$ mkdir -p /u01/app/oracle/fast_recovery_area/ORACLEDR/controlfile
[oracle@srv2 BACKUP]$ mkdir -p /u01/app/oracle/oradata/ORACLEDR/

Database Restoration on Dr Server

Start the DR database Instance in nomount and crosscheck certain parameters – 

[oracle@srv2 dbs]$ cd -
/u01/app/oracle/oradata/ORACLEDR/BACKUP
[oracle@srv2 BACKUP]$ 
[oracle@srv2 BACKUP]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 31 22:23:54 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1795159104 bytes
Fixed Size		    8897600 bytes
Variable Size		  419430400 bytes
Database Buffers	 1358954496 bytes
Redo Buffers		    7876608 bytes
SQL> show parameter control

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time	     integer	 7
control_files			     string      /u01/app/oracle/oradata/ORACLE
						 DR/controlfile/o1_mf_jjbk3sdy_
						 .ctl, /u01/app/oracle/fast_rec
						 overy_area/ORACLEDR/controlfil
                                                 e/o1_mf_jjbk3sgr_.ctl
control_management_pack_access	     string	 DIAGNOSTIC+TUNING
SQL> show parameter db_uniq

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_unique_name			     string	 oracledr
SQL> show parameter convert

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert		     string	 /u01/app/oracle/oradata/ORACLE
						 DB/, /u01/app/oracle/oradata/O
						 RACLEDR/
log_file_name_convert		     string	 /u01/app/oracle/oradata/ORACLE
						 DB/, /u01/app/oracle/oradata/O
						 RACLEDR/, /u01/app/oracle/fast
						 _recovery_area/ORACLEDB/,/u01/                                                                   app/oracle/fast_recovery_area/
                                                 ORACLEDR/
pdb_file_name_convert		     string      
SQL>

Restore the standby controlfile from the backup and mount the standby database.

[oracle@srv2 BACKUP]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Jul 31 22:29:47 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORACLEDB (not mounted)

RMAN> restore standby controlfile from '/u01/app/oracle/oradata/ORACLEDR/BACKUP/controlforstandby.ctl';

Starting restore at 31-JUL-2021 22:30:14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORACLEDR/controlfile/o1_mf_jjbk3sdy_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORACLEDR/controlfile/o1_mf_jjbk3sgr_.ctl
Finished restore at 31-JUL-2021 22:30:16

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN>

Restore Datafiles using run block – If you see I have used “set newname for database to location” This is done when you want datafiles to be restored on specific location or you want datafiles to be restored in specific folder.. This can be done when on production you have different mount point which holds the datafiles and on DR they have given different mount point 

run
{
set newname for database to '/u01/app/oracle/oradata/ORACLEDR/%b';
restore database;
}
RMAN> 
RMAN> run
2> {
3> set newname for database to '/u01/app/oracle/oradata/ORACLEDR/%b';
4> restore database;
5> }

executing command: SET NEWNAME

Starting restore at 31-JUL-2021 22:34:19
Starting implicit crosscheck backup at 31-JUL-2021 22:34:19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 31-JUL-2021 22:34:20

Starting implicit crosscheck copy at 31-JUL-2021 22:34:20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-JUL-2021 22:34:21

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORACLEDR/o1_mf_system_jjbk0dnw_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORACLEDR/o1_mf_sysaux_jjbk1t1d_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORACLEDR/o1_mf_undotbs1_jjbk2mbk_.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORACLEDR/o1_mf_users_jjbk2ngm_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/ORACLEDR/BACKUP/0105c8ut_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/ORACLEDR/BACKUP/0105c8ut_1_1 tag=TAG20210731T214501
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 31-JUL-2021 22:36:27

RMAN>

Once restoration is completed, make sure controlfile reflects the new location as per set newname. If not then you have to perform additional step as follows-

You don’t need to fire switch database to copy if you are restoring the datafiles in same location and same absolute path as that of production. In this case control file already have the information.

RMAN> catalog start with '/u01/app/oracle/oradata/ORACLEDR/';

searching for all files that match the pattern /u01/app/oracle/oradata/ORACLEDR/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/ORACLEDR/BACKUP/controlforstandby.ctl
File Name: /u01/app/oracle/oradata/ORACLEDR/BACKUP/arch_0505ca32_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/ORACLEDR/BACKUP/controlforstandby.ctl
File Name: /u01/app/oracle/oradata/ORACLEDR/BACKUP/arch_0505ca32_1_1

RMAN> 

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/u01/app/oracle/oradata/ORACLEDR/datafile/o1_mf_system_jjc5xg8c_.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ORACLEDR/datafile/o1_mf_sysaux_jjc5xgbr_.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/ORACLEDR/datafile/o1_mf_undotbs1_jjc5xg9w_.dbf"
datafile 7 switched to datafile copy "/u01/app/oracle/oradata/ORACLEDR/datafile/o1_mf_users_jjc5xgc3_.dbf"

RMAN>

Re-create logfiles and standby redo logs ( if any )

I don’t have standby redo logs in my system but in case you have standby redo logs please recreate standby redo logs on standby database.

For re-creating the logfiles, I have cleared the logfile, this way it will create the physical files. Please note it’s not mandatory but recommended to check – Since I have used log_file_name_convert, my redo logs member location are pointing to the location I actually wanted it to be. Hence I just cleared. ( If you start the MRP for first time, it will clear it anyway)


SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME	 |OPEN_MODE  |DATABASE_ROLE   |LOG_MODE    |FORCE_LOGGING
---------|-----------|----------------|------------|-------------------
ORACLEDB |MOUNTED    |PHYSICAL STANDBY|ARCHIVELOG  |YES

SQL> 

SQL> def
DEFINE _DATE	       = "01-AUG-21" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oracledr" (CHAR)
DEFINE _USER	       = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1908000000" (CHAR)
DEFINE _EDITOR	       = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1908000000" (CHAR)
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORACLEDR/onlinelog/o1_mf_3_jjbk3w5b_.log
/u01/app/oracle/fast_recovery_area/ORACLEDR/onlinelog/o1_mf_3_jjbk48dp_.log
/u01/app/oracle/oradata/ORACLEDR/onlinelog/o1_mf_2_jjbk3w1r_.log
/u01/app/oracle/fast_recovery_area/ORACLEDR/onlinelog/o1_mf_2_jjbk485w_.log
/u01/app/oracle/oradata/ORACLEDR/onlinelog/o1_mf_1_jjbk3w0j_.log
/u01/app/oracle/fast_recovery_area/ORACLEDR/onlinelog/o1_mf_1_jjbk488w_.log

6 rows selected.

SQL> select group# from v$log;

    GROUP#
----------
	 1
	 3
	 2

SQL> alter database clear logfile group 1;

Database altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> alter database clear logfile group 3;

Database altered.

Copy Password File from Production to DR server

scp $ORACLE_HOME/dbs/orapworacledb oracle@192.168.1.11:/u01/app/oracle/product/19.0.0/db_1/

Enable Logshipment from Production to DR

Primary/Production database – Set the Destination on Production

Set the available destination parameter, ( ie. show parameter log_archive_dest_<n> shouldn’t return any value for that specific <n>

alter system set log_archive_dest_2='service=oracledr db_unique_name=oracledr' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;

DR/Standby database – Set the Destination on DR

If you have noticed that I have set the destination and set the state to defer so whenever there is switchover from production to DR and you need to enable reverse log shipment then you have use it during that time. Currently it will be deferred state.


alter system set log_archive_dest_state_2=defer scope=both;
alter system set log_archive_dest_2='service=oracledb db_unique_name=oracledb' scope=both;

It’s recommended to set Fal_server and fal_client on both server. Along with that if you have log_archive_config parameter set on production then you need to set it on DR as well

Start the Media Recovery and Check the Sync of the Database-

Primary Side /Production Side –

[oracle@srv1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 1 15:50:39 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
ORACLEDB  READ WRITE	       PRIMARY

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       15

SQL>

On DR Side – 

[oracle@srv2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 1 15:57:18 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select name,open_mode,database_role from v$database;

NAME	  OPEN_MODE	       DATABASE_ROLE
--------- -------------------- ----------------
ORACLEDB  MOUNTED	       PHYSICAL STANDBY

SQL> 
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       15

SQL> 
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       15

SQL> select thread#,max(sequence#) from v$log_history group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       15

SQL>

Lets do 3-4 log switches on production database –

alter system switch logfile (3 times)
SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME	  OPEN_MODE	   DATABASE_ROLE	LOG_MODE     FORCE_LOGGING
--------- ------------ ---------------- ------------ ----------------
ORACLEDB  READ WRITE   PRIMARY		    ARCHIVELOG   YES

SQL> 

SQL> archive log list
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence	       20
SQL>

Check alert log of production database.

Alert log of production –

Check the sync of DR database – 

SQL> select name,open_mode,database_role,log_mode,force_logging from v$database;

NAME	  OPEN_MODE	 DATABASE_ROLE	  LOG_MODE     FORCE_LOGGING
--------- ---------- ---------------- ------------ ---------------
ORACLEDB  MOUNTED	 PHYSICAL STANDBY ARCHIVELOG   YES

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       19

SQL> select thread#,max(sequence#) from v$log_history group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       19

SQL>

It’s always better to check alert log to make it sure as well and everything is clean.

Alert log of DR ->

Done. Hope you liked the detailed Blog on How to create physical standby database on oracle 19c.

You may also like –

How to Take Incremental Backup and Roll Forward Standby Database in Oracle

References

Creating a Physical Standby Database (Doc ID 1475344.1)
Step By Step Guide To Create Physical Standby Database Using RMAN Backup and Restore (Doc ID 469493.1)
Creating a Physical Standby database using RMAN restore database from service (Doc ID 2283978.1)
Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE (Doc ID 1075908.1)
Step-By-Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN (Doc ID 838828.1)

Leave a Reply

Your email address will not be published. Required fields are marked *