Oracle 12c R2 create Standby database hands-on notes

7 min readJul 30, 2020

1. Description:-
In this hands-on lab we are going to create a physical standby for practice DG implementation and using RMAN duplicate in Oracle Database 12c R2.

2. Environment Details
Primary DB
— — — — —
hostname: ol7–12cr2
db_name: LTXA
db_unique_name: LTXA_TAIPEI

Standby DB
— — — — —
hostname: ol7–12cr2-stby
db_name: LTXA
db_unique_name: LTXA_TAINAN

3. Parameters needs to configure in both nodes.

3–1. Primary db:
* db_name — Same name for all primary and standby database
* db_unique_name — a unique db name to uniquely identify primary and standby db separately

* db_file_name_convert — specify the path name of datafile location of primary database datafile to standby location (‘original/path’,’standby/path’)
* log_file_name_convert — specify the path name of redo logfile location of primary database redo logfile to standby location (‘original/path’,’standby/path’)
* Log_archive_dest_1 — local archive destination (locally)
* Log_archive_dest_2 — destination to standby database (remotely)
* fal_server — to fetch archived log from primary db when log gap is detected at standby db.(setup by tns service name of standby db) (mainly setup in standby db)
* Standby_file_management — automatically create file at standby db site (AUTO)

3–2. Standby Side:
* db_unique_name — a unique db name to uniquely identify primary and standby db because separately
* db_file_name_convert — specify the path name of datafile location of primary database datafile to standby location (‘original/path’,’standby/path’)
* log_file_name_convert — specify the path name of redo logfile location of primary database redo logfile to standby location (‘original/path’,’standby/path’)
* log_archive_dest_1 — location of archives coming from primary database
* fal_server — to fetch archived log from primary site when log gap is detected at standby site.(setup by tns service name of primary db)
* Standby_file_management — automatically create file at standby db site (AUTO)

4. Primary db configuration step

Step 1 — Check FORCE LOGGING is enabled

Step 2 — Make sure primary is in archive log mode

Step 3 — PFILE creation and copy to standby database (scp to standby)

Step 4 — Edit the parameters and directories in created pfile for standby db
—
#Change the Audit dump location
*.audit_file_dest=’/u01/app/oracle/admin/LTXA_TAINAN/adump’
#Change the control file location
*.control_files=’/oradb/oradata/LTXA_TAINAN/control01.ctl’,’/oradb/oradata/LTXA_TAINAN/control02.ctl’
#Change the DISPATCHER service name#
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=LTXA_TAINANXDB)’

#Setup DG_CONFIG by the db_unique_name(same as two side)#

.log_archive_config=’dg_config=(LTXA_TAIPEI,LTXA_TAINAN)’
#Change the LOCAL DEST location#
*.log_archive_dest_1=’LOCATION=/oradb/archive’
#Add DB_UNIQUE_NAME for standby database#
DB_UNIQUE_NAME=LTXA_TAINAN’
#Add FAL entries#
*.fal_server=LTXA
#Add the below to the created pfile from LTXA database (same as two side)
*.db_file_name_convert=’/u01/oradata/LTXA/’,’/oradb/oradata/LTXA_TAINAN/’
*.log_file_name_convert=’/u01/archive/’,’/oradb/archive/’
—
Step 5 — Parameters for primary database

#SERVICE= <this is tns name>

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=LTXA_TAINAN
NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)’ DB_UNIQUE_NAME=’LTXA_TAINAN’ scope=spfile;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

ALTER SYSTEM SET FAL_SERVER=LTXA_TAINAN;

- added in standby db first, and to be create after DG finished.
alter database add standby logfile group 11 ‘/u01/oradata/LTXA/standby_redo1.log’ size 200M;
alter database add standby logfile group 12 ‘/u01/oradata/LTXA/standby_redo2.log’ size 200M;
alter database add standby logfile group 13 ‘/u01/oradata/LTXA/standby_redo3.log’ size 200M;
alter database add standby logfile group 14 ‘/u01/oradata/LTXA/standby_redo4.log’ size 200M;

Step 6 — Copy password file from primary to standby server (scp to standby)

-STANDBY SIDE CONFIGURATION

Step 7 — Make the respective directories for standby database

mkdir -p oradb/oradata
mkdir -p oradb/archive
mkdir -p LTXA_TAINAN/adump

Step 8 — Keep the database in NOMOUNT stage to create standby database

sqlplus / as sysdba

SQL> startup nomount pfile=’/home/oracle/pfile_stby.ora’;

Step 9 — Create spfile for standby database

SQL> create spfile from pfile=’/home/oracle/pfile_stby.ora’;

Step 10 — Listener and TNS Configuration

