How to check the Physical RAM and Swap Space in Unix/Linux Systems

How to check the Physical RAM and Swap Space in Unix/Linux Systems.
Following are the most requred OS commands while installing and configuring Oracle Software on Unix/Linux Operating Systems.

Operating System: AIX 5L Based Systems (64-Bit)
Physical RAM: # /usr/sbin/lsattr -E -l sys0 -a realmem
Swap Space: # /usr/sbin/lsps -a

Operating System: HP Tru64 UNIX
PPhysical RAM: # /bin/vmstat -P grep "Total Physical Memory"
Swap Space: # /sbin/swapon -s

Operating System: HP-UX Itanium
Physical RAM: # /usr/contrib/bin/machinfo grep -i Memory
Swap Space: # /usr/sbin/swapinfo -a

Operating System: HP-UX PA-RISC (64-Bit)
Physical RAM: # grep "Physical:" /var/adm/syslog/syslog.log
Swap Space: # /usr/sbin/swapinfo -a

Operating System: IBM zSeries Based Linux, LinuxItanium/POWER/x86/x86-64
Physical RAM: # grep MemTotal /proc/meminfo
Swap Space: # grep SwapTotal /proc/meminfo

Operating System: Solaris SPARC 64-Bit/x86/x86-64
Physical RAM: # /usr/sbin/prtconf grep "Memory size"
Swap Space: # /usr/sbin/swap -s

Download Oracle 11g New Features PDF

Dear Blog viewers,

Today, when I'm giving answers to in OTN forums, I came across a link for Oracle 11g New Features Document, prepared by Ahmed Baraka.

Below is the link for downloading Oracle 11g New Features pdf.

Oracle 11g New Features for Administrators !

Few more documents available in his website.

Ahmed's Other Documents

Regards,
poorna,
http://poornaappsdba.blogspot.com/
http://syedracdba.wordpress.com/

Subpartition Statistics

Subpartition Statistics

I’ve been working on a project with a very large subpartitioned table and I’ve been trying to come up with a sensible statistics generation strategy. This application is on a Linux based RAC system currently running 10g R2 (10.2.0.3). I’ve been trying to use every source of information available and of course, many 10053 traces. I came across this little blurb on Jonathon Lewis’ web site:
Sub-Partition stats (12th June)
Page 39, Second paragraph:
The problem of partitions and table-level statistics echoes on down the chain to subpartitions. If you want to query exactly one subpartition of one partition, then the optimizer uses the statistics for that one subpartition.
This is wrong. It looks as if Oracle hardly uses sub-partition statistics at all. I have a test case on 10.2.0.1 which shows the optimizer using the partition level statistics to calculate the cardinality of a very simple query that has been identified as targeting exactly one sub-partition of one partition. However, the access path is a full table scan of that sub-partition, and the cost of the scan is clearly derived from the number of blocks in the sub-partition.
Thanks to Adrian Billington for bringing this to my attention.
I must say that this mirrors what I’ve found. For the queries that I’ve been modeling, the 10053 traces lead me to believe that the optimizer only considers the row and block counts for the pruned subpartitions in its calculations. All other information, including histogram statistics, all appear to be taken from the partition level only.
The following excerpt from a 10053 trace is the only place that I’ve found where the optimizer considers the subpartition statistics:
Table Stats::
Table: mytab Alias: mytab (making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 286200000 #Blks: 1598304 AvgRowLen: 82.00
SUBPARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
Partition [5]
#Rows: 286200000 #Blks: 1598304 AvgRowLen: 82.00
#Rows: 25004700 #Blks: 135309 AvgRowLen: 82.00 <== This is the only place that reflects subpartition stats. Category: Oracle DBA PL/SQL Exception Handling The following blog post by Tom Kyte (http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html) prompted me to re-evaluate my error handling in the PL/SQL that I’ve written for a large project that I’m working on. Tom feels very strongly about exception handling, and while I don’t disagree, I hadn’t considered the value of the originating line number as all that important. In reading some of the sources cited at the end of this article I got curious about seeing just how hard it would be to save a little more information about the original error, including the original line number. The following are the results of my investigation. I should note that all of this was done with Oracle 10g and utilizes the DBMS_UTILITY and its “format” functions. Earlier releases won’t be able to make use of some/all(?) of these features, but I guess that’s the price of progress. I offer the following basic tenets of exception handling with a nod to both Tom and Steven Feuerstein for the inspiration: 1) Trap exceptions as close to the point of the error being raised as possible. 2) Never hide an exception. Exceptions should be raised because of errors and errors should always be propogated to the calling function (the client application in Tom speak). 2a) If a WHEN OTHERS is used then it MUST re-raise the exception. 3) Always capture the line number and error message where the exception is raised (this can be hard if you’re following number 1). 4) Handle all exceptions that can be handled. Do the work up front rather than forcing someone to debug your “generic” exceptions later. The following example is taken straight from the Oracle documentation (1) with the exception of changes to the “Top_With_Logging” procedure. I’ve commented the original code in the exception handler and added calls for each of the three DBMS_UTILITY “format” functions. CREATE OR REPLACE PROCEDURE Top_With_Logging IS -- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack. -- But SqlErrm is subject to some length limits, -- while Format_Error_Stack is not. BEGIN P5(); EXCEPTION WHEN OTHERS THEN -- Log_Errors ( 'Error_Stack...' || Chr(10) || -- DBMS_UTILITY.FORMAT_ERROR_STACK() ); -- Log_Errors ( 'Error_Backtrace...' || Chr(10) || -- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() ); -- DBMS_OUTPUT.PUT_LINE ( '----------' ); DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' ); DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() ); -- DBMS_OUTPUT.PUT_LINE ( '----------' ); DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' ); DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() ); -- DBMS_OUTPUT.PUT_LINE ( '----------' ); DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' ); DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() ); -- DBMS_OUTPUT.PUT_LINE ( '----------' ); END Top_With_Logging; / The following will show the creation of each of the test procedures as shown in the original example: SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 31 09:12:37 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
END P0;
/ 2 3 4 5 6
Procedure created.

SQL> l
1 CREATE OR REPLACE PROCEDURE P0 IS
2 e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
3 BEGIN
4 RAISE e_01476;
5 END P0;
6*
SQL> CREATE OR REPLACE PROCEDURE P1 IS
BEGIN
P0();
END P1;
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE P2 IS
BEGIN
P1();
END P2;
/
SHOW ERRORS
CREATE OR REPLACE PROCEDURE P3 IS
BEGIN
P2();
END P3;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P4 IS
BEGIN P3(); END P4;
/
CREATE OR REPLACE PROCEDURE P5 IS
BEGIN P4(); END P5;
/
SHOW ERRORS 2 3 4 5
Procedure created.
SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.
SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.
SQL> No errors.
SQL> SQL> 2 3
Procedure created.
SQL> 2 3
Procedure created.
SQL>
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Top_Naive IS
BEGIN
P5();
END Top_Naive;
/ 2 3 4 5
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Procedure created.
SQL>
Now we’ll run the first example calling the Top_Naive procedure which will simply call the P5 procedure which will invoke the chain of procedures with the P0 procedure raising an error. The Top_Naive procedure has no exception handling so we see the full chain of events. Notice that the initial exception that occurs in P0 tells us the line number of the offending statement (during the install above I listed the PO procedure to show the line numbers).
SQL> Set ServerOutput On
call Top_Naive()SQL>
2 /
call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3
Now we’ll run the second example and see the bare output of each of the three DBMS_UTILITY “format” functions:
SQL> Set ServerOutput On
SQL> call Top_With_Logging();
----------
FORMAT_CALL_STACK:
----- PL/SQL Call Stack -----
object line object
handle number name
0xee5cbec8 16 procedure TEST.TOP_WITH_LOGGING
----------
FORMAT_ERROR_STACK:
ORA-01476: divisor is equal to zero
----------
FORMAT_ERROR_BACKTRACE:
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512:
at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_WITH_LOGGING", line 6
----------

Call completed.
SQL>
We see that the FORMAT_CALL_STACK function only shows us the information from the call to the FORMAT_CALL_STACK function. The FORMAT_ERROR_STACK call shows us the actual error message message only, and the FORMAT_ERROR_BACKTRACE call shows us the full execution path with originating line number. Note that we still need the FORMAT_ERROR_STACK call in order to get the original error message.
Another way to achieve the same thing would be to capture the exception at the source. The following shows a different take on handling the exception and being able to capture the line number and error message. This example simply outputs the messages using DBMS_OUTPUT, but in reality this could be logged to an error table:
CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise; -- Note that we still raise an exception!
END P0;
/
SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise;
END P0;
/ 2 3 4 5 6 7 8 9 10 11
Procedure created.
SQL> call Top_Naive();
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4
call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 9
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "CPPTEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3
SQL>
Now we see that the error message and line number are output and then the exception is propogated back to the initial invoking procedure. This provides an easy way to trap all exception information and provides the possiblity of capturing the entire exception path. This may not be as important for an interactive system, but can be invaluable for a “batch” system running in an automated fashion.
Sources:
(1) Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2),
Part Number B14258-01, Section on DBMS_UTILITY
(2) http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html
(3) http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html
(4) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:433029981484
(5) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4684561825338

