Step by step guide for Online Multi-node Cloning:

 Step by step guide for Online Multi-node Cloning:

Pre Cloning Tasks:

1) Prepare Target node:

Install Linux Operating System

Install required Rpms

2)On Target Machine:

Create group for DbTier

useradd -d /d01/ -g -m Username

Change the Permissions for User and change password

Chmod -R 755 /d01/

Chown -R Username:Group name /d01/

passwd Username

3)On Source Machine(DbTier):

Run adpreclone.pl

perl adpreclone.pl dbTier

File Location:$ORACLE_HOME/appsutil/scripts//

Cloning TASKS:

4)On Source Machine(AppsTier):

Run adpreclone.pl

perl adpreclone.pl appsTier

File Location:$INST_TOP//admin/scripts

5)Create Control file and SCP to target DbTier node and Edit trace file:

alter database backup control to trace;

Trace file Location:$ORACLE_HOME/admin//udump/

to get latest trace file type >ls -ltr

scp trace_file.trc root@target:/d01

Open trace_file.trc go to Startup nomount and press ESC-->:dgg it will delete all above rows then go below row ; and press ESC-->:dG then change SID ,NORESETLOGS to RESETLOGS,location of all datafiles.

6) On source(DbTier) take Hot backup and SCP dbtier to target(DbTier) and AppsTier on target(appsTier):

alter system archive log current;

alter database begin backup;

select count(*) from v$backup where status='ACTIVE';

scp -r db/ root@target:/d01// after copying done

alter system archive log current;

alter database end backup;

alter system switch logfile;(3 times)

scp -r archive/ root@target:$ORACLE_SID/dbs

Scp -r apps ins root@targerapps:/d01//

7) On Target DbTier:

Run adcfgclone.pl

File Location:$ORACLE_HOME/appsutil/clone/bin/

perl adcfgclone.pl dbTechStack(should complete with status 0)will create CONTEXT FILE and Environment file.

8) Create Control File:

Note: Run the environment file

login to database (sqlplus '/as sysdba')

>startup nomount;

>@trace_file.trc

>recover database using backup controlfile until cancel;

Add datafiles for TEMP tablespace as mentioned in trace

>altertablespaceTEMP1addtempfile'/d01//db/apps/apps_st/data/temp01.dbf'size 5012M reuse autoextend on;

>alter database default temporary tablespace TEMP1;

9) Update the Oracle binaries on Target DbTier:

Run aduplib.sql

File Location: $ORACLE_HOME/appsutil/install//

>@adupdlib.sql so

10) On Target DbTier:

Run adcfgclone.pl

File Location:$ORACLE_HOME/appsutil/clone/bin/

Note: Make sure that database and listener is up and running.

]$ perl adcfgclone.pl database $CONTEXT_FILE(will configure database)

should complete with status '0'.

11) On Target AppsTier :

Run adcfgclone.pl

File Location:$COMMON_TOP/clone/bin/

]$ perl adcfgclone.pl appsTier(should complete with status 0)

12) Create appsutil directory by using admkappsutil.pl

]$ perl admkappsutil.pl

scp appsutil.zip to target DbTier $ORACLE_HOME

unzip -o appsutil.zip (at DbTier and run autoconfig on both tiers first dbtier and the appstier)

Till here all major cloning tasks are done..

Post Cloning tasks:

If u want to know the URL of apps then:

Login to database connect as apps user:

>select home_url from icx_parameters;

13) Change the color of application so that it will differentiate production and clone Instance form

System administrator>System>Profile---->java color Scheme

--->change color

14) Check for instance name and display_name form application if different then update

>select name.display_name from apps.wf_systems;

>update apps.wf_systems set name='clone_name',dispaly_name='.domain name';

>Commit;

>update wf_agents set address=replace(address,'PROD','clone_name');

>commit;

15) Clear Fnd_Concurrent_nodes

exec FND_CONC_CLONE_SETUP_CLEAN (as apps user)

>commit;

Note if necessary run cmclean.sql script and don't forget to commit;

16) Generate Jar files from adadmin utility(1-4)

17) Relink applications programs(2-1)

18) Run autoconfig on both tiers first DbTier and second AppsTier

How to Change Profile Option Value Without Forms?

How to Change Profile Option Value Without Forms?

Applies to:

Oracle Application Object Library - Version: 11.5.10.0 to 11.5.10.2 - Release: 11.5 to 11.5
Information in this document applies to any platform.
Goal

How to update a Profile Option using SQL (if Forms login is not possible)?

Solution

Please use the API:  FND_PROFILE
The package FND_PROFILE  can be found in file AFPFPROS.pls


FND_PROFILE.SAVE - sets the value of a profile option permanently to the database,
at any level. This routine can be used at runtime or during patching.
This routine will not actually commit the changes; the caller must commit.
The levels are: 'SITE', 'APPL', 'RESP', or 'USER'.

Examples of use:
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);

returns: TRUE if successful, FALSE if failure.


To update a Profile Option value at Site level, you need to run the SQL Script below:
Declare
value Boolean;
Begin
value := fnd_profile.save('APPS_DATABASE_ID','','SITE');
End;


Example:
===========
Sample Code
===========
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/ORACLE', 'SITE');
IF stat THEN
    dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
    dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;
===============
End of Sample Code
===============


