Duplicating Oracle Database 19c using

Data Domain Backup with Veritas

NetBackup
Majid Shabani
[email protected]
January 21, 2024
Duplicating the database using NetBackup involves these steps:
• Overview
• Data Guard Requirement
• Data Guard Pros and Cons
• Preparing Primary Database
• Preparing Standby Database
• Creating standby database
• Monitoring Restore Operation
• Post Action

Disclaimer
Please note that this document is intended as a guide, not a reference, for creating a standby database.
The author is not accountable for any losses or damage resulting from the application of this article.
There's no assurance that the advice provided is error-free. It is recommended to use this guide at your
own risk and conduct thorough testing in your specific environment.
If you identify any inaccuracies in the information presented, please reach out to me for corrections. Your
input is appreciated for continual improvement.
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Overview:
In this article, I will guide you through the process of creating a standby database for Oracle Database with
ASM release 19c non-CDB, employing Backup-based duplication via pre-existing RMAN backups stored on
Data Domain with Veritas NetBackup tools.

Before delving into the steps, it's worth noting that some DBAs, assuming that duplicating a database with
any method, be it Backup-based duplication or Active database duplication is the optimal solution for
almost all cases, often argue that it's a semi-automatic method involving fewer steps and resulting in ease
of use.

On the other hand, other DBAs those who believe duplicating database is not always the best method in
all cases prefer to create a standby database manually, involving multiple steps that can become complex,
using backups stored on Data Domain with Veritas NetBackup or using the backup saved on disk. However,
this method carries a low risk due to its distinct steps, making it a suitable choice for certain cases, large
databases in specific. Moreover, it eliminates resource usage on the source database during the
duplication process.

Personally, I favor of using manual steps to create large standby databases due to the lower risk involved.
Meanwhile, I find the duplication method suitable for smaller databases in size.

If you're interested in a guide on creating a standby database manually, please leave a comment or send
me a message. I'll be happy to prepare a document on this topic for you.

Specification of oracle software products

Oracle Grid Home /u01/app/19.0.0/grid
Primary Oracle Home
Oracle Database Home /u01/app/oracle/product/19.0.0/db_1
Oracle Grid Home /u01/app/19.0.0/grid
Standby Oracle Home
Oracle Database Home /u01/app/oracle/product/19.0.0/db_1
OS RHEL Linux 8.4

The backup scheduled to be taken with the following specification.

Specification of backup
Backup Strategy Whole database
Backup Type Full Backup + archive backup
Backup Mode Online
Used Tool RMAN on Data Domain using NetBackup
If RMAN catalog DB used? Yes, used
To obtain more detail about the NetBackup, please use this link.

Primary & standby databases details are as follow:

Primary Database Unique Name proddb
Primary Database Hostname db-srv01
Standby Database Unique Name stproddb
Standby Database Hostname stdb-srv01
The management interface SQL*Plus

Majid Shabani Page |2

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Data Guard Requirement

• Both platforms must use the same endian format, Big or Little.
• The Databases must be Enterprise Edition (EE).
• The Oracle Database release must be the same.
• The primary database must run in ARCHIVELOG mode.
• Database must operate in FORCE LOGGING mode.
• The Hardware can be different.
• Enable Flashback Database (optional)

Tip: Please be aware, you can obtain information about endian format of different platform using
v$transportable_platform performance view.

Please note that I omitted the software installation details but emphasized the key considerations.

Data Guard Pros and Cons

This table provides a concise overview of the key advantages and disadvantages of Oracle Data Guard
across various aspects. In my opinion, understanding these pros and cons can significantly aid in making
informed decisions regarding data protection and disaster recovery strategies.

Category Advantages Disadvantages

Site Disaster • Doesn't support heterogeneous platforms,
High Availability and requiring the endian format of the source
Data Corruption
Data Protection database to match that of the standby
User Error or Mistake database. For example, a configuration
with Oracle Solaris on SPARC cannot be
Efficient Resource Offload read-only queries paired with Linux in your Data Guard setup.
Utilization Offload backups
• Lacks support for advanced replication,
Rolling upgrade meaning it cannot replicate subsets of data
High availability as it cannot filter data based on rows and
Testing columns.

Majid Shabani Page |3

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Preparing Primary Database:

The forthcoming steps will guide you in preparing the primary database for the Data Guard environment.

1. Modify the hosts file to include the hostnames and IP addresses of both the primary and standby
databases.

# Login as root user

su -

# open /etc/hosts file.

vi /etc/hosts

# Add the new entries.

10.15.91.9 db-srv01
10.156.34.58 stdb-srv01

# save & exit

:x

# Press Enter

