Scope
The objective of this document is to outline the steps required to setup the DR site for the existing PROD instance hosted at PANSOFT Data Center. The document outlines the steps as required for carrying out successfully switchover and failover from primary site to DR site and vice versa.
Overview
The document has been classified under three major sections.

  • Preparation – This is required for the initial setup at the primary instance.
  • Creation of Standby Database Instance – This section is to be followed during initial setup of DR site with regards to primary site.
  • Creation of Standby Application Instance – This section is be followed during the initial setup of application DR site with regards to primary site.
  • Role Transition – This will include switchover and failover methods.
  • E-Business Suite Maintenance with Standby Database

Oracle Data Guard is a set of services that create, manage and monitor one or more standby databases to enable a primary database to survive disasters and data corruption. Oracle Data Guard can switch a standby database to the primary role, minimizing the downtime. While setting up Oracle Data Guard, the ‘Maximum Performance’ mode was chosen.

Logical standby Databases are not supported with Oracle E-Business Suite standard functionality. Also Snapshot standby databases should be used with caution, as the data will be out of sync with the primary. Hence the document details the steps for setting up a Physical Standby database between the primary and DR site.

Preparation

1. Identify your production and DR nodes. The same has been outlined under Appendix A.
2. As root user, edit the file /etc/hosts and add the following entries on both the database and application server of your primary site.

192.168.1.81 DRDB.pansoft.com DRDB
192.168.1.82 DRAPP.pansoft.com DRAPP

3. As root user, edit the file /etc/hosts and add the following entries on both the database and application server of your standby site.

192.168.1.61 DB.pansoft.com DB
172.16.1.118 PRODAPP.pansoft.com PRODAPP

4. The following tasks should be completed on the primary site (database server)
a. Enforce force logging on the primary database. As a sysdba user excute the following.

SQL>ALTER DATABASE FORCE LOGGING;

b. Configure Oracle Net to the standby node. Login as oracle owner (oracle). Set the environment. Navigate to $TNS_ADMIN. Check if the file PROD_DB_ifile.ora exists. If not, create the file and add the tnsnames alias (PRODS) entries. This file is referred within tnsnames.ora. The tnsnames alias (PRODS) will be used later in the sections. The same is outlined in Appendix B.
c. Oracle Dataguard uses Oracle Net sessions to transport redo data and control messages between the members of a Oracle Dataguard configuration. These redo transport sessions are authenticated using either the SSL protocol or a remote login password file. Here in this setup we use a remote login password file. Verify on the primary site database server whether a password file exists or not. If it does not exists, we will need to create the password file. The password file is located at $ORACLE_HOME/dbs.

oracle@PRODDB>cd $ORACLE_HOME/dbs
oracle@PRODDB>orapwd file=orapwPROD password= entries =5 ignorecase=y

Note : One will need the sys user password. In case required, change the sys user password.

SQL>ALTER USER SYS IDENTIFIED BY password

d. Modify/add the init.ora parameters. On the primary database, define initialization parameters in initPROD.ora that control redo transport services while the database is in primary role. The parameters along with values are mentioned under Appendix C. Create the spfile from pfile.

e. Enable archive logging on primary database if not done yet. Here, PROD was already running on ARCHIVELOG mode.
f. Add standby redo logs. Standby redo logs are required to use real-time apply. As the remote file server (RFS) process writes the redo data to standby redo log files on the standby database, apply services can recover redo from standby redo logs as they are being filled. Best practice is to add them in both the primary and the standby database so switchovers between the environments are quicker and easier. Here, you will add them in production (primary) so they are in place when you clone the database for standby. The standby redo logs created on PROD are outlined in Appendix D.
g. Gather temporary file information. You might need to manually recreate your temporary files on the standby database. Gather the required data now from the primary database with the following query.

SQL>SELECT FILE_NAME,BYTES /(1024*1024*1024)SIZE IN GB” FROM dba_temp_files;

h.

oracle@PRODDB>cd $ORACLE_HOME/appsutil/scripts/R12PROD_R12DB
oracle@PRODDB>perl adpreclone.pl dbTier

Run the application tier and database tier pre-clone scripts.

applmgr@PRODAPP>cd $INST_TOP/admin/scripts/
applmgr@PRODAPP>perl adpreclone.pl appsTier

5. Copy the ORACLE_HOME from primary database server to standby database server.
6. Copy the APPL_TOP and Oracle E-Business suite technology stacks of primary application server to standby application server.
Creation of Standby Database Instance
1. The following needs to done on the primary site database server.
a.

oracle@DB>bash
oracle@DB>. .prod_profile
oracle@DB>./start_db_backup.sh

