Oracle Apps DBA stuff: June 2009

Tuesday, June 23, 2009

http://www.psoug.org/reference/init_dot_ora.html

how to recover from a lost control file

There is no back up of database and it is in NOARCHIVELOG mode.

Please note that the database is still up and we are getting errors while querying some objects like V$LOGFILE as follows:

ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/bt/mts/home2/oradata2/mts/control03.ctl'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3

To recover from the deleted control file, you need to do the following:

Shut down the database (not abort).
Copy one of the control files to the missing version OR remove it from the control file.
Start the database.

This may actually recreate the missing log file. If it does not, you can do this to recreate the missing file:

Shut down the database (not abort).
startup mount
alter database open resetlogs;

That should recreate the missing files.

how to change the database name

Follow these steps to rename a database:
1. Start by making a full database backup of your database (in case you need to restore if this procedure is not working).

2. Execute this command from sqlplus while connected to 'SYS AS SYSDBA':

ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
2. Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql.

3. Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".

4. Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql.

Database Views

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

Restoring Hot Backup Steps

Detailed steps:---------------
These steps assume the source database and the new copy of the database, hereafter called the target database, will have the same database name.

For the example in this note the source and target database names will both be PROD. If you wish to rename the database upon copying the database then follow the procedures in Note 135651.1.

1. Create a new init.ora file and password file (if used) from the production init.ora file. If using an spfile see Note 249664.1
on how to create a pfilefrom an spfile.
Then can use Note 166601.1 to convert back to an spfile once this procedure is completed.
Change all listed directory locations (for controlfiles, bdump, udump, ...) to the new directory locations as necessary.
2. Obtain a trace file script to recreate the controlfile. On the source database issue the following command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
The trace file script will be put into the user_dump_dest directory.
The name of the trace file script will be something like PROD_ora_12345.trc.
Compare the date and time of the new trace file script to the time in which you entered the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command.

This will ensure you will be using the most recent trace file script, the one you created in this step.
3. In the create controlfile script just
created in step 2 change: CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS to: CREATE CONTROLFILE DATABASE "PROD" RESETLOGS ARCHIVELOG You must specify RESETLOGS.
The ARCHIVELOG mode may be changed to NOARCHIVELOG if you wish to run the copied database in noarchive log mode.
Change all directories in the create controlfile clause to point to the correct directories for the new target database, if necessary.
Leave "only" the CREATE CONTROLFILE clause.
The other statements, like the recover command, will be done manually.

Be sure you also remove the STARTUP NOMOUNT command.

4. On the source database make an online copy of all datafiles using:
ALTER TABLESPACE BEGIN BACKUP;
Copy all datafiles within tablespace to the new directory.
On Unix systems, this can be done with the cp command.
Then do:
ALTER TABLESPACE END BACKUP;
Do NOT copy the controlfiles and redo log files as they will be recreated.

You must copy the datafiles only after the
ALTER .. BEGIN BACKUP command has been executed, otherwise the datafiles may be corrupted.
The names of the datafiles and tablespaces to which the datafiles belong can be
obtained using the following
command: SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

Note: Instead of performing step 4 and step 5 to create a new online backup,
you may instead use a previously taken online backup of your database.

If you choose to use a previous online backup be sure to copy the required archived redo logs taken with the the previous online backup. 5.

After all datafiles have been copied and the tablespaces taken out of backup mode issue the following command:
ALTER SYSTEM ARCHIVE LOG CURRENT;

You will need all of the archivelog files from the start of datafile copy commands including the one just created with the command
ALTER SYSTEM ARCHIVE LOG CURRENT.
6. On the target system set your ORACLE_SID to PROD and start Server Manager or SQL*Plus.
7. On the target system issue STARTUP NOMOUNT command. For example:

SQL> startup nomount pfile=initPROD.ora

8. Run the prepared script created in step 3 to create the new controlfile. For example:

SQL> @PROD_ora_12345.trc

9. Issue the command:

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

You will be prompted to apply all of the archived redo logs from the source database including

the last archive redo log file created with the ALTER DATABASE ARCHIVE LOG CURRENT
command from step 5.
After applying all of these archive log files issue the CANCEL command.

10. Open the database with resetlogs:

ALTER DATABASE OPEN RESETLOGS

At this point the target database will have been successfully cloned and opened.

If this is a 10g database and you wish to add Enterprise Manager Database Control for the new
database, follow the steps in this note:
278100.1 How To Drop, Create And Recreate DB Control In A 10g Database

Monday, June 22, 2009

Configure the database in archive mode..?

By default the database will be in NOARCHIVELOG mode when created initially.The archiving mode can be changed by :
1) Setting ARCHIVELOG initialization parameter
2) Configuring ARCHIVELOG mode manually

1) Setting ARCHIVELOG initialization parameter :
In case of local host set LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST. In case of local or remote host set LOG_ARCHIVE_DEST_n where n is a value from 1 to 10. These are the parameters set in initialization parameter file to specify the archivelog destination.

For Example,
LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc'
LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'
LOG_ARCHIVE_DEST = '/disk1/arc'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/arc'
For more details visit>>
2) Configuring ARCHIVELOG Mode :
Follow the steps below to configure ARCHIVELOG mode or switch from NOARCHIVELOG to ARCHIVELOG mode.
1) Shutdown the database instance using :
SQL>SHUTDOWN

2) Backup the database to protect against any major problems.

3) Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archive log files.

4) Start a new instance and mount, but do not open, the database.
SQL>STARTUP MOUNT

5) Change the database archiving mode. Then open the database for normal operations.
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
For more details visit>>
6) Shut down the database.
SQL> SHUTDOWN IMMEDIATE

7) Changing the database archiving mode updates the control file. After changing the database archiving mode, we must back up all of our database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode.

Scenario when to run cmclean.sql

Stuck Concurrent Requests
Every now and then users call us with a concurrent request that is running longer than normal and/or blocking other batch jobs because of incompatibilities. Upon investigation we'll see that there is no database session for the request. Since there isn't a database session users may not be unable to cancel the request themselves. The cancel button will be grayed out. The solution is to clean the fnd_concurrent_requests table. Background: Concurrent programs may be incompatible with other programs which means they cannot execute at the same time. If the stuck concurrent request has such rules defined, then programs it is incompatible with will not run until the problem is solved.There are 2 ways to do this, update the table manually or run the Oracle provided cmclean.sql script. Depending on the method you choose, you'll need the request id. This can be provided by the user or you can look at the running requests via Oracle Applications Manager (OAM). To navigate there click on Site Map on the top left hand corner of the page. Under Concurrent requests click on Running.
Once your in the Running requests screen you'll see which programs are currently being executed. With the help of your users, find the request id in question and make note of it. The recommended approach from Oracle will be:1. Kill the database sessions for the requests. (In our case there weren't any.)2. Shutdown the concurrent managers.3. Run the cmclean.sql script Note: 134007.14. Start your concurrent managers.The other method is to update the bad rows in the fnd_concurrent_requests table manually.update fnd_concurrent_requests set STATUS_CODE='D',phase_code='C' where request_id=STATUS_CODE of D means Cancelled and a phase_code of C is completed.For a list of status, phase_codes and what they mean, refer to Note: 297909.1.The benefit to updating the fnd_concurrent_requests table manually is that no downtime is required. If you are using cmclean.sql remember to shutdown the concurrent managers first

