Standby - Create Clone

Author : Adarsh Kumar

 Clone or Create  Standby Database

Note :  In this doc  Standby Host/Remote Host/ Auxilary refers to the remote host you want to create additional copy of the primary database.
My environment

Primary Host : 172.18.53.172 / 173

Primary  DB Name=corem,  Instance_name =corem1 , corem2  (RAC , ASM) , db_unique_name=corem

primary Host ASM disk Groups +BACKUP, +DATA

Standby Host : 172.18.53.20
Standby  DB Name=corem,  Instance_name =corem1 ,  db_unique_name=coreb
Standby  Host ASM disk Grous   +DG_TIER1, +DG_TIER2  ,+DG_BACKUP

Goal is to create single instance corem1  on Standby Host with same directory/Disk location

Check List

Make Sure Standby Host has

-           Enough Space
-           Both box can ping each other [primary – Standby]

Primary

Alter Database Force Logging:

Primary & Remote Host must have TNS defied: Add This in Both HOSTS

corem_primary =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.53.172)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = corem1)
    )
  )

corem_standby =

  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.53.20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = corem1)
    )
  )

Check TNSPING  from both Servers

Primary> tnsping corem_primary
                  tnsping corem_standby

Standby > tnsping corem_primary
                  tnsping corem_standby
PWD File
Copy pwd file from Primary to Standby Host $ORACLE_HOME/dbs  & Rename it 

Init File

Copy pfile from the primary to Standby Location and Modify the parameters

File Name <initcorem1.ora>

DB_NAME=corem
DB_UNIQUE_NAME=coremb
Instance_name=corem1
cluster_database=false
diagnostic_dest=/apps/oracle
log_archive_config='dg_config=(corem,coremb)'
db_create_file_dest=+DG_TIER1
db_recovery_file_dest=+DG_BACKUP
log_file_name_convert=('+BACKUP','+DG_BACKUP','+DATA','+DG_TIER1')
db_recovery_file_dest_size=1024000M
Note : in my case my primary database logfile was in the Diskgroup "+BACKUP" which is not available in the
new enviroment , the new env disks are "+DG_BACKUP" and "+DG_TIER1"

--Control_files=’+COREVIN,+DG_ACFS'

Fix Listener.ora  in Standby Host

see 4 Lines Must be Added ow you will get Agent Block errors

SID_LIST_LISTENER =

  (SID_LIST =
    (SID_DESC =
       (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /apps/oracle/product/11.2.0/db)
       (PROGRAM = extproc)
     )
   (SID_DESC =
      (SID_NAME = corem1)
      (ORACLE_HOME = /apps/oracle/product/11.2.0/db)
    )
   )

Standby  Host  Create Directories & Start Instance in NOMOUNT
export ORACLE_SID=corem1
export ORACLE_HOME=<your Oracle Home>

a)       add following in /etc/oratab

corem1:/apps/oracle/product/11.2.0/db:N

b)       Create all directory for dump

c)        
SQL>  startup nomount pfile=$ORACLE_HOME/dbs/initcorem1.ora

ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                      2225064 bytes
Variable Size             159386712 bytes
Database Buffers      50331648  bytes
Redo Buffers                5214208  bytes

d) SQL>  create spfile from pfile=$ORACLE_HOME/dbs/initcorem1.ora

Stop & Start Listener In standby Host ( Note in mycase  Listener was in Grid Home)

srvctl stop listener;
srvctl start listener;
srvctl status listener;
lsnrctl services;

lsnrctl service must not show BLOCKING
Example
..
..
Service "dcfxm1" has 1 instance(s).
  Instance "corem1", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0
         LOCAL SERVER
...
Test Connectivity using TNS String from BOTH  Standby & Primary Nodes
Following  should pass from both nodes
Primary SQL> conn sys/password@corem_primary as sysdb
connected
Primary SQL> conn sys/password@corem_standby  as sysdba
connected

On Primary Database Run This ( You have 3  choices)

Choice 1: Duplicate from primary active datyabase without a backup & using SPFILE of primary Database
n  Following will use all values of the SPFILE from the primary Database
n  You can overwrite values to be used by Standby by giving set command ( see below)
n  If you want to control all attributes then do not use SPFILE clause
As Oracle#

rman target sys/password@corem_primary auxiliary sys/password@corem_standby
run {
allocate channel primary1 type disk;
allocate channel primary2 type disk;
allocate auxiliary channel auxilary1 type disk;
allocate auxiliary channel auxilary2 type disk;
DUPLICATE TARGET DATABASE TO corem FROM ACTIVE DATABASE  nofilenamecheck
SPFILE
  set cluster_database='false'
;
}
n  More convert example
SPFILE
 PARAMETER_VALUE_CONVERT '/disk1', '/disk2'
  SET LOG_FILE_NAME_CONVERT '/disk1','/disk2'
  SET CLUSTER_DATABASE='false'

