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.

Troubleshoot ORA-16191: ORA-17629 with case studies in Active Dataguard ( Oracle 11g)

~ Troubleshoot ORA-16191: ORA-17629 with case studies in Active Dataguard ( Oracle 11g)
~ Troubleshoot : when standby server is out of synch
~ Troubleshoot : when archivelogs are not applying in standby side

Case:1:  ORA-16191

Error 1017 received logging on to the standby ( message fro alert log)
< ------------------------------------------------------------
< Check that the primary and standby are using a password file
< and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
< and that the SYS password is same in the password files.
<       returning error ORA-16191
< ------------------------------------------------------------
< PING[ARC2]: Heartbeat failed to connect to standby 'STANDBY'. Error is 16191.
< Sat Jul 06 15:23:28 2013
< Error 1017 received logging on to the standby

If above error found from alert log in DR side, i.e., some body changed Primary sys password.

Solution: Recreate password file both Primary and DR side with same password and re-synch again.

Now do the following: e.g.,

SQL> alter database recover managed standby database cancel;
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database using  current logfile disconnect;

Case:2: : ORA-17627: ORA-01017

channel ORA_DISK_1: SID=33 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/27/2012 15:05:53
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/27/2012 15:05:53
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01017: invalid username/password; logon denied
ORA-17629: Cannot connect to the remote database server

Solution:

RMAN> duplicate target database for standby from active database nofilenamecheck;

This may not work. do the following

RMAN> run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database nofilenamecheck;
}

Case-3 : ORA-16191

Thu Nov 14 10:52:35 2013
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files.
returning error ORA-16191

Solution:

1) In primary side:

Ship missed archive logs:
pass the password file again to standby side and rename it with standy database name in standby side.

2) In standby side:
e.g.,
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database disconnect from session;
RMAN> catalog start with '/oracle/PRIMARY/flash_recovery_area/archivelogs';

Now check, sure archive logs will apply

Now you will find following type alerts in alert log file.

Thu Nov 14 12:17:07 2013
Media Recovery Log /oracle/PRIMARY/flash_recovery_area/archivelogs/1_7945_804801209.dbf
Thu Nov 14 12:17:34 2013
Media Recovery Waiting for thread 1 sequence 7946

Now do the following:

SQL> alter database recover managed standby database cancel;
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database using  current logfile disconnect;


Now sure it will start shipping

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
          7951


Hope this document will help you.
Post your comments.

Fix : ORA-00910: specified length too long for its data type in materialized views ( mviews)

~ ORA-00910: specified length too long for its data-type - a solution
~ Issues with creating Materialized views when remote table contains XML type column.
~ Error : ORA-00910: specified length too long for its data-type

-- Solution

Please use following sample method while creating Mviews for remote tables containing a XML type column.

1) Use xmltype.getclobval() for XML  type column.
2) Don't forget to add exact column name again as alias after convertion.
3) Recheck all the columns are placed in DDL or not.
4) Use complete refresh to add fresh rows in mviews.

Example:

-- DDL of sample Mview while remote table contains XML type data.

create materialized view owner.remote_table
refresh complete on demand as
select methodid, worksheetcode, xmltype.getclobval(worksheet) worksheets,status  from owner.remote_table@dblink_name;

Useful queries for mview refresh/ Hang sessions :

-- All mview refresh method and time analysis
select MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD,SUMMARY,FULLREFRESHTIM,INCREFRESHTIM
from ALL_MVIEW_ANALYSIS
where owner='&owner';