Saturday, June 20, 2009

cmclean.sql

NOTE:

 Copy from the first REM statement below to last REM statement of this
 document and save as: cmclean.sql
====================================================================

REM
REM FILENAME
REM   cmclean.sql
REM DESCRIPTION
REM   Clean out the concurrent manager tables
REM NOTES
REM   Usage: sqlplus  @cmclean
REM
REM
REM   $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM         
REM
REM +======================================================================+


set verify off;
set head off;
set timing off
set pagesize 1000

column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'


WHENEVER SQLERROR EXIT ROLLBACK;

DOCUMENT

  WARNING : Do not run this script without explicit instructions
            from Oracle Support


  *** Make sure that the managers are shut down     ***
  *** before running this script                    ***
           
  *** If the concurrent managers are NOT shut down, ***
  *** exit this script now !!                       ***

#

accept answer prompt 'If you wish to continue type the word ''dual'': '

set feed off
select null from &answer;
set feed on


REM     Update process status codes to TERMINATED

prompt
prompt  ------------------------------------------------------------------------

prompt  -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager

SELECT  concurrent_queue_name manager,
       concurrent_process_id pid,
       process_status_code pscode
FROM    fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE   process_status_code not in ('K', 'S')
AND     fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND     fcq.application_id = fcp.queue_application_id;

set head off
set feedback on
UPDATE  fnd_concurrent_processes
SET     process_status_code = 'K'
WHERE   process_status_code not in ('K', 'S');



REM     Set all managers to 0 processes

prompt
prompt  ------------------------------------------------------------------------

prompt  -- Updating running processes in FND_CONCURRENT_QUEUES
prompt  -- Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE  fnd_concurrent_queues
SET     running_processes = 0, max_processes = 0;




REM     Reset control codes

prompt
prompt  ------------------------------------------------------------------------

prompt  -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT  concurrent_queue_name manager,
       control_code ccode
FROM    fnd_concurrent_queues
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;

set feedback on
set head off
UPDATE  fnd_concurrent_queues
SET     control_code = NULL
WHERE   control_code not in ('E', 'R', 'X')
AND     control_code IS NOT NULL;

REM     Also null out target_node for all managers
UPDATE  fnd_concurrent_queues
SET     target_node = null;


REM     Set all 'Terminating' requests to Completed/Error
REM     Also set Running requests to completed, since the managers are down

prompt
prompt  ------------------------------------------------------------------------

prompt  -- Updating any Running or Terminating requests to Completed/Error
set feedback off
set head on
SELECT  request_id request,
       phase_code pcode,
       status_code scode
FROM    fnd_concurrent_requests
WHERE   status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;
    
set feedback on
set head off
UPDATE  fnd_concurrent_requests
SET     phase_code = 'C', status_code = 'E'
WHERE   status_code ='T' OR phase_code = 'R';





REM     Set all Runalone flags to 'N'
REM     This has to be done differently for Release 10

prompt
prompt  ------------------------------------------------------------------------

prompt  -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
       c         pls_integer := dbms_sql.open_cursor;
       upd_rows  pls_integer;
       vers      varchar2(50);
       tbl       varchar2(50);
       col       varchar2(50);
       statement varchar2(255);
begin

       select substr(release_name, 1, 2)
       into   vers
       from fnd_product_groups;

       if vers >= 11 then
          tbl := 'fnd_conflicts_domain';
          col := 'runalone_flag';
       else
          tbl := 'fnd_concurrent_conflict_sets';
          col := 'run_alone_flag';
       end if;


       statement := 'update '  tbl  ' set '  col  '=''N'' where '  col  ' = ''Y''';
       dbms_sql.parse(c, statement, dbms_sql.native);
       upd_rows := dbms_sql.execute(c);
       dbms_sql.close_cursor(c);
       dbms_output.put_line('Updated '  upd_rows  ' rows of '  col  ' in '  tbl  ' to ''N''');
end;
/



prompt

prompt  ------------------------------------------------------------------------

prompt  Updates complete.
prompt  Type commit now to commit these updates, or rollback to cancel.
prompt  ------------------------------------------------------------------------

prompt

set feedback on



After executing the script it will prompt you

type dual


then type commit;

How to start the Output Post Processor (OPP)?

===========================================================

1. Login to Apps with sysadmin responsibility
2. Navigate to: Concurrent -> Managers -> Define
3. Query for
Manager = 'Output Post Processor'
or Short Name = FNDCPOPP
4. Check the checkbox "Enable" .
5. Click on 'Work Shifts button
6. see Work Shift of the OPP and
Set
Processes = 1
and
Parameters = oracle.apps.fnd.cp.opp.OPPServiceThread:2:0:max_threads=5
and
Sleep Second = 30
7. Save
8. Navigate to:_ Concurrent -> Managers -> Administer
9. Make sure the Output Post Processor Status is not 'Deactivate'
Set Status to 'Activate' if necessary.

10. Stop and Start the Concurrent Managers using
adcmctl.sh

Friday, June 19, 2009

UNIX SCRIPTS FOR DBA

Index Of Oracle Scripts

General Oracle Database Information

Server SQL/PLSQL Scripts
1. Object Management
2. Space Management
  1. Tablespace Fragmentation
  2. Table Fragmentation
  3. Online Index Rebuilds
  4. Datafile Free Space
  5. Database Usage Report
  6. Tablespace Usage Report
  7. Index Statistics [Updated 28-FEB-01]
  8. Block Space Utilisation in 9i
  9. Unused Space in an Extent [Updated 12-SEP-00]
  10. Is there space for the next extent in the tablespace ? [Updated 30-JUL-02]
  11. Actual space used by table rows [Updated 28-FEB-01]
  12. Generate Create Rollback Segment Script
  13. Coalesce Tablespaces in Oracle 7.1 and 7.2
  14. Generate Create Tablespace Script
  15. List all database datafiles
  16. List all tables/indexes extents in a given tablespace
  17. List all tables/indexes with extents greater than a given value
  18. List all used and free extents in a given tablespace
  19. List %age and bytes free in tablespaces. [Updated 30-MAR-00]
3. DB Resource Manager
4. Performance
  1. TOP 10 CPU Users
  2. TOP 10 Users Reading Data
  3. TOP 10 Users Open Cursors
  4. Sort Usage Report
  5. Datafile Statistics
  6. Latch Contention
  7. Database hit ratio [Updated 30-MAR-00]
  8. Quick database performance overview, check all key database performance indicators
  9. Free SGA Memory Usage
  10. Explain Plan TEMPLATE [Updated 28-FEB-01]
  11. List Unpinned Objects in DB Cache
  12. Pin Oracle Standard Packages
  13. Pin All Database Packages [Updated 30-JUN-99]
  14. Tables Recently queried by a full scan
  15. Recently Accessed Indexes
  16. DB Block Buffer usage [Updated 30-JUN-99]
  17. Cost of current open cursors
  18. Session Hit Ratios
  19. Session Details [Updated 29-MAR-00]
  20. SQL Text of database locks [Updated 12-SEP-00]