Choice 2: Duplicate from primary active datyabase without a backup & using NEW SPFILE of  Standby  Database
As Oracle#
rman target sys/password@corem_primary auxiliary sys/password@corem_standby

run {
allocate channel primary1 type disk;
allocate channel primary2 type disk;
allocate auxiliary channel auxilary1 type disk;
allocate auxiliary channel auxilary2 type disk;
DUPLICATE TARGET DATABASE TO corem FROM ACTIVE DATABASE  nofilenamecheck
;
}

Choice 3: Using Backup from Primary and then Creating Standby & using NEW SPFILE of Standby Database

As Oracle#  Take backup on the PRIMARY Database Location

----------------------- Backup Script  -------------------------------- ---------------------  ----
oram101-corem1 /apps/oracle/myrmanbackup> cat rman.sh

export ORACLE_BASE=/home/oracle

export ORACLE_HOME=/apps/oracle/product/11.2.0/db
export ORACLE_SID=corem1
rman target sys/password@corem_primary nocatalog << EOFspool log to /apps/oracle/myrmanbackup/RMANBACKUP.log append;
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup as compressed backupset incremental level 0 format  '/apps/oracle/myrmanbackup/%U'  database plus archivelog;
backup current controlfile for standby format '/apps/oracle/myrmanbackup/%U';
backup spfile;
release channel c1;
release channel c2;
}
exit
EOF
--- --  --

Transfer this backup on the Standby Host Same Directory Structure Location

Now  On Standby Server Start the Database in the NOMOUNT mode

oram101-corem1 /home/oracle> echo $ORACLE_SID

corem1

oram101-corem1 /home/oracle> echo $ORACLE_HOME

/apps/oracle/product/11.2.0/db

For review here is the pfile  (/apps/oracle/product/11.2.0/db/dbs/initcorem1.ora)

DB_NAME=corem

DB_UNIQUE_NAME=coreb
cluster_database=false
Audit_file_dest=/apps/oracle
Background_dump_dest=/apps/oracle/diag/rdbms/corem/corem1/trace
Instance_name=corem1
log_archive_config='dg_config=(corem,coreb)'
#Control_files='+DG_ACFS'
LOG_FILE_NAME_CONVERT=('+DG_COREVIN','+DG_COREVIN')     ß  Very Imp ow you will get Error  RMAN-05535: WARNING: All redo log…
Fal_server='corem_primary'

SQL> startup nomount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area  217157632 bytes
Fixed Size                  2225064 bytes
Variable Size             159386712 bytes
Database Buffers           50331648 bytes
Redo Buffers                5214208 bytes

Now Create SPFILE 

SQL> create spfile from pfile;

Start Restore from STANDBY HOST  ( We are coooecting to primary but using Stored Backup on Standby HOST)

rman target sys/password@corem_primary auxiliary /  << EOF
run {
allocate auxiliary channel a1 type disk format '/backup/RMANBKUP/%U';
allocate auxiliary channel a2 type disk format '/backup/RMANBKUP/%U';
duplicate target database for standby dorecover  nofilenamecheck;
}
EOF

Now Configure LOG TRANSPORT between Primary and Standby (Data Guard but no Broker)

Steps to be done in Primary Node

Our primary database Unique Name : corem & TNS String:corem_primary

alter system set log_archive_config='dg_config=(corem,coreb)'  <--DB_UNIQUE_NAMES not TNS strings

alter system set LOG_ARCHIVE_DEST_2='SERVICE=corem_standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=coreb';

alter system set log_archive_dest_state_2 = 'enable' scope=both;

Standy side(DBUnique Name : corem_stby , TNS String:corem_standby

Now we need to add few parameter in the spfile and restart the mounted database in the managed recovery mode

Note : OPEN PFILE and MAKE sure you have correct CONTROLFILE name with path

If Not then ADD it and create SPFILE and Restart Standby Database in Mount mode with new SPFILE

Now add Standby Logs in the Standby Database ( Add Primary + 1 Logs )

select * from v$standby_log;

Example : Alter database add standby logfile group 01 '+COREVIN' size 512M;

Now Change couple More parameters in Standby

alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=spfile;
alter system set log_archive_config='dg_config=(corem,coreb)' scope=spfile; <--DB_UNIQUE_NAMES not TNS
alter system set fal_server='corem_primary'

shutdown immdiate

startup mount
alter database recover managed standby database using current logfile disconnect;

Troubleshoot

Primary
select * from v$archived_log;
select dest_name,status,type,archived_seq#,applied_seq#,applied_thread#,error,destination
from v$archive_dest_status
where destination is not null;

Standby

select * from v$managed_standby where process='MRP0';