Oracle Applications DBA Blog

Setting up Disaster Recovery with Oracle Applications

In my current post I will talk about the steps involved in setting up a Disaster Recovery solution in a Oracle Applications environment. Keeping in Line with Oracle's Maximum Availability Architecture (MAA) to implement a complete DR scenario we will  have to setup a standby environment both for your database and applications which can be switched over with the least possible downtime.

My current Primary environment (Node A) involves
Single Node installation of Oracle Application
Operation System Redhat Linux
Oracle RDBMS version 9.2.0.6

The approach to setup a complete Disaster Recovery Site would involve

• Setup a application tier similar to the primary on the standby site.
• Setup a physical standby database.
• Verify the standby environment.
• Perform a switchover of the database and Application Tier services to the standby (Node B)

Naming conventions and Servers
SAMLX01 (Primary Node) also referred as Node A
SAMLX02 (Standby Node) also referred as Node B
Database SID is SAM
Database port 1535
Standby Service Name SAMSTB
FAL Service Name SAMFAL
OS Application user on SAMLX01 applsam
OS Database user on SAMLX01 orasam
OS Application user on SAMLX02 applsam
OS Database user on SAMLX02 orasam

Enable Force Logging
Oracle Applications does not implement force logging at the database. In order to implement a standby database you must enable force logging first in your primary database. Log in as the sysdba in your primary DB and execute the following.

$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Feb 27 11:03:37 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> alter database force logging;
Database altered.
SQL> quit

Set up TNSNAMES.ora for your primary database.
Set up service names for your Standby service and FAL (Fetch Archive Log) Service in your primary instance through the <context>_ifile.ora. Make sure your standby service name points to the standby server name. Your FAL service should point to the standby server name also. Make these changes in your database tier at $TNS_ADMIN location.

SAMSTB=
         (DESCRIPTION=
              (ADDRESS=(PROTOCOL=tcp)
                      (HOST=samlx02.appsdbablog.com)
                      (PORT=1535)

              )
              (CONNECT_DATA=(SID=SAM)

              )

         )

During the switchover process this entry will be changed to hold the primary server name ( ie samlx01)

SAMFAL=
         (DESCRIPTION=
              (ADDRESS=(PROTOCOL=tcp)
                      (HOST=samlx02.appsdbablog.com)
                      (PORT=1535)

              )
              (CONNECT_DATA=(SID=SAM)

              )

              )

Set up LISTENER.ora for your primary database
Setup your standby listener service for your primary node via the ifile. Make these changes in your database tier at $TNS_ADMIN location. A standby listener is started on your primary node after a switchover happens.

SAMSTB =
   (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)
               (HOST=samlx01.appsdbablog.com)
               (PORT=1535)
      )
    )

SID_LIST_SAMSTB =
   (SID_LIST =
      (SID_DESC =
         (ORACLE_HOME=/u03/sam/samdb/9.2.0)
         (SID_NAME=SAM)
      )
   )

STARTUP_WAIT_TIME_SAMSTB = 0
CONNECT_TIMEOUT_SAMSTB = 10
TRACE_LEVEL_SAMSTB = OFF
LOG_DIRECTORY_SAMSTB = /u03/sam/samdb/9.2.0/network/admin
LOG_FILE_SAMSTB = SAMSTB
TRACE_DIRECTORY_SAMSTB = /u03/sam/samdb/9.2.0/network/admin
TRACE_FILE_SAMSTB = SAMSTB
ADMIN RESTRICTIONS_SAMSTB = OFF

Enable Archiveloging at your primary Database
You must enable archivelogging in your primary database if not already done. You can modify the <CONTEXT>_ifile.ora at the $ORACLE_HOME/dbs location to have the following additional entries.

log_archive_dest_1    =    'LOCATION=/u03/sam/samarc MANDATORY'
log_archive_dest_2    =    'SERVICE=SAMSTB LGWR ASYNC=20480 OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30'
log_archive_dest_state_2 = defer
# log_archive_dest_state_2 = enable
log_archive_format    =    sam%s.arc
log_archive_min_succeed_dest = 1
# for 9i only (deprecated in 10g):
log_archive_start     =    TRUE
standby_archive_dest = '/u03/sam/samarc'
standby_file_management = AUTO
remote_archive_enable = TRUE
# db_file_name_convert: do not need; same directory structure
# log_file_name_convert: do not need; same directory structure
fal_server = SAMFAL
fal_client = SAMSTB

Now shutdown your database and start it again to enable archive logging.

$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Feb 27 18:23:30 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile=/u03/sam/samdb/9.2.0/dbs/initSAM.ora
ORACLE instance started.
Total System Global Area  581506668 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers               10629120 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.