5. RMAN
6. Utilities
  1. Count Rows For Table Partitions
  2. Set Session in Trace
  3. Database Info
  4. Convert EPOC date
  5. Database Uptime [Updated 8-Jan-02]
  6. Database Layout [Updated 30-Jul-02]
  7. Database Documentor
  8. Count all records on database for given table owner
  9. Change to another database user without knowing the password
  10. Disable all database triggers
  11. Enable all database triggers
  12. Compare Objects of 2 different databases [Updated 28-Feb-01]
  13. Size SGA based on an existing database
  14. Calculate Average Row length for a given table [Updated 06-JUN-00]
7. Monitor
  1. Monitor Session
  2. Redo History
  3. Monitor Redo Logs
  4. Monitor Users [Updated 06-JUN-00]
  5. Monitor all database lock details
  6. Monitor all database deadlocks
  7. Monitor what users are doing in Rollback Segments [Updated 06-JUN-00]
  8. Monitor the key statistics for Open Cursors
  9. Monitor SQL Area [Updated 30-JUN-99]

Oracle Applications
1. Profile Options
2. Apps Login Details

Platform Specific UNIX Scripts
1. Common
2. Backup Scripts
  1. Cold backup [Updated 8-Jan-02]
  2. Hot backup
  3. Set database in ARCHIVELOG mode
  4. Full database export and UNIX compress
  5. Full database import and UNIX uncompress

rem ----------------------------------------------------------------------- rem Filename: appinfo.sql rem Purpose: Script to display status of al


set head on

column OsId       format A10 justify left
column CpId       format 999999
column Opid       format 999
column Manager    format A30
column Status     format A20
column Started_At format A30

column Cpid       heading 'Concurrent|Process ID'
column OsId       heading 'System|Process ID'
column Opid       heading 'Oracle|Process ID'
column Manager    heading 'Concurrent Manager Name'
column Status     heading 'Status|of Concurrent|Manager'
column Started_At heading 'Concurrent Manager|Started at'

select distinct Concurrent_Process_Id CpId, PID Opid,
      Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
      P.process_status_code Status,
      To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from   Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where  Q.Application_Id = Queue_Application_ID
 and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
 and  Spid = Os_Process_ID
 and  Process_Status_Code not in ('K','S')
order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
/

http://www.orafaq.com/wiki/Scripts

Monday, June 15, 2009

Important tables in apps

Concurrent Manager

FND_CONCURRENT_QUEUES FND_CONCURRENT_PROGRAMS FND_CONCURRENT_REQUESTS FND_CONCURRENT_PROCESSES FND_CONCURRENT_QUEUE_SIZE

FND

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

Some important queries.........!

sql> select file_name , tablespace_name from dba_datafile_files where tablespace_name like
'%HRD%';

sql> select open_mode from v$database;

sql> desc v$database;

sql> select log_mode from v$database;

sql> select name from v$tablespace;

sql> select name from v$datafile;

sql> select name from v$tablespace;

sql> select count(*) from v$tablespaces;

Bakup

sql> alter tablespace HRD begin backup ;
copy the datafile to the backup location
sql> alter tablespace HRD end backup;

sql> select open_mode from v$datafile;

sql> alter database open reset logs;

Tablespace Offline:

sql> alter database datafile '/u01/appsmode/jet/oradata/data/HRD01.dbf' offline;

exit
copy the datafile from backup

recover datafile 7;

sql> alter database datafile '/u01/appsmode1/jet/oradata/data/hrd01.dbf' online;

Friday, June 12, 2009

Upgrading Sun JRE with Oracle Applications 11i.

I will focus on the upgrade process itself - step by step.

I tested the upgrade on the following test environment:
EBS 11.5.10.2

Database 10.2.0.3

ATG Rollup 5

Developer 6i patchset 18

OS RHEL4.

Be aware that before upgrading to Java Plug-in you must upgrade to Developer 6i patchset 18 or

later (currently the latest patchset is 19).

* You can use my previous post in order to Upgrading Developer 6i with Oracle Apps 11i to
patchset 18.

Download JRE plug-in Oracle E-Business Suite interoperability patch - 6863618

Download the Sun JRE Plug-in

Select Java Runtime Environment (JRE) 6 Update X (select the latest available update,

currently it’s 5)

Select Windows offline installation, multi-language

Rename the downloaded installation file jre-6_uX-windows-i586-p.exe to j2se1600x.exe In my

case rename jre-6_u5-windows-i586-p.exe to ==>> j2se16005.exe

Copy the j2se1605.exe file to $COMMON_TOP/util/jinitiator on the Apps Tier node

If you are on Developer 6i patchset 18 you should apply forms patches 6195758 & 5884875. **

Skip this step if you are on Developer 6i patchset 19.

Download Patches 6195758 & 5884875

Apply patch 6195758

Stop all applications processes by adstpall.sh

Unzip p6195758_60827_GENERIC.zip

cd 6195758

cp -r $ORACLE_HOME/forms60/java/oracle/forms/ handler/UICommon.class

$ORACLE_HOME/forms60/java/oracle/forms/ handler/UICommon.class.PRE_BUG6195758

cp -r $ORACLE_HOME/forms60/java/ oracle/forms /handler/ComponentItem.class

$ORACLE_HOME/forms60/java/oracle/forms/

handler/ComponentItem.class.PRE_BUG6195758

cp oracle/forms/handler/UICommon.class $ORACLE_HOME/forms60/java/oracle/forms/

handler/UICommon.class

cp oracle/forms/handler/ComponentItem.class
$ORACLE_HOME/forms60/java/oracle/forms/ handler/ComponentItem.class

Apply Patch 5884875

Unzip p5884875_60827_GENERIC.zip

cd 5884875

cp -r $ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class

$ORACLE_HOME/forms60/java/oracle/forms/ engine/Main.class.PRE_BUG5884875

cp -r $ORACLE_HOME/forms60/java/ oracle/forms/ handler/AlertDialog.class

$ORACLE_HOME/forms60/java/oracle/forms/ handler/AlertDialog.class.PRE_BUG5884875

cp oracle/forms/engine/Main.class

$ORACLE_HOME/forms60/java/oracle/forms/engine/Main.class

cp oracle/forms/handler/AlertDialog.class $ORACLE_HOME/forms60/java/oracle/forms/

engine/AlertDialog.class

Run adadmin -> Generate Applications Files menu -> Generate product JAR files

Apply the Interoperability patch 6863618

Make sure all applications processes are down

Enable maintenance mode: Execute adadmin -> Change Maintenance Mode (5) -> Enable

Maintenance Mode (1)

Unzip p6863618_11i_GENERIC.zip

Cd 6863618

Run adpatch to apply patch driver u6863618.drv

cd [PATCH_NUMBER]/fnd/bin

Execute the following command where X represent the update number: $ txkSetPlugin.sh

1600X In my case: $ txkSetPlugin.sh 16005

Disable maintenance mode: Execute adadmin -> Change Maintenance Mode (5) -> Disable

Maintenance Mode (2)

Start all applications processes by adstrtall.sh

Verify installation by sign in Oracle EBS and select a forms based responsibility.

For those who worry about performance, take a look at this benchmark: Benchmark comparison

test with Jinitiator 1.3.1.23 and Java Plug-in 1.5.0_05 – performance whitepaper For more

information
Note: 290807.1 - Upgrading Sun JRE with Oracle Applications 11i

Configure Workflow..........?

1. go to application> services> dovecot>click check box

2. Go to /etc/mail/sentmail.mc

/smtp
port=25,Address=192.168.20.18.name=MTA'

:wq!

Telnet 192.168.20.18:25

service restart sentmail

