Oracle 12c Transparent Data Encryption (TDE) on RAC Database with Physical Standby Database : Multiple Databases in Single Oracle Home

Oracle 12c TDE on RAC Database with Physical Standby RAC. The Oracle Database Home has Multiple Instances, due to which we have to use parameter $ORACLE_UNQNAME for ENCRYPTION_WALLET_LOCATION in sqlnet.ora file

In Oracle 12c ALTER SYSTEM and orapki has been deprecated and the correct method is to use ADMINISTER KEY MANAGEMENT commands. Many of the statements from the ALTER SYSTEM SQL statement correspond to the ADMINISTER KEY MANAGEMENT statement.

1. Create folder for wallets, since this is RAC cluster database ,it is better to have a shared filesystem. In our case it is ACFS
mkdir -p /ACFS/wallets/primeprd
mkdir -p /ACFS/wallets/onlnprd
chown -R oracle:oinstall /ACFS/wallets/primeprd
chown -R oracle:oinstall /ACFS/wallets/onlnprd
2. Create sqlnet.ora file with encryption wallet location, do on both nodes with oracle user
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))
3. Set DB Unique Name for the databases and verfiy the encryption wallet location, do on both nodes with oracle user

Also it is better to set the ORACLE_UNQNAME in the user profile

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primeprd -T “ORACLE_UNQNAME=primeprd”
select * from v$encryption_wallet;

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlnprd -T “ORACLE_UNQNAME=onlnprd”
select * from v$encryption_wallet;

4. At Dr side, with Oracle user in DB home directory
mkdir -p /ACFS/wallets/primedr
mkdir -p /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/primedr

Create sqlnet.ora file in DR side with encryption wallet location, do on both nodes with oracle user

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlndr -T “ORACLE_UNQNAME=onlndr”

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primedr -T “ORACLE_UNQNAME=primedr”

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management create keystore ‘/ACFS/wallet/primeprd/’ identified by “Abc1234$#”;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> administer key management create key identified by Abc1234$# with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> select key_id from v$encryption_keys;

KEY_ID
——————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> administer key management use key ‘AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ identified by Abc1234$# with backup;

keystore altered.

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
23-NOV-16 09.53.46.485930 AM +00:00
SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0
— Create Auto Login for the Keystore —

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/ACFS/wallet/primeprd/’ identified by Abc1234$#;

keystore altered.

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:13:30 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN AUTOLOGIN SINGLE NO
0
Copy the Encryption key Files to Standby Site as “Oracle” user

primeprd2::12c–>cd /ACFS/wallet/primeprd/
primeprd2::12c–>scp -r * oracle@exadrdbadm01:/ACFS/wallets/primedr

The authenticity of host ‘exadrdbadm01 (10.5.238.11)’ can’t be established.
RSA key fingerprint is c8:c0:69:80:1c:c8:ea:e3:6c:46:6b:c1:05:86:aa:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘exadrdbadm01,10.5.238.11’ (RSA) to the list of known hosts.
oracle@exadrdbadm01’s password:
cwallet.sso 100% 3893 3.8KB/s 00:00
ewallet_2016112309525312.p12 100% 2408 2.4KB/s 00:00
ewallet_2016112309534645.p12 100% 3656 3.6KB/s 00:00
ewallet.p12 100% 3848 3.8KB/s 00:00

— At DR Side on Both Nodes–
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
—————————— ——————– ——— ——— ———-
FILE
/u03/app/oracle/admin/primedr/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
/home/oracle::12c–>cd /u03/app/oracle/product/12.1.0.2/teststandby/network/admin/
/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /ACFS/wallets/primedr/$ORACLE_UNQNAME/)))

/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:27:22 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallets/primedr/
OPEN AUTOLOGIN SINGLE NO
0
SQL> exit

 

5. Create Encrypted Tablespace
CREATE TABLESPACE encrypt_ts
DATAFILE ‘+DATAC1’ SIZE 250M
ENCRYPTION USING ‘AES256’
DEFAULT STORAGE (ENCRYPT);

Advertisements

Testing a Physical Standby using Snapshot Standby in Oracle 10g

TESTING A PHYSICAL STANDBY DATABASE IN ORACLE 10G Using SNapshot Standby

This procedure is very useful when you have physical standby databases for testing and other purposes that require read-write access to the standby database. Also, it improves your checklist in the event of an error or disaster.
By using Snapshot standby databases, redo data is not applied until you convert the snapshot standby database back into a physical standby database, and after all local updates to the snapshot standby database are discarded.
Activation of the standby
On the standby database

1. Stop dataguard brokers on standby:

SQL> alter system set dg_broker_start=FALSE;
Get the SCN

SQL> select current_scn from v$database;

2. Cancel the managed recovery:

SQL> alter database recover managed standby database cancel;
3. Create the restore point.A restore point can be specified such that it guarantees the database can be recovered to a particular point-in-time and eliminates the need to manually record an SCN or timestamp to use with the Flashback database and Flashback table operations:

SQL> create restore point TEST_NEW_FEATURE guarantee flashback database;

