Part 34 of series - Create standby database 11gR2 from 3 node RAC primary to 2 node RAC standby

He sleeps, intoxicated by corruption and Maya; he does not come to realize or understand. Seizing him by the hair, the Messenger of Death pulls him up; then, he comes to his senses. 1 Those who are attached to the poison of greed and sin grab at the wealth of others; they only bring pain on themselves. They are intoxicated by their pride in those things which shall be destroyed in an instant; those demons do not understand. 1 Pause The Vedas, the Shaastras and the holy men proclaim it, but the deaf do not hear it. When the game of life is over, and he has lost, and he breathes his last, then the fool regrets and repents in his mind. 2 He paid the fine, but it is in vain -- in the Court of the Lord, his account is not credited. Those deeds which would have covered him -- those deeds, he has not done. 3 The Guru has shown me the world to be thus; I sing the Kirtan of the Praises of the One Lord. Renouncing his pride in strength and cleverness, Nanak has come to the Lord's Sanctuary.





Index of all the posts of Gurpartap Singh's Blog





Creating a Physical Standby

The following names will be used when building our configuration:

Primary Site:

rac1 is hostname ,simar database name and is Primary Node 1 and instance name is simar1 and tns alias is simar1
rac2 is hostname ,simar database name and is Primary Node 2 and instance name is simar2 and tns alias is simar2
rac3 is hostname ,simar database name and is Primary Node 3 and instance name is simar3 and tns alias is simar3

Service simarsv1 is configured to run on node 1.
ASM diskgroups are: +DATA1 and +RECV1 having luns in san named "san".



Standby Site:

dgrac1 is hostname,simar database name and is Primary Node 1 and instance name is simarh1
dgrac2 is hostname,simar database name and is Primary Node 2 and instance name is simarh2

simarh is the default service that will be used to transfer the logs to the standby site from the primay site.
ASM diskgroups are: +DATADG1 and +RECVDG1 having luns in san named "san1".

Though you can let RMAN create the standby database and all the directory structures for you but, I don't like that and want to have
control on the process and will create the standby database myself. That will create all the directory structures required and then
will delelte all the datafiles, redolog files and controlfiles. We will be left with just spfile and the structure of the database
athat will be regeistered with the cluster as well.
So, you can create a new SAN by looking at post-3, this time name this san as "san1". Now create luns on it and then install grid
and database binaries on it. Then create a database simarh on this infrastructure by looking at posts 4 to 7. Just note to
keep the sys password same while creating the database OR you will have to modify the password file later.
Now that the database is ready. Take its backup if you want. Then stop the database and delete all the datafiles of this new database
simarh and then all the redolog files and all the controlfiles.