> Application > Internert>Email>

Toools>Settings>Add a account>

create > Process > Discard folder

Thursday, June 11, 2009

Begin backup

ALTER SYSTEM SWITCH LOGFILE;
alter tablespace SYSTEM begin backup;
alter tablespace CTXD begin backup;
alter tablespace OWAPUB begin backup;
alter tablespace ODM begin backup;
alter tablespace PORTAL begin backup;
alter tablespace APPS_UNDOTS1 begin backup;
alter tablespace APPS_TS_TX_DATA begin backup;
alter tablespace APPS_TS_TX_IDX begin backup;
alter tablespace APPS_TS_SEED begin backup;
alter tablespace APPS_TS_INTERFACE begin backup;
alter tablespace APPS_TS_SUMMARY begin backup;
alter tablespace APPS_TS_NOLOGGING begin backup;
alter tablespace APPS_TS_ARCHIVE begin backup;
alter tablespace APPS_TS_QUEUES begin backup;
alter tablespace APPS_TS_MEDIA begin backup;
alter tablespace OLAP begin backup;
exit

hotbackup

#/bin/bash.
/PROD/d01/proddb/9.2.0/PROD_geprod.env
sqlplus system/manager
@/PROD/d01/backup_scripts/beginbackup.sql
cd /PROD/d01/NOWDATE=`date +%d-%m-%y_HOT`export NOWDATE
tar cf - proddata gzip -c > $NOWDATE.tgz.
/PROD/d01/proddb/9.2.0/PROD_geprod.env
sqlplus system/manager @/PROD/d01/backup_scripts/endbackup.sql

how to compress archieve logs.?

#/bin/bash

cd /PROD/d01/archivelogs

compress *.dbf

Step by Step Information on Oracle Application R12 Environment files

Received numerous emails from my blog readers to start working on blog. Thanks for all of your appreciation ,support and encouragement to start again. My todays post will be based on Oracle Application R12. As I received request from my blog reader to continue with the series of R12. I will continue with R12 articles also going to cover other areas like Database 11g, Oracle Application server, SOA suites etc in my future posts. In this post I am going to discuss about R12 Oracle Environment files.Environment Files and Control scripts:Environment files are in place to manage the setup and configuration within the Oracle Application R12 system.Rapid install generates/create number of different environment files.
· .env => $ORACLE_HOME (10.2.0.2) => Oracle Server Ent. Edition
· .env => $ORACLE_HOME (AS 10.1.2)=> Oracle Tools Tech Stack
· .env=> $ORACLE_HOME (AS 10.1.3)=> Java Tech Stack

· .env => $APPL_TOP => Oracle Applications

· APPS.env => $APPL_TOP =>Consolidated Environment fileHere

CONTEXT_NAME is referred to _.CONTEXT_NAME.env is called environment setup file.

Whereas APPS.env.env is called a consolidated environment file which reside under APPL_TOP directory.Now question must be arise in your mind what is in
CONTEXT_NAME.env environment file.As pre R12 DBA must be aware of and fresher might be

thinking what it consist of. So here comes the answer....CONTEXT_NAME.env consist of :

· Product Directories and Subdirectories

· Product Directories Paths

· Other environment informationOracle application have important and core environment file is

.envAlso you should know what parameters are part of

.env environment file.Here you go to know the description of these parameters.

· APPLFENV - This parameter refers to name of the .env environment file.

· APPL_TOP - This parameter refers to top-level directory for this installation of Oracle applications.

· INST_TOP - This parameter refers to top-level directory for this instance.

· FND_TOP - This parameter refers to path to AOL directory. For e.g /d01/oracle/apps/apps_st/appl/fnd/12.0.0

· AU_TOP - This parameter refers to path to the applications utilities directory. For e.g /d01/oracle/apps/apps_st/appl/au/12.0.0.

· NLS_LANG - This parameter refers to territory,language and character set installed in the database. For e.g AMERICAN_AMERICA.UTF8

· NLS_DATE_FORMAT - This parameter refers to National language support date format.For e.g The default is "DD-MON-RR", e.g 16-JUL-08.
·

NLS_NUMERIC_CHARACTERS - This parameter refers to National language support numberic separators. The defulat is period and comman (".,").

· _TOP - This parameter refers to path to a product's top directory

· GWYUID - This parameter refers to the user name/password that give access to the initial sign on.

· APPLDCP - This parameter refers to whether PCP is being used. This should be set to OFF if not using PCP.

· APPLTMP - This parameter refers to directory for temp file in Oracle Applications.

· APPLPTMP - This parameter refers to directory for temp PL/SQL output files.

· APPLCNAM - This parameter refers to 8.3 file name convention for concurrent manager log and output files format.

· APPLLOG - This parameter refers to concurrent manager log subdirectory.

· APPLOUT - This parameter refers to concurrent manager output files subdirectory.

· APPLCSF - This parameter refers to the top-level directory for concurrent manager log and output files if they are consolidated into single direcotry for all products.

· PATH - This parameter refers to sets directory search path

· FNDNAM - This parameter refers to the name of the schema to which the system administration responsibility connects.

· ADMIN_SCRIPTS_HOME - This parameter refers to directory under $INST_TOP that identifies the location of control scripts.

· PLATFORM - This parameter refers to the O/S currently in use. The value of this parameter should match with $APPL_TOP/admin/adpltfrom.txt

Scipts

run_hots.sql

spool hot_backup_run.lst
alter tablespace TOOLS begin backup;
!cp -p /u02/app/oracle/oradata/rh1dev1/tools01.dbf
/ubackup/hot_backup_dump/rh1dev1
!gzip -f /ubackup/hot_backup_dump/rh1dev1/tools*.dbf
alter tablespace TOOLS end backup;
alter tablespace USERS begin backup;
!cp -p /u02/app/oracle/oradata/rh1dev1/users01.dbf
/ubackup/hot_backup_dump/rh1dev1
!gzip -f /ubackup/hot_backup_dump/rh1dev1/users*.dbf
alter tablespace USERS end backup;
alter database backup controlfile to trace;
alter database backup controlfile to
‘/ubackup/hot_backup_dump/rh1dev1/control.15062000135844’;
alter system switch logfile;
spool off
exit

create_analyze_script.sql

REM Note the hard coded owner. You need to modify this or use Dynamic SQL.
set heading off
set feedback off
set linesize 180
set pagesize 32767
spool analyze_customer_tables.sql
select ‘analyze table CUSTOMER.’ table_name ‘ estimate statistics;’
from dba_tables
where owner = ‘CUSTOMER’
/
spool off
set heading on
set feedback on

tail-alert

# The following is a handy shell script to check the end
# of the alert.log for a database identified by $ORACLE_SID
# I normally run this script several times a day and immediately
# whenever problems are reported.
# I usually give this script 755 permissions.
tail -150 $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert*.log more

compare_users.sql

REM Get two database users and show their roles.
REM
accept user_1 prompt ‘Enter the first user: ‘
accept user_2 prompt ‘Enter the second: ‘
select grantee, granted_role from dba_role_privs where
grantee in (upper(‘&user_1’),upper(‘&user_2’))
order by granted_role, grantee
/

show_tablespaces.sql