This script will internally call another script full.scr. full.scr calls rman script to backup database along with archivelogs in background.
Backup the primary database. The script used for backup is mentioned under Appendix E. Once the database backup is completed; copy the backup onto the standby database server.
b.

oracle@PRODDB>alter database create standby controlfile as ‘/home/oraprod/stbyprod.dbf’;
oracle@PRODDB>alter system switch logfile;
oracle@PRODDB>select thread#, sequence# -1 from v$log where status = ‘CURRENT’;

Create the standby control file. Copy the standby control file onto the standby database server.
2. Note the thread# and sequence# for later use: You will only be able to open the standby database after this log has been applied on the standby. In our case, sequence#-1 was 1800.
The above standby controlfile from primary database server was copied onto /home/oraprod of standby database server.
Configure RDBMS_ORACLE_HOME on standby database server. This has reference to step 4 mentioned above under Preparatory Tasks. Once the step 4 is completed, you may start this task.
Login as root user on DRDB server and create the /usr/tmp/PROD (utl_file_dir) and /oraarch/oracle/PROD/archive (LOG_ARCHIVE_DEST_1) directory.

A. These directories are the DATA_TOPS where stores the redo, undo and temp files.
B. This directory contains the archive logs at the standby database site.
C. Refer to Appendix F for responses that you will be prompted to specify when you execute ‘perl adcfgclone.pl dbTechStack’.
D. Successful completion of the above step will create the environment file PROD_DRDB.env located at /orahome/oracle/PROD/db/tech_st/11.2.0.
E. The environment can be setup using the file .prod_profile located at /home/oraprod at the standby server. Henceforth to set the RDBMS_ORACLE_HOME environment do the following.

oraprod@DRDB>bash
oraprod@DRDB>. .prod_profile
oracle@DRDB>bash
oracle@DRDB>export PATH=$PATH:/usr/vacpp/bin
oracle@DRDB>mkdir –p /oradata1/oracle/R12PROD/db/apps_st/data/
oracle@DRDB>mkdir –p /oradata2/oracle/R12PROD/db/apps_st/data/
oracle@DRDB>mkdir –p /oradata3/oracle/R12PROD/db/apps_st/data/
oracle@DRDB>cd /orahome/oracle/R12PROD/db/tech_st/11.2.0/appsutil/clone/bin
oracle@DRDB>perl adcfgclone.pl dbTechStack

Login as oracle on server DRDB.

root@DRDB>bash
root@DRDB>mkdir –p /usr/tmp/PROD
root@DRDB>mkdir –p /oraarch/oracle/PROD/archive
root@DRDB>chown –R oracle:dba /usr/tmp/PROD
root@DRDB>chown –R oracle:dba /oraarch/oracle

3.

oracle@DRDB>bash
oracle@DRDB>. .prod_profile
oracle@DRDB>sqlplus “/ as sydba
SQL>create spfile from pfile;
SQL>exit
oracle@DRDB>cd $ORACLE_HOME/dbs

Modify / add the parameters required.

oracle@DRDB>sqlplus “/ AS sysdba”
SQL>CREATE SPFILE FROM PFILE;

Setup the init.ora parameters at the standby database server. The parameters are outlined under Appendix G.

4. Modify the listener.ora at the standby database server. The listener.ora entries can be referred under Appendix H. Note that listener is already started automatically after execution of step 2 above (perl adcfgclone.pl dbTechStack)

oracle@DRDB>bash
oracle@DRDB>. .prod_profile
oracle@DRDB>cd $TNS_ADMIN
oracle@DRDB>lsnrctl stop PROD
oracle@DRDB>cp listener.ora listener.orig
oracle@DRDB>rm listener.ora
oracle@DRDB>vi listener.ora
oracle@DRDB>lsnrctl start PRODS

5. Configure Oracle Net to the primary site. Login as oracle owner (oracle) on standby database server. Set the environment. Navigate to $TNS_ADMIN. Check if the file PROD_DRDB_ifile.ora exists. If not, create the file and add the tnsnames alias (PRODS) entries. This file is referred within tnsnames.ora. The tnsnames alias ‘PRODS’ is outlined in Appendix I.

oracle@DRDB>bash
oracle@DRDB>. .prod_profile
oracle@DRDB>cd $TNS_ADMIN
oracle@DRDB>vi PROD_DRDB_ifile.ora

7. Refer Step 1.b above. Using the standby controlfile that was copied from primary database server to standby database server, mount the standby database on standby database server.