Description of the FND_PROFILE.SAVE function parameters:
Function SAVE(
X_NAME in varchar2,             /* Profile name you are setting */
X_VALUE in varchar2,            /* Profile value you are setting */
X_LEVEL_NAME in varchar2,       /* Level that you're setting at:
                                   'SITE','APPL','RESP','USER', etc. */
X_LEVEL_VALUE in varchar2 default NULL,
                                /* Level value that you are setting at,
                                   e.g. user id for 'USER' level.
                                   X_LEVEL_VALUE is not used at site level. */
X_LEVEL_VALUE_APP_ID in varchar2 default NULL,
                                /* Used for 'RESP' and 'SERVRESP' level;
                                   Resp Application_Id. */
X_LEVEL_VALUE2 in varchar2 default NULL
                                /* 2nd Level value that you are setting at.
                                   This is for the 'SERVRESP' hierarchy. */
) return boolean;

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus 


Oracle Application Object Library - Version 11.5.0 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
Goal

How To List E-Business Suite Profile Option Values For All Levels Using SQLPlus

Fix



One can display Profile Options within eBusiness Suite Release 11i and Release 12
both through the "System Administrator" responsibility (forms based) and also
through the "Functional Administrator" responsibility (OA Framework page), but
there may be times when one wishes to quickly see all Profile Option values
outside of these screens.

One can use the SQL below to query all settings for a particular profile option 
or profile options.

As this script is just performing a SELECT command, it is safe to run on any 
eBusiness Suite instance and has been tested for 11.5.10 and 12.0.x environments

Instructions
------------
1. Connect to the eBusiness Suite database using APPS schema name 

2. Execute the script listed below.

3. The script will prompt to enter a "profile_name".  Enter the value for the profile option name to display.  This value is case insensitive and is encased by % in the where clause.

For example, if entering the value 'fnd%debug' (do not enter the ' characters), then will see around 15 entries including:

FND: Debug Log Level
FND_DEBUG_RULE_THRESHOLD

4. If Oracle Support has asked to run this script, then one should enable "spool" in SQLPLus to capture the output before running the script.

NOTE - The REM commands are for documentation purposes only and can be ignored.

set long 10000
set pagesize 500
set linesize 160
column SHORT_NAME format a30
column NAME format a40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format a40
select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
10006, 'Org',
10007, decode(to_char(v.level_value2), '-1', 'Responsibility',
decode(to_char(v.level_value), '-1', 'Server',
'Server+Resp')),
'UnDef') LEVEL_SET,
decode(to_char(v.level_id),
'10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10004', usr.user_name,
'10005', svr.node_name,
'10006', org.name,
'10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,
decode(to_char(v.level_value), '-1',
(select node_name from fnd_nodes
where node_id = v.level_value2),
(select node_name from fnd_nodes
where node_id = v.level_value2)||'-'||rsp.responsibility_key)),
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(p.profile_option_name) in ( select profile_option_name
from fnd_profile_options_tl 
where upper(user_profile_option_name) 
like upper('%&user_profile_name%'))
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, user_profile_option_name, level_id, level_set;

Concurrent Processing - After Cloning All the Concurrent Managers Do Not Start for the Clone

Concurrent Processing - After Cloning All the Concurrent Managers Do Not Start for the Clone

Applies to:

Oracle Application Object Library - Version: 11.5.9 to 12.1.3 - Release: 11.5.10 to 12.1
Information in this document applies to any platform.
Checked for relevance on 22-Jul-2010

Symptoms

In Oracle Application Release 11i and 12 , after performing a clone when attempting to start the concurrent managers in the target instance using the command below : 

. adcmctl.sh stop apps/apps under $COMMON_TOP/admin/scripts// 

the following error can be found in ICM logfile : 
========== 
Could not initialize the Service Manager FNDSM__. Verify that has been registered for concurrent processing. 
==========

Cause

The issued was found to have been caused by the FND_NODES table having incorrect node entries (old node from the source instance) and that no Service Manager was found for the existing node. 

Solution

1.  Stop all middle tier services including the concurrent managers. 

2.  Stop the database. 

3.  Start the database. 

4.  Connect SQLPLUS as APPS user and run the following : 

EXEC FND_CONC_CLONE.SETUP_CLEAN; 
COMMIT; 
EXIT; 

=========================
NOTE:260887.1 - Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES 
=========================

( Note : In the case of a clone instance, it is strongly recommended to clean the non-existing nodes )

5.  Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtiers to repopulate the required system tables. 

6.  Connect to SQLPLUS as APPS user and run the following statement : 

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%'; 

If the above SQL does not returning any value then please do the following: 

Go to $FND_TOP/patch/115/sql

Connect SQLPLUS as APPS user and run the following script : 

SQL> @afdcm037.sql; 

========================= 
NOTE:218893.1 - How to Create The Service Manager 'FNDSM' on Oracle Applications 
=========================

(Note :  The same "afdcm037.sql" is used for Release 12 also ).

This script will create libraries for FNDSM and create managers for preexisting nodes. 

Check again that FNDSM entries now exist: 

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%'; 

7.  Go to cd $FND_TOP/bin 
$ adrelink.sh force=y "fnd FNDLIBR" 
$ adrelink.sh force=y "fnd FNDSM" 
$ adrelink.sh force=y "fnd FNDFS" 
$ adrelink.sh force=y "fnd FNDCRM" 

8.  Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit).

========================= 
NOTE:134007.1 - CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables 
=========================

9.  Start the middle tier services including your concurrent manager. 

10.  Retest the issue .

APPS DBA Interview faqs

Q)What is your day to day activity as an Apps DBA?
Ans: As an Apps DBA we monitor the system for different alerts (Entreprise Manager or third party tools used for configuring the Alerts) Tablespace Issues, CPU consuption
Database blocking sessions..etc

