RMAN Data Guard Config and Broker Config.
Hai, in this article
I will demonstrate how to configure Dataguard with RMAN, and also
configure Broker for this Dataguard Setup.
This is single
command based Datagaurd Configuration
Assumptions:
Node
|
IP Address
|
DB Name, SID,
Listener
|
db_unique_name,
TNS Names
|
Port
|
Primary
DB
|
192.168.1.11
|
DELL
|
DELL_LIVE
|
1529
|
Standby
DB
|
192.168.1.12
|
DELL
|
DELL_STBY
|
1528
|
RAC 1 (Primary) Machine Database DELL is
installed
RAC 2 (Machine) Installed only Oracle
Software
Mandatory requirements:
1.
Oracle database
Version should be min 11gR2 (Duplicate Command will not work for 11gR1)
2.
PRAMETER file should
be in SPFILE
3.
DBNAME, SID,TNS
should be defined in Uppercase (Case sensitive)
4.
Password
file(orapwDELL) should be on both locations with same password
5.
All directory
available with valid permissions
6.
Auxiliary database
should connect as “not mounted”
Directory and env.
File for PRIMARY and STANDBY:
RAC-1 PRIMARY
|
RAC-2 STANDBY
|
mkdir -p /u01/app/oracle/DELL/arch/
mkdir -p /u01/app/oracle/flash_recovery_area/
chown –R oracle:oinstall /u01
chmod –R 775 /u01
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ vi dell.env
export ORACLE_SID=DELL
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export
LD_LIBRARY_PATH
|
mkdir -p /u01/app/oracle/DELL/arch/
mkdir -p /u01/app/oracle/flash_recovery_area/
chown –R oracle:oinstall /u01
chmod –R 775 /u01
[root@rac2 ~]# su - oracle
[oracle@rac2 ~]$ vi dell.env
export ORACLE_SID=DELL
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export
LD_LIBRARY_PATH
|
PARAMTER, Listener, tnsnames,sqlnet.ora
files for PRIMARY and STANDBY:
RAC-1 PRIMARY
|
RAC-2 STANDBY
|
Create pfile from spfile and then add below parameters
for PRIMARY
[oracle@rac1
dbs]$ vi initDELL.ora
#--paste
the parameters in notepad without option
Word Wrap and then copy to your pfile
#--addnl
prameter for PRIMARY
*.db_flashback_retention_target=4320
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_unique_name='DELL_LIVE'
*.global_names=TRUE
*.local_listener='DELL'
#
*.log_archive_dest_1='location="/u01/app/oracle/DELL/arch"','valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=DELL_LIVE'
#
*.log_archive_dest_2='service="DELL_STBY"','ASYNC
NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1
reopen=300 db_unique_name="DELL_STBY"
net_timeout=30','valid_for=(online_logfile,all_roles)'
#
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_trace=0
*.log_archive_format='arch%s%t%r.arc'
*.archive_lag_target=0
*.log_archive_max_processes=30
*.log_archive_min_succeed_dest=1
*.db_file_name_convert='DELL_STBY','DELL_LIVE'
*.log_file_name_convert='DELL_STBY','DELL_LIVE'
*.log_archive_config='dg_config=(DELL_LIVE,DELL_STBY)'
*.fal_client='DELL_LIVE'
*.fal_server='DELL_STBY'
*.standby_file_management='AUTO'
###----DG
Broker Parameters----###
*.dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1.dat'
*.dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2.dat'
*.dg_broker_start=TRUE
|
Create New Pfile using vi-editor and add the below
parameters for STANDBY
[oracle@rac2
dbs]$ vi initDELL.ora
#--paste the parameters in notepad without option
Word Wrap and then copy to your pfile
#--addnl
parameters for STANDBY
*.db_name='DELL'
*.db_unique_name='DELL_STBY'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.global_names=TRUE
*.local_listener='DELL'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.diagnostic_dest='/u01/app/oracle'
*.memory_target=730857472
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#
*.log_archive_dest_1='location="/u01/app/oracle/DELL/arch"','valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=DELL_STBY'
#
*.log_archive_dest_2=''
#
*.log_archive_trace=0
*.log_archive_format='arch%s%t%r.arc'
*.archive_lag_target=0
*.log_archive_max_processes=30
*.log_archive_min_succeed_dest=1
*.db_file_name_convert='DELL_LIVE','DELL_STBY'
*.log_file_name_convert='DELL_LIVE','DELL_STBY'
*.log_archive_config='dg_config=(DELL_LIVE,DELL_STBY)'
*.fal_client='DELL_STBY'
*.fal_server='DELL_LIVE'
*.standby_file_management='AUTO'
###----DG
Broker Parameters----###
*.dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1.dat'
*.dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2.dat'
*.dg_broker_start=TRUE
|
[oracle@rac1
admin]$ cat listener.ora
DELL
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1529))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1529))
)
)
SID_LIST_DELL
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DELL_LIVE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DELL)
)
(SID_DESC =
(GLOBAL_DBNAME = DELL_LIVE_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DELL)
)
)
INBOUND_CONNECT_TIMEOUT_DELL
= 100
|
[oracle@rac2
admin]$ cat listener.ora
DELL
=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1528))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
)
)
SID_LIST_DELL
=
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DELL_STBY)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DELL)
)
(SID_DESC =
(GLOBAL_DBNAME = DELL_STBY_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DELL)
)
)
INBOUND_CONNECT_TIMEOUT_DELL
= 100
|
[oracle@rac1
admin]$ cat tnsnames.ora
DELL
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DELL)
)
)
DELL_LIVE
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_LIVE)
(SID = DELL)
(UR=A)
)
)
DELL_STBY
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
192.168.1.12)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_STBY)
(SID = DELL)
(UR=A)
)
)
DELL_LIVE_DGMGRL
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_LIVE_DGMGRL)
(INSTANCE_NAME = DELL)
)
)
DELL_STBY_DGMGRL
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_STBY_DGMGRL)
(INSTANCE_NAME = DELL)
)
)
|
[oracle@rac2
admin]$ cat tnsnames.ora
DELL
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = DELL)
)
)
DELL_LIVE
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_LIVE)
(SID= DELL)
(UR=A)
)
)
DELL_STBY
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT
= 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_STBY)
(SID= DELL)
(UR=A)
)
)
DELL_LIVE_DGMGRL
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_LIVE_DGMGRL)
(INSTANCE_NAME=DELL)
)
)
DELL_STBY_DGMGRL
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.12)(PORT = 1528))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DELL_STBY_DGMGRL)
(INSTANCE_NAME=DELL)
)
)
|
If required create and add following in sqlnet.ora file on both Primary & Standby :
[oracle@rac2 admin]$ cat
sqlnet.ora
[oracle@rac1
admin]$ cat sqlnet.ora
MES.DIRECTORY_PATH=(TNSNAMES,
ONAMES, HOSTNAME)
SQLNET.EXPIRE_TIME=
10
SQLNET.INBOUND_CONNECT_TIMEOUT
=60
[oracle@rac1
admin]$
[oracle@rac1
admin]$
Now install DELL database using dbca
At step no
5. Provide Same Administrative Password as sys for password file
At step no
6. (2) Use Common location /u01/app/oracle
At step no
7. Disable flash recovery, Archive in GUI
PRIMARY
DB (192.168.1.11)
[oracle@rac1 DELL]$ cd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@rac1 dbs]$ ll
total 24
-rw-rw---- 1 oracle
oinstall 1544 Feb 2 19:49 hc_DBUA0.dat
-rw-rw---- 1 oracle
oinstall 1544 Feb 2 19:50 hc_DELL.dat
-rw-r--r-- 1 oracle
oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle
oinstall 24 Feb 2 19:52 lkDELL
-rw-r----- 1 oracle
oinstall 1536 Feb 2 19:53 orapwDELL
-rw-r----- 1 oracle
oinstall 2560 Feb 2 19:55 spfileDELL.ora
[oracle@rac1 dbs]$ sqlplus / as
sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Mar 8
08:48:54 2017
Copyright (c) 1982,
2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
SQL>
SQL> show
parameter pfile;
NAME TYPE VALUE
---------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
Create Pfile & add Parameters & enable features
PRIMARY
DB (192.168.1.11)
SQL> create pfile
from spfile;
File created.
SQL> shut
immediate
Database closed.
Database dismounted.
ORACLE instance shut
down.
SQL> exit
Disconnected from
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning,
OLAP, Data Mining and Real Application Testing options
[oracle@rac1 dbs]$ ll
total 28
-rw-rw---- 1 oracle
oinstall 1544 Feb 2 19:49 hc_DBUA0.dat
-rw-rw---- 1 oracle
oinstall 1544 Feb 2 19:50 hc_DELL.dat
-rw-r--r-- 1 oracle
oinstall 807 Feb 2 19:57 initDELL.ora
-rw-r--r-- 1 oracle
oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle
oinstall 24 Feb 2 19:52 lkDELL
-rw-r----- 1 oracle
oinstall 1536 Feb 2 19:53 orapwDELL
-rw-r----- 1 oracle
oinstall 2560 Feb 2 19:55 spfileDELL.ora
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ mv
spfileDELL.ora bkp_spfileDELL.ora
[oracle@rac1 dbs]$ ll
total 28
-rw-r----- 1 oracle
oinstall 2560 Feb 2 19:55 bkp_spfileDELL.ora
-rw-rw---- 1 oracle
oinstall 1544 Feb 2 19:49 hc_DBUA0.dat
-rw-rw---- 1 oracle
oinstall 1544 Feb 2 19:50 hc_DELL.dat
-rw-r--r-- 1 oracle
oinstall 807 Feb 2 19:57 initDELL.ora
-rw-r--r-- 1 oracle
oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle
oinstall 24 Feb 2 19:52 lkDELL
-rw-r----- 1 oracle
oinstall 1536 Feb 2 19:53 orapwDELL
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ orapwd
file=orapwDELL password=sys force=y
[oracle@rac1 dbs]$ vi
initDELL.ora ( Copy the above given PRIMARY init parameters)
Enable the following features for Database DELL
1. Archive log
|
2. Flashback database
|
3. Force logging
|
4. Adding standby redo logs
|
PRIMARY DB (192.168.1.11)
[oracle@rac1 dbs]$ sqlplus / as
sysdba
Connected to an idle
instance.
SQL> startup
mount
Database mounted.
SQL>
SQL> show
parameter spfile;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
spfile string
SQL>
orapwd file query from database:
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ---------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SQL>
Checkout our given parameters
are loaded or not
SQL> show
parameter fal;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
fal_client string DELL_LIVE
fal_server string DELL_STBY
1. Enable archive log
SQL> archive log
list
Database log mode No Archive Mode
Automatic
archival Disabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 1
Current log
sequence 2
SQL> alter
database archivelog;
Database altered.
SQL> archive log
list
Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 1
Next log sequence to
archive 2
Current log
sequence 2
SQL>
2. Enable flashback and
force logging.
SQL> select
LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;
LOG_MODE FLASHBACK_ON FOR
------------ ---- -------------- ---
ARCHIVELOG NO NO
SQL> alter
database flashback on;
Database altered.
SQL> alter
database force logging;
Database altered.
SQL> select
LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;
LOG_MODE FLASHBACK_ON FOR
------------
------------------ ---
ARCHIVELOG YES YES
SQL>
3. Add standby log files
as per your requirements
SQL> col MEMBER
for a40
SQL> SELECT *
FROM V$LOGFILE;
GROUP# STATUS TYPE
MEMBER
IS_
---------- -------
------- ---------------------------------------- ---
3 ONLINE
/u01/app/oracle/ DELL /redo03.log NO
2 ONLINE
/u01/app/oracle/ DELL /redo02.log NO
1 ONLINE
/u01/app/oracle/ DELL /redo01.log NO
SQL> select bytes
from v$standby_log;
no rows selected
SQL> alter
database add standby logfile '/u01/app/oracle/DELL/stbyredo04.log' size 50M;
Database altered.
SQL> alter
database add standby logfile '/u01/app/oracle/DELL/stbyredo05.log' size 50M;
Database altered.
SQL> alter
database add standby logfile '/u01/app/oracle/DELL/stbyredo06.log' size 50M;
Database altered.
SQL> col MEMBER
for a40
SQL> SELECT *
FROM V$LOGFILE;
GROUP# STATUS TYPE
MEMBER
IS_
---------- -------
------- ---------------------------------------- ---
3 ONLINE
/u01/app/oracle/DELL/redo03.log
NO
2 ONLINE
/u01/app/oracle/DELL/redo02.log
NO
1 ONLINE
/u01/app/oracle/DELL/redo01.log
NO
4 STANDBY
/u01/app/oracle/DELL/stbyredo04.log
NO
5 STANDBY
/u01/app/oracle/DELL/stbyredo05.log
NO
6 STANDBY
/u01/app/oracle/DELL/stbyredo06.log
NO
6 rows selected.
SQL>
SQL>
SQL> select bytes
from v$standby_log;
BYTES
----------
52428800
52428800
52428800
SQL>
Current Datafiles count
SQL> select count(*) from v$dba_data_files;
SQL> select count(*) from v$database:
To check component list and last applied bundel patch
SQL> select comp_id,comp_name,version,status from dba_registry;
SQL>
SQL> select description from dba_registry_sqlpatch;SQL>
All the features required
for dataguard are enabled
Now start the database
with SPFILE.
SQL> select
status from v$instance;
STATUS
------------
MOUNTED
SQL>SQL> alter
database open;
Database altered.
SQL> show
parameter spfile;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
spfile string
Database is using Pfile.
So, create spfile from pfile and start
SQL> create
spfile from pfile;
File created.
SQL> shut
immediate;
Database closed.
Database dismounted.
ORACLE instance shut
down.
Now start the database
with SPFILE
SQL>
SQL> startup
Database mounted.
Database opened.
SQL> show
parameter spfile;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL>
SQL>
Configure Listener, tns, sqlnet on PRIME & STBY
PRIMARY DB (192.168.1.11)
[oracle@rac1 dbs]$ cd
$TNS_ADMIN
[oracle@rac1 admin]$ vi
listener.ora ( Copy the above given Primary listener parameter)
[oracle@rac1 admin]$ vi
tnsnames.ora ( Copy the above given Primary tns parameter)
[oracle@rac1 admin]$ vi
sqlnet.ora ( Copy the above given Primary sqlnet parameter)
[oracle@rac1 admin]$ lsnrctl
start dell
LSNRCTL for Linux:
Version 11.2.0.3.0 - Production on 07-MAR-2017 13:11:33
Copyright (c) 1991,
2011, Oracle. All rights reserved.
Starting
/u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux:
Version 11.2.0.3.0 - Production
System parameter file
is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written
to
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/rac1/dell/alert/log.xml
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1529)))
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.11)(PORT=1529)))
STATUS of the LISTENER
------------------------
Alias dell
Version TNSLSNR for Linux: Version
11.2.0.3.0 - Production
Start Date 07-MAR-2017 13:11:34
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter
File
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/rac1/dell/alert/log.xml
Listening Endpoints
Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1529)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1529)))
Services Summary...
Service "DELL_LIVE" has 1 instance(s).
Instance "DELL", status UNKNOWN,
has 1 handler(s) for this service...
Service "DELL_LIVE_DGMGRL" has 1 instance(s).
Instance "DELL", status UNKNOWN,
has 1 handler(s) for this service...
The command completed
successfully
[oracle@rac1 admin]$
Services with name
DELL_LIVE and DELL_LIVE_DGMRL started for Listener
[oracle@rac1 admin]$
[oracle@rac1 admin]$ tnsping dell
[oracle@rac1
admin]$ tnsping dell_live
[oracle@rac1
admin]$ tnsping dell_live_dgmgrl
[oracle@rac1 admin]$ tnsping
dell_stby
STANDBY DB (192.168.1.12)
[oracle@rac2 DELL]$ cd
$TNS_ADMIN
[oracle@rac2 admin]$ vi
listener.ora ( Copy the above given Standby listener parameter)
[oracle@rac2 admin]$ vi
tnsnames.ora ( Copy the above given Standby tns parameter)
[oracle@rac2 admin]$ vi
sqlnet.ora ( Copy the above given Standby sqlnet parameter)
[oracle@rac2 admin]$ lsnrctl
start dell
LSNRCTL for Linux:
Version 11.2.0.3.0 - Production on 07-MAR-2017 13:14:09
Copyright (c) 1991,
2011, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr:
please wait...
TNSLSNR for Linux:
Version 11.2.0.3.0 - Production
System parameter file
is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written
to /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/rac2/dell/alert/log.xml
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1528)))
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1528)))
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.12)(PORT=1528)))
STATUS of the LISTENER
------------------------
Alias dell
Version TNSLSNR for Linux: Version
11.2.0.3.0 - Production
Start Date 07-MAR-2017 13:14:09
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter
File
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/rac2/dell/alert/log.xml
Listening Endpoints
Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.12)(PORT=1528)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1528)))
Services Summary...
Service "DELL_STBY" has 1 instance(s).
Instance "DELL", status UNKNOWN,
has 1 handler(s) for this service...
Service "DELL_STBY_DGMGRL" has 1 instance(s).
Instance "DELL", status UNKNOWN,
has 1 handler(s) for this service...
The command completed
successfully
[oracle@rac2 admin]$
Services with name
DELL_STBY, DELL_STBY_DGMGRL started
[oracle@rac1 admin]$
[oracle@rac2 admin]$ tnsping dell
[oracle@rac2 admin]$ tnsping
dell_live
[oracle@rac2 admin]$ tnsping
dell_live_dgmgrl
[oracle@rac2 admin]$ tnsping
dell_stby
[oracle@rac2 admin]$ tnsping
dell_stby_dgmgrl
[oracle@rac2 admin]$ cd
$ORACLE_HOME/dbs
[oracle@rac2 dbs]$ vi
initDELL.ora ( Copy the above given Standby init parameter)
PRIMARY DB (192.168.1.11)
[oracle@rac1 admin]$ tnsping
dell_stby
[oracle@rac1 admin]$ tnsping
dell_stby_dgmgrl
STANDBY DB (192.168.1.12)
[oracle@rac2 DELL]$ cd
[oracle@rac2 ~]$ . dell
[oracle@rac2 dbs]$ cd
$ORACLE_HOME/dbs/
[oracle@rac2 dbs]$ vi
initDELL.ora ( Copy the above given Standby init parameter)
[oracle@rac2 dbs]$ sqlplus / as
sysdba
Connected to an idle
instance.
SQL> startup
nomount pfile='initDELL.ora'
ORACLE instance
started.
SQL> show
parameter spfile;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
spfile string
SQL> show
parameter pfile;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
spfile string
SQL> create
spfile from pfile;
File created.
SQL> shut
ORACLE instance shut
down.
Start Standby database in nomount state using spfile
STANDBY DB (192.168.1.12)
SQL> startup
nomount
SQL> show
parameter spfile;
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileDELL.ora
SQL> show
parameter background_dump_dest
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
background_dump_dest string /u01/app/oracle/diag/rdbms/del
l_stby/DELL/trace
SQL>
SQL> select value from v$diag_info where name='Diag Trace';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dell_stby/DELL/trace
SQL>
Note: Open tail for alert
log of Standby in new window and keep on observing it till end of DGMGRL
[root@rac2 ~]# tail -f /u01/app/oracle/diag/rdbms/dell_stby/DELL/trace/alert_DELL.log
SQL> archive log
list;
ORA-01507: database
not mounted
SQL> exit
Creation of Password file for Standby
STANDBY DB (192.168.1.12)
[oracle@rac2 dbs]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@rac2 dbs]$ orapwd
file=orapwDELL password=sys
[oracle@rac2 dbs]$ ll
total 16
-rw-r--r-- 1 oracle
oinstall 1150 Feb 2 20:40 initDELL.ora
-rw-r--r-- 1 oracle
oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle
oinstall 1536 Feb 2 20:50 orapwDELL
-rw-r----- 1 oracle
oinstall 3584 Feb 2 20:41 spfileDELL.ora
[oracle@rac2 dbs]$ cd
/u01/app/oracle/DELL/
[oracle@rac2 DELL]$ ll
total 4
drwxr-xr-x 2 oracle oinstall
4096 Mar 7 14:03 arch
RMAN DATAGUARD CONFIGURATION SETUP WITH SINGLE COMMAND
STANDBY DB (192.168.1.12)
Connect RMAN using target
and auxiliary should connected as “not mounted” only.
If the connection shows
other than this as (not started, not connected) then you are missing something.
Fix the issue if you got.
[oracle@rac2 dbs]$ rman target
sys/sys@dell_live auxiliary sys/sys@dell_stby
Recovery Manager:
Release 11.2.0.3.0 - Production on Tue Mar 7 13:25:14 2017
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
connected to target
database: DELL (DBID=3908745866)
connected to auxiliary
database: DELL (not mounted)
RMAN>
The connection is Correct.
Now duplicate the Target
DB.
Monitor both PRIMARY and STANDBY alert logs for updates
RMAN>
RMAN> duplicate
target database for standby from active database nofilenamecheck dorecover;
Starting Duplicate Db
at 07-MAR-17
using target database
control file instead of recovery catalog
allocated channel:
ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory
Script:
{
backup as copy reuse
targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDELL' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDELL' ;
}
executing Memory
Script
Starting backup at
07-MAR-17
allocated channel:
ORA_DISK_1
channel ORA_DISK_1:
SID=68 device type=DISK
Finished backup at
07-MAR-17
contents of Memory
Script:
{
sql clone "alter system set control_files =
''/u01/app/oracle/flash_recovery_area/DELL_STBY/controlfile/o1_mf_dcx2pn1s_.ctl''
comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for
standby auxiliary format
'/u01/app/oracle/flash_recovery_area/DELL_STBY/controlfile/o1_mf_dcx2pn20_.ctl';
sql clone "alter system set control_files =
''/u01/app/oracle/flash_recovery_area/DELL_STBY/controlfile/o1_mf_dcx2pn20_.ctl''
comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory
Script
sql statement: alter
system set control_files =
''/u01/app/oracle/flash_recovery_area/DELL_STBY/controlfile/o1_mf_dcx2pn1s_.ctl''
comment= ''Set by RMAN'' scope=spfile
Starting backup at
07-MAR-17
using channel
ORA_DISK_1
channel ORA_DISK_1:
starting datafile copy
copying standby
control file
output file
name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_DELL.f
tag=TAG20170307T132628 RECID=1 STAMP=938006788
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:03
Finished backup at
07-MAR-17
sql statement: alter
system set control_files =
''/u01/app/oracle/flash_recovery_area/DELL_STBY/controlfile/o1_mf_dcx2pn20_.ctl''
comment= ''Set by RMAN'' scope=spfile
Oracle instance shut
down
connected to auxiliary
database (not started)
Oracle instance
started
Total System Global
Area 730714112 bytes
Fixed Size 2231952 bytes
Variable Size 432013680 bytes
Database Buffers 293601280 bytes
Redo Buffers 2867200 bytes
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
"/u01/app/oracle/DELL/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/DELL/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/DELL/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/DELL/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/DELL/users01.dbf";
backup as copy reuse
datafile
1 auxiliary format
"/u01/app/oracle/DELL/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/DELL/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/DELL/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/DELL/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory
Script
executing command: SET
NEWNAME
renamed tempfile 1 to
/u01/app/oracle/DELL/temp01.dbf in control file
executing command: SET
NEWNAME
executing command: SET
NEWNAME
executing command: SET
NEWNAME
executing command: SET
NEWNAME
Starting backup at
07-MAR-17
using channel
ORA_DISK_1
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00001 name=/u01/app/oracle/DELL/system01.dbf
output file
name=/u01/app/oracle/DELL/system01.dbf tag=TAG20170307T132646
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00002 name=/u01/app/oracle/DELL/sysaux01.dbf
output file
name=/u01/app/oracle/DELL/sysaux01.dbf tag=TAG20170307T132646
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00003 name=/u01/app/oracle/DELL/undotbs01.dbf
output file
name=/u01/app/oracle/DELL/undotbs01.dbf tag=TAG20170307T132646
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1:
starting datafile copy
input datafile file
number=00004 name=/u01/app/oracle/DELL/users01.dbf
output file
name=/u01/app/oracle/DELL/users01.dbf tag=TAG20170307T132646
channel ORA_DISK_1:
datafile copy complete, elapsed time: 00:00:01
Finished backup at
07-MAR-17
sql statement: alter
system archive log current
contents of Memory
Script:
{
backup as copy reuse
archivelog like
"/u01/app/oracle/DELL/arch/arch61938005132.arc" auxiliary
format
"/u01/app/oracle/DELL/arch/arch61938005132.arc" archivelog like
"/u01/app/oracle/DELL/arch/arch71938005132.arc"
auxiliary format
"/u01/app/oracle/DELL/arch/arch71938005132.arc" ;
catalog clone archivelog
"/u01/app/oracle/DELL/arch/arch61938005132.arc";
catalog clone archivelog
"/u01/app/oracle/DELL/arch/arch71938005132.arc";
switch clone datafile all;
}
executing Memory
Script
Starting backup at
07-MAR-17
using channel
ORA_DISK_1
channel ORA_DISK_1:
starting archived log copy
input archived log
thread=1 sequence=6 RECID=5 STAMP=938006808
output file
name=/u01/app/oracle/DELL/arch/arch61938005132.arc RECID=0 STAMP=0
channel ORA_DISK_1:
archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1:
starting archived log copy
input archived log
thread=1 sequence=7 RECID=6 STAMP=938006891
output file
name=/u01/app/oracle/DELL/arch/arch71938005132.arc RECID=0 STAMP=0
channel ORA_DISK_1:
archived log copy complete, elapsed time: 00:00:01
Finished backup at
07-MAR-17
cataloged archived log
archived log file
name=/u01/app/oracle/DELL/arch/arch61938005132.arc RECID=1 STAMP=938006893
cataloged archived log
archived log file
name=/u01/app/oracle/DELL/arch/arch71938005132.arc RECID=2 STAMP=938006893
datafile 1 switched to
datafile copy
input datafile copy
RECID=1 STAMP=938006894 file name=/u01/app/oracle/DELL/system01.dbf
datafile 2 switched to
datafile copy
input datafile copy
RECID=2 STAMP=938006894 file name=/u01/app/oracle/DELL/sysaux01.dbf
datafile 3 switched to
datafile copy
input datafile copy
RECID=3 STAMP=938006894 file name=/u01/app/oracle/DELL/undotbs01.dbf
datafile 4 switched to
datafile copy
input datafile copy
RECID=4 STAMP=938006894 file name=/u01/app/oracle/DELL/users01.dbf
contents of Memory
Script:
{
set until scn 1007250;
recover
standby
clone database
delete archivelog
;
}
executing Memory
Script
executing command: SET
until clause
Starting recover at
07-MAR-17
allocated channel:
ORA_AUX_DISK_1
channel
ORA_AUX_DISK_1: SID=44 device type=DISK
starting media
recovery
archived log for
thread 1 with sequence 6 is already on disk as file /u01/app/oracle/DELL/arch/arch61938005132.arc
archived log for
thread 1 with sequence 7 is already on disk as file
/u01/app/oracle/DELL/arch/arch71938005132.arc
archived log file
name=/u01/app/oracle/DELL/arch/arch61938005132.arc thread=1 sequence=6
archived log file
name=/u01/app/oracle/DELL/arch/arch71938005132.arc thread=1 sequence=7
media recovery
complete, elapsed time: 00:00:00
Finished recover at
07-MAR-17
Finished Duplicate Db
at 07-MAR-17
RMAN>
RMAN>
RMAN>
RMAN> exit
Recovery Manager
complete.
RMAN>
================================================================================================================
Informational note:
If Duplicate command issued without "dorecover" option then manually start Standby db Recovery (MRP Process) on mount stage :
If you want standby database to be restored and recovered at same time then issue duplicate command with dorecover option:
- DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
- DORECOVER: When the DUPLICATE command is specified with the DORECOVER option, it starts recovery after restoration. The recovery is performed using all available archived redo logs and incremental backups. This is the recommended option while creating standby databases using recovery manager. This will save the extra step of recovering the standby database once it is created
RMAN>
RMAN> duplicate target database for standby from active database nofilenamecheck;
SQL> select status from v$instance;
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database recover managed standby database disconnect from sessions;
Database altered.
SQL>
The Managed Recovery
Process (MRP) applies information from the archived redo logs to the standby
database. When performing managed recovery operations, log apply services
automatically apply archived redo logs to maintain transactional
synchronization with the primary database.
Now verify standby db after all Archives applied:
- CRD files,
- Flashback, forcelogging, Archivelog ---> Enable all
- Start DG Broker configuration
Check CRD files created at Standby Side
STANDBY DB (192.168.1.12)
[oracle@rac2 dbs]$ cd
/u01/app/oracle/DELL/
[oracle@rac2 DELL]$ ll
total 1573492
drwxrwxr-x 2 oracle
oinstall 4096 Mar 7 13:37 arch
-rw-r----- 1 oracle
oinstall 52429312 Mar 7 13:28 redo01.log
-rw-r----- 1 oracle
oinstall 52429312 Mar 7 13:28 redo02.log
-rw-r----- 1 oracle
oinstall 52429312 Mar 7 13:28 redo03.log
-rw-r----- 1 oracle
oinstall 52429312 Mar 7 13:46 stbyredo04.log
-rw-r----- 1 oracle
oinstall 52429312 Mar 7 13:37 stbyredo05.log
-rw-r----- 1 oracle
oinstall 52429312 Mar 7 13:28 stbyredo06.log
-rw-r----- 1 oracle
oinstall 524296192 Mar 7 13:28
sysaux01.dbf
-rw-r----- 1 oracle
oinstall 734011392 Mar 7 13:28
system01.dbf
-rw-r----- 1 oracle
oinstall 31465472 Mar 7 13:28 undotbs01.dbf
-rw-r----- 1 oracle
oinstall 5251072 Mar 7 13:28 users01.dbf
[oracle@rac2 DELL]$
[oracle@rac2 dbs]$ sqlplus / as
sysdba
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 0
Next log sequence to
archive 0
Current log
sequence 0
SQL>
-----------------------------------------------------------------------------
Wait till STANDBY alert log file updates RFS Process started receiving Redo from Primary DB (5 Mins)
Wed Mar 08 09:10:31 2017
Switch of datafile 1
complete to datafile copy
checkpoint is 1006629
Switch of datafile 2
complete to datafile copy
. . . .
. . . .
. . . .
. . . .
Errors in file /u01/app/oracle/diag/rdbms/dell_stby/DELL/trace/DELL_ora_6139.trc:
ORA-00313: open failed
for members of log group 6 of thread 0
ORA-00312: online log
6 thread 0: '/u01/app/oracle/DELL/stbyredo06.log'
ORA-27037: unable to
obtain file status
Linux-x86_64 Error: 2:
No such file or directory
Additional
information: 3
Completed: alter
database clear logfile group 6
RFS connections are
allowed
Wed Mar 08 09:14:27 2017
RFS[1]: Assigned to
RFS process 6733
RFS[1]: Selected log 4
for thread 1 sequence 8 dbid -386150402 branch 938076161
Wed Mar 08 09:14:27
2017
Primary database is in
MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to
RFS process 6737
RFS[2]: Selected log 5
for thread 1 sequence 9 dbid -386150402 branch 938076161
Wed Mar 08 09:14:27
2017
Archived Log entry 3
added for thread 1 sequence 8 ID 0xe8fb65fe dest 1:
-----------------------------------------------------------------------------
Wait till PRIMARY alert log file update LOG_ARCHIVE_DEST_2 is Active for Archival(5 Mins)
Wed Mar 08 09:10:27 2017
Thread 1 advanced to
log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0:
/u01/app/oracle/DELL/redo02.log
Archived Log entry 6
added for thread 1 sequence 7 ID 0xe8fb65fe des
Wed Mar 08 09:14:26
2017
Thread 1 advanced to
log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0:
/u01/app/oracle/DELL/redo03.log
Wed Mar 08 09:14:26
2017
Archived Log entry 7
added for thread 1 sequence 8 ID 0xe8fb65fe dest 1:
Wed Mar 08 09:14:26
2017
******************************************************************
LGWR: Setting 'active'
archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Mar 08 09:14:26 2017
ARC7: Standby redo
logfile selected for thread 1 sequence 8 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo
logfile selected for thread 1 sequence 9 for destination LOG_ARCHIVE_DEST_2
-----------------------------------------------------------------------------
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 0
Next log sequence to
archive 0
Current log
sequence 9
SQL>
SQL>
set linesize 200;
col name for a10;
col DB_UNIQUE_NAME for a12;
select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;
SQL> select
NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;
NAME CONTROL OPEN_MODE DATABASE_ROLE PROTECTION_MODE
------ -------
-------------------- ---------------- --------------------
DELL STANDBY MOUNTED PHYSICAL STANDBY
MAXIMUM PERFORMANCE
SQL>
Command for dataguard
broker status
SQL> select
NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,
DATAGUARD_BROKER from v$database;
SQL> select
NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;
NAME DATABASE_ROLE DATAGUAR
---------
---------------- --------
DELL PHYSICAL STANDBY DISABLED
SQL>
PRIMARY DB (192.168.1.11)
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 7
Next log sequence to
archive 9
Current log
sequence 9
SQL>
SQL> col name for
a6;
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;
SQL> select
NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE from v$database;
NAME CONTROL OPEN_MODE DATABASE_ROLE PROTECTION_MODE
------ -------
-------------------- ---------------- --------------------
DELL CURRENT READ WRITE PRIMARY MAXIMUM PERFORMANCE
SQL>
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
here new service added to
only PRIMARY listener
[oracle@rac1 ~]$ lsnrctl
status dell
Service "DELLXDB" has 1 instance(s).
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
Monitor the standby log
file for log shipping
SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';
System altered.
MAX(SEQUENCE#)
--------------
9
SQL> alter system switch logfile;
System altered.
SQL>
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 8
Next log sequence to
archive 10
Current log
sequence 10
SQL>
STANDBY DB (192.168.1.12)
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive destination /u01/app/oracle/DELL/arch
Oldest online log
sequence 8
Next log sequence to
archive 0
Current log
sequence 10
SQL>
Enable FLASHBACK for STANDBY
SQL>
SQL> select
LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;
LOG_MODE
FLASHBACK_ON FOR
------------
------------------ ---
ARCHIVELOG
NO
YES
SQL>
SQL> alter database
flashback on;
Database
altered.
SQL>
SQL> select
LOG_MODE,FLASHBACK_ON,FORCE_LOGGING from v$database;
LOG_MODE
FLASHBACK_ON FOR
------------
------------------ ---
ARCHIVELOG
YES
YES
SQL>
SQL> alter database open;
Database altered
SQL> select max(sequence#) from v$archived_log where applied='YES';
System altered.
MAX(SEQUENCE#)
--------------
10
SQL>
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;
SQL>
NAME DB_UNIQUE_NA CONTROL OPEN_MODE DATABASE_ROLE PROTECTION_MODE DATAGUAR
---------- ------------ ------- -------------------- ---------------- -------------------- --------
AHCRYD11 AHCSTBY1 STANDBY READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE ENABLED
SQL>
We have successfully configured Dataguard with RMAN Enjoy!!!!!
Now let’s Configure BROKER to Data Guard
PRIMARY DB (192.168.1.11)
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;
SQL> select NAME,DATABASE_ROLE,DATAGUARD_BROKER
from v$database;
NAME DATABASE_ROLE DATAGUAR
---------
---------------- --------
DELL PRIMARY DISABLED
Set parameter
dg_broker_start=TRUE in init or SPFILE
SQL> show
parameter dg_broker
NAME TYPE VALUE
----------------------------------
----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr1.dat
dg_broker_config_file2 string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/dr2.dat
dg_broker_start boolean
TRUE
SQL>
SQL>
SQL> exit
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ dgmgrl
DGMGRL for Linux:
Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000,
2009, Oracle. All rights reserved.
Welcome to DGMGRL,
type "help" for information.
DGMGRL>
DGMGRL>
DGMGRL> connect
sys/sys@DELL_LIVE
Connected.
DGMGRL>
DGMGRL> show
configuration;
ORA-16532: Data Guard
broker configuration does not exist
Configuration details
cannot be determined by DGMGRL
This is because there is
no configuration available for Broker
Now let’s create
configuration and add Database to it
DGMGRL>
DGMGRL>
DGMGRL> create
configuration DELL as
> primary
database is DELL_LIVE
> connect
identifier is DELL_LIVE;
Configuration
"dell" created with primary database "dell_live"
DGMGRL>
DGMGRL>
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
Here at this point files Dataguard
Broker files dr1.dat, dr2.dat created for Primary database as per our
spfile record.
[oracle@rac1 dbs]$ cd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@rac1 dbs]$ ll
total 9592
-rw-r----- 1 oracle
oinstall 8192 Mar 7 14:58 dr1.dat
-rw-r----- 1 oracle
oinstall 12288 Mar 7 14:58 dr2.dat
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
DGMGRL>
DGMGRL> show
configuration;
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary
database
Fast-Start Failover:
DISABLED
Configuration Status:
DISABLED
DGMGRL>
Here when you add standby database a background Process NSV1 will start on PRIMARY.
You can monitor this in PRIMARY log file
DGMGRL> add database
DELL_STBY as
> connect
identifier is DELL_STBY
> maintained
as physical;
Database
"dell_stby" added
DGMGRL>
DGMGRL>
DGMGRL> show
configuration;
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical
standby database
Fast-Start Failover:
DISABLED
Configuration Status:
DISABLED
DGMGRL>
Broker config is done now
you have to enable this Config
DGMGRL>
DGMGRL> enable configuration
(monitor PRIM and STANDBY logs)
Enabled.
DGMGRL>
Here at this point files
dr1.dat, dr2.dat files are created for Standby database and added Broker Service to STANDBY listener
DGMGRL>
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
[oracle@rac2 dbs]$ cd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@rac2 dbs]$ ll
total 60
-rw-r----- 1 oracle
oinstall 16384 Mar 7 15:06 dr1.dat
-rw-r----- 1 oracle oinstall
16384 Mar 7 15:06 dr2.dat
[oracle@rac2 ~]$
[oracle@rac2 ~]$ lsnrctl
status dell
Service "DELL_STBY_DGB" has 1 instance(s).
------ Added Broker Service to PRIMARY Listener also.
[oracle@rac1 ~]$
[oracle@rac1 ~]$ lsnrctl
status dell
Service "DELL_LIVE_DGB" has 1 instance(s).
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
DGMGRL> show
configuration;
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical standby database
Fast-Start Failover:
DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show
database verbose dell_stby
DGMGRL>
DGMGRL> validate
database dell_stby
DGMGRL>
DGMGRL> exit
[oracle@rac1 dbs]$ sqlplus / as
sysdba
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;
SQL> select
NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;
NAME DATABASE_ROLE DATAGUAR
---------
---------------- --------
DELL PRIMARY ENABLED
SQL>
SQL> archive log
list;
Database log mode Archive Mode
Automatic archival Enabled
Archive
destination
/u01/app/oracle/DELL/arch
Oldest online log
sequence 9
Next log sequence to
archive 11
Current log
sequence 11
SQL>
STANDBY DB (192.168.1.12)
SQL> select NAME,DB_UNIQUE_NAME,CONTROLFILE_TYPE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE, DATAGUARD_BROKER from v$database;
SQL> select
NAME,DATABASE_ROLE,DATAGUARD_BROKER from v$database;
NAME DATABASE_ROLE DATAGUAR
------
---------------- --------
DELL PHYSICAL STANDBY ENABLED
SQL> archive log
list;
Database log mode Archive Mode
Automatic
archival Enabled
Archive destination /u01/app/oracle/DELL/arch
Oldest online log
sequence 9
Next log sequence to
archive 0
Current log
sequence 11
SQL>
INFORMATIONAL:
Redo Transport Compression in a Data
Guard Environment (Doc ID 729551.1)
Oracle Database has the ability to compress redo as it is transmitted over a network by Data Guard Redo Transport Services.
By setting this parameter "compression=enable" in dataguard configuration, it allows to compress archive log on air and send to Standby Database server
SQL> show parameter dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="ahcstby1", ASYNC NOAF
FIRM delay=0 optional compress
ion=enable max_failure=0 max_c
onnections=1 reopen=30 db_uniq
ue_name="ahcstby1" net_timeout
=300, valid_for=(online_logfil
es,all_roles)
OR
DGMGRL> edit database 'STDBY' set property 'RedoCompression' = enable;
--------------------------------------
Create Standby Database “using compressed network backup set
from service” to reduce network bandwidth in far STBY Destination by enabling Compression Parameter
for dest_2 on Primary:
RMAN> duplicate target database for standby from active database using
compressed backupset nofilenamecheck;
==============================================================================================================
If Standby Database deleted and recreated due to any reasons like patching prod etc. then simply using backup of spfile recreate instance and issue RMAN duplicate.
Then on primary just enable standby database
DGMGRL> show configuration;
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical standby database (disabled)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> enable database dell_stby (monitor PRIM and STANDBY logs)
Enabled.
DGMGRL>
DGMGRL> show configuration;
Configuration - dell
Protection Mode: MaxPerformance
Databases:
dell_live - Primary database
dell_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
==============================================================================================================
The physical standby database uses several processes to automate archiving redo data and recovering redo logs on the standby database. On the standby database, log apply services use the following processes:
Standby Database Startup Procedure and List of Background Process in Alertlog, how Dataguard Broker is Starting Recovery Mode by itself:
Before starting
Standby make sure broker config enabled in DGMGRL Prompt and below parameters are
set in Prod & standby:
DGMGRL> show configuration
. . . . .
Configuration Status:
SUCCESS (status updated 42 seconds ago)
DGMGRL>
SQL> show parameter broker;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
dg_broker_start boolean TRUE
SQL>
SQL> show parameter config
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
log_archive_config string dg_config=(ahcryd11)
SQL>
SQL> show parameter dest_1
SQL>
SQL> show parameter dest_2
SQL>
SQL> show parameter convert
SQL>
SQL> show parameter management
SQL>
SQL> show parameter fal
Now open Standby alertlog & Start Standby
Server to check Recovering Standby DB with DG Broker:
SQL> startup nomount
RFS connections are allowed
SQL> alter database mount standby database;
alter database mount standby database
Starting background process TMON
Sun Aug 09 16:31:47 2020
TMON started with pid=31, OS id=23215
Sun Aug 09 16:31:47 2020
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
Sun Aug 09 16:31:47 2020
ARC0 started with pid=32, OS id=23217
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Sun Aug 09 16:31:47 2020
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Sun Aug 09 16:31:47 2020
Successful mount of redo thread 1, with mount id 1518148143
Sun Aug 09 16:31:47 2020
Allocating 31874880 bytes in shared pool for flashback generation buffer.
Allocated 31874880 bytes in shared pool for flashback generation buffer
Sun Aug 09 16:31:47 2020
ARC1 started with pid=33, OS id=23219
Starting background process RVWR
Sun Aug 09 16:31:47 2020
RVWR started with pid=34, OS id=23221
Starting background process ARC2
Flashback mount Marker scn during SCN 38281885701
Marker checkpoint scn during mount SCN 38281848664
Marker fgda seq 1 bno 30952
Flashback mount unfinished crash recovery 0
Physical Standby Database mounted.
Lost write protection disabled
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Starting background process ARC3
Sun Aug 09 16:31:47 2020
ARC2 started with pid=35, OS id=23223
Sun Aug 09 16:31:47 2020
ARC3 started with pid=36, OS id=23225
Starting background process ARC4
Sun Aug 09 16:31:47 2020
Starting background process ARC5
ARC4 started with pid=37, OS id=23227
Completed: alter database mount standby database
Sun Aug 09 16:31:47 2020
ARC5 started with pid=38, OS id=23229
Starting background process ARC6
Sun Aug 09 16:31:47 2020
ARC6 started with pid=39, OS id=23231
Starting background process ARC7
Sun Aug 09 16:31:47 2020
ARC7 started with pid=40, OS id=23233
Starting background process ARC8
Sun Aug 09 16:31:47 2020
Starting background process ARC9
ARC8 started with pid=41, OS id=23235
Sun Aug 09 16:31:47 2020
ARC9 started with pid=42, OS id=23237
Starting background process ARCa
Sun Aug 09 16:31:47 2020
ARCa started with pid=43, OS id=23239
Starting background process ARCb
Sun Aug 09 16:31:47 2020
Starting background process ARCc
ARCb started with pid=44, OS id=23241
Sun Aug 09 16:31:47 2020
ARCc started with pid=45, OS id=23243
Starting background process ARCd
Sun Aug 09 16:31:47 2020
ARCd started with pid=46, OS id=23245
Starting background process ARCe
Sun Aug 09 16:31:47 2020
ARCe started with pid=47, OS id=23247
Starting background process ARCf
Sun Aug 09 16:31:48 2020
ARCf started with pid=48, OS id=23249
Starting background process ARCg
Sun Aug 09 16:31:48 2020
ARCg started with pid=49, OS id=23251
Starting background process ARCh
Sun Aug 09 16:31:48 2020
ARCh started with pid=50, OS id=23253
Starting background process ARCi
Sun Aug 09 16:31:48 2020
ARCi started with pid=51, OS id=23255
Starting background process ARCj
Sun Aug 09 16:31:48 2020
ARCj started with pid=52, OS id=23257
Sun Aug 09 16:31:48 2020
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
ARC6: Archival started
ARC7: Archival started
ARC8: Archival started
ARC9: Archival started
ARCa: Archival started
ARCb: Archival started
ARCc: Archival started
ARCd: Archival started
ARCe: Archival started
ARCf: Archival started
ARCg: Archival started
ARCh: Archival started
ARCi: Archival started
Sun Aug 09 16:31:48 2020
ARCi: Becoming the 'no FAL' ARCH
Sun Aug 09 16:31:48 2020
ARC6: Becoming the heartbeat ARCH
ARC6: Becoming the active heartbeat ARCH
Sun Aug 09 16:31:48 2020
ARCj: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Sun Aug 09 16:31:51 2020
Starting Data Guard Broker (DMON)
Starting background process INSV
Sun Aug 09 16:31:51 2020
INSV started with pid=53, OS id=23259
Starting background process NSV0
Sun Aug 09 16:31:55 2020
NSV0 started with pid=54, OS id=23261
Starting background process RSM0
Sun Aug 09 16:31:59 2020
RSM0 started with pid=55, OS id=23263
Sun Aug 09 16:32:03 2020
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='DELL_STBY';
Sun Aug 09 16:32:03 2020
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='DELL_STBY';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Sun Aug 09 16:32:03 2020
Attempt to start background Managed Standby Recovery process (DELL_STBY)
Starting background process MRP0
Sun Aug 09 16:32:03 2020
MRP0 started with pid=56, OS id=23269
Sun Aug 09 16:32:03 2020
MRP0: Background Managed Standby Recovery process started (DELL_STBY)
Sun Aug 09 16:32:03 2020
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Assigned to RFS process (PID:23271)
RFS[1]: Selected log 6 for thread 2 sequence 4196 dbid 1307206531 branch 1046066129
Sun Aug 09 16:32:03 2020
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:23265)
RFS[2]: Selected log 7 for thread 1 sequence 3599 dbid 1307206531 branch 1046066129
RFS[2]: Selected log 8 for thread 1 sequence 3600 dbid 1307206531 branch 1046066129
Sun Aug 09 16:32:05 2020
Archived Log entry 321 added for thread 1 sequence 3599 ID 0x5a5ebdfb dest 1:
RFS[1]: Selected log 5 for thread 2 sequence 4197 dbid 1307206531 branch 1046066129
Sun Aug 09 16:32:05 2020
Archived Log entry 322 added for thread 2 sequence 4196 ID 0x5a5ebdfb dest 1:
Sun Aug 09 16:32:08 2020
Started logmerger process
Sun Aug 09 16:32:08 2020
Managed Standby Recovery starting Real Time Apply
Flashback mount Marker scn during SCN 38281885701
Marker checkpoint scn during mount SCN 38281848664
Marker fgda seq 1 bno 30952
Flashback mount unfinished crash recovery 0
Sun Aug 09 16:32:09 2020
Parallel Media Recovery started with 8 slaves
Sun Aug 09 16:32:09 2020
Waiting for all non-current ORLs to be archived...
Sun Aug 09 16:32:09 2020
All non-current ORLs have been archived.
Sun Aug 09 16:32:09 2020
Media Recovery Log /u01/app/oracle/DELL/fraDELL_STBY/archivelog/2020_08_09/o1_mf_1_3599_hlzypo74_.arc
Sun Aug 09 16:32:09 2020
Media Recovery Log /u01/app/oracle/DELL/fraDELL_STBY/archivelog/2020_08_09/o1_mf_2_4196_hlzypoxf_.arc
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Media Recovery Waiting for thread 1 sequence 3600 (in transit)
Sun Aug 09 16:32:15 2020
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='DELL_STBY';
Sun Aug 09 16:32:15 2020
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='DELL_STBY';
Sun Aug 09 16:32:20 2020
Recovery of Online Redo Log: Thread 1 Group 8 Seq 3600 Reading mem 0
Mem# 0: /u01/app/oracle/DELL/group_8.5323.1046066971
Mem# 1: /u01/app/oracle/DELL/group_8.8511.1046066971
Media Recovery Waiting for thread 2 sequence 4197 (in transit)
Sun Aug 09 16:32:20 2020
Recovery of Online Redo Log: Thread 2 Group 5 Seq 4197 Reading mem 0
Mem# 0: /u01/app/oracle/DELL/group_5.4130.1046066969
Mem# 1: /u01/app/oracle/DELL/group_5.5304.1046066971
Sun Aug 09 16:33:19 2020
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
RFS[2]: Selected log 7 for thread 1 sequence 3601 dbid 1307206531 branch 1046066129
Sun Aug 09 16:41:01 2020
Archived Log entry 323 added for thread 1 sequence 3600 ID 0x5a5ebdfb dest 1:
RFS[1]: Selected log 6 for thread 2 sequence 4198 dbid 1307206531 branch 1046066129
Sun Aug 09 16:41:03 2020
Archived Log entry 324 added for thread 2 sequence 4197 ID 0x5a5ebdfb dest 1:
Sun Aug 09 16:41:03 2020
Media Recovery Waiting for thread 1 sequence 3601 (in transit)
Sun Aug 09 16:41:09 2020
Recovery of Online Redo Log: Thread 1 Group 7 Seq 3601 Reading mem 0
Mem# 0: /u01/app/oracle/DELL/group_7.3721.1046066971
Mem# 1: /u01/app/oracle/DELL/group_7.7058.1046066971
Media Recovery Waiting for thread 2 sequence 4198 (in transit)
Sun Aug 09 16:41:09 2020
Recovery of Online Redo Log: Thread 2 Group 6 Seq 4198 Reading mem 0
Mem# 0: /u01/app/oracle/DELL/group_6.4351.1046066971
Mem# 1: /u01/app/oracle/DELL/group_6.4192.1046066971
Sun Aug 09 16:46:36 2020
db_recovery_file_dest_size of 307200 MB is 0.28% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sun Aug 09 16:50:02 2020
RFS[2]: Selected log 8 for thread 1 sequence 3602 dbid 1307206531 branch 1046066129
Sun Aug 09 16:50:02 2020
Archived Log entry 325 added for thread 1 sequence 3601 ID 0x5a5ebdfb dest 1:
Sun Aug 09 16:50:03 2020
RFS[1]: Selected log 5 for thread 2 sequence 4199 dbid 1307206531 branch 1046066129
Sun Aug 09 16:50:03 2020
Media Recovery Waiting for thread 1 sequence 3602 (in transit)
Sun Aug 09 16:50:03 2020
Archived Log entry 326 added for thread 2 sequence 4198 ID 0x5a5ebdfb dest 1:
Sun Aug 09 16:50:09 2020
================================================================
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined (Doc ID 2103842.1)
In Data Guard Broker Configuraion, SWITCHOVER VERIFY Command via SQL*Plus Returns ORA-16475 (Doc ID 2679209.1)
SYMPTOMS:
Running a switchover using the broker, following warning is seen in the standby alert log.
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
Fri Dec 18 15:27:53 2015
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
The switchover is done successfully but still these warning appears in alert log. VALIDATE DATABASE using dgmgrl shows no issues.
In primary, log_archive_config shows dg_config=(SAPRD1,SAPRD2) but in the standby shows only dg_config=(SAPRD1).
When switchover is performed, these settings are reversed.
The same is true for the log_archive_dest_2. The primary shows an entry but the standby does not and after switchover the same is reversed.
SOLUTION:
This is just a warning because log_archive_dest_2 in not configured on standby database pointing to primary.
Setting of these parameter is done by broker. This is not an issue as far as our databases are in sync and switchover is happening without any issue.
This issue would be fixed in 12.2 release.
This warning can be ignored or fix for Bug 17229099 can be applied if available on my oracle support or backport can be requested.
DATAGUARD_BROKER has successfully configured
This article helps those who like to configure dataguard with
RMAN
And also to configure Broker service to a dataguard Setup
No comments:
Post a Comment