2. Activate archiving mode in the primary database. While many production databases are typically
already in archive mode, you can use the following commands if necessary. Note that enabling
archiving requires temporarily bringing down the database.
# as oracle user invoke sql*plus and connect to primary database.
sqlplus / as sysdba

# check archive log mode.

SELECT LOG_MODE FROM V$DATABASE;

# set archivelog destination. Assuming FRA is already configured.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;

# put database in mount mode, the DB must be shut down consistently.

# Tip: before shutting down the database, it’s advisable to check for any running scheduled
# jobs, such as data pump export job, if there are any, please wait until they finish
# before proceeding.
SHUTDOWN IMMEDIATE
STARTUP MOUNT

# enable archive mode & open the database.

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

# verify
ALTER SYSTEM SWITCH LOGFILE;
SELECT NAME FROM V$ARCHIVED_LOG;

3. Enable the forced logging option in the database. This ensures that the database skips any no logging
operation.
ALTER DATABASE FORCE LOGGING;

# verify
SELECT FORCE_LOGGING FROM V$DATABASE;

Majid Shabani Page |4

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

4. Set up Standby Redo Log files, which will be utilized during switchover or failover. Determine the
suitable number of standby redo log file groups using the formula below:

# formula
(maximum number of redo logfiles +1) * number of threads

If we assume there are 5 online redo log (ORL) groups, and the database is not
RAC, the required number of standby redo log files is calculated as (5 + 1) * 1,
which equals 6.

# Get the number of online redo log (ORL) groups and their members.
SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG;

COL MEMBER FOR a70

SELECT MEMBER FROM V$LOGFILE;

# Retrieve the thread count.

COL INSTANCE FOR A15
SELECT THREAD#, INSTANCE FROM V$THREAD;

# Add standby logfiles with a single member each.

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO1' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO1' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO1' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO2' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO2' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO2' SIZE 4096M ;

# verify
SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS, BYTES/1024/1024 SIZE_MB
FROM V$STANDBY_LOG;
SELECT MEMBER FROM V$LOGFILE WHERE TYPE = 'STANDBY';

# Tip: If the destination is not specified in the ADD command, the database will
create them in the FRA (Fast Recovery Area) and DATA disk group if
db_create_online_log_dest_1 is not specified.

5. Set the required initialization parameters for primary database.

# DG_CONFIG specifies a list of unique database names (case-sensitive)

# When combined with DB_UNIQUE_NAME in the destination parameter, it serves as a security
# check for permitted Data Guard connections.

show parameter DB_UNIQUE_NAME

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(proddb,stproddb)' scope=BOTH;

# The SERVICE attribute specifies a valid Oracle Net service name identifying the remote
Oracle database.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=stimapprod ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stimapprod' scope=BOTH;

# (Optional) Force the primary database to switch logfiles after a specified time.
ALTER SYSTEM SET ARCHIVE_LAG_TARGET=1800 scope=BOTH;

# should be EXCLUSIVE
show parameter REMOTE_LOGIN_PASSWORDFILE

Majid Shabani Page |5

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

# FAL = fetch archive log, must be Oracle Net service name

# Note: In Oracle 19c, setting the FAL_CLIENT parameter is no longer required.

show parameter FAL_SERVER ;

ALTER SYSTEM SET FAL_SERVER = 'stimapprod' scope=BOTH;

# Setting the value to 'AUTO' automates the replication of data file additions and removals
from the primary database to the standby database.

show parameter STANDBY_FILE_MANAGEMENT

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=BOTH;

# As the standby system shares the same directory structure, there's no necessity to
configure DB_FILE_NAME_CONVERT.
SHOW PARAMETER DB_FILE_NAME_CONVERT

6. Get list of directory-dependent parameters and create the directories in the standby server:
col name for a30
col value for a50
SELECT NAME, VALUE FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%/proddb/%');

Tip: if the standby system has the same directory structure, you need to just create
'audit_file_dest'

7. Enable Flashback Database (optional but recommended)

# Invoke SQL*PLUS and then run following command
SHOW PARAMETER DB_FLASHBACK_RETENTION_TARGET
SELECT FLASHBACK_ON FROM V$DATABASE;

# set flashback database retention to 2 days, default value is 1440 which means 1 day.
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880 SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;

8. Set CONTROL_FILE_RECORD_KEEP_TIME parameter to 30 days. It enables database to save backup

record for 30 days. Its default value is 7 days.
SHOW PARAMETER CONTROL_FILE_RECORD_KEEP_TIME
ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME=30 SCOPE=BOTH;

