How to resolve “Unable to create Patch Object” error?

when I am supposed to patch a 11.2.0.1 database and checking conflicts, I got below error
FTU % opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419278
Invoking OPatch 11.1.0.6.0  
Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation.  All rights reserved.  
PREREQ session  
Oracle Home       : /u02/app/oracle/product/11.2.0.1/FTU
Central Inventory : /u01/app/oracle/oraInventory   
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.0
OUI version       : 11.2.0.1.0
OUI location      : /u02/app/oracle/product/11.2.0.1/FTU/oui Log file location : /u02/app/oracle/product/11.2.0.1/FTU/cfgtoollogs/opatch/opatch2011-11-05_01-50-34AM.log  
Invoking prereq “checkconflictagainstohwithdetail”
The location “./12419278/README.html” is not a directory or a valid patch zip file.
Prereq “checkConflictAgainstOHWithDetail” not executed
PrereqSession failed: Unable to create Patch Object.
Exception occured : Patch ID is null.  
OPatch failed with error code 73
We have two solutions for this problem
SOLUTION # 1
——————-
Move the text files like readme.html, readme.txt etc from the patch directory to some other location and try checking conflicts and it will be successful
But the problem is, this is not a permanent solution and just a workaround
SOLUTION # 2
——————–
This is the permanent solution for this problem. Cause for this error is we don’t have latest opatch version. As you can see in the output above my database version is 11.2.0.1, but my opatch version is 11.1.0.6. So, download latest opatch version from My Oracle Support with the help of patch # 6880880 and install it
Steps to install new opatch
1. copy the downloaded zip file for patch # 6880880 to $ORACLE_HOME
2. change location to ORACLE_HOME and rename the old OPatch directory
example : FTU%  cd $ORACLE_HOME
FTU%  mv OPatch OPatch.old
3. unzip the zip file which will create new OPatch directory
4. run below command to check if version is changed
FTU% $ORACLE_HOME/OPatch/opatch version
The above steps are also available in README file of the patch

Listener fails to start if listener log file is > 4GB

I got an email from application team stating they are unable to connect to database and getting below error
TNS-12518: TNS: listener could not hand off client connection
I immediately checked the listener status on the database server (My database is of 11.2.0.1 version on Windows platform) and it is up and running fine.As per regular troubleshoot check, the solution would be to trace the connection
ORA-12518: TNS:listener could not hand off client connection
 
Cause: The process of handing off a client connection to another process failed.
Action: Turn on listener tracing and re-execute the operation. Verify that the listener and database instance are properly configured for direct handoff. If problem persists, call Oracle Support.
But, before tracing, I checked the listener log file and observed below message
TNS-12560: TNS: Protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 53: Unknown error
TNS-12518: TNS: listener could not hand off client connection
TNS-12571: TNS: packet writer failure
TNS-12560: TNS: Protocol adapter error
TNS-00530: Protocol adapter error
64-bit Windows Error: 54: Unknown error
I tried reloading the listener and after restarting, users were able to connect for sometime. But after few minutes again same issue(error) occurred.
Finally after searching few sites, I got to know that this would be because of large size of listener log file.
If the listener.log is greater than 4GB in size, the listener may fail to start on Windows platforms.
This is a bug (bug no 9497965) on windows and as a resolution, I moved the file to other location, so that Oracle will created a new log file. From that moment, there are no issues observed in the user connectivity.
So, next time if you face this issue, check your listener log file size.
Please refer to MOS DOC ID 9497965.8 for more details….
Note : As per the MOS doc, this bug will not reproduce in 11.1.0.7 patch 29 or higher, but I am still wondering because my version is 11.2.0.1. It might have not fixed now….

Expdp parameters

TABLESPACES:In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, its dependent objects are also unloaded.

TRANSPORT_TABLESPACES:Use this parameter to specify a list of tablespace names for which object metadata will be exported from the source database into the target database.

TRANSPORT_FULL_CHECK:Specifies whether or not to check for dependencies between those objects inside the transportable set and those outside the transportable set. This parameter is applicable only to a transportable-tablespace mode export.

TRANSPORT_FULL_CHECK={y | n}

