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);

Oracle 12c Dataguard RAC Primary to RAC Standby with Dataguard Broker (on Exadata)

Setup for the Environment
——————-

Exadata X4-2 Quater Rack n Primary and Standby Site
Grid Version : 12.1.0.2
DB Home Version : 12.1.0.2

Primary, 2 Node RAC, db_unique_name = primeprd
Instance 2 : primeprd1
Instance 2 : primeprd2
TNS entry: PRIMEPRD, but below entry in tnsnames.ora files on both node with DB Home user, most likely it will be Oracle user.

PRIMEPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primeprd)
)
)

Standby, 2 Node RAC, db_unique_name= primedr
Instance 2 : primedr1
Instance 2 : primedr2
TNS ENTRY: PRIMEDR, but below entry in tnsnames.ora files on both node with DB Home user, most likely it will be Oracle user.

PRIMEDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost.domain.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primedr)
)
)

1. Enable Force Logging on the Database

 alter database force logging;

Database altered.

2.1 change the below parameter in Primary database.


ALTER SYSTEM SET db_recovery_file_dest='+RECOC1' scope=both sid='*';

System altered.

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(primeprd,primedr)' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primeprd' scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_2='SERVICE=primedr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primedr' scope=both sid='*';

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile sid='*';

alter system set log_archive_max_processes=8 scope=both sid='*';

alter system set fal_server=primedr scope=both sid='*';

alter system set fal_client=primeprd scope=both sid='*';

alter system set standby_file_management=AUTO scope=both sid='*';

alter system set db_file_name_convert=('+DATAC1/primedr/', '+DATAC1/primeprd/', '+RECOC1/primedr/', '+RECOC1/primeprd/') scope=both sid='*';

alter system set log_file_name_convert=('+DATAC1/primedr/', '+DATAC1/primeprd/', '+RECOC1/primedr/', '+RECOC1/primeprd/') scope=both sid='*';


-- Query to check if parameters are set correctly --

set linesize 500 pages 0
col value for a90
col name for a50
select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management');

select name, value
from v$parameter
where name in ('db_name','db_unique_name','log_archive_config', 'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2', 'remote_login_passwordfile',
'log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert',
'log_file_name_convert', 'standby_file_management');

3.1. verify the logfile using the below querys.

select group#,THREAD#,bytes/1024/1024,status,members from v$log;
select member from v$logfile;

3.2. Add the below logfile and standby logfile in primary database, make sure you have large redolog files. In this day and age, 100MB does not really cut it.

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 4 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ('+DATAC1','+RECOC1') SIZE 1G;

 select * from v$standby_log;

-- Add Standby Redolog files, number of standby redo log files = Redo logfile Groups + 2 --

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 9 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 10 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 14 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 15 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 16 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 17 ('+DATAC1','+RECOC1') SIZE 1G;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 18 ('+DATAC1','+RECOC1') SIZE 1G;

4. Take the rman backup using below command.

rman target / nocatalog
run
{
sql 'alter system archive log current';
backup as compressed backupset database plus archivelog format '/u03/rmanbkp/online/Primary_bkp_for_stndby_%U';
backup current controlfile for standby format '/u03/rmanbkp/online/stby.ctl';
sql 'alter system archive log current';
}

5.1. create the init file using the below command in primary database.

create pfile='/u03/rmanbkp/prime/pfile_for_standby.txt' from spfile;

— Primary PFILE —