-- Mview status during refresh
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR "MVIEW BEING REFRESHED",
       decode(REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN') REFTYPE,
       decode(GROUPSTATE_KNSTMVR,
              1,
              'SETUP',
              2,
              'INSTANTIATE',
              3,
              'WRAPUP',
              'UNKNOWN') STATE,
       TOTAL_INSERTS_KNSTMVR INSERTS,
       TOTAL_UPDATES_KNSTMVR UPDATES,
       TOTAL_DELETES_KNSTMVR DELETES
  from X$KNSTMVR X
 WHERE type_knst = 6
   and exists (select 1
          from v$session s
         where s.sid = x.sid_knst
           and s.serial# = x.serial_knst);

-- Identify the Hung Materialized View to kill its session. 
select VS.INST_ID,VL.SID||','||VS.SERIAL#,VS.USERNAME,ao.object_name,
'alter system kill session '''||vl.SID||','||vs.SERIAL#|| ',@'||vs.inst_id ||''' IMMEDIATE;'
  from GV$LOCK VL, Gv$session VS, all_objects ao
 where vl.type = 'JI' and vl.Lmode = 6 and VS.SID=VL.SID
 and VS.INST_ID=VL.inst_id and vl.ID1=ao.object_id;

-- get the list of views refreshing right now.
SELECT CURRMVOWNER, CURRMVNAME FROM V$MVREFRESH


Thanks ... Cheers !!!          

Oracle Database - Bytes or Characters for VARCHAR2 and CHAR

Oracle Database - Bytes or Characters for VARCHAR2 and CHAR

Historically database columns which hold alphanumeric data have been defined using the number of bytes they store. This approach was fine as the number of bytes equated to the number of characters when using single-byte character sets.

With the increasing use of multi-byte character sets to support globalized databases comes the problem of bytes no longer equating to characters.

The VARCHAR2 and CHAR types support two methods of specifying lengths:

In bytes: VARCHAR2(10 byte). This will support up to 10 bytes of data, which could be as few as two characters in a multi-byte character sets.
In characters: VARCHAR2(10 char). This will support to up 10 characters of data, which could be as much as 40 bytes of information.


1) Difference between varchar2(11 CHAR) and varchar2(11 BYTE)

If you define the field as VARCHAR2(11 BYTE), Oracle will allocate 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle to allocate enough space to store 11 characters, no matter how many bytes it takes to store each one. I believe that in Oracle 10g, 3 bytes per character were used.

Advise:
When using a multibyte character set such as UTF8, since your intention is likely to define a column that can in fact store characters of data, you may use :

> the CHAR modifier in the VARCHAR2/CHAR definition. That is, use of VARCHAR2(80 CHAR), not VARCHAR2(80).
> the session or system parameter NLS_LENGTH_SEMANTICS to change the default behavior from BYTE to CHAR.
It's not recommended to change this setting at the system level scope but rather an the SESSION scope.

ALTER system SET nls_length_semantics=CHAR scope=both;
ALTER system SET nls_length_semantics=byte scope=both;

ALTER session SET nls_length_semantics=CHAR;
ALTER session SET nls_length_semantics=byte

Depending on the system configuration, size of CHAR mesured in BYTES can vary. In your examples:

1.Limits field to 11 BYTE
2.Limits field to 11 CHARacters

Conclusion:
1 CHAR is not equal to 1 BYTE.
BYTE would be the default; the other possibility is CHAR.

2) Difference between varchar2(10 CHAR) and varchar2(10)

BYTE would be the default; the other possibility is CHAR.
So, technically, there is no difference between the two (BYTE being default), the difference is in the CHAR option: a character
can take more that one byte (up to 3 byte, iirc). Think multibyte character sets.
This option tells a multibyte database (!) to use character semantics when storing data: a varchar2(10) would be able to store 10 characters,
in stead of 10 byte worth of characters.

For example in ASCII each character is coded on a single byte, so a varchar2(10) field would accept any 10 characters long word, whereas in UTF-16 where each character is coded on 2 bytes, the same field would only accept 5 characters long words.

In comparison, a varchar2(10 CHAR) would always accept 10 characters long words, be the character set ASCII or UTF-16 or any other one.

for details click here for Character Semantics and Globalization in Oracle
 character-semantics-and-globalization


Thanks

Temporary Tablespace Enhancements in Oracle Database 11g