If TRANSPORT_FULL_CHECK=y, then Export verifies that there are no dependencies between those objects inside the transportable set and those outside the transportable set. The check addresses two-way dependencies. For example, if a table is inside the transportable set but its index is not, a failure is returned and the export operation is terminated. Similarly, a failure is also returned if an index is in the transportable set but the table is not.

If TRANSPORT_FULL_CHECK=n, then Export verifies only that there are no objects within the transportable set that are dependent on objects outside the transportable set. This check addresses a one-way dependency. For example, a table is not dependent on an index, but an index is dependent on a table, because an index without a table has no meaning. Therefore, if the transportable set contains a table, but not its index, then this check succeeds. However, if the transportable set contains an index, but not the table, the export operation is terminated.

Oracle managed files

Oracle managed files were introduced in version 9. You can implement them using initialisation parameters. These can be set:

1. In the init.ora or server parameter file.
2. In an alter session or alter system statement.

The parameters specify directories which Oracle should use for datafiles in subsequent DDL statements such as create tablespace etc. You can see what I mean in the example below, which I ran on Oracle 9.2.0.4.0:

First, specify the directory where files should be created using the db_create_file_dest parameter:

SQL> alter session set db_create_file_dest = '/mnt/redhat';

 

Session altered.

 

db_create_file_dest specifies the default location for oracle 

managed datafiles.this location is also used as default location 

for oracle managed control files and online redo logs if none of

the db_create_online_log_dest_n parameters are specified,if file

system location is specified as default directory then create the

directory structure and give permissions.

 

db_create_online_log_dest_n (n=1,2,3,4,5) specifies the default 

location for oracle managed control files and online redo logs.

if more than one location is specified then oracle managed control file and online redo log is multiplexed across the other db_

create_online_log_dest_n locations.

 

Now create a tablespace. Oracle is managing the creation of datafiles so no filename is required:

 

SQL> create tablespace srinu

  2  datafile size 10m

  3  /

 

Tablespace created.

 

SQL>

 

Check the name(s) of the datafile(s) in the tablespace. There is only one and Oracle has created it in the location specified by the db_create_file_dest parameter:

 

SQL> l

  1  select file_name, bytes from dba_data_files

  2* where tablespace_name = 'SRINU'

SQL> /

 

FILE_NAME                                     BYTES

---------------------------------------- ----------

/mnt/redhat/o1_mf_srinu_7fh2qylt_.dbf     10485760

 

SQL>

 

Add a datafile to the tablespace and check the name(s) of the datafile(s) again:

 

SQL> alter tablespace srinu add datafile size 5m

  2  /

 

Tablespace altered.

 

SQL> select file_name, bytes from dba_data_files

  2  where tablespace_name = 'SRINU'

  3  /

 

FILE_NAME                                     BYTES

---------------------------------------- ----------

/mnt/redhat/o1_mf_srinu_7fh2qylt_.dbf     10485760

/mnt/redhat/o1_mf_srinu_7fh2zndg_.dbf      5242880

 

SQL>

 

Look at the files at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -1

o1_mf_srinu_7fh2qylt_.dbf

o1_mf_srinu_7fh2zndg_.dbf

TEST9 >

 

Drop the tablespace:

 

SQL> drop tablespace srinu

  2  /

 

Tablespace dropped.

 

SQL>

 

Oracle deletes managed files once they are no longer required.Check that the files have gone at the Linux level:

 

TEST9 > pwd

/mnt/redhat

TEST9 > ls -l

total 0

TEST9 >

 

Oracle table fragmentation causing performance issue

Issue Description:

Application team reported, one of the jobs is running very slow in production. Due to this delay another job also accessing the same objects and it’s causing blocking locks on database.

1.    Almost one year, we deployed the new enhancement in prod database. Suddenly we faced this performance issue.

2.    Job A is running every  hour and it’s copying the data  from few tables and fetching into another DB via DB link and deleting the records in source database (whatever data copied from source database to target database)

3.     We locked the better statistics for these tables based on performance testing.

Impact:

1.    Job A deleting records on tables for every hour, Due to the large number of deletion on tables causing the fragmentation on tables. Obviously if tables are fragmented, corresponding indexes also fragmented.

