Enable/Disable Archive log mode in RAC 11gR2

Enable Archivelog

1)  Login to one of the nodes , verify the archive log mode

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 200
Current log sequence 201

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
PUB NOARCHIVELOG

2) Disable the cluster instance parameter by setting cluster_database to FALSE from the current instance:

sqlplus "/ as sysdba"

SQL> alter system set cluster_database=false scope=spfile sid='orcl1;

3) Shutdown all instances accessing the clustered database:

  srvctl stop database -d orcl

4)Using the local instance, MOUNT the database:

sqlplus "/ as sysdba"
SQL> startup mount 

5) Enable archiving:

SQL> alter database archivelog;

6) Re-enable support for clustering by modifying the instance parameter cluster_database to TRUE from the current instance:

SQL> alter system set cluster_database=true scope=spfile sid='orcl1;

7) Shutdown the local instance:

SQL> shutdown immediate 

8) Bring all instance back up using srvctl:

srvctl start database -d orcl

9)Login to the local instance and verify Archive Log Mode is enabled:

sqlplus "/ as sysdba"

SQL> archive log list
Database log mode            Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     200
Next log sequence to archive  201

After enabling Archive Log Mode, each instance in the RAC configuration can automatically archive redologs.

Disable Archivelog
1) export oracle_sid=orcl1

SQL> alter system set cluster_database=false scope=spfile sid='orcl1';

System altered.

2) srvctl stop database -d orcl -o immediate

3) SQL> startup mount

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Next log sequence to archive 19

Current log sequence 19

SQL> alter database noarchivelog;

Database altered.

SQL> alter system set cluster_database=true scope=spfile sid='orcl1';

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

4) srvctl start database -d orcl

5) export oracle_sid=orcl1

sqlplus / as sysdba

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 18

Current log sequence 19

SQL>

Duplicate Database Using RMAN backup location 11gR2

Introduction

RMAN has the ability to duplicate a database from a backup. It is possible to create a duplicate database on a remote server with the same file structure, a remote server will a different file structure or the local server with a different file structure.

The article assumes the duplicate database is being created on a separate server, using the same SID (TEST) and the same file structure as the source database. 

• Create a password file for the duplicate instance.

$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPROD password=password entries=10

scp password file to TEST server ($ORACLE_HOME/dbs location) 

•  Make proper entries in tnsnames.ora in both PROD and TEST database

Sample entry as below mention:-

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD) (UR=A)

    )

  )

TEST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = test.localdomain.com)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = TEST)(UR=A)

 )

 )

 

Preparation of TEST database.

        

•  Create pfile (ORACLE_HOME/dbs/initTEST.ora) with below mention parameters.

*.DB_NAME=TEST
*.DB_UNIQUE_NAME=TEST
*.COMPATIBLE='11.2.0'
*.db_block_size=8192
*.db_files=500
*.diagnostic_dest='/u01/app/test/db/tech_st/11.2.0/admin/TEST_test'
*.db_file_name_convert='/d01/oracle/PROD/data/','/d01/oracle/TEST/data/','/d01/oracle/PROD/archive/','/d01/oracle/TEST/archive/'
*.log_file_name_convert=
'/d01/oracle/PROD/data/','/d01/oracle/TEST/data/','/d01/oracle/PROD/archive/','/d01/oracle/TEST/archive/' *.Memory_TARGET=1068937216

• Make proper entries in tnsnames.ora and listener.ora

Sample entry is as below mention

cat tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.localdomain.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD) (UR=A)

    )

  )

TEST =

 (DESCRIPTION =

 (ADDRESS = (PROTOCOL = TCP)(HOST = test.localdomain.com)(PORT = 1521))

 (CONNECT_DATA =

 (SERVER = DEDICATED)

 (SERVICE_NAME = TEST)(UR=A)

 )

 )

• Test connection from both database (PROD/TEST). We should be able to connect sqlplus from both PROD and TEST database.

sqlplus sys/Welcome123@PROD as sysdba

sqlplus sys/Welcome123@TEST as sysdba

• Connect to the duplicate instance.

$ ORACLE_SID=TEST; export ORACLE_SID $ sqlplus / as sysdba

Start the database in NOMOUNT mode.

SQL> STARTUP NOMOUNT pfile='$ORACLE_HOME/dbs/initTEST.ora';

rman target / sys/sys@prod log=$HOME/restore.log
connect auxiliary sys/sys@TEST

run
{
allocate auxiliary channel d1 type disk;
allocate auxiliary channel d2 type disk;
allocate auxiliary channel d3 type disk;
allocate auxiliary channel d4 type disk;
allocate auxiliary channel d5 type disk;
allocate auxiliary channel d6 type disk;
duplicate database to TEST backup location '/backups/PROD/RMAN/01Jun';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}

Your duplicate database is ready.

sqlplus > select name from v$database;

TEST

cheers!!!

 

Maintenance Mode -- EBS

Maintenance Mode is a new mode of operation introduced with Release 11.5.10, in which the Oracle Applications system is made accessible only for patching activities not allowing the users to login to any responsibility. This provides optimal performance for AutoPatch sessions, and minimizes downtime needed.

1. Scheduling System Downtime

Administrators can schedule 'System Downtime' using Oracle Applications Manager (OAM):

Site Map --> Maintenance --> Manage Downtime Schedules