oracle@DRDB>bash
oracle@DRDB>. .prod_profile
oracle@DRDB>cp stbyprod.dbf /oradata2/oracle/R12PROD/db/apps_st/data/cntrl01.dbf
oracle@DRDB>cp stbyprod.dbf /oradata2/oracle/PROD/db/apps_st/data/cntrl02.dbf
oracle@DRDB>cp stbyprod.dbf /oradata2/oracle/PROD/db/apps_st/data/cntrl03.dbf
oracle@DRDB>sqlplus “/ as sysdba”
SQL>startup nomount
SQL>ALTER DATABASE MOUNT STANDBY DATABASE;
SQL>exit

8. Refer Step 1.b above. Once the backup has been copied from primary database server to standby database server. Here we have copied at ‘/oraarch/RMAN_PRODBKUP/250811’ on standby database server. Using this backup we will restore the database first on the standby database server. Note that at this stage the standby database is already in MOUNTED state using the standby control file that was created and shipped from the primary database server. The restore script is mentioned under Appendix J.

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>rman target /
RMAN>catalog start with ‘/oraarch/RMAN_PRODBKUP/250811;
RMAN>exit;
oracle@DRDB>nohup rman @restore.rman &

Monitor the nohup.out.
9. Put the standby database in managed recovery mode.

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRD>sqlplus “/ AS sysdba”
oracle@DRDB>ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;
oracle@DB>ALTER system SET log_archive_dest_state_2 = enable;

OBSERVATION: At this stag the archivelogs from sequence 1800 starts getting shipped from primary site to standby site.
At secondary site, we checked the status of managed recovery processes.

oracle@DRDB>SELECT process, thread#, SEQUENCE#, STATUS FROM v$managed_standby WHERE process='MRP0';
PROCESS THREAD# SEQUENCE# STATUS
--------- ---------- ---------- ------------
MRP0 1 1798 WAIT_FOR_GAP

After some point of time, the following errors are observed in the alert_R12PROD.log at the secondary site.

Tue Jun 25 16:21:49 2013
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 1798-1799
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------

10. Copy all the archive logs 1798 till sequence 1798 through 1799 to the standby database site at LOG_ARCHIVE_DEST_1 (/oraarch/oracle/PROD/archive) location. In our case we need all the archive logs till 1800 to be applied for the standby database to be opened in READ ONLY mode.
11.

applmgr@DB>sqlplus “/ AS sysdba”
applmgr@DB>ALTER system SET log_Archive_dest_state_2=defer;
oracle@DRDB>sqlplus “/ AS sysdba”
oracle@DRDB>recover managed standby DATABASE cancel;
oracle@DRDB>recover automatic standby DATABASE;

Manually recover the standby database.
12. Open the standby database as READ ONLY Mode and then put it back in managed recovery mode. This is just to verify that standby database got created successfully.
1. Monitor the status of Managed recovery processes at standby site.

oracle@DRDB>SELECT process, thread#, SEQUENCE#, STATUS FROM v$managed_standby WHERE process='MRP0';
PROCESS THREAD# SEQUENCE# STATUS
--------- ---------- ---------- ------------
MRP0 1 1822 WAIT_FOR_GAP
oracle@DRDB>SELECT process, thread#, SEQUENCE#, STATUS FROM v$managed_standby WHERE process='MRP0';
PROCESS THREAD# SEQUENCE# STATUS
--------- ---------- ---------- ------------
MRP0 1 1863 APPLYING_LOG
oracle@DRDB>SELECT process, thread#, SEQUENCE#, STATUS FROM v$managed_standby WHERE process='MRP0';
PROCESS THREAD# SEQUENCE# STATUS
--------- ---------- ---------- ------------
MRP0 1 1864 WAIT_FOR_LOG

You must notice the sequence# is progressing ahead and getting closer to the current log sequence present on the Primary site.

oracle@DRDB>sqlplus “/ AS sysdba”
oracle@DRDB>ALTER DATABASE OPEN READ ONLY;
oracle@DRDB>shutdown immediate
oracle@DRDB>startup mount
oracle@DRDB>ALTER DATABASE recover managed standby DATABASE disconnect FROM SESSION;
oracle@DB>sqlplus “/ AS sysdba”
oracle@DB>ALTER system SET log_Archive_dest_state_2=enable;

Monitor the alert_PROD.log at both standby and primary site for any possible errors.
2. Check which archive logs have been shipped and archived.

oracle@DRDB>SELECT SEQUENCE#, archived, applied FROM v$archived_log ORDER BY SEQUENCE#;

SEQUENCE# ARC APPLIED
———- — ———
1866 YES YES
1867 YES YES
1868 YES YES
1869 YES YES
1870 YES YES
1871 YES YES
1872 YES YES
1873 YES YES
1874 YES YES
1875 YES YES
1876 YES YES

3. At any given point of time if one needs to validate whether the archive logs those are shipped from primary site to standby site is shipped correctly or not, one may issue the following on the standby site.