~ Temporary Tablespace Enhancements in Oracle Database 11g onwards
~ Oracle 11g has a new view called DBA_TEMP_FREE_SPACE that displays information about temporary tablespace usage.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE     FREE_SPACE
------------------------------         ---------------                  ---------------                ----------
TEMP                                            56623104                            56623104                   55574528

1 row selected.

SQL>Armed with this information, you can perform an online shrink of a temporary tablespace using the ALTER TABLESPACE command.

SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE     FREE_SPACE
------------------------------         ---------------                 ---------------                   ----------
TEMP                                           42991616                        1048576                            41943040

1 row selected.

SQL>The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/DB11G/temp01.dbf' KEEP 30M;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE     FREE_SPACE
------------------------------         --------------- -                --------------                    ----------
TEMP                                              31522816                          65536                          31457280

1 row selected.

SQL>The KEEP clause specifies the minimum size of the tablespace or tempfile. If this is omitted, the database will shrink the tablespace or tempfile to the smallest possible size.

SQL> ALTER TABLESPACE temp SHRINK SPACE;

Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE     FREE_SPACE
------------------------------         ---------------             ---------------                  ----------
TEMP                                             1114112                      65536                            1048576

1 row selected.







Note: If you are doing in prod database, do it in off peak hours and reset application server services.

CURSOR_SHARING parameter vs High Version count in Oracle

~ CURSOR_SHARING parameter Vs High Version count  in Oracle
~ CURSOR_SHARING parameter setting in Oracle : boost your database performance
~ About the parameter:

Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects) cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter.

Note:  The cursor_sharing=similar option has been deprecated in Oracle 11g and will be removed in version 12 per MOSC Note 1169017.1

Best to Keep : cursor_sharing=EXACT in OLTPs

Other notes on cursor_sharing:
-------------------------------------------------
SQL that is generated by ad-hoc query tools with hard-coded literal values embedded within the SQL. As we know, hard-coded literal values make the SQL statements non-reusable unless cursor_sharing=force is set in the Oracle initialization file. Shops that are plagued with non-reusable SQL can set cursor_sharing=force.

SQL in Oracle to see if your SQL is using bind variables.  Systems that need cursor sharing are littered with SQL that contains literal values, like this:

select cust_id from customer where cust_name = 'JONES';

These system that benefit from cursor_sharing have low "executions" because the SQL cannot be re-used it is only executed once. (as seen in the v$sql view) and the need for cursor sharing van be seen by Oracle hard parse (sharing criteria) elapsed time.  Other symptoms indicating the need for cursor_sharing include:

> High CPU attributable to shared pool activity
> High hard parses
> Low number of executions for individual SQL statements

Parameters for CURSOR_SHARING :
-------------------------------------------------
CURSOR_SHARING determines what kind of SQL statements can share the same cursors. Values:

•FORCE : Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

•SIMILAR : Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

•EXACT : Only allows statements with identical text to share the same cursor.


To Change cursor_sharing parameter value:
-------------------------------------------------

SQL> show parameter cursor_sharing ;

NAME                                 TYPE        VALUE
------------------       ------------------------ --------------------------------
cursor_sharing                   string        SIMILAR

SQL> alter system set cursor_sharing = 'EXACT' scope=spfile;

In RAC:
SQL> alter system set cursor_sharing = 'EXACT' scope=spfile sid='*';

Bounce the database to apply immediate effect.

Note: If you are (DBA) observing more counts coming against any sql_id ( e.g., see the query) then assume more version counts coming for that query. Confirm that more hard-coded values are used instead of bind variables. May be cursor_sharing parameter value is set as 'similar'. Change this value to 'exact' to maximize the performance.
e.g.,
select count(1) from v$SQL_shared_cursor where sql_id='2ht73m99gww6h'
--in RAC
select count(1) from gv$SQL_shared_cursor where sql_id='2ht73m99gww6h'

If values are in terms of 100 then more version counts are coming.

Hope this may help you. Always use oracle support to read more details.
Thanks