Can't allocate log, archival required - alert log file in Oracle

-- Troubleshoot "Can't allocate log, archival required"
-- Found Oracle 11gR2 RAC environment
-- Found from alert log

< ORACLE Instance PRODDB2 - Can not allocate log, archival required
< Thread 2 cannot allocate new log, sequence 12265
< All online logs needed archiving

-- Cause:
It seems that all the online redo logs are full and you need to empty them. This can happeen due to two reasons
1) archivelog destination is full
2)archiving is disabled although the database is in archive log mode

-- Diagnosis: - a sample result

SQL> show parameter log_archive_max_process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4


SQL> show parameter LOG_ARCHIVE_START;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE


-- solution:

If redo log size is too small to handle current redo log switch frequency then bigger redo log size will work in this case. Increase redo log size to 500M.

OTHERWISE

If automatic archival is disabled, run the following command

sql> ALTER SYSTEM ARCHIVE LOG ALL;


it is better if you set as well LOG_ARCHIVE_START parameter into init.ora

LOG_ARCHIVE_START=TRUE


Consider increasing either or both of the initialisation parameters LOG_ARCHIVE_BUFFER_SIZE and LOG_ARCHIVE_BUFFERS.

Sure it will help.

ORA-00600: internal error code, arguments: [15851]

-- ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []
-- Issue faced in Oracle 11gR2

sql> EXEC dbms_stats.gather_schema_stats('HR', cascade=>TRUE);

ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []

*Cause: 

> This is the generic internal error number for Oracle program exceptions.
> This indicates that a process has encountered an exceptional condition.
> Due to some tables stats failure, the entire schema gather failed.

*Action: 

> Report as a bug - the first argument is the internal error number.
> Create dynamic script to gather all tables for the schema and find what tables stats not gathered and failed with 'ORA-00600'.                                                                                                   

