ORA-01103: database name ‘MW’ in control file is not ‘MWTST’

This error most likely occurred when you duplicated a database and the DB_NAME parameter in spfile is not matching the one in the control file. Sometimes it so happened that after duplicating the database name in SPFILE with which you started the instance with is not matching the one in the control file.

Explanation:
=====================

The database name in the controlfile is not consistent with the db_name
parameter in the init.ora file.

Solution Step 1:

Change the DB_NAME parameter in spfile and bounce the database

STARTUP NOMOUNT

ALTER SYSTEM SET DB_NAME=ORIGINAL_DBNAME SCOPE=spfile;

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN RESETLOGS;

If you get like below error

ORA-19838: Cannot use this control file to open database then have to recreate the control file from trace . Go to Solution 2:

See Oracle support Doc ID 1906009.1

 

Solution Step 2:

 

Now the only way to bring up the cloned DB is to re-create controlfile from trace. Login to the source database and run below commands.

SQL> alter database backup controlfile to trace;

Check location of controlfile which is created to trace; checking in ALERT LOG

ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /u02/app/oracle/diag/rdbms/mw/mw1/trace/mw1_ora_146366.trc

Then Copy/Edit the Details of ControlFile from the block like below.

[code language=”sql”]

CREATE CONTROLFILE SET DATABASE “MW” RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 9344
LOGFILE
GROUP 1 (
‘+DATAC1/mwtst1/onlinelog/group_1.366.854619221’,
‘+RECOC1/mwtst1/onlinelog/group_1.504.854619221’
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
‘+DATAC1/mwtst1/onlinelog/group_2.367.854619389’,
‘+RECOC1/mwtst1/onlinelog/group_2.505.854619389’
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
‘+DATAC1/mwtst1/onlinelog/group_3.368.854619411’,
‘+RECOC1/mwtst1/onlinelog/group_3.506.854619411’
) SIZE 100M BLOCKSIZE 512,
GROUP 4 (
‘+DATAC1/mwtst1/onlinelog/group_4.365.854619175’,
‘+RECOC1/mwtst1/onlinelog/group_4.507.854619175’
) SIZE 100M BLOCKSIZE 512
DATAFILE
‘+DATAC1/mwtst1/datafile/SYSTEM.467.971185463’,
‘+DATAC1/mwtst1/datafile/SYSAUX.439.971266529’,
‘+DATAC1/mwtst1/datafile/USERS.473.971266529’,
‘+DATAC1/mwtst1/datafile/UNDOTBS1.468.971185463’,
‘+DATAC1/mwtst1/datafile/UNDOTBS2.466.971185463’,
‘+DATAC1/mwtst1/datafile/MQ.357.971266521’,
‘+RECOC1/mwtst1/datafile/AUDIT_TBS.1787.971185463′
CHARACTER SET AL32UTF8
;

[/code]

Save it as control_file.sql

Ensure you check the Datafile location and name, it should be same as in what resides on your filesystem. This is specially true when you using ASM and OMF together.

 

Now start the instance

 

SQL> startup nomount

SQL> @control_file.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

SQL> select open_mode,name from v$database;

OPEN_MODE NAME
——————– ———
READ WRITE MW

SQL> select * from v$controlfile;

STATUS
——-
NAME
——————————————————————————–
IS_ BLOCK_SIZE FILE_SIZE_BLKS
— ———- ————–

+RECOC1/mw/controlfile/current.2156.971360621
YES 16384 1878

Now change the database name in control file using nid utility