Now lets do some of the checks as:
1. Enable archiving on primary database if its not already enabled.
2. Create a password file (in our case its already there) as dbca created it for us and keep sys password same on both the sites and
set remote_login_passwordfile=exclusive on both the sites.
3. Enable force logging on primary(It's optional but I think it should be used).
alter database force logging;

Output on primary is as:
SQL> alter database force logging;
Database altered.
SQL>


4. Create standby redo logs on primay site so that after rman duplicate they exist on standby site as well.
Some of the protection modes like maximum protection and maximum availability, require standby redo logs to be present.
The size of the standby redo logs must match exactly with the size of online redo logs. We should have one more standby
redo log file group than the number of online redo log file groups on the primary database.
The following syntax is used to create the standby redo logs:
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 1 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 2 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;
alter database add standby logfile thread 3 ('+RECV1') SIZE 50M;

I will create 21 standby redologs on primary with 6 logs per thread.
5. Configure the primary database initialization parameters.
After considering the future role transitions we should configure the parameters to control log transport services and apply
services. If we do that the database will seamlessly work in any role without any parameter modifications. When the database
is running on primay controlfile , standby parameters are not read by it and will not effect the database operation when it will
be in primary role.
Place the following parameters in the primary database parameter file.
######## Primary Role Parameters ############
LOG_ARCHIVE_DEST_1=
'LOCATION=+RECV1
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=simar'
LOG_ARCHIVE_DEST_2=
'SERVICE=simarh
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=simarh'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=DEFER
DB_UNIQUE_NAME=simar
SERVICE_NAMES=simar
LOG_ARCHIVE_CONFIG='DG_CONFIG=(simar,simarh)'

## Standby Role Parameters ##
DB_FILE_NAME_CONVERT=
('+DATADG1','+DATA')
LOG_FILE_NAME_CONVERT=
('+RECVDG1','+RECV')
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=simarh ## Tnsentry_name
FAL_CLIENT=simar ## Tnsentry_name
Paramters DB_FILE_NAME_CONVERT and the LOG_FILE_NAME_CONVERT are required only if the paths of datafiles and logfiles are not identical between
the primary and the standby host.
For now we will keep LOG_ARCHIVE_DEST_2 as 'DEFER' but later once the standby database is created and mounted we will change it
to 'ENABLE'.

After all the modifications my pfile on standby looks like:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 29 22:54:06 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create pfile='/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/temp_simar1.ora' from spfile='+DATA1/simar/spfilesimar.ora';
File created.
SQL> !cat /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/temp_simar1.ora
simar3.__db_cache_size=369098752
simar2.__db_cache_size=440401920
simar1.__db_cache_size=377487360
simar3.__java_pool_size=4194304
simar2.__java_pool_size=4194304
simar1.__java_pool_size=4194304
simar3.__large_pool_size=4194304
simar2.__large_pool_size=8388608
simar1.__large_pool_size=8388608
simar1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
simar3.__pga_aggregate_target=226492416
simar2.__pga_aggregate_target=226492416
simar1.__pga_aggregate_target=226492416
simar3.__sga_target=905969664
simar2.__sga_target=905969664
simar1.__sga_target=905969664
simar3.__shared_io_pool_size=0
simar2.__shared_io_pool_size=0
simar1.__shared_io_pool_size=0
simar3.__shared_pool_size=515899392
simar2.__shared_pool_size=440401920
simar1.__shared_pool_size=503316480
simar3.__streams_pool_size=0
simar2.__streams_pool_size=0
simar1.__streams_pool_size=0
*.aq_tm_processes=2
*.audit_file_dest='/u01/app/oracle/admin/simar/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+RECV1/simar/controlfile/current.264.734044335'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_create_online_log_dest_1='+RECV1'
*.db_domain=''
*.db_name='simar'
*.DB_UNIQUE_NAME='simar'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=simarXDB)'
simar2.instance_number=2
simar3.instance_number=3
simar1.instance_number=1
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(simar,simarh)'
*.log_archive_dest_1='LOCATION=+RECV1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=simar'
*.log_archive_dest_2='SERVICE=simarh LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=simarh'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=225443840
*.processes=150
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_listener='scan.rac.meditate.com:1521'
*.remote_login_passwordfile='exclusive'
simar1.service_names='SIMARSV1','SIMAR'
*.sga_max_size=905969664
*.sga_target=905969664
simar2.thread=2
simar3.thread=3
simar1.thread=1
simar1.undo_tablespace='UNDOTBS1'
simar3.undo_tablespace='UNDOTBS3'
simar2.undo_tablespace='UNDOTBS2'
SQL>


6. Create a standby controlfile on the primary database as:
backup current controlfile for standby ;
Output is as:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs
$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 29 22:56:32 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: SIMAR (DBID=668825007)
RMAN> backup current controlfile for standby ;
Starting backup at 29-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=simar1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-JUN-11
channel ORA_DISK_1: finished piece 1 at 29-JUN-11
piece handle=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/15mg4ppm_1_1 tag=TAG20110629T225654 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 29-JUN-11
Starting Control File and SPFILE Autobackup at 29-JUN-11
piece handle=/home/oracle/backup/c-668825007-20110629-01 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUN-11
RMAN> exit

Recovery Manager complete.
oracle : rac1.rac.meditate.com : @simar1 : /u02/app/11.2.0.1/grid/network/admin
$

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
7. Modify the initialization parameter parameter file on the standby site as following:

Create a pfile from spfile on ASM on the standby site and add the following parameters to that pfile for use.
create pfile= '/database/10gDR/backup/initNashville.ora' from spfile;
Once you have a pfile created for your standby, you will need to adjust several parameters. Below are parameters that needed to
be modified in our configuration:
control_files='+DATADG','+RECVDG'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(simarh,simar)'
LOG_ARCHIVE_DEST_1=
'LOCATION=+RECVDG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=simarh'
LOG_ARCHIVE_DEST_2=
'SERVICE=simarsv1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=simar'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
DB_UNIQUE_NAME=simarh
SERVICE_NAMES=simarh