9. Verify that a password file is there. Create a password file, if there isn’t one (case-sensitive).
COL FILE_NAME FOR A50
SELECT FILE_NAME FROM V$PASSWORDFILE_INFO;

# verify
host ls -al '&FILE_NAME'

10. If sqlnet.ora file is not configured, create it (optional).

cat /u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Majid Shabani Page |6

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

11. Add the following connection descriptor into the tnsnames.ora:

proddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-srv01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
)
)

stproddb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stdb-srv01)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = stproddb)
) (UR=A)
)

12. Test the configuration:

tnsping proddb
tnsping stproddb

# The test result of both of them must be successful.

13. Now it’s time to copy the below files and start to configure standby database.
# copy pwfile file
scp /u01/app/oracle/product/19.0.0/db_1/dbs/orapwimapprod \
stdb-srv01:/u01/app/oracle/product/19.0.0/db_1/dbs/orapwstproddb

# copy tnsnames file

scp /u01/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora \
stdb-srv01:/u01/app/oracle/product/19.0.0/db_1/network/admin/

# copy sqlnet file

scp /u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora \
stdb-srv01:/u01/app/oracle/product/19.0.0/db_1/network/admin/

Majid Shabani Page |7

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Preparing Standby Database:

In the upcoming steps, you will set up the standby database system on stdb-srv2 for the Data Guard
environment.

14. Login as oracle user.

15. create an init<SID>.ora file for the standby database. Just put only DB_NAME in it. RMAN will
create the full spfile.
echo 'DB_NAME=proddb' > $ORACLE_HOME/dbs/initstproddb.ora

16. Verify that the password file exists in the standby database. please be aware name of the file must
follow orapwORACLE_SID format. Notice we copied that file in step 13.
ls -al /u01/app/oracle/product/19.0.0/db_1/dbs/orapwstproddb

17. Also, please verify the existence of the tnsnames.ora file.

ls -l /u01/app/oracle/product/19.0.0/db_1/network/admin/tnsnames.ora

18. If sqlnet.ora file is not configured, create it (optional):

cat > /u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora <<EOL
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
EOL

19. As grid user, create a static database registry in the listener. This configuration is essential to ensure
the success of the duplicate command.
# login as grid user
su – grid

# edit listener file.

vi $TNS_ADMIN/listener.ora

# add the below entries to the listener configuration file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stproddb)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
(SID_NAME = stproddb)
)
)

# save the file and exit

Tip: GLOBAL_DBNAME = db_unique_name + db domain

20. As grid user, restart the listener.

srvctl stop listener
srvctl start listener

# status of the registered service name must be UNKNOWN

lsnrctl services

Majid Shabani Page |8

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

21. As oracle user create the directories obtained in step 6, if the standby database has same structure
as primary database, you just need to create 'audit_file_dest' directory path manually same
as primary database.
# For example:
su - oracle
mkdir /u01/app/oracle/admin/stproddb/adump

Majid Shabani Page |9

Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Creating standby database through DB duplication using backup on Data Domain

with Veritas NetBackup
In the forthcoming steps, you will execute the necessary actions to create the standby database in in
db-srv2. Follow these steps to prepare the standby database auxiliary instance:

22. Login as oracle user.

23. Create the directory of the new Oracle Database home.
# Startup the auxiliary instance
export ORACLE_SID=stproddb

sqlplus / as sysdba
STARTUP NOMOUNT

Exit;

24. Before initiating the database duplication process, as the oracle user, create the following scripts
to gather backup details and monitor the restore operation. The provided script displays backup job
details.
# create directory on standby database server to save the script files.
mkdir /home/oracle/temp
cd /home/oracle/temp

# create scripts:
cat > bkp_detail.sql <<EOL
SET LINES 300
COL SESSION_RECID FOR 9999999 HEADING S_RECID
COL STATUS FOR A9
COL START_TIME FORMAT A20
COL OUTPUT_DEVICE_TYPE FOR A8 HEADING DEVICE
COL END_TIME FOR A20
col IN_SIZE for a12
col OUT_SIZE for a12
col COMP for 99.9999
col IN_SPEED for a10
col OUT_SPEED for a10

SELECT SESSION_RECID, STATUS, INPUT_TYPE, STARTING_TIME START_TIME, OUTPUT_DEVICE_TYPE,