Regular maintanance activies like cloning,patching,custom code migrations (provided by developers)
Working with user isses.

Q)How often Do you patch?
Ans: Usually for non-production the patching request comes aroung weekly 4-6 and the same patches will be applied to Production in the outage or maintanance window.
Production has weekly maintance window (Eg Sat 6PM to 9PM) where all the changes (patches) will applied on production.

Q)How often Do you clone?
Ans: Cloning happens biweekly or monthly depending on the organization requierement.

Q)What change control/management or CCB?
Ans: Every organization has change control process, Change control process is no change goes into production witout proir testing on non-production instance.
Eg: If a user encouters an issue in production instance and the fix for the issue is known, Still the fix should not be applied directly on production, as it is not tested.
The same fix need to be first applied on a non-prod instance where the similar issue is happening and test the issue and instance stability.
Once user is happy with the results the change or fix will be implemented to production with the approval from Change control Board, CCB is a managemant team who reviews all the changes being deployed to production,
Depending on the need and criticality and testing results they approve the change movement to production instance.

Q)How much time does it take to upgrade, clone ?
Ans: Clone usually takes around 48hrs to copy and configure and upgrade depends on the database size and module involved.
upgrade from 11.5.9 to 11.5.10.2 will take around 3-4 days and 11i to R12 upgrade will take around 4-5 days.

Q)What is the meaning QA,CRP,SIT,DEV,UAT,PRE-PROD,PROD Instance?
QA - Tesing Instance
CRP - Conference Room Pilot
SIT - System Integration Testing
DEV - Developement
UAT- User Acceptence Testing
STAGE - Pre-production Instance
Prod - Production/actuall instance where the business is running

Q)What do we have in FND_NODES?Ans:
FND_NODES table contains  information about node_names and services enabled on a node.
In multinode instance if you want to know which node is running what services, You can query the fnd_nodes and get that information.

Q)when do we run FND_CONC_CLONE.SETUP_CLEAN ?
Ans:
FND_NODES table contains node information, If you have cloned test instance from production still the node information of production will be present after clone in the test instance.
we use FND_CONC_CLONE.SETUP_CLEAN to cleanup FND_NODES table in the target to clear source node information as part of cloning.
Below syntax to execute:
SQL> sho user
USER is "APPS"
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

This will delete all the entries in the fnd_nodes table, to populate it with target system node information, Run autoconfig on DB node and Applications node.

Q)How verify the sysadmin password from command line?
Ans:
This utility can be used to verify the GUEST/ORACLE password
SQL>select fnd_web_sec.validate_login('SYSADMIN','')from dual;
If it returns Y then sysadmin password is correct
If it returns N then sysadmin password that we are using
Eg:
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
N
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN') from dual;
FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN')
--------------------------------------------------------------------------------
Y

Q)List out few Apps related tables ?
Ans:
CONCURRENT REQUEST/PROGRAM/MANAGERS
-------------------
FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND/AOL Tables
--------------
FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD/Patches
-------------
AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

Q) How To find the latest application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

Q) How to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application
select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

Q) How to know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

Q) Howto find the base application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'

Q) How To find all available application version
select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES

Q) How To get file version of any application file which is changed through patch application
select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME

Q) How To get information related to how many time driver file is applied for bugs
select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

Q) How To find latest patchset level for module installed
select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME

Q) How To find what is being done by the patch
select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE

Q) How To find Merged patch Information from database in Oracle Applications
select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );
Q) How toto know, what all has been done during application of PATCH
Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE

Q) How to find out Patch level of mini Pack
Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';
Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order

Q)List out Profile Options Useful for Oracle Apps DBA?
Here is the list of few profile options which Apps DBA use frequently.
**It is not necessary that you as Apps DBA must know all profile options**
Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text

Does Last Update Date Column In The AD_APPLIED_PATCHES Table Get Updated For Every Patch Run

Does Last Update Date Column In The AD_APPLIED_PATCHES Table Get Updated For Every Patch Run?


Oracle Application Install - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]

Information in this document applies to any platform.

Goal

 Does Last Update Date Column In The AD_APPLIED_PATCHES Table Get Updated For Every Patch Run?

Fix

There is no information captured in the AD_APPLIED_PATCHES Table for every re-application of a patch.

Users should refer to tables like ad_patch_runs,ad_patch_run_bugs and ad_patch_run_bug_actions to see change in  last updated date as these tables hold information of  various runs of a  patch and the bugs that may or may not have got applied in a particular  invocation.

 Whereas the tables like ad_applied_patches and ad_bugs do not get updated their rows with various invocations of adpatchand hence last updated date also does not change.

How To Create, Update or Rebuild The Central Inventory For Oracle Applications EBS R12

How To Create, Update or Rebuild The Central Inventory For Oracle Applications EBS R12
How to create, update or rebuild the Central Inventory for Applications R12

November 2011
This note describes the process of creating, updating or rebuilding the Central Inventory for Oracle E-Business Suite Release 12 version 12.0 or higher. It also provides information about the Oracle Inventories and tools that use it, specific for Oracle Applications.

The most current version of this document is published as My Oracle Support Knowledge Document 742477.1 There is a change log at the end of this document.

This document contains the following sections:

Introduction
Creating or modifying the Central Inventory
How to verify the contents of the Central Inventory
Procedure 1: Adding an Oracle Home using ouicli.pl
Procedure 2: Adding an Oracle Home using the Oracle Universal Installer
Procedure 3: Removing an Oracle Home using the Oracle Universal Installer
How to build a new Central Inventory from scratch
Miscellaneous
References