## Standby Role Parameters ##
DB_FILE_NAME_CONVERT=
('+DATA','+DATADG1')
LOG_FILE_NAME_CONVERT=
('+RECV','+RECVDG1')
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=simar ## Tnsentry_name
FAL_CLIENT=simarh ## Tnsentry_name


My spfile on standby site looks like:
oracle : dgrac01.rac.meditate.com : @simarh1 : /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 29 23:16:21 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create pfile='/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/simarh_temp2.ora' from SPFILE='+DATADG1/simarh/spfilesimarh.ora';
File created.
SQL> !cat /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/simarh_temp2.ora
simarh.__db_cache_size=318767104
simarh2.__db_cache_size=247463936
simarh1.__db_cache_size=213909504
simarh2.__java_pool_size=4194304
simarh1.__java_pool_size=4194304
simarh.__large_pool_size=4194304
simarh2.__large_pool_size=4194304
simarh1.__large_pool_size=4194304
simarh.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
simarh1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
simarh2.__pga_aggregate_target=159383552
simarh1.__pga_aggregate_target=159383552
simarh2.__sga_target=473956352
simarh1.__sga_target=473956352
simarh2.__shared_io_pool_size=0
simarh1.__shared_io_pool_size=0
simarh2.__shared_pool_size=209715200
simarh1.__shared_pool_size=243269632
simarh2.__streams_pool_size=0
simarh1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/simarh/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATADG1/simarh/controlfile/current.263.755135005','+RECVDG1/simarh/controlfile/current.272.755135005'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATADG1'
*.db_domain='rac.meditate.com'
*.db_file_name_convert='+RECV','+RECVDG1'
*.db_name='simar'
*.db_recovery_file_dest='+RECVDG1'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='SIMARH'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=simarhXDB)'
*.fal_client='SIMARH'
*.fal_server='SIMARSV1'
simarh1.instance_number=1
simarh2.instance_number=2
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(simarh,simar)'
*.LOG_ARCHIVE_DEST_1='LOCATION=+RECVDG1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=simarh'
*.LOG_ARCHIVE_DEST_2='SERVICE=simarsv1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=simar'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='DEFER'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='+RECV','+RECVDG'
*.open_cursors=300
*.pga_aggregate_target=157286400
*.processes=150
*.remote_listener='dgscan.rac.meditate.com:1521'
*.remote_login_passwordfile='exclusive'
*.service_names='simarh.rac.meditate.com,simarh'
*.sga_target=471859200
*.standby_file_management='AUTO'
simarh2.thread=2
simarh1.thread=1
simarh1.undo_tablespace='UNDOTBS1'
simarh2.undo_tablespace='UNDOTBS2'
SQL>



8. Configure Oracle Network components as:
To get the changes from primary site to the standby site oracle dataguard relies on oracle net transportation mechanism.
In order to support dataguard communication needs we need to configure listners.ora and tnsnames.ora on both the standby and
the primary site. Specifically we should configure the following:
a. A listener running on the primary hosts, that will be there i.e. our grid listener on all the three nodes on primay site with a static listener entry
b. A static listener entry on all standby hosts for default grid listener.
Output of step b is as:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Output from standby node 1 is as:

oracle : dgrac01.rac.meditate.com : @crs : /home/oracle
$ echo $ORACLE_HOME
/u02/app/11.2.0.1/grid
oracle : dgrac01.rac.meditate.com : @crs : /home/oracle
$ cd $ORACLE_HOME/network/admin
oracle : dgrac01.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simarh) (SID_NAME=simarh1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

SID_LIST_LISTENER_SCAN1=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simarh) (SID_NAME=simarh1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

ADR_BASE_LISTENER_DGRAC01 = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
oracle : dgrac01.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$

srvctl stop listener -n `hostname -s`
srvctl start listener -n `hostname -s`
srvctl stop scan_listener
srvctl start scan_listener
lsnrctl status


and output is as:

oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap
$ srvctl stop listener -n `hostname -s`

oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap
$ srvctl start listener -n `hostname -s`

oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap


oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap
$ srvctl stop scan_listener

oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap
$ srvctl start scan_listener


oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap
$


$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-JUL-2011 20:03:30

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 01-JUL-2011 00:31:39
Uptime 1 days 19 hr. 31 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /etc/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dgrac01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.216)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.2.190)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "SIMARH.rac.meditate.com" has 1 instance(s).
Instance "simarh1", status READY, has 1 handler(s) for this service...
Service "simarh" has 1 instance(s).
Instance "simarh1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