Create Standby redo logs on your primary database.
With 9i we will use the LGWR process to take care of the shipping of redo logs to the standby server. This method is fatser. You will need to create standby redo logs on your primary database. For 9i these must exactly match your current redo logs. You can get this information from your current control file.

SQL> alter database add standby logfile group 3 (
  2  '/u03/sam/samdata/stblog01a.dbf',
  3  '/u03/sam/samdata/stblog01b.dbf')
  4   size 10M;

Database altered.

SQL> alter database add standby logfile group 4 (
  2  '/u03/sam/samdata/stblog02a.dbf',
  3  '/u03/sam/samdata/stblog02b.dbf')
  4  size 10M;

Database altered.

Make a note of your Temp Files on your Primary database
Make a note of your temp file details of your primary database which will be use to create a temp file on your standby  database manually. This process will reduce your switchover time.

SQL> select file_name, bytes
from dba_temp_files;
FILE_NAME    BYTES
/u03/sam/samdata/temp01.dbf 1153433600

Create a Standby Control File on your primary database
alter database create standby controlfile as '/u03/sam/samdata/stbcntrl01.dbf';

Disable Restricted security access
Since Oracle Applications 11.5.10 uses the restricted security option. You must disable this so that your standby database server can connect to your primary database.

Oracle Applications Manager→ Applications Dashboard → Security → Manage Security Options → Disable Restricted Access.
You must run autoconfig in your database tier and then bounce your database listener.

Pre clone on DB Tier
You must now run pre clone on your database tier of your primary node.
# su - orasam
$ perl adpreclone.pl dbTier

Pre clone on Application Tier
You must now run pre clone on your application tier of your primary node.
#su - applsam
$ perl adpreclone.pl appsTier

Shutdown Services
Shutdown Application Tier services on your primary node. Also Shutdown the Database on yuour primary node. You can also do the  DB copy using RMAN or a hot backup but since this is test environment I can afford to shut my database down.

Copy your files across to the standby server
Do a Copy fo your Application Tier Files and Database Tier Files to the standby server
tar -cf - sam | ssh samlx02 tar -xf - -C /u03/

Create a context file for your application tier on the standby
Create a context file for your application tier on the standby node by sourcing the context file of the primary node which  you have copied across. Log on to the standvy node as the application user.
# su - applsam
$ cd /u03/sam/samcomn/clone/bin
$ perl adclonectx.pl /u03/sam/samappl/admin/SAM_samlx01.xml
Choose not to validate your context file as it will fail as the database is not available yet.

Run Autoconfig On Application Tier Standby Node
Propagate the changes from your newly created context file to the application tier configuration files

$perl adconfig.pl contextfile=/u03/sam/samappl/admin/SAM_samlx02.xml run=INSTE8

Post Clone On Database Tier of Standby Node
Run post clone on your DB tier with the tech stack option only. This will take care of the file based changes for your  database tier on the standby node. Log in as the oracle user on the standby node to do this.

$su - orasam
$perl adcfgclone.pl dbTechStack
This will start your listener also after it completes successfully. Shutdown the listener on your standby as of now
$lsnrctl stop SAM

Set Up listener and tnsnames for Standby Node.
Copy across the listener_ifile.ora and <CONTEXT_NAME>_ifile.ora from the primary node $TNS_ADMIN location to the standby  server $TNS_ADMIN location.Make sure the entry for the standby service’s HOST parameter holds the standby database host name,and change the FAL service’s host name to hold the new primary host name.
In the listener_ifile.ora file, change the HOST for the standby service entry to point to the standby database host.As the ORACLE user, start the database listener for the standby.

$lsnrctl start SAMSTB

Change init.ora for Standby Database.
Change the <Context>_ifile.ora on the primary database at $ORACLE_HOME/dbs
#log_archive_dest_state_2 = defer
 log_archive_dest_state_2 = enable
Make Changes in the init.ora of the standby DB to reflect the standby control file
control_files                   = /u03/sam/samdata/stbcntrl01.dbf

Startup Standby Database
Log in as the oracle user on the standby database tier and set the environment
$ su - orasam
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 28 04:55:10 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u03/sam/samdb/9.2.0/dbs/initSAM.ora
ORACLE instance started.
Total System Global Area  581506668 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers               10629120 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

Verify Shipping of Redo Logs
Log on to the primary instance and do a switch of the logfile
$su - orasam
$sqlplus "/ as sysdba"
sql>alter system switch logfile;

Verify in the alert log of the standby for media recovery