primeprd1.__data_transfer_cache_size=0
primeprd2.__data_transfer_cache_size=0
primeprd1.__db_cache_size=7046430720
primeprd2.__db_cache_size=7147094016
primeprd1.__java_pool_size=67108864
primeprd2.__java_pool_size=67108864
primeprd1.__large_pool_size=301989888
primeprd2.__large_pool_size=301989888
primeprd2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
primeprd1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
primeprd1.__pga_aggregate_target=3221225472
primeprd2.__pga_aggregate_target=3221225472
primeprd1.__sga_target=9663676416
primeprd2.__sga_target=9663676416
primeprd1.__shared_io_pool_size=469762048
primeprd2.__shared_io_pool_size=469762048
primeprd1.__shared_pool_size=1644167168
primeprd2.__shared_pool_size=1577058304
primeprd1.__streams_pool_size=100663296
primeprd2.__streams_pool_size=67108864
*.audit_file_dest='/u03/app/oracle/admin/primeprd/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='12.1.0.2.0'
*.control_files='+DATAC1/PRIMEPRD/CONTROLFILE/current.660.914322763','+DATAC1/PRIMEPRD/CONTROLFILE/current.665.914322763'
*.cpu_count=16
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_domain=''
*.db_file_name_convert='+DATAC1/primedr/','+DATAC1/primeprd/','+RECOC1/primedr/','+RECOC1/primeprd/'
*.db_name='primeprd'
*.db_recovery_file_dest='+DATAC1'
*.db_recovery_file_dest_size=536870912000
*.db_writer_processes=6
*.ddl_lock_timeout=30
*.diagnostic_dest='/u03/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primeprdXDB)'
*.fal_client='primeprd'
*.fal_server='primedr'
primeprd2.instance_number=2
primeprd1.instance_number=1
*.log_archive_config='dg_config=(primeprd,primedr)'
*.log_archive_dest_1='LOCATION=+RECOC1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primeprd'
*.log_archive_dest_2='service=primedr LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primedr'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='log%t_%s_%r.arc'
*.log_file_name_convert='+DATAC1/primedr/','+DATAC1/primeprd/','+RECOC1/primedr/','+RECOC1/primeprd/'
*.nls_length_semantics='CHAR'
*.open_cursors=2000
*.optimizer_adaptive_features=FALSE
*.pga_aggregate_target=3072m
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.resource_manager_cpu_allocation=48
*.resource_manager_plan='DEFAULT'
*.session_cached_cursors=100
*.sessions=2272
*.sga_max_size=17179869184
*.sga_target=9216m
*.standby_file_management='AUTO'
*.temp_undo_enabled=TRUE
primeprd2.thread=2
primeprd1.thread=1
primeprd2.undo_tablespace='UNDOTBS2'
primeprd1.undo_tablespace='UNDOTBS1'

— DR PFILE —

primedr1.__data_transfer_cache_size=0
primedr2.__data_transfer_cache_size=0
primedr1.__db_cache_size=7046430720
primedr2.__db_cache_size=7147094016
primedr1.__java_pool_size=67108864
primedr2.__java_pool_size=67108864
primedr1.__large_pool_size=301989888
primedr2.__large_pool_size=301989888
primedr2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
primedr1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
primedr1.__pga_aggregate_target=3221225472
primedr2.__pga_aggregate_target=3221225472
primedr1.__sga_target=9663676416
primedr2.__sga_target=9663676416
primedr1.__shared_io_pool_size=469762048
primedr2.__shared_io_pool_size=469762048
primedr1.__shared_pool_size=1644167168
primedr2.__shared_pool_size=1577058304
primedr1.__streams_pool_size=100663296
primedr2.__streams_pool_size=67108864
*.audit_file_dest='/u03/app/oracle/admin/primeprd/adump' # Create on Both Nodes
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='12.1.0.2.0'
#*.control_files='+DATAC1/PRIMEPRD/CONTROLFILE/current.660.914322763','+DATAC1/PRIMEPRD/CONTROLFILE/current.665.914322763'
*.cpu_count=16
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_domain=''
*.db_file_name_convert='PRIMEPRD','PRIMEDR' ##### Changed for dataguard
*.db_unique_name='primedr' ##### Changed for dataguard
*.db_name='primeprd'
*.db_recovery_file_dest='+RECOC1'
*.db_recovery_file_dest_size=536870912000
*.db_writer_processes=6
*.ddl_lock_timeout=30
*.diagnostic_dest='/u03/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primedrXDB)' ##### Changed for dataguard
*.fal_client='primedr' ##### Changed for dataguard
*.fal_server='primeprd' ##### Changed for dataguard
primedr2.instance_number=2
primedr1.instance_number=1
*.log_archive_config='dg_config=(primeprd,primedr)'
*.log_archive_dest_1='LOCATION=+RECOC1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primedr' ##### Changed for dataguard
*.log_archive_dest_2='service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd' ##### Changed for dataguard
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='log%t_%s_%r.arc'
*.log_file_name_convert='PRIMEPRD','PRIMEDR' ##### Changed for dataguard
*.remote_listener='exadr-scan:1521' ##### Changed for dataguard
*.nls_length_semantics='CHAR'
*.open_cursors=2000
*.optimizer_adaptive_features=FALSE
*.pga_aggregate_target=3072m
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.resource_manager_cpu_allocation=48
*.resource_manager_plan='DEFAULT'
*.session_cached_cursors=100
*.sessions=2272
*.sga_max_size=17179869184
*.sga_target=9216m
*.standby_file_management='AUTO'
*.temp_undo_enabled=TRUE
primedr2.thread=2
primedr1.thread=1
primedr2.undo_tablespace='UNDOTBS2'
primedr1.undo_tablespace='UNDOTBS1'