PL/SQL Exception Handling

PL/SQL Exception Handling

The following blog post by Tom Kyte (http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html) prompted me to re-evaluate my error handling in the PL/SQL that I’ve written for a large project that I’m working on. Tom feels very strongly about exception handling, and while I don’t disagree, I hadn’t considered the value of the originating line number as all that important. In reading some of the sources cited at the end of this article I got curious about seeing just how hard it would be to save a little more information about the original error, including the original line number. The following are the results of my investigation.

I should note that all of this was done with Oracle 10g and utilizes the DBMS_UTILITY and its “format” functions. Earlier releases won’t be able to make use of some/all(?) of these features, but I guess that’s the price of progress. I offer the following basic tenets of exception handling with a nod to both Tom and Steven Feuerstein for the inspiration:

1) Trap exceptions as close to the point of the error being raised as possible.

2) Never hide an exception. Exceptions should be raised because of errors and errors should always be propogated to the calling function (the client application in Tom speak).

2a) If a WHEN OTHERS is used then it MUST re-raise the exception.

3) Always capture the line number and error message where the exception is raised (this can be hard if you’re following number 1).

4) Handle all exceptions that can be handled. Do the work up front rather than forcing someone to debug your “generic” exceptions later.

The following example is taken straight from the Oracle documentation (1) with the exception of changes to the “Top_With_Logging” procedure. I’ve commented the original code in the exception handler and added calls for each of the three DBMS_UTILITY “format” functions.


CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/

The following will show the creation of each of the test procedures
as shown in the original example:

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 31 09:12:37 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
END P0;
/ 2 3 4 5 6

Procedure created.

SQL> l
1 CREATE OR REPLACE PROCEDURE P0 IS
2 e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
3 BEGIN
4 RAISE e_01476;
5 END P0;
6*
SQL> CREATE OR REPLACE PROCEDURE P1 IS
BEGIN
P0();
END P1;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P2 IS
BEGIN
P1();
END P2;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P3 IS
BEGIN
P2();
END P3;
/
SHOW ERRORS

CREATE OR REPLACE PROCEDURE P4 IS
BEGIN P3(); END P4;
/
CREATE OR REPLACE PROCEDURE P5 IS
BEGIN P4(); END P5;
/
SHOW ERRORS 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3 4 5
Procedure created.

SQL> No errors.
SQL> SQL> 2 3
Procedure created.

SQL> 2 3
Procedure created.

SQL>
No errors.
SQL>
SQL> CREATE OR REPLACE PROCEDURE Top_Naive IS
BEGIN
P5();
END Top_Naive;
/ 2 3 4 5

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE Top_With_Logging IS
-- NOTE: SqlErrm in principle gives the same info as Format_Error_Stack.
-- But SqlErrm is subject to some length limits,
-- while Format_Error_Stack is not.
BEGIN
P5();
EXCEPTION
WHEN OTHERS THEN
-- Log_Errors ( 'Error_Stack...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_STACK() );
-- Log_Errors ( 'Error_Backtrace...' || Chr(10) ||
-- DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
END Top_With_Logging;
/

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Procedure created.

SQL>

Now we’ll run the first example calling the Top_Naive procedure which will simply call the P5 procedure which will invoke the chain of procedures with the P0 procedure raising an error. The Top_Naive procedure has no exception handling so we see the full chain of events. Notice that the initial exception that occurs in P0 tells us the line number of the offending statement (during the install above I listed the PO procedure to show the line numbers).

SQL> Set ServerOutput On
call Top_Naive()SQL>
2 /
call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3

Now we’ll run the second example and see the bare output of each of the three DBMS_UTILITY “format” functions:

SQL> Set ServerOutput On
SQL> call Top_With_Logging();
----------
FORMAT_CALL_STACK:
----- PL/SQL Call Stack -----
object line object
handle number name
0xee5cbec8 16 procedure TEST.TOP_WITH_LOGGING

----------
FORMAT_ERROR_STACK:
ORA-01476: divisor is equal to zero

----------
FORMAT_ERROR_BACKTRACE:
ORA-06512: at "TEST.P0", line 4
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "TEST.P3", line 3
ORA-06512:
at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_WITH_LOGGING", line 6

----------

Call completed.

SQL>

We see that the FORMAT_CALL_STACK function only shows us the information from the call to the FORMAT_CALL_STACK function. The FORMAT_ERROR_STACK call shows us the actual error message message only, and the FORMAT_ERROR_BACKTRACE call shows us the full execution path with originating line number. Note that we still need the FORMAT_ERROR_STACK call in order to get the original error message.

Another way to achieve the same thing would be to capture the exception at the source. The following shows a different take on handling the exception and being able to capture the line number and error message. This example simply outputs the messages using DBMS_OUTPUT, but in reality this could be logged to an error table:

CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise; -- Note that we still raise an exception!
END P0;
/

SQL> CREATE OR REPLACE PROCEDURE P0 IS
e_01476 EXCEPTION; pragma exception_init ( e_01476, -1476 );
BEGIN
RAISE e_01476;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE());
raise;
END P0;
/ 2 3 4 5 6 7 8 9 10 11

Procedure created.

SQL> call Top_Naive();
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 4

call Top_Naive()
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "TEST.P0", line 9
ORA-06512: at "TEST.P1", line 3
ORA-06512: at "TEST.P2", line 3
ORA-06512: at "CPPTEST.P3", line 3
ORA-06512: at "TEST.P4", line 2
ORA-06512: at "TEST.P5", line 2
ORA-06512: at "TEST.TOP_NAIVE", line 3

SQL>

Now we see that the error message and line number are output and then the exception is propogated back to the initial invoking procedure. This provides an easy way to trap all exception information and provides the possiblity of capturing the entire exception path. This may not be as important for an interactive system, but can be invaluable for a “batch” system running in an automated fashion.

Sources:

(1) Oracle® Database PL/SQL Packages and Types Reference, 10g Release 2 (10.2),
Part Number B14258-01, Section on DBMS_UTILITY

(2) http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html

Services and backups

Services and backups
I’ve been a proponent of using services both to help categorize database connections and to use them to help classify workload. For applications where “you can’t touch the code” this can be the only way to come up with categories to measure application workload. Recently I’ve started using a “backup” service to identify the cost backups contribute to overall system workload. The problem with services however is that there are only limited ways to associate a service to a connection. For “internal” connections a service can be associated with a scheduler job through a job class, and can also be associated with parallel execution or parallel DML, or with advanced queuing (although I’ve only used scheduler jobs with services). As for client connections, the service is assigned with the “SERVICE_NAME” parameter on the connect string. This implies a connection through the listener since this is typically part of Oracle net services. This can pose a problem for backups since this creates, in my opinion, an unnecessary dependence on the listener being up in order to take advantage of running the backup process through a service.
Typically a backup program, and we’ll talk about RMAN here but it could be any product really, will connect to the database through a “bequeath” connection by setting the ORACLE_SID environment variable as part of the environment setup. This type of connection doesn’t require that a listener be available, and also doesn’t need a tnsnames.ora entry. Unfortunately there is also no way to associate the connection with a service that I’ve found. However, I have stumbled upon a solution. It turns out that it is possible to define a “bequeath” connection in the tnsnames.ora file and subsequently use the SERVICE_NAME parameter as part of the connect string. This type of connection, even though part of the tnsnames.ora file, doesn’t require a listener to work. I stumbled upon this in Metalink note 132764.1, and it seems that it has always existed, at least since Oracle7 and SQL Net V2, based on some of the other notes available on Metalink.
The following will show an example of setting up the TNS entry and then setting up the backup service and running RMAN connected to the new backup service.
The first task is to create the service in the database. The following example is for a single instance database, in RAC you should use srvctl.
SQL> show parameters service

NAME TYPE VALUE
---------------------------- ----------- -------------------------------------------
service_names string orcl.appsdba.com
SQL> alter system set service_names = "orcl.appsdba.com,backup.appsdba.com" scope=both;

System altered.

SQL> show parameters service

NAME TYPE VALUE
---------------------------- ----------- -------------------------------------------
service_names string orcl.appsdba.com,backup.appsdba.com
SQL>
Verify the listener service registration:
$ lsnrctl services

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 25-JUN-2008 10:12:00

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "backup.appsdba.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl.appsdba.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
The command completed successfully
$
Let’s show the service registration in the database:
SQL> select * from v$services where service_id = 10;

SERVICE_ID NAME NAME_HASH
---------- -------------------------- ----------
NETWORK_NAME
------------------------------------------------
CREATION_ CREATION_DATE_HASH
--------- ------------------
10 backup.appsdba.com 833319543
backup.appsdba.com
25-JUN-08 638794112

SQL>
Now, we’ll show the tnsnames.ora entry. Note that the protocol is “beq” for bequeath and that we’ve used the SERVICE_NAME for our backup service. Also note that as part of the definition we’ve concatenated the “oracle” executable name and our oracle SID name for the ARGV0 value.
ORCL_BACKUP =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = BEQ)
(PROGRAM = oracle)
(ARGV0 = oracleorcl)
(ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')
)
(CONNECT_DATA =
(SERVICE_NAME = backup.appsdba.com)
)
)
Now let’s test the database connection:
$ sqlplus system@orcl_backup

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 10:19:06 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select USERNAME, SERVER, PROGRAM, STATUS, SERVICE_NAME
from v$session where username = 'SYSTEM';