ARC1: Evaluating archive   log 3 thread 1 sequence 72
ARC1: Beginning to archive log 3 thread 1 sequence 72
Creating archive destination LOG_ARCHIVE_DEST_1: '/u03/sam/samarc/sam72.arc'
Wed Feb 28 05:57:01 2007
RFS: Successfully opened standby logfile 4: '/u03/sam/samdata/stblog02a.dbf'
Wed Feb 28 05:57:01 2007
ARC1: Completed archiving  log 3 thread 1 sequence 72

Create Temp files on your standby database.
This process will help in reducing your switchover time, use the temp files query from above to create these files.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 28 05:11:52 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> alter tablespace temp add tempfile '/u03/sam/samdata/temp01.dbf' size 1153433600 reuse;
Tablespace altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

Testing switchover

Change init.ora settings for both current primary and current standby.
On the current primary database
In the $ORACLE_HOME/dbs/context_ifile.ora
log_archive_dest_state_2 = defer
#log_archive_dest_state_2 = enable

On the current standby database
#log_archive_dest_state_2 = defer
 log_archive_dest_state_2 = enable

Stop Services on Primary Node
Stop all Application Tier Processs on Primary Node
./adstpall.sh apps/apps

After all your application services have come down set the database parameters job_queue_processes and aq_tm_processes to  zero in the running production database
SQL> alter system set job_queue_processes = 0;
System altered.
SQL> alter system set aq_tm_processes = 0;
System altered.

Verify Primary is Ready to be switched over
Check if your primary database is ready to be switched over
SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
------------------
TO STANDBY
the 'TO SATANDBY' status reflects that your primary database is ready for the switchover

Execute the switchover on the primary database.
SQL> alter database commit to switchover to physical standby;
Database altered.
At this point your primary database has been converted to standby database

Shutdown primary database and Restart as standby
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount pfile=/u03/sam/samdb/9.2.0/dbs/initSAM_noaq.ora;
ORACLE instance started.
Total System Global Area  581506668 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers               10629120 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.

At this point of time both your databases are in complete standby phase

Verify that your Original standby is ready to be switched over to primary
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
------------------
TO PRIMARY

Convert the original standby into Primary
SQL> alter database commit to switchover to primary;
Database altered.

Complete the transition by shutdown and startup
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup  pfile=/u03/sam/samdb/9.2.0/dbs/initSAM_noaq.ora;
ORACLE instance started.
Total System Global Area  581506668 bytes
Fixed Size                   452204 bytes
Variable Size             402653184 bytes
Database Buffers          167772160 bytes
Redo Buffers               10629120 bytes

Listener and Tnsnames changes for switchover
Make changes in the <context>_ifile.ora at $TNS_ADMIN on both servers for standby service definitions

Old Primary node
SAMSTB=
         (DESCRIPTION=
              (ADDRESS=(PROTOCOL=tcp)
                      (HOST=samlx01.satyam.com)
                      (PORT=1535)

              )
              (CONNECT_DATA=(SID=SAM)

              )

         )

Old Standby
SAMSTB=
         (DESCRIPTION=
              (ADDRESS=(PROTOCOL=tcp)
                      (HOST=samlx01.satyam.com)
                      (PORT=1535)

              )
              (CONNECT_DATA=(SID=SAM)

              )

         )

Complete the database configurations
On new primary node (Node B) execute the following as the apps user
SQL> exec fnd_net_services.remove_system('SAM');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

Database Tier Autoconfig for New Primary
Run Autoconfig on new Primary (Node B) database tier
cd <ORACLE_HOME>/appsutil/scripts/<context>
./adautocfg.sh

Stop and Start the listener on New primary node and New Standby Node
Stop the standby listener which was running on the new primary node
$lsnrctl stop SAMSTB
Now start the DB listener on the new primary node
$lsnrctl start SAM
Now stop the DB listener on the New standby
$lsnrctl stop SAM
And start the standby listener on the new standby
lsnrctl start SAMSTB

Application Tier Autoconfig for New Primary
Run autoconfig on your application tier on the new primary node
cd /u03/sam/samcomn/admin/scripts/SAM_hcslnx04/
./adautocfg.sh

Start Application Services on New Primary
Start up application tier services on the new primary node
./adstrtal.sh apps/apps

Verify redo log shipping from new primary node (Node B) to new standby node (Node A)
Login as sysdba on new primary node and do a log switch
SQL> alter system switch logfile;
System altered.

Open the alert log of the new standby database to check if recovery is happening
Wed Feb 28 20:16:58 2007
RFS: Successfully opened standby logfile 3: '/u03/sam/samdata/stblog01a.dbf'
Wed Feb 28 20:17:04 2007
Media Recovery Log /u03/sam/samarc/sam79.arc
Media Recovery Waiting for thread 1 seq# 80 (in transit)