cp -ip pfile_for_standby.txt pfile_to_apply.ora

vi pfile_to_apply.ora

— Actual PFILE after modifying all parameters —

primedr1.__data_transfer_cache_size=0
primedr2.__data_transfer_cache_size=0
primedr1.__db_cache_size=7046430720
primedr2.__db_cache_size=7147094016
primedr1.__java_pool_size=67108864
primedr2.__java_pool_size=67108864
primedr1.__large_pool_size=301989888
primedr2.__large_pool_size=301989888
primedr2.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
primedr1.__oracle_base='/u03/app/oracle'#ORACLE_BASE set from environment
primedr1.__pga_aggregate_target=3221225472
primedr2.__pga_aggregate_target=3221225472
primedr1.__sga_target=9663676416
primedr2.__sga_target=9663676416
primedr1.__shared_io_pool_size=469762048
primedr2.__shared_io_pool_size=469762048
primedr1.__shared_pool_size=1644167168
primedr2.__shared_pool_size=1577058304
primedr1.__streams_pool_size=100663296
primedr2.__streams_pool_size=67108864
*.audit_file_dest='/u03/app/oracle/admin/primeprd/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='12.1.0.2.0'
#*.control_files='+DATAC1/PRIMEPRD/CONTROLFILE/current.660.914322763','+DATAC1/PRIMEPRD/CONTROLFILE/current.665.914322763'
*.cpu_count=16
*.db_block_size=8192
*.db_create_file_dest='+DATAC1'
*.db_domain=''
*.db_file_name_convert='PRIMEPRD','PRIMEDR'
*.db_unique_name='primedr'
*.db_name='primeprd'
*.db_recovery_file_dest='+RECOC1'
*.db_recovery_file_dest_size=536870912000
*.db_writer_processes=6
*.ddl_lock_timeout=30
*.diagnostic_dest='/u03/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primedrXDB)'
*.fal_client='primedr'
*.fal_server='primeprd'
primedr2.instance_number=2
primedr1.instance_number=1
*.log_archive_config='dg_config=(primeprd,primedr)'
*.log_archive_dest_1='LOCATION=+RECOC1 valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=primedr'
*.log_archive_dest_2='service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='log%t_%s_%r.arc'
*.log_file_name_convert='PRIMEPRD','PRIMEDR'
*.remote_listener='exadr-scan:1521'
*.nls_length_semantics='CHAR'
*.open_cursors=2000
*.optimizer_adaptive_features=FALSE
*.pga_aggregate_target=3072m
*.processes=1500
*.remote_login_passwordfile='exclusive'
*.resource_manager_cpu_allocation=48
*.resource_manager_plan='DEFAULT'
*.session_cached_cursors=100
*.sessions=2272
*.sga_max_size=17179869184
*.sga_target=9216m
*.standby_file_management='AUTO'
*.temp_undo_enabled=TRUE
primedr2.thread=2
primedr1.thread=1
primedr2.undo_tablespace='UNDOTBS2'
primedr1.undo_tablespace='UNDOTBS1'

6. Create same RMAN backup directory in DR and copy the backup Piece and init file from Production to DR server.

Also do not forget to create directory for adump in path ‘/u03/app/oracle/admin/primeprd/adump’

