Oracle EBS-12.2 High Availability using Active DataGuard

This document describes how to configure an Oracle EBusinessSuite Release 12 environment to use Oracle

Database 11gR2 as physical standby.

 Before You jump to configuration You need to understand the Directory Structure of EBS suit.

                         

 First we need to configure Production Database for Standby. Here, our Production DB name is PROD and the target Standby Database name is PRODDG.

STPES#1

 Connect to PROD Database. Check the archiving status by following SQL . Before we do any change at Production database Please make sure that you’ve taken a RMAN full backup. Without taken Full backup please do not proceed.

SQL> archive log list

Database log mode

No Archive Mode

Automatic archival Disabled

STPES#2

If  the query show  Automatic archival   Disabled  then Shutdown the Database .Startup at mount stage and execute the following sql statements .

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

Database altered.

Then enable the force logging on same Database mode.

SQL> alter database force logging;

Database altered.

Then Open the Database by following SQL statements.

SQL> alter database open;

Database altered.

Then check the database by following SQL.

SQL> select name, force_logging from v$database;

NAME      FOR

--------- ---

PROD      YES

SQL> archive log list;

Database log mode       Archive Mode

Automatic archival       Enabled

Archive destination       /u01/oracle/PROD/db/apps_st/data/archive/

Oldest online log sequence     21

Next log sequence to archive   24

Current log sequence       24

# Now Prepare the PROD DB parameter for Standby DB

STPES#3

SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

standby_file_management              string      MANUAL

SQL> alter system set standby_file_management = 'AUTO';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/U01/ORACLE/PROD/DB/APPS_ST/DATA/ARCHIVE/ MANDATORY' ;

System altered.

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=PRODDG VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) LGWR ASYNC=20480 DB_UNIQUE_NAME=PRODDG OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODDG)' ;

System altered.

SQL> ALTER SYSTEM SET FAL_SERVER = PRODDG;

System altered.

SQL> ALTER SYSTEM SET FAL_CLIENT= PROD;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 300G;

System altered.

SQL> alter system set DB_RECOVERY_FILE_DEST= '/u01/oracle/PROD/db/apps_st/data/archive/' ;

System altered.

STPES#4

Add the standby logfile  on same location where your logfile is stored. Check the location and also the size of redolog  of logfile by following sql statements.

SQL> select bytes/1024/1024 as MB from v$log;

SQL> select GROUP#,MEMBER from v$logfile;

    GROUP#

----------

MEMBER

--------------------------------------------------------------------------------

         1

/u01/oracle/PROD/db/apps_st/data/log01a.dbf

         1

/u01/oracle/PROD/db/apps_st/data/log01b.dbf

         2

/u01/oracle/PROD/db/apps_st/data/log02a.dbf

Then Add the standby logfile Group

SQL> alter database add standby logfile group 5 ('/u01/oracle/PROD/db/apps_st/data/log05b.dbf','/u01/oracle/PROD/db/apps_st/data/log05a.dbf') size 500M; 

Database altered.

SQL> Alter database add standby logfile group 6 ('/u01/oracle/PROD/db/apps_st/data/log06b.dbf','/u01/oracle/PROD/db/apps_st/data/log06a.dbf') size 500M ;

Database altered.

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

System altered.

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      PROD

STPES#5

# If you know the password SYS user then you need not change the password file but if you don’t know you need to chnge it by following way as Oracle user . Before you do it take copy of existing orapwPROD  file.

[oracle@sml-test ]$ orapwd file=/u01/oracle/PROD/db/tech_st/11.2.0.3/dbs/orapwPROD password=oracle entries=10 ignorecase=y

STPES#6

Now create Pfile from spfile on PROD db.

SQL> create pfile from spfile;

Go to the directory of /u01/oracle/PROD/db/tech_st/11.2.0.3/dbs and find out the both orapwPROD and initPROD.ora file and copy them to your machine. Rename the initPROD.ora to initPRODDG.ora .  Now You need to edit the  initPRODDG.ora  for standby. 

· Depending on your configuration and file locations on the standby you might needed to change the

AUDIT_FILE_DEST, CONTROL_FILES and DISPATCHERS parameters (there may be others, but be

careful, not everything should be changed this way).

· In LOG_ARCHIVE_DEST_1 change db_unique_name to the standby SID (in this case PRODDG).

· In LOG_ARCHIVE_DEST_2 change the service and db_unique_name to the primary SID (in this case

PROD).

· Change FAL_SERVER to the primary SID (PROD).

· Add the following parameters:

. db_unique_name=PRODDG

                                                  Sample of Pfile for sTandby:

STPES#7

# Add the following two TNS on   tnsnames.ora File in  PROD

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sml-test.bsrm.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD)

      (UR = A)

    )

  )

PRODDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-test.bsrm.com)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVER = DEDICATED)

      (INSTANCE_NAME= PROD)

      (SERVICE_NAME =PRODDG)

      (SID = PROD)

      (UR = A)

    )

   )

