Step By Step Guide On Creating Physical Standby Using RMAN(ASM)

Step By Step Guide On Creating Physical Standby Using RMAN(ASM)

This post contains a quick, step-by-step walk over the procedure of creating a Physical Standby Database using Rman Duplicate command, not using any backup.
Setting up a physical standby database is a simple operation when the required infrastructure is ready.
We need 2 servers, a network that communicate both of them, and storage connected to the servers that is proportional to the database size + extra space for archive logs and backups.

Primary database Name:proddev1

Standby database Name:standbydev1

1)Make sure primary database is running in archive log mode.
SQL> archive log list

2)Determine if FORCE LOGGING is enabled. If it is not enabled, enable FORCE LOGGING mode.
SQL> SELECT force_logging FROM v$database;

FOR
—
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT force_logging FROM v$database;

FOR
—
YES

3)Configure the primary database to receive redo data, by adding the standby logfiles to the primary.
It is highly recommended that you have one more standby redo log group than you have online redo log groups as the primary database.
The files must be the same size or larger than the primary database’s online redo logs.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘+DATA1/proddev1/stdyrl01.log’ SIZE 52428800m;

SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘+DATA1/proddev1/stdyrl02.log’ SIZE 52428800m;

SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘+DATA1/proddev1/stdyrl03.log’ SIZE 52428800m;

SQL> ALTER DATABASE ADD STANDBY LOGFILE ‘+DATA1/proddev1/stdyrl04.log’ SIZE 52428800m;

4)Set primary database initialization parameters

SQL> ALTER SYSTEM SET log_archive_config = ‘dg_config=(proddev1,standbydev1)’;

SQL> ALTER SYSTEM SET log_archive_dest_2 =‘service=stbydv1 async valid_for=(online_logfile,primary_role) db_unique_name=standbydev1';

SQL> show parameter log_archive_config

SQL> show parameter log_archive_dest_2

****If you get following error ********************************************************
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

Solution :

1. Reset the LOG_ARCHIVE_DEST to NULL value :

SQL> ALTER SYSTEM set log_archive_dest=”;

2. Set the LOG_ARCHIVE_DEST_1 to the value set to LOG_ARCHIVE_DEST before

SQL> ALTER SYSTEM set log_archive_dest_1=’LOCATION=/…’;

if you use this code, you dont need to restart database:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=h:\oradata\archive’ SCOPE=BOTH;

To check archival destination issue is ok:
SQL> show parameter LOG_ARCHIVE_DEST;
SQL> select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;
*********************************************************************************
update tnsnames.ora file with the following entries on both hosts:

PRODDEV1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba001.dba.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddev1.dba.com)
)
)

STANDBYDEV1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba001.dba.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standbydev1.dba.com)
)
)
update the listner.ora with the following values on standby database:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby1.example.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = standby1)
)
)

copy the remote login password file (orapwproddev1) from the primary database system to the $ORACLE_HOME/dbs directory on the standby database system, renaming it to orapwstandbydev1.

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstandbydev1.ora
containing a single parameter: DB_NAME=standbydev1(i.e. standbydev1)

On the standby system, change to the /u01/app/oracle/admin directory. Create a directory with a name that matches your physical standby SID (i.e. standbydev1).
Change to the newly created directory (i.e. standbydev1) and create an adump directory.

now on the standby system log into database
export ORACLE_SID=standbydev1
dba001:/u01/app/oracle/admin/standbydev1>export ORACLE_SID=standbydev1
dba001:/u01/app/oracle/admin/standbydev1>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 14 22:57:24 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=$ORACLE_HOME/dbs/initstandbydev1.ora
ORACLE instance started.

Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes

RMAN> connect target sys

target database Password:
connected to target database: PRODDVE1 (DBID=1462743077)

RMAN> connect auxiliary sys@standbydev1

auxiliary database Password:
connected to auxiliary database: STANDBYDEV1 (not mounted)

Below is the rman script to create standby database:
run {
allocate channel primy1 type disk;
allocate channel primy2 type disk;
allocate channel primy3 type disk;
allocate channel primy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘proddev1',’standbydev1'
set db_unique_name=’standbydev1'
set db_file_name_convert=’/proddev1/’,’/standbydev1/’
set log_file_name_convert=’/proddev1/’,’/standbydev1/’
set control_files=’+DATA1'
set log_archive_max_processes=’5'
set fal_client=’standbydev1'
set fal_server=’proddev1'
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(proddev1,standbydev1)’
set log_archive_dest_2=’service=proddev1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=proddev1'
;
}

channel primy3: SID=55 device type=DISK

allocated channel: primy4
channel primy4: SID=56 device type=DISK

allocated channel: standby
channel standby: SID=19 device type=DISK

Starting Duplicate Db at 14-APR-12

contents of Memory Script:
{
backup as copy reuse
.
.
.
.
.
.

input datafile copy RECID=4 STAMP=780621158 file name=+DATA1/standbydev1/datafile/users.260.780621011
Finished Duplicate Db at 14-APR-12
released channel: primy1
released channel: primy2
released channel: primy3
released channel: primy4
released channel: standby

RMAN> **end-of-file**

Perform a log switch on the primary database and redo will start being sent to the standby.
dba001:/u01/app/oracle/wkdir>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 14 23:14:57 2012

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system switch logfile;

System altered.

dba001:/u01/app/oracle/product/11.2.0.3/db_1/network/admin>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 14 23:15:50 2012

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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

Now Verify that the Physical Standby Database is Performing Correctly

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#

SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
———- ——— ——— ———
31 4-APR-16 4-APR-16 YES
32 4-APR-16 4-APR-16 YES
33 4-APR-16 4-APR-16 YES
34 4-APR-16 4-APR-16 YES
35 4-APR-16 4-APR-16 YES
36 4-APR-16 4-APR-16 IN-MEMORY

6 rows selected.

Thanks comments.