-Primary listener configuration:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7–12cr2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = LTXA)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/db)
)
)

-Primary connection string configuration:(tnsnames.ora)

LTXA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7–12cr2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LTXA)
)
)

LTXA_TAINAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7–12cr2-stby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LTXA)
)
)

-Standby listener configuration:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7–12cr2-stby)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

-Standby Connection string configuration:(tnsnames.ora)

LTXA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7–12cr2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LTXA)
)
)

LTXA_TAINAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol7–12cr2-stby)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LTXA)
)
)

Step 11 -Start the listener in Standby database and check the status

[oracle@ol7–12cr2-stby ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 — Production on 30-JUL-2020 02:03:32

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7–12cr2-stby)(PORT=1521)))
STATUS of the LISTENER
— — — — — — — — — — — —
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 — Production
Start Date 30-JUL-2020 00:39:26
Uptime 0 days 1 hr. 24 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol7–12cr2-stby/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7–12cr2-stby)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “LTXA” has 1 instance(s).
Instance “LTXA”, status UNKNOWN, has 1 handler(s) for this service…
Service “LTXA_TAINAN” has 1 instance(s).
Instance “LTXA”, status READY, has 1 handler(s) for this service…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

Step 12 -In standby side,check RMAN connection.Connect primary database as TARGET and standby database as AUXILIARY(creating new instance)

[oracle@ol7–12cr2-stby LTXA_TAINAN]$ rman target sys/oracle123@ltxa auxiliary sys/oracle123@ltxa_tainan

Recovery Manager: Release 12.2.0.1.0 — Production on Thu Jul 30 02:05:12 2020

connected to target database: LTXA (DBID=324771130)
connected to auxiliary database: LTXA (DBID=324771130, not open)

Step 13 — Active Duplication for creating standby database:

RMAN> duplicate target database for standby from active database nofilenamecheck;

—

Starting Duplicate Db at 30-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/12.2.0.1/db/dbs/orapwLTXA’ auxiliary format
‘/u01/app/oracle/product/12.2.0.1/db/dbs/orapwLTXA’ ;
}
executing Memory Script

Starting backup at 30-JUL-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
Finished backup at 30-JUL-20

contents of Memory Script:
{
restore clone from service ‘ltxa’ standby controlfile;
}
executing Memory Script

Starting restore at 30-JUL-20
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 ltxa
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradb/oradata/LTXA_TAINAN/control01.ctl
output file name=/oradb/oradata/LTXA_TAINAN/control02.ctl
Finished restore at 30-JUL-20

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

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
“/oradb/oradata/LTXA_TAINAN/temp01.dbf”;
switch clone tempfile all;
set newname for datafile 1 to
“/oradb/oradata/LTXA_TAINAN/system01.dbf”;
set newname for datafile 3 to
“/oradb/oradata/LTXA_TAINAN/sysaux01.dbf”;
set newname for datafile 4 to
“/oradb/oradata/LTXA_TAINAN/undotbs01.dbf”;
set newname for datafile 5 to
“/oradb/oradata/LTXA_TAINAN/example01.dbf”;
set newname for datafile 7 to
“/oradb/oradata/LTXA_TAINAN/users01.dbf”;
restore
from nonsparse from service
‘ltxa’ clone database
;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradb/oradata/LTXA_TAINAN/temp01.dbf in control file

executing command: SET NEWNAME

Starting restore at 30-JUL-20
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 ltxa
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradb/oradata/LTXA_TAINAN/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ltxa
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradb/oradata/LTXA_TAINAN/sysaux01.dbf
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 ltxa
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradb/oradata/LTXA_TAINAN/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ltxa
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /oradb/oradata/LTXA_TAINAN/example01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service ltxa
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /oradb/oradata/LTXA_TAINAN/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-JUL-20

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1047085210 file name=/oradb/oradata/LTXA_TAINAN/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1047085210 file name=/oradb/oradata/LTXA_TAINAN/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1047085210 file name=/oradb/oradata/LTXA_TAINAN/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=1047085210 file name=/oradb/oradata/LTXA_TAINAN/example01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=1047085210 file name=/oradb/oradata/LTXA_TAINAN/users01.dbf
Finished Duplicate Db at 30-JUL-20

RMAN>

—

Step 14:- Post check status for Standby database,

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

NAME DATABASE_ROLE OPEN_MODE
— — — — — — — — — — — — — — — — — — — — — — —
LTXA PHYSICAL STANDBY MOUNTED

Step 15:- Enable the recovery:

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

Step 16:- Check the Standby database sync status with primary:

SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

Thread Last Sequence Generated
— — — — — — — — — — — — — — — — -
1 41

Oracle 12c R2 create Standby database hands-on notes

Written by J.T Chang