Configuring Oracle Wallet for Multiple RAC Databases sharing same Oracle Home

1 ) Set the environment variable also via srvctl @ oracle user.

export ORACLE_UNQNAME=’$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}’

srvctl setenv database -d secdev -T “ORACLE_UNQNAME=secdev”

2) Create wallet directory on both nodes @ oracle user.

mkdir -p /u01/app/oracle/WALLETS/secdev

3) Configure sqlnet.ora as follows on both nodes @ oracle user.

If the databases share the same ORACLE_HOME, they also share the same sqlnet.ora file in $TNS_ADMIN. In order to access their individual wallet, the DIRECTORY entry for the ENCRYPTION_WALLET_LOCATION needs to point each database to its own wallet location:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/WALLETS/$ORACLE_UNQNAME/)))

The names of the subdirectories under /u01/app/oracle/WALLETS/  reflect the ORACLE_UNQNAME names of the individual databases. That’s why we created the directory ‘secdev’ in step 2. For each Database unique name, create one directory.

4) Create the wallet by using node1 login @ oracle user.

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “*******”;

5) Open wallet by using node1 login @ oracle user.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “welcome1”;

6) To configure auto login for wallet by using both nodes login @ oracle user.

orapki wallet create -wallet /u01/app/oracle/WALLETS/secdev -auto_login

7) copy the below files to node2 @ oracle user.

scp ewallet.p12  node2host:/u01/app/oracle/WALLETS/secdev
8) Change permissions on directory and files in both nodes @ oracle user.

cd /u01/app/oracle/WALLETS
chmod 700 secdev
cd secdev
chmod 600 ewallet.p12

9) After initially creating the encryption wallet (and optionally a (local) auto-open wallet), navigate to the directory that stores the Oracle Wallet and set the ‘immutable’ bit with: on both nodes @ root user.

# chattr +i ewallet.p12
# chattr +i cwallet.sso
10. You can have different wallets for different databases, all we have to do this create the directories for the unique name of databases and set the environment using srvctl utlity
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /orabin/app/oracle/WALLETS/$ORACLE_UNQNAME/)))

srvctl setenv database -d testdb -T “ORACLE_UNQNAME=testdb”

srvctl setenv database -d ftestdb -T “ORACLE_UNQNAME=ftestdb”

mkdir -p /u01/app/oracle/WALLETS/testdb

mkdir -p /u01/app/oracle/WALLETS/ftestdb

And for the other databases  proceed as above steps

Link to Oracle Whitepaper for best practises for TDE

http://www.oracle.com/technetwork/database/security/twp-transparent-data-encryption-bes-130696.pdf

 

 

Remove Whitespace Perl

Remove all whitespace from file

perl -lape ‘s/\s+//sg’ INPUT.TXT > OUTPUT.TXT

Remove whitespace more than one space

perl -wlpe ‘~ s/ +/ /g’ INPUT.TXT > OUTPUT.TXT

Remove whitespace more than one space and replace with ‘_’ character

perl -wlpe ‘~ s/ +/_/g’ INPUT.TXT > OUTPUT.TXT

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