Attention: This note applies to UNIX, Linux, and Windows platforms. However, you may need to modify some instructions slightly depending on your platform. For example, these notes typically use UNIX/Linux syntax when specifying a directory, so Windows users will need to substitute the appropriate syntax when accessing that directory.

Introduction

Applying patches to an Oracle Home, or performing some other maintenance tasks, is usually performed using the OPatch utility or the Oracle Universal Installer (OUI). These tools require information for each Oracle Home about the installed software, releases, interim patches, etc. This information is stored in the Local Inventory (Oracle Home Inventory) - each Oracle Home holds an inventory directory for this - and in the Central (= Global) Inventory which contains information on the Local ones. Without this information it is not possible to install/remove patches, products, etc.


Attention: The Central Inventory can be rebuilt, but a removed or corrupt Local Inventory might require a re-install, restore or re-clone (if possible).
For more information on Inventory, OUI or OPatch please check the References section.

Oracle E-Business Suite R12 should normally have all Oracle Homes and their respective Local Inventories registered in a Central Inventory.
This as per Note 549389.1: "Oracle Applications Release Notes, Release 12.0.4":

Verify oraInst.loc

On all platforms except Windows, the oraInst.loc file must exist and point to a valid directory. Specifically:
oraInst.loc must be located in /etc (for Linux and IBM AIX) or /var/opt/oracle (for Solaris, HP-UX PA RISC, and HP-UX Itanium)
oraInst.loc must point to a valid central inventory location
the central inventory location must be writable by the owner of the oracle files and the application files
If oraInst.loc does not exist, create it in the proper directory. The contents should look like this:

inventory_loc=/oracle/oraInventory

where:

/oracle/oraInventory points to the directory where the central inventory is to be located and must be writable by the owner of the oracle files and the application files.

On Windows the location of the central inventory is defined in registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\inst_loc
Oracle Applications R12 installs 3 Oracle Home's and one oraInst.loc file per tier pointing to the respective Central Inventory:

Version Purpose Location of oraInst.loc
10.1.2 Developer Tools $INST_TOP/admin   (Apps tier)
10.1.3 Web Server
10.2.1 or 11.1.0 or 11.2.0 RDBMS $ORACLE_HOME   (DB tier)

Note: It is possible to have one or more Central Inventories. Each Local Inventory must be registered to only one Central Inventory. All Local Inventories belonging to an Applications environment must be attached to the same Central one.

Creating or modifying the Central Inventory

In some cases the Central Inventory might not contain references to all 3 Oracle Home's and their Local Inventories correctly, which can be fixed using one of the two the following procedures. Note that the first procedure also updates the Local Inventory, so is the recommended one when fixing Cloning or Install related issues. The third procedure describes how to remove a Local Inventory from the Central one.


Warning: Before proceeding or performing any other task that modifies the Inventory make sure to have a full backup, or at least of all Oracle Homes and Inventories.

Attention:
If the directory for the Central Inventory does not yet exist create it as described in the Introduction section.
To enable multiple users to write to this directory on UNIX use a command like eg: chmod -R 777 /oracle/oraInventory
Before proceeding first stop all related services. Otherwise the procedure will complete without error, but not register the Oracle Home correctly.
It is possible to manipulate an Oracle Home registration using an OUI with a higher version than that Oracle Home. For example, it is possible to attach an 10.1 Oracle Home Inventory to the Central one using the 10.2 OUI.


How to verify the contents of the Central Inventory

This can be done using the Oracle Universal Installer, by clicking on the 'Installed Products...' button on the main screen. To start the OUI perform the following:
UNIX:

cd /oui/bin
./runInstaller

Windows:

cd \oui\bin
setup.exe -silent


The contents of the Central Inventory can also be checked by viewing file .../oraInventory/ContentsXML

Example:


   10.2.0.1.0
   2.1.0.6.0

REMOVED="T" means the ORACLE_HOME has been removed.

Do not modify any files for the oraInventory manually.

Procedure 1: Adding an Oracle Home using ouicli.pl

This procedure will make the Oracle Home(s) match its/their current environment and register it/them in the Central Inventory. The ouicli.pl is normally also executed by the Rapid Clone procedure. For more information about Rapid Clone please check Note 406982.1 - "Cloning Oracle Applications Release 12 with Rapid Clone".

As the oracle user, run the environment (UNIX) or the command (Windows) file for the current ORACLE_HOME and run ouicli.pl

UNIX:

. /.env
cd $ORACLE_HOME/appsutil/clone
./ouicli.pl

Windows:

\.cmd
cd %ORACLE_HOME%\appsutil\clone
ouicli.pl


Note:

Running ouicli.pl can result in error:
"Exception in thread "main" java.lang.NoClassDefFoundError: oracle/apps/ad/clone/util/OracleHomeCloner"

This can be fixed by setting Context variable s_clonestage to \clone

For example:
E:\oracle\PROD\apps\apps_st\comn\clone

For details on modifying Context variables please check Note 387859.1 - "Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12".

Procedure 2: Adding an Oracle Home using the Oracle Universal Installer

This procedure will only register the Local Inventory for the specified Oracle Home in the Central Inventory.