Now confirm the scn from restore point:
SQL> col name form a40;
SQL> select scn, time, name from v$restore_point where name = ‘TEST_NEW_FEATURE’;

4. Prepare the primary

Archive logsWhen using the standby redo logs, this step is essential to ensure that the database can be properly flashed back to the restore point.
SQL> alter system archive log current;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

Stop shipment of logs
SQL> alter system set log_archive_dest_state_2=DEFER
5. Activate the Physical Standby
Activation of the standby:

SQL> alter database activate standby database;
SQL> startup mount force;
SQL> alter database set standby database to maximize performance;
(This is used in case you have not set it before)

SQL> alter database open;

disable log_archive_dest_2 (this will prevent archive logs being sent to primary):
SQL> alter system set log_archive_dest_state_2=DISABLE;
6. Revert the snapshot database to physical standby

Flashback to the restore point:
SQL> STARTUP MOUNT FORCE;
SQL> FLASHBACK DATABASE TO RESTORE POINT TEST_NEW_FEATURE;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;
SQL> STARTUP MOUNT FORCE;

Re-enable log shipping on the primaryEnable shipment:
SQL> alter system set log_archive_dest_state_2=ENABLE

To re-enable log shipping on the standby, enable this parameter:
SQL> alter system set log_archive_dest_state_2=ENABLE

Multiple Standby Databases Dataguard Oracle

We will create 2 standby databases from a single RAC primary database. 11gR2 allows upto 30 standby databases.  The most important part is to consider the dataguard parameters, the rest is a normal dataguard creation. The important parameters are as below :

db_file_name_convert

log_archive_config

log_archive_dest_1

log_archive_dest_2

log_archive_dest_3

LOG_ARCHIVE_DEST_STATE_1

LOG_ARCHIVE_DEST_STATE_2

LOG_ARCHIVE_DEST_STATE_3

log_file_name_convert

remote_login_passwordfile

STANDBY_FILE_MANAGEMENT

fal_client

fal_server

sec_case_sensitive_logon    (if on 11gR2)
All database are running on Grid + ASM 11.2.0.3.4 with RAC primary and RAC standby’s. Please note the procedure is identical to a single instance setup. In Rac the only special thing you do is register the standby database and instances to the OCR via srvctl command.

We all call our primary database SWX and our PHYSICAL STANDBY databases as SWXSTANDBY, SWXSTANDYBY2. We will use RMAN Duplicate Active Standby database feature of 11gR2. If your standby databases are in different data centesr and the bandwidht isn’t suffcient you can use the tradionatl RMAN  duplicate command.

1. Create tnsnames.ora entry on Primary database. Use RDBMS owner for the tnsnames.ora not Grid infrastructure owner

SWX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.213)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swx1)(UR=A)
)
)

SWXSTANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.219)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swxstandby)(UR=A)
)
)

SWXSTANDBY2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =  *.*.*.224)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swxstandby2)(UR=A)
)

2. Add an SID entry on the listener.ora as Grid infrastructure user

SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=swx)(ORACLE_HOME=/grid/app/11.2.0/grid/)))

3. Create a pfile and the below datagaurd parameters to the pfile

#########################STANDBY PARAMETERS########################
*.DB_UNIQUE_NAME=’swx’
*.db_file_name_convert=’+DATA/SWXSTANDBY2/’,’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY2/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’
*.log_archive_config=’dg_config=(swxstandby2,swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swx’
*.log_archive_dest_2=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby’
*.log_archive_dest_3=’service=swxstandby2 LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby2′
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_3=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/SWXSTANDBY2/’,’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY2/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’
*.remote_login_passwordfile=’exclusive’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.fal_client=’SWX’
*.fal_server=’SWXSTANDBY2′,’SWXSTANDBY’
*.sec_case_sensitive_logon=FALSE
########################################

4. Restart the database with the new pfile and create spfile from it and bounce the database again

5. Login to the First DR database server as Oracle user and create tnsnames.ora entry

SWX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.213)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swx)(UR=A)
)
)

SWXSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.218)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swxstandby)(UR=A)
)
)

SWXSTANDBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.224)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swxstandby2)(UR=A)
)
)

6. Add SID listener entry in listener.ora file as Grid infrastructure user.

SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=swx)(ORACLE_HOME=/grid/app/11.2.0/grid)))

7. Create a pfile with the dataguard parameters from the pfile created before on primary and add below parameters.