-- Dynamic script: ( Asume schema name/ owner of tables is 'HR'):
SQL > select ' exec dbms_stats.gather_table_stats('''||d.owner||''','''||d.table_name||''','
||'cascade => true'||');' from all_tables d where owner='HR' order by table_name;

-- Sample example to show which table failed with error.

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEE');

BEGIN dbms_stats.gather_table_stats('HR','EMPLOYEE'); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [],[], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

*Solution:
Try-1:

SQL> EXEC dbms_stats.gather_TABLE_stats(OWNNAME=>'HR',TABNAME=>'EMPLOYEE');

-- if above/ same error came then,
Try-2:
SQLl> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE', estimate_percent => 5,cascade=>TRUE, degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- if above/ same error came then,
Try-3:

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE',
estimate_percent => 50,cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

Note: Increase estimate % with some larger value. It will work

-- if above/ same error came then,
Try-4:
SQL> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE', estimate_percent => 10, cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

Click here to view solution for Other ORA-00600

Sure it will work.
Don't forget to post comments.

when standby DB not in synch due to ORA-00333/00334/27069 ( Incomplete read from log member )- solution

-- Troubleshoot standby  ORA-00333/00334/27069 issue

-- When archive logs are not applying ( scenario based)
-- Applied in Oracle 11gR2 ( applicable to Oracle 10g onwards)
-- observed standby DB some times out of synch due to following error

Error Message: ( from alert log)

Errors in file /u03/app/oracle/diag/rdbms/PROD/PROD/trace/PROD_pr00_10867.trc:
ORA-00333: redo log read error block 30720 count 2048
ORA-00334: archived log: '/u03/ARCBKP/1_918_829075801.dbf'
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 30720
Additional information: 2048
Additional information: 32320
Incomplete read from log member '/u03/ARCBKP/1_918_829075801.dbf'. Trying next member.
Errors with log /u03/ARCBKP/1_918_829075801.dbf
MRP0: Detected read corruption! Retry recovery once log is re-fetched...


Solution:

Don't disturb recovery mode. If down, then bring the DB into recovery managed mode again. Register the archive log(s) which is/are not recovered.

SQL> alter database register logfile '/u03/ARCBKP/1_918_829075801.dbf';

Sure recover will be started.
Sure archive logs will start applying if cataloged already.

Troubleshoot "Can not allocate log, archival required" in alert log

-- Troubleshoot "Can not allocate log, archival required"
-- In Oracle 9i/10g/11g
-- Found from alert log

< ORACLE Instance PROD2 - Can not allocate log, archival required
< Thread 2 cannot allocate new log, sequence 12265
< All online logs needed archiving

-- Cause:
It seems that all the online redo logs are full and you need to empty them. This can happeen due to two reasons
1) archivelog destination is full
2)archiving is disabled although the database is in archive log mode

-- Diagnosis:


SQL> show parameter log_archive_max_process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4


SQL> show parameter LOG_ARCHIVE_START;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE


-- solution:

If redo log size is too small to handle current redo log switch frequency then bigger redo log size will work in this case. Increase redo log size to 500M.

OTHERWISE

If automatic archival is disabled, run the following command

ALTER SYSTEM ARCHIVE LOG ALL;

it is better if you set as well LOG_ARCHIVE_START parameter into init.ora

LOG_ARCHIVE_START=TRUE

Consider increasing either or both of the initialisation parameters LOG_ARCHIVE_BUFFER_SIZE and LOG_ARCHIVE_BUFFERS.

Troubleshoot 'ora.ctssd' RAC services 'OBSERVER' status in Oracle 11gR2

-- Troubleshoot 'ora.ctssd' RAC services 'OBSERVER' status
-- In Oracle 11gR2
-- crsctl status resource -t -init in 11.2.0.2 grid infrastructure

-- Some times it may lead scan listener bounce

-- Status when issue found

$ crsctl status res -t -init
ora.ctssd
1 ONLINE ONLINE prddb1 OBSERVER

Node:2
$ crsctl status res -t -init

ora.ctssd
1 ONLINE ONLINE prddb2 OBSERVER

-- Issue find and troubleshoot

-- Collect all resource status and re-look

$ crsctl stat res -p

Rename '/etc/ntp.conf' file or moved to any other location to change "ora.ctssd" status to "online" in both nodes.
After change, now check

$ crsctl status res -t -init
ora.ctssd
      1        ONLINE  ONLINE       prddb1                    ACTIVE:0

Node:2
$ crsctl status res -t -init
ora.ctssd
      1        ONLINE  ONLINE       prddb2                    ACTIVE:0

-- Why ntp.conf not required some scenarios:

NTP (Network Time Protocol) is automatically configured and enabled to ensure time synchronization. Oracle VM automatically configures Oracle VM Manager as the NTP source for all Oracle VM Servers under its control.


- Configuring the NTP Service
To provide time services to the Oracle VM Servers, NTP must first be installed and configured on the Oracle VM Manager host server. Make sure that your Oracle VM Manager host is either registered with the Unbreakable Linux Network (ULN) or configured to use Oracle's public YUM service.

for details follow from Oracle document
link : http://docs.oracle.com/cd/E26996_01/E18548/html/manager_ntpconfig.html
or Click here to open

Restore Oracle database RAC to RAC (ASM) with creating fresh RAC services

-- Implemented IBM-AIX 6.1 flatform
-- Upgrade from 11.2.0.2 to 11.2.0.3
-- Oracle Clusterware 11.2.0.4
-- Source Oracle Software verison = 11.2.0.2
-- Target Oracle Sofware version = 11.2.0.3

About Document:
This document can be used to migrate data using RMAN from one cluster database to another cluster database with lower version to higher version of Oracle 11gR2. It is helpful to create all required clusterware services for newly restored database.

Basic Checks:
1) OS and Oracle version
2) Check transportable_platform ( endian format)
SQL> select * from v$transportable_platform

3) all user password like root, oracle and grid (if)

# export ORACLE_HOME=/oracle/app/11.2.0/grid
# export PATH=$ORACLE_HOME/bin:$PATH

=========================Created pfile in new server=======================

========================// initPROD1.ora //==============================

*._keep_remote_column_size=TRUE
*.audit_file_dest='/oracle/app/oracle/admin/PROD/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA/PROD/controlfile/current.272.759561861','+FRA/PROD/controlfile/current.388.759561863'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PROD'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=268435456000
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
PROD2.instance_number=2
PROD1.instance_number=1
*.log_archive_start=TRUE
*.memory_target=6871318528
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.open_cursors=300
*.processes=500
*.remote_listener='PRDDB-SCAN-IP:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sec_case_sensitive_logon=FALSE
*.service_names='PROD'
*.session_cached_cursors=100
*.sessions=555
PROD2.thread=2
PROD1.thread=1
PROD1.undo_tablespace='UNDOTBS1'
PROD2.undo_tablespace='UNDOTBS2'

===========================// initPROD1.ora //========================
Steps:
=================

1) Take RMAN backup from source side and transfer to target side. (make sure controlfile autobackup is on)
2) create pfile in source side and move to target side.
3) Create required directory structures in ASM for C.R.D. files.
4) Change required parameters in pfile( audit, controlfiles, FRA etc,)
5) Be sure of following changes:
*.cluster_database=false #( default true)
*.audit_file_dest='/oracle/app/oracle/admin/PROD/adump'
*.control_files='+DATA/PROD/controlfile/current.272.759561861','+FRA/PROD/controlfile/current.388.759561863'
*.remote_listener='test-cluster-scan:1521'
*.diagnostic_dest='/oracle/app/oracle'

etc.

6) Stop cluster services in node-2 ...(stop crs in node 2)
7) start with nomount
SQL> create spfile from pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora';
File created.
SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
.....
SQL> quit
bash-4.2$ rman target /

RMAN> set DBID=52099846; (Collect from source database)

executing command: SET DBID
RMAN> restore controlfile from '/oradb/PROD_bkp/c-62083845-20131129-01';
RMAN> startup mount;
RMAN> catalog start with '/oradb/PROD_bkp'; ( Path where all backup pieces are kept to restore)

-- run your switch datafile script.( Collect from source database)

RMAN> RUN
{
allocate channel ch1 type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA/PROD/datafile/system.278.769561767';
SET NEWNAME FOR DATAFILE 2 TO '+DATA/PROD/datafile/sysaux.279.769561767';

....
}
RMAN>

8)  Recover database
RMAN> catalog start with '/oradb/PROD_bkp/archivelogs';
 ( Path where all archive logs are kept to recover)

SQL> recover database ;

9) Open with upgrade / follow the upgrade process
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter database open resetlogs upgrade;
( if not work), then
SQL> shut immediate;
SQL> startup upgrade;

-- download following file from oracle.com and runn immediate after startup upgrade.
-- utlu112i_3.sql

SQL> @utlu112i_3.sql
SQL> @catupgrd.sql
SQL> shut immediate;
SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
...
Database mounted.
Database opened.

SQL> @utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(1) from dba_objects where status='INVALID';

Note: Collect from source and target databases. Target database Valid object counts should be equal or more or invalid object counts of target database should be same or lower.

10) create common spfile:

do the changes in pfile(add) :

SPFILE='+DATA/PROD/spfilePROD.ora'
*.cluster_database=true

11) SQL> shut immediate;
12) Create parameter file in ASM
SQL> create spfile='+DATA/PROD/spfilePROD.ora' from pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora';

File created.

create pfile in node 2 in $ORACLE_HOME/dbs location and keep only below parameter
$cd $ORACLE_HOME/dbs
$ cat initPROD2.ora
SPFILE='+DATA/PROD/spfilePROD.ora'

13) Try to startup
SQL> startup;
....
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile;
NAME           TYPE        VALUE
------------- ----------- -------------------
spfile         string      +DATA/PROD/spfilePROD.ora

14) start crs services in Node-2, goto root and start
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# export ORACLE_HOME=/oracle/app/11.2.0/grid
# export PATH=$ORACLE_HOME/bin:$PATH

# ./crsctl start crs
# crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4534: Cannot communicate with Event Manager

( wait it will take time to start all services as 'online')

# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

15) add database and instance to cluster

$ srvctl add database -d PROD -o /oracle/app/oracle/product/11.2.0/dbhome_1 ( oracle home)
$ srvctl config database -d PROD
$ srvctl add instance -d PROD -i PROD1 -n testclient1  
( testclient1 is the hostname of node-1)
$ srvctl add instance -d PROD -i PROD2 -n testclient2
$ srvctl add service -d PROD -s node1.db -r PROD1   
( Use hostname in case of node1)
$ srvctl add service -d PROD -s node2.db -r PROD2
$ srvctl start database -d PROD

Now confirm:

$ srvctl status database -d PROD
Instance PROD1 is running on node testclient1
Instance PROD2 is running on node testclient2

16) Do the following activities
a)create password file in both node
go dbs location
$ orapwd force=y file=orapwPROD1 password=sys$
$ orapwd force=y file=orapwPROD2 password=sys$

b) add in host file of client and collect scan IP(s)
$ cat /etc/hosts
test-cluster-scan  ( scan IP hostname )

c) Gather schema stats & Dictionary stats
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => SC.USERNAME);
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS:

17) Final verification of all RAC services

# crsctl stat res -t
--------------------------------------------------------------------------------
NAME      TARGET  STATE        SERVER      STATE_DETAILS     
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.DATA.dg
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.FRA.dg
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.LISTENER.lsnr
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.asm
               ONLINE  ONLINE       testclient1              Started           
               ONLINE  ONLINE       testclient2              Started           
ora.gsd
               OFFLINE OFFLINE      testclient1                                
               OFFLINE OFFLINE      testclient2                                
ora.net1.network
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.ons
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.registry.acfs
               ONLINE  UNKNOWN      testclient1                                
               ONLINE  ONLINE       testclient2                                
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       testclient1                                
ora.PROD.db
      1        ONLINE  ONLINE       testclient1              Open              
      2        ONLINE  ONLINE       testclient2              Open              
ora.PROD.node1.db.svc
      1        ONLINE  ONLINE       testclient1                                
ora.PROD.node2.db.svc
      1        ONLINE  ONLINE       testclient2                                
ora.cvu
      1        ONLINE  ONLINE       testclient1                                
ora.oc4j
      1        ONLINE  ONLINE       testclient1                                
ora.scan1.vip
      1        ONLINE  ONLINE       testclient1                                
ora.testclient1.vip
      1        ONLINE  ONLINE       testclient1                                
ora.testclient2.vip
      1        ONLINE  ONLINE       testclient2     

-- One sample example when I upgraded from 11.2.0.3 to 11.2.0.4
-- Ugrading catalog and dba registry

SQL> startup upgrade;
ORACLE instance started.
.....
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD     READ WRITE

SQL>

-- Now see the dba registry values of all database components:

set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;

select COMP_NAME, VERSION,status from dba_registry;

COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.3.0 VALID
Oracle XML Database                 11.2.0.3.0 VALID
Oracle Text                         11.2.0.3.0 VALID
Oracle Expression Filter            11.2.0.3.0 VALID
Oracle Rules Manager                11.2.0.3.0 VALID
Oracle Workspace Manager            11.2.0.3.0 VALID
Oracle Database Catalog Views       11.2.0.3.0 VALID
Oracle Database Packages and Types  11.2.0.3.0 VALID
JServer JAVA Virtual Machine        11.2.0.3.0 VALID
Oracle XDK                          11.2.0.3.0 VALID
Oracle Database Java Packages       11.2.0.3.0 VALID
Oracle Real Application Clusters    11.2.0.3.0 VALID

12 rows selected.

Note: Here database components are not upgraded.

-- Upgrade the catalog

SQL>@?/rdbms/admin/catupgrd.sql

Note: After catalog upgrade database will be down automatically. Again do startup upgrade.

SQL>startup upgrade;

-- Now see the version of database components:

SQL> startup upgrade;
ORACLE instance started.
......
Database mounted.
Database opened.
SQL>

SQL> set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;

select COMP_NAME, VERSION,status from dba_registry;

COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.4.0 VALID
Oracle XML Database                 11.2.0.4.0 VALID
Oracle Text                         11.2.0.4.0 VALID
Oracle Expression Filter            11.2.0.4.0 VALID
Oracle Rules Manager                11.2.0.4.0 VALID
Oracle Workspace Manager            11.2.0.4.0 VALID
Oracle Database Catalog Views       11.2.0.4.0 VALID
Oracle Database Packages and Types  11.2.0.4.0 VALID
JServer JAVA Virtual Machine        11.2.0.4.0 VALID
Oracle XDK                          11.2.0.4.0 VALID
Oracle Database Java Packages       11.2.0.4.0 VALID

COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Real Application Clusters    11.2.0.4.0 VALID

12 rows selected.

A common issue:

After restore and recover database in two node RAC, node2 instance failed to start with below error message:

Example:

$ srvctl start database -d PROD
PRCR-1079 : Failed to start resource ora.prod.db
CRS-5017: The resource action "ora.prod.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00123: invalid character 0 found in the input file
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/racdb5/agent/crsd/oraagent_oracle//oraagent_oracle.log".

CRS-2674: Start of 'ora.ehishyd.db' on 'racdb5' failed
CRS-2632: There are no more servers to try to place resource 'ora.ehishyd.db' on that would satisfy its placement policy

$ srvctl status database -d PROD
Instance PROD2 is not running on node racdb5
Instance PROD1 is running on node racdb6
$

I verified and found spfile is in ASM and it is common to all nodes. But local parameter file not set properly in node 2. I changed like below and started node2 instance and started also.

Change :

$vi initPROD2.ora

SPFILE='+DATA/PROD/spfilePROD.ora' 

     

Hope this document will help more
Hope this document will help. Don't forget to post comments....

Oracle database PSU patch details - using SQL

-- To find applied PSU patch sets in Oracle
-- Using SQL and OS commands

CPU, PSU, SPU - Oracle Critical Patch Update Terminology Update

It all started in January 2005 with Critical Patch Updates (CPU).  Then Patch Set Updates (PSU) were added as cumulative patches that included priority fixes as well as security fixes.  As of the October 2012 Critical Patch Update, Oracle has changed the terminology to better differentiate between patch types.  This terminology will be used for the Oracle Database, Enterprise Manager, Fusion Middleware, and WebLogic.

Critical Patch Update (CPU) now refers to the overall release of security fixes each quarter rather than the cumulative database security patch for the quarter.  Think of the CPU as the overarching quarterly release and not as a single patch.

Patch Set Updates (PSU) are the same cumulative patches that include both the security fixes and priority fixes.  The key with PSUs is they are minor version upgrades (e.g., 11.2.0.1.1 to 11.2.0.1.2).  Once a PSU is applied, only PSUs can be applied in future quarters until the database is upgraded to a new base version.

Security Patch Update (SPU) terminology is introduced in the October 2012 Critical Patch Update as the term for the quarterly security patch.  SPU patches are the same as previous CPU patches, just a new name.  For the database, SPUs can not be applied once PSUs have been applied until the database is upgraded to a new base version.

Bundle Patches are the quarterly patches for Windows and Exadata which include both the quarterly security patches as well as recommended fixes.

Note: Always visit oracle sites / news letters to get update with all Oracle patches

Method -1 : Using SQL query

$ sqlplus / as sysdba
sql>
select substr(action_time, 1, 30) action_time,
       substr(id, 1, 10) id,
       substr(action, 1, 10) action,
       substr(version, 1, 8) version,
       substr(BUNDLE_SERIES, 1, 6) bundle,
       substr(comments, 1, 20) comments
  from registry$history;

Sample output:
ACTION_TIME            ID    ACTION    VERSION    BUNDLE    COMMENT
-------------------            --    -----------    -------------    ------------    ---------------
21-OCT-11 04.48.04.498723 AM    0    APPLY    11.2.0.3        PSU    Patchset 11.2.0.2.0
19-AUG-13 06.07.26.023981 PM    7    APPLY    11.2.0.3        PSU    PSU 11.2.0.3.7

Method -2 : By OS command


$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'

Sample output:

bash-3.2$ ./opatch lsinventory -bugs_fixed | egrep 'PSU|PATCH SET UPDATE'
16619892   16619892  Fri Aug 16 22:11:20 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPU
16056266   16056266  Fri Aug 16 22:10:57 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPU
16368108   16056266  Fri Aug 16 22:10:57 GMT+05:30 2013RUNNING OWMV1120.PLB IN PSU 112036 GIVING ORA-0095
14727310   14727310  Fri Aug 16 22:10:43 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPU
14275605   14275605  Fri Aug 16 22:09:58 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPU
13923374   13923374  Fri Aug 16 22:09:49 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES
13696216   13696216  Fri Aug 16 22:09:20 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES
13343438   13343438  Fri Aug 16 22:08:15 GMT+05:30 2013DATABASE PATCH SET UPDATE 11.2.0.3.1
12925041   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013112024GIPSU OLOGGERD CORE DUMP AT 'CRFLOGDB.C
13079948   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013CVU FILES NOT BEING PICKED UP FOR 11.2.0.2.4GIPSU
13348650   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.1
                                                       (INCLUDES DB PSU 11.2.0.3.1)
13396284   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3 GI PSU 1 HAS-CRS TRACKING BUG
13531373   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.1GIPSU CVU CHECK FAIL FOR NETWORK
13540563   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013MERGE REQUEST ON TOP OF 11.2.0.3.1PSU FOR BUGS
13569812   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.1GIPSU CVU FAIL TO DETERMINE IF ORACLE
                                                       NETWORK HICCUP PSU4 HAS BEEN APP
13696251   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.2
                                                       (INCLUDES DB PSU 11.2.0.3.2)
13919095   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.3
                                                       (INCLUDES DB PSU 11.2.0.3.3)
14001941   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013DOWNGRADE ISSUE FROM 11.2.0.3.1 TO 11.2.0.2 PSU4
14271305   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.3GIPSU SIHA ORAAGENT.BIN ALWAYS CONSUME
14275572   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.4
                                                       (INCLUDES DB PSU 11.2.0.3.4)
15876003   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.5
16315641   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.6
16578706   16619898  Fri Aug 16 22:04:48 GMT+05:30 201311.2.0.3.6GIPSU ROLLBACK WITH QOS FAILED IF
16619898   16619898  Fri Aug 16 22:04:48 GMT+05:30 2013GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.7

In simple :
$ cd $ORACLE_HOME/OPatch ( for rdbms home)
$ cd $GRID_HOME/OPatch ( for grid home in RAC)
$ ./opatch lsinventory
OR
$ ./opatch lsinventory -details

>> It will display all applied patch details  for rdbms home / grid home
Note : Always touch with Oracle support to apply latest PSU / CPU patch.

SQL trace 10046 -- analysis of tkprof outputs

~ SQL trace with event10046 in Oracle database
~ tkprof and analysis
~ Different levels of tracing
~ Examples

We will discuss following topics:

1) What is sql tracing?
2) Why 10046?
3) Why level 8 or 12?
4) How to use?
5) Where will be the xxx.trc file ?
6) How to user tkprof xxx.trc file?
7) How to analyze tkprof output file?
8) Any performance issue during sql trace?

1) What is sql tracing?
Ans:

• The SQL Trace facility provides performance information on individual SQL statements in bottlenect situations.
• It generates the following statistics for each statement
• Parse, execute, and fetch counts
• CPU and elapsed times
• Physical reads and logical reads
• Number of rows processed
• Username under which each parse occurred
• Each commit and rollback etc.
• Can enable the SQL Trace facility for a session or for an instance. When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files.
• The additional overhead of running the SQL Trace facility against an application with performance problems is normally insignificant compared with the inherent overhead caused by the application's inefficiency.

Note:
Try to enable SQL Trace only for statistics collection and on specific sessions. If you must enable the facility on an entire production environment, then you can minimize performance impact with the following:
    Maintain at least 25% idle CPU capacity.
    Maintain adequate disk space for the USER_DUMP_DEST location.
    Stripe disk space over sufficient disks.

2) Why 10046?
Ans:

• This is oracle tracing event. ( see details : Click here)
• The quickest way to capture the SQL being processed by a session is to switch on SQL trace
• Set the 10046 event for a representative period of time. The resulting trace files can be read in their raw state or translated using the tkprof utility.

3) Why level 8 ?
Ans:
Various kinds of levels are there. Here are use of various events during tracing.
    1 - Standard trace output including parsing, executes and fetches plus more
    2 - same as Level 1
    4 - Level 1 + Bind Variables
    8 - Level 1 + Waits
    12 - Level 1 + Bind Variables & Waits

4) How to use?
-- When reoccur the issue and trace the same. Basically this is not useful when your system hung or slow. For that you can use "hang analyzer" or "oradebug". Next to this "oradebug" examples given.
Ans:
SQL> ALTER SESSION SET tracefile_identifier = 'gou_plan1';
SQL>alter session set timed_statistics = true;
SQL>alter session set statistics_level=all;
SQL>alter session set max_dump_file_size = unlimited;
SQL>alter session set events '10046 trace name context forever,level 12';
SQL><run the problem query> / wait for 3 to 5 minutes (in prod) / do some transaction/ problematic query (in SIT/DEV/UAT)
SQL>select * from dual;
SQL>alter session set events '10046 trace name context off';

-- With sid and serial#
You can SID and SERIAL has of the session from v$ session or v$session_wait or AWR/ ASH reports as well. Using sid and searl# find out OS pid and you can trace it via "oradebug".

SQL> CONN sys/password AS SYSDBA;   -- User must have SYSDBA.
SQL> ORADEBUG SETMYPID;           -- Debug current session.
SQL> ORADEBUG SETOSPID 1234; -- Debug session with the specified OS process.
SQL> ORADEBUG SETORAPID 123456; -- Debug session with the specified Oracle process ID.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME;     -- Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

5) Where will be the xxx.trc file ?

Ans:
xxx.trc file will be generated in user_dump_dest location of the DB server.
Example:
SQL> show parameter user_dump_dest;

NAME              TYPE        VALUE------------ ----------- ------------------------------user_dump_dest    string       /u01/app/oracle/diag/rdbms/prddb/PRDDB1/trace

6) How to user 'tkprof' xxx.trc file?
Ans:

• TKPROF accepts as input a trace file produced by the SQL Trace facility, and it produces a formatted output file.
• TKPROF can also be used to generate execution plans.

After the SQL Trace facility has generated a number of trace files, you can:

• Run TKPROF on each individual trace file, producing a number of formatted output files, one for each session.
• Concatenate the trace files, and then run TKPROF on the result to produce a formatted output file for the entire instance.
• TKPROF does not report COMMITs and ROLLBACKs that are recorded in the trace file.

Syntax:
$ TKPROF <trace_file_name> <output_file_name>
[explain=<user/password> [table=<schema.table_name>]]
[print=<integer>]            -- list only the first nth SQL statements
[aggregate=<yes|no>
[insert=<file_name>]     -- list SQL statements and data inside INSERT statements
[sys=<no>]                   -- TKPROF does not list SQL statements run as user SYS
[record=<file_name>]     -- record non-recursive statements in the trace file
[waits=<yes|no>]         -- record summary of for wait events in the trace file
[sort=<sort_options_list>]     -- zero or more of the listed sort options

Sort options:
prsela : elapsed time parsing
exeela: elapsed time executing
fchela: elapsed time fetching
fchcpu:cpu time spent fetching
etc.

Example: 
$ tkprof prddb_ora_33751070_gou_plan1.trc gou_plan1.txt SYS=NO SORT =prsela,exeela,fchela

see more on follow link Click here

Sample Examples:

$ pwd
/u01/app/oracle/diag/rdbms/prddb/PRDDB1/trace
bash-4.2$ sqlplus / as sysdba
SQL> ALTER SESSION SET tracefile_identifier = 'gou_plan1';
Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.

$ tkprof    PRDDB1_ora_14680516_gou_plan1.trc gou_plan.txt
TKPROF: Release 11.2.0.3.0 - Development on Mon Nov 18 18:25:50 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Output:
$ cat gou_plan.txt
TKPROF: Release 11.2.0.3.0 - Development on Mon Nov 18 18:25:50 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Trace file: PRDDB1_ora_14680516_gou_plan1.trc
Sort options: default
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
*******************************************************************************
SQL ID: 0kjg1c2g4gdcr Plan Hash: 0
ALTER SESSION SET EVENTS '10046 trace name context off'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1      118.64        118.64

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: PRDDB1_ora_14680516_gou_plan1.trc
Trace file compatibility: 11.1.0.7
Sort options: default
       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
      34  lines in trace file.
       0  elapsed seconds in trace file.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Example:2

ALTER SESSION SET tracefile_identifier = 'plan10046_lev12';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
--wait some time
ALTER SESSION SET EVENTS '10046 trace name context off';
----- go to user_dump_dest and find the trace file

$ tkprof PRDDB2_ora_40829114_plan10046_lev12.trc plan10046_lev12.txt sort = exeela, prsela,fchela
TKPROF: Release 11.2.0.3.0 - Development on Tue Nov 19 11:09:02 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Out put:

bash-3.2$ cat plan10046_lev12.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Nov 19 11:09:02 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Trace file: PRDDB2_ora_40829114_plan10046_lev12.trc
Sort options: exeela  prsela  fchela 
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 0kjg1c2g4gdcr Plan Hash: 0
ALTER SESSION SET EVENTS '10046 trace name context off'
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: SYS
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1      112.60        112.60

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: ehischn2_ora_40829114_plan10046_lev12.trc
Trace file compatibility: 11.1.0.7
Sort options: exeela  prsela  fchela 
       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
      34  lines in trace file.
       0  elapsed seconds in trace file.

Example:3

SQL>ALTER SESSION SET tracefile_identifier = 'Example10046_lev12';
SQL>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
--wait some time
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

7) How to analyze tkprof outputs:
Ans:
The SQL Trace facility provides performance information on individual SQL statements. It generates the following statistics for each statement:

•Parse, execute, and fetch counts
•CPU and elapsed times
•Physical reads and logical reads
•Number of rows processed
•Misses on the library cache
•Username under which each parse occurred
•Each commit and rollback
•Wait event data for each SQL statement, and a summary for each trace file

8) Any performance issue during sql trace?

Caution : sql trace

Although it is possible to enable the SQL Trace facility for a session or for an instance, it is recommended that you use the DBMS_SESSION or DBMS_MONITOR packages instead. When the SQL Trace facility is enabled for a session or for an instance, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files. Using the SQL Trace facility can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space.