Attention:
Attaching an Oracle Home is documented for the OUI version 10.2 or higher, but can also work for 10.1.
A standalone copy of OUI 10.2.0.4 and OUI 10.2.0.5 is available for download via patch 6640752 as per Note 557291.1 - How to download OUI 10.2.0.4 and OUI 10.2.0.5
If the oraInst.loc is not in the default location (as in the Introduction section) or not point to the Central Inventory that needs to be updated, the argument "-invPtrLoc" should be used as shown in some of the examples below.
Some sample Oracle Home names are (this naming convention is not mandatory):
PROD_DB__oracle_PROD_db_tech_st_10_2_0
TEST_TOOLS__oracle_TEST_apps_tech_st_10_1_2
DEMO_WEBOH__oracle_test_apps_tech_st_10_1_3
In some cases the runInstaller will error out during pre-requisite checking. If the environment DOES meet the requirements this checking can be disabled using command:
./runInstaller -ignoreSysPrereqs

UNIX:

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -attachHome -invPtrLoc \
ORACLE_HOME="" ORACLE_HOME_NAME=""

For example:
./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc \
ORACLE_HOME="/oracle/test/apps/tech_st/10.1.3" ORACLE_HOME_NAME="DEMO_WEBOH__oracle_test_apps_tech_st_10_1_3"

Windows:

cd %ORACLE_HOME%\oui\bin
setup.exe -silent -attachHome ORACLE_HOME="" \
ORACLE_HOME_NAME=""

For example:
setup.exe -silent -attachHome ORACLE_HOME="e:\oracle\PROD\apps\tech_st\10.1.2" \
ORACLE_HOME_NAME="PROD_nlicm21_TOOLS"


Procedure 3: Removing an Oracle Home using the Oracle Universal Installer

To detach an Oracle Home from the Central Inventory perform the following actions:

Attention:
This procedure will detach an Oracle Home from the Central Inventory without deleting the Oracle Home or its Local Inventory.
This can only be done using OUI 10.2 or higher, but using this OUI version also for a 10.1 Oracle Home.
A standalone copy of OUI 10.2.0.4 and OUI 10.2.0.5 is available for download via patch 6640752 as per Note 557291.1 - How to download OUI 10.2.0.4 and OUI 10.2.0.5
DO NOT use the argument "-removeHome" (available with the 10.1 OUI), as this will remove the Local Inventory.

UNIX:

cd $ORACLE_HOME/oui/bin
./runInstaller -silent -detachHome ORACLE_HOME="" ORACLE_HOME_NAME=""
Windows:

cd %ORACLE_HOME%\oui\bin
setup.exe -silent -detachHome ORACLE_HOME="" ORACLE_HOME_NAME=""


How to build a new Central Inventory from scratch

1. Create a new directory to hold the new Central Inventory, or empty the existing one after backing up its contents, and set its permissions.
Example:

mkdir /oracle/oraInventory
chmod 777 /oracle/oraInventory


2. Verify or modify the oraInst.loc file, for its location see the Introduction, so 'inventory_loc' points to the directory created in step 1.
Example:

inventory_loc=/oracle/oraInventory


3. Perform the Procedure 1: Adding an Oracle Home using ouicli.pl or Procedure 2: Adding an Oracle Home using the Oracle Universal Installer for all Oracle Home's, so for the 10.1.2 Developer Tools Oracle Home, the 10.1.3 Web Server Oracle Home and the 10.2.1/11.1.0/11.2.0 RDBMS Oracle Home.
Example:

. /ora/10.1.2/.env
cd $ORACLE_HOME/appsutil/clone
./ouicli.pl


. /ora/10.1.3/.env
cd $ORACLE_HOME/appsutil/clone
./ouicli.pl


. /.env
cd $ORACLE_HOME/appsutil/clone
./ouicli.pl


4. Verify the oraInventory using the procedure of section How to verify the contents of the Central Inventory

Miscellaneous

If OPatch or the Oracle Universal Installer cannot identify the Central Inventory though the oraInst.loc in the default location, the following argument can be used:
-invPtrLoc

For example with OPatch:
opatch apply -invPtrLoc /oracle/test/apps/tech_st/10.1.2/oraInst.loc

For example with the OUI:
runInstaller -invPtrLoc /oracle/test/apps/tech_st/10.1.2/oraInst.loc


References

Note 549389.1 - Oracle Applications Release Notes, Release 12.0.4
Note 564192.1 - "FAQs on Central Inventory and Oracle Home Inventory (Local Inventory) in Oracle RDBMS"
Note 458893.1 - "Oracle Universal Installer (OUI) FAQ"
Note 557291.1 - How to download OUI 10.2.0.4 and OUI 10.2.0.5
Note 334108.1 - "OPatch version 10.2 - FAQ"
Note 453495.1 - "FAQs on OPatch Version : 11.1"
Note 242993.1 - "OPATCH FAQ"
Note 387859.1 - "Using AutoConfig to Manage System Configurations in Oracle E-Business Suite Release 12"
Note 406982.1 - "Cloning Oracle Applications Release 12 with Rapid Clone"

How To Check The Current India Localization Patchset Level Installed On An Instance?

How To Check The Current India Localization Patchset Level Installed On An Instance?

Solution

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:

Oracle Application - India Localization - Version: 11.5.10.2
Information in this document applies to any platform.
Goal

How to check the current India Localization Patchset Level installed on an Instance?


Solution

The latest India Localization Patchset is IN60107(Patch Number = 5498551)
If the Patch Number 5498551 is applied, your current India Localization patchset is IN60107.
This can be verified by running the below select

select * from JAI_APPLIED_PATCHES where Patch_number = 5498551
/

If the Patch Number 5498551 is not applied, the India Localization Patchset should be IN60106 or below.
This can be checked by running the below statement:-
select * from JA_IN_INSTALL_CHECK_INFO where name like '601%'

USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH)

USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH)


Oracle Application - India Localization - Version: 11.5.5 to 11.5.10

Information in this document applies to any platform.

Purpose

