• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Create a Physical Standby from ASM Primary

by admin

The Setup

This post offers step by step procedure to create physical standby from ASM primary.

Database Name: chicago 
Primary db_unique_name: chicago 
standby db_unique_name: boston
Primary Hostname: prim.example.com
standby Hostname: stdby.example.com

Steps

1. Enable Force Logging:

SQL> ALTER DATABASE FORCE LOGGING; 

Database altered.

2. On the primary node, create a staging directory. For example:

[oracle@prim ~]$ mkdir –p /home/oracle/stage

3. Create the same exact path on the standby host:

[oracle@stdby ~]$ mkdir -p /home/oracle/stage

4. On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory. For example:

sql> create pfile='/home/oracle/stage/boston.ora' from spfile;

5. On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory. For example:

RMAN> run{ 
2> backup device type disk format '/home/oracle/stage/%U' database; 
3> backup device type disk format '/home/oracle/stage/%U' current controlfile for standby; 
4> }

Starting backup at 24-FEB-09 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=30 devtype=DISK 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
input datafile fno=00001 name=+DATA/chicago/datafile/system.271.679675991 
input datafile fno=00003 name=+DATA/chicago/datafile/sysaux.273.679676023 
input datafile fno=00002 name=+DATA/chicago/datafile/undo1.272.679676015 
channel ORA_DISK_1: starting piece 1 at 24-FEB-09 
channel ORA_DISK_1: finished piece 1 at 24-FEB-09 
piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
including current control file in backupset 
including current SPFILE in backupset 
channel ORA_DISK_1: starting piece 1 at 24-FEB-09 
channel ORA_DISK_1: finished piece 1 at 24-FEB-09 
piece handle=/home/oracle/stage/02k8651v_1_1 tag=TAG20090224T154323 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 
Finished backup at 24-FEB-09 

Starting backup at 24-FEB-09 
using channel ORA_DISK_1 
channel ORA_DISK_1: starting full datafile backupset 
channel ORA_DISK_1: specifying datafile(s) in backupset 
including standby control file in backupset 
channel ORA_DISK_1: starting piece 1 at 24-FEB-09 
channel ORA_DISK_1: finished piece 1 at 24-FEB-09 
piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 
Finished backup at 24-FEB-09 

RMAN>

6. Copy the contents of the staging directory from primary to standby’s staging location. For example