STPES#8

 Run the Application Tier and Database Tier PreClone Scripts

[oracle@sml-test archive]$  /u01/oracle/PROD/db/tech_st/11.2.0.3/appsutil/scripts/PROD_sml-test

 [oracle@sml-test archive]$    perl adpreclone.pl dbTier

# Pre Clone script of Apps Tire should find out from here...

[oracle@sml-test archive]$  /u01/oracle/PROD/inst/apps/PROD_sml-test/admin/scripts

  [oracle@sml-test archive]$  perl adpreclone.pl appsTier

# Now Copy Your DB Home And APPS tire to the Standby Machine.

Preparing the Standby environment:

On Standby Machine go to the directory /u01/oracle/PROD/db/tech_st/11.2.0.3/dbs  and place the  orapwPROD and  initPRODDG.ora   file.

STPES#9

# Now prepare the TNSNAME.ora file  on Stnadby Machine.

Add the following TNS entry on TNSNAME.ora file.

 PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sml-test.bsrm.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD)

      (UR = A)

    )

  )

PRODDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-test.bsrm.com)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVER = DEDICATED)

      (INSTANCE_NAME= PROD)

      (SERVICE_NAME =PRODDG)

      (SID = PROD)

      (UR = A)

    )

   )

 invite the communication by tnsping command.

STPES#10

THEN CONNECT TO STANDBY DB.

SQLPLUS /NOLOG

SQL> startup nomount pfile=/u01/oracle/PROD/db/tech_st/11.2.0.3/dbs/initPRODDG.ora;

# Then create Spfile from Pfile by following sql statements and shutdown the DB and startup on NOMOUNT stage.

SQL> create spfile from  pfile='/u01/oracle/PROD/db/tech_st/11.2.0.3/dbs/initPRODDG.ora'

SQL> shutdown

SQL> startup nomount

SQL> exit

STPES#11

Creating the Standby Database:

From Standby Machine (dg-test.bsrm.com) connect to RMAN as oracle user .

$RMAN

RMAN> connect target sys/oracle@PROD

RMAN> connect auxiliary sys/oracle@PRODDG

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

Wait till Finishing this stage .Then Connect to Standby db as sys user.

Execute The Following Sql statements for starting recovery process.

STPES#12

SQL> alter database mount standby database;

Database altered

SQL> alter database recover managed standby database disconnect from session;

Database altered

STPES#13

 Connect to The Primary Database ( PROD) and execute the following sql  statements to Start Shipping Redo from the Primary to the Standby Database Server.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

System altered.

Now your standby Database is ready for taking responsibility in case of failure on Production.

In Case of failure of PROD or during maintenance of Production you need to change the role of both PROD and PRODDG database.

# IN PRIMARY DATABASE (PROD) EXECUTE THE FOLLOWUNG SQL STATEMENTS FOR ROLE CHANGING.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN ;

 Database altered.

# The Shutdown the Database and Startup on nomount stage with pfile. Create the spfile and put the Database on recovery mood.

SQL> conn sys as sysdba

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=defer ;

System altered.

SQL> Shutdown Immediate;

SQL> startup nomount pfile=/u01/oracle/PROD/db/tech_st/11.2.0.3/dbs/initPROD.ora;

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

In That position both of your PROD and PRODDG are at Standby mode. Now Connect to the Standby Database and execute The following sql statements for switching your standby to Primary mode.

# IN STANDBY DATABASE (PRODDG) EXECUTE THE FOLLOWUNG SQL STATEMENTS FOR ROLE CHANGING.

SQL> conn sys as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL>  Shutdown Immediate

SQL> startup

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=enable;

System altered.

Now connect as APPS user ,

SQL> conn apps/apps

Connected.

SQL>exec fnd_net_services.remove_system('PROD');

SQL>commit;

SQL>exec fnd_conc_clone.setup_clean ;

# UPDATE THE TABLE WITH THE VALUE OF YOUR STANDBY MACHINE NAME IN REPLACE OF PRODUCTION MACHINE.

SQL> update apps.fnd_concurrent_requests

set logfile_node_name = 'DG-TEST',

outfile_node_name =  'DG-TEST'

where logfile_node_name = 'SML-TEST'

and outfile_node_name = 'SML-TEST'

SQL>update apps.fnd_conc_req_outputs set file_node_name= 'DG-TEST'

where file_node_name= 'SML-TEST';

# Configure the APPS for connecting with your new production databae (PRODDG).

Go to the directory <RDBMS_ORACLE_HOME>/appsutil/scripts/<context> to create new context file. 

[oracle@dg-test]$ cd  /u01/oracle/PROD/db/tech_st/11.2.0.3/appsutil/scripts/PROD_dg-test

 [oracle@dg-test]$  sh adautocfg.sh