END_TIME, HOURS, IN_SIZE, OUT_SIZE, IN_SPEED, OUT_SPEED, COMP
FROM (
SELECT SESSION_RECID, STATUS, INPUT_TYPE,TO_CHAR(START_TIME,'DD/MM/YY DY HH24:MI')
STARTING_TIME,
OUTPUT_DEVICE_TYPE, TO_CHAR(END_TIME,'DD/MM/YY DY HH24:MI') END_TIME,
ROUND(ELAPSED_SECONDS/60/60,1 )HOURS,
INPUT_BYTES_DISPLAY IN_SIZE, OUTPUT_BYTES_DISPLAY OUT_SIZE,
INPUT_BYTES_PER_SEC_DISPLAY IN_SPEED, OUTPUT_BYTES_PER_SEC_DISPLAY OUT_SPEED,
ROUND(COMPRESSION_RATIO,4) COMP
FROM V\$RMAN_BACKUP_JOB_DETAILS WHERE TRUNC(START_TIME) >= TRUNC(SYSDATE) - &1
ORDER BY START_TIME );
PROMPT
PAUSE
EOL

Usage:
bkp_detail.sql <DB Name> <duration in day>

Majid Shabani P a g e | 10
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

25. The provided script displays details of a specific completed backup job.
cat > bkpset_detail.sql<<EOL
SET LIN 500 PAGES 10000
COL STARTING_TIME FOR A19
COL COMPLETION_TIME FOR A22
COL HANDLE FOR A24
COL TIME_TAKEN_DISPLAY FOR A10
COL HOURS FOR A10
COL SIZE_GB FOR 999,999
COL FIRST_SEQ FOR 999999999
COL LAST_SEQ FOR 9999999999
COL FIRST_SCN FOR 999999999999999
COL LAST_SCN FOR 999999999999999
COL LAST_TIME FOR A24
col CHECKPOINT_CHANGE for a20
COMP COUNT LABEL 'TPIECE#:' -
MAX LABEL 'MAXPS-G:' -
SUM LABEL 'TSIZE_G:' OF SIZE_GB ON BACKUP_TYPE FOR A20
BREAK ON BACKUP_TYPE SKIP 2