startup nomount pfile='/u03/rmanbkp/prime/pfile_to_apply.ora';
rman target sys/*****@primeprd auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 28 10:05:31 2016

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

connected to target database: PRIMEPRD (DBID=111111111111111)
connected to auxiliary database: PRIMEPRD (not mounted)

show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIMEPRD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/rmanbkp/prime/autobackup_control_file_%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/rmanbkp/prime';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/app/oracle/product/12.1.0.2/payapps/dbs/snapcf_primeprd1.f'; # default

 DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

9. start the MRP process using the below command in SQL prompt.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

10. In case if you get error while receiving logging onto standby use the below command and create password files on both nodes of production and dr

— For Oracle 12c, do not create password files with this method. Go to section below for Dataguard Broker Setup

orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimeprd1 force=y ignorecase=y
orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimeprd2 force=y ignorecase=y
orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimedr1 force=y ignorecase=y
orapwd file=/u03/app/oracle/product/12.1.0.2/payapps/dbs/orapwprimedr2 force=y ignorecase=y

select * from v$pwfile_users;

Check by logging in from sqlplus from both production site and DR site from both nodes

From DR both nodes

sqlplus sys/*****@primeprd as sysdba

From Prod Both nodes

sqlplus sys/******@primedr as sysdba

Stop and start recovery again in DR

alter database recover managed standby database cancel;

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

select process,status from v$managed_standby;

11. Create the spfile and add the newly created control files

show parameter control_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATAC1/PRIMEDR/CONTROLFILE/current.636.915704919, +RECOC1/PRIMEDR/CONTROLFILE/current.15794.915704923

Add the controlfile string in the pfile you created earlier called pfile_to_apply.ora

cd /u03/rmanbkp/prime/
vi pfile_to_apply.ora

*.control_files=’+DATAC1/PRIMEDR/CONTROLFILE/current.636.915704919′,’+RECOC1/PRIMEDR/CONTROLFILE/current.15794.915704923′

shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

startup nomount pfile='/u03/rmanbkp/prime/pfile_to_apply.ora';

create spfile='+DATAC1/primedr/spfileprimedr.ora' from pfile='/u03/rmanbkp/prime/pfile_to_apply.ora';

shutdown immediate;

cd /u03/app/oracle/product/12.1.0.2/payapps/dbs/

vi initprimedr1.ora

–add only one line pointing to the SPFILE in ASM

spfile=’+DATAC1/primedr/spfileprimedr.ora’

startup mount pfile='/u03/app/oracle/product/12.1.0.2/payapps/dbs/initprimedr1.ora';

show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATAC1/primedr/spfileprimedr.
ora

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

12. Create cluster server using the below command.

srvctl add database -d primedr -o /u03/app/oracle/product/12.1.0.2/payapps -p +DATAC1/primedr/spfileprimedr.ora -r physical_standby -a DATAC1,RECOC1 -s MOUNT
srvctl add instance -d primedr -i primedr1 -n exadrdbadm01
srvctl add instance -d primedr -i primedr2 -n exadrdbadm02

srvctl add service -db onlndr -s onlnprod -preferred onlndr1,onlndr2
srvctl add service -db primedr -s primeprod -preferred primedr1,primedr2

srvctl config service -s onlnprod -db onlndr
srvctl config service -s primeprod -db primedr

srvctl stop database -d primedr

-- if gives error stop from sqlplus

srvctl start database -d primedr -o mount

— Start Recovery Again —

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

13.1. Add the below entries in both production nodes tnsnames.ora file as a oracle user. (Not required from ORacle 12c 12.1.0.2 onwards)

primeprd_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primeprd_DGMGRL)
)
)

13.2. Add the below entries in both DR nodes tnsnames.ora file as a oracle user.

primedr_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ********)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primedr_DGMGRL)
)
)

13.3. Add the below entries in production node1 listener.ora file as a grid user.

(SID_DESC =
(GLOBAL_DBNAME = primeprd_DGMGRL)
(ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps)
(SID_NAME = primeprd1)
)

13.4. Add the below entries in production node2 listener.ora file as a grid user.

(SID_DESC =
(GLOBAL_DBNAME = primeprd_DGMGRL)
(ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps)
(SID_NAME = primeprd2)
)

13.5. Add the below entries in DR node1 listener.ora file as a grid user.

(SID_DESC =
(GLOBAL_DBNAME = primedr_DGMGRL)
(ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps)
(SID_NAME = primedr1)
)

13.6. Add the below entries in DR node2 listener.ora file as a grid user.

(SID_DESC =
(GLOBAL_DBNAME = primedr_DGMGRL)
(ORACLE_HOME = /u03/app/oracle/product/12.1.0.2/payapps)
(SID_NAME = primedr2)
)

13.7. Using the below command to restart the listener.

$ srvctl stop listener
$ srvctl start listener

14.1. Set the below parameter in primary database.

alter system set dg_broker_config_file1 = '+DATAC1/PRIMEPRD/dr1primeprd.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATAC1/PRIMEPRD/dr2primeprd.dat' scope=both sid='*';
alter system set dg_broker_start = true scope=both sid='*';

14.2. Set the below parameter in standby database.

alter system set dg_broker_config_file1 = '+DATAC1/PRIMEDR/dr1primedr.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATAC1/PRIMEDR/dr2primedr.dat' scope=both sid='*';
alter system set dg_broker_start = true scope=both sid='*';

15.1. Configure the dataguard brocker using the below command from production database server

/home/oracle::11g- dgmgrl

connect sys/*****

create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

add database primedr as connect identifier is primedr maintained as physical;

Configuration "dg_primeprd" created with primary database "primeprd"

add database primedr as connect identifier is primedr maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

Failed.

remove configuration;
Removed configuration

Set the log_archive_dest_2 settings from both the Primary and Standby databases to be nothing.

alter system set log_archive_dest_2='' scope=both sid='*';

Disable then Enable the broker parameter on both the Primary and Standby databases.

— Primary

alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';

— Standby

alter system set dg_broker_start=false scope=both sid='*';
alter system set dg_broker_start=true scope=both sid='*';

On the Primary database create the broker configuration for the Primary and Standby database and this time it should work fine with no issues since the log archive destination 2 setting is not set, this is the workaround/solution.

/home/oracle::11g-- dgmgrl

connect sys/*****

create configuration dg_primeprd as primary database is primeprd connect identifier is primeprd;

add database primedr as connect identifier is primedr maintained as physical;

enable configuration;

Set back the log_archive_dest_2 on both production and dr

— In Production Database —

alter system set log_archive_dest_2='service=primedr LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primedr' scope=both sid='*';

— In DR Database —

alter system set log_archive_dest_2='service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd' scope=both sid='*';

If in Broker you get below error

 show configuration

Configuration - dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd - Primary database
primedr - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 40 seconds ago)

Check in Broker Log File.

Mon Jul 04 14:41:46 2016
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191

Failed to send message to site . Error code is ORA-01017.

Then re-create password files in ASM in 12c like below, the correct format is pwd

Primary

srvctl config database -d primeprd
orapwd file='+DATAC1/PRIMEPRD/pwdprimeprd' entries=10 dbuniquename=primeprd password=****** force=y ignorecase=y

Standby

srvctl config database -d primedr
orapwd file='+DATAC1/PRIMEDR/pwdprimedr' entries=10 dbuniquename=primedr password=****** force=y ignorecase=y

Check configuration now and the error should have vanished and the status is success

show configuration

Configuration - dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd - Primary database
primedr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 18 seconds ago)

— Try doing Switchover using DGMGRL Now —

 switchover to primedr
Performing switchover NOW, please wait...
Error: ORA-16644: apply instance not available

Failed.
Unable to switchover, primary database is still "primeprd"

edit database primedr set state='APPLY-ON' with apply instance='primedr1';

show database verbose primedr

Database - primedr

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 6.00 KByte/s
Active Apply Rate: 755.00 KByte/s
Maximum Apply Rate: 827.00 KByte/s
Real Time Query: OFF
Instance(s):
primedr1 (apply instance)
primedr2

switchover to primedr
Performing switchover NOW, please wait...
New primary database "primedr" is opening...
Oracle Clusterware is restarting database "primeprd" ...
Switchover succeeded, new primary is "primedr"

 show configuration

Configuration - dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr - Primary database
primeprd - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 40 seconds ago)

edit database primeprd set state='APPLY-ON' with apply instance='primeprd1';

show database verbose primeprd

Database - primeprd

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 307.00 KByte/s
Active Apply Rate: 0 Byte/s
Maximum Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
primeprd1 (apply instance)
primeprd2

DGMGRL

show configuration

Configuration - dg_primeprd

Protection Mode: MaxPerformance
Members:
primedr - Primary database
primeprd - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 55 seconds ago)

Connect to DR site broker to intiate switchover

switchover to primeprd ;
Performing switchover NOW, please wait...
Operation requires a connection to instance "primeprd1" on database "primeprd"
Connecting to instance "primeprd1"...
Connected as SYSDBA.
New primary database "primeprd" is opening...
Oracle Clusterware is restarting database "primedr" ...
Switchover succeeded, new primary is "primeprd

show configuration

Configuration - dg_primeprd

Protection Mode: MaxPerformance
Members:
primeprd - Primary database
primedr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 11 seconds ago

Add Members for Online Relog File Group on RAC ASM

Let’s say you have already created logfiles from Group 1 to Group 8 on a 2 node RAC cluster on Diskgroup :+RECOC1. Now it is always highly recommended to mutiplex the relog files. This is how we will add new members to existing groups.

First let us see the commands to create the logfile groups on +RECOC1

— Add Redo LogFile’s Thread 1 —

alter database add logfile thread 1 group 1 (‘+RECOC1’) size 1G;

alter database add logfile thread 1 group 2 (‘+RECOC1’) size 1G;

alter database add logfile thread 1 group 3 (‘+RECOC1’) size 1G;

alter database add logfile thread 1 group 4 (‘+RECOC1’) size 1G;
— Add Redo LogFile’s Thread 2 —

alter database add logfile thread 2 group 5 (‘+RECOC1’) size 1G;

alter database add logfile thread 2 group 6 (‘+RECOC1’) size 1G;

alter database add logfile thread 2 group 7 (‘+RECOC1’) size 1G;

alter database add logfile thread 2 group 8 (‘+RECOC1’) size 1G;

 

Now we will add membersto all above created groups  in diskgroup +DATAC1

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 1;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 2;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 3;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 4;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 5;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 6;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 7;

ALTER DATABASE
ADD LOGFILE MEMBER ‘+DATAC1’
TO GROUP 8;

 

 

Oracle CRS fails with CRS-5017 ORA-01017, what to do when even in init mode you cannot bring up CRS

After a memory upgrade in our RAC servers, the Cluster wasn’t coming up after a reboot. We first tried to start the CRS in init mode but even that failed. Upon checking the CRS alert log we discovered that the ASM was failing to start due to below error.

Error:

CRS-5017: The resource action “ora.asm start” encountered the following error:
ORA-01017: invalid username/password; logon denied
. For details refer to “(:CLSN00107:)” in “/u01/app/grid/diag/crs/<hostname>/crs/trace/ohasd_oraagent_grid.trc”

Solution:

We discovered the above error in the CRS alert log. After knocking our heads around, trying to start crsd in init mode without any success. We eventually discovered it was related to SQLNET.AUTHENTICATION_SERVICES parameter in sqlnet.ora file in the Grid Home. We removed the entry in the sqlnet.ora file from both nodes of the cluster and restarted the cluster. Everything was back to normal after that. Another day of troubleshooting CRS without any help from Oracle Support.

Move SPFILE to ASM RAC

— Move SPFILE to ASM RAC —

Already we have a PFILE called initmove.ora which was created previously from spfile

SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u02/app/oracle/product/11.2.0
/dbhome_1/dbs/spfiletest1.ora

As you can see the spfile is in Filesystem

SQL> create spfile=’+DATAC1/test/spfiletest.ora’ from pfile=’/u03/rman/initmove.ora’;

File created.

SQL> exit

Create a pfile called inittest.ora and add only below line only in it ( format is init<sid>.ora )
spfile=’+DATAC1/test/spfiletest.ora’

SQL> shutdown immediate;

Now Startup database with this pfile and automatically it will point to the SPFILE which we created in the first step

SQL> startup nomount pfile=’/u03/rman/inittest.ora’;

SQL> show parameter spfile;

NAME TYPE VALUE
———————————— ———– ——————————
spfile string +DATAC1/eccdr/spfiletest.ora
SQL> exit

As you see now the SPFILE is in ASM disk group. Modify the database with SRVCTL incase you didnt add, add like below

srvctl modify database -d test-p +DATAC1/eccdr/spfiletest.ora

To RAC or to not RAC

Mogens Norgaard ( He is Danish, and my Danish is rubbish. So i hope i got his name right :D) famous paper on why you probably dont need something as complex as Oracle RAC for having a highly available system.

http://www.my-idconcept.de/downloads/You_Probably_Dont_Need_RAC.pdf