set linesize 132
set pagesize 65
set heading off
set feedback off
set verify off
col tablespace_name format a30
col file_name format a60
col bytes format 999,999,999,999,999
col status format a15
spool tablespaces.lst
select to_char(sysdate,’MM-DD-YYYY HH:MM’)from dual;
set heading on
select tablespace_name, file_name, bytes, status from dba_data_files
Oracle DBA on Unix and Linux
540
order by tablespace_name, file_name
/
spool off

show_segments.sql

REM Note the hard coded owner, you will have to fix this for your system
set linesize 180
col tablespace_name format a16
col segment_name format a30
col segment_type format a6
col initial_extent format 9,999,999,999
col next_extent format 9,999,999,999
col bytes format 99,999,999,999
spool verify_import-2000.lst
select tablespace_name, segment_name, segment_type, initial_extent,
next_extent, bytes, extents
from dba_segments
where owner = ‘CUSTOMER’
order by tablespace_name, segment_type, segment_name
/
spool off

show_rollback_contention.sql

set linesize 180
col name format a15
select a.name, b.extents, b.rssize, b.xacts “Active X-actions”, b.waits,
b.gets,
optsize, status
from v$rollname a, v$rollstat b
where a.usn = b.usn
/

show_redo_logs.sql

set linesize 180
col member format a50
col bytes format 999,999,999,999
select v$log.group#, members, member, v$log.status, bytes, archived
from v$log, v$logfile
where v$log.group# = v$logfile.group#;

show_index_depth.sql

REM B*Tree indexes should not go past 4 levels, performance suffers.
REM Rebuild anything greater than 3, but remember it will lock the table from
dml
REM unless you are using 8i online rebuilds (which take space instead).
REM Also remember to run analyze before running this
REM
col owner format a15
accept user_name1 prompt ‘Enter index owner to examine: ‘
select owner, table_name, index_name, blevel, last_analyzed from dba_indexes
where upper(owner) = upper(‘&user_name1’)
order by blevel
/
set heading off
select ‘Note: blevel should not be greater than 3’ from dual
/
set heading on

show_filestat.sql

set linesize 180
col tablespace_name format a20
col file_name format a52
col PHYRDS format 999,999,999
col PHYWRTS format 999,999
col PHYBLKRD format 999,999,999
col PHYBLKWRT format 999,999
spool show_filestat.lst
select tablespace_name, file_name, PHYRDS, PHYWRTS, PHYBLKRD, PHYBLKWRT
from v$filestat, dba_data_files
where file_id = file#
order by PHYRDS, PHYWRTS desc
/
spool off

show_dba_rollback_segs.sql

select segment_name, owner, tablespace_name, initial_extent,
next_extent, min_extents, max_extents,
status, instance_num from dba_rollback_segs
/

show_session_short.sql

select s.username, osuser, status, server as “Connect Type”,
to_char(logon_time,’fmHH:MI:SS AM’) as “Logon Time”,
sid, s.serial#, p.spid as “UNIX Proc”
from v$session s, v$process p
where s.paddr = p.addr
and s.username is not null
order by status, s.username, s.program, logon_time
/

login.sql

REM This file provides custom display settings with SQL*Plus.
REM Have it in the directory from where you start SQL*Plus.
set pagesize 25
col member format a60
col file_name format a60
col tablespace_name format a20
col owner format a15
col object_name format a30
col initial_extent format 999,999,999
col next_extent format 999,999,999
col bytes format 999,999,999,999
col sum(bytes) format 999,999,999,999
select name, created, log_mode from v$database;
show user;

Wednesday, June 10, 2009

How to Count Total Number of Users Connected to ORACLE Application

Goal : Want to determine how many users are connected to Oracle apps 11i,V$session does not give the true picture as mostly there are more than 1 oracle session for the same forms connection depending on how many forms the user has opened up.

Solution

Can use this SQL statement to count concurrent_users in Oracle apps:select count(distinct d.user_name) from apps.fnd_logins a, v$session b, v$process c, apps.fnd_user dwhere b.paddr = c.addrand a.pid=c.pidand a.spid = b.processand d.user_id = a.user_idand (d.user_name = 'USER_NAME' OR 1=1)

2:- In the Oracle Applications Manager, go to the site map, and select the "Applications Usage"option in the Activity region.

There you will be able to report on many usage questions, such as
Products InstalledApplication Users Per Module SummaryApplications Usage ReportsSuppliersPurchase Line Items Processed - Internet Supplier PortalPurchase Line Items Processed - Purchasing IntelligenceOrder Entry Lines Processed - Ordering ApplicationPurchase Line Items Processed - iProcurementExpense Reports Processed - Internet ExpenseInvoice Line Items Processed - Accounts ReceivablesNote the Application Users per module summary is based on the number of users that have Active responsibilities for the Application module. I am not aware of there being any check on last usage date for the responsibility.

3:- Also if you are using responsibilities connected to a custom-application, which is standard Consulting practice in some countries, those users will be counted as users of the custom-application and not users of the actual Application-module for the screens they are accessing.

4:- Run the following queries:- This will give the number of users on the system in the past 1 hour. select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1/24 and user_id != '-1';

This will give the number of users on the system in the past 1 day. select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';

This will show the activity in the last 15 minutes. select limit_time, limit_connects, to_char(last_connect, 'DD-MON-RR HH:MI:SS') "Last Connection time",

user_id, disabled_flag from icx_sessions where last_connect > sysdate - 1/96;5:-

Check the Note:233871.1
which will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.

Surely this will affect the performance but how it affects is based on the number of users logged in and accessing the forms.
Factoring in System Overhead========================In planning your organization€™ Sign€“On Audit implementation,
you should consider the additional system overhead required to precisely User and Data Auditing monitor and audit your users as they access Oracle Applications.

The more users you audit and the higher the level of auditing, the greater the likelihood of incurring additional system overhead.

Tuesday, June 9, 2009

backup of datatop and appltop........?

tar cvf - proddata gzip -c > proddata8006jun10.tgz
tar cvf - prodappl gzip -c > prodaappl8006jun10.tgz
cp proddata8006jun10.tgz /media/usbdisk1/afco/8006bkp/.
cp prodaappl8006jun10.tgz /media/usbdisk1/afco/8006bkp/.

Copy the data top and appl top

Patch info

SELECT * FROM (SELECT bug_number AS Patch FROM ad_bugs UNION SELECT patch_name AS Patch FROM ad_applied_patches) WHERE Patch IN (’Patch_Number’);

How and when to create a control file.....?

Q. When to create control files ?A. Create controlfiles when1. You have lost all your control files.2. When you want to rename a database name (db_name)Q. How to create control files.

You need a create controlfile script for recreating control files.SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:04:00 2003Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.Enter user-name: sys as sysdbaEnter password:Connected to:Oracle9i Enterprise Edition Release 9.2.0.2.1 - ProductionWith the Partitioning, Oracle Label Security,

OLAP and Oracle Data Mining optionsJServer Release 9.2.0.2.0 - Production

SQL> alter database backup controlfile to trace;Database altered.

SQL>This will create a trace file in the udump directory.Im my case it was