oracle : dgrac01.rac.meditate.com : @crs : /home/oracle/gurpartap
$


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Output from standby node 2 is as:
oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ echo $ORACLE_HOME
/u02/app/11.2.0.1/grid
oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ cd $ORACLE_HOME/network/admin
oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ cat listener.ora
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simarh) (SID_NAME=simarh2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

SID_LIST_LISTENER_SCAN1=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simarh) (SID_NAME=simarh2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

ADR_BASE_LISTENER_DGRAC02 = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent


oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$



Output at starting this listener is as:

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ ps -ef grep pmon
oracle 12063 1 0 21:27 ? 00:00:00 asm_pmon_+ASM2
oracle 12885 1 0 21:32 ? 00:00:00 ora_pmon_BAL2
oracle 21439 1 0 22:29 ? 00:00:00 ora_pmon_simarh2
oracle 21591 8968 0 22:30 pts/0 00:00:00 grep pmon
oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$


Now execute the following commands on node 2.

srvctl stop listener -n `hostname -s`
srvctl start listener -n `hostname -s`
srvctl stop scan_listener
srvctl start scan_listener
lsnrctl status



Output is as:

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ srvctl stop listener -n `hostname -s`

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ srvctl start listener -n `hostname -s`

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ srvctl stop scan_listener

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ srvctl start scan_listener

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 01-JUL-2011 00:19:10

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 01-JUL-2011 00:17:22
Uptime 0 days 0 hr. 1 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /etc/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dgrac02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgrac02.rac.meditate.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "BAL" has 1 instance(s).
Instance "BAL2", status READY, has 1 handler(s) for this service...
Service "BALXDB" has 1 instance(s).
Instance "BAL2", status READY, has 1 handler(s) for this service...
Service "SIMARH.rac.meditate.com" has 1 instance(s).
Instance "simarh2", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle : dgrac02.rac.meditate.com : @crs : /u02/app/11.2.0.1/grid/network/admin
$




On Primay node 1 listener.ora looks like following:


LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simar) (SID_NAME=simar1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

SID_LIST_LISTENER_SCAN1=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simar) (SID_NAME=simar1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

ADR_BASE_LISTENER_DG2 = /u01/app/oracle



Now execute the following commands on node 1.

srvctl stop listener -n `hostname -s`
srvctl start listener -n `hostname -s`
srvctl stop scan_listener
srvctl start scan_listener
lsnrctl status


On Primay node 2 listener.ora looks like following:


LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simar) (SID_NAME=simar2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

SID_LIST_LISTENER_SCAN1=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simar) (SID_NAME=simar2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)



Now execute the following commands on node 2.

srvctl stop listener -n `hostname -s`
srvctl start listener -n `hostname -s`
srvctl stop scan_listener
srvctl start scan_listener
lsnrctl status




On Primay node 3 listener.ora looks like following:


LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simar) (SID_NAME=simar2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

SID_LIST_LISTENER_SCAN1=
(SID_LIST=
(SID_DESC=(GLOBAL_DBNAME = simar) (SID_NAME=simar2) (ORACLE_HOME=/u01/app/oracle/product/11.2.0.1/dbhome_1))
)

ADR_BASE_LISTENER_DG2 = /u01/app/oracle



Now execute the following commands on node 3.

srvctl stop listener -n `hostname -s`
srvctl start listener -n `hostname -s`
srvctl stop scan_listener
srvctl start scan_listener
lsnrctl status


c. An Oracle Net alias on the primary and standby for both primay and standby databases as:

RAC1ASM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = +ASM)
)
)

BAL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgscan.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = BAL)
)
)


EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = goldengate)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep)
)
)

SIMAR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.rac.meditate.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = simar)
(UR = A)
)
)

