How to Build Standby Database using DGMGRL.
- The SYS$BACKGROUND service is the service that the Oracle background processes use when establishing a connection to the database.
- The SYS$USERS service is used for local connections. A third service is created that gets its name from the setting of the SERVICE_NAMES parameter. By default the SERVICE_NAMES parameter is populated by the value of DB_UNIQUE_NAME, and by default DB_UNIQUE_NAME is populated from the DB_NAME initialization parameter.
- In other words, for a freshly created Oracle database, if you don’t set the SERVICE_NAMES or the DB_UNIQUE_NAME initialization parameters,a service is created by default with the same name as the value in the DB_NAME initialization parameter.
Step 1
Primary Database:- Listener.ora
[oraprd@prd1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERPRMDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB.hana.com)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPPRIMDB)
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERPRMDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB.hana.com)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPPRIMDB)
)
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL)
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPPRIMDB)
(SID_NAME = LAPPRIMDB)
)
)
ADR_BASE_LISTENERPRMDB = /oracle/PRD
LISTENERPRMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
ADR_BASE_LISTENERPRD = /oracle/PRD
)
ADR_BASE_LISTENERPRMDB = /oracle/PRD
LISTENERPRMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
ADR_BASE_LISTENERPRD = /oracle/PRD
Where ==>
(GLOBAL_DBNAME = LAPPRIMDB.hana.com):- Parameter GLOBAL_DBNAME to identify the database service
(SID_NAME = LAPPRIMDB) :- DB_NAME/SID NAME/INSTANCE_NAME
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL):- Use for BROKER connection.
(SID_NAME = LAPPRIMDB) :- DB_NAME/INSTANCE_NAME
Primary Database:- Tnsname.ora
[oraprd@prd1 admin]$ cat tnsnames.ora
listener_lap=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED)
(SID = LAPPRIMDB)))
LAPDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPDRDB)
)
)
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPPRIMDB)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPDRDB)
)
)
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPPRIMDB)
)
)
STANDBY DATABASE: Listener.ora & Tnsname.ora Configuration.
[oraprd@prd2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERDRDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB.hana.com)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPDRDB)
)
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB_DGMGRL)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPDRDB)
)
)
LISTENERDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
ADR_BASE_LISTENERDRDB = /oracle/PRD
[oraprd@prd2 admin]$
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERDRDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB.hana.com)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPDRDB)
)
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB_DGMGRL)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPDRDB)
)
)
LISTENERDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
ADR_BASE_LISTENERDRDB = /oracle/PRD
[oraprd@prd2 admin]$
Standby Database:- Tnsname.ora
[oraprd@prd2 admin]$ cat tnsnames.ora
[oraprd@prd2 admin]$ cat tnsnames.ora
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPPRIMDB)
)
)
LAPDRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPDRDB)
)
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPPRIMDB)
)
)
LAPDRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPDRDB)
)
)
Set Parameter on Primary Database.
Step 2: Use this Same Script on Satandby And Primary database.
[oraprd@prd1 STANDBY]$ cat 1_create_dg_para_set.sh
sqlplus / as sysdba <<eof
alter database force logging;
select log_mode,force_logging from v\$database;
show parameter LOG_ARCHIVE_DEST_2
alter system set DB_UNIQUE_NAME=LAPPRIMDB SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both;===>if any parameter set
alter system set dg_broker_start=true scope=both;
show parameter dg_broker_start
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both;===>if any parameter set
alter system set dg_broker_start=true scope=both;
show parameter dg_broker_start
create pfile='/tmp/initLAPDRDB.ora' from spfile;
eof
ps -ef |grep dmonls -ltr /tmp/initLAPDRDB.ora
set dg_broker_start=true ===> This Parameter will create below DG configuration files
dg_broker_config_file1 /oracle/PRD/19/dbs/dr1LAPPRIMDB1.dat
dg_broker_config_file2 /oracle/PRD/19/dbs/dr1LAPPRIMDB2.dat
Take Level0 Backup of database of Primary database.
[oraprd@prd1 STANDBY]$ cat rman_backup.sh
sqlplus -s / as sysdba <<eof
alter system switch logfile;
alter system switch logfile;
eof
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
rman target / <<eof
backup database format '/BKP/bkp_LAP.%U' tag Level0_bkp;
backup archivelog all format '/BKP/bkp_LAP_arc.%U' tag Arc_bkp;
backup current controlfile for standby format '/BKP/bkp_standby_crl.%U';
eof
[oraprd@prd1 dbs]$ ls -ltr initLAPPRIMDB.ora
-rw-r----- 1 oraprd oinstall 3685 May 28 23:34 initLAPPRIMDB.ora
[oraprd@prd1 dbs]$ ls -ltr orapwLAPPRIMDB
-rw-r----- 1 oraprd oinstall 3584 May 28 23:31 orapwLAPPRIMDB
[oraprd@prd1 dbs]$ grep db_name initLAPPRIMDB.ora
*.db_name='LAP'==> here DB name is LAP
[oraprd@prd1 dbs]$ grep UNIQUE initLAPPRIMDB.ora
[oraprd@prd1 dbs]$ grep -i UNIQUE initLAPPRIMDB.ora
*.db_unique_name='LAPPRIMDB'
[oraprd@prd1 dbs]$
Add Standby Redolog.
sqlplus -s / as sysdba <<eof
alter database add standby logfile group 4 ('/u001/oradata/LAP/redo04.log') size 200m reuse;
alter database add standby logfile group 5 ('/u001/oradata/LAP/redo05.log') size 200m reuse;
alter database add standby logfile group 6 ('/u001/oradata/LAP/redo06.log') size 200m reuse;
alter database add standby logfile group 7 ('/u001/oradata/LAP/redo07.log') size 200m reuse;
eof
To verify the Redo
[oraprd@prd1 STANDBY]$ cat show_redo.sh
sqlplus -s / as sysdba <<eof
select group#,thread#,sequence#,bytes,archived,status from v\$log;
select group#,sequence#,bytes,used,archived,status from v\$standby_log;
select member from v\$logfile;
set lines 200
col MEMBER for a40
select lf.group#,l_type.log_type as type, lf.member
from v\$logfile lf
join (
select group#,'ORL' as log_type from v\$log
union
select group#,'SRL' as log_type from v\$standby_log) l_type
on lf.group#=l_type.group#
order by lf.group#;
eof
Step 3: Use Below Same Script on Satandby.
[oraprd@prd2 dbs]$ ls -ltr orapwLAPDRDB
-rw-r----- 1 oraprd oinstall 3584 May 29 09:45 orapwLAPDRDB
[oraprd@prd2 dbs]$ ls -ltr initLAPDRDB.ora
-rw-r----- 1 oraprd oinstall 3691 May 29 09:35 initLAPDRDB.ora
[oraprd@prd2 dbs]$ grep -i DB_NAME initLAPDRDB.ora
*.db_name='LAP'
[oraprd@prd2 dbs]$ grep -i UNIQUE_NAME initLAPDRDB.ora
*.db_unique_name='LAPDRDB'
SQL> show parameter dg
NAME TYPE VALUE
------------------------------------ ----------- --------------------
dg_broker_config_file1 string /oracle/PRD/19/dbs/dr1LAPDRDB.dat
dg_broker_config_file2 string /oracle/PRD/19/dbs/dr2LAPDRDB.dat
dg_broker_start boolean TRUE
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL> create spfile from pfile;
File created.
SQL> exit
[oraprd@prd2 STANDBY]$ cat 1_create_dg_para_set.sh
sqlplus / as sysdba <<eof
show parameter LOG_ARCHIVE_DEST_2
show parameter LOG_ARCHIVE_DEST_2
alter system set DB_UNIQUE_NAME=LAPDRDB SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both;===>if any parameter set
alter system set dg_broker_start=true scope=both;
show parameter dg_broker_start
eof
ps -ef |grep dmon
[oraprd@prd1 STANDBY]$
--ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both;===>if any parameter set
alter system set dg_broker_start=true scope=both;
show parameter dg_broker_start
eof
ps -ef |grep dmon
[oraprd@prd1 STANDBY]$
Restore Database on Standby Side.
Restore Controlfile:-
[oraprd@prd2 STANDBY]$ cat rman_rest_con.sh
#!/bin/bash
. /BKP/.rman_env
logfile=/tmp/$(date +%Y%m%d_%H%M%S)_con.log
rman target / <<EOF > ${logfile}
set echo on;
restore controlfile from "/BKP/bkp_standby_crl.0mvtefcb_1_1";
EOF
echo "Controlfile Restoration is completed..." >> ${logfile}
echo "tail -100f $logfile"
exit
List Database Backup:-
[oraprd@prd2 STANDBY]$ cat rman_list_db.sh
#!/bin/bash
. /BKP/.rman_env
logfile=/tmp/$(date +%Y%m%d_%H%M%S)_con.log
rman target / <<EOF > ${logfile}
set echo on;
list backup of database;
list backup of archivelog all;
EOF
echo "tail -100f $logfile"
[oraprd@prd2 STANDBY]$
Restore Database:-
[oraprd@prd2 STANDBY]$ cat rman_rest_db.sh
#!/bin/bash
. /BKP/.rman_env
logfile=/tmp/$(date +%Y%m%d_%H%M%S)_${ORACLE_SID}.log
echo "tail -100f $logfile"
echo "Restoration started..." > ${logfile}
rman target / <<EOF >> ${logfile}
set echo on;
run
{
restore database;
recover database;
}
EOF
echo "Database Restoration is completed..." >> ${logfile}
exit
[oraprd@prd2 STANDBY]$
[oraprd@prd2 STANDBY]$ cat add_standby_redo.sh
sqlplus -s / as sysdba <<eof
recover managed standby database cancel;
alter database add standby logfile group 4 ('/u001/oradata/LAP/redo04.log') size 200m reuse;
alter database add standby logfile group 5 ('/u001/oradata/LAP/redo05.log') size 200m reuse;
alter database add standby logfile group 6 ('/u001/oradata/LAP/redo06.log') size 200m reuse;
alter database add standby logfile group 7 ('/u001/oradata/LAP/redo07.log') size 200m reuse;
eof
[oraprd@prd2 STANDBY]$ cat show_redo.sh
sqlplus -s / as sysdba <<eof
select group#,thread#,sequence#,bytes,archived,status from v\$log;
select group#,sequence#,bytes,used,archived,status from v\$standby_log;
select member from v\$logfile;
eof
[oraprd@prd2 STANDBY]$
Stop And Start the Standby Database.
[oraprd@prd2 STANDBY]$ cat stop_start_standby.sh
export ORACLE_SID=LAPDRDB
sta()
{
sqlplus -s / as sysdba <<eof
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
select process from v\$managed_standby;
set lines 200
col destination for a40
select destination,status,error from v\$archive_dest_status
where rownum< 3;
eof
}
stp()
{
sqlplus -s / as sysdba <<eof
alter database recover managed standby database cancel;
shut immediate;
eof
}
stp
sta
[oraprd@prd2 STANDBY]$ cat stat.sh
sqlplus -s / as sysdba <<eof
set lines 200
col destination for a40
select destination,status,error from v\$archive_dest_status
where rownum< 3;
select process from v\$managed_standby;
show parameter service name
select name,database_role,open_mode,db_unique_name from v\$database;
eof
[oraprd@prd2 STANDBY]$
[oraprd@prd2 STANDBY]$ cat arch_stat.sh
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'
sqlplus -s / as sysdba<<eof
set lines 200
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,FIRST_CHANGE#,NEXT_CHANGE#
FROM V\$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT SEQUENCE#,APPLIED FROM V\$ARCHIVED_LOG
ORDER BY SEQUENCE#;
eof
[oraprd@prd2 STANDBY]$
From Primary Database Server.
[oraprd@prd1 STANDBY]$ cat 2_create_dg_add_prim_db.sh
dgmgrl <<eof
connect sys/abc#123@lapprimdb
create configuration 'DRsolution' as primary database is 'LAPPRIMDB' connect identifier is LAPPRIMDB;
show configuration;
eof
[oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$ cat 3_create_dg_add_standby_db.sh
dgmgrl <<eof
connect sys/abc#123@LAPPRIMDB
add database LAPDRDB as connect identifier is LAPDRDB;
show configuration;
eof
[oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$ cat 4_created_enable_dg.sh
dgmgrl <<eof
connect sys/abc#123@lapprimdb
show configuration;
enable configuration;
show configuration;
eof
[oraprd@prd1 STANDBY]$
To verify the Configuration
[oraprd@prd1 STANDBY]$ cat show_dg.sh
dgmgrl <<eof
connect sys/abc#123@lapprimdb
show configuration;
show configuration verbose
show database verbose 'LAPDRDB';
show database verbose 'LAPPRIMDB';
exit
eof
echo "Bye...:"
[oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon May 31 18:19:40 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "LAPPRIMDB"
Connected as SYSDG.
DGMGRL> connect sys/abc#123@lapprimdb
Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - NewDR
Protection Mode: MaxAvailability
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 11 seconds ago)
DGMGRL>
Please update StaticConnectIdentifier value with tnsentry.
[oraprd@prd1 STANDBY]$ cat updat_staticConnection.sh
upd()
{
dgmgrl <<eof
connect sys/abc#123@lap
edit database lapprimdb set property StaticConnectIdentifier='lapprimdb';
edit database lapdrdb set property StaticConnectIdentifier='lapdrdb';
eof
}
**********************************************************************************
EOD
**********************************************************************************
