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.

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.1
4. Start your concurrent managers.

The other method is to update the bad rows in the fnd_concurrent_requests table manually.

1. Query up the concurrent process that is stuck and make note of it’s Request ID.
2. Log onto unix as applmgr and Log onto SQL*Plus as APPS.
3. Perform this update statement:
update fnd_concurrent_requests
set phase_code = ‘C’,
status_code = ‘X’
where request_id = &request_id;
This statement will set the request to completed terminated
4. should return a value of “1 row updated”. If so then commit the change, if not, rollback and find the error.
5. Check the concurrent process in the application and it should now be set to Completed Terminated.

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.

Thanks for Reading.

The following steps explain what needs to take place in order to recover from a crash where a DB control file gets corrupted:

The likely error thrown in this scenario is as follow:

Fri Mar  7 22:00:46 2008
Hex dump of (file 0, block 1) in trace file /u01/app/oracle/admin/teng/bdump/teng_j001_9438.trc
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Bad check value found during control file header read
Data in bad block:
type: 21 format: 2 rdba: 0x00000001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00001501
check value in block header: 0xf8de
computed block checksum: 0x1501

Problem Description:
====================

The OS crashed and at the time two instances were running.

After correcting the OS issue then you try to start the instance(s)
and upon mounting the database you receive the following errors:

ORA-00227: corrupt block detected in controlfile: (block %s, # blocks %s)
Cause: A block header corruption or checksum error was detected on
reading the controlfile.
Action: Use the CREATE CONTROLFILE or RECOVER DATABASE USING BACKUP
CONTROLFILE command.

ORA-00202: control file: 
Cause: This message reports the name of the file involved in other
messages.
Action: See the associated messages for a description of the problem.

Problem Explanation:
====================

Control file is corrupt.

Solution Description:
=====================

Try to use one of the mirrored copies of the control file. You should have
mirrored images of your control file. Oracle recommends at least 2 mirrored
copies. Their locations should be listed in your “init.ora” file.

Try using one of these mirrored control files to enable you to bring up the
database by copying one of them over the bad one.

Or

Comment out the bad control file from the “control_files” parameter in your
“init.ora” file and try bringing up the database with one of your mirrored
control files.

If the above doesn’t work, then all control files are corrupt and you must
create a new control file.

Take a backup of the control files from the last backup and follow steps
below to recreate a good control file.

===========
OPTION ONE
===========

1. The alert log should identify which control file it is trying to use.

See error ora-202.
>  Note this (control1).

2. Make a backup of control files as they are now.

cp controlfiles controlfile.bk

3. Take one of the other control files and copy over the bad control file
noted above.

If this does not work, then proceed to the next step.

==============
OPTION TWO
==============

Comment the bad control file from the control_files parameter.

In the init.ora:

control_file = comment out the one listed in alert log from ora-202.

Try starting the instance.

If you receive the same errors as previously with the name of the control file
changed to controlfile2, then go to the next option.

=============
OPTION THREE
=============

Issue:
1. Shutdown immediate.

2. Startup nomount pfile= .

3. Alter database mount;

4. Alter database backup controlfile to trace;

5. Shutdown.

6. Check for trace file:

Either in user_dump_dest = trace file with latest time stamp; nothing
or background_dump_dest.

The file name should be:  ora
.trc.

This should contain a script to recreate the control file.

7. Save file as control.sql.

8. Edit file to start with:  startup nomount.

9. Check Oracle_sid : should be set to correct sid.

10. Svrmgrl>  connect internal

11. @control.sql

You will reeceive a message: Stmt processed.

waiting on svrmgrl prompt

12. Svrmgrl media recovery complete

svrmgrl> select sysdate from dual; june 4, 1999

Database is up and open.

13. Select on main application table:

sqlplus :  connected ok.

Solution Explanation:
=====================

If all the control files are corrupt, then you will need to create a new control
file to be able to bring up your database.

Once again Thanks for reading   !

You have a tablespace that is growing out of control and you need to re org.

this is a very lengthy subject but below are the steps you ll need to take in order to rename your datafiles, you can then trun off autoextend, and run a monitoring script to either:

1) Resize a current datafile and adding extra KB
2) Addin a new datafile with a given # of KB.