2.    If tables statistics were locked, so I couldn’t find the exact details about these tables also I couldn’t gather the current stats for these tables.

How to check if tables are fragmented?

select t.owner,

t.table_name,

t.avg_row_len,

t.last_analyzed,

s.bytes/1024/1024 as SEGMENT_SIZE_MB

from

dba_tables t,

dba_segments s

where t.table_name=s.segment_name

and

t.owner=s.owner

and s.segment_type='TABLE'

and owner='&owner';

 

These below table’s stats were locked. So I manually count the records from below tables and put on Original columns.

OWNER     TABLE_NAME    AVG_ROW_LEN    SEGMENT SIZE  (MB)    Original Rows    Original Space Size (MB)
TEST           Table1             302                          8.000                         0                               0.000
TEST           Table2            120                          259.000                     4369                          0.650
TEST           Table3            104                          145.000                     442                            0.057
TEST           Table4           148                          0.125                         0                                0.000
TEST           Table5           147                          0.125                         0                                0.000
TEST           Table6           0                              0.125                         0                                0.000
TEST           Table7           0                              0.125                         0                                0.000
TEST           Table8           154                          20.000                       4509                          0.861
TEST           Table9           143                          130.000                      25058                       4.442
TEST           Table10          152                          59.000                        0                               0.000
TEST           Table11          158                            0.125                        0                               0.000
TEST           Table12           0                               0.125                        0                               0.000
                                                                   ~622MB                                                      ~6MB

These tables having only need ~ 6 MB, but these occupied ~622 MB.

How to calculate the actual space requirement?

Actual Space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)

Explanation:                                     

(Num of rows in a table) * (Avg_row_len) --- gives a actual space required for a table

Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table.

Note: whenever we creating the segment oracle initially allocated, 0.125 MB space allocated to each segment.

Temporary Solution:

We have a several method to fix the fragmentation.( reset the HWM)

1.    Export/import method

2.    Online redefinition method

3.    CTA’s method

4.    Move the table segment

I suggested the below method.

1)    Hold the jobs

2)    Take the listed tables backup using exp/expdp utility

3)    Truncate the tables

4)    Imported the tables using backup

5)    Release the Job

Permanent Solution:

1.    Tables should be change as daily partition tables.

2.    Instead of deleting the records from tables for every hour, every day that job will drop the daily partition. It will help to avoid the fragmentation.

OPTIMIZER_MODE-ALL_ROWS/FIRST_ROWS/FIRST_ROWS_N

Possible values for optimizer_mode = choose(or)all_rows/ first_rows/ first_rows[n]
By default, the value of optimizer_mode is CHOOSE which basically means ALL_ROWS.
FIRST_ROWS and ALL_ROWS are both cost based optimizer features. You may use them 
according to their requirement.
FIRST_ROWS/ FIRST_ROWS[n]

In simple terms it ensures best response time of first few rows (n rows).

This mode is good for interactive client-server environment where server serves first few rows and by the time user scroll down for more rows, it fetches other. So user feels that he has been served the data he requested, but in reality the request is still pending and query is still fetching the data in background.

Best example for this is toad, if you click on data tab, it instantaneously start showing you data and you feel toad is faster than sqlplus, but the fact is if you scroll down, you will see the query is still running.

Ok, let us simulate this on SQLPLUS

Create a table and index over it:

SQL> create table test as select * from all_objects;

Table created.
SQL> create index test_in on test(object_type);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SAC’,'TEST')

PL/SQL procedure successfully completed.

SQL> select count(*) from test;COUNT(*)----------37944

SQL> select count(*) from test where object_type='JAVA CLASS';

COUNT(*)----------14927


You see out of almost 38k records, 15k are of JAVA class.

And now if you select the rows having object_type=’JAVA_CLASS’, it should not use index as almost half of the rows are JAVA_CLASS.

It will be foolish of optimizer to read the index first and then go to table.


Check out the Explain plans

SQL> set autotrace traceonly exp

SQL> select * from test where object_type='JAVA CLASS';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------