SIMARH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgrac01.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = simarh)
(UR = A)
)
)

SIMAR3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = simar3)
)
)

SIMAR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = simar2)
)
)

SIMAR1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.rac.meditate.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = simarsv1)
)
)

SIMARSV1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan.rac.meditate.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = simarsv1)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
)
)
)




9. Start the standby database in nomount state.

Output is as:
oracle : dgrac01.rac.meditate.com : @simarh1 : /u02/app/11.2.0.1/grid
$ srvctl start database -d simarh -o nomount
oracle : dgrac01.rac.meditate.com : @simarh1 : /u02/app/11.2.0.1/grid
$

10. Now we are ready to create a standby database.

Finally, we are ready to create the standby database. Do the following to create it:
Onstandby node 1, do the following:
Logon to RMAN prompt and execute the followimg:
connect target sys/gurpartap@simarsv1
connect auxiliary sys/gurpartap@simarh
DUPLICATE TARGET DATABASE FOR STANDBY from active database;

Output is as:

oracle : dgrac01.rac.meditate.com : @simarh1 : /u01/app/oracle/product/11.2.0.1/dbhome_1/network/admin
$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jun 27 23:01:37 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/gurpartap@simarsv1
connected to target database: SIMAR (DBID=668825007)
RMAN> connect auxiliary sys/gurpartap@simarh
connected to auxiliary database: SIMAR (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY from active database;
Starting Duplicate Db at 29-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=32 instance=simarh1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/orapwsimar1' auxiliary format
'/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/orapwsimarh1' ;
}
executing Memory Script
Starting backup at 29-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=simar1 device type=DISK
Finished backup at 29-JUN-11
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATADG1/simarh/controlfile/current.296.755135005'', ''+RECVDG1/simarh/controlfile/current.290.755135005'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATADG1/simarh/controlfile/current.263.755135005';
restore clone controlfile to '+RECVDG1/simarh/controlfile/current.272.755135005' from
'+DATADG1/simarh/controlfile/current.263.755135005';
sql clone "alter system set control_files =
''+DATADG1/simarh/controlfile/current.263.755135005'', ''+RECVDG1/simarh/controlfile/current.272.755135005'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATADG1/simarh/controlfile/current.296.755135005'', ''+RECVDG1/simarh/controlfile/current.290.755135005'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 29-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/snapcf_simar1.f tag=TAG20110629T234326 RECID=4 STAMP=755135028
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 29-JUN-11
Starting restore at 29-JUN-11
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 29-JUN-11
sql statement: alter system set control_files = ''+DATADG1/simarh/controlfile/current.263.755135005'', ''+RECVDG1/simarh/controlfile/current.272.755135005'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 471830528 bytes
Fixed Size 2214456 bytes
Variable Size 251659720 bytes
Database Buffers 213909504 bytes
Redo Buffers 4046848 bytes
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"+datadg1";
switch clone tempfile all;
set newname for datafile 1 to
"+datadg1";
set newname for datafile 2 to
"+datadg1";
set newname for datafile 3 to
"+datadg1";
set newname for datafile 4 to
"+datadg1";
set newname for datafile 5 to
"+datadg1";
set newname for datafile 6 to
"+datadg1";
set newname for datafile 7 to
"+datadg1";
set newname for datafile 8 to
"+datadg1";
backup as copy reuse
datafile 1 auxiliary format
"+datadg1" datafile
2 auxiliary format
"+datadg1" datafile
3 auxiliary format
"+datadg1" datafile
4 auxiliary format
"+datadg1" datafile
5 auxiliary format
"+datadg1" datafile
6 auxiliary format
"+datadg1" datafile
7 auxiliary format
"+datadg1" datafile
8 auxiliary format
"+datadg1" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +datadg1 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 29-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA1/simar/datafile/sysaux.273.743406845
output file name=+DATADG1/simarh/datafile/sysaux.267.755135281 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:10:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA1/simar/datafile/system.267.743406749
output file name=+DATADG1/simarh/datafile/system.266.755135885 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA1/simar/datafile/undotbs1.269.743406693
output file name=+DATADG1/simarh/datafile/undotbs1.262.755136011 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA1/simar/datafile/undotbs3.262.743406695
output file name=+DATADG1/simarh/datafile/undotbs3.261.755136167 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA1/simar/datafile/undotbs2.266.743406695
output file name=+DATADG1/simarh/datafile/undotbs2.279.755136253 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA1/simar/datafile/goldengate.271.743406751
output file name=+DATADG1/simarh/datafile/goldengate.278.755136327 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA1/simar/datafile/example.272.743406751
output file name=+DATADG1/simarh/datafile/example.277.755136393 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA1/simar/datafile/users.263.743406851
output file name=+DATADG1/simarh/datafile/users.276.755136429 tag=TAG20110629T234737
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 30-JUN-11
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=755136449 file name=+DATADG1/simarh/datafile/system.266.755135885
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=755136450 file name=+DATADG1/simarh/datafile/sysaux.267.755135281
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=755136450 file name=+DATADG1/simarh/datafile/undotbs1.262.755136011
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=755136450 file name=+DATADG1/simarh/datafile/users.276.755136429
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=755136450 file name=+DATADG1/simarh/datafile/example.277.755136393
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=755136450 file name=+DATADG1/simarh/datafile/undotbs2.279.755136253
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=755136450 file name=+DATADG1/simarh/datafile/undotbs3.261.755136167
datafile 8 switched to datafile copy
input datafile copy RECID=11 STAMP=755136450 file name=+DATADG1/simarh/datafile/goldengate.278.755136327
Finished Duplicate Db at 30-JUN-11
RMAN>