E:\oracle\admin\ORCL\udump\orcl_ora_2052.trc
Edit the file to point the path of the datafiles and redologfiles.
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 14
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILEGROUP 1
'E:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 100M,GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 100M,GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 100MDATAFILE'
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF','
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF','
E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF','
E:\ORACLE\ORADATA\ORCL\INDX01.DBF','
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS01.DBF','
E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF','
E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF','
E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF','
E:\ORACLE\ORADATA\ORCL\ODM01.DBF','
E:\ORACLE\ORADATA\ORCL\XDB01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS02.DBF','
E:\ORACLE\ORADATA\ORCL\USERS03.DBF','
E:\ORACLE\ORADATA\ORCL\USERS04.DBF'
CHARACTER SET WE8MSWIN1252;
ALTER DATABASE OPEN RESETLOGS;
and rename it to ctl.sql
Then startup the database in nomount mode and run the ctl.sql
file as sys as sysdba user
SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:25:14 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdbaEnter password:Connected to an idle instance.
SQL> startup nomountORACLE instance started.Total System Global Area 80812648 bytes
Fixed Size 453224 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> @E:\oracle\admin\ORCL\udump\ctl.sql

Control file created.Database altered.

SQL> select status from v$instance;

STATUS-------------OPENS

QL> select name from v$controlfile;
NAME-----
E:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL02.CTL
E:\ORACLE\ORADATA\ORCL\CONTROL03.CTLTo

rename the database change reuse to set in the create control file script as shown below
CREATE

CONTROLFILE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 14
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILEGROUP 1 '
E:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 100M,GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 100M,GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 100MDATAFILE'
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF','
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF','
E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF','
E:\ORACLE\ORADATA\ORCL\INDX01.DBF','
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS01.DBF','
E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF','
E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF','
E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF','
E:\ORACLE\ORADATA\ORCL\ODM01.DBF','
E:\ORACLE\ORADATA\ORCL\XDB01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS02.DBF','
E:\ORACLE\ORADATA\ORCL\USERS03.DBF','

E:\ORACLE\ORADATA\ORCL\USERS04.DBF'CHARACTER SET WE8MSWIN1252;

ALTER DATABASE OPEN RESETLOGS;

How and When to create a control file......?

Q. When to create control files ?
A. Create controlfiles when
1. You have lost all your control files.
2. When you want to rename a database name (db_name)

Q. How to create control files.
You need a create controlfile script for recreating control files.

SQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:04:00 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: sys as sysdbaEnter password:Connected to:Oracle9i Enterprise Edition Release 9.2.0.2.1 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.2.0 -
Production

SQL> alter database backup controlfile to trace;
Database altered.
SQL>This will create a trace file in the udump directory.
Im my case it wasE:\oracle\admin\ORCL\udump\orcl_ora_2052.trc
Edit the file to point the path of the datafiles and redologfiles.

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOGMAXLOGFILES 5MAXLOGMEMBERS 3MAXDATAFILES 14MAXINSTANCES 1MAXLOGHISTORY 226LOGFILEGROUP 1 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 100M,GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 100M,GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 100MDATAFILE'E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF','E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF','E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF','E:\ORACLE\ORADATA\ORCL\INDX01.DBF','E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF','E:\ORACLE\ORADATA\ORCL\USERS01.DBF','E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF','E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF','E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF','E:\ORACLE\ORADATA\ORCL\ODM01.DBF','E:\ORACLE\ORADATA\ORCL\XDB01.DBF','E:\ORACLE\ORADATA\ORCL\USERS02.DBF','E:\ORACLE\ORADATA\ORCL\USERS03.DBF','E:\ORACLE\ORADATA\ORCL\USERS04.DBF'CHARACTER SET WE8MSWIN1252;ALTER DATABASE OPEN RESETLOGS;and rename it to ctl.sqlThen startup the database in nomount mode and run the ctl.sql file as sys as sysdba userSQL*Plus: Release 9.2.0.2.0 - Production on Fri Feb 28 17:25:14 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Enter user-name: sys as sysdbaEnter password:Connected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area 80812648 bytesFixed Size 453224 bytesVariable Size 54525952 bytesDatabase Buffers 25165824 bytesRedo Buffers 667648 bytesS
QL> @E:\oracle\admin\ORCL\udump\ctl.sql
Control file created.
Database altered.
SQL> select status from v$instance;
STATUS-----------------------------------OPEN
SQL> select name from v$controlfile;
NAME--------------------------------------------------------E:\ORACLE\ORADATA\ORCL\CONTROL01.
CTLE:\ORACLE\ORADATA\ORCL\CONTROL02.
CTLE:\ORACLE\ORADATA\ORCL\CONTROL03.
CTLTo rename the database change reuse to set in the create control file script as shown belowCREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS ARCHIVELOGMAXLOGFILES 5MAXLOGMEMBERS 3MAXDATAFILES 14MAXINSTANCES 1MAXLOGHISTORY 226LOGFILEGROUP 1
'E:\ORACLE\ORADATA\ORCL\REDO01.LOG' SIZE 100M,GROUP 2 'E:\ORACLE\ORADATA\ORCL\REDO02.LOG' SIZE 100M,GROUP 3 'E:\ORACLE\ORADATA\ORCL\REDO03.LOG' SIZE 100MDATAFILE'
E:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF','
E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF','
E:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF','
E:\ORACLE\ORADATA\ORCL\INDX01.DBF','
E:\ORACLE\ORADATA\ORCL\TOOLS01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS01.DBF','
E:\ORACLE\ORADATA\ORCL\OEM_REPOSITORY.DBF','
E:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF','
E:\ORACLE\ORADATA\ORCL\DRSYS01.DBF','
E:\ORACLE\ORADATA\ORCL\ODM01.DBF','
E:\ORACLE\ORADATA\ORCL\XDB01.DBF','
E:\ORACLE\ORADATA\ORCL\USERS02.DBF','
E:\ORACLE\ORADATA\ORCL\USERS03.DBF','
E:\ORACLE\ORADATA\ORCL\USERS04.DBF'
CHARACTER SET WE8MSWIN1252;
ALTER DATABASE OPEN RESETLOGS;

Monday, June 8, 2009

WHAT IS RAC.........?

What is RAC and how is it different from non RAC databases?
RAC stands for Real Application Clusters. It allows multiple nodes in a clustered system to mount and open a single database that resides on shared disk storage. Should a single system fail (node), the database service will still be available on the remaining nodes.
A non-RAC database is only available on a single system. If that system fails, the database service will be down (single point of failure).

Can any application be deployed on RAC?
Most applications can be deployed on RAC without any modifications and still scale linearly (well, almost).
However, applications with 'hot' rows (the same row being accessed by processes on different nodes) will not work well. This is because data blocks will constantly be moved from one Oracle Instance to another. In such cases the application needs to be partitioned based on function or data to eliminate contention.