Here is an example: (remember you can only do this if you are sys)
sqlplus “/ as sysdba”
shutdown immediate;
!mv /p03/oracledbf/pan/AWPDR.dbf /p03/oracledbf/pan/AWPDR01.dbf
!mv /p03/oracledbf/pan/AWMBeanDB.dbf /p03/oracledbf/pan/AWMBeanDB01.dbf
!mv /p03/oracledbf/pan/AWMSUI.dbf /p03/oracledbf/pan/AWMSUI01.dbf
startup mount;

alter database rename file ‘/p03/oracledbf/pan/AWPDR.dbf’ TO ‘/p03/oracledbf/pan/AWPDR01.dbf’;
alter database rename file ‘/p03/oracledbf/pan/AWMBeanDB.dbf’ TO ‘/p03/oracledbf/pan/AWMBeanDB01.dbf’;
alter database rename file ‘/p03/oracledbf/pan/AWMSUI.dbf’ TO ‘/p03/oracledbf/pan/AWMSUI01.dbf’;

alter database datafile ‘/p03/oracledbf/pan/AWPDR01.dbf’ autoextend off;
alter database datafile ‘/p03/oracledbf/pan/AWMBeanDB01.dbf’ autoextend off;
alter database datafile ‘/p03/oracledbf/pan/AWMSUI01.dbf’ autoextend off;

alter database open;

To Verify:
col file_name format a50;
select file_name, autoextensible from dba_data_files;
clear columns;
eof

As always Thanks for reading

Every Database should have the screen below, It would let everyone know how important this is

The only thing a DBA has to get right is recovery. In order to do that, they need the backups.

-Again Thanks for reading

because when you own it, you own the world. You can literally do anything you want (given you have that privilege of course)…

1. Quick, what does the following Unix/Linux command do?

ls -R | grep “:$” | sed -e ’s/:$//’ -e ’s/[^-][^\/]*\//–/g’ -e ’s/^/ /’ -e ’s/-/|/’

If you said, “Well, that’s obvious; it shows a graphical representation of the current sub-directories.“, you’d be correct.

2. You can use the command to create a program called tree that would work something like the following:

[ fayoubi@mars - Saturday, February 09, 2008 - 12:11:04 PM]
[ /home/fayoubi ] dba/scripts/tree.sh

/home/fayoubi
.
|-dba
|—cfg_refresh
|—junk
|—onetime
|—scripts
|—snaps
|—–2006_07
|——-nic
|——-nie
|——-nij
|—–2006_11
|——-nic
|——-nie
|——-nij
|—–2007_03
|——-nic
|——-nie
|—–2007_08
|—–2007_09
|—–2007_q1
|—–2007_q2
|——-nic
|——-nie
|—–2007_q4
|——-nic
|——-nie
|—–2008_q1
|——-nic
|——-nie
|—Training
|-example
|—yourdir
|—–mydir
|-junk
|-nipw
|-tempdir
|-tmp
|-Vignette
|-web

[ fayoubi@mars - Saturday, February 09, 2008 - 12:11:07 PM]
[ /home/fayoubi ]

3. Here is the code ready to go in a shell script:

#!/bin/sh
#######################################################
#  UNIX TREE                                          #
#                                                     #
#  Displays Structure of Directory Hierarchy          #
#  -------------------------------------------------  #
#  This tiny script uses "ls", "grep", and "sed"      #
#  in a single command to show the nesting of         #
#  sub-directories.                                   #
#                                                     #
#  Setup:                                             #
#     % cd ~/apps/tree                                #
#     % chmod u+x tree.sh                             #
#     % ln -s ~/apps/tree/tree.sh ~/bin/tree          #
#                                                     #
#  Usage:                                             #
#     % tree [directory]                              #
#                                                     #
#  Examples:                                          #
#     % tree                                          #
#     % tree /etc/opt                                 #
#     % tree ..                                       #
#                                                     #
#######################################################

