Overview:
In this article we explain the process of build a physical standby database using RMAN active duplicate.
Standby database is exact copy of production in read only mode with apply. In case if the production goes down for any outage. Standby can failover and make as primary.
Environment Details
Standby database is exact copy of production in read only mode with apply. In case if the production goes down for any outage. Standby can failover and make as primary.
Environment Details
Hostname
|
Primary server :–Primary
Standby Server :– Standby
|
Operating system
|
Linux
|
Database Environment
|
NSMPRD and NSMDR
|
NSMPRD@PRIMARY
Determine
if the FORCE LOGGIN is enabled.
SQL> select
force_logging from v$database;
For
------
YES
If the output of the query is YES, then proceed with the
next step. If the output of the above query is NO then enable the FORCE LOGGING
at the database level.
SQL> alter database force logging;
Set
the primary database initialization parameters
SQL> SHOW
PARAMETER DB_NAME
NAME TYPE VALUE
----------------------------------------------------------------
db_name string nsmprd
SQL> ALTER
SYSTEM SET log_archive_config='dg_config=(NSMPRD,NSMDR)';
System altered
SQL> show
parameter log_archive_config
NAME TYPE VALUE
----------------------------------------------------------------
log_archive_config
string 'dg_config=(NSMPRD,NSMDR)
Set the archive loctions on primary
SQL> alter
system set log_archive_dest_1='LOCATION=/u01/oraarch1/nsmprd
valid_for=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nsmprd REOPEN=30
MAX_FAILURE=3 scope=both;
SYSTEM ALTERED
SQL> alter
system set log_archive_dest_2='service="nsmdr"','LGWR SYNC AFFIRM
delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=10
db_unique_name="nsmdr" net_timeout=30','template="/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC"
valid_for=(all_logfiles,primary_role)' scope=both;
System altered
SQL> alter
system set log_archive_dest_state_3='ENABLE';
System altered
SQL> alter
system fal_client= NSMPRD;
System altered
SQL> alter
system fal_server= NSMDR;
System altered
SQL> Show
parameter fal
NAME TYPE VALUE
----------------------------------------------------------------
fal_client string NSMPRD
fal_server sting NSMDR
SQL> ALTER
SYSTEM SET log_archive_dest_state_2=defer;
System altered
These parameters are dynamic and will take effect
immediately. Check the parameter to make sure it points to the correct
locations as specified.
SQL> SHOW
PARAMETER LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
----------------------------------------------------------------
LOG_ARCHIVE_DEST_2 string service="nsmdr"','LGWR
SYNC AFFIRM
delay=0
Optional
compression=disable
max_failure=0
max_connections=1
reopen=10 db_unique_name="nsmdr"
net_timeout=30','template="/u02/oraarch2/nsmdr_standby/LOG_%r_%s_%t.ARC"
valid_for=(all_logfiles,primary_role)'
SQL> SHOW
PARAMETER REMOTE_LOGIN_PASSWORDFILE (
this must be set to “EXCLUSIVE” (default))
NAME TYPE VALUE
----------------------------------------------------------------
REMOTE_LOGIN_PASSWORDFILE string EXCLUSIVE
Create
standby redo log files on the primary database.
SQL>column
member format a50
SQL>SELECT
a.group#, a.member, b.bytes/1024/1024 bytes_MB
FROM v$logfile a,
v$log b WHERE a.group# = b.group#
Union select
a.group#, a.member, b.bytes/1024/1024 bytes_MB
from v$logfile a,
v$standby_log b WHERE a.group# =
b.group#;
GROUP# MEMBER
BYTES_MB
----------------------------------------------------------------
1 /u01/oradata1/nsmprd/nsmprd_redo01.log 50MB
2 /u01/oradata1/nsmprd/nsmprd_redo02.log 50MB
3 /u01/oradata1/nsmprd/nsmprd_redo03.log 50MB
4 /u01/oradata1/nsmprd/nsmprd_redo04.log 50MB
SQL>ALTER DATABASE
ADD STANDBY LOGFILE GROUP 5 '/u01/oradata1/nsmprd/nsmprd_sb_redo05.log'
SIZE 50M;
Database altered
SQL> ALTER DATABASE
ADD STANDBY LOGFILE GROUP 6 '/u01/oradata1/nsmprd/nsmprd_sb_redo06.log'
SIZE 50M;
Database altered
SQL> ALTER DATABASE
ADD STANDBY LOGFILE GROUP 7 '/u01/oradata1/nsmprd/nsmprd_sb_redo07.log'
SIZE 50M;
Database altered
SQL> ALTER DATABASE
ADD STANDBY LOGFILE GROUP 8 '/u01/oradata1/nsmprd/nsmprd_sb_redo08.log'
SIZE 50M;
SQL> ALTER DATABASE
ADD STANDBY LOGFILE GROUP 9 '/u01/oradata1/nsmprd/nsmprd_sb_redo09.log'
SIZE 50M;
Database altered
SQL>column
member format a50
SQL>SELECT
a.group#, a.member, b.bytes/1024/1024 bytes_MB
FROM v$logfile a,
v$log b WHERE a.group# = b.group#
Union select
a.group#, a.member, b.bytes/1024/1024 bytes_MB
from v$logfile a,
v$standby_log b WHERE a.group# =
b.group#;
GROUP# MEMBER
BYTES_MB
----------------------------------------------------------------
1 /u01/oradata1/nsmprd/nsmprd_redo01.log 50MB
2 /u01/oradata1/nsmprd/nsmprd_redo02.log 50MB
3 /u01/oradata1/nsmprd/nsmprd_redo03.log 50MB
4 /u01/oradata1/nsmprd/nsmprd_redo04.log 50MB
5 /u01/oradata1/nsmprd/nsmprd_sb_redo05.log 50MB
6 /u01/oradata1/nsmprd/nsmprd_sb_redo06.log 50MB
7 /u01/oradata1/nsmprd/nsmprd_sb_redo07.log 50MB
8 /u01/oradata1/nsmprd/nsmprd_sb_redo08.log 50MB
9 /u01/oradata1/nsmprd/nsmprd_sb_redo09.log 50MB
Verify the standby redo log file groups were created
(do this after the creation of standby database)
SQL> SELECT
GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP#
THREAD# SEQUENCE# ARC STATUS
----------
---------- ---------- --- ----------
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
8 0 0 YES UNASSIGNED
9 0 0 YES UNASSIGNED
Configuring TNS for primary and physical standby
database:
NSMPRD@PRIMARY
NSMDR =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT
= 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= nsmdr)
)
)
NSMDR@STANDBY
NSMPRD =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.1.1 )(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= nsmprd)
)
)
NSMDR =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME
= nsmdr)
)
)
LISTENER_NSMDR=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.2)(PORT=1521))
Creating the physical standby database:
NSMDR@STANDBY
Configure
listener entry for the standby database in the Listener.ora file on standby
server.
$cat listener.ora
SID_LIST_LISTENER
=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=nsmdr)
(ORACLE_HOME=
/u01/app/oracle/product/11.2.0.4/db)
(SID_NAME=nsmdr))
)
$tnsping nsmprd
TNS Ping Utility
for Linux: Version 11.2.0.4.0 - Production on 05-FEB-2017 09:42:43
Copyright (c)
1997, 2013, Oracle. All rights reserved.
Used parameter
files:
/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora
Used TNSNAMES
adapter to resolve the alias
Attempting to
contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT =
1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmprd)))
OK (0 msec)
$tnsping nsmdr
TNS Ping Utility
for Linux: Version 11.2.0.4.0 - Production on 05-FEB-2017 09:42:51
Copyright (c)
1997, 2013, Oracle. All rights reserved.
Used parameter
files:
/u01/app/oracle/product/11.2.0.4/db/network/admin/sqlnet.ora
Used TNSNAMES
adapter to resolve the alias
Attempting to
contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT =
1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nsmdr)))
OK (0 msec)
NSMPRD@PRIMARY
create password file on Primary
$orapwd file=orapwnsmprd password=****** force=y entries=20
Copy
the password file from the primary database server to the standby server.
$scp $ORACLE_HOME/dbs/ orapwnsmprd oracle@STANDBY: $ORACLE_HOME/dbs
In the
$ORACLE_HOME/dbs directory of the standby database server create an initialization
parameter file named initnsmdr.ora containing only one parameter DB_NAME.
$cd
$ORACLE_HOME/dbs
$vi initnsmdr.ora
DB_NAME= NSMPRD
:wq (save and
exit the file)
On the standby database server go to ORACLE_BASE/admin
and create a directory as your DB_UNIQUE_NAME.
$ cd
$ORACLE_BASE/admin
$mkdir NSMDR
On the standby server se the ORACLE_SID for the
standby database.
$vi /etc/oratab -->file and entry as below
nsmdr:/u01/app/oracle/product/11.2.0.4/db:N
$ . oraenv
ORACLE_SID = [oracle]
? nsmdr
The Oracle base
remains unchanged with value /u01/app/oracle
$ echo
$ORACLE_SID=nsmdr
nsmdr
Connect
as sysdba and start the database in NOMOUNT state.
SQL> STARTUP
NOMOUNT PFILE=’/u01/app/oracle/product/11.2.0.4/db/dbs/ initnsmdr.ora’;
Total System
Global Area 225509376 bytes
Fixed Size 2252896 bytes
Variable
Size 167773064 bytes
Redo Buffers
5152768 bytes
SQL> EXIT
NSMPRD@PRIMARY
On the
primary database server set the ORACLE_SID for primary database.
$ echo
$ORACLE_SID
nsmprd
On the
primary database server CHECK RMAN CONNECTION and connect to the primary
database and auxiliary database as sys.
$ rman
RMAN>connect
target sys
target database
Password:
connected to
target database : NSMPRD (DBID=139468621)
RMAN>connect
auxiliary sys@nsmdr ( Make sure listener
is running on standby)
auxiliary database
Password:
connected to auxiliary
database: NSMDR(not mounted)
RMAN>
RMAN>duplicate target database for standby from active
database
spfile
SET audit_file_dest=' /u01/oraadmin1/nsmdr/adump’
SET DB_UNIQUE_NAME 'NSMDR'
SET log_archive_dest_2 ''
SET log_archive_dest_3 ''
SET dg_broker_config_file1 ''
SET dg_broker_config_file2 ''
SET dg_broker_start 'FALSE'
nofilenamecheck;
After DR restore please set the standby parameters.
NSMDR@STANDBY
SQL> ALTER
SYSTEM SET log_archive_config='dg_config=(NSMPRD,NSMDR)';
System altered
SQL> ALTER
SYSTEM SET log_archive_dest_state_2=defer;
System altered
ALTER SYSTEM SET
log_archive_dest_2='service="nsmprd"','LGWR ASYNC NOAFFIRM delay=0
optional compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name="nsmprd" net_timeout=30','template="/u01/oraarch2/nsmprd_standby/LOG_%r_%s_%t.ARC"
valid_for=(all_logfiles,primary_role)' scope=both;
System altered
SQL> alter
system set fal_client= NSMDR;
System altered
SQL> alter
system set fal_server= NSMPRD;
System altered
Enable
the log_archive_dest_state_2 on primary
SQL> ALTER SYSTEM SET
log_archive_dest_state_2=ENABLE;
System altered
Perform
a log switch on the primary database and redo will start being sent to the
standby.
SQL> ALTER
SYSTEM SWITCH LOGFILE;
System altered
SQL>/
System altered
On the
standby database set the database SID and start the manage recovery process.
SQL> ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered
Verify that the standby database is performing correctly:
On the primary database switch the logfile.
SQL> ALTER
SYSTEM SWITCH LOGFILE;
System altered
Check the log sequence on the primary database.
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM
V$LOG_HISTORY GROUP BY THREAD#;
THREAD# MAX(SEQUENCE#)
------------------------
1
199
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM
V$LOG_HISTORY GROUP BY THREAD#;
THREAD# MAX(SEQUENCE#)
------------------------
1
198
SQL> select PROCESS,THREAD#,SEQUENCE#,STATUS
from v$managed_standby where process='MRP0';
PROCESS THREAD# SEQUENCE# STATUS
---------------------------
---------- ---------- ------------------------------------
MRP0 1 199 WAIT_FOR_LOG
No comments:
Post a Comment