0 SELECT STATEMENT 1001 94094 10 (0) 00:00:01 * 1 TABLE ACCESS FULL TEST 1001 94094 10 (0) 00:00:01

--------------------------------------------------------------------------
As you see above, optimizer has not used Index we created on this table.

Now use FIRST_ROWS hint:

SQL> select /*+ FIRST_ROWS*/ * from test where object_type='JAVA CLASS';

Execution Plan
----------------------------------------------------------
Plan hash value: 3548301374
---------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------
0 SELECT STATEMENT 14662 1345K 536 (1) 00:00:07 1 TABLE ACCESS BY INDEX ROWID TEST 14662 1345K 536 (1) 00:00:07 * 2 INDEX RANGE SCAN TEST_IN 14662 43 (3) 00:00:01
---------------------------------------------------------------------------------------
In this case, optimizer has used the index.

Q> Why?

Ans> Because you wanted to see first few rows quickly. So, following your instructions oracle delivered you first few rows quickly using index and later delivering the rest.
See the difference in cost, although the response time (partial) of second query was faster but resource consumption was high.

But that does not mean that this optimizer mode is bad. As I said this mode may be good for interactive client-server model. In most of OLTP systems, where users want to see data fast on their screen, this mode of optimizer is very handy.

Important facts about FIRST_ROWS

It gives preference to Index scan Vs Full scan (even when index scan is not good).
It prefers nested loop over hash joins because nested loop returns data as selected (& compared), but hash join hashes one first input in hash table which takes time.

Cost of the query is not the only criteria for choosing the execution plan. It chooses plan which helps in fetching first rows fast.

It may be a good option to use this in an OLTP environment where user wants to see data as early as possible.

ALL_ROWS

In simple terms, it means better throughput
While FIRST_ROWS may be good in returning first few rows,

ALL_ROWS ensures the optimum resource consumption and throughput of the query.

In other words, ALL_ROWS is better to retrieve the last row first.

In above example while explaining FIRST_ROWS, you have already seen how efficient ALL_ROWS is.

Important facts about ALL_ROWS

ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example ‘where employee_code=7712’), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best.

Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers)
Likes hash joins over nested loop for larger data sets. ConclusionCost based optimizer gives you flexibility to choose response time or throughput. So use them based on your business requirement.

statistics_level parameter options

Unsetting the default statistics_level=basic will disable AWR and the advisory utilities.
statistics_level=typical activates the advisory statistics collections, but it does not activate “Timed OS statistics” and “Plan Execution Statistics”.  To activate these statistics collection, you must set statistics_level=all.

SQL> ALTER SYSTEM SET statistics_level=basic;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO

MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO

PGA Advice                     DISABLED   DISABLED   TYPICAL    NO

Plan Execution Statistics      DISABLED   DISABLED   ALL        YES

Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO

Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO

Timed OS Statistics            DISABLED   DISABLED   ALL        YES

Timed Statistics               DISABLED   DISABLED   TYPICAL    YES

 

8 rows selected.

 

SQL> ALTER SYSTEM SET statistics_level=typical;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO

MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO

PGA Advice                     ENABLED    ENABLED    TYPICAL    NO

Plan Execution Statistics      DISABLED   DISABLED   ALL        YES

Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO

Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO

Timed OS Statistics            DISABLED   DISABLED   ALL        YES

Timed Statistics               ENABLED    ENABLED    TYPICAL    YES

 

8 rows selected.

 

SQL> ALTER SYSTEM SET statistics_level=all;

 

System altered.

 

SQL> SELECT statistics_name,

  2         session_status,

  3         system_status,

  4         activation_level,

  5         session_settable

  6  FROM   v$statistics_level

  7  ORDER BY statistics_name;

 

                               Session    System     Activation Session

Statistics Name                Status     Status     Level      Settable

------------------------------ ---------- ---------- ---------- ----------

Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO

MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO

PGA Advice                     ENABLED    ENABLED    TYPICAL    NO

Plan Execution Statistics      ENABLED    ENABLED    ALL        YES

Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO

Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO

Timed OS Statistics            ENABLED    ENABLED    ALL        YES

Timed Statistics               ENABLED    ENABLED    TYPICAL    YES