SELECT DECODE(S.BACKUP_TYPE,'D','FULL','L','ARCHBKP','I','DBINC')BACKUP_TYPE,
TO_CHAR(S.START_TIME,'DD/MM/YY DY HH24:MI')STARTING_TIME,
TO_CHAR(S.COMPLETION_TIME,'DD/MM/YY DY HH24:MI:SS')COMPLETION_TIME,
S.TIME_TAKEN_DISPLAY, ROUND(P.BYTES/POWER(1024,3))SIZE_GB,
/*S.ORIGINAL_INPUT_BYTES_DISPLAY IN_SIZE, S.OUTPUT_BYTES_DISPLAY OUT_SIZE,*/
P.HANDLE, COUNT(*) FILE#, THREAD#,
MIN(A.SEQUENCE#) FIRST_SEQ, MAX(A.SEQUENCE#) LAST_SEQ,
MIN(A.NEXT_CHANGE#)FIRST_SCN, MAX(A.NEXT_CHANGE#)LAST_SCN,
TO_CHAR(MAX(A.NEXT_TIME),'YYYY-MM-DD DY HH24:MI:SS')LAST_TIME,
MAX(C.CHECKPOINT_CHANGE#) ||'' CHECKPOINT_CHANGE
FROM V\$BACKUP_SET_DETAILS S, v\$BACKUP_PIECE P,
V\$BACKUP_DATAFILE BD, V\$BACKUP_ARCHIVELOG_DETAILS A, V\$BACKUP_CONTROLFILE_DETAILS C
WHERE S.RECID = P.RECID
AND BD.SET_STAMP (+)= P.SET_STAMP
AND BD.SET_COUNT (+)= P.SET_COUNT
AND a.ID1 (+)= P.SET_STAMP
AND a.ID2 (+)= P.SET_COUNT
AND C.ID1 (+)= P.SET_STAMP
AND C.ID2 (+)= P.SET_COUNT
AND C.BTYPE (+)= 'BACKUPSET'
AND S.SESSION_KEY=&1
GROUP BY DECODE(S.BACKUP_TYPE,'D','FULL','L','ARCHBKP','I','DBINC'),
TO_CHAR(S.START_TIME,'DD/MM/YY DY HH24:MI'),
TO_CHAR(S.COMPLETION_TIME,'DD/MM/YY DY HH24:MI:SS'),
S.TIME_TAKEN_DISPLAY, S.ORIGINAL_INPUT_BYTES_DISPLAY,
S.OUTPUT_BYTES_DISPLAY , P.HANDLE, THREAD#, P.BYTES
ORDER BY STARTING_TIME, MAX(A.NEXT_TIME), LAST_SEQ;
CLEAR BREAK;
CLEAR COMP;
EOL

Usage:
bkpset_detail.sql <specific backup session key>

Majid Shabani P a g e | 11
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

26. The script below reveals the total amount of data restored by all channels.
cat > rman_tio.sql <<EOL
PROMPT
PROMPT READ WRITE PERFORMED BY ALL CHANNELS
PROMPT
COL NAME FOR A30
COL SID FOR 99999
COL VAL_GB FOR 999,999,999,999,999
SELECT N.NAME, ROUND(SUM(S.VALUE)/POWER(1024,3),2) VAL_GB FROM
V\$SESSTAT S, V\$STATNAME N, V\$SESSION SS
WHERE S.STATISTIC# = N.STATISTIC#
AND S.SID = SS.SID
AND CLIENT_INFO LIKE 'rman%'
AND NAME = 'physical write total bytes'
GROUP BY N.NAME
ORDER BY NAME, VAL_GB DESC;
EOL

Feel free to download and use the scripts on my GitHub. They include scripts for obtaining backup details
and monitoring restore operations.

27. Retrieve the SCN value of the last backup through connecting to the primary database. Specifying this
SCN in the duplicate command prevents errors.
- Tip: please be aware the backup is taken and saved in data domain by using Veritas NetBackup.
- Tip: I will use the value of the LAST_SCN column in the second output that is colored in blue. It
represents the last SCN in the archivelog backup.

# invoke SQL*PLUS and connect to the primary database as sys user

# Tip: SQLPATH points to the location where scripts are created and allows us to use them from any directory
path while connecting to the database via SQL*PLUS.

export SQLPATH=/home/oracle/temp
sqlplus sys/OracleDB@proddb as sysdba

# obtain DB backup information for last two days:

SQL> @ bkp_detail.sql 2
S_RECID STATUS INPUT_TYPE START_TIME DEVICE END_TIME HOURS IN_SIZE OUT_SIZE IN_SPEED OUT_SPEED COMP
-------- --------- ------------- -------------------- -------- -------------------- ------- ------------ ------------ ---------- ---------- --------
1477 COMPLETED ARCHIVELOG 24/12/23 SUN 05:30 SBT_TAPE 24/12/23 SUN 05:30 .0 5.07G 5.07G 108.08M 108.10M 1.0000
1483 COMPLETED ARCHIVELOG 24/12/23 SUN 09:30 SBT_TAPE 24/12/23 SUN 09:30 .0 5.01G 5.02G 106.98M 107.00M 1.0000
1489 COMPLETED ARCHIVELOG 24/12/23 SUN 13:30 SBT_TAPE 24/12/23 SUN 13:30 .0 4.20G 4.20G 87.84M 87.86M 1.0000
1495 COMPLETED ARCHIVELOG 24/12/23 SUN 17:30 SBT_TAPE 24/12/23 SUN 17:31 .0 5.01G 5.01G 88.48M 88.48M 1.0000
1501 COMPLETED ARCHIVELOG 24/12/23 SUN 21:30 SBT_TAPE 24/12/23 SUN 21:31 .0 5.45G 5.45G 97.93M 97.95M 1.0000
1507 COMPLETED ARCHIVELOG 25/12/23 MON 01:30 SBT_TAPE 25/12/23 MON 01:30 .0 4.51G 4.51G 92.40M 92.42M 1.0000
1513 COMPLETED ARCHIVELOG 25/12/23 MON 05:30 SBT_TAPE 25/12/23 MON 05:30 .0 5.34G 5.34G 113.96M 113.98M 1.0000

# obtain detail of the last backup

SQL> @ bkpset_detail.sql 1513
BACKUP_ STARTING_TIME COMPLETION_TIME TIME_TAKEN SIZE_GB HANDLE FILE# THREAD# FIRST_SEQ LAST_SEQ FIRST_SCN LAST_SCN LAST_TIME CHECKPOINT_CHANGE
------- ------------------- ---------------------- ---------- -------- ------------------------ ---------- ---------- ---------- ----------- ---------------- ---------------- ------------------------ --------------------
ARCHBKP 25/12/23 MON 05:30 25/12/23 MON 05:30:32 00:00:19 1 na2et2n5_746_1_1 2 1 40911 40912 11149157555 11149314241 2023-12-25 MON 02:30:09
25/12/23 MON 05:30 25/12/23 MON 05:30:35 00:00:22 1 n82et2n5_744_1_1 2 1 40913 40914 11149461516 11149606954 2023-12-25 MON 03:30:09
25/12/23 MON 05:30 25/12/23 MON 05:30:35 00:00:22 2 n72et2n5_743_1_1 2 1 40915 40916 11149743453 11149895773 2023-12-25 MON 04:30:12
25/12/23 MON 05:30 25/12/23 MON 05:30:34 00:00:21 1 n92et2n5_745_1_1 2 1 40917 40918 11150043454 11150191410 2023-12-25 MON 05:30:09
******* --------
TPIECE# 4
MAXPS-G 2
TSIZE_G 5

FULL 25/12/23 MON 05:30 25/12/23 MON 05:30:51 00:00:11 0 c-3759451217-20231225-01 1 11150195781
******* --------
TPIECE# 1
MAXPS-G 0
TSIZE_G 0

Majid Shabani P a g e | 12
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

28. Create the database duplicate command.

vi /oracle/temp/duplicate_db.sh

export ORACLE_SID=stimapprod
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export LOGPATH=/home/oracle/temp/restore_db.log

$ORACLE_HOME/bin/rman target sys/OracleDB@proddb AUXILIARY sys/OracleDB@stproddb log=$LOGPATH <<EOL

RUN {
ALLOCATE AUXILIARY CHANNEL STBY1 TYPE 'SBT_TAPE' parms='ENV=(NB_ORA_SERV=NetBackup_srv,NB_ORA_CLIENT=db_host)';
ALLOCATE AUXILIARY CHANNEL STBY2 TYPE 'SBT_TAPE' parms='ENV=(NB_ORA_SERV=NetBackup_srv,NB_ORA_CLIENT=db_host)';
ALLOCATE AUXILIARY CHANNEL STBY3 TYPE 'SBT_TAPE' parms='ENV=(NB_ORA_SERV=NetBackup_srv,NB_ORA_CLIENT=db_host)';
ALLOCATE AUXILIARY CHANNEL STBY4 TYPE 'SBT_TAPE' parms='ENV=(NB_ORA_SERV=NetBackup_srv,NB_ORA_CLIENT=db_host)';
DUPLICATE DATABASE FOR STANDBY DORECOVER NOFILENAMECHECK
UNTIL SCN 11150191410
SPFILE
SET DB_UNIQUE_NAME 'stproddb'
SET LOG_ARCHIVE_CONFIG 'DG_CONFIG=(proddb,stproddb)'
SET LOG_ARCHIVE_DEST_2 'SERVICE=proddb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=proddb'
SET log_archive_dest_state_2 'RESET'
SET STANDBY_FILE_MANAGEMENT 'AUTO'
SET ARCHIVE_LAG_TARGET 1800
SET CONTROL_FILES '+REDOGRP1','+REDOGRP2'
SET DB_CREATE_ONLINE_LOG_DEST_1 '+REDO1'
SET DB_CREATE_ONLINE_LOG_DEST_2 '+REDO2'
SET AUDIT_FILE_DEST '/u01/app/oracle/admin/stproddb/adump'
SET DIAGNOSTIC_DEST '/u01/app/oracle'
SET DISPATCHERS '(PROTOCOL=TCP) (SERVICE=stproddbXDB)'
SET FAL_SERVER 'proddb'
SET LOCAL_LISTENER 'LISTENER_STPRODDB'
;
}
exit;
EOL

# save and exit

Please observe the following:

• Any parameters not explicitly specified in the RMAN command will retain their values from the
source parameters.

• When using the DUPLICATE command to create a standby database, it is essential to specify the
FOR STANDBY option.

• The DORECOVER option is employed to recover the standby database after its creation.

• To perform a database duplication using backups on Data Domain, specific parameters must be
provided in the ALLOCATE AUXILIARY CHANNEL command. There are two methods to specify
these parameters: either directly in the ALLOCATE AUXILIARY CHANNEL command or by using
the SEND command. For instance, you can use the send command like this:
send 'NB_ORA_SERV=NetBackup_srv, NB_ORA_CLIENT=db_hostname'.
However, in my testing cases, the latter method raised an error in the duplicate command. The
first option, as demonstrated in the following script, works seamlessly.

• Ensure to replace the values of the following parameters with the appropriate ones:
- NB_ORA_SERV: Hostname of the NetBackup server
- NB_ORA_CLIENT: Hostname of the primary database server

Majid Shabani P a g e | 13
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

• Since the database modifications applied to the primary database occurred after the last backup
we are restoring, these changes won't be transmitted to the standby database. Therefore, we
need to set them again, and the duplicate script includes these parameters.

• Using UNTIL SCN keyword to specify the SCN that obtained in the previous step and colored in
blue in the script, it prevents facing error RMAN-06025, RMAN-06053 indicating no backup of
archived log found during duplicate process.

• When connecting to the primary database, RMAN uses the metadata in the control file of the
source database to determine which backups or copies must be used to perform the duplication.
However, if connecting RMAN client to recovery catalog, an error with the message RMAN-05543:
DUPLICATE without TARGET connection requires that DATABASE is specified will raise.

29. Run the script in background, if the session in which running duplicate command terminates, the
duplicate command don’t fail.
# make the script executable
chmod u+x /home/oracle/temp/duplicate_db.sh

# run it in background
nohup /home/oracle/temp/duplicate_db.sh &

# keep eyes on the duplicate log file.

tail -100f /home/oracle/temp/restore_db.log

Tip: If you encounter an issue with the DUPLICATE command and need to re-execute it, shut down
the instance with the abort option. Delete the generated SPFILE and data files, then proceed with
the step again.

Majid Shabani P a g e | 14
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Monitoring Restore Operation

30. Execute the following script to observe the amount of data restored by RMAN.
# invoke sql*plus and connect to the standby database
sqlplus / as sysdba

# run the below script

SQL> @ rman_tio

Sample output:

READ WRITE PERFORMED BY ALL CHANNELS

NAME VAL_GB
------------------------------ --------------------
physical write total bytes 460

Please be aware that with that script, you can determine the amount of data restored by RMAN.

Majid Shabani P a g e | 15
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

Post Action
In this section, after completing the duplicate operation, additional steps are required to finalize the Data
Guard configuration.

31. As oracle user in standby database, check database role and its name by running the following
commands.
# invoke sql*plus
sqlplus / as sysdba

# run the below command

select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

# check db name as below

show parameter DB_NAME

NAME TYPE VALUE

---------------- ----------- ------------------------------
db_name string proddb

# obtain db unique name

show parameter DB_UNIQUE_NAME
NAME TYPE VALUE
---------------- ----------- ------------------------------
db_unique_name string stproddb

32. List the SRL group members. The query returns no rows, indicating the absence of standby log files.
The reason for this absence is that the control file, restored from the latest backup, lacks
modifications, including the addition of standby logfiles at the primary database. Therefore, manual
creation is necessary, similar to the process on the primary database. Ensure to stop MRP before
executing the following command.
# List the SRL group members.
set lines 220
col MEMBER for a80
SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;

# stop MRP
recover managed standby database cancel;

# add standby logfile, they have just single member.

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO1' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO1' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO1' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO2' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO2' SIZE 4096M ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 '+REDO2' SIZE 4096M ;

# List the SRL group members.

set lines 400 pages 100
col MEMBER for a80
SELECT GROUP#, MEMBER, TYPE FROM V$LOGFILE ORDER BY 1,3;

Majid Shabani P a g e | 16
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

33. Issue the following command to start MRP process.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

# VERIFY
SELECT STATUS, SEQUENCE# FROM V$MANAGED_STANDBY WHERE PROCESS = 'MRP0';

STATUS SEQUENCE#
---------------- ----------
WAIT_FOR_LOG 40919

34. On the primary database, check the latest archived redo log. Also, on the standby database, verify
that the last archived redo log has arrived and has been applied.
# on primary database.
SELECT MAX(SEQUENCE#), THREAD#
FROM V$ARCHIVED_LOG
WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)
GROUP BY THREAD#;

# on standby database.
SELECT MAX(SEQUENCE#), THREAD#
FROM V$ARCHIVED_LOG
WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)
GROUP BY THREAD#;

Tip: the value of both servers must be the same.

35. If the STATUS column in step 34 shows WAIT_FOR_LOG, and there are archive log files that don't exist
on the standby server, you need to restore some archivelogs. To do this, execute the script below,
remembering to change the seq# based on the value obtained in step 34. Otherwise, skip this step.

Tip: Please be aware that to restore missed archived logs, I will connect to the recovery catalog
database to obtain archivelog backup details from there. Also, you can use rman_tio.sql script to
monitor restoring operation.
vi /home/oracle/temp/restore_arch.sh

export ORACLE_SID=stproddb
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
export LOGPATH=/home/oracle/temp/restore_arch.log

$ORACLE_HOME/bin/rman target/ catalog rman/oracle@catdb log=$LOGPATH <<EOL

# connect to catalog, due to RMAN-20242: specification does not match any archived log
in the repository

RUN {
ALLOCATE CHANNEL C01 DEVICE TYPE 'SBT_TAPE';
ALLOCATE CHANNEL C02 DEVICE TYPE 'SBT_TAPE';
ALLOCATE CHANNEL C03 DEVICE TYPE 'SBT_TAPE';
SEND 'NB_ORA_SERV=NetBackup_srv, NB_ORA_CLIENT=db_host';
RESTORE ARCHIVELOG FROM SEQUENCE <seq# obtained from step 34>;
}
exit;
EOL

# save and exit

Majid Shabani P a g e | 17
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

36. Make the file executable and run it. Then after keep eyes on the restore log file.
# make it executable
chmod u+x /home/oracle/temp/restore_arch.sh

# run the script in background

nohup /home/oracle/temp/restore_arch.sh &

# check logfile
tail -100f /home/oracle/temp/restore_arch.log

37. On the standby database, enable Flashback Database (optional but recommended).
# Invoke SQL*PLUS
sqlplus / as sysdba

# check if flashback is enabled, it must show NO values

SELECT LOG_MODE,FLASHBACK_ON FROM V$DATABASE;

# set flashback logs retention to two days

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880;

# assuming 'db_recovery_file_dest' has already set.

# stop MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

# enable flashback database

ALTER DATABASE FLASHBACK ON;

# start MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

# verify MRP process status

SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS ='MRP0';

38. Configure the ARCHIVELOG DELETION POLICY in RMAN for both the primary and standby databases.
# primary DB
rman target / catalog rman/oracle@catdb
configure archivelog deletion policy to APPLIED ON ALL STANDBY;

# standby DB
rman target / catalog rman/oracle@catdb
configure archivelog deletion policy to APPLIED ON STANDBY

Majid Shabani P a g e | 18
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

39. Create a script to delete applied archivelog after specific time in the standby database.
# as oracle user create the following path to save the script and its logs:

mkdir -p /home/oracle/rman_script/rman_log/delete_arch

# create the following script to delete applied archived log after 12 hours

cat > /home/oracle/rman_script/delete_arch.sh << EOL

#!/bin/bash
ORACLE_SID=stproddb; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1; export ORACLE_HOME
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
LOGFILE=/home/oracle/rman_script/rman_log/delete_arch/delete_arch_\$(date
+"%y%m%d_%H%M").log

\$ORACLE_HOME/bin/rman target "'/ AS SYSBACKUP'" LOG=\$LOGFILE <<EOF

SET ECHO ON;
RUN{
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 12/24';
}
exit;
EOF
EOL

# verify
cat /home/oracle/rman_script/delete_arch.sh

# make it executable
chmod u+x /home/oracle/rman_script/delete_arch.sh

40. Add the script to crontab and set it to run every hour.
# open crontab
crontab -e

# add record
0 * * * * /home/oracle/rman_script/delete_arch.sh # delete archive log older than
12 hours ago

# save and exit

41. In standby database, Move spfile into ASM disk group and also add database to be managed by Oracle
Restart.
# invoke sql*plus
sqlplus / as sysdba

# move spfile into DATA diskgroup

create pfile from spfile;
create spfile='+DATA/spfilestproddb.ora' from pfile;

# add database
srvctl add database -d stproddb -oraclehome /u01/app/oracle/product/19.0.0/db_1 \
-spfile +DATA/spfilestproddb.ora -role PHYSICAL_STANDBY -startoption mount \
-diskgroup DATA,FRA,REDO1,REDO2

# verify
srvctl config database -d stproddb

Majid Shabani P a g e | 19
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

# invoke sql*plus
sqlplus / as sysdba

# stop MRP and shutdown the database.

recover managed standby database cancel.
shu immedeiate
exit;

# start database using srvctl utility

srvctl start database -d stproddb

# verify
srvctl status database -d stproddb

# start MRP
sqlplus / as sysdba

recover managed standby database disconnect;

# verify
select status, sequence# from v$managed_standby where process='MRP0';

# verify spfile location – it must be on the DATA diskgroup

sho parameter spfile

42. At this point, the Data Guard configuration has been completed. Optionally, you can perform the
following tasks, which are not covered to prevent the article from becoming too lengthy:
• Creating a database service to enable application users to connect to the database when MRP is
started and running, and the database is in open mode. It's important to note that this mode is
referred to as Active Data Guard, which requires a separate license.
• Testing Switchover
• Modifying the backup script to delete archivelogs older than desired hours or even days based on
the business requirements, for example, 12 hours. Ensure that sufficient space is available to store
these archivelog files on the standby database.
• Enabling broker
• Enabling FSFO

Majid Shabani P a g e | 20
Duplicating Oracle Database 19c using Data Domain Backup with Veritas NetBackup

In wrapping up, duplicating an Oracle Database using backups on Data Domain device has proven
incredibly effective. It's not just about disaster recovery or high availability but also optimizing resources
efficiently. Sharing this journey is my way of helping others navigate this process more smoothly. Here's
to making database management friendlier and more accessible for everyone!

Thanks,
Majid Shabani

Majid Shabani P a g e | 21