This bulletin provides you the following details of  India Localization Patch Tool - INDPATCH

a) Salient features

b) Technical aspects

Scope and Application

About the Tool

The India Localization Patch Tool is designed to apply India Localization patches. All India Localization patches which are patch tool compliant clearly mention in the readme as to how to apply them using the patch tool. There are several options which can be used during application of the patch using the Patch Tool which are clearly detailed in the Patch Tool User Manual.
USAGE OF INDIA LOCALIZATION PATCH TOOL (INDPATCH)

Listed below are the salient features of the Patch Application Tool :
1. Applies the India Localization patch-tool compliant patches.
( You would find many patches of India Localization which are not patch tool compliant. These patches need to be applied as per instructions in the readme only if you have not applied Cummulative Patchset IN60107)
2. Performs Version checking, so latest objects will never get overwritten by old ones.
3. Performs backup of the following objects :
a. All database objects - functions, triggers, package ( body and spec ), procedures and views.
b. Forms(.fmb), reports(.rdf), form libraries (.pll) and seeded data (.ldt).
4. Every aspect of patch application is clearly detailed in the logs generated.
These logs can be used to diagonize patch application failures easily and quickly.
The following are the types of logs generated :
a. Main log : This is by default $JA_TOP/$APPLLOG//.log or value specified to logfile parameter provided to the patch application tool. This log details the complete patch application. 
b. L*.log : Concurrent application logs upon application of ".ldt" files using FNDLOAD command.
c. *.err : Compilation details of forms to generate ".fmx" files.
d. odf.log : Application details of odf files.
5. Takes the following parameters :
* drvr_file - Name of the driver file, usually .drv. No default provided. Mandatory. 
* fnd_patchset - The patchset level of FND on the environment. This information is required for successful application of ldt files in the patchset. Values can be between the range [A-Z]. No default provided, Mandatory.
* appspwd - Password of APPS schema. Default value is "apps".
* japwd - Password of JA schema. Default value is "ja".
* mode - any combination of letters "F", "C" and "D". Give "F", if the node is Forms Server node, "C", if the node is concurrent processing node and "D", if Database node. Default is "FCD", meaning a single node instance. For multi-node, apply the patch with
appropriate mode specified.
* logfile - Name of the logfile to which the log messages will be written. Default is $JA_TOP/$APPLLOG//.log 
* test - Y/N, "N" being the default. If test="Y", then the patch is not actually applied, but only the versions of objects are displayed on screen, listing what objects will be applied and what will not be.
* paramfile - Instead of the above, just one single parameter file can be given as an argument, in which all the above parameters can be stored.
* systempwd - Password of SYSTEM schema. Default value is "manager".

Technical aspects of the tool

The tool is a perl script which can be run from command line by providing appropriate parameters. It calls sql scripts for version checking and backup.

Process to start applying a patch-tool compliant patch ( say ) : 

1. The patch would be a zip file which needs to downloaded from metalink and unzipped.
2. On unzipping, a directory would be created with . 
3. The Readme_ would contain the information about "Automated Patch Application Steps" as shown below : 



Automated Patch Application Steps
=================================
If you have applied the patch Patch 5458248 from metalink (India Localization Patch Application Tool), you should be able to apply this patch using that tool.



4. If you have not installed patch application tool, then you need to download patch Patch 5458248
a. You need to copy the zip to JA_TOP and unzip it there.
b. Follow the "User Manual" to install the Patch Application Tool.
c. Using the patch application tool, go to the directory where you have downloaded the patch . 
d. Apply the patch using a command similar to the one given below :
perl $JA_TOP/inpatch/indpatch.pl drvr_file=.drv fnd_patchset=H appspwd=apps japwd=ja systempwd=manager 

5. If you already have the patch application tool, then you can use this tool to apply the patch . 
Please note that if you are at using an older version of the patch tool, on patch application using the old tool you will be prompted to download the latest patch tool.

6. It clearly details all the status of the application of the patch tool.
The status would be similar to the one given below :



STATUS :
Patch application completed successfully.
Please check /slot05/appmgr/ILCERTappl/ja/11.5.0/log/ILCERT/5352145/5352145.log for patch application details.



Alternatively you can also check the patch application status using the following query as "APPS" user.
select * from jai_applied_patches where patch_number = '' ; 

7. If the patch had not got applied successfully, then please log a TAR with the following information.
a. Patch number
b. Command used to apply the patch
c. Zip the log file directory specified as an argument logfile to patch application tool or the default directory.
(
default path for log files : $JA_TOP/$APPLLOG/ 
or
perl $JA_TOP/inpatch/indpatch.pl logfile=/slot05/appmgr/ILCERTappl/5352145/5352145_applied.log
then log files would be present in /slot05/appmgr/ILCERTappl/5352145 directory
)
d. Output of the following query :
select * from jai_applied_patches where patch_number = '' ; 

8. Even if the patch application is successful, customers have customization which needs to be incorporated to the main files.
In this scenario, the old files which have got overwritten by files in the patch would be present in the /backup directory. 
You need to manually make the changes and apply the objects.

Here are some of the most common wait events, and what they mean

Here are some of the most common wait events, and what they mean:

enqueue

The process is waiting on an enqueue (a lock you can see in v$lock). This commonly occurs when one user is trying to update a row in a table that is currently being updated by another user.

library cache pin

The process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. This happens when you are compiling or parsing a PL/SQL object or a view.

library cache load lock

The process is waiting for the opportunity to load an object or a piece of an object into the library cache. (Only one process can load an object or a piece of an object at a time.)

latch free