oracle@DB>alter system dump logfile '' validate;

Note : If there are no errors with regards to archive logs, the command will just come out successfully.
To Cancel the media recovery execute the below query.

oracle@DRDB>alter database recover managed standby database cancel;

While the managed recovery is cancel, if the LOG_ARCHIVE_DEST_STATE_2 is ENABLE state on the primary database site, the archive logs will still continue to ship from primary to standby site. In case you want to disable shipping of archive logs from primary to standby site, execute the below query.

oraprod@R12DB>alter system set log_archive_dest_state_2=DEFER;

To re-enable managed recovery mode on the standby site.

oracle@DRDB>alter database recover managed standby database disconnect from session;

In case you have disabled shipping of logs from primary site, you must enable it now.

oraprod@R12DB>alter system set log_archive_dest_state_2=ENABLE;

This completes the creation of standby database.

Creation of Standby Application Instance

1. The following needs to done regarding configuration standby application server after standby database is enabled.

applmgr@DRAPP>bash
applmgr@DRAPP>cd $COMMON_TOP/clone/bin
applmgr@DRAPP>perl adclonectx.pl $INST_TOP/appl/admin/R12PROD_R12PRODAPP.xml

a. This step is to be carried out once Step 6 mentioned under Preparatory task is completed.
b. The above task will create the new context file PROD_DRAPP.xml for the standby application server. The following will resolve the reference to manually. Answer the questions when prompted. This creates your environment files on the application tier. It will try to connect to the database, so some portions will fail, but the environment scripts should be created successfully.

applmgr@DRAPP>bash
applmgr@DRAPP>cd $APPL_TOP/ad/12.0.0/bin
applmgr@DRAPP>perl adconfig.pl contextfile= $INST_TOP/appl/admin/R12PROD_R12DRAPP.xml run=INSTE8
applmgr@DRAPP>bash
applmgr@DRAPP>. .r12prod_profile
applmgr@DRAPP>mkdir –p /log
applmgr@DRAPP>mkdir –p /out
applmgr@PRODAPPHA>bash
applmgr@PRODAPPHA>. .r12prod_profile
applmgr@PRODAPPHA>mkdir –p /log
applmgr@PRODAPPHA>mkdir –p /out
applmgr@PRODAPP>bash
applmgr@PRODAPP>. .r12prod_profile
applmgr@PRODAPP>mkdir –p /log
applmgr@PRODAPP>mkdir –p /out

Note: Since we have two application server(s) at primary site, we are creating the log and out directories on both the application server(s) at the primary site. Since R12PRODAPP is the server at primary site hosting the concurrent processing of log and out files will happen between R12PRODAPP and R12DRAPP. In the event concurrent processing is moved to R12PRODAPPHA node at primary site, the synchronization should happen between R12PRODAPP and R12DRAPP server.

c. To synchronize your concurrent manager log and out files from primary to the standby. For this, first create the directories matching to the APPLCSF environment variables in the appropriate place on the standby application tier server.

Synchronization of log and out files between the primary and standby site could either be done automatically or manually (during a switchover or failover)

To synchronize the files automatically, on the primary application (tiers), set up an rsync job in cron, to run every few minutes.

$ rsync av /log :/log –rsync –path=/usr/local/bin/rsync
$ rsync av /out : /out –rsync –path=/usr/local/bin/rsync

To synchronize the files manually, before carrying out a failover or switchover between the primary and standby sites, copy the files from /log, /out TO /log, /out.

Role Transition – Switchover

The following section outlines the steps to be followed when one would like to switchover operations from primary site to standby site.

1. Verify the primary database instance is open and standby instance is mounted.
2. Verify there are no active users connected to the database, Shutdown primary application server processes.
3. Ensure that the last redo data transmitted from the primary database was applied on the standby database. Issue the following SQL command on the primary and standby database(s) to find out.

oracle@DB>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
oracle@DRDB>SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

If necessary, perform a manual switch on the primary site database.
4. Check whether the primary site database is ready to switch. Query the switchover_status column of the v$database fixed view.

oracle@DB>SELECT switchover_status FROM v$database;

If the above query returns “TO STANDBY”, then the environment is ready to switch. If it returns “ACTIVE SESSIONS”, then the switch command should be used with the ‘sessionshutdown’ option.
5. Depending on the outcome of the above step, initiate the switch over on the primary site database.

oracle@DB>connect / as SYSDBA
oracle@DB>alter database commit to switchover to physical standby with session shutdown;
oracle@DB>connect / as SYSDBA
oracle@DB>alter system set log_archive_dest_state_2 = defer

6. Change the value of LOG_ARCHIVE_DEST_STATE_2 to defer on the primary site. Since this site (DB) is going to changed to new Standby site, we do not want the archive logs to be shipped to the present standby site (DRDB).
6.