USERNAME SERVER PROGRAM STATUS
------------------------------ --------- ---------------------------------- --------
SERVICE_NAME
----------------------------------------------------------------
SYSTEM DEDICATED sqlplus@appsdba.com (TNS V1-V3) ACTIVE
backup.appsdba.com

SQL>
So, we’ve connected to the database and verified through the v$session view that we’ve connected to our backup service.
Now let’s connect with RMAN:
$ $ORACLE_HOME/bin/rman nocatalog target /@orcl_backup

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: ORCL (DBID=3806185278)
using target database controlfile instead of recovery catalog

RMAN>
In another session we’ll query v$session again:
SQL> l
1 select username, status, program, service_name from v$session
2* where username = 'SYS'
SQL> /

USERNAME STATUS PROGRAM
------------------------------ -------- --------------------------------
SERVICE_NAME
----------------------------------------------------------------
SYS ACTIVE sqlplus@appsdba.com (TNS V1-V3)
SYS$USERS

SYS INACTIVE rman@appsdba.com (TNS V1-V3)
backup.appsdba.com

SYS INACTIVE rman@appsdba.com (TNS V1-V3)
backup.appsdba.com

SQL>
Now let’s verify that we can still connect with no listener:
/u01/app/oracle/local/bkup$ lsnrctl stop

LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 25-JUN-2008 10:33:09

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=orcl)))
The command completed successfully
/u01/app/oracle/local/bkup$
Now let’s see if we can connect with our normal TNS alias:
/u01/app/oracle/local/bkup$ sqlplus system@orcl

SQL*Plus: Release 10.1.0.5.0 - Production on Wed Jun 25 10:33:23 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12541: TNS:no listener

Enter user-name:
And no, we can’t because the listener is down. Now let’s see if our backup connection will work:
/u01/app/oracle/local/bkup$ $ORACLE_HOME/bin/rman nocatalog target /@orcl_backup

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: ORCL (DBID=3806185278)
using target database controlfile instead of recovery catalog

RMAN> exit

Recovery Manager complete.
/u01/app/oracle/local/bkup$
And indeed it does. We now have a backup service and it is independent of the Oracle network listener.

RMAN

Throttling RMAN
Oracle’s Recovery Manager product has been around since Oracle 8.0 and has always included limits on channel I/O rates. Prior to 9.0.1 the parameter was called READRATE and was an option on the SET LIMIT CHANNEL command. In 9.0.1 Oracle introduced the RATE option as part of the ALLOCATE CHANNEL command. The problem however wat that it was difficult to determine exactly what affect the throttling had.
Typically the desire to throttle RMAN stems from the desire to not consume all of the database server’s I/O bandwidth running a backup. The idea is generally to perform a backup in the background. In 10g Release 1 Oracle has now made this a little easier. I actually stumbled across this only recently. Like a lot of additions Oracle makes this one goes pretty much unnoticed until you need it. Then it seems pretty cool since it makes the job of quantifying I/O rates a little easier.
The following shows an example of the output of throttling an RMAN backup. It appears that if no throttling takes place then no messages. I believe this includes the setting of the RATE parameter so high that it doesn’t have an affect.
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18>
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2000000 K FORMAT ‘/u01/backups/DBSID/DBSID.bkup.%U’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2000000 K FORMAT ‘/u01/backups/DBSID/DBSID.bkup.%U’ RATE 2000000;
new RMAN configuration parameters are successfully stored
channel ORA_DISK_1: backup set complete, elapsed time: 00:12:26
channel ORA_DISK_1: throttle time: 0:09:01
channel ORA_DISK_2: finished piece 1 at 08-DEC-06
channel ORA_DISK_2: backup set complete, elapsed time: 00:12:26
channel ORA_DISK_2: throttle time: 0:10:39
Finished backup at 08-DEC-06

Oracle Scheduler