nid TARGET=system/****** DBNAME=MWTST SETNAME=Y

 

DBNEWID: Release 11.2.0.4.0 – Production on Wed Mar 21 14:27:18 2018

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

Connected to database MW (DBID=1312370394)

Connected to server version 11.2.0

Control Files in database:
+RECOC1/mw/controlfile/current.2156.971360621

Change database name of database MW to MWTST? (Y/[N]) => Y

Proceeding with operation
Changing database name from MW to MWTST
Control File +RECOC1/mw/controlfile/current.2156.971360621 – modified
Datafile +DATAC1/mwtst1/datafile/system.467.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/sysaux.439.97126652 – wrote new name
Datafile +DATAC1/mwtst1/datafile/undotbs1.468.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/users.473.97126652 – wrote new name
Datafile +DATAC1/mwtst1/datafile/undotbs2.466.97118546 – wrote new name
Datafile +DATAC1/mwtst1/datafile/mq.357.97126652 – wrote new name
Datafile +RECOC1/mwtst1/datafile/audit_tbs.1787.97118546 – wrote new name
Control File +RECOC1/mw/controlfile/current.2156.971360621 – wrote new name
Instance shut down

Database name changed to MWTST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

 

Change PARAMETER in PFILE or in Memory

SQL> alter system set db_name=’MWTST’ scope=spfile;

System altered.

SQL> show parameter cluster

NAME TYPE VALUE
———————————— ———– ——————————
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string

 

SQL> alter system set db_unique_name=’MWTST’ scope=spfile;

SQL> shutdown immediate;

SQL> startup mount

SQL> alter database open;

Database altered.

SQL> select open_mode,name from v$database;

OPEN_MODE NAME
——————– ———
READ WRITE MWTST

 

 

 

 

 

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

SYSAUX tablespace growing rapidly in Oracle, 12c Unified Audit Trail AUDSYS

The Unified Auditing in Oracle 12c can Grow the SYSAUX table to a large size. In our case it grew to about 100GB and We had to take action to make sure it did not grow any larger. First we query to identify what is residing inside the SYSAUX tablespace. In our case it was the AUDSYS schema which was taking the most space. All arrows pointed to Unified Auditing’s way

— Query to Check the SYSAUX Tablespace Occupants —

COLUMN “Item” FORMAT A25
COLUMN “Space Used (GB)” FORMAT 999.99
COLUMN “Schema” FORMAT A25
COLUMN “Move Procedure” FORMAT A40

SELECT occupant_name “Item”,
space_usage_kbytes/1048576 “Space Used (GB)”,
schema_name “Schema”,
move_procedure “Move Procedure”
FROM v$sysaux_occupants
ORDER BY 2
/
— Query to Check the SYSAUX Tablespace Largest Segments–

col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||’~’||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = ‘SYSAUX’ ORDER BY BLOCKS desc) where rownum < 11;
— Clean Up the Unified Audit Trail —

1. First set the Date, starting from where you need to have the Unified Auditing Records

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TO_TIMESTAMP(’15-SEP-2016 00:00:10.0′,’DD-MON-RRRR HH24:MI:SS.FF’),
rac_instance_number => 2);
END;
/
2. Since Unified Auditing caches the audit trail in memory to implement a ‘lazy write’ feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache

BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/

3. Execute the Purge Procedure

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

Useful Links:

https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG474

How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

 

Check ASM Diskgroup Space and Directory Size

The script below can be used to check the Disk Group Space Free and also Check Directory sizes for each Disk Group

The script is written by somebody at Pythian but i cannot re-collect the original link to the blog.

eg:
./asmcmd_du.sh

DiskGroup Total_MB Free_MB % Free
——— ——– ——- ——
DATAC1 15962112 11215880 70
DBFS_DG 415296 403740 97
RECOC1 3996000 3460272 86

./asmcmd_du.sh DATAC1

DiskGroup Total_MB Free_MB % Free
——— ——– ——- ——
DATAC1 15962112 10155732 63

DATAC1 subdirectories size

Subdir Used MB Mirror MB
—— ——- ———
CARATST/ 55646 111356
ECCTST/ 174912 349856
—— ——- ———
Total 2799978 5600788

Script Below :

[code language=”bash”]
#!/bin/bash
# Shadab Mohammad — 2016
#
# – If no parameter specified, show a du of each DiskGroup
# – If a parameter, print a du of each subdirectory
#

D=$1

#
# Colored thresholds (Red, Yellow, Green)
#
CRITICAL=90
WARNING=75

#
# Set the ASM env
#
ORACLE_SID=`ps -ef | grep pmon | grep asm | awk ‘{print $NF}’ | sed s’/asm_pmon_//’ | egrep "^[+]"`
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1

#
# A quick list of what is running on the server
#
ps -ef | grep pmon | grep -v grep | awk ‘{print $NF}’ | sed s’/.*_pmon_//’ | egrep "^([+]|[Aa-Zz])" | sort | awk -v H="`hostname -s`" ” | sed s’/, $//’

#
# Manage parameters
#
if [[ -z $D ]]
then # No directory provided, will check all the DG
DG=`asmcmd lsdg | grep -v State | awk ‘{print $NF}’ | sed s’/\///’`
SUBDIR="No" # Do not show the subdirectories details if no directory is specified
else
DG=`echo $D | sed s’/\/.*$//g’`
fi

#
# A header
#
printf "\n%25s%16s%16s%14s" "DiskGroup" "Total_MB" "Free_MB" "% Free"
printf "\n%25s%16s%16s%14s\n" "———" "——–" "——-" "——"

#
# Show DG info
#
for X in ${DG}
do
asmcmd lsdg ${X} | tail -1 |\
awk -v DG="$X" -v W="$WARNING" -v C="$CRITICAL" ‘\
BEGIN \
{COLOR_BEGIN = "\033[1;" ;
COLOR_END = "\033[m" ;
RED = COLOR_BEGIN"31m" ;
GREEN = COLOR_BEGIN"32m" ;
YELLOW = COLOR_BEGIN"33m" ;
COLOR = GREEN ;
}
{ FREE = sprintf("%12d", $8/$7*100) ;
if ((100-FREE) > W) {COLOR=YELLOW ;}
if ((100-FREE) > C) {COLOR=RED ;}
printf("%25s%16s%16s%s\n", DG, $7, $8, COLOR FREE COLOR_END) ; }’
done
printf "\n"

#
# Subdirs info
#
if [ -z ${SUBDIR} ]
then
(for DIR in `asmcmd ls ${D}`
do
echo ${DIR} `asmcmd du ${D}/${DIR} | tail -1`
done) | awk -v D="$D" ‘ BEGIN { printf("\n\t\t%40s\n\n", D " subdirectories size") ;
printf("%25s%16s%16s\n", "Subdir", "Used MB", "Mirror MB") ;
printf("%25s%16s%16s\n", "——", "——-", "———") ;}
{
printf("%25s%16s%16s\n", $1, $2, $3) ;
use += $2 ;
mir += $3 ;
}
END { printf("\n\n%25s%16s%16s\n", "——", "——-", "———") ;
printf("%25s%16s%16s\n\n", "Total", use, mir) ;} ‘
fi

#************************************************************************#
#* E N D O F S O U R C E *#
#************************************************************************#
[/code]

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

[code language=”sql”]
alter database force logging;
[/code]

Database altered.

2.1 change the below parameter in Primary database.

[code language=”sql”]

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

[/code]

3.1. verify the logfile using the below querys.

[code language=”sql”]
select group#,THREAD#,bytes/1024/1024,status,members from v$log;
select member from v$logfile;
[/code]

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.

[code language=”sql”]
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;
[/code]

4. Take the rman backup using below command.

[code language=”sql”]
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’;
}
[/code]

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

[code language=”sql”]
create pfile=’/u03/rmanbkp/prime/pfile_for_standby.txt’ from spfile;
[/code]

— Primary PFILE —

[code language=”sql”]
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′
[/code]

— DR PFILE —
[code language=”sql”]
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′
[/code]

cp -ip pfile_for_standby.txt pfile_to_apply.ora

vi pfile_to_apply.ora

— Actual PFILE after modifying all parameters —

[code language=”sql”]
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′
[/code]

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’

[code language=”sql”]
startup nomount pfile=’/u03/rmanbkp/prime/pfile_to_apply.ora’;
[/code]

[code language=”sql”]
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;

[/code]

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

[code language=”sql”]
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
[/code]

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

[code language=”sql”]
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
[/code]

[code language=”sql”]
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;
[/code]

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

From DR both nodes

[code language=”sql”]
sqlplus sys/*****@primeprd as sysdba
[/code]

From Prod Both nodes

[code language=”sql”]
sqlplus sys/******@primedr as sysdba
[/code]

Stop and start recovery again in DR

[code language=”sql”]
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;

[/code]

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

[code language=”sql”]
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

[/code]

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′

[code language=”sql”]
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;
[/code]

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’

[code language=”sql”]
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;

[/code]

12. Create cluster server using the below command.

[code language=”bash”]
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

[/code]

— Start Recovery Again —

[code language=”sql”]
alter database recover managed standby database using current logfile disconnect from session;
[/code]

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.

[code language=”sql”]
$ srvctl stop listener
$ srvctl start listener
[/code]

14.1. Set the below parameter in primary database.

[code language=”sql”]
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=’*’;
[/code]

14.2. Set the below parameter in standby database.

[code language=”sql”]
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=’*’;
[/code]

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

[code language=”bash”]
/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
[/code]

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

[code language=”sql”]
alter system set log_archive_dest_2=” scope=both sid=’*’;
[/code]

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

— Primary
[code language=”sql”]
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;
[/code]

— Standby
[code language=”sql”]
alter system set dg_broker_start=false scope=both sid=’*’;
alter system set dg_broker_start=true scope=both sid=’*’;
[/code]

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.

[code language=”bash”]
/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;
[/code]

Set back the log_archive_dest_2 on both production and dr

— In Production Database —

[code language=”sql”]
alter system set log_archive_dest_2=’service=primedr LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primedr’ scope=both sid=’*’;
[/code]

— In DR Database —

[code language=”sql”]
alter system set log_archive_dest_2=’service=primeprd LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primeprd’ scope=both sid=’*’;
[/code]

If in Broker you get below error

[code language=”bash”]
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.

[/code]

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

Primary

[code language=”bash”]
srvctl config database -d primeprd
orapwd file=’+DATAC1/PRIMEPRD/pwdprimeprd’ entries=10 dbuniquename=primeprd password=****** force=y ignorecase=y
[/code]

Standby

[code language=”bash”]
srvctl config database -d primedr
orapwd file=’+DATAC1/PRIMEDR/pwdprimedr’ entries=10 dbuniquename=primedr password=****** force=y ignorecase=y
[/code]

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

[code language=”bash”]
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)
[/code]

— Try doing Switchover using DGMGRL Now —

[code language=”bash”]
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
[/code]

Query to check sessions writing to flashcache on Exadata

Query to check sessions writing to flashcache on Exadata
select se.sid, sn.name, s.value, se.program from v$sesstat s natural join v$statname sn left join v$session se on (s.sid = se.sid) where sn.name in (‘physical write requests optimized’, ‘cell writes to flash cache’, ‘cell overwrites in flash cache’) and s.value <> 0 order by s.sid,name;

ORA-12801: error signaled in parallel query server P001 ORA-00600: internal error code, arguments: [kxspoac : EXL 1], [32], [1], [], …

Oracle Version Affected : 12.1.0.2

OS : OEL 6 Update 7, x64

This error is related to Parallel Query and we encountered it many times, once on test and once on production. The solution is to apply below patch

Patch 19201867: SR12.2PX_HYBRID_LOAD – TRC – ORA-600 [KXSPOAC : EXL 1]

It is due to a bug, as mentioned in Oracle Support site

Bug 19201867 – Parallel slave process fails with ORA-600 [kxspoac : EXL 1] (Doc ID 19201867.8)

You can search the patch from Oracle support website and download it specific to your PSU. We had  to apply for Proactive BP 12.1.0.2.160419

The patch has to be applied in the Oracle DB home and all instances under that DB home need to be stopped before applying the patch. Since we were already on the BP 12.1.0.2.160419 there was no conflict in the application

1.Download the patch zip file

p19201867_12102160419DBEngSysandDBIM_Linux-x86-64.zip

2. Unzip the patch in directory

$ unzip -d <PATCH_TOP_DIR> p19201867_12102160419DBEngSysandDBIM_Linux-x86-64.zip

3. Run Pre-requsite check

$ cd <PATCH_TOP_DIR>/19201867
$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

If no conflicts found proceed further, if there is some conflict open SR with Oracle to investigate further

4. Stop all databases on both nodes under the Oracle Home to where you are applying the patch

5. Apply the patch

$ opatch apply

6. Verify application

$ opatch lsinventory | grep 19201867

Create ACFS Filesystem Oracle 12c Linux 12.1.0.2

— Create ACFS filesystem on 12c Linux Exadata —
1. Create a volume in ASM

ASMCMD [+] > volcreate -G datac1 -s 500G ACFS_VOL

If you get like below error

ORA-15032: not all alterations performed
ORA-15479: ASM diskgroup does not support volumes
ORA-15221: ASM operation requires compatible.asm of 12.1.0.2.0 or higher (DBD ERROR: OCIStmtExecute

Check the Current Compatibility for the Diskgroup

select group_number, name,compatibility, database_compatibility from v$asm_diskgroup
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
———— —————————— ———————————————————— ————————————————————
1 DATAC1 12.1.0.1.0 11.2.0.2.0
2 DBFS_DG 12.1.0.0.0 11.2.0.2.0
3 RECOC1 12.1.0.1.0 11.2.0.2.0
SQL> alter diskgroup DATAC1 set attribute ‘compatible.asm’=’12.1.0.2.0’;

Diskgroup altered.

SQL> alter diskgroup RECOC1 set attribute ‘compatible.asm’=’12.1.0.2.0’;

Diskgroup altered.

SQL> alter diskgroup DBFS_DG set attribute ‘compatible.asm’=’12.1.0.2.0′;

Diskgroup altered.

SQL> select group_number, name,compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
———— —————————— ———————————————————— ————————————————————
1 DATAC1 12.1.0.2.0 11.2.0.2.0
2 DBFS_DG 12.1.0.2.0 11.2.0.2.0
3 RECOC1 12.1.0.2.0 11.2.0.2.0

Run again the volcreate command
ASMCMD [+] > volcreate -G datac1 -s 500G ACFS_VOL
2. Check the volume information
ASMCMD [+] > volinfo -G datac1 ACFS_VOL

Diskgroup Name: DATAC1

Volume Name: ACFS_VOL
Volume Device: /dev/asm/acfs_vol-45
State: ENABLED
Size (MB): 512000
Resize Unit (MB): 64
Redundancy: MIRROR
Stripe Columns: 8
Stripe Width (K): 1024
Usage:
Mountpath:
sqlplus “/as sysasm”

SELECT volume_name, volume_device FROM V$ASM_VOLUME
WHERE volume_name =’ACFS_VOL’;

VOLUME_NAME
——————————
VOLUME_DEVICE
——————————————————————————–
ACFS_VOL
/dev/asm/acfs_vol-45

3. Create a file system with the Oracle ACFS mkfs command using output of above command

With root user run below command

/sbin/mkfs -t acfs /dev/asm/acfs_vol-45
mkfs.acfs: version = 12.1.0.2.0
mkfs.acfs: on-disk version = 39.0
mkfs.acfs: volume = /dev/asm/acfs_vol-45
mkfs.acfs: volume size = 536870912000 ( 500.00 GB )
mkfs.acfs: Format complete.
4. Register the file system with the acfsutil registry command.

Create a directory called ACFS

cd /
mkdir /ACFS

/sbin/acfsutil registry -a /dev/asm/acfs_vol-45 /ACFS

acfsutil registry: mount point /ACFS successfully added to Oracle Registry
Imp Note 1: Registering an Oracle ACFS file system also causes the file system to be mounted automatically whenever Oracle Clusterware or the system is restarted.
Imp Note 2: Oracle ACFS registration (acfsutil registry) is not supported in an Oracle Restart (standalone) configuration, which is a single-instance (non-clustered) environment.
5. Verify if ACFS filesystem mounted automatically

$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VGExaDb-LVDbSys1
30G 17G 12G 59% /
tmpfs 252G 22G 231G 9% /dev/shm
/dev/sda1 496M 54M 418M 12% /boot
/dev/mapper/VGExaDb-LVDbOra1
99G 57G 37G 61% /u01
/dev/mapper/VGExaDb-LVDbOra2
197G 68G 119G 37% /u02
/dev/mapper/VGExaDb-LVBkp1
985G 288G 648G 31% /u03
/dev/asm/acfs_vol-45 500G 1.1G 499G 1% /ACFS
As you can see from above output the ACFS filesystem moutned automatically after registration
6. If you did not register the ACFS filesystem it will not mount automatically, you can mount the ACFS filesystem manually using below command

As root user

/bin/mount -t acfs /dev/asm/acfs_vol-45 /ACFS

7. Give appropriate permissions to the filesystem required by Oracle users
chown -R oracle:dba /ACFS

su – oracle

cd /ACFS

touch abc.txt

Enable WriteBack Cache for Exadata

Writeback cache is a feature of cache where the blocks are written to flash first before flushing to the disk. Writethrough is the default setting for Exadata cells, where the data is written first to disk and moved to flashcache for read purpose.

Download the setWBFC.sh script from Oracle support website

[root@exatestdbadm01 ~]# dcli -g cell_group -l root “cellcli -e ‘list cell attributes flashcachemode'”
exatestceladm01: WriteThrough
exatestceladm02: WriteThrough
exatestceladm03: WriteThrough

The Flashcache mode is in WriteBack mode in the Current Setup

[root@exatestdbadm01 ~]# dcli -g cell_group -l root cellcli -e list flashcache detail

[root@exatestdbadm01 u03]# mkdir writeback

[root@exatestdbadm01 u03]# cd writeback/

[root@exatestdbadm01 writeback]# unzip setWBFC_10020_20160418.zip
Archive: setWBFC_10020_20160418.zip
inflating: setWBFC_10020_20160418/setWBFC.sh
inflating: setWBFC_10020_20160418/wbfc_FLUSH.sh
inflating: setWBFC_10020_20160418/WBFC_README.txt

[root@exatestdbadm01 writeback]# cd setWBFC_10020_20160418

[root@exatestdbadm01 setWBFC_10020_20160418]# ls -ltr
total 168
-rw-r–r– 1 root root 683 Apr 18 16:24 wbfc_FLUSH.sh
-rw-r–r– 1 root root 144383 Apr 18 16:24 setWBFC.sh
-rw-r–r– 1 root root 14574 Apr 18 20:06 WBFC_README.txt

[root@exatestdbadm01 setWBFC_10020_20160418]# cd ..
[root@exatestdbadm01 writeback]# chmod -R 777 setWBFC_10020_20160418
[root@exatestdbadm01 writeback]# cd setWBFC_10020_20160418
— To run pre-req checks for enabling write back flash cach —

[root@exatestdbadm01 setWBFC_10020_20160418]# ./setWBFC.sh -g /root/cell_group -l /tmp -m WriteBack -o rolling -p
— To run pre-req checks for disabling write back flash cache —

[root@exatestdbadm01 setWBFC_10020_20160418]# ./setWBFC.sh -g /root/cell_group -l /tmp -m WriteThrough -o rolling -p
— Now run the Actual Script to Execute and change to WriteBack mode —

[root@exatestdbadm01 setWBFC_10020_20160418]# ./setWBFC.sh -g /root/cell_group -l /tmp -m WriteBack -o rolling

2016-05-19 14:37:56
Skipping STEP 9: Waiting for grid disks to sync not required.
2016-05-19 14:37:56
Validating inventory for griddisks
2016-05-19 14:38:01
Validation of griddisk: [PASSED]
2016-05-19 14:38:01
Validating inventory for flashdisks
2016-05-19 14:38:13
Validation of flashdisk: [PASSED]
2016-05-19 14:38:13
Validating inventory for flashsize
2016-05-19 14:38:14
Validation of flashsize: [PASSED]
2016-05-19 14:38:14
Setting flash cache to WriteBack completed successfully.
— To Check the Mode of FlashCache —

[root@exatestdbadm01 ~]# dcli -g ~/cell_group -l root cellcli -e “list cell attributes flashcachemode”
exatestceladm01: WriteBack
exatestceladm02: WriteBack
exatestceladm03: WriteBack

Install GUI X11 packages and VNC Server on Exadata Compute Nodes

Recently we patched our Exadata machines to the April 2016 QFSP. On running DBCA we got X11 forwarding error and upon further investigation we discovered that X11 related packages RPM are removed in the new compute node patches.

Image version: 12.1.2.3.1.160411

On Oracle support website there is a note about it:

Refer MOS for more information –> Unable to run graphical tools (gui) (runInstaller, dbca and dbua) on Exadata 12.1.2.1.0 – 12.1.2.2.0 (Doc ID 1969308.1)

In addition to adding pack the X11 packages we also installed VNC Server(tigervnc), which can be a useful thing to do some basic GUI stuff.

— Install GUI Packages back in Exadata —

1. Download OEL6 (OEL6.7) Linux from E-delivery website as image

2. FTP file to serve, preferably to Cluster file system to do on both nodes

3. Create a directory and mount the OEL6 media

mkdir /mnt

— IF exists just continue

mount -o loop /ACFS/V77197-01.iso /mnt
4. Change the REPO, Edit /etc/yum.repos.d/Exadata-computenode.repo

Remove old information and add below lines

[ol6_iso]
name=Oracle Exadata DB server
baseurl=file:///mnt
gpgcheck=0
enabled=1
5. Validate the repository

yum list –disablerepo=* –enablerepo=ol6_iso
6. Install the X11 packacges

yum –disablerepo=* –enablerepo=ol6_iso install xorg-x11-xauth

–Update 2 –: starting 12.1.2.2.0 this package is not mandatory for X applications, so you can run below if your imageinfo version is more than it–

–Update 1–: I had a conversation with Oracle Development team after posting this blog post and interestingly they could not re-produce the same scenario in their lab. We upgraded from OL5 to OL6 on the compute nodes with almost 2 years of difference between the image versions. If your compute node versions is already OL6 then most likely you should not encounter the above scenario (that’s what the Oracle development team told me atleast).  If you do encounter it then log a SR with Oracle and get their blessings before proceeding.

yum –disablerepo=* –enablerepo=ol6_iso install xorg-x11-utils
If packages already exist remove the pakages and reinstall since it will fix the library dependencies
yum –disablerepo=* –enablerepo=ol6_iso remove xorg-x11-utils
yum –disablerepo=* –enablerepo=ol6_iso install xorg-x11-utils
7. Edit /etc/ssh/sshd_config to enable X11Forwarding

vi /etc/ssh/sshd_config

Below values should not be commented

X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes

UseDNS yes
8. Restart the SSHD service to reflect the new changes
service sshd restart
Stopping sshd: [ OK ]
Starting sshd: [ OK ]
9. Run any GUI utility to check, export the display if required

export DISPLAY=10.10.10.1:0.0

dbca
10. Install VNC Server

yum –disablerepo=* –enablerepo=ol6_iso install vnc-server
11. Configure VNC Server for oracle user
vi /etc/sysconfig/vncservers

— Add / Edit Parameters like below in file, make sure the 2 lines are uncommented

VNCSERVERS=”2:oracle”
VNCSERVERARGS[2]=”-geometry 800×600 -nolisten tcp -localhost”

— Set VNC password for oracle user —

su – oracle

vncpasswd

Password:
Verify:

— Login back as root user and start the vnc server service —

service vncserver start
New ‘exatestdbadm01.domain.com:2 (oracle)’ desktop is exatestdbadm01.domain.com:2

Creating default startup script /home/oracle/.vnc/xstartup
Starting applications specified in /home/oracle/.vnc/xstartup
Log file is /home/oracle/.vnc/exatestdbadm01.domain.com:2.log

[ OK ]
Login with any VNC viewer to test

— To Stop Service Run —

service vncserver stop
Shutting down VNC server: 2:oracle [ OK ]