echo
if [ "$1" != "" ]  #if parameter exists, use as base folder
then cd "$1"
fi
pwd
ls -R | grep ":$" |
sed -e 's/:$//' -e 's/[^-][^/]*//--/g' -e 's/^/   /' -e 's/-/|/'
# 1st sed: remove colons
# 2nd sed: replace higher level folder names with dashes
# 3rd sed: indent graph three spaces
# 4th sed: replace first dash with a vertical bar
if [ `ls -F -1 | grep "/" | wc -l` = 0 ]   # check if no folders
then echo "   -> no sub-directories"
fi
echo
exit

That’s it.

Again y’all, thanks for reading.

Does oracle really expect you DBAs to know every vdollar table by heart ?

The answer is no. I have run into the issue so many times. specially if you are oncall and that big poster that has every table name etc… is in you cube and nowhere close to the terminal u are sitting in . Here is a quick solution to this problem be SYS before you try this:

15:05:26 SQL> !cat /home/fayoubi/dba/scripts/vdollar.sql
select table_name, column_name
from dba_tab_columns
where table_name like ‘V_%’
and column_name like UPPER(’%&column_name%’)
order by table_name
/

15:05:43 SQL>

Enter value for column_name: INDEX
old   4: and column_name like UPPER(’%&column_name%’)
new   4: and column_name like UPPER(’%INDEX%’)

TABLE_NAME                     COLUMN_NAME
—————————— ——————————
V$OBJECT_USAGE                 INDEX_NAME
VTABLE$                        VINDEX
V_$ASM_ALIAS                   REFERENCE_INDEX
V_$ASM_ALIAS                   ALIAS_INDEX
V_$ASM_ALIAS                   PARENT_INDEX
V_$ASM_DISK                    COMPOUND_INDEX
V_$ASM_DISK_STAT               COMPOUND_INDEX
V_$ASM_FILE                    COMPOUND_INDEX
V_$CONTROLFILE_RECORD_SECTION  LAST_INDEX
V_$CONTROLFILE_RECORD_SECTION  FIRST_INDEX
V_$INDEXED_FIXED_COLUMN        INDEX_NUMBER

TABLE_NAME                     COLUMN_NAME
—————————— ——————————
V_$LOADISTAT                   INDEXNAME

12 rows selected.

Quickly you get back which tables (v$ ones) have the column you are looking for. you can now select the one you want to query and know what you ll get back.

Hope you found this very helpful.

-Thanks for reading.

What: Pinning an Object to the shared pool area is the process of tellin oracle to leave an object in the shared pool at all time - unless the database is shutdown.