Do you need special hardware to run RAC?
RAC requires the following hardware components:
A dedicated network interconnect - might be as simple as a fast network connection between nodes; and
A shared disk subsystem.
Example systems that can be used with RAC:
Windows Clusters
Linux Clusters
Unix Clusters like SUN PDB (Parallel DB).
IBM z/OS in SYSPLEX
[edit] How many OCR and voting disks should one have?
For redundancy, one should have at lease two OCR disks and three voting disks (raw disk partitions). These disk partitions should be spread across different physical disks.
[edit] How does one convert a single instance database to RAC?
Oracle 10gR2 introduces a utility called rconfig (located in $ORACLE_HOME/bin) that will convert a single instance database to a RAC database. $ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml racconv.xml
$ vi racconv.xml
$ rconfig racconv.xml
One can also use dbca and enterprise manager to convert the database to RAC mode.
For prior releases, follow these steps:
Shut Down your Database: SQL> CONNECT SYS AS SYSDBA
SQL> SHUTDOWN NORMAL
Enable RAC - On Unix this is done by relinking the Oracle software.
Make the software available on all computer systems that will run RAC. This can be done by copying the software to all systems or to a shared clustered file system.
Each instance requires its own set of Redo Log Files (called a thread). Create additional log files: SQL> CONNECT SYS AS SYSBDA
SQL> STARTUP EXCLUSIVE
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
SQL> GROUP G4 ('RAW_FILE1') SIZE 500k,
SQL> GROUP G5 ('RAW_FILE2') SIZE 500k,
SQL> GROUP G6 ('RAW_FILE3') SIZE 500k;
SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;
Each instance requires its own set of Undo segments (rollback segments). To add undo segments for New Nodes: UNDO_MANAGEMENT = auto
UNDO_TABLESPACE = undots2
Edit the SPFILE/INIT.ORA files and number the instances 1, 2,...: CLUSTER_DATABASE = TRUE (PARALLEL_SERVER = TRUE prior to Oracle9i).
INSTANCE_NUMBER = 1
THREAD = 1
UNDO_TABLESPACE = undots1 (or ROLLBACK_SEGMENTS if you use UNDO_MANAGEMENT=manual)
# Include %T for the thread in the LOG_ARCHIVE_FORMAT string.
# Set LM_PROCS to the number of nodes * PROCESSES
# etc....
Create the dictionary views needed for RAC by running catclust.sql (previously called catparr.sql): SQL> START ?/rdbms/admin/catclust.sql
On all the computer systems, startup the instances: SQL> CONNECT / as SYSDBA
SQL> STARTUP;

[edit] How does one stop and start RAC instances?
There are no difference between the way you start a normal database and RAC database, except that a RAC database needs to be started from multiple nodes. The CLUSTER_DATABASE=TRUE (PARALLEL_SERVER=TRUE) parameter needs to be set before a database can be started in cluster mode.
In Oracle 10g one can use the srvctl utility to start instances and listener across the cluster from a single node. Here are some examples: $ srvctl status database -d RACDB
$ srvctl start database -d RACDB
$ srvctl start instance -d RACDB -i RACDB1
$ srvctl start instance -d RACDB -i RACDB2
$ srvctl stop database -d RACDB
$ srvctl start asm -n node2
Before Oracle 8.0, use the following command sequence from each node (using the old server manager): SVRMGR> connect INTERNAL
SVRMGR> set retries 5
SVRMGR> startup parallel retry .. or SVRMGR> startup shared
You can also use the SET INSTANCE instanceN command to switch between instances (if defined in TNSNAMES.ORA).
[edit] Can I test if a database is running in RAC mode?
Use the DBMS_UTILITY package to determine if a database is running in RAC mode or not. Example: BEGIN
IF dbms_utility.is_cluster_database THEN
dbms_output.put_line('Running in SHARED/RAC mode.');
ELSE
dbms_output.put_line('Running in EXCLUSIVE mode.');
END IF;
END;
/
For Oracle 8i and prior releases: BEGIN
IF dbms_utility.is_parallel_server THEN
dbms_output.put_line('Running in SHARED/PARALLEL mode.');
ELSE
dbms_output.put_line('Running in EXCLUSIVE mode.');
END IF;
END;
/
Another method is to look at the database parameters. For example, from SQL*Plus: SQL> show parameter CLUSTER_DATABASE
If the value of CLUSTER_DATABASE is FALSE then database is not running in RAC Mode.
[edit] How can I keep track of active instances?
You can keep track of active RAC instances by executing one of the following queries: SELECT * FROM SYS.V_$ACTIVE_INSTANCES;
SELECT * FROM SYS.V_$THREAD;
To list the active instances from PL/SQL, use DBMS_UTILITY.ACTIVE_INSTANCES().
[edit] How does one setup SQL*Net for RAC?
See Transparent Application Failover.
[edit] Can one see how connections are distributed across the nodes?
Select from gv$session. Some examples: SELECT inst_id, count(*) "DB Sessions" FROM gv$session
WHERE type = 'USER' GROUP BY inst_id;
With login time (hour): SELECT inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') "Hour when connected", count(*) "DB Sessions"
FROM gv$session
WHERE type = 'USER'
GROUP BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24')
ORDER BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24');

[edit] What is pinging and why is it so bad?
Starting with Oracle 9i, RAC can transfer blocks from one instance to another across the interconnect (cache fusion). This method is much faster than the old "pinging" method, where one instance had to write the block to disk before another instance could read it.
Oracle 8i and below:
Pinging is the process whereby one instance requests another to write a set of blocks from its SGA to disk so it can obtain it in exclusive mode. This method of moving data blocks from one instance's SGA to another is extremely slow. The challenge of tuning RAC/OPS is to minimize pinging activity.

Discoverer in oracle applications

Whats is Discoverer Server ?

Discoverer is and ad hoc query, reporting, analysis and publishing tool which help business users to get quick access to information from datawarehouse or OLTP systems.
In Apps, Discoverer version 4i used to be installed & configured out of the box which is desupported now.

You should configure Apps with Discoverer 10g (I am going to cover about how to configure discoverer 10g with E-Business Suite)

What should I know about Discoverer as an Apps DBA ?

Though each component in Apps like forms, reports, web server including Discoverer in itself are quite big and more you know about them better it is for you .

Few Important things you should know about Discoverer in Apps is like how to start/stop & how to access it.

In case of any issues you should be able to troubleshoot discoverer.

What is EUL ?
You will hear lot about EUL from business analyst which means End User Layer, which is the key to provide ease of use to underlying data in Oracle Apps Database.Technical things about discoverer in Apps ..--

In Apps default discoverer version 4i, is installed under
$ORACLE_HOME/discwb4--Script to start discoverer server is addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME-- This script in turn start discoverer processes which can be start by startall.sh under $ORACLE_HOME/discwb4/util (This script is different from adstrtal.sh)-- startall.sh calls scripts like startgatekeeper.sh, startlocator.sh , startoad.sh, startosagent.sh to start gatekeeper, locator, oad & osagent resp.--

In Apps two major way to access EUL is via Discoverer view & Discoverer Plus edition (You can use Discoverer Administration Edition as well)

Quiz to reader :A. How will you find discoverer version in Apps ?B. What URL you use to access Disco viewer & Disco plus .

concurrent manger trouble shooting steps

TROUBLESHOOTING CONCURRENT MANAGER STARTUP PROBLEMS - Applications 11.0
=======================================================================

IMPORTANT NOTE - Some of these steps are destructive and should not be run on a
Production system without advice from Oracle Support.

INTRODUCTION
============
This note is designed to help diagnose and resolve problems starting the Oracle
Applications Concurrent Manager. There can be many issues which prevent the
CCM from starting and the errors may not always indicate the true problem.
Whilst this note has been written with Oracle Applications 11.0 in mind,
many of these steps are still relevant for 10.7 and 11i.

CHECKS TO MAKE
==============

1) Check the log files for any reported errors. The default name for the
Internal Manager log file is 'CM_.log' on NT or >ManagerName>.mgr
on Unix. Also check any other manager log files in the $APPLCSF/$LOG directory
for relevant messages. Do these errors point to any obvious places to check
(e.g. database not running, TNS alias incorrect)? If these files do not exist
at all, there may be permission problems that prevent processes from writing
files to the proper directories.

