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 simplest way to rebuild and index is:

Alter index rebuild tablespace ;

This also has the advantage of not preventing the index from being used whilst it is being rebuilt. The tablespace must be specified otherwise the index will be rebuilt in the current users default tablespace.

Rebuilding the index also allows new storage parameters to be assigned to the index.

If the index is corrupt, it must be dropped and recreated.

1. Firstly, identify the original creation parameters:

   SELECT COLUMN_NAME FROM USER_IND_COLUMNS
   WHERE INDEX_NAME = 'index_name';

   SELECT TABLE_NAME, UNIQUENESS, TABLESPACE_NAME,
   INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
   PCT_INCREASE, PCT_FREE
   FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';

   The above will give you the columns on which the index is based.

2. And then drop the index and recreate it:

   Drop index ;

   Create [UNIQUE] index 
   On
   (  [ ,  ] )
   tablespace
   PCTFREE
   
   STORAGE (
   NEXT K
   INITIAL K
   PCTINCREASE
   
   MIN_EXTENTS 
   MAX_EXTENTS  );

Today, a battle is raging between the “academics” who do not believe that indexes should be rebuilt without expensive studies and the “pragmatists” who rebuild indexes on a schedule because their end-users report faster response times.

I highly recommend Reading up on the differences between these two different approaches.

-Thanks for reading and come back again.

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

In Here we focus on the resize operation rather than Creation:

To resize TEMP, you must drop and recreate it. You cannot drop the temporary temp tablespace, so these commands create a temporary temp tablespace, then drop and recreate temp with the new size.

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
‘/u08/oracledbf/zwebt/temp101.dbf’ SIZE 800M
EXTENT MANAGEMENT LOCAL
uniform size 1M;

alter database default temporary tablesp@ace temp1;

drop tablespace temp including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp0*.dbf

! rm /u*/oracledbf/zwebt/temp0*.dbf

CREATE TEMPORARY TABLESPACE temp TEMPFILE

‘/u08/oracledbf/zwebt/temp01.dbf’ SIZE 2048M

EXTENT MANAGEMENT LOCAL

uniform size 1M;

alter database default temporary tablespace temp;

drop tablespace temp1 including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp10*.dbf

! rm /u*/oracledbf/zwebt/temp10*.dbf

HERE IS TO REVERT THESE CHANGES:

CREATE TEMPORARY TABLESPACE temp1 TEMPFILE
‘/u08/oracledbf/zwebt/temp101.dbf’ SIZE 800M
EXTENT MANAGEMENT LOCAL
uniform size 1M;

alter database default temporary tablespace temp1;

drop tablespace temp including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp0*.db

! rm /u*/oracledbf/zwebt/temp0*.dbf

CREATE TEMPORARY TABLESPACE temp TEMPFILE
‘/u08/oracledbf/zwebt/temp01.dbf’ SIZE 800M
EXTENT MANAGEMENT LOCAL
uniform size 1M;

alter database default temporary tablespace temp;

drop tablespace temp1 including contents and datafiles;

! ls /u*/oracledbf/zwebt/temp10*.dbf

! rm /u*/oracledbf/zwebt/temp10*.dbf

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

From a command prompt:

ftp updates.oracle.com (metalink username and password)
ftp> bin
## For 9i R2(9.2.x) and 10GR1(10.1.x)
ftp> cd /2617419
ftp> get p2617419_10102_GENERIC.zip
ftp> quit
## For 10GR2(10.2.x) ftp> cd /4898608
ftp> get p4898608_10202_GENERIC.zip
ftp> quit

2)

## For 9i R2(9.2.x) and 10GR1(10.1.x)

Then upload or move the p2617419_10102_GENERIC.zip to ORACLE_HOME and uncompress it:

Example:
==================================================================

% mv p2617419_10102_GENERIC.zip $ORACLE_HOME
% cd $ORACLE_HOME
% unzip p2617419_10102_GENERIC.zip

Archive:p2617419_10102_GENERIC.zip
inflating: OPatch/docs/README.txt
inflating: OPatch/docs/bt1.txt
inflating: OPatch/docs/bt2.txt
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/jlib/opatch.jar
extracting: OPatch/opatch.ini
inflating: OPatch/opatch
inflating: OPatch/opatch.pl
inflating: OPatch/perl_modules/Query.pm
inflating: OPatch/perl_modules/Version.pm
inflating: OPatch/perl_modules/RollBack.pm
inflating: OPatch/perl_modules/XML.pm
inflating: OPatch/perl_modules/compile.csh
inflating: OPatch/perl_modules/opatchIO.pm
inflating: OPatch/perl_modules/LsInventory.pm
inflating: OPatch/perl_modules/AttachHome.pm
inflating: OPatch/perl_modules/Command.pm
inflating: OPatch/perl_modules/Apply.pm
inflating: OPatch/opatch.bat
inflating: README.txt

% cd OPatch
% opatch version
PERL5LIB=/oracle/V920/Apache/perl/lib/5.00503:./perl_modules; export PERL5LIB
/oracle/V920/Apache/perl/bin/perl ./opatch.pl version
./opatch.pl version: 1.0.0.0.49

## For 10GR2(10.2.x)

Then upload or move the p4898608_10202_GENERIC.zip to ORACLE_HOME and uncompress it:
Example
=======
% mv p4898608_10202_GENERIC.zip $ORACLE_HOME
% cd $ORACLE_HOME
% unzip p4898608_10202_GENERIC.zip
Archive: p4898608_10202_GENERIC.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/Prereq_Users_Guide.txt
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/jlib/opatchutil.jar
inflating: OPatch/jlib/opatchprereq.jar
creating: OPatch/opatchprereqs/
creating: OPatch/opatchprereqs/opatch/
inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml
inflating: OPatch/opatchprereqs/opatch/rulemap.xml
inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml
creating: OPatch/opatchprereqs/oui/
inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml
extracting: OPatch/opatchprereqs/opatch_prereq.sh
inflating: OPatch/opatchprereqs/prerequisite.properties
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl
inflating: OPatch/opatch.ini
inflating: OPatch/emdpatch.pl
inflating: OPatch/README.txt
% cd OPatch
% opatch version
Invoking OPatch 10.2.0.2.1

OPatch Version: 10.2.0.2.1

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

3) Now you can set the Opatch directory in your PATH variable so you can execute the opatchcommand from anywhere.

Example:
==================================================================

For Korn / Bourne shell
% export PATH=$PATH:$ORACLE_HOME/Opatch

And that should do it !

As Always thanks for reading