Step 11. Begin shipping redo to the standby database.
I hope you remmeber that we had earlier deferred LOG_ARCHIVE_DEST_2 on the primary database until we had the
standby mounted. Now we cam enalbe the second destination to ship the redo logs to standby location.
Here is teh following command to so that:
alter system set log_archive_dest_state_2=enable scope=both;


Output is as:
oracle : rac1.rac.meditate.com : @simar1 : /u01/app/oracle/product/11.2.0.1/dbhome_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 30 00:12:05 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> alter system set log_archive_dest_state_2=enable scope=both;
System altered.
SQL>


Step 12. Next, perform a log switch on the primary site on all the instances and verify that the transmission of that
log was successful:
alter system switch logfile;
select status,error from v$archive_dest where dest_id=2;
Output is as follows, I executed the following commands and got the following output on all the nodes :

SQL> alter system switch logfile;
System altered.
SQL> select status,error from v$archive_dest where dest_id=2;
STATUS ERROR
--------- -----------------------------------------------------------------
VALID
SQL>


If the transmission was successful, the status of the destination should be valid.
If the status is invalid, investigate the error listed in the error column to correct any issues.


Step 13. Now start the recovery on the standby site on node 1 using the following command :
alter database recover managed standby database disconnect from session;

Output is as:

SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>


Now check if the logs are being applied or not using following commands:

Primary on any node execute the following command:
select max(sequence#),THREAD# from v$archived_log group by THREAD#;
Output is:
SQL> select max(sequence#),THREAD# from v$archived_log group by THREAD#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
1178 1
934 2
967 3
SQL>



Standby:
select max(sequence#),THREAD# from v$archived_log where applied = 'YES' group by THREAD#;
Output is:

SQL> select max(sequence#),THREAD# from v$archived_log where applied = 'YES' group by THREAD#;

MAX(SEQUENCE#) THREAD#
-------------- ----------
1175 1
929 2
964 3

SQL>

If the no. on standby are increasing it means the logs are being applied otherwise look at the alertlogs to
investigate the error and fix it.



Meditate on the Lord, the Lord of the Universe. Cherish the Beloved Lord, Har, Har, in your mind. The Guru says to install it in your consciousness. Turn away from others, and turn to Him. Thus you shall obtain your Beloved, O my companion. 1 Pause In the pool of the world is the mud of attachment. Stuck in it, the his feet cannot walk towards the Lord. The fool is stuck; he cannot do anything else. Only by entering the Lord’s Sanctuary, O my companion, will you be released. 1
Thus your consciousness shall be stable and steady and firm. Wilderness and household are the same. Deep within dwells the One Husband Lord; outwardly, there are many distractions. Practice Raja Yoga, the Yoga of meditation and success. Says
Nanak, this is the way to dwell with the people, and yet remain apart from them.