2) Ensure that you are logged in as the 'applmgr' (or equivalent) user on Unix,
or if on NT that the service is being started by the 'applmgr' (or equivalent)
user.

3) If you are getting errors such as 'establish ICM unable to insert ICM record
APP-1167 APP-1244 Oracle error 1631 in insert_icm_record'
Check your database's tablespace for the FND tables to ensure you enough
free space to create the next extent. Also check the FND tables to ensure no
table has hit the maximum number of extents.

4) Log in to SQLPLUS as database user "apps".
Verify that view fnd_v$process exists: SELECT view_name
FROM all_views
WHERE view_name LIKE '%V$%';

If the view is missing or invalid, rebuild it. From the operating system,
as the applmgr user type:

FNDLIBR FND FNDCPBWV applsys/fnd SYSADMIN 'System Administrator' SYSADMIN

or as the applsys user run the following SQL statement

CREATE VIEW fnd_v$process AS
( SELECT P.Addr, P.Pid, S.Process Spid,
S.Osuser Username, P.Serial#,
S.Terminal, S.Program, P.Background,
P.Latchwait, S.Lockwait
FROM V$process P, V$session S
WHERE P.Addr = S.Paddr );

5) Close down all Concurrent Manager processes and clear database tables/log
files. This process will remove all history of Concurrent Jobs and remove all
scheduled jobs. Please contact Oracle Support for advice before doing this to a
Production installation.

a) Check there are no FNDLIBR, FNDCRM or other Concurrent Manager
processes running. This can be done via the
Concurrent Manager-->Administer screen, or from the operating system -
Note 68993.1 has a list of the process names to look for.

b) In SQLPLUS run the following SQL
UPDATE fnd_concurrent_requests
set phase_code = 'C', status_code = 'X'
where status_code = 'T';

UPDATE fnd_concurrent_queues SET running_processes = 0;

SELECT concurrent_queue_name, control_code, running_processes,
max_processes
FROM fnd_concurrent_queues;

The control_codes should be 'E' for deactivated managers, blank for all others
except FNDICM which should be 'X'. You will need to update the table
manually if you find any exceptions to these values.
For example:-
UPDATE fnd_concurrent_queues SET control_code = 'X'
WHERE concurrent_queue_name = 'FNDICM';

c) Delete all log and output files in FND_TOP/log, APPLCSF/log and APPLCSF/out if these files are no longer needed.
Having to many files in these directories has caused issues previously, so may be worth temporarily removing these files.

6) Check environment variables.

On NT using REGEDT32 check the following keys have these values:

WARNING - ensure you do not change or delete any registry Settings as this
may irrecoverably damage your Oracle or NT Server installation :-

HKEY_LOCAL_MACHINE-->Software-->Oracle-->Applications-->11.0.0-->APPL_CONFIG=
HKEY_LOCAL_MACHINE-->Software-->Oracle-->LOCAL=
HKEY_LOCAL_MACHINE-->Software-->Oracle-->Applications-->11.0.0-->-->DIAG=Y
HKEY_LOCAL_MACHINE-->Software-->Oracle-->Applications-->11.0.0-->-->pmon=20
HKEY_LOCAL_MACHINE-->Software-->Oracle-->Applications-->11.0.0-->-->LOCAL=
HKEY_LOCAL_MACHINE-->Software-->Oracle-->Applications-->11.0.0-->-->SERVICE_SID=

On Unix check :-

$ORACLE_SID
$LOCAL

If any of these settings are incorrect, reset them to the correct value.

7) Check the program versions

On NT check versions of FND_TOP/bin/FNDLIBR.EXE and AU_TOP/bin/FNDCORE.DLL.
This is achieved by highlighting the program file, then using the right mouse
button to click-->Properties-->Version. Compare this information to that found
in Bug 881201 - this patch delivers v11.1.179 of FNDCORE.DLL (pre-req of this
patch is to be on 11.0.3) Alternatively you may need to apply Patch 872159
which is FNDCORE.dll v11.0.215

On Unix you can run the following command to determine the version numbers:

'strings -a $FND_TOP\bin\FNDLIBR grep Header > fndlibr_versions.txt'

This will create a file called 'fndlibr_versions.txt' to send to Oracle Support.

8) Set up SQLNET trace and Database SQL tracing.
a) set TRACE_LEVEL_SERVER=16 in the SQLNET.ORA file on the database server.
b) Set SQL_TRACE=true in the init.ora file

NOTE - the database will need to be restarted for this change to take effect.
It will also generate trace files for every database session, which induces
a great deal of processing overhead as well as a lot of disk writing. Once
the database has been restarted, try starting the Concurrent Manager, then
stop the database and reset 'SQL_TRACE=false' in the init.ora file and
restart the database again.

9) Check the RDBMS version and ensure it is certified; certified combinations
change periodically.

10) Check dual tables: (assuming no-one is in the system and Concurrent Manager processes are not running)
a) select count(*) from sys.dual; There must be one and only one row returned.
b) select count(*) from apps.fnd_dual; There must be at least one row.

11) Check there are no invalid objects in the database. Connect as database
user "system" and run the following query:
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status != 'VALID';

If this returns any rows, we may need to identify why the object is invalid
and get it to compile before proceeding.

12) Check/change Applications profile options.
Check system level profile option 'Concurrent:Use ICM'; by default this should
be set to 'No'. Try changing this value to 'Yes' and see if this changes the
nature of the problem.

13) Check process monitor (PMON) method. Connect as database user "APPS".
SELECT profile_option_value
FROM fnd_profile_option_values
WHERE level_id = 10001
AND level_value = 0
AND application_id = 0
AND profile_option_id =
(SELECT profile_option_id
FROM fnd_profile_options
WHERE profile_option_name = 'CONC_PMON_METHOD');

This should return one row with a value of 'RDBMS' or 'LOCK'

If the value is 'RDBMS' run the script FND_TOP/sql/AFIMPMON.SQL - this will set
the PMON method to LOCK instead of RDBMS.

14) Stop database and restart database server.
If not already shutdown in previous step, stop and restart the database.
If running on NT, restart the NT Server completely.

15) If the above still fails,
if running on NT delete the concurrent manager and recreate from
scratch. This is described in the Applications on NT Installation
manual.

Once the above has been tried, please advise/send in the following information
to Oracle Support:-
a) On NT, versions of FND_TOP/bin/FNDLIBR.EXE and AU_TOP/bin/FNDCORE.DLL.
On Unix run
'strings $FND_TOP/bin/fndlibr grep Header > fndlibr_version.txt'.
b) SQLNET trace files (svr_*.trc)
c) Database trace files.
d) Confirmation there are no invalid packages for the APPS user.
e) Manager log files.
f) Advise of how many and which Concurrent Manager processes are running.

References
----------
Note 69336.1 - Basic information about Concurrent Managers
Bug 420021 - APP-988 ORACLE error 1480 in insert_icm_record
Bug 526567 - APP-1117 establish_icm:Error occurred in AFPDLGH when requesting lock handle.
Bug 636823 - NT: Error 2140 internel Windows NT Error
Bug 732112 - NT: only the Internal and Conflict Resolution managers come up
Bug 748854 - app-1117 establish icm_failed afpdlgh when requesting log handle
Note 921379.101 - Defunct processes