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.

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.

Statspack fundamentally differs from the well known UTLBSTAT/UTLESTAT tning scripts by collecting more information, and also by storing the performance statistics permanently in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using the report provided, which includes an ‘instance health and load’ summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters.

Statspack improves on the existing UTLBSTAT/UTLESTAT performance scripts in the following ways:

- Statspack collects more data, including high resource SQL (and the optimizer execution plans for those statements)

- Statspack pre-calculates many ratios useful when performance tuning, such as cache hit ratios, per transaction and per
second statistics (many of these ratios must be calculated manually when using BSTAT/ESTAT)

- Permanent tables owned by PERFSTAT store performance statistics; instead of creating/dropping tables each time, data is inserted into the pre-existing tables. This makes historical data comparisons easier

- Statspack separates the data collection from the report generation. Data is collected when a ’snapshot’ is taken; viewing the data collected is in the hands of the performance engineer when he/she runs the performance report

- Data collection is easy to automate using either dbms_job or an OS utility

NOTE: The term ’snapshot’ is used to denote a set of statistics gathered at a single time, identified by a unique Id which includes the snapshot number (or snap_id). This term should not be confused with Oracle’s Snapshot Replication technology.

How does Statspack work?

Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning.

Statspack users will become familiar with the concept of a ’snapshot’. ’snapshot’ is the term used to identify a single collection of performance data. Each snapshot taken is identified by a ’snapshot id’ which is a unique number generated at the time the snapshot is taken; each time a new collection is taken, a new snap_id is generated.

The snap_id, along with the database identifier (dbid) and instance number (instance_number) comprise the unique key for a snapshot (using this unique combination allows storage of multiple instances of a Clustered database in the same tables).

Once snapshots are taken, it is possible to run the performance report. The performance report will prompt for the two snapshot id’s the report will process. The report produced calculates the activity on the instance between the two snapshot periods specified, in a similar way to the BSTAT/ESTAT report; to compare - the first snap_id supplied can be considered the equivalent of running BSTAT; the second snap_id specified can be considered the equivalent of ESTAT. Unlike BSTAT/ESTAT which can by its nature only compare two static data points, the report can compare any two snapshots specified.

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

]]> http://www.maroc-it.ma/blogs/fahd/?feed=rss2&p=67 http://www.maroc-it.ma/blogs/fahd/?p=66 http://www.maroc-it.ma/blogs/fahd/?p=66#comments Fri, 23 May 2008 18:42:38 +0000 fahd Oracle Technical sql/sh scripts http://www.maroc-it.ma/blogs/fahd/?p=66 Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

It is worth to tune the following important parameters

1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter

Actual Undo Size

SELECT SUM(a.bytes) “UNDO_SIZE”
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE
———-
209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
“UNDO_BLOCK_PER_SEC”
FROM v$undostat;

UNDO_BLOCK_PER_SEC
——————
3.12166667

DB Block Size

SELECT TO_NUMBER(value) “DB_BLOCK_SIZE [KByte]”
FROM v$parameter
WHERE name = ‘db_block_size’;

DB_BLOCK_SIZE [Byte]
——————–
4096

Optimal Undo Retention

209‘715‘200 / (3.12166667 * 4′096) = 16′401 [Sec]

Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”,
SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = ‘UNDO’
AND c.status = ‘ONLINE’
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’
/

ACTUAL UNDO SIZE [MByte]
————————
200

UNDO RETENTION [Sec]
——————–
10800

OPTIMAL UNDO RETENTION [Sec]
—————————-
16401

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   !

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

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

-Again Thanks for reading

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.

STEP ONE : Modify your initSID.ora

Modify your initSID.ora to include the following three lines:

log_archive_start   = true
log_archive_dest    = /mount_point/oradata/SID/ARCHIVE/arch
log_archive_format  = _%s.arc

log_archive_start: This parameter specifies whether archiving should be started automatically when the database is started.

log_archive_dest: This value specifies the default location of the file to be used when archiving redo log files.

log_archive_format: This parameter is used to specify the default file name format for the archive log files, this value is appended to the log_archive_dest value ( %s is the log sequence number ).

STEP TWO : Starting archive logging

The final step to starting archive logging, involves an Oracle SQL*DBA command which is run when the database is mounted but not open.

With the database shut down, use one of the many DBA tools available to manually start the database ( I still prefer to use SQL*DBA ) and execute the following sequence of commands:

startup mount exclusive
connect internal
alter database archivelog;
alter database open;

Now check to ensure archive logging is running. Whilst still connected internal, execute the command archive log list as shown below:

SQLDBA> archive log list
Database log mode              ARCHIVELOG
Automatic archival             ENABLED
Archive destination            /mount_point/oradata/SID/ARCHIVE/arch
Oldest online log sequence     2
Current log sequence           3
SQLDBA>

As usual thanks for reading