Why: Pinning Oracle objects prevents oracle from doing moving objects in and out of the shared pool which can degrade performance (specifically if the objects in question are core libraries (large core packages that always get called by your applications - i.e. Web application )

When: Pinning should happen when a database is started, while calling your custom statup.sh script, u can call a pin_objs.sh which can in turn call a sql file, that determines what objects to pin and then pins them to the shared pool.

How: Let s get to the core of this

insert into OBJECTS_TO_PIN (object_name, object_type_flag,
owner, date_entered)
SELECT name, decode(type,’PACKAGE’, ‘P’,'PACKAGE BODY’,'P’,
‘TRIGGER’,'R’,'PROCEDURE’,'P’), owner, sysdate
FROM v$db_object_cache
WHERE type in (’TRIGGER’,'PROCEDURE’,'PACKAGE BODY’)
AND executions > 0
AND kept = ‘NO’
AND (loads > 1
OR sharable_mem >=40000)
ORDER BY
sharable_mem desc,
loads,
executions desc;

commit;

——————————————————————————————————

******Once all these objects are identified and inserted into your table OBJECTS_TO_PIN******

——————————————————————————————————
declare
own varchar2(100);
nam varchar2(100);
flagx varchar2(1);

cursor objs is
select owner, object_name, object_type_flag
from objects_to_pin;

begin
open objs;
loop
fetch objs into own, nam, flagx;
exit when objs%notfound;
dbms_shared_pool.keep(own||’.'||nam, flagx);
end loop;

end;
/

USE Pinning it will have a great advantage on performance of your DBs.

I hope you enjoyed reading

Let me ask you a legitimate question:
How does your organization control code versioning ?

IT departments will often struggle with this unless of course your organization writes software in which case a versionning software is a must and more likely the IT department will tag along and use a Depot. Among the bunch in the market the most famous are : Perforce, CVS, OldVersion.

So most likely, the last version in your release branch will exist on your production system. Suppose however that you have some doubt there could be discrepencies between your Version software and what s on production. How do u go about getting that code from production ???

I received this request this morning and found myself asking this:”If I honor this request, how many like it will come up !!!”, This examples shows how you can get source code from a production environment dumped to your dev/test env:

rem **********************************************************************
rem $Id:  $
rem Title:  Get Package Spec & Body
rem Description:  Get Package SQL from Prod to Dev or Test.
rem History:
rem 20-Mar-2007  Fahd Ayoubi         Created.
rem
rem **********************************************************************

set verify off
set heading off
set serveroutput off
set pause off
set feedback off
set time off

define package=&pkg_name
spool get_pkgtext.log

select text
from dba_source
where owner = ‘APPS’ AND NAME = ‘&package’ AND Type = ‘PACKAGE’
UNION ALL
select text
from dba_source
where owner = ‘APPS’ and NAME = ‘&package’ AND Type = ‘PACKAGE BODY’;
/

spool off

The code above will prompt you for a package name. when that is entered, all the source code from that package is dumped into a file.

You can wrap the script above with a sh script that allows you to ftp rename and execute the generated file assentially copying 100% of your production code into whatever environment you choose.

-Thanks for reading

Oracle Applications 11i utilizes the Cost-Based optimizer, so it is important that the database has current statistics for all tables and indexes. Oracle provides a set of utilities to be used to gather statistics on a regular basis. These tools are:

Concurrent Programs: “Gather Schema Stats” and “Gather Table Stats”
Package: FND_STATS

The concurrent programs call the FND_STATS package to gather statistics. The FND_STATS package is a “wrapper” for DBMS_STATS which uses extra information about the oracle applications table to calculate histograms for certain tables, and exclude certain interface tables from normal statistics gathering.

The old fashioned �??analyze table�?� and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance. This is because the cost-based SQL Optimizer (CBO) relies on the quality of the statistics to choose the best execution plan for all SQL statements. The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats results in faster SQL execution plans.

Let�??s see how dbms_stats works. It�??s easy! Here is a sample execution of dbms_stats with the options clause:

I found that the best practice is to bundle your sql calls in a shell script that you call from a cron (a scheduled job at the unix level) here is an example:

15 6 * * 0 ORACLE_SID=fahd_db; . $HOME/cron.env > /dev/null; /mount01/app/oracle/admin/table_stats.sh ALL_TABLES 15 50000000 > /u01/app/oracle/admin/stats_zwebt.out

15 6 * * 0 ———–> This means the cron will run weekly at 6:15 pm More details
table_stats.sh ———> This is the shell scrip that will be execeuted.

ALL_TABLES 15 50000000 ——> Three parameters, analyze all tables, 15%, tables with less than 50 million records.

stats_zwebt.out ———-> Is the file where everything will be dumped; a log file for record trail.

Thanks for reading.

Assume the following;

You refresh your development instance from your porsuction environment. Guess what, if some concurent manager was running during the Hot backup, they wil lbe flagged as running during the refresh (snapshot). So , you want to go in the application layer and kill these requests . the screenshot below shows how you acheive that.

However, the step above will not flag the request to be killed. Witout getting into too much details, Oracle’s way of dealing with the phantom phenomenon is exactly this. So the question is how u make sure it s dead. You can do that using the following query:

update APPLSYS.FND_CONCURRENT_REQUESTS
set PHASE_CODE=’C', STATUS_code=’C’
where request_id = 278373874;/

The query above will update phase_code and status_code which pretty much define what you see on the apps tier.

Remember that FND tables are propriatory to oracle and no DDLs should be applied. Also they are defined under APPLSYS schema. You need to have sysdba access to be able to run the query and have access to “system Administrator” on the apps tier.