oracle@DB>connect / as SYSDBA
oracle@DB>shutdown immediate
oracle@DB>startup nomount
oracle@DB>alter database mount standby database;
oracle@DB>alter database recover managed standby database disconnect from session;

Shutdown and mount the primary database (DB). Put the database in managed recovery mode.
7. Verify the switchover status on the standby server (DRDB).

oracle@DRDB>SELECT switchover_status from v$database;

The above query should return “TO PRIMARY”. Any other value, such as “SESSIONS ACTIVE”, “NOT ALLOWED” and so on, should be investigated and corrected.
8. If the above query returned “TO PRIMARY”, the switch over process can be initiated. Please note a standby database should either be mounted and in REDO APPLY mode or READ ONLY mode for it to be switched to PRIMARY. oracle@DRDB> alter database commit to switchover to primary with session shutdown;

oracle@DRDB>shutdown immediate
oracle@DRDB>startup
oracle@DRDB>alter system set log_archive_dest_state2 = enable;

Enable log_archive_dest_state_2 to enable log shipping from DRDB site to DB site.
9. At this stage DRDB is the primary site, DB is the standby site. Log shipping is happening from DRDB site to DB site. Connect to the new primary database (DRDB) and complete the database configuration(s) with regards to E-Biz applications.

oracle@DRDB>sqlplus apps/
oracle@DRDB>EXEC fnd_net_services.remove_system(‘R12PROD’);
oracle@DRDB>commit;
oracle@DRDB>EXEC fnd_conc_clone.setup_clean;
oracle@DRDB>commit;
oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>lsnrctl stop R12PRODS
oracle@DRDB>cd /appsutil/scripts/R12PROD_R12DRDB
oracle@DRDB>./adautocfg.sh

Note: R12PRODS was created in step 4 of Creation of Standby Database Instance. The listener name would get changed to R12PROD when you executed autoconfig.sh script. This is only time change.
10. As oracle user on the DRD server, stop the listener and then run autoconfig.sh
11. Start the listener at the DRDB server (new primary)

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>lsnrctl start R12PROD

12. Now complete application related configurations at the new primary / old standby application tier (DRAPP).
Only for the first time switchover, run post cloning procedure and follow additional steps outlined below except autoconfig.sh. For subsequent switchover, just execute all the additional steps outlined below.

applmgr@DRAPP>bash
applmgr@DRAPP>cd $COMMON_TOP/clone/bin
applmgr@DRAPP>perl adcfgclone.pl appsTier
applmgr@DRAPP>bash
applmgr@DRAPP>. .r12prod_profile
applmgr@DRAPP>cd $INST_TOP/admin/scripts
applmgr@DRAPP>./autocfg.sh

Login to database on your new primary instance.

SQL>UPDATE apps.fnd_concurrent_requests SET logfile_node_name=' R12DRAPP', outfile_node_name = ‘DRAPP’ WHERE logfile_node_name = ‘PRODAPP’ AND outfile_node_name = ‘PRODAPP’;
SQL>UPDATE apps.fnd_conc_req_outputs SET file_node_name=‘DRAPP’ WHERE file_node_name = ‘PRODAPP’;
SQL>UPDATE apps.fnd_concurrent_queues SET node_name = ‘DRAPP’ WHERE node_name = ‘PRODAPP’;

Additional Steps
13. Synchronize concurrent manager out and log files from primary application server (PRODAPP or PRODAPPHA – depending upon where the concurrent manager was running) to standby application server (DRAPP).
1. Copy the log files from /log to /log
2. Copy the log files from /out to /out
Refer Point 1.c under Creating of Standby Application Instance for further information.
14. Start the new primary application server processes

applmgr@DRAPP>bash
applmgr@DRAPP>. .r12prod_profile
applmgr@DRAPP>cd $INST_TOP/admin/scripts
applmgr@DRAPP>./adstrtal.sh apps/apps_pwd;

Users can now be directed to login to application using the new application server URL

http://drapp.pansoft.com:2021

Role Transition – Failover

You may need to failover to your standby site due to complete failure of the primary site. The following tasks will demonstrate the steps to failover to a standby site.

Note: Performing a failover separates the standby database from the primary. You must create a new standby database environment from the environment to which you failed over, to restore disaster recovery protection.

1. Flush any unsent redo from the primary database to target standby database.

oracle@DB>bash
oracle@DB>. .r12prod_profile
oracle@DB>ALTER SYSTEM FLUSH REDO TO ‘R12PROD’;