The process is waiting for a latch held by another process. (This wait event does not apply to processes that are spinning while waiting for a latch; when a process is spinning, it is not waiting.)

buffer busy waits

The process wants to access a data block that is currently not in memory, but another process has already issued an I/O request to read the block into memory. (The process is waiting for the other process to finish bringing the block into memory.)

control file sequential read

The process is waiting for blocks to be read from a control file.

control file parallel write

The process has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.

log buffer space

The process is waiting for space to become available in the log buffer (Space becomes available only after LGWR has written the current contents of the log buffer to disk.) This typically happens when applications generate redo faster than LGWR can write it to disk.

log file sequential read

The process is waiting for blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.

log file parallel write

The process is waiting for blocks to be written to all online redo log members in one group. LGWR is typically the only process to see this wait event. It will wait until all blocks have been written to all members.

log file sync

The process is waiting for LGWR to finish flushing the log buffer to disk. This occurs when a user commits a transaction. (A transaction is not considered committed until all of the redo to recover the transaction has been successfully written to disk.)

db file scattered read

The process has issued an I/O request to read a series of contiguous blocks from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during a full table scan or full index scan.

db file sequential read

The process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!

db file parallel read

The process has issued multiple I/O requests in parallel to read blocks from data files into memory, and is waiting for all requests to complete. The documentation says this wait event occurs only during recovery, but in fact it also occurs during regular activity when a process batches many single block I/O requests together and issues them in parallel. (In spite of the name, you will not see this wait event during parallel query or parallel DML. In those cases wait events with PX in their names occur instead.)

db file parallel write

The process, typically DBWR, has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.

direct path read, direct path write

The process has issued asynchronous I/O requests that bypass the buffer cache, and is waiting for them to complete. These wait events typically involve sort segments.

There are several wait events that we call "idle events" because each of these wait events typically occurs when the Oracle process has nothing to do and is waiting for somebody to give it a task. Idle events are usually not very interesting from a tuning standpoint, so we usually overlook them when evaluating data extracted from the wait event interface. The common idle events are as follows:


Idle Wait Events


client message: PX Idle Wait
dispatcher timer rdbms ipc message
lock manager wait for remote message smon timer
Null event SQL*Net message from client
parallel query dequeue SQL*Net message to client
pipe get SQL*Net more data from client
PL/SQL lock timer virtual circuit status
pmon timer wakeup time manager

preserving patch state in oracle apps

Ever wondered how to preserve a patching status

Overview : Be it junior apps DBA or an Experienced Apps DBA architect, you would encounter this situation atleast once in your life time as an Apps DBA.

Whether you are on an point release upgrade or normal apps patching assume a patch has failed. Patch is about 30 mb and you are half through, you found that its a known issue and applying new pre-req/fix patch would resolve the issue.

Two things can be done here

1) Abort the current patch failing/skipping the workers using adctrl.
2) Apply the new patch which would fix the issue
3) Reapply the main patch which had issue

Patching would be successful but will take up additional time reapplying the main patch. Suitable for DEV/TEST instance but not for Production where downtime is critical.

Lets see the next senario where we can reduce the patching time .

1) Abort the current patch failing/skipping the workers using adctrl.
2) Save the patch/worker state of the current patch to do this follow the steps below

a)Backup the FND_INSTALL_PROCESSES & AD_DEFERRED_JOBS table which is owned by the APPLSYS schema

rename fnd_Install_processes to fnd_Install_processes_bkp;
rename AD_DEFERRED_JOBS_bkp to AD_DEFERRED_JOBS;

b) Backup the .rf9 files located in $APPL_TOP/admin//restart directory.
At this point, the adpatch session should have ended .

c) Backup restart directory

cd $APPL_TOP/admin/
mv restart restart_bkp
mkdir restart

d) Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table ( we already backed up these).

drop table FND_INSTALL_PROCESSES;
drop table AD_DEFERRED_JOBS;

2)Apply the new patch.

3) Restore the backedup info for main patch i.e FND_INSTALL_PROCESSES,AD_DEFERRED_JOBS, restart directory,.rf9

cd $APPL_TOP/admin/
mv restart restart_old
mv restart_bkp restart


rename fnd_Install_processes_bkp to FND_INSTALL_PROCESSES;
rename AD_DEFERRED_JOBS_bkp to AD_DEFERRED_JOBS

4) Start adpatch, it will resume where it stopped

Note: This will resume the patch and not restart the patch :)

How to relocate the redo log files to a different location on disk?

How to relocate the redo log files to a different location on disk?

Solution

A.

1. Shut down the database.

2. Copy the online redo log files to the new location.

Operating system files, such as online redo log members, must be copied using the appropriate operating system commands.

The following example uses operating system commands (UNIX) to move the online redo log members to a new location:

$ mv /diska/logs/log1a.rdo   /diskc/logs/log1c.rdo

$ mv /diska/logs/log2a.rdo   /diskc/logs/log2c.rdo

3. Startup the database in mount mode, but do not open it.

SQL> CONNECT / as SYSDBA 

SQL> STARTUP MOUNT 

4. Rename the online redo log members.

SQL> ALTER DATABASE RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo' 

TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo'; 

5. Open the database for normal operation.

SQL> ALTER DATABASE OPEN; 

B. If the database cannot be shut down, it will not possible to rename the files directly : first add new groups and then drop the non-needed ones.

1. Add as many new groups as the ones to be renamed with the following commands :

SQL> ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M; 

SQL> ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', '/log02B.dbf ') SIZE 512M;

 SQL> ALTER DATABASE ADD LOGFILE group 6 ('/log03A.dbf', '/log03B.dbf ') SIZE 512M;