When this completes, stop and start the listener on the new primary database server (PRODDG):

$ lsnrctl stop

$ lsnrctl start

On the new standby server (PROD) , stop and start the listener for standby services:

$ lsnrctl stop

$ lsnrctl start

Follow the directory to execute adclonectx.pl   script.

[oracle@dg-test]$ cd /u01/oracle/PROD/apps/apps_st/comn/clone/bin

[oracle@dg-test]$  perl adclonectx.pl  /u01/oracle/PROD/inst/apps/PROD_prodapps1/appl/admin/PROD_prodapps1 .xml

[oracle@dg-test]$ cd /u01/oracle/PROD/apps/apps_st/appl/ad/12.0.0/bin

[oracle@dg-test]$  perl adconfig.pl  contextfile=/u01/oracle/PROD/inst/apps/PROD_dg-test/appl/admin/PROD_dg-test.xml run=INSTE8

# Execute the DB Tire clone scripts.

[oracle@dg-test]$   lsnrctl stop

export ORACLE_HOME=/u01/oracle/PROD/db/tech_st/11.2.0.3

export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/site_perl/5.10.0:$ORACLE_HOME/appsutil/perl

export PATH=$ORACLE_HOME/perl:$ORACLE_HOME/perl/lib:$ORACLE_HOME/perl/bin:$PATH

[oracle@dg-test]$   cd $ORACLE_HOME/appsutil/clone/bin

[oracle@dg-test]$  perl adcfgclone.pl dbTechStack

Answer the following questions to configure ORACLE_HOME

?              Target System Hostname (virtual or normal) [rman] : [ENTER]

?              Target Instance is RAC (y/n) [n] : [ENTER]

?              Target System Database SID : PROD {should be same as Source Database}

?              Target System Base Directory : /u01/oracle/PROD

?              Target System utl_file_dir Directory List : /usr/tmp

?              Number of DATA_TOP's on the Target System [1] : [ENTER]

?              Target System DATA_TOP Directory 1 [/u01/oracle/PROD/db/apps_st/data] : [ENTER]

?              Target System RDBMS ORACLE_HOME Directory [/u01/oracle/PROD/db/tech_st/11.1.0] : /u01/oracle/PROD/db/tech_st/11.2.0.3

?              Do you want to preserve the Display [rman-test:0.0] (y/n)  : n

?              Target System Display [rman:0.0] : [ENTER]

?              Do you want the the target system to have the same port values as the source system (y/n) [y] ? : [ENTER]

# Execute The APPS Tire Clone script

# [oracle@dg-test]$   cd /u01/oracle/PROD/apps/apps_st/comn/clone/bin

 [oracle@dg-test]$     perl adcfgclone.pl appsTier

Answer the following questions to configure Application Node

?              Target System Hostname (virtual or normal) [rman] : [ENTER]

?              Target System Database SID : PROD

?              Target System Database Server Node [rman]: [ENTER]

?              Target System Database Domain Name [bsrm.com] :

?              Target System Base Directory : /u01/oracle/PROD

?              Target System Tools ORACLE_HOME Directory [/u01/oracle/PROD/apps/tech_st/10.1.2]: [ENTER]

?              Target System Web ORACLE_HOME Directory [/u01/oracle/PROD/apps/tech_st/10.1.3] : [ENTER]

?              Target System APPL_TOP Directory [/u01/oracle/PROD/apps/apps_st/appl] : [ENTER]

?              Target System COMMON_TOP Directory [/u01/oracle/PROD/apps/apps_st/comn] : [ENTER]

?              Target System Instance Home Directory [/u01/oracle/PROD/inst] : [ENTER]

?              Target System Root Service [enabled] : [ENTER]

?              Target System Web Entry Point Services [enabled] : [ENTER]

?              Target System Web Application Services [enabled] : [ENTER]

?              Target System Batch Processing Services [enabled] : [ENTER] ( If Disabled make it enabled if it’s a single node environment)

?              Target System Other Services [disabled] : [ENTER]

?              Do you want to preserve the Display [rman-test:0.0] (y/n)  : n

?              Target System Display [rman:0.0] : [ENTER]

?              Do you want the the target system to have the same port values as the source system (y/n) [y] ? : [ENTER]

?              UTL_FILE_DIR on database tier consists of the following directories.

?             

?              1. /usr/tmp

?              2. /usr/tmp

?              3. /u01/oracle/PROD/db/tech_st/11.2.0.3/appsutil/outbound/PROD_oracleebs

?              4. /usr/tmp

# EDIT THE TNSNAME.ora file

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sml-test.bsrm.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD)

      (UR = A)

    )

  )

PRODDG =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dg-test.bsrm.com)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVER = DEDICATED)

      (INSTANCE_NAME= PROD)

      (SERVICE_NAME =PRODDG)

      (SID = PROD)

      (UR = A)

    )

   )

$ lsnrctl stop

$ lsnrctl start