Oracle Scheduler
the scheduler was used to automate the refresh of a materialized view and an Oracle Text (i.e. context) index associated with that view. As of Oracle 10.2 when you create a materialized view Oracle will use the old dbms_jobs interface to automagically build an automatic refresh job. In this case I decided to use the newer dbms_scheduler interface since I wanted to tie the materialized view refresh and the Oracle Text index refresh together since the index is dependent on the materialized view. This requires that you first create the materialized view as an “ON DEMAND” job and then write your own commands to create the scheduler pieces to run the refresh.
As it turns out this is more involved than one might think. To do this involves creating a program definition for each “step”, defining a chain and the steps in that chain, and then chain rules to tell Oracle what to do when a step completes successfully or errors out. Lastly you actually define the job that will run in the scheduler. The following shows the syntax for each of these steps.
--
-- Create the scheduler programs
--
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'TABLE_MV_PGM',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN DBMS_MVIEW.REFRESH(''TABLE_MV'', ''F'', ''''); END;',
enabled => TRUE,
comments => 'Refresh materialized view');
END;
/
--
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'TABLE_MV_CTXIDX_PGM',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN ctxsys.drvdml.auto_sync_index(''TABLE_MV_CTXIDX'', 12582912, NULL, NULL, NULL, 0); END;',
enabled => TRUE,
comments => 'Refresh text index');
END;
/
--
-- Define the job chain
--
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
chain_name => 'TABLE_CHAIN',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => 'Job Chain to refresh TABLE materialized view');
END;
/
--
-- Define the chain steps
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'TABLE_CHAIN',
step_name => 'Step1',
program_name => 'TABLE_MV_PGM');
END;
/
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'TABLE_CHAIN',
step_name => 'Step2',
program_name => 'TABLE_MV_CTXIDX_PGM');
END;
/
--
-- Define the chain rules
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TABLE_CHAIN',
condition => 'TRUE',
action => 'START Step1',
rule_name => 'TABLE_Rule1',
comments => 'Start the chain');
END;
/
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TABLE_CHAIN',
condition => 'Step1 COMPLETED',
action => 'START Step2',
rule_name => 'TABLE_Rule2');
END;
/
--
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'TABLE_CHAIN',
condition => 'Step1 COMPLETED AND Step2 COMPLETED',
action => 'END',
rule_name => 'TABLE_Rule3');
END;
/
--
-- Enable the chain
--
BEGIN
DBMS_SCHEDULER.ENABLE ('TABLE_CHAIN');
END;
/
--
-- Create the job
--
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'TABLE_JOB',
job_type => 'CHAIN',
job_action => 'TABLE_CHAIN',
repeat_interval => 'freq=daily;byhour=23;byminute=0;bysecond=0',
enabled => TRUE);
END;
/
It is possible to set this up in Enterprise Manager, although I had problems with the Grid Control version and found it easier to use the scripts above to create the programs, chain and job. I do however find it much easier to administer the scheduler components in Grid Control once it is set up. I have not been as impressed with the Database Control flavor of Enterprise Manager though, and I’m not sure why the two are different for administering the scheduler.
Category:
Other Topics
Comments:
No Comments »
Timestamps
December 2nd, 2007
I’ve been using the timestamp data type to capture timing information for a process and my first try at it didn’t work very well. I don’t think Oracle’s documentation is very good at helping figure them out either, so I thought I’d share the PL/SQL that I used to decipher what I needed to make use of timestamp data:
declare
l_starttim TIMESTAMP;
l_elapsedtim INTERVAL DAY TO SECOND;
p_tottime VARCHAR2(100);
l_loadtime VARCHAR2(100);
begin
l_starttim := SYSTIMESTAMP;
dbms_lock.sleep(5);
IF l_elapsedtim IS NULL THEN
l_elapsedtim := SYSTIMESTAMP-l_starttim;
ELSE
l_elapsedtim := l_elapsedtim + (SYSTIMESTAMP-l_starttim);
END IF;
--
dbms_output.put_line(to_char(l_starttim));
dbms_output.put_line(to_char(l_elapsedtim));
--
l_starttim := SYSTIMESTAMP;
dbms_lock.sleep(5);
IF l_elapsedtim IS NULL THEN
l_elapsedtim := SYSTIMESTAMP-l_starttim;
ELSE
l_elapsedtim := l_elapsedtim + (SYSTIMESTAMP-l_starttim);
END IF;
--
dbms_output.put_line(to_char(l_starttim));
dbms_output.put_line(to_char(l_elapsedtim));
--
p_tottime := TO_CHAR(l_elapsedtim);
dbms_output.put_line('p_tottime: '||p_tottime);
DBMS_OUTPUT.PUT_LINE('Hour: '||extract(HOUR FROM l_elapsedtim));
DBMS_OUTPUT.PUT_LINE('Minute: '||extract(MINUTE FROM l_elapsedtim));
DBMS_OUTPUT.PUT_LINE('Seconds: '||ROUND(extract(SECOND FROM l_elapsedtim),2));
--
l_loadtime := SUBSTR(TO_CHAR(extract(HOUR FROM l_elapsedtim),'00'),2)||':'||
SUBSTR(TO_CHAR(extract(MINUTE FROM l_elapsedtim),'00'),2)||
':'||SUBSTR(TO_CHAR(ROUND(extract(SECOND FROM l_elapsedtim),2),'00.99'),2);
dbms_output.put_line('l_loadtime: '||l_loadtime);
end;
/
Category:
Other Topics
Comments:
No Comments »
System Level Data – Revisited
December 2nd, 2007
I was reviewing the Open World presentations and came across Cary Millsap’s presentation titled “IOUG: Why You Can’t See Your Real Performance Problems“. Now I’ve seen Cary speak many times but I didn’t get to see this presentation at Open World. After reviewing his slides I wish I had. I think he’s really hit the nail on the head as to why you must use extended SQL trace data to identify performance problems. He has actually tied several topics that he’s used in the past together to make a very persuasive argument as to why other methods fail to find performance problems as effectively as trace data does. This made me realize that there was a very heavy emphasis at this Open World conference on using what I’ll call “database” oriented data to identify performance problems. In other words, AWR, ADDM, ASH, direct sampling of v$ views, OEM and all manner of “advisors” were touted as the way to identify your performance problem. It seems that Cary’s “discovery” of using extended SQL trace data to “profile” performance problems has fallen out of favor. Whether this has truly happened or not, it seems to me that it is rare to hear anyone other than a Hotsos employee talk about using extended SQL trace data to start out a tuning exercise. Of course AppsDBA.com has offered a free interval resource profiler for several years now, so maybe we also qualify as profiling practitioners. It just seems surprising to me that so many are still so intent on finding the “holy grail” of performance tuning when it is sitting right under their noses, if they just looked

adpcpcmp.pls

Any simple problem after the upgrade that is not resolved will have long term maintenance related issues in production.

It is very important to watch the alert log while applying Oracle Applications patch as it gives wealth of information. As a matter of practice I watch the alert log while applying a patch and noticed that Invalid's Compilation stage of adpatch performed by "adpcpcmp.pls" was taking 60 mins. The alert log watch of mine caught the following error.

-- Clip --

Wed June 5 11:55:00 2007
Waited too long for library cache load lock. More info in file /xxxx/xxxx/udump/orcl_ora_7064.trc.

-- End Clip --

The trace file shows the following object

-- Clip --

LIBRARY OBJECT HANDLE: handle=3b3a2f458
name=TESTDB.MY_TEST_TABLE@db_link_name
hash=7e342709

-- End Clip --

Thats all was needed for me to cut down the compilation time from 60 mins to 10 mins. The host referenced in SID used by the above database link was not reachable.

So, watching the alert log while applying Oracle Apps patches pays off !!.

Enjoy !!

vi for Apps DBAs

vi for Apps DBAs
I intend to start of my first Blog with "vi for DBAs". I used to receive queries ( not sql queries :) ) from fellow DBAs on how to search and replace in "vi". A collection of complex (which I think are) search and replace commands of vi that I came across are summarized below. These commands are useful for DBAs in their day-to-day Administration.
#1. Once upon a time a user sent me an excel sheet with 100 tables in it. Requesting me to grant INSSERT,UPDATE & DELETE privileges for two different users. Just imagine the complexity if you are not familiar with vi. I opened up "vi" (my favourite editor), snipped & pasted all the 100 tables into a file. Now my requirement is to generate a grant sql script with the following sql query.

SQL> select 'grant insert on' table_name 'to xyz user' from dba_tables where table_name in ('TABLE1','TABLE2',.....)
Just to high-light every table in the above sql need to be quoted using ' ' and separated by a ",". To achieve this for all the tables that are already copied into to a file I used the following command.

:1,$ s/.*/'&',/g

Explanation:

".*" - indicates any text/pattern in the line. "&" - indicates the text that is already in the line. So, if the line has TABLE1 it will be replaced by 'TABLE1', (notice the quote and comma).

#2. How to search and replace a text containing lots of "/"s.

Assume the search string is "/prod1/applmgr/prod1appl" and replace string is "/test1/applmgr/test1appl". If you use the syntax :1,$ s//prod1/applmgr/prod1appl/test1/applmgr/test1appl/g. Obviously "vi" is going to get confused and errors out. Because it will not know which "/" is part of the search string and which "/"is a delimiter that is part of the search-replace syntax.

So, use the syntax mentioned below which will make you smile.

:1,$s#/prod1/applmgr/prod1appl#/test1/applmgr/test1appl#g.
"#" is part of the search-replace syntax. In other words you can use any character as a delimiter as long as the character that you are using as delimeter is not part of the search/replace string. See, how flexible the "vi" is....

#3. How to do a copy and paste across files ?

Assume, we have two files a.txt and b.txt. Our goal is to copy the text from a.txt and paste it onto b.txt. In "vi" you can define buffers and even name them. Surprising isn't it !!. Yes you can. Where each buffer can be named after numbers 1-9 or alphabets a-i. So,follow the procedure mentioned below to copy and paste across files.

#1. First step in this process of search and replace is to define a buffer and put some content into it.
#2. vi a.txt and "a2yy (Remember #1. Buffer Name can be a-i or 1-9). So, in this case I have chosen buffer name to be "a". 2yy indicates 2 lines to be copied/yanked. #3. Now open b.txt by using :e b.txt - once b.txt is opened, go to the line you would like to have the lines pasted and type "ap. This will recall the contents of buffer "a" and the contents will be pasted.

Tip: Must be wondering how to remember the syntax ? It is easy. All you need to do is use it couple of times.

Enjoy !! & Happy vi'ing.

Protecting your "APPS" password

10 Tips for protecting your 'APPS' password - is a very nice post by Oracle Corp - Mike Shaw. Out of the 10 tips in the post by M.Shaw, #7 & #10 are my favourites.

#7. Ensure no processes are running with APPS username/password in command line - This is very important as Apps DBAs are always tempted to use sqlplus apps/apps-password at unix command prompt. Any other users who are not supposed to know the apps password but have unix access can easily find out 'APPS' password by simply firing the command 'ps -ef | grep -i apps'. So, Apps DBAs - Watch out !! :)

#10. Allow only specific IP addresses to access RDBMS via SQLNET - In large enterprises the IP traffic is controlled using firewall. It is always a good idea to allow traffic from a combination of Midlle tier IP address + DB Port #.

Over a period of time the implemented protection has to be maintained. A typical Apps DBA's work life is so busy with Patches, User Calls, Upcoming Project dead lines etc., by the time he realizes that there are so many things to do , the clock ticks 07:00PM. It is time to go home. So, taking time out to proactively go and check the protection is little not practical. This is where monitoring comes into picture.

What needs to be monitored ?

It is always said that you need to be paranoid to monitor something that needs to be protected. The following are some tips I can think of to protect the 'APPS' password not to fall in wrong hands. Even if it falls, how to catch it fast.

#1. The most obvious one is to ensure $APACHE_TOP/Apache/modplsql/wdbsvr.app & $ORACLE_HOME/reports60/server/CGIcmd.dat has 700 permission. Let a monitoring script check at regular intervals for expected permissions and send e-mail/SMS alerts.

#2. There is always one place that the 'APPS' password gets recorded (Even best of the best security guides will fail to document this place) which is .sh_history or .bash_history depending on the default shell type of applmgr unix user account. It is always a good practice to clear the history upon logout as the way to stop Oracle Apps is to use adstapll.sh apps/apps-password.

bash shell (bash)- "~/.bash_logout" - this file called by bash shell upon logout. Place "rm $HOME/.bash_history" to clear the history upon logout.

korn shell (ksh) - In korn shell I think there is no file that automatically gets called during the logout process. So, alias 'exit' to 'alias exit='rm $HOME/.sh_history; exit'.

#3. Recently I learnt that, sql sessions to 'APPS' can be monitored as well. I think this is the best way to check the 'APPS' database sessions. So, have the following script in place to check 'APPS' Database sessions for unauthorized access.

Script Courtesy : My fellow Apps DBAs.

select s.sid "SID" , s.serial# "SERIAL#", s.username dbuser, s.osuser,s.machine "MACHINE", s.terminal "TERMINAL", to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS') logged_in,s.program , s.module from v$session s where s.username = 'APPS' and s.module in ('TOAD.exe','PL/SQLDeveloper','SQL*Plus') and lower(s.osuser) not like '%osusername' and lower(s.osuser) not in ('oracle user','applmgr user');

Posted by Madhu Sudhan at 12:37 AM 2 comments Li

Background Process errors

Worflow Background Process errors with ORA-00600 internal error code, arguments: [4414]
Workflow Background Process concurrent request fails with the following error message when the request is submitted with Order Management Item type.
Symptom:
ORA-00600: internal error code, arguments: [4414], [12226], [1], [12226], [1], [], [],
ORA-01403: no data found

Workflow Background Process concurrent request is a Workflow Background Engine. Workflow Backedground Process concurrent request needs to be scheduled at regular intervals as it processes deferred activities and timed out activities.

Generally the 'Workflow Background Process' fails with the following error after upgrading to Oracle Applications 11.5.10.2 and if the database is upgraded from 8.1.7.4 to 9i.

What does "ORA-600 [4414] " followed by an "ORA-01403: no data found" mean ?

If a queue has messages enqueued for rule based subscribers prior to the upgrade, then dequeueing these messages on the upgraded database results in an "ORA-600 [4414]" followed by an "ORA-01403: no data found" error.

Reference from Metalink Note:200983.1

Solution:

sqlplus "/ as sysdba"
SQL> exec sys.dbms_prvtaqis.upgrade_rulesub_msgs;


This should solve the issue.

The package "dbms_prvtaqis.upgrade_rulesub_msgs" is not available in 8i, but available in 9i. The package "dbms_prvtaqis.upgrade_rulesub_msgs" upgrades "Rule Based Subscriber Messages" to 9i.

Enjoy !!
Package 'CSI_ITEM_INSTANCE_PVT_W' Invalid after 11.5.10.2 Upgrade
Symptom:

If the existing .pls in your environment has the following version & you have just upgraded to Oracle Apps 11.5.10.2, then read further.

$ strings -a ./patch/115/sql/csiviiws.pls | grep -i Header
REM $Header: csiviiws.pls 115.4.1159.2 2006/03/10 10:04:55 abhgupta ship $
/* $Header: csiviiws.pls 115.4.1159.2 2006/03/10 10:04:55 abhgupta ship $ */
procedure construct_inst_header_rec(p_inst_id NUMBER

SQL> alter package apps.CSI_ITEM_INSTANCE_PVT_W compile body ;
Warning: Package Body altered with compilation errors.

SQL> show error;
Errors for PACKAGE BODY APPS.CSI_ITEM_INSTANCE_PVT_W:

LINE/COL ERROR
-------- -----------------------------------------------------------------
997/5 PLS-00306: wrong number or types of arguments in call to
'GET_PARENT_SORT_ORDER'
997/5 PL/SQL: Statement ignored
2855/5 PL/SQL: Statement ignored
2855/22 PLS-00302: component 'VERSION_LABEL' must be declared
2856/5 PL/SQL: Statement ignored
2856/22 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
2998/5 PL/SQL: Statement ignored
2998/33 PLS-00302: component 'VERSION_LABEL' must be declared
2999/5 PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
2999/33 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3279/5 PL/SQL: Statement ignored
3279/22 PLS-00302: component 'VERSION_LABEL' must be declared
3280/5 PL/SQL: Statement ignored
3280/22 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3420/5 PL/SQL: Statement ignored
3420/33 PLS-00302: component 'VERSION_LABEL' must be declared
3421/5 PL/SQL: Statement ignored
3421/33 PLS-00302: component 'VERSION_LABEL_MEANING' must be declared
3568/5 PLS-00306: wrong number or types of arguments in call to
'ROSETTA_TABLE_COPY_IN_P22'
LINE/COL ERROR
-------- -----------------------------------------------------------------
3568/5 PL/SQL: Statement ignored

Solution:

Apply the patch:6264601. The patch:6264601 might make CSE_PROJ_ITEM_IN_SRV_PKG / CSE_PROJ_TRANSFER_PKG invalid as well. If at all these packages go invalid. No need to worry. Follow the Metalink Note:403993.1 to fix CSE_PROJ_ITEM_IN_SRV

Autopatch Timing Report

Autopatch Timing Report. Useful information that helps you to plan your Production Downtime.
I was all set to write a shell script that will parse the patch log file and arrive at top time consuming sqls that will help me know the time taken by the sqls for the upgrades. After constantly watching the Maintenance Pack ( 3480000 ) session for 30+ hours. The following lines of the autopatch session caught my attention.

-- Clip --

sqlplus -s APPS/***** @/xxxx/xxxx/xxxxappl/ad/11.5.0/admin/sql/adtimrpt.sql 5578 adt05578

A job timing report has been generated for the current session.
You should check the file
/xxxx/xxxx/xxxxappl/admin/XXXXXX/out/adt05578.lst

for details.

-- End Clip --

Out of curiosity I opened the file "adt05578.lst", I saw the information that I was exactly looking for.

-- Clip --

Job Timing Report for AutoPatch session 5578 14-DEC-2007 19:37

Summary page 1

Jobs that ran successfully on the first try : 96048
Jobs Failed, deferred, then run successfully : 35
Jobs Failed, restarted, then run successfully : 3
Jobs Failed and Skipped : 0

Total number of jobs : 96086


^L
Job Timing Report for AutoPatch session 5578 14-DEC-2007 19:37

Top 100 Time Consuming Jobs (out of 96086 total jobs) page 1

Elapsed
Task Time Wrk Start Time
Num Prod Job (HH:MM:SS Phase Id (Mon DD YYYY HH:MI:SS)
----- ------ ------------------ ---------- ------------ ---- -----------------------
0 inv inviusi1.sql 3:25:18 dat+10 16 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:15 dat+10 10 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:15 dat+10 9 Dec 13 2007 21:28:59
0 inv inviusi1.sql 3:25:13 dat+10 18 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:25:08 dat+10 3 Dec 13 2007 21:28:59
0 inv inviusi1.sql 3:25:08 dat+10 13 Dec 13 2007 21:29:06
0 inv inviusi1.sql 3:25:07 dat+10 8 Dec 13 2007 21:28:57
0 inv inviusi1.sql 3:24:58 dat+10 19 Dec 13 2007 21:29:17
0 inv inviusi1.sql 3:24:57 dat+10 27 Dec 13 2007 21:29:01
0 inv inviusi1.sql 3:24:57 dat+10 6 Dec 13 2007 21:29:18
0 inv inviusi1.sql 3:24:56 dat+10 12 Dec 13 2007 21:28:58

-- End Clip --


Summary section that this log shows is important and can be a source of truth for auditing purposes.

Cloning Basics

Cloning Basics
If you are trying to learn oracle apps dba skills, cloning would be first practical thing would try to learn and try.
So what exactly is cloning ?? Cloning is a process by which you can literally copy all of your Oracle Apps instance to another location and bring it up. The “copied” instance will inherit virtually everything (patches, profiles, data, code etc…) from source instance, hence it is called “cloned” instance. The reason why you would clone an instance are varied. As per official Oracle Metalink note on cloning (Note:230672.1), you would clone:
1. Creating a copy of the production system for testing updates.
2. Migrating an existing system to new hardware.
3. Creating a stage area to reduce patching downtime.
Let us briefly discuss how cloning works.
For example you have a production instance called PROD and want to create a testing instance for developers and others to play around, this instance is called TEST. In this case source instance is PROD and target instance is TEST. As you know oracle apps has three distinct tiers (see my post Apps Architecture), the desktop tier, apps tier, db tier. You would have to first clone your db tier (As Oracle DBAs you must be familier with cloning db). Once target database and listeners are up and running, copy your application tier files and change configuration files to reflect target instance (e.g. hostname, ports etc). This step of configuring your apps tier is the most difficult step until release 11i when adclone,autoconfig and rapidclone came into the picture.
I would suggest you to go through following metalink notes and get your self familiar with cloning. I will discuss in detail about cloning steps in my future posts on cloning.
References:
Note: 216664.1 – FAQ : Cloning Oracle Applications Release 11i
Note 282930 – Cloning Oracle Applications Release 11i
Note ID 230672.1 – Cloning Oracle Applications Release 11i with Rapid Clone

How to Become Oracle Apps DBA

Oracle Apps Architecture is the first step towards learning anything in oracle apps. This blog entry tries to address this topic.
When Oracle first came out with ERP package it was all character based and basically was a centralized computing architecture. In apps release 10.7, the character release of Applications primarily had the database and it’s end-user forms defined in character based forms. The database was Oracle 7.3.2, and forms version was “SQL forms 3″ (character forms). The users had to telnet to the server to be able to access the applications. After connecting to the server they had to issue the command ‘found’ to be able to invoke the form that used to connect them to the server. The client machines used to connect to the server were primarily dumb terminals. e.g. VT100 etc.
In Release 10.7 there were three options:
1. The original char based
2. Smart Client (which was basically client-server architecture)
3. NCA (Network Computing Architecture)
In a Smart Client Install of 10.7, the database was Oracle 7.3.2 and the front end constituted of Developer 1.3.1 or 1.3.2. The clients used to connect to the server with the help of the named service or TCP/IP link made in the file tnsnames.ora. The SQL*Net protocol or the Net80 protocol was used for connectivity.
In NCA oracle apps was finally available in “The Three Tier Architecture”. There were three distinct “Tiers” now and client side could be much lighter (generally a java enabled browser). This architecture has been taken into release 11 and 11i, and from what I hear and read about release 12, there will not be any major change in this architecture.
The three disctinct “Tiers” in Oracle apps 11i (11.5.10.2 is the latest as of now) are:
1. Desktop Tier
2. Application Tier
3. Database Tier
The following image taken from Oracle Applications Concepts illustrates the three tiers
Lets talk breifly about these tiers individaully:
1. Deskptop Tier: Its is nothing but a simple web browser (Internet explorer or Netscape etc) from which end-user logs into the application. Important thing to remember is that browser has to be java enabled. The componants required on the desktop tier are Forms Client Applet and Oracle Jinitiator.
a. Forms Client Applet:

Most of the Oracle Applications is based on Oracle Forms and Reports (Oracle Developer) and to display oracle forms on a client’s browser an applet is used. This forms client applet displays Oracle Applications screens and support field level validation, multiple windows and list of values (LOVs). The forms client applet is paclaged as Java Archive (JAR) files that contain all java classes for forms applet. These jar files are downoaded from Web server at the beginning of the client’s session and remains in the cache known as Jcache.

b. Oracle Jinitiator:

The Forms Client Applet must run within a Java Virtual Machine (JVM) on the client machine. For Oracle Apps the JVM is provided by Oracle Jinitiator which acts as a plug-in (Active X componant in IE) in the web browser. Please note that for Oracle apps we do not use other JVMs e.g. Microsoft JVM, Sun JVM etc..
When end-user enters the desired Oracle Applications signon URL withing the web browser, the browser will execute the Oracle Jinitiator. If Jinitiator has not been previously installed, end user will be prompted to download the necessary installation executable to the desktop. Once Jinitiator is installed, the forms client applet is started and Oracle Forms sessions is opened.

2. Application Tier : The application Tier, as the name suggest is nothing, but location of application servers. This Tier is sometimes referred to as middle tier and provided business logic and code processing. There are five servers in Applications tier:

a. HTTP server (powered by Oracle Apache)
b. Forms server (This host all you Oracle forms)
c. Concurrent Processing Server
d. Reports Server (host all the Oracle reports)
e. Admin Server

3. Database Tier : The database tier contains the Data Server and holds all the data stored and maintained by Oracle Applications system. There are basically two types of database objests: Data Objects like Oracle apps Tables, Indexes, sequences etc.. and Code Objects like Stored Procedures, Packages, functions,triggers etc..
Important thing to remember is that database tier does not directly communicates with desktop tier, but rather with servers on the middle tier which in turn provides the end-users with the information as required in interface which is more user friendly. Hence almost all the processing is handled at Applications and Database Tier (which are much bigger machines) leaving client machines free of any processing.
At this point you must be having a basic understanding of Oracle Applications Architecture. I suggest that for deeper understaning of the various componants of Oracle Applications, one should carefully read chapter 1 (Applications Architecture) of Oracle Applications Concept Manual (Oracle Applications Concept)

Backup and Recovery

As part of recovery process, our restore went fine and also were able to re-create controlfile. During recovery, it asked for Archive logs. We checked with our Unix team for required archivelogs and found out they don’t have required archive logs.
It was critical for us to recover database because of some project deadline.
Error:
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
After doing some research, I found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.
We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. I checked the alert.log file and found out we have undo tablespace corruption.
Alert log shows below error
Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340
To resolve undo corruption issue, I changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, I created new undo tablespace and dropped old corrupted undo tablespace. I changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.
It resolved our issue and database was up and running without any issue.
_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.
As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:
1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.

APPS OCP EXAM

"How do you prepare for the certification exams?" And "what advice/tips/suggestions do you have for the certification candidates?"
Even if I have answered to those questions many times, this time, I wanted to have my advice/tips/suggestions posted in my blog for the certification candidates.
01) In addition to any available book in the market for OCA/OCP preparation, I would strongly advice and suggest using Oracle Student Guides/Oracle ILT Materials for your OCA/OCP preparation.
02) Most of the theoretical and logical questions in the real exam are asked from these guides/materials concepts or chapters.
03) These student guides are more simplified and very easy to understand, and have very précised and handy information.
04) The information or concepts of Oracle Student Guides/Oracle ILT Materials have been abstracted, prepared, and developed using the bunch of Oracle Documentations.
05) For writing Oracle Certification Exams, it's very hard to prepare the whole repository of Oracle Documentations. That's why; Oracle University prepared the précised and concise Student Guides/ITL Materials.
06) After finishing thoroughly reading these guides, as many times as you can, till you get the concepts from this materials, the next step is preparing the practice exams, before you take up the real exam.
07) In my opinion and my experience in writing OCP Exams, the practice exams of Self Test Software and Transcender are highly recommended for preparing the OCA/OCP/OCE Exams.
08) This will give you the similar look and feel, and the pattern of the real exam, and helps you manage in real exam.
09) All most of all the practice questions from these test engines (Self Test Software and Transcender) are developed and derived from Oracle Student Guides/Oracle ILT Materials Concepts.
10) Never mug up the practice exam questions and answers, instead try understanding the question and the answer, if needed for any syntax or command sort of questions, do the paper work i.e write the question, syntax or command, and answers in the paper to remember and understand.
11) Concisely what I would suggest is, read the Oracle Student Guides/ILT Materials meticulously as much as you can and do more practice the questions of the test engines (Self Test Software and Transcender) for your Oracle Certification Exam Preparation.
What are the Student Guides/ILT (Instructor Led Training) Materials?
Click here to know about the Student Guides/ILT (Instructor Led Training) Materials

What are the Self Test Software and Transcended Test engines?
Self Test Software and Transcended
For any suggestions, tips, experience of Others on OCA/OCP/OCE, please take a look in the below blog URL.
ORACLE CERTIFIED PROFESSIONALS
And also search in OTN Forums for more questions and answers on Certification doubts/questions.
Oracle Certification Forum
One stop for all your Oracle Certification Program Details.
Oracle Certification Program
Oracle Certifiction Program Candidate Guide
OCP 10g Candidate Guide
Oracle Magazine Inside OCP Columns
List of Topics on OCP Exams
Oracle Certification Preparation (OCP)/Hidden Treasures
OCP Hidden Treasures!!!
How important is to an Oracle Certification?
Happy reading !! and best of luck for your Certification Exams !!
Any comments or suggestions are always welcomed.

Regards,
POORNA

Changing an Oracle Database Name in Oracle 10g

Changing an Oracle Database Name in Oracle 10g
As per the requirement for our development team, we needed to perform a fresh installation of Oracle 10g and Creation of a database on Solaris 10, and import the production data. During the database creation, we had to use the same scrip what we used for Production Database Creation. After the database creation of database for development environment we realized that the Database name is also same as Production Database. So, we had used the oracle DBNEWID utility to change the Database Name to avoid the confusion between Prod DB and Dev DB.
Below is the snapshot the task.
Make sure that you have a whole database backup.
Note: In our case, we had not taken the backup as the database was created recently and we had an export dump of a production DB.
Ensure to perform clean shut down of a database and startup in mount stage (but not open).
SQL> SHUDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:
$ nid TARGET=SYS/XXXX@unc DBNAME=uncdev SETNAME=YES
DBNEWID performs validations in the headers of the control files (not the data files) before attempting I/O to the files. If validation is successful, then DBNEWID prompts for confirmation, changes the database name in the control files, shuts down the database and exits.
DBNEWID: Release 10.2.0.1.0 - Production on Mon Mar 26 20:04:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database UNIC (DBID=3680129277)
Connected to server version 10.2.0
Control Files in atabase:
/s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl
/s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl

Change database ID and database name UNIC to UNCDEV? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3680129277 to 975738954
Changing database name from UNIC to UNCDEV Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - modified Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - modified Datafile /s/oracle10g/product/10.2.0/oradata/system01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/undotbs01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/sysaux01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/users01.dbf - dbid changed, wrote new name Datafile /s/oracle10g/product/10.2.0/oradata/temp01.dbf - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control01.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control02.ctl - dbid changed, wrote new name Control File /s/oracle10g/product/10.2.0/uncdev/oradata/control03.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to UNCDEV.
Modify parameter file and generate a new password file before restarting.
Database ID for database UNCDEV changed to 975738954.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Successfully changed database name and ID.
DBNEWID - Completed successfully.
Connect to SQL* Plus and shutdown the database
SQL> SHUDOWN IMMEDIATE;
Change DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.
SQL> STARTUP MOUNT;
SQL > ALTER SYSTEM SET DB_NAME=uncdev SCOPE=spfile;
SQL> SHUDOWN IMMEDIATE;
Create a new password file
On Windows:
C:\> orapwd file= C:\oracle\product\10.2.0\db_1\database\pwdUNCDEV.ora password=xxxxxxx entries=10
On Unix:
$ orapwd file= /s/oracle10g/product/10.2.0/uncdev/dbs/ pwdUNCDEV.ora password=xxxxxxx entries=10
We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and not the database ID, it is not necessary to use the RESETLOGS option when you open the database
SQL> STARTUP;
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string UNCDEV
Backup the whole database again
Conclusion: The above series of steps are only for changing the database name, if you wanna change Database ID, or Database ID and Database Name both, please refer the below URLs.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/dbnewid.htm
http://www.oracle-base.com/articles/9i/DBNEWID.php

STATSPACK

All about the STATSPACK (Statistics Packages) in Oracle 8i, 9i & 10g
Dear Friends,
By default Oracle provides the STATSPACK information in the form of spdoc.txt under
Unix: $ORACLE_HOME/rdbms/admin/spdoc.txt
Windows: %ORACLE_HOME%\rdbms\admin\spdoc.txt
This document talks about the following contents.
TABLE OF CONTENTS
-------------------------------
0. Introduction and Terminology
1. Enterprise Manager (EM), Automatic Workload Repository (AWR) and Statspack
2. Statspack Configuration
2.1. Database Space Requirements
2.2. Installing the Tool
2.3. Errors during Installation
3. Gathering data - taking a snapshot
3.1. Automating Statspack Statistics Gathering
3.2. Using dbms_job
4. Running the Performance reports
4.1. Running the instance report
4.2. Running the instance report when there are multiple instances
4.3. Configuring the Instance Report
4.4. Running the SQL report
4.5. Running the SQL report when there are multiple instances
4.6. Configuring the SQL report
4.7. Gathering optimizer statistics on the PERFSTAT schema
5. Configuring the amount of data captured
5.1. Snapshot Level
5.2. Snapshot SQL thresholds
5.3. Changing the default values for Snapshot Level and SQL Thresholds
5.4. Snapshot Levels - details
5.5. Specifying a Session Id
5.6. Input Parameters for the SNAP and
MODIFY_STATSPACK_PARAMETERS procedures
6. Time Units used for Performance Statistics
7. Event Timings
8. Managing and Sharing performance data
8.1. Baselining performance data
8.1.1. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Snap Ids
8.1.2. Input Parameters for the MAKE_BASELINE and CLEAR_BASELINE
procedure and function which accept Begin and End Dates
8.2. Purging/removing unnecessary data
8.2.1. Input Parameters for the PURGE procedure and function
which accept Begin Snap Id and End Snap Id
8.2.2. Input Parameters for the PURGE procedure and function
which accept Begin Date and End Date
8.2.3. Input Parameters for the PURGE procedure and function
which accept a single Purge Before Date
8.2.4. Input Parameters for the PURGE procedure and function
which accept the Number of Days of data to keep
8.2.5. Using sppurge.sql
8.3. Removing all data
8.4. Sharing data via export
9. New and Changed Features
9.1. Changes between 10.1 and 10.2
9.2. Changes between 9.2 and 10.1
9.3. Changes between 9.0 and 9.2
9.4. Changes between 8.1.7 and 9.0
9.5. Changes between 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
10.1. Compatibility Matrix
10.1.1. Using Statspack shipped with 10.1
10.1.2. Using Statspack shipped with 10.0
10.1.3. Using Statspack shipped with 9.2
10.1.4. Using Statspack shipped with 9.0
10.1.5. Using Statspack shipped with 8.1.7 on 9i releases
10.2. Upgrading an existing Statspack schema to a newer release
10.2.1. Upgrading the Statspack schema from 10.1 to 10.2
10.2.2. Upgrading the Statspack schema from 9.2 to 10.1
10.2.3. Upgrading the Statspack schema from 9.0 to 9.2
10.2.4. Upgrading the Statspack schema from 8.1.7 to 9.0
10.2.5. Upgrading the Statspack schema from 8.1.6 to 8.1.7
10.2.6. Upgrading the Statspack schema from 8.1.6 to 9.2
10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.0
10.2.8. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
11.1. Changing Instance Numbers
11.2. Cluster Specific Reports
11.3. Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
12.1. Running BSTAT/ESTAT in conjunction to Statspack
12.2. Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
15.1. Limitations
15.2. Modifications
Happy reading!!!!

UTILITY

How We Used the Oracle CSSCAN Utility and CSALTER Script to Migrate the Database Character Set?
As per the requirement from the Application Vendor, we had to change the database character set on one of the existing test databases.
CSSCAN Utility and CSALTER Script:
Database character set migration has two stages: data scanning (csscan) and data conversion (csalter.plb).
The CSSCAN Utility is used to denitrify possible database character set conversion problems and truncation of data. Data scanning identifies the amount of effort required to migrate data into the new character encoding scheme before changing the database character set. This information helps to determine the best approach for converting the database character set.

The CSALTER script is part of the Database Character Set Scanner utility. The CSALTER script is the most straightforward way to migrate a character set, but it can be used only if all of the schema data is a strict subset of the new character set. The new character set is a strict superset of the current character set if:
Each and every character in the current character set is available in the new character set.
Each and every character in the current character set has the same code point value in the new character set.
Here are the simple steps to migrate the default Database Character Set:
Database Version: Oracle 10g Database R2 (10.2.0.3)
Source Character Set: AL32UTF8
Target Character Set: WE8ISO8859P1
Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Take a full database backup:
Sometimes, incorrect data conversion can lead to data corruption, so perform a full backup of the database before attempting to migrate the data to a new character set. The database can be reconstructed later using the backup taken before this action, if something goes wrong.

Note: We have taken the cold backup of the database and also performed the full database export using Datapump utility (expdp/impdp). It’s up to you, how and what type of full backup you prefer for your database i.e. Cold/Hot Backup/RMAN. So, you should recreate the database using this backup when the conversion fails.
Start up the database:
SQL> startup;
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
Run the Database Character Set Scanner (CSSCAN) utility.
This CSSCAN Utility executable is under the $ORACLE_HOME/bin directory.
$ csscan \"sys/password@mydb as sysdba\" full=y
Character Set Scanner v2.1 : Release 10.2.0.3.0 - Production on Sat Sep 13 15:58:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Current database character set is AL32UTF8.
Enter new database character set name: > WE8ISO8859P1
Enter array fetch buffer size: 1024000 >
Enter number of scan processes to utilize(1..32): 1 > 32
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAABIAABAAAAIRAAA]
. process 2 scanning SYS.SOURCE$[AAAABIAABAAAHCJAAA]
. process 3 scanning SYS.ATTRIBUTE$[AAAAC1AABAAAAURAAA]
…………………………………………………………………………….
…………………………………………………………………………….
…………………………………………………………………………….

Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
$

For any help on CSSCAN utility i.e. keyword, prompt, and description, use the following command.
$ csscan help=y
Run the CSALTER script.
Once the Database Character Set Scanning has completed successfully, the database must be opened in restricted mode, because no normal user should allow to access the database during this task is being performed. So you can run the CSALTER script as the SYS user. The location of the CSALTER Script is "$ORACLE_HOME/rdbms/admin/csalter.plb".
Shut Down the Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Start Up the Database in Restricted Mode.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
Run the csalter.plb script
SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('y') <> 'Y') then
Checking data validility...
begin converting system objects
12 rows in table SYS.WRI$_ADV_RATIONALE are converted
1 row in table MDSYS.SDO_GEOR_XMLSCHEMA_TABLE is converted
80 rows in table SYS.METASTYLESHEET are converted
17 rows in table SYS.WRI$_DBU_HWM_METADATA are converted
79 rows in table SYS.WRI$_DBU_FEATURE_METADATA are converted
4 rows in table SYS.RULE$ are converted
78 rows in table MDSYS.SDO_STYLES_TABLE are converted
10576 rows in table SYS.WRH$_SQL_PLAN are converted
21 rows in table SYS.WRI$_DBU_FEATURE_USAGE are converted
19 rows in table SYS.WRI$_ADV_ACTIONS are converted
4 rows in table MDSYS.SDO_XML_SCHEMAS are converted
2308 rows in table MDSYS.SDO_COORD_OP_PARAM_VALS are converted
578 rows in table SYS.WRI$_ADV_OBJECTS are converted
789 rows in table SYS.WRH$_SQLTEXT are converted

PL/SQL procedure successfully completed.

Alter the database character set...
CSALTER operation completed, please restart database

PL/SQL procedure successfully completed.

0 rows deleted.

Function dropped.

Function dropped.
Procedure dropped.
Shut Down the Database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Start Up the Database
SQL> startup;
ORACLE instance started.
Total System Global Area 2097152000 bytes
Fixed Size 2074120 bytes
Variable Size 771754488 bytes
Database Buffers 1308622848 bytes
Redo Buffers 14700544 bytes
Database mounted.
Database opened.
SQL>
Make sure the changes:
SQL> SELECT * FROM nls_database_parameters;
References:
Character Set Migration
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch11charsetmig.htm

Character Set Scanner Utilities
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/ch12scanner.htm

Oracle Metalink Notes on Database Character Set Migration

Note: 225912.1 - Subject: Changing the Database Character Set - a short Overview
Note: 119164.1 - Subject: Changing Database Character Set - Valid Superset Definitions
Note: 66320.1 -Subject: Changing the Database Character Set or the Database National Character Set
Note: 123670.1 - Subject: Use Scanner Utility before Altering the Database Character Set
Note: 225938.1 - Subject: Database Character Set Health check.

Conclusion: Here I have only explained the steps I followed to convert the database character set, which is specific to our single instance database. It means that this article doesn’t consist of any other information about Database Character Set, as these details are available and discussed thoroughly in the Oracle Documentations and Oracle Metalink. If the database is a multi instance database i.e. RAC, and has other options enabled in place. I would strongly recommend to follows the Oracle Documentations and Oracle Metalink Notes. Moreover, this scenario, explained here, is specific to our requirement, and need not be the same with others. When you are not sure or in doubt, then do *NOT* do this for other character set combinations without logging a tar with Oracle Support for verification.
Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
* Start the export with following command and options.
exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log
Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.
Help on Export and Import:

Windows:
C:\> exp help=y
C:\> imp help=y

Linux/Unix.
$ exp help=y
$ imp help=y

References:

Oracle 10g :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm

Oracle 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/part1.htm#435787

Oracle 8i:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch01.htm
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76955/ch02.htm

ORA-Errors

ORA-27123: unable to attach to shared memory segment
Hello,
Of late, in one of our test unix (Sun Solaris) boxes, a database user is trying to connect to the database, but getting an error “ORA-27123: unable to attach to shared memory segment” with permission denied issue.
Initially we thought that there might be an issue with SGA memory area or may be an issue with the shared memory segments and semaphores for the instance allocated. But later we found that the permission on ORACLE_HOME directory got changed accidentally with the full permission.
Here are our findings:
$ sqlplus testusr/password@testdbSQL*Plus: Release 10.2.0.2.0 - Production on Tue Jan 27 20:53:44 2009Copyright (c) 1982, 2005, Oracle. All Rights Reserved.ERROR:ORA-01034: ORACLE not availableORA-27123: unable to attach to shared memory segmentSVR4 Error: 13: Permission denied As per Oracle Error Messages:
Error: ORA-27123: unable to attach to shared memory segmentCause: shmat() call failedAction: check permissions on segment, contact Oracle supportCheck the oracle executable file permission.
$ cd $ORACLE_HOME/bin$ ls -l oracle-rwxrwxr-x 1 oracle dba 119582976 Feb 3 2008 oracleHere the oracle file permission has -rwxrwxr-x i.e. 775, but this file must have the permission -rwsr-s- -x i.e. 6751
Change the permissions for oracle file.
$ cd $ORACLE_HOME/bin$ chmod 6751 oracle$ ls -l oracle-rwsr-s--x 1 oracle dba 119582976 Feb 3 2008 oracleAfter changing the permissions on oracle executable file, all the users are now able to connect to the database without any errors.
Note: For further information refer the Oracle Metalink Note ID: 1011995.6 Subject: COMMON ORACLE PERMISSION PROBLEMS ON UNIX.

Upgrade on sun soalris

Upgrade Oracle Database from 9.2.0.1 to 9.2.0.7 on Soalris 10 (Sun Sparc)
As per the requirement I have upgrded one of our test database from the Version Oracle 9.2.0.1 to 9.2.0.7 on Solaris 10 (Sun Sparc)
Details:
Operating Version Details : SunOS libtest 5.10 Generic_118833-23 sun4u sparc SUNW,Sun-Fire
Oracle Version Details (Current) : Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
Oracle Instance Name : testdb
Patchset (to be applied on) : p4163445_9207_solaris64.zip (Downloaded from www.metalink.oracle.com)
Requirements for installing the patchset # 4163445
Oracle Universal Installer Version Requirements
The Oracle Universal Installer release must be 10.1.0.4 (This includes in the patchset).
System Requirements
* Operating System is Solaris 10 (But it can be Solaris 8 or 9)
* Oracle9i release 2 (9.2.0.1.0) or later

Preinstallation Tasks

Upgrade and SYS Schema

During an upgrade from release 9.2.0.1 or later, the catpatch.sql script can take a long time if there are statistics for the SYS schema. Delete the statistics on all of the objects in the SYS schema, and then recollect the statistics after normal database open, if necessary.

To drop and re-create the statistics.
SQL> EXECUTE DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

Download and Extract the Installation Software

To download and extract the patch set installation software:
Download and extract the p4163445_9207_solaris64.zip patch set installation archive to a directory " /oracle9i/9207_patch"

To unzip and extract the installation files:
$ unzip p4163445_9207_solaris64.zip

Set the ORACLE_HOME and ORACLE_SID Environment Variables

To set the ORACLE_HOME and ORACLE_SID environment variables:
Bourne, Bash, or Korn shell:

$ ORACLE_HOME=/oracle9i
$ ORACLE_SID=testdb
$ export ORACLE_HOME ORACLE_SID
C shell:
% setenv ORACLE_HOME /oracle9i
% setenv ORACLE_SID testdb
The oracle_home is the Oracle home directory where the Oracle9i installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.
Shut Down Oracle Databases
Shut down any existing Oracle database instances with normal or immediate priority.
SQL> SHUTDOWN IMMEDIATE;

Stop All Processes

Stop all listener and other processes running in the Oracle home directory where you want to install the patch set.
LSNRCTL> stop
Back Up the System
Oracle recommends that you create a backup of the Oracle9i installation before you install the patch set.
We took the backup of Oracle home and all the database files.
Installation Tasks
Installing the Patch Set Interactively
To install the patch set interactively:
* Log in as the Oracle software owner (typically oracle).
su - oracle
* If you are not installing the software on the local system, enter the following command to direct X applications to display on the local system:
Bourne, Bash, or Korn shell:
$ DISPLAY=libtest:0.0 ; export DISPLAY
C shell:
% setenv DISPLAY libtest:0.0
Here, local_host is the host name or IP address of the system that you want to use to display the Installer (your workstation or PC).
* Enter following commands to start the Installer where patchset_directory is the directory where you unzipped the patch set software:
$ cd /oracle9i/9207_patch/Disk1
$ ./runInstaller
* On the Welcome screen, click Next.
* On the Specify File Locations screen, click Browse next to the Path field in the Source section.
* Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. i,e
/oracle9i/9207_patch/Disk1/stage/products.xml
* In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
Name: Home1
Path: /oracle9i.
* On the Summary screen, click Install.
* This screen lists all of the patches available for installation.
* When prompted, run the $ORACLE_HOME/root.sh script as the root user.
* On the End of Installation screen, click Exit, then click Yes to exit from the Installer.
Postinstallation Tasks
Required Postinstallation Tasks
Check SYSTEM Tablespace Size
If JServer is part of the installation ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
SQL> alter database datafile '/u02/oradata/testdb/SYSTEM01.DBF' resize 500m;
Database altered.
Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
* Start the database:
SQL> STARTUP;
* If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;

* This command displays the name and location of the server parameter file or the initialization parameter file.
Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
* If the system is using a server parameter file:
If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
* If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
* If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
* Shut down the database:
SQL> SHUTDOWN
Upgrade the Database
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
* Log in as the Oracle software owner (typically oracle).
su - oracle

* Start the Oracle Net listener as follows:
$ lsnrctl start

* For single-instance installations, use SQL*Plus to log in to the database as the SYS user with SYSDBA privileges:
$ sqlplus /nolog
SQL> CONNECT / AS SYSDBA

* Enter the following SQL*Plus commands:
SQL> STARTUP MIGRATE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
* Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
* This list provides the version and status of each SERVER component in the database.
* If necessary, rerun the catpatch.sql script after correcting any problems.
* Restart the database:
SQL> SHUTDOWN
SQL> STARTUP
* Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.
SQL> @?/rdbms/admin/utlrp.sql
Conclusion: It is importnat note that We have followed the above process as per our requirement and perticular limites to our test instance. But this is not the complete upgrade procedure, please refer the read me document of README for 4163445 " Oracle9i Patch Set Notes Release 2 (9.2.0.7) Patch Set 6 for Solaris Operating System (SPARC 64-bit) " .