2. Then drop the online redo log groups that are not needed anymore: you must have the ALTER DATABASE system privilege. 

Before dropping an online redo log group, consider the following restrictions and precautions: 

2.1 An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.) 

2.2 You can drop an online redo log group only if it is INACTIVE. If you need to drop the current group, first force a log switch to occur. 

Make sure an online redo log group is archived (if archiving is enabled) before dropping it.

To see whether this has happened, use the V$LOG view :

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG; 

GROUP#    ARC STATUS 

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

1         YES ACTIVE 

2         NO  CURRENT 

3         YES INACTIVE

4         YES UNUSED

5         YES UNUSED

6         YES UNUSED

As group 3 is in INACTIVE mode, you can drop it :

SQL> ALTER DATABASE DROP LOGFILE GROUP 3; 

3. After dropping an online redo log group, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log files. 

Example of How To Resize the Online Redo Logfiles

Example of How To Resize the Online Redo Logfiles

EXAMPLE OF HOW TO RESIZE THE ONLINE REDO LOGS:
==============================================
Often times the online redo logs are sized too small causing database
performance problems.
The following is an example of how to resize the online log groups:
NOTE:  Examples are given for 9i and higher.   In prior releases, you needed
to use Server Manager and connect as the internal user.
1. First see the size of the current logs:            
             
   > sqlplus /nolog
   SQL> connect / as sysdba

   SQL> select group#, bytes, status from v$log;
   GROUP#     BYTES      STATUS                  
   ---------- ---------- ----------------                    
            1    1048576 INACTIVE                      
            2    1048576 CURRENT                        
            3    1048576 INACTIVE                          
                             
    Logs are 1MB from above, let's size them to 10MB.                              
                                 

2. Retrieve all the log member names for the groups:                                          
                                                   
   SQL> select group#, member from v$logfile;                                  
                                                     
            GROUP# MEMBER                                                        
   --------------- ----------------------------------------                      
                 1 /usr/oracle/dbs/log1PROD.dbf                                  
                 2 /usr/oracle/dbs/log2PROD.dbf                                  
                 3 /usr/oracle/dbs/log3PROD.dbf                                  
                                                             
                                                               
3. In older versions of the database you needed to shutdown and issue the following
   commands in restricted mode.   You can still do this, but the database can be online
   to perform these changes.

   Let's create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
   size:                          
                           
   SQL> alter database add logfile group 4                            
           '/usr/oracle/dbs/log4PROD.dbf' size 10M;                                
                               
   SQL> alter database add logfile group 5                                
           '/usr/oracle/dbs/log5PROD.dbf' size 10M;    
       
   SQL> alter database add logfile group 6        
           '/usr/oracle/dbs/log6PROD.dbf' size 10M;  
       

4. Now run a query to view the v$log status:                                  
                                                                       
   SQL> select group#, status from v$log;                                      

      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 CURRENT
           3 INACTIVE          
           4 UNUSED
           5 UNUSED
           6 UNUSED        
     
   From the above we can see log group 2 is current, and this is one of the
   smaller groups we must drop. Therefore let's switch out of this group into
   one of the newly created log groups.                
                 

5. Switch until we are into log group 4, so we can drop log groups 1, 2, and 3:                                
   
   SQL> alter system switch logfile;        
   ** repeat as necessary until group 4 is CURRENT **
                                                           
                                                           
6. Run the query again to verify the current log group is group 4:                                                          
                                                               
   SQL> select group#, status from v$log;                                      
                                                                 
      GROUP# STATUS
   --------- ----------------
           1 INACTIVE
           2 INACTIVE
           3 INACTIVE          
           4 CURRENT
           5 UNUSED
           6 UNUSED                        

 Note: redo log Group 1 or 2 or 3 can be active after "alter system switch log file" which means could not be dropped, in this case,
you need to do "alter system checkpoint" to make redo log groups 1,2 and 3 inactive.                        
                                                 
7. Now drop redo log groups 1, 2, and 3:    

   SQL> alter database drop logfile group 1;                                  
   SQL> alter database drop logfile group 2;                                  
   SQL> alter database drop logfile group 3;                                  
                                 
   Verify the groups were dropped, and the new groups' sizes are correct.

   SVRMGR> select group#, bytes, status from v$log;

      GROUP#     BYTES STATUS
   --------- --------- ----------------
           4  10485760 CURRENT
           5  10485760 UNUSED
           6  10485760 UNUSED          


 
8.  At this point, you consider taking a backup of the database.

9.  You can now go out to the operating system and delete the files associated
    with redo log groups 1, 2, and 3 in step 2 above as they are no longer
    needed:
   
    % rm /usr/oracle/dbs/log1PROD.dbf
    % rm /usr/oracle/dbs/log2PROD.dbf
    % rm /usr/oracle/dbs/log3PROD.dbf                                                
                                                 
   Monitor the alert.log for the times of redo log switches. Due to increased
   redo log size, the groups should not switch as frequently under the same
   load conditions.


References:
===========

Chapter 6 of the Oracle8i Administrator's Guide, Release 8.1.5,
Part No. A67772-01 for further information on redo log maintenance.

Chapter 7 of the Oracle 9i Administrator's guide
Part No. A96521-01 for further information on redo log maintenance.


Chapter 6 of the Oracle 10g Administrator's guide
Part No. B10739-01 for further information on redo log maintenance.

Chapter 10 of the Oracle 11g Administrator's guide
Part No. B28310-04 for further information on redo log maintenance.