Note: This statement flushes any unsent redo from the primary database to the standby database, and waits for that redo to be applied to the standby database.
If the primary database can be mounted, it may be possible to flush any urgent archived and correct redo from primary database to the standby database. Ensure that redo apply is active on standby server. Mount the database but do not open it.
2.

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST FROM v$ARCHIVED_LOG;

Verify that standby database has the most recently archived redo log file for each of primary database redo thread. Here in our case we have only one thread.
3. Identify and resolve any archived redo log gaps. On the standby database server, connect as sysdba to the standby database. Query V$ARCHIVE_GAP to determine whether there are missing archive logs.

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>SELECT * FROM V$ARCHIVE_GAP;

Note: If the above query returns a row, it indicates at least one archived redo log is missing from the standby. If you still have access to your primary database, you can determine the full name of the redo logs by querying v$archived_log, using the low_sequence# and high_sequence# returned above.

oraprod@R12DB>SELECT name FROM V$ARCHIVED_LOG WHERE thread# = <thread# FROM above query> AND SEQUENCE# BETWEEN <low_sequence# above query> AND <high_sequence# above query>;

Locate the missing logs and copy them to the standby server’s standby redo log destination, then register them manually.

oracle@DRDB>ALTER DATABASE register physical logfile ‘&lt;filespec/name ON standby&gt;’;

Note: When you query V$ARCHIVE_GAP at standby site, note only one gap will be reported at a time. If you find a gap and resolve it, repeat this process until no more gaps are reported.

4.

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Adjust standby archive destination status. This will ensure that archive logs are shipped onto the old primary site (DB) if network is available.
5. Stop redo apply and finish applying all received redo data.

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>alter database recover managed standby database cancel;

Finish the recovery session

oracle@DRDB>ALTER DATABASE recover managed standby DATABASE finish;

Convert the physical standby to primary database role

oracle@DRDB>ALTER DATABASE commit TO switchover TO PRIMARY;
oracle@DRDB>shutdown immediate
oracle@DRDB>startup

Note: You should back up this database without delay, as you cannot recover any changes made after the failover without a fresh backup.

6. Execute Step 9 to 14 mentioned under Role Transition – Switchover.

Users can now be directed to login to application using the new application server URL

http://drapp.pansoft.com:2021

E-Business Suite Maintenance with Standby Database

This section will describe how to apply an Oracle E-Business Suite patch in on the primary, and incrementally update the standby.
Applying an application patch when standby is configured requires:

  • Syncing of standby with the primary after applying the patch. There are two choices.
  • Synching File System using rsync, and redo log apply for the database.
  • Recreate the standby completely. When the patch is a major upgrade of the application this is the recommended approach.
  • Protecting the primary as well as standby from any problems during patch application.
  • If your standby database is running during patch application, the database changes on primary will be automatically pushed to the standby. If you do not want these changes pushed to standby until after patching is complete, you should shutdown standby recovery before applying patches. This would be the best recommended approach.
  • If you have enough disk space, backup both the database and Oracle E_Business file system before patching.

1. On the standby, stop recovery delay if it is set. On the standby database, run the following.

<code lang="sql">
oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DRDB>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

2. SHUTDOWN the application tier services on the PRIMARY.

applmgr@PRODAPP>bash
applmgr@PRODAPP>. .r12prod_profile
applmgr@PRODAPP>cd $ADMIN_SCRIPTS_HOME
applmgr@PRODAPP>./adstpall.sh apps/ applmgr@PRODAPPHA>bash
applmgr@PRODAPPHA>. .r12prod_profile
applmgr@PRODAPPHA>cd $ADMIN_SCRIPTS_HOME
applmgr@PRODAPPHA>./adstpall.sh apps/

3. Switch redo logs in the primary database

oracle@DB>bash
oracle@DB>. .r12prod_profile
oracle@DB>ALTER SYSTEM SWITCH LOGFILE;
oracle@DRDB>SELECT SEQUENCE# -1 FROM v$log WHERE STATUS =CURRENT’;

4. Ensure that the last log is applied on the standby

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DB>SELECT sequence#, applied, to_char(first_time, ‘dd/mm/yy hh24:mi:ss’) first FROM V$ARCHIVED_LOG ORDER BY FIRST_TIME;

5. Stop recovery on the standby

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oracle@DB>ALTER DATBASE RECOVER MANAGED STANDBY DATABASE CANCEL;

6. NOW start patching at the primary instance.
7. Restart redo data shipping and apply on the standby

oracle@DRDB>bash
oracle@DRDB>. .r12prod_profile
oraprod@R12DB>ALTER DATBASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

8.

applmgr@PRODAPP>bash
applmgr@PRODAPP>. .r12prod_profile
applmgr@PRODAPP>cd $ADMIN_SCRIPTS_HOME
applmgr@PRODAPP>perl adpreclone.pl appsTier
applmgr@PRODAPPHA>bash
applmgr@PRODAPPHA>. .r12prod_profile
applmgr@PRODAPPHA>cd $ADMIN_SCRIPTS_HOME
applmgr@PRODAPPHA>perl adpreclone.pl appsTier
oracle@DB>bash
oracle@DB>. .r12prod_profile
oracle@DB>cd $ORACLE_HOME/appsutil/scripts/R12PROD_R12DB
applmgr@PRODAPPHA>perl adpreclone.pl dbTier

Run the database and application pre-clone procedures on primary site
9. 1. When you apply an E-Business suite patch, Synchronize APPL_TOP, COMMON_TOP, OracleAS 10.1.2 ORACLE_HOME and OracleAS 10.1.3 ORACLE_HOME.
2. If you had applied an E-Business Suite technology stack upgrade – synchronize OracleAS 10.1.2 ORACLE_HOME, OracleAS 10.1.3 ORACLE_HOME
Synchronize the following directories between the primary site and Standby site.
10. Start Application tier Services on Primary Site.
11. Reconfigure the standby application tier file system. This recreates the environment files on the application tier. This script will try to connect to the database, since the database is in managed recovery mode – so some portions will fail but the environment script will be successfully created.

applmgr@DRAPP>bash
applmgr@DRAPP>. .r12prod_profile
applmgr@DRAPP>cd $APPL_TOP/ad/12.0.0/bin
applmgr@DRAPP>perl adconfig.pl contextfile= /appl/admin/R12PROD_R12DRAPP.xml run=INSTE8
Appendix A

Machine configuration
Primary Database

Hostname DB.pansoft.com
IP Address 192.168.1.61
OS User oracle
DB Name PROD
TNS_ALIAS PROD
DB Unique Name PROD

Standby Database

Hostname DRDB.pansoft.com
IP Address 192.168.1.81
OS User oracle
DB Name PROD
TNS_ALIAS PRODS
DB Unique Name PRODS

Primary APPS

Hostname PRODAPP.pansoft.com
IP Address 192.168.1.62
OS User applmgr

Standby APPS

Hostname DRAPP.pansoft.com
IP Address 192.168.1.82
OS User applmgr
Appendix B

Configure Oracle Net to the standby node.

bash-3.2$ ssh oracle@192.168.1.61
oracle@192.168.1.61's password:
Last unsuccessful login: Tue Jun 25 07:32:03 IST 2013 on ssh from testclone.pansoft.com
Last login: Tue Jun 25 09:34:09 IST 2013 on /dev/pts/4 from 80.0.0.162
*******************************************************************************
* *
* *
* Welcome to AIX Version 6.1! *
* *
* *
* Please see the README file in /usr/lpp/bos for information pertinent to *
* this release of the AIX Operating System. *
* *
* *
*******************************************************************************
[YOU HAVE NEW MAIL]
oracle@PRODDB>bash
oracle@PRODDB>. .prod_profile
[YOU HAVE NEW MAIL]
oracle@PRODDB>cd $TNS_ADMIN
PRODS entry in PROD_DB_ifile.ora
PRODS=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=DRDB.pansoft.com)(PORT=1546))
(CONNECT_DATA=
(SERVICE_NAME=PRODS)
(INSTANCE_NAME=PRODS)
)
)
Appendix C

Init.ora parameters at Primary site.

*.db_unique_name=PROD
*.log_archive_dest_1='LOCATION=/oraarch/oracle/PROD/archive COMPRESSION=ENABLE'
*.log_archive_dest_2='SERVICE=PRODS LGWR ASYNC=20480 DB_UNIQUE_NAME=PRODS OPTIONAL REOPEN=15 MAX_FAILURE=100 NET_TIMEOUT=300'
*.log_archive_config='dg_config=(PROD,PRODS)'
*.log_archive_min_succeed_dest=1
*.standby_file_management=AUTO
*.fal_server=PRODS
*.log_file_name_convert='/oradata1/oracle/PROD/db/apps_st/data/'
,'/oradata1/oracle/PROD/db/apps_st/data/'
*.log_archive_dest_state_2=DEFER
*._redo_transport_compress_all = TRUE
Appendix D

Standby redo logs created at primary site

SQL>ALTER DATABASE ADD standby logfile GROUP 5 ('/oradata1/oracle/PROD/db/apps_st/data/log5a.dbf', '/oradata1/oracle/PROD/db/apps_st/data/log5b.dbf') SIZE 500M;
SQL>ALTER DATABASE ADD standby logfile GROUP 6 ('/oradata1/oracle/PROD/db/apps_st/data/log6a.dbf', '/oradata1/oracle/PROD/db/apps_st/data/log6b.dbf') SIZE 500M;
SQL>ALTER DATABASE ADD standby logfile GROUP 7 ('/oradata1/oracle/PROD/db/apps_st/data/log7a.dbf', '/oradata1/oracle/PROD/db/apps_st/data/log7b.dbf') SIZE 500M;
SQL>ALTER DATABASE ADD standby logfile GROUP 8 ('/oradata1/oracle/PROD/db/apps_st/data/log8a.dbf', '/oradata1/oracle/PROD/db/apps_st/data/log8b.dbf') SIZE 500M;
Appendix E

Backup Scripts (located at /home/oracle of primary database server)

1. Start_db_backup.sh

#!/usr/bin/ksh
. /home/oracle/.prod_profile
export CUR_DATE=`date +%d%m%y`
mkdir -p /orarman/RMAN_PRODBKUP/$CUR_DATE
nohup rman target / nocatalog cmdfile=/home/oracle/full.scr log=/home/oracle/full_bkp_`date ‘+%d%b%y’`.log &;
echo “\nbackup started check logfile \t /home/oracle/full_bkp_`date ‘+%d%b%y’`.log \t for errors\n”

2. full.scr

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/orarman/RMAN_PRODBKUP/$CUR_DATE/PROD_hot_full_%U';
RUN
{
BACKUP AS compressed backupset filesperset 8 DATABASE plus archivelog TAG PROD_HOTBKP_DSK;
}
Appendix F

Sr. No Name Value
1 System Hostname (virtual or normal) [drdb] DRDB
2 Instance is RAC (y/n) N
3 Target System Database SID PROD
4 Target System Base Directory /orahome/oracle/PROD
5 Target System utl_file_dir Directory List /usr/tmp/PROD
6 Number of DATA_TOP’s on the Target System [3] 3
7 Target System DATA_TOP Directory 1 [/oradata1/oracle/PROD/db/apps_st/data] oradata1/oracle/PROD/db/apps_st/data
8 Target System DATA_TOP Directory 2 [/oradata2/oracle/PROD/db/apps_st/data] /oradata2/oracle/PROD/db/apps_st/data
9 Target System DATA_TOP Directory 3 [/oradata3/oracle/PROD/db/apps_st/data] /oradata3/oracle/PROD/db/apps_st/data
10 Target System RDBMS ORACLE_HOME Directory [/orahome/oracle/PROD/db/tech_st/11.1.0]
[/orahome/oracle/PROD/db/tech_st/11.2.0
11 Do you want to preserve the Display [DB:0.0] (y/n)
N
12 Target System Display [DRDB:0.0] DRDB:0.0
13 Do you want the the target system to have the same port values as the source system (y/n) [y] Y

Appendix G

InitPROD.ora parameter entries at the standby site.

*.db_unique_name=PRODS
*.log_archive_dest_1='LOCATION=/oraarch/oracle/PROD/archive COMPRESSION=ENABLE'
*.log_archive_dest_2='SERVICE=PROD ASYNC REGISTER VALID_FOR=(online_logfile,primary_role) DB_UNIQUE_NAME=PROD COMPRESSION=ENABLE'
*.log_archive_config='dg_config=(PROD,PRODS)'
*.log_archive_min_succeed_dest=1
*.standby_file_management=AUTO
*.fal_server=PROD
*.log_file_name_convert='/oradata1/oracle/PROD/db/apps_st/data/','/oradata1/oracle/PROD/db/apps_st/data/'
*.log_archive_dest_state_2=DEFER
*._redo_transport_compress_all = TRUE
Appendix H

Listener.ora entries at standby site.

PRODS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DRDB.pansoft.com)(PORT = 1546))
)
)
SID_LIST_PRODS =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= /orahome/oracle/PROD/db/tech_st/11.2.0)
(SID_NAME = PROD)
)
)

STARTUP_WAIT_TIME_PRODS = 0
CONNECT_TIMEOUT_PRODS = 10
TRACE_LEVEL_PRODS = OFF

LOG_DIRECTORY_PRODS = /orahome/oracle/PROD/db/tech_st/11.2.0/network/admin
LOG_FILE_PRODS = STDBY
TRACE_DIRECTORY_PRODS = /orahome/oracle/PROD/db/tech_st/11.2.0/network/admin
TRACE_FILE_PRODS = STDBY

Appendix I

PROD entry in PRODS_DRDB_ifile.ora

PROD=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=DB.pansoft.com)(PORT=1546))
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(INSTANCE_NAME=PROD)
)
)
Appendix J

Restore.rman script (located at /home/oracle at Standby database server)

connect target sys/sys_pwd@PROD
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
restore database;
}
Tagged with →  
Share →
0 comments
Skip to toolbar