#########################STANDBY DATABASE PARAMETERS########################
*.DB_UNIQUE_NAME=’SWXSTANDBY’
*.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWXSTANDBY2/’
*.log_archive_config=’dg_config=(swxstandby2,swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swxstandby’
*.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swx’
*.log_archive_dest_3=’service=swxstandby2 LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby2′
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_3=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWXSTANDBY2/’
*.remote_login_passwordfile=’exclusive’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.fal_client=’SWXSTANDBY’
*.fal_server=’SWX’,’SWXSTANDBY2′
*.sec_case_sensitive_logon=FALSE
########################################

8. Start the database with the pfile and create spfile from it

9. Clone the database from RMAN using active option

rman target sys/***@swx auxiliary sys/***@swxstandby

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

10. Register the database and instance with srvctl command

srvctl add database -d SWXSTANDBY -o /oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -r PHYSICAL_STANDBY -a DATA,FRA
srvctl add instance -d SWXSTANDBY -i swx1 -n csftest-2
srvctl stop database -d SWXSTANDBY
sqlplus “/as sysdba”
srvctl start database -d swxstandby
srvctl modify database -d swxstandby -s mount
srvctl config database -d swxstandby

## TO ACTIVATE ACTIVE STANDBY DATABASE ###
srvctl start database -d swxstandby -o open

11. Login to the Second DR database server as Oracle user and create tnsnames.ora entry

SWX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.213)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swx)(UR=A)
)
)

SWXSTANDBY2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SWXSTANDBY2)(UR=A)
)
)

SWXSTANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.218)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SWXSTANDBY)(UR=A)
)
)

12. Create a listener.ora entry as grid user

SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=swx)(ORACLE_HOME=/grid/app/11.2.0/grid)))

13. Create a pfile with the dataguard parameters from the pfile created before on primary and add below parameters.

#########################STANDBY DATABASE PARAMETERS########################
*.DB_UNIQUE_NAME=’SWXSTANDBY2′
*.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWXSTANDBY2/’
*.log_archive_config=’dg_config=(swxstandby2,swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swxstandby2′
*.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swx’
*.log_archive_dest_3=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_3=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+DATA/SWXSTANDBY2/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWXSTANDBY2/’
*.remote_login_passwordfile=’exclusive’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.fal_client=’SWXSTANDBY2′
*.fal_server=’SWX’,’SWXSTANDBY’
*.sec_case_sensitive_logon=FALSE
########################################

14. Start the database with the pfile and create spfile from it

15. Clone the database from RMAN using active option

rman target sys/***@swx auxiliary sys/***@swxstandby

RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

16. Register the database and instance with srvctl command

srvctl add database -d swxstandby2 -o /oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -r PHYSICAL_STANDBY -n swx -a DATA,FRA
srvctl add instance -d swxstandby2 -i swx1 -n suncsftst

Now that we  have  a fully functional RAC PRIMARY with 2 PHYSICAL STANDBYs now let us consider the 2 most important things in Dataguard: Switchover and Failover

————–

Switchover

—————-

No extra steps are necessary when performing a switchover. All bystander physical
standby databases automatically apply redo data received from the new primary
database.

————–

Failover

—————-

The steps for performing a failover to a physical standby database depend on the
Redo Apply progress of the new primary database and any bystander physical
standby databases at the time of the failover.
• If the new primary database has applied more redo than all of the bystander
physical standby databases, no additional steps are required. Only the original
primary database needs to be reinstated, using the steps documented in Oracle
Data Guard Concepts and Administration[5], Section 12.4.1 “Flashing Back a Failed
Primary Database into a Physical Standby Database.”.
• If any bystander physical standby database has applied more redo than the new
primary database, then perform the following steps to reinstate the bystander
physical standby

SQL*Plus Physical/Physical Failover with Physical Bystander Ahead

1. Determine STANDBY_BECAME_PRIMARY_SCN from the new
primary.
SQL> select STANDBY_BECAME_PRIMARY_SCN from
v$database;
2. On the bystander physical standby, flash back to
STANDBY_BECAME_PRIMARY_SCN from the new primary
database.
SQL> flashback database to scn
<STANDBY_BECAME_PRIMARY_SCN>;
3. On the bystander physical standby, delete divergent archived redo logs
created at the time of, or after, the failover.
RMAN> delete archivelog from scn
<STANDBY_BECAME_PRIMARY_SCN>;
4. On the new primary database, enable the redo transport destination
for this bystander physical standby and archive the current redo log.
SQL> alter system set
log_archive_destination_2=enable;
SQL> alter system archive log current;
5. After the logs have been received by the bystander physical standby,
start Redo Apply on the bystander physical standby.

SQL> alter database recover managed standby
database using current logfile through all
switchover disconnect;
The bystander standby database is now reinstated.

For more information refer to this excellent Oracle document.

http://www.oracle.com/technetwork/database/features/availability/maa10gr2multiplestandbybp-1-131937.pdf

Interesting Case-Study on Multiple Standby Databases. Apparently Apple uses it for one of their Large databases.

http://www.oracle.com/technetwork/database/features/availability/311400-134359.pdf

RAC to Single Instance Standby Database Setup (Oracle 11gR2 Physical Standby)

Primary is a 2 node-rac cluster with instances swx1 and swx2 running on ASM. A service called swxscan is created for client sides. We will use this service name for the tnsnames.ora.

The standby instance will be called swxstandby and it will be running single instance on ASM as well.

This is a standard dataguard configuration but the only tricky part is the listeners on primary and DR, since in 11gR2 we have the Grid Infrastructure running under Grid user and RDBMS under oracle (provided you followed Oracle’s OFA, which by the way is the best way to do things imho)

Also take care for the user permissions on DR server for the raw devices. Since grid user owns the raw devices the group which grid is part of should also have oracle user as its member.

Do we will begin the procedure now. First we will do steps on Production RAC and then do the remaining on Standby server. I am not going to use active duplicate over the network. The reason for this is that in a production environment where size of your database is genereally above 500gb. If your bandwidth is not good enough it might take days to duplicate the database over network. Its better to use the old duplicate command and copy the rman backups over using a tape or any restore method at your disposal. What I do is backup it up to tape and then restore on DR site.

———————-

Environment

——————–
Primary Database 2 Node RAC
1 Node Names: swxracnode1, swxracnode2
2 DB Name: SWX
3 DB Unique Name: SWX
4 DB Version: 11.2.0.3
5 Grid Infrastructure (CRS + ASM).
6 SCAN settings in /etc/hosts file, so SCAN listener only running on one node (Swxracnode2 for this demo)
7 ASM Diskgroups: +DATA, +FRA
8 CRS installed with ‘Grid’ User, RDBMS installed with ‘Oracle’ user

Standby Database Single Instance:
1 ASM Diskgroup : +DATA, +FRA (For datafiles and FRA)
2 DB Unique Name: SWXSTANDBY
3 DB Name: SWX
4 CRS(for single instance) installed with ‘Grid’ User, RDBMS installed with ‘Oracle’ user

—————————
PREPARATIONS AT PRIMARY SITE
—————————-

GI_HOME= /grid/11.2.0/grid
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1

CRS User= Grid
RDBMS User= Oracle

1. Force logging on primary.
Login to any one node and run below sql command

alter database force logging;

2. Check for Online Redo Logfiles(ORL) on nodes and Create Standby Redo Logs (SRL) on the Primary RAC.

select * from gv$logfile;
select * from gv$log;

— ORL ADDITION —
Add atleast four groups per thread of size 100M;

ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 1 (‘+DATA/swx/onlinelog/redo-01-01.log’,’+DATA/swx/onlinelog/redo01-02.log’) SIZE 100m,
GROUP 2 (‘+DATA/swx/onlinelog/redo-02-01.log’,’+DATA/swx/onlinelog/redo-02-02.log’) SIZE 100m;

ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 (‘+DATA/swx/onlinelog/redo-03-01.log’,’+DATA/swx/onlinelog/redo-03-02.log’) SIZE 100m,
GROUP 4 (‘+DATA/swx/onlinelog/redo-04-01.log’,’+DATA/swx/onlinelog/redo-04-02.log’) SIZE 100m;

ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 5 (‘+DATA/swx/onlinelog/redo-05-01.log’,’+DATA/swx/onlinelog/redo05-02.log’) SIZE 100m,
GROUP 6 (‘+DATA/swx/onlinelog/redo-06-01.log’,’+DATA/swx/onlinelog/redo-06-02.log’) SIZE 100m;

ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 7 (‘+DATA/swx/onlinelog/redo-07-01.log’,’+DATA/swx/onlinelog/redo-07-02.log’) SIZE 100m,
GROUP 8 (‘+DATA/swx/onlinelog/redo-08-01.log’,’+DATA/swx/onlinelog/redo-08-02.log’) SIZE 100m;

ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 9 (‘+DATA/swx/onlinelog/redo-09-01.log’,’+DATA/swx/onlinelog/redo-09-02.log’) SIZE 100m,
GROUP 10 (‘+DATA/swx/onlinelog/redo-10-01.log’,’+DATA/swx/onlinelog/redo-10-02.log’) SIZE 100m;

ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 11 (‘+DATA/swx/onlinelog/redo-11-01.log’,’+DATA/swx/onlinelog/redo-11-02.log’) SIZE 100m,
GROUP 12 (‘+DATA/swx/onlinelog/redo-12-01.log’,’+DATA/swx/onlinelog/redo-12-02.log’) SIZE 100m;

Create Standby Redo Logs (SRL) related to the online redo log (ORL) files in the database. As there are three redo log groups with two members each we will create four members for each thread. Note that OMF (db_create_file_dest=’+DATA’) are used and the SRL are created with the same size as ORL.

(maximum # of logfiles +1) * maximum # of threads

For example in setup using two online log files for each thread. Thus, the number of standby redo logs should be (2 + 1) * 2 = 6. That is, one more standby redo log file for each thread.

3. Set DB_RECOVERY_FILE_DEST_SIZE, DB_RECOVERY_FILE_DEST and ADD the SRL’s

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=400G scope=both sid=’*’;

SQL> alter system set DB_RECOVERY_FILE_DEST=’+FRA’ scope=both sid=’*’;

alter database add standby logfile thread 1 group 7 size 104857600;

alter database add standby logfile thread 1 group 8 size 104857600;

alter database add standby logfile thread 1 group 9 size 104857600;

alter database add standby logfile thread 1 group 10 size 104857600;

alter database add standby logfile thread 2 group 11 size 104857600;

alter database add standby logfile thread 2 group 12 size 104857600;

alter database add standby logfile thread 2 group 13 size 104857600;

alter database add standby logfile thread 2 group 14 size 104857600;

4. Bring Database in Archivelog mode

alter system set log_archive_dest_1=’location=+fra’ scope=both sid=’*’;

srvctl stop database -d swx

### log into both nodes

sqlplus “/as sysdba”

startup mount

alter database archivelog;

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FRA
Oldest online log sequence 1625
Next log sequence to archive 1626
Current log sequence 1626

SQL> alter database open;

5. Configure Listener.ora (GRID user) and Tnsnames.ora (ORACLE user) for both nodes on Primary Side
Configure tnsnames alias and a static registration with the GI listeners for both primary and standby database and compliant with Data Guard Broker. With Oracle 11gR2 since the SCAN Listener was introduced the modification is made to both listeners in GI $OH and to the tnsnames.ora in RDBMS $OH. Reload the listeners.

— Listener.ora on both Nodes of Primary RAC as Grid User–

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))
# line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_S
CAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_S
CAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_S
CAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by
Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by
Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by
Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SWXSTANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = swxracdr)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swx)
(SID_NAME = swx)
)
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swxstandby)
(SID_NAME = swxstandby)
)
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swx_DGMGRL)
(SID_NAME = swx)
)
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swxstandby_DGMGRL)
(SID_NAME = swxstandby)
)
)

## As grid user in $ORACLE_HOME (which is the Grid Home) do the below on both nodes.Start and Stop all scan listeners on both nodes

lsnrctl stop LISTENER_SCAN1
lsnrctl stop LISTENER_SCAN2
lsnrctl stop LISTENER_SCAN3

lsnrctl start LISTENER_SCAN1
lsnrctl start LISTENER_SCAN2
lsnrctl start LISTENER_SCAN3

— TNSNAMES.ora on both Nodes of Primary RAC as Oracle user —

SWXSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = swxracdr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swxstandby)
)
)

SWX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = swxscan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swx)
)
)

## tnsping ping swx, swxstandby as Oracle user to check ### If not pingabe check if DNS is configured on your server

6. Create password files for the Nodes as Oracle user.

User format as orapwswx1 for node 1
User format as orapwswx2 for node 2

Create the password file in folder $ORACLE_HOME/dbs on all nodes.

orapwd file=$ORACLE_HOME/dbs/orapwsswx1 ignorecase=y password=xxxxxxxx force=y
orapwd file=$ORACLE_HOME/dbs/orapwsswx2 ignorecase=y password=xxxxxxxx force=y

–ignore case is important parameter here since from 11gR2 onwards passwords are case-sensitive

PING[ARC2]: Heartbeat failed to connect to standby ‘swx’. Error is 16191.
Error 1017 received logging on to the standby

These are common errors associated with password files. If you encounter the above errors, recreate the password files.

7. Create initialization parameter file and add Standby database parameters to it.

We will not use Active duplication over the network. Since in a production environment it is sometimes impractical; where size of database can easily exceed 1TB. The method to duplicate via RMAN and copy the backup to Standby database server is a tried and tested method and works beautifully.

Login to sqlplus on one of the nodes.

show parameter spfile;

create pfile=’/backup/swxPRIMARY.ora’ from spfile;

Ensure you remote_listener parameter is set to your scan listener

*.remote_listener=’swxscan.domain.com:1521′

Add the below parameters to the new swxPRIMARY.ora file

#########################STANDBY PARAMETERS########################
*.DB_UNIQUE_NAME=’swx’
*.db_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’
*.log_archive_config=’dg_config=(swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swx’
*.log_archive_dest_2=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’
*.remote_login_passwordfile=’exclusive’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.fal_client=’SWX’
*.fal_server=’SWXSTANDBY’
*.sec_case_sensitive_logon=FALSE
########################################

FULL PARAMETER FILE OF THE PRIMARY DATABASE

swx2.__db_cache_size=4294967296
swx1.__db_cache_size=3892314112
swx2.__java_pool_size=16777216
swx1.__java_pool_size=33554432
swx2.__large_pool_size=33554432
swx1.__large_pool_size=50331648
swx2.__pga_aggregate_target=2147483648
swx1.__pga_aggregate_target=2147483648
swx2.__sga_target=6442450944
swx1.__sga_target=6442450944
swx2.__shared_io_pool_size=536870912
swx1.__shared_io_pool_size=536870912
swx2.__shared_pool_size=1526726656
swx1.__shared_pool_size=1862270976
swx2.__streams_pool_size=0
swx1.__streams_pool_size=33554432
*.audit_file_dest=’/oracle/app/oracle/admin/swx/adump’
*.audit_trail=’db’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/swx/controlfile/current.265.773009443′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’swx’
*.db_recovery_file_dest_size=429496729600
*.db_recovery_file_dest=’+FRA’
*.diagnostic_dest=’/oracle/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=swxXDB)’
*.swx1.instance_number=1
*.swx2.instance_number=2
*.job_queue_processes=1000
*.log_archive_config=’dg_config=(swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swx’
*.log_archive_dest_2=’service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby’
*.open_cursors=300
*.pga_aggregate_target=2147483648
*.processes=1000
*.remote_listener=’swxscan.qiibonline.com:1521′
*.remote_login_passwordfile=’exclusive’
*.sessions=1105
*.sga_target=6442450944
*.standby_file_management=’AUTO’
*.swx2.thread=2
*.swx1.thread=1
*.swx2.undo_tablespace=’UNDOTBS2′
*.swx1.undo_tablespace=’UNDOTBS1′
*.db_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’
*.log_file_name_convert=’+DATA/SWXSTANDBY/’,’+DATA/SWX/’,’+FRA/SWXSTANDBY/’,’+FRA/SWX/’
*.standby_file_management=auto
*.fal_server=’SWXSTANDBY’
*.fal_client=’SWX’

Bounce the database with the new parameter file

sqlplus “/as sysdba”

shutdown immediate;

— shutdown 2nd node also, you can use ‘srvctl stop database -d swx’ for this also

startup pfile=’/backup/swxPRIMARY.ora’;

create spfile from pfile=’/backup/swxPRIMARY.ora’;

shutdown immediate;

startup;

show parameter spfile;

Now we have completed the steps at Primary Side, only the duplicate command from RMAN is remaining. But let us first ensure we prepare the standby database before we duplicate and restore the database.

—————————
PREPARATIONS AT STANDBY SITE
—————————-

GI_HOME= /grid/11.2.0/grid
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1

CRS User= Grid
RDBMS User= Oracle

The installation for database is grid infrastrcuture utilizing ASM for the database. We have already created the HAS services and the RDBMS software was also installed. In ASM 2 diskgroups called +DATA, +FRA was created, same like production.

We will create a new database using the intialization parameter file we created before but we will add new parameters for the standby database. If your are doing this setup on windows then you will have to create a windows service first using ‘oradim’

1. Create listener.ora with Grid user

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = swxracdr)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /grid/app

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

SWXSTANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = swxracdr)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swx)
(SID_NAME = swx)
)
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swxstandby)
(SID_NAME = swxstandby)
)
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swx_DGMGRL)
(SID_NAME = swx)
)
(SID_DESC =
(ORACLE_HOME= /grid/11.2.0/grid)
(GLOBAL_DBNAME = swxstandby_DGMGRL)
(SID_NAME = swxstandby)
)
)

Reload the listeners using lsnrctl reload command

2. Create tnsnames.ora file using Oracle user

SWX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = swxscan.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swx)
)
)

SWXSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = swxracdr.domain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swxstandby)
)
)

3. Create Directory for adump audit_file_dest, also create directories in the ASM diskgroup using ASMCMD

— as oracle user —
mkdir -p /oracle/app/oracle/admin/swx/adump

asmcmd>

asmcmd> mkdir +DATA/SWXSTANDBY/
asmcmd> mkdir +DATA/SWX/
asmcmd> mkdir +FRA/SWXSTANDBY/
asmcmd> mkdir +FRA/SWX/

Also create any other directories you think you have missed in ASM or on the local filesystem.

4. Change intilization parameters in swxPRIMARY.ora file and name file as swxSTANDBY.ora

Since it is RAC to single instance dataguard the parameter cluster_database needs to be set to false. This is the primary difference between a single instance and a rac database.

#########################STANDBY DATABASE PARAMETERS########################
*.DB_UNIQUE_NAME=’SWXSTANDBY’
*.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’
*.log_archive_config=’dg_config=(swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swxstandby’
*.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swx’
*.LOG_ARCHIVE_DEST_STATE_1=’ENABLE’
*.LOG_ARCHIVE_DEST_STATE_2=’ENABLE’
*.LOG_ARCHIVE_FORMAT=’log%t_%s_%r.arc’
*.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’
*.remote_login_passwordfile=’exclusive’
*.STANDBY_FILE_MANAGEMENT=’AUTO’
*.fal_client=’SWXSTANDBY’
*.fal_server=’SWX’
*.cluster_database=false
*.sec_case_sensitive_logon=FALSE
########################################

FULL PARAMETER FILE swxSTANDBY.ora

swxstandby.__db_cache_size=3724541952
swxstandby.__java_pool_size=16777216
swxstandby.__large_pool_size=50331648
swxstandby.__pga_aggregate_target=2147483648
swxstandby.__sga_target=6442450944
swxstandby.__shared_io_pool_size=536870912
swxstandby.__shared_pool_size=2063597568
swxstandby.__streams_pool_size=16777216
*.audit_file_dest=’/oracle/app/oracle/admin/swx/adump’
*.cluster_database=false
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/swxstandby/controlfile/current.272.792584487’#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+FRA’
*.db_domain=”
*.db_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’
*.db_name=’swx’
*.db_recovery_file_dest_size=429496729600
*.db_recovery_file_dest=’+FRA’
*.db_unique_name=’SWXSTANDBY’
*.diagnostic_dest=’/oracle/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=swxXDB)’
*.fal_client=’SWXSTANDBY’
*.fal_server=’SWX’
*.job_queue_processes=1000
*.log_archive_config=’dg_config=(swxstandby,swx)’
*.log_archive_dest_1=’LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swxstandby’
*.log_archive_dest_2=’service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swx’
*.log_file_name_convert=’+DATA/SWX/’,’+DATA/SWXSTANDBY/’,’+FRA/SWX/’,’+FRA/SWXSTANDBY/’
*.open_cursors=300
*.pga_aggregate_target=2147483648
*.processes=1000
*.remote_listener=’swxscan.qiibonline.com:1521′
*.remote_login_passwordfile=’exclusive’
*.sec_case_sensitive_logon=FALSE
*.sessions=1105
*.sga_target=6442450944
*.standby_file_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′

5. Now startup the database in nomount mode using this pfile and create spfile from it.

sqlplus “/as sysdba”

startup nomount pfile=’/backup/swxSTANDBY.ora’;

create spfile from pfile=’/backup/swxSTANDBY.ora’;

shutdown immediate;

startup nomount;

6. Create password file for standby database

Create the password file in folder $ORACLE_HOME/dbs on all nodes and DR server as Oracle user

User format as orapwswxstandby for Standby database

orapwd file=$ORACLE_HOME/dbs/orapwswxstandby force=y ignorecase=y password=xxxxxx

To check password file use query from sql plus.

select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
—————————— —– —– —–
SYS TRUE TRUE FALSE

—————————
DUPLICATING THE DATABASE FOR STANDBY FROM RMAN
—————————-

1. Before duplicating ensure that the mountpoint /backup exists on the DR server. This is the location where we will copy the backups to and restore from there.

RMAN > show all;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/rmanbkp/SWX_Lev0_%s_%D_%M_%Y’;

RMAN> sql ‘alter system archive log current’;

RMAN> backup database include current controlfile for standby plus archivelog;

Copy the database to the DR server on folder ‘/backup/rmanbkp’ and now we will begin to duplicate

Login to RMAN from DR server from Oracle user like below

rman target sys/***@swx auxiliary sys/***

or

rman target sys/***@swx auxiliary /

or

rman target sys/***@swx auxiliary sys/****@swxstandby

$ rman target sys/*****@swx auxiliary /

Recovery Manager: Release 11.2.0.3.0 – Production on Tue Nov 20 15:39:01 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SWX (DBID=xxxxxx)
connected to auxiliary database: SWX (DBID=xxxxxx)

If you get some error use (UR=A) in tnsnames.ora file like

(SERVICE_NAME = swx) (UR=A)

Go to primary server and crosscheck archivelogs and backupsets and delete expired or obsolete objects and relogin to auxiliary.

RMAN> duplicate target database for standby nofilenamecheck dorecover;

2. Put the Standby into Managed standby mode:
ON standby server

shutdown immediate;

alter database mount standby database;

alter database recover managed standby database using current logfile disconnect from session;

Go to the Primary Server:

SQL> alter system switch logfile;
SQL> alter system archive log current;

Check if the logs are coming here and properly applying.

3. Verify the sync between primary and standby

From primary
sql > select max(sequence#) as “PRIMARY” from v$log_history;

From Standby

SQL > select max(sequence#) as “STANDBY”,applied from v$archived_log group by applied ;

SQL> Select process,status from v$managed_standby;

If there is error like below , it is generally due to password files and for 11gR2 the parameter sec_case_sensitive_logon

PING[ARC2]: Heartbeat failed to connect to standby ” Error is 16191.
Error 1017 received logging on to the standby

Recreate the password files and alter sec_case_sensitive_logon to false

Invoking a DR Standby Database

Overview

There are different methods you can use to invoke a standby database depending on the typical DR scenario you are facing.

Scenario for usage.

1. Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment.

This scenario can be used if both the Primary and Standby Servers are available so that you can connect to both environments simultaneously and perform a clean switchover.

2. Activating a Standby Database

This method will activate the Standby Database as the primary database recovering up to the point of the last log shipment. After activating a standby database as the primary the original primary server becomes obsolete and will need to be rebuilt as a standby database (e.g. you can not just switch the servers back to act as a primary or standby database and will need to rebuild the data-guard environments).

This scenario can be used if the primary server is not available and you need to force the standby database to become the primary.

3. Opening the standby Database in Read-Only Mode

This method involves stopping data-guard replication and opening the standby database in read-only mode for querying. The database can then be shutdown and reopened in standby mode so that data-guard replication can resume (assuming all required archive logs are still available)

This scenario can be used if you want to open the standby database temporarily for querying data and then wish to put the database back into standby mode.

The following section describes how to perform each of the above methods of invoking a standby database:

1. Database Switchover

Using this method you can switch backwards and forwards between the primary and DR servers (e.g. so that the primary can become DR and DR can become primary) without having to rebuild either environment:

On Primary Server:

SQL> alter database commit to switchover to standby;

This may cause the following error to be generated:

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

If this does occur then restart the database, as below, before retrying the above command:

SQL> shutdown immediate

SQL> startup

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

The primary server is now configured as a DR standby database.

On DR Server:

SQL> alter database recover managed standby database cancel;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup

The DR server is now configured as the primary database.

To switch back you just need to repeat the above process but the other way around (e.g. convert the DR database back to a standby and the primary database back to primary).

2. Activating a Standby Database

If the primary database is not available the standby database can be converted into the primary database as follows:

SQL> alter database recover managed standby database cancel;

SQL> alter database activate standby database;

SQL> shutdown immediate

SQL> startup

The original primary database is now obsolete and can be rebuilt as a standby database once it is available again.

3. Opening the Standby Database in Read Only Mode (Active Dataguard)

The standby database can be opened in read only mode for querying and then converted back into a standby database without affecting the primary.

On standby server:

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

The standby database is now open and available for querying in read only mode.

To put the standby database back into standby mode:

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

=====================================================================================

2 Node RAC to Single Instance Dataguard Switchover (Oracle 11gR2 Physical Standby )

Many a times to save cost in building a DR solution for a certain application; you have to create a single instance database for your RAC primary. So you have gone ahead and build the 2-node to single instance setup and now comes the time to test it. Well it is not much different than any other switchover scneario but the difference is that the switchover will be done with one node active in the primary (I prefer to use the OCR master node, check for OCR master node using “ocrconfig -showbackup” command)

This procedure was done on Oracle 11gR2 11.2.0.3 on Solaris 64-bit SPARC servers.

Now let us begin our planned switchover. But before we begin I hope you have checked all the necessary parameters on both sides. Viz.

Log_archive_dest_1
Log_archive_dest_2
Log_archive_dest_state_1
Log_archive_dest_state_2
Fal_client
Fal_server
Local_listener
Remote_listener
Standby_archive_Dest
Standby_archive_management
service_names
db_unique_name
instance_name
db_file_name_convert
log_file_name_convert

Verify the logs on both sites are in sync and logs are properly applied on the standby database.

On Primary,
Select thread#,max(sequence#) from v$archived_log group by thread#;
On Standby,
Select thread#,max(sequence#) from v$log_history group by thread#;

Now we begin the actual switch over procedure

1. Shutdown RAC DB and STARTUP AGAIN TO CLEAR SESSIONS. I always prefer this method after shutting down the application

srvctl stop database -d primary

srvctl start database -d primary

— Shutdown any service if you have defined one —

srvctl stop service -d swx -s primaryservice

2. Shutdown Standby database and Restart Recovery

shutdown immediate;

startup mount;

alter database recover managed standby database using current logfile disconnect from session;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

— Create a Guaranteed Restore point on Standby Database in case of a rollback if something goes wrong —

CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

3. Shutdown Node 2 in cluster

srvctl stop instance -d primary -n primarynode2

4. Take Guaranteed Flashback Restore points on Primary

CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

5. Verify that the primary database can be switched to the standby role

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
—————–
TO STANDBY

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

— Actual SWAPOVER Procedure —

6. Initiate Shutdown on Primary( Ensure 2nd Instance is shutdown)

alter database commit to switchover to physical standby with session shutdown;

Shutdown immediate;

Startup mount;

select name,db_unique_name, log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

Make log_Archive_Dest_state_2 to DEFER

alter system set log_archive_dest_state_2=’DEFER’ sid=’*’;

7. On the (old) standby database.

select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

On the (old) standby database switch to new primary role:

alter database commit to switchover to primary WITH SESSION SHUTDOWN;

shutdown immediate;

startup;

8. On new Primary database

select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

Make log_Archive_Dest_state_2 to ENABLE

alter system set log_archive_dest_state_2=’ENABLE’ sid=’*’;

alter system switch logfile;

On new primary,

select error from v$archive_Dest_status;
select max(sequence#) from v$archived_log;

9. On new Standby, Start Redo Apply

alter database recover managed standby database using current logfile disconnect;

Select max(sequence#) from v$log_history; (should be matching with Primary)

10. Now Start RAC databases services (both Primary – in open & Standby – in mount)

srvctl start instance –d primary -n primarynode2 -o mount

Check if the mrp0 process is saying “APPLYING_LOG” on the new standby database. Check with below query.

select process,status from v$managed_standby;

If the logs are shipping and applying if not check the alert logs for any errors. The general error in an assymetrical rac is due to password files. If the logs not applying and you seeing like below error in the alert log of new primary.

PING[ARC2]: Heartbeat failed to connect to standby ‘swx’. Error is 16191.
Error 1017 received logging on to the standby

Solution : RECREATE THE PASSWORD FILES FOR NODE1 if failover is happening from NODE1

User format as orapw$ORACLE_SID for Standby database
eg: orapwstandbydatabase

User format as orapw$ORACLE_SID for node 1
eg: orapwprimarynode1

User format as orapw$ORACLE_SID for node 2
eg: orapwprimarynode2

Create the password file in folder $ORACLE_HOME/dbs on all nodes and DR server as Oracle(Database) user