When the System has been scheduled for 'Downtime', Apache should be re-started on Restricted Mode by using the Script (adaprstctl.sh).  By doing this, users attempting to log on to Oracle Applications will be automatically redirected to a System Downtime URL showing a message similar to the following one:

Scheduled Downtime Details

Start Time       : 17:30:00 12/11/2004
Expected Up Time : 09:00:00 12/12/2004
For Updates      : John.Smith@oracle.com

The system is currently undergoing a scheduled maintenance.

<Current Status>

This message can be customized with any text message.  If No Downtime has been specified, and the users try to access theApplications, the following message might also appear:

! Warning
The system has not been taken off maintenance mode completely.
Please contact your System Administrator.

2. Advantages

There are several practical points relating to the use of Maintenance Mode:

• You can toggle Maintenance Mode between Enabled and Disabled using the new Change Maintenance Mode menu in AD Administration, or the equivalent function in Oracle Applications Manager.

• Although you can run AutoPatch with Maintenance Mode disabled, there will be a significant degradation in performance.

• There is a separate logon page for Restricted Mode access while the system is in Maintenance Mode. Restricted Mode allows administrators access to specific privileged functionality in OAM, for example to view the timing report that shows the progress of a patching session.
  For more Information on Restricted Mode Access please consult Metalink Note: 364236.1
  or the OAM Online Help (OAM->Patches and Utilities -> Managing Downtime Schedules -> Restricted Mode)

3. Enabling and Disabling Maintenance Mode

Maintenance mode is Enabled or Disabled from adadmin.

When you Enable or Disable 'Maintenance Mode', adadmin will execute the script:

$AD_TOP/patch/115/sql/adsetmmd.sql sending the parameter 'ENABLE' or 'DISABLE' :

sqlplus <APPS_Schema name>/<APPS Password>@adsetmmd.sql ENABLE | DISABLE

    ENABLE  -   Enable Maintenance Mode .
    DISABLE -   Disable Maintenance Mode.

When adsetmmd.sql runs, it sets the Profile Option 'Applications Maintenance Mode' (APPS_MAINTENANCE_MODE) to 'MAINT' to Enable 'Maintenance Mode' and to 'NORMAL' to Disable it.

4.  Determining if Maintenance Mode is Running

A quick way to verify if the Environment is on Maintenance Mode or not, is by checking the value of this Profile Option as follows:

sqlplus apps/apps
SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

If the query returns 'MAINT', then Maintenance Mode has been Enabled and the Users will not be able to Login.  If the query returns 'NORMAL' then Maintenance Mode has been De-Activated and the Users will be able to use the application.

Note:  Maintenance Mode is only needed for AutoPatch Sessions. Other AD utilities do not require Maintenance Mode to be enabled. Maintenance Mode must be 'Enabled' before running AutoPatch and 'Disabled' after the patch application was completed.

When Maintenance Mode is disabled, you can still run Autopatch by using options=hotpatch on the command line, if necessary.  However, doing so can cause a significant degradation of performance.

5. Error Messages

Always remember to Disable Maintenance Mode after any Patch application. If Maintenance Mode is not Disabled, the Application will not allow the users to use the system.  Take note that Apache must be re-started in normal mode after disabling 'Maintenance Mode' by using the Script adapcctl.sh (or adstrtal.sh)

As explained before, when 'Maintenance Mode' is enabled, a Downtime should be Scheduled from OAM and Apache should be started on Restricted Mode by using the Script (adaprstctl.sh).

If a 'DownTime' is not Scheduled from OAM and Apache has not been re-started on Restricted Mode, the Application will allow the users to Login, but it might experience unusual behaviors afterwards depending on the Patch Level.

Here are some examples of the possible error messages:

• When clicking on a Responsibility from the PHP

There are no applications available for this responsibility.  Please click on a different responsibility link to display the list of available applications.

or

You are not authorized to access the function Applications Home Page.  Please contact your System Administrator.

• When trying to access to the Application via CGI directly (not supported):

There are no valid navigations for this responsibility

Cause: The menu compilation has failed.
Cause: There is not valid menu defined for this responsibility.
Cause: There are no navigable forms associated with this responsibility.

Action: Contact your system administrator. Ensure that a valid menu,
containing navigable forms, is defined for the responsibility.
Ensure that the menu is correctly compiled.

Note:  In some cases, the behavior is slightly different.  Instead of showing the above messages, the Application might not show any Responsibilities listed for the user at all.

6. Step by Step Process

1.  Schedule the 'System Downtime' from OAM

OAM: Site Map --> Maintenance --> Manage Downtime Schedules

At the moment of the downtime, do the following:

2.  Shutdown Apache (on Normal Mode):

   adapcctl.sh stop
   or
   adstpall.sh <apps_user>/<apps_pwd>

3.  Enable 'Maintenance Mode' from adadmin

   adadmin: Options 5, 1

4. Start Apache (on Restricted Mode)

   adaprstctl.sh start

5. Apply the Patch with adpatch

6.  Stop Apache (on Restricted Mode)

  adaprstctl.sh stop

7.  Disable 'Maintenance Mode' from adadmin

   adadmin: Options 5, 2

8.  Start Apache (on Normal Mode):
 

  adapcctl.sh start
  or
  adstrtal.sh <apps_user>/<apps_pwd>