[oracle@prim stage]$ scp /home/oracle/stage/* oracle@stdby.example.com:/home/oracle/stage/ 
oracle@stdby.example.com's password: 
01k8650r_1_1 100% 236MB 10.7MB/s 00:22 
02k8651v_1_1 100% 6016KB 5.9MB/s 00:00 
03k86525_1_1 100% 5984KB 5.8MB/s 00:01 
boston.ora 100% 458 0.5KB/s 00:00 
[oracle@raca prim]$

7. Prepare the initialization file for standby instance:

[oracle@stdby ~]$ mkdir -p /u01/app/oracle/product/10.2/oradata 
[oracle@stdby ~]$ cd /u01/app/oracle/product/10.2/oradata 
[oracle@stdby oradata]$ mkdir redo data ctrl bdump udump srl arc1 
[oracle@stdby oradata]$

Set the below parameters in init.ora of standby,

*.db_name='chicago'
*.db_create_file_dest='/u01/app/oracle/product/10.2/oradata/boston/data/'
*.db_create_online_log_dest_1='/u01/app/oracle/product/10.2/oradata/boston/redo/'
*.db_file_name_convert='+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/','/u01/app/oracle/product/10.2/oradata/boston/data/'
*.log_file_name_convert='+DATA/chicago/onlinelog/','/u01/app/oracle/product/10.2/oradata/boston/redo/'
*.db_unique_name=boston
NOTE: For RAC primary to non RAC standby, include below line in the standby init.ora,
cluster_database=false

8. Create password file for standby database:

[oracle@stdby boston]$ export ORACLE_SID=boston 
[oracle@stdby dbs]$orapwd file=orapwboston password=oracle 
[oracle@stdby dbs]$ ls -ltr *boston* 
-rw-r----- 1 oracle oinstall 1536 Mar 3 15:55 orapwboston

9. Compose a tnsnames or connect string at standby server:

chicago = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST =prim.example.com )(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = chicago) 
) 
) 

boston = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST =stdby.example.com )(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = boston) 
) 
)
Note: chicago will connect to primary while boston connects to standby itself.

10. Create standby database

[oracle@stdby boston]$ sqlplus / as sysdba 

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 3 16:55:19 2009 

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

Connected to an idle instance.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2/oradata/boston/initboston.ora'; 

File created.
SQL> startup nomount 
ORACLE instance started.

Total System Global Area 327155712 bytes 
Fixed Size 1218844 bytes 
Variable Size 150996708 bytes 
Database Buffers 104857600 bytes 
Redo Buffers 70082560 bytes 
SQL> exit 
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
With the Partitioning, OLAP and Data Mining options 
[oracle@stdby boston]$ /u01/app/oracle/product/10.2/bin/rman target sys/oracle@chicago auxiliary / 

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 3 16:56:09 2009 

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

connected to target database: CHICAGO (DBID=1289394690) 
connected to auxiliary database: CHICAGO (not mounted) 

RMAN> duplicate target database for standby; 

Starting Duplicate Db at 03-MAR-09 
using target database control file instead of recovery catalog 
allocated channel: ORA_AUX_DISK_1 
channel ORA_AUX_DISK_1: sid=36 devtype=DISK 

contents of Memory Script: 
{ 
restore clone standby controlfile; 
sql clone 'alter database mount standby database'; 
} 
executing Memory Script 

Starting restore at 03-MAR-09 
using channel ORA_AUX_DISK_1 

channel ORA_AUX_DISK_1: starting datafile backupset restore 
channel ORA_AUX_DISK_1: restoring control file 
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/03k86525_1_1 
channel ORA_AUX_DISK_1: restored backup piece 1 
piece handle=/home/oracle/stage/03k86525_1_1 tag=TAG20090224T154405 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 
output filename=/u01/app/oracle/product/10.2/oradata/boston/ctrl/ctrl01.ctl 
Finished restore at 03-MAR-09 

sql statement: alter database mount standby database 
released channel: ORA_AUX_DISK_1 

contents of Memory Script: 
{ 
set newname for tempfile 1 to 
"/u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043"; 
switch clone tempfile all; 
set newname for datafile 1 to 
"/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991"; 
set newname for datafile 2 to 
"/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015"; 
set newname for datafile 3 to 
"/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023"; 
restore 
check readonly 
clone database 
; 
} 
executing Memory Script 

executing command: SET NEWNAME 

renamed temporary file 1 to /u01/app/oracle/product/10.2/oradata/boston/data/tmp1.274.679676043 in control file 

executing command: SET NEWNAME 

executing command: SET NEWNAME 

executing command: SET NEWNAME 

Starting restore at 03-MAR-09 
allocated channel: ORA_AUX_DISK_1 
channel ORA_AUX_DISK_1: sid=36 devtype=DISK 

channel ORA_AUX_DISK_1: starting datafile backupset restore 
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set 
restoring datafile 00001 to /u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991 
restoring datafile 00002 to /u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015 
restoring datafile 00003 to /u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023 
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/stage/01k8650r_1_1 
channel ORA_AUX_DISK_1: restored backup piece 1 
piece handle=/home/oracle/stage/01k8650r_1_1 tag=TAG20090224T154323 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25 
Finished restore at 03-MAR-09 

contents of Memory Script: 
{ 
switch clone datafile all; 
} 
executing Memory Script 

datafile 1 switched to datafile copy 
input datafile copy recid=4 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/system.271.679675991 
datafile 2 switched to datafile copy 
input datafile copy recid=5 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/undo1.272.679676015 
datafile 3 switched to datafile copy 
input datafile copy recid=6 stamp=680547428 filename=/u01/app/oracle/product/10.2/oradata/boston/data/sysaux.273.679676023 
Finished Duplicate Db at 03-MAR-09 

RMAN>

11 Add standby redo logs to standby database

SQL> alter database add standby logfile group 3 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl3a.log' size 150m; 

Database altered.
SQL> alter database add standby logfile group 4 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl4a.log' size 150m; 

Database altered.
SQL> alter database add standby logfile group 5 '/u01/app/oracle/product/10.2/oradata/boston/srl/srl5a.log' size 150m; 

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

Database altered.

12. Establish the communication to primary from standby:

SQL> select name,database_role from v$database; 

NAME DATABASE_ROLE 
--------- ---------------- 
CHICAGO PHYSICAL STANDBY 
SQL> alter system set standby_archive_dest='/u01/app/oracle/product/10.2/oradata/boston/arc1/'; 

System altered. 

SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/10.2/oradata/boston/arc1/ valid_for=(all_logfiles,all_roles) db_unique_name=boston'; 


SQL> alter system set log_archive_config='dg_config=(chicago,boston)'; 

System altered. 

SQL> alter system set log_archive_dest_2='service=chicago lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=chicago'; 

System altered. 

SQL> alter system set fal_client=boston; 

System altered. 

SQL> alter system set fal_server=chicago; 

System altered. 

SQL> alter system set standby_file_management=auto; 

System altered.

13. Compose tnsnames or connect strings at Primary server

chicago = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST =prim.example.com )(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = chicago) 
) 
) 

boston = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST =stdby.example.com )(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = boston) 
) 
)
Note: Boston will connect to standby while chicago connects to primary itself.

From 11g we can use RMAN>DUPLICATE FROM ..ACTIVE 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;

14. Establish the communication to standby from primary

SQL> alter system set log_archive_config='dg_config=(chicago,boston)'; 

System altered. 

SQL> alter system set log_archive_dest_2='service=boston lgwr async noaffirm valid_for=(online_logfiles,primary_role) db_unique_name=boston'; 

System altered. 

SQL> alter system set fal_client=chicago; 

System altered. 

SQL> alter system set fal_server=boston; 

System altered. 

SQL> select name,database_role from v$database; 

NAME DATABASE_ROLE 
--------- ---------------- 
CHICAGO PRIMARY 

15. Set role transition specific parameters for current primary:

SQL> alter system set db_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/datafile/','/u01/app/oracle/product/10.2/oradata/boston/data/','+DATA/chicago/tempfile/' scope=spfile; 

System altered. 

SQL> alter system set log_file_name_convert='/u01/app/oracle/product/10.2/oradata/boston/redo/','+DATA/chicago/onlinelog/' scope=spfile; 

System altered. 

SQL> alter system set standby_file_management=auto; 

System altered.

16. Configure a Standby Redo Log,

SQL> alter database add standby logfile group 3 size 150m; 

Database altered. 

SQL>alter database add standby logfile group 4 size 150m 

Database altered. 

SQL>alter database add standby logfile group 5 size 150m 

Database altered.

NOTE: To check the number of SRL:

(maximum number of logfiles for each thread + 1) * maximum number of threads 

For example, if the primary database has 3 log files for each thread and 2 threads, then 8 standby redo log file groups are needed on the standby database. Verify the standby redo log file groups were created.

Filed Under: ASM, oracle, Oracle 10g, Oracle 11g, RMAN

Some more articles you might also be interested in …

  1. Oracle Database 12c New Feature – RMAN “RECOVER TABLE”
  2. Automatic Stop of Oracle Database (dbshut) not working in CentOS/RHEL 7 with systemd
  3. When to Use Startup/Shutdown Database and Alter Database Command in Oracle 12c
  4. CentOS / RHEL 7 : Oracleasm Create Disk Failed “Instantiating disk: failed”
  5. Oracle RAC Interview Questions – Coherence and Split-Brain
  6. SQL*Plus Editing Commands
  7. How to Verify if Oracle Active Data Guard is Enabled
  8. New Background Processes In Oracle Database 12c
  9. Beginners Guide to Flash Recovery Area in Oracle Database
  10. Step By Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN

You May Also Like

Primary Sidebar

Recent Posts

  • fprintd-delete Command Examples in Linux
  • fprintd-delete: command not found
  • foreman: command not found
  • foreman Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright