Create a Physical Standby using RMAN Active Duplicate
In this post I am going to share how to create an Active standby database using active duplicate RMAN command.
High Level steps1. Make the necessary changes to the primary database.
a. Enable force logging.
b. Creating the password file if one does not exist.
c. Create standby redologs.
d. Modify the parameter file suitable for Dataguard.
2. Ensure that the sql*net connectivity is working fine.
3. Create the standby database over the network using the active(primary) database files.
a. Create the password file
b. Create the initialization parameter file for the standby database (auxiliary database)
c. Create the necessary mount points or the folders for the database files
d. Run the standby creation ON STANDBY by connecting to primary as target database.
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
PARAMETER_VALUE_CONVERT '', ''
SET DB_FILE_NAME_CONVERT '', ''
SET LOG_FILE_NAME_CONVERT '', ''
SET SGA_MAX_SIZE 200M
SET SGA_TARGET 125M;
4. Check the log shipping and apply.
Detailed Steps
Primary database
Host: fundb.lab
DB_NAME = BEE
DB_UNIQUE_NAME=BEE
File system: Normal File System
OEL 6.9
Database Version 11.2.0.4
Standby database
Host: fundbstd.lab
DB_NAME = BEE
DB_UNIQUE_NAME=BEE_CL
File system: Normal File System
OEL 6.9
Database Version 11.2.0.4
Please do below steps on Primary Database
Ensure primary database is in archive log mode
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archlog/BEE
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>
Verify that forced logging is enabled and enable it if it not, please do enable.
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL>
Standby redo logs should be created in addition to the redo logs. The standby redo logs need to be as large as the largest redo log and there should be one extra group.
SQL> select group#, thread#, bytes/1024/1024
2 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 1024
2 1 1024
3 1 1024
SQL> alter database add standby logfile '/oradata/BEE/log1s01.log' size 1024M;
Database altered.
SQL> alter database add standby logfile '/oradata/BEE/log1s02.log' size 1024M;
Database altered.
SQL> alter database add standby logfile '/oradata/BEE/log1s03.log' size 1024M;
Database altered.
SQL> alter database add standby logfile '/oradata/BEE/log1s04.log' size 1024M;
Database altered.
SQL>
Validate from V$standby_log if it is added correctly
Initialization Parameters needed for Dataguard
The DB_NAME parameter will be BEE for the both the primary database and the standby database. The DB_UNIQUE_NAME will be BEE for the primary database and BEE_CL for the standby.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string BEE
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string BEE
SQL>
The DB_UNIQUE_NAME will be used in the LOG_ARCHIVE_CONFIG parameter to enable the sending and receiving of redo logs to remote destinations.
SQL> show parameter LOG_ARCHIVE_CONFIG
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
SQL> alter system set log_archive_config='DG_CONFIG=(BEE,BEE_CL)';
System altered.
SQL>
Set value for LOG_ARCHIVE_DEST_1 and LOG_ARCHIVE_DEST_2
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/archlog/BEE
SQL> alter system set log_archive_dest_1='LOCATION=/archlog/BEE/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEE';
System altered.
The redo transport mode is set in the parameter LOG_ARCHIVE_DEST_2. Below we configure redo transport mode for Maximum performance by setting ASYNC NOAFFIRM. Instead of a location a service name is provided to send the archive logs.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ -----------
log_archive_dest_2 string
SQL> alter system set log_archive_dest_2='SERVICE=BEE_CL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BEE_CL';
System altered.
The FAL_SERVER specifies the fetch archive log (FAL) server for the standby database. The FAL_CLIENT specifies the FAL client name. Both are used by the FAL service. The FAL_SERVER is the primary and FAL_CLIENT is the standby.
SQL> alter system set fal_server=BEE;
System altered.
SQL> alter system set fal_client=BEE_CL;
System altered.
Operating system file additions and deletions can be replicated to the physical standby database setting STANDBY_FILE_MANAGEMENT to auto.
SQL> alter system set standby_file_management=auto;
System altered.
Remote Login
SQL> alter system set remote_loging_passwordfile=exclusive;
System altered.
Network Connectivity
The primary database should be statically registered with its listener.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = BEE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
Add TNS Names Entries for both BEE and BEE_CL databases on both servers
[orabee@fundb dbs]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
BEE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fundb.lab)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BEE)
)
)
BEE_CL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fundbstd.lab)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BEE)
)
)
[orabee@fundb dbs]$
Standby Server Setup
[orabee@fundb ~]$ mkdir /oradata
[orabee@fundb ~]$ mkdir /oradata/BEE
[orabee@fundb ~]$ mkdir /oradata/BEE/arch
[orabee@fundb ~]$ mkdir /u01/app/oracle/admin
[orabee@fundb ~]$ mkdir /u01/app/oracle/admin/BEE
Add the following line to /etc/oratab on the standby server.
BEE:/u01/app/oracle/product/11.2.0/dbhome_1:N
Create the listener and statically register the standby database.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = BEE)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fundbstd.lab)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[orabee@fundb ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-NOV-2022 18:10:54
Copyright (c) 1991, 2009, 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.4.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/diag/tnslsnr/ocm2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fundbstd.lab)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fundbstd.lab)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 11-NOV-2022 18:10:54
Uptime 0 days 0 hr. 0 min. 10 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/diag/tnslsnr/ocm2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fundbstd.lab)(PORT=1521)))
Services Summary...
Service "BEE" has 1 instance(s).
Instance "BEE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Also insure that the TNS entries for both the primary and standby are in the ORACLE_HOME/network/admin/tnsnames.ora file.
BEE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fundb.lab)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BEE)
)
)
BEE_CL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = fundbstd.lab)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BEE)
)
)
Create a basic PFILE in ORACLE_HOME/dbs with the following parameters.
[orabee@fundb dbs]$ cat initBEE.ora
DB_NAME=BEE
DB_UNIQUE_NAME=BEE_CL
The rest of the parameters will be set in the duplicate command and carried over from the primary.
Copy the password file from the primary server to the standby server
[orabee@fundb ~]$ scp orabee@fundb:$ORACLE_HOME/dbs/orapwBEE $ORACLE_HOME/dbs
With the network configuration made and the PFILE and password file in place the standby instance can now be started in nomount.
[orabee@fundb ~]$ sqlplus / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL> exit
Make sure you exit after starting the database in nomount. The duplicate procedure will restart the database and an open connection can cause the process to fail.
Duplicate for standby
FOR STANDBY – the duplicate is for use as a standby so a DBID change will not be forced.
FROM ACTIVE DATABASE – instructs RMAN to use the active target database instead of disk based backups.
DORECOVER – do recovery bringing the standby database up to the current point in time.
SPFILE – values for parameters specific to the auxiliary instance can be set here.
NOFILENAMECHECK – this option is added because the duplicate database files uses the same name as the source database.
Create command file and save.
[orabee@fundb ~]$ cat duplicate_rman.cmd
run {
allocate channel pri1 type disk;
allocate channel pri2 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='BEE_CL'
set control_files='/oradata/BEE/control01.ctl','/oradata/BEE/control02.ctl'
set fal_client='BEE'
set fal_server='BEE_CL'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(BEE,BEE_CL)'
set log_archive_dest_1='LOCATION=/oradata/BEE/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEE_CL'
set log_archive_dest_2='service=BEE ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=BEE'
nofilenamecheck;
}
[orabee@fundb ~]$
On standby connect to both the target (BEE) and the auxiliary (BEE_CL) in RMAN.
[orabee@fundb ~]$ rman target sys/password@BEE auxiliary sys/password@BEE_CL
connected to target database: BEE (DBID=3720300117)
connected to auxiliary database: BEE (not mounted)
RMAN> @duplicate_rman.cmd
This will take time based on DB size and the network speed so I prefer to run in VNC.
On the standby start the managed recovery process.
[orabee@fundb ~]$ sqlplus / as sysdba
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> exit
So duplicate is complete with the dataguard environment.
Validate that that logs are shipping and applying. On the standby issue the following query.
SQL> select sequence#, first_time, next_time, applied
from v$archived_log
order by sequence#;
On the primary database switch the logs a few times and use archive log list to information on the oldest, current and next log sequence.
[orabee@fundb ~]$ . oraenv
ORACLE_SID = [oracle] ? BEE
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
[orabee@fundb ~]$ sqlplus / as sysdba
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/BEE/arch/
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
SQL>
We would see new archives being applied on standby.
Post a Comment
Post a Comment