Thursday, May 29, 2014

Shared Pool Internals

In this post, we will see shared pool architecture and how it works internally since Oracle 7 through Oracle 11gR2. 
Defining the Shared Pool
Shared pool is the most important component in System Global Area (SGA) which is the RAM of database and it is the second largest in SGA memory area.  It contains several key performance related memory areas.  If the size of the shared pool is sized either less or more, then the entire database performance will suffer.
Purpose of Shared Pool
Many users run SQL or PL/SQL statements and it goes through 3 phases.

a.  Parse – Translate and optimize the query
b. Execute – Lay down the execution plan and run the query
c. Fetch – Pull back data from oracle objects based on the execution plan.
When SQL queries are executed, the shared pool caches the executable versions of SQL and PL/SQL statements.  So, when users/applications execute same SQL or PL/SQL code, it does multiple executions without doing hard parse which significantly results in reduction in CPU, memory and latches.

What does Shared Pool Contains
Shared pool contains following substructures

1. Fixed area/Permanent area
·         This is allocated during instance startup
·         Contains structures such as
i.                     Processes  - using PROCESSES paramenter
ii.                   Sessions - using SESSIONS parameter
iii.                  Segmented Arrays - used to store of objects.  It may grow dynamically.

2. Variable area
·         It is handled by oracle’s internal algorithm
·         Contains
i.                     Library cache

ii.                   Data dictionary cache ,etc

Shared pool acts like a repository for storing the SQL and Pl/sql code which was executed successfully such that if we receive similar statements, it can reduce the speed of the parsing.  Much of the shared pool usage is to support the execution of the shared SQL and Pl/SQL packages, but in order to build the cursor or compile PL/Sql package, we need to about all the database objects (like tables, procedures, indexes, etc) referenced by the pl/sql package and their optimizer statistics. All these information are stored in the shared pool independent of the cursors/program unit. 
Metadata are stored independently and hence it is easy to build the cursors.  Few executions using shared server, parallel query, RMAN used large memory allocations in the shared pool.
Fixed area is permanent during instance startup after setup by DBA and not much to deal with these areas.
Now, we will the dynamic memory allocation areas

Data Dictionary Cache
The data dictionary cache contains information like table definitions, referential integrities, index informations, column definitions, users, passwords, privileges, etc.  This is like the buffer cache except it stores dictionary information instead of user information. Since the dictionary informations are buffered, when parsing SQL cursors or during the compilation of PL/SQL program, it will be quick as these are in RAM.  It is also known as ROW CACHE.
V$ROWCACHE will give details on the hit ratio for Data dictionary. The tuning of data dictionary cache is done by changing shared_pool_size parameter.
select sum(gets) "Gets", sum(getmisses) "Get Misses", (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio" from v$rowcache;
The value of the Hit Ratio should be over 90%
Library Cache
The primary responsibility of Library Cache is to collect, parse and execute the SQL statements that are going against the database.  It contains the following.

a.       SQL/cursors – Executable representation of SQL statements that may be used repeatedly by many sessions.
b.      PL/SQL – Executable representation of PL/SQL packages, procedures, functions thaty may be used repeatedly by many sessions.
c.       Objects of various types required to parse and execute a SQL statement which includes tables, indexes, types, methods, etc.

Library cache will maintain the relation between tables and SQL statements (specifically child cursors) and if any change is done to tables, then oracle will know which cursors needs to invalidate and which one to keep in the cache.

We need to understand that Oracle will not keep all objects like synonyms, tables, indexes, etc in the cache. It keeps only the objects which are recently referenced.

Statistics about the library cache activity is available in V$LIBRARYCACHE.
If you want to know the objects in cache, we have to use V$DB_OBJECT_CACHE.

Result Cache

This cache holds the result sets and the query fragments. When a user queries again, the results will fetched from this cache and the response will be quick.


Internal Working of Shared Pool

As we have seen from above topics, we now know that shared pool is a collection of objects like tables, cursors, views, procedures, functions or packages (Pl/SQL package).

Size of single object will be small but when you have a PL/SQL package, the size would grow in MB as it includes many objects and its attributes. Example: A package body will have many procedures which in turn will occupy more space when we run a simple SQL statement.





Each shared pool object is not a single allocation unit (AU) and it is partitioned into independent memory allocations called “HEAPS”.  Number of Heaps for the object varies depends upon the type of objects. Eg: for a SQL cursor, there will be 2 heaps a) Smaller Heap for library cache metadata and b) Larger Heap for executable representation of the cursor.


Each HEAP is comprised of 1 or more chunks of memory of Standard Allocation Units (AU) and these reduce the problem of memory fragmentation.

When memory is allocated for objects, the memory is not allocated in contiguous fashion. i.e., the chunks will not be contiguous as you can see from the figure. 
But the memory (free lists – Pointers to memory chunks) must be contiguous.
Eg. Consider, we need 5KB size of Heap memory and each chunk is 4K and we need 1K to complete memory allocation, so the remaining memory is allocated to the heap from another memory chunk and hence the memory chunks are not contiguous.
When the chunks are not contiguous and if they work this way, it will avoid fragmentation. 
Generally, the chunk sizes are of 1k and 4K creating more uniform memory allocations. So, when same objects are allocated and aged out, same size of chunks are allocated and aged out. By doing so, we can avoid memory fragmentation and memory usage effectively.

LARGE POOL
RMAN, parallel query and shared servers uses allocation of more than 5KB and they need large allocation of memory. Hence the use other memory pool called LARGE POOL.
RESERVED POOL
Sometimes SQL, PL/SQL packages, if they are over 5KB, they might require larger contiguous chunks of memory.  Default settings of reserved pool is 4,400 bytes
If there is not enough free space in the shared pool, then oracle must search for free enough memory to satisfy the request.  Oracle might even have to age out the old objects to satisfy it.  In such cases, oracle will hold the latch resource for a period until it tries to find the memory. Till then, it will cause a minor disruption to the other requests (concurrent request) at memory allocation.
So, what oracle does is, it internally configures a small space called RESERVERED POOL so that when  we have operations involving PL/SQL and trigger compilations or to load any java objects, this will be used.  When the operation is over, the memory freed will be returned back to reserved pool.

When ORA-4031 error occurs
ORA-4031 occurs in any of the memory pools in the SGA when oracle cannot find a memory chunk large enough to satisfy the internal allocation request on behalf of users operation.
1.       A user executes a query.
2.       An object needs to be allocated in shared pool.
3.       The memory allocated for chunks to be contiguous and this will be allocated to the objects.
4.       If it is small memory size, then the Heap manager scans through freelists and if it is small, it will allocated.
5.       If the requested amount of contiguous memory is not available for chunks, then Heap Manager iterates through the shared pool’s LRU list and it attempts to create a contiguous chunk of requested size (this is done by aging out the LRU objects).
6.       But, ORA-4031 error occurs even if the large/free space created by the heap manager is NOT CONTIGUOUS
In case if ASMM/AMM is enabled, an additional granule of memory is requested if sufficient contiguous memory can’t be found.



References :


Oracle database Architecture - Part 2

PGA – Program Global Area
It is the memory region which contains the data and control information for a server process.  PGA memory is not shared and it is created by oracle when a server process is started.

PGA is a memory heap that contains session-dependent variables required by dedicated or shared server processes.  Server process allocates memory that it requires in the PGA.

 Two components of 2 PGA are.

1. Stack Space and 
2.       User Global Area.
Stack Space:  It holds the bind variables, plsql array,etc. 
User Global Area: UGA contains the following.

1. Session Memory 
2. SQL work Area
3. Private SQL area.

Private SQL area: Whenever a user initiates a session and issues a SQL statement, it will have Private SQL area to store bind variable values, query execution state information and query execution work areas, i.e., in short it uses a single shared SQL area.  Thus many private SQL areas can be associated with the same shared SQL area. 
But do not confuse that private SQL area is in UGA, while the shared SQL area which stores the execution plan in SGA.  
Eg. 50 executions of  Select * from test_table in one session and 30 executions of same query in a different session can share the same plan.
Private shared SQL area are not shared and may contain different values and data

Cursor
Cursor on the other side is a name or pointer to the private SQL area.  Cursor is like a pointer on the client side and as a state on the server side.

Private SQL area is divided into following areas.
1. Run-time area – Contains query execution state information.  It also tracks the number of rows retrieved so far when the SQL is going for Full Table Scan (FTS). This is the area oracle creates initially when the execute request is passed.  For DML statements, the run-time area is freed when the SQL statement is closed.
2.  Persistent Area – It contains the bind variables. When the user executes the statement, the bind variable is supplied at run time. This area is freed only when the cursor is closed.
The allocation and deallocation of private SQL areas mainly dependent on the application design, eventhough the private sql areas for client process is limited by OPEN_CURSORS.
               
SQL Work Area
This private allocation of PGA memory is used for memory-intensive operations like sorting, has joins, bitmap merge join,etc.
Ø  Sort area is used for sorting operation. 
Ø  Hash area is used by hash join for building the hash table
Ø  Bitmap merge area is used by bitmap merge operation which uses to merge data retrieved from scans of multiple bitmap indexes.

When we use automatic memory management for PGA using PGA_AGGREGATE_TARGET , these work areas are automatically managed by oracle itself.
The bigger the work areas, the more the performance of the operator but with higher cost in memory consumption.
We have to allocate size of the work area size such that it should be big enough to accommodate the input data and auxillary memory structures allocated by its SQL operator.  If not, the response time increases, because the input data has to spill to temporary disk storage.  In extreme cases, when the size of sort area size is too small, multiple passes over the data pieces must be performed. This would increase the response time drastically.

We can see these with more examples in PGA tuning post. 


Oracle database Architecture - Part 1

Oracle database is widely used RDBMS for nearly 2 decades and has become most successful database. In this article, we will see how the oracle database architecture is designed and how db works both at memory and OS level.
Oracle database server is categorized into 3 parts.

1. Oracle memory structures
2. Oracle background structures and
3. Oracle disk utilization structures.
Oracle memory structures consist of two main components.
i.                     System Global Area (SGA)

ii.                   Program Global Area (PGA)


SGA – It is a RAM of the database and this is the first and most important component.  Whenever DBA speaks about memory of the database, it means that it is SGA.
SGA stores different components of memory usage that are designed to fetch the data quickly for users and to maximize the number of concurrent users to access the oracle instance.

SGA consists of 3 mandatory parts.

a.  Buffer cache
b.  Redo log buffer
c. Shared pool

Buffer cache consists of buffers which are in the size of database blocks. These are designed to store data blocks recently used by the SQL statements issued by the user. If the recently used  blocks are used in the buffer cache, the performance will improvise on subsequent fetching of the data using select queries.
Redo log buffer allows the user processes to write their redo log entries to the memory area in order to increase the speed on tracking the database changes.  It is important to remember that every user processes that makes a change to database must write an entry to the redo log in order to allow the database to recover the changes. If the database set up to archive redo logs, the database changes are kept  in order to rebuild the database objects in the event of disk failure.  The main purpose of having the redo information in memory is that it avoids the need for the  user process to spend extra time to write directly on the physical files (redo log files on the disk). By doing so, oracle database avoids contention on the disk usage which would in-turn would slow down the database.
Shared pool is a another most important mandatory component of Oracle memory. It consists of
1.       Library cache
2.       Data Dictionary cache (also known as “row” cache)
Shared SQL library cache is designed to store the parse information of SQL queries executed against the database. Parse information includes the set of database operations that SQL execution performs to obtain the data (i.e., Parse phase,execute phase and fetch phase). This information is a shared resource in the library cache. When another user session/process executes for same query again, oracle will first check whether the query is existing in the shared pool (library cache) and if it is available, then the parse information in the shared pool will be used. But, the data returned to the user will not be shared in the shared pool, because sharing data between applications represent a integrity/security issue.
Data dictionary cache or “row” cache as referred is other mandatory component of shared pool. This is used to store data from data dictionary in order to improve response time on the data dictionary views.  Since all user processes and oracle database internal processes use data dictionary, the database benefits a lot in performance when data dictionary objects are cached in memory.

We will see more about shared pool architecture and its working in a different post.



Index on Foreign Keys to avoid locks


In this article we will see how the locks are on formed on the tables when we do not have an index on the foreign key colum

Example, we will see the following scenario.

1.       Let us create a primary table “P” with primary key.

SQL> create table p (x int primary key);

Table created.

2.       Create a child table with column “x” referencing to the parent table “P”
SQL> create table c (x references p);

Table created.

3.       Structure of the tables are as below.
SQL> desc p;
 Name                                   Null?    Type
 -------------------------------------- -------- --------------------------
 X                                      NOT NULL NUMBER(38)

SQL> desc c;
 Name                                   Null?    Type
 -------------------------------------- -------- --------------------------
 X                                               NUMBER(38)


4.       Let us insert records into the parent table “P”  and commit the changes.
SQL> insert into p select rownum from dual connect by level < 10000;

9999 rows created.

SQL> commit;

Commit complete.

5.       Insert the same from table to “P” into table “C”

SQL> insert into c select * from p;

9999 rows created.

SQL> commit;

Commit complete.


6.       Now, update the child table “C” for a value of row and pause the session without commiting.  This implies that the session is not ended.

SQL> update c set x=2 where x=1;

1 row updated.

SQL> pause

7.       In another session, update the child record of another row  and do not commit. 
SQL> update c set x=2 where x=3;

1 row updated.

8.       Next, in same session (2nd session), try to delete a row from parent table “P”

SQL> delete from p where x=10;
9.       Now, you can the session is not ending the hanging.

10.   Let us see whether do we have any locks on the tables .
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
  2    3    4    5    6    7
BLOCKING_STATUS
------------------------------------------------------------------------------------------------------------------------------------------------------
BCTEST@vm1 ( SID=145 )  is blocking BCTEST@vm1 ( SID=145 )
BCTEST@vm1 ( SID=133 )  is blocking BCTEST@vm1 ( SID=145 )



SQL> select a.sid,a.serial#,c.object_name,c.object_type from V$session a, V$locked_object b, dba_objects c
 where a.sid=b.session_id
 and b.object_id=c.object_id;  2    3

       SID    SERIAL# OBJECT_NAME     OBJECT_TYPE
---------- ---------- --------------- -------------------
       145         61 P               TABLE
       133         11 P               TABLE
       145         61 C               TABLE
       133         11 C               TABLE
SQL> @pid.sql
Enter Oracle SID: 145
=====================================================================
SID/Serial  : 145,61
Foreground  : PID: 6794 - sqlplus@vm1 (TNS V1-V3)
Shadow      : PID: 6797 - oracle@vm1 (TNS V1-V3)
Terminal    : pts/2/ UNKNOWN
OS User     : oracle on vm1
Ora User    : BCTEST
Status Flags: ACTIVE DEDICATED USER
Tran Active : 000000006D487318
Login Time  : Fri 15:42:49
Last Call   : Fri 15:52:26 -           6.2 min
Lock/ Latch : 000000006E39F108/ NONE
Latch Spin  : NONE
Current SQL statement:
        delete from p where x=:"SYS_B_0"
Previous SQL statement:
        update c set x=:"SYS_B_0" where x=:"SYS_B_1"
Session Waits:
        WAITING: enq: TM - contention
Connect Info:
        : Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        : Oracle Advanced Security: authentication service for Linux: Version 11.2.0.3.0 - Production
        : Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production
        : Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
        TRANSAC ENQ H: X R: NONE - RS+SLOT#983048 WRP#3823
        DML/DATA ENQ H: RX R: RSX - C
        DML/DATA ENQ H: RX R: NONE - P
        TYPE=AE H: S R: NONE - ID1=100 ID2=0
=====================================================================
SQL> @pid.sql
Enter Oracle SID: 133
=====================================================================
SID/Serial  : 133,11
Foreground  : PID: 6880 - sqlplus@vm1 (TNS V1-V3)
Shadow      : PID: 6881 - oracle@vm1 (TNS V1-V3)
Terminal    : pts/1/ UNKNOWN
OS User     : oracle on vm1
Ora User    : BCTEST
Status Flags: INACTIVE DEDICATED USER
Tran Active : 000000006D4C5558
Login Time  : Fri 15:48:17
Last Call   : Fri 15:51:50 -           7.0 min
Lock/ Latch : NONE/ NONE
Latch Spin  : NONE
Current SQL statement:
Previous SQL statement:
        update c set x=:"SYS_B_0" where x=:"SYS_B_1"
Session Waits:
        WAITING: SQL*Net message from client
Connect Info:
        : Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.3.0 - Production
        : Oracle Advanced Security: authentication service for Linux: Version 11.2.0.3.0 - Production
        : Oracle Advanced Security: encryption service for Linux: Version 11.2.0.3.0 - Production
        : Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0.3.0 - Production
Locks:
        TYPE=AE H: S R: NONE - ID1=100 ID2=0
        DML/DATA ENQ H: RX R: NONE - P
        TRANSAC ENQ H: X R: NONE - RS+SLOT#851992 WRP#3894
        DML/DATA ENQ H: RX R: NONE - C
=====================================================================
SQL>


1.       From the above results, you can see the sessions are locked one another on both table “P” and  “C”. 
2.       Session 145 is blocked itself as it has both update and delete statement within itself and not continuing due to references between the columns in both tables.


11.   Next, we will see what happens when we create index an index on foreign key (in child table).
SQL> create index c_idx on c(x);

Index created.

12.   Let us try to update and pause it in 1 session.
SQL> update c set x=2 where x=1;

1 row updated.

SQL> pause

In another session, let us update another row of child table and try to delete from Parent table.
SQL> update c set x=2 where x=3;

1 row updated.

SQL> delete from p where x=10;
delete from p where x=10
*
ERROR at line 1:
ORA-02292: integrity constraint (BCTEST.SYS_C0011326) violated - child record found
Here, we see that the lock is disappeared and we get an integrity constraint error.
So, we need to first delete the record from child table “C” and then from parent table “P”.



In case, if you want to delete both the parent and child records when we execute a delete a record from parent table, then we have to create foreign key as below.

alter table sample1 
add foreign key (col1) 
   references sample (col2)
on delete cascade;




Another example is below.

1.       Create a table supplier and insert some records.
CREATE TABLE supplier
( supplier_id number(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');
INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');
COMMIT;

2.       Create another table  Product . Please note on delete cascade.
CREATE TABLE product
( product_id number(10) not null,
product_name varchar2(50) not null,
supplier_id number(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE );

INSERT INTO product VALUES (1, 'Product 1', 1);
INSERT INTO product VALUES (2, 'Product 2', 1);
INSERT INTO product VALUES (3, 'Product 3', 2);
COMMIT;



3.       Delete some records from supplier (parent table )  in Session 1
SQL> DELETE supplier WHERE supplier_id = 1;

1 row deleted.

In another session, try to delete another record from same parent table

SQL>  DELETE supplier WHERE supplier_id = 2;
In 3rd session, try to insert a record into same Parent table.
INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');


4.       We can see, sessions 2 and 3 will hung  and will have enq:TM-contention issues from below query.

SQL> SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_type
FROM v$lock l, dba_objects o, v$session s
WHERE UPPER(s.username) = UPPER('&User')
AND l.id1 = o.object_id (+)
AND l.sid = s.sid
ORDER BY sid, type;  2    3    4    5    6
Enter value for user: BCTEST
old   3: WHERE UPPER(s.username) = UPPER('&User')
new   3: WHERE UPPER(s.username) = UPPER('BCTEST')

       SID    BLOCKER EVENT                          TY      LMODE    REQUEST OBJECT_NAME     OBJECT_TYPE
---------- ---------- ------------------------------ -- ---------- ---------- --------------- -------------------
       133            SQL*Net message from client    AE          4          0 ORA$BASE        EDITION
       133            SQL*Net message from client    TM          3          0 SUPPLIER        TABLE
       133            SQL*Net message from client    TM          3          0 PRODUCT         TABLE
       133            SQL*Net message from client    TX          6          0
       145        133 enq: TM - contention           AE          4          0 ORA$BASE        EDITION
       145        133 enq: TM - contention           TM          3          0 C               TABLE
       145        133 enq: TM - contention           TM          3          0 P               TABLE
       145        133 enq: TM - contention           TM          3          0 SUPPLIER        TABLE
       145        133 enq: TM - contention           TM          0          5 PRODUCT         TABLE
       145        133 enq: TM - contention           TX          6          0
       152        145 enq: TM - contention           AE          4          0 ORA$BASE        EDITION
       152        145 enq: TM - contention           TM          0          3 PRODUCT         TABLE
       152        145 enq: TM - contention           TM          3          0 SUPPLIER        TABLE

13 rows selected.

5.       Now, we will see which foreign keys are not having indexes.
SQL> col table_name format a15
SQL> col column_name format a35
SQL> SELECT * FROM (
  2  SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R' and  c.table_name='PRODUCT'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

TABLE_NAME      COLUMN_NAME                         COLUMN_POSITION
--------------- ----------------------------------- ---------------
PRODUCT         SUPPLIER_ID                                       1


6.       Now, create an index on foreign key in child table.
SQL> CREATE INDEX fk_supplier ON product (supplier_id);

Index created.




7.       Now, we can see all sessions are completing their statements without any issues.
Session 1

SQL> DELETE supplier WHERE supplier_id = 1;

1 row deleted

Session 2
SQL> DELETE supplier WHERE supplier_id = 2;

1 row deleted.


Session 3

SQL> INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');

1 row created.


Sunday, May 25, 2014

Connecting to MYSQL databases from Oracle database using DB links

Here is how to do it in Windows – namely Windows Server 2008 64 Bit with Oracle 11.2.0, but should be similar in any other constellation. Apart from the different ODBC setup, it is very similar on any other OS like Linux or Unix an Oracle server is running on.
1. Login to the following and download the odbc connector related to 32-bit or 64-bit.
http://dev.mysql.com/downloads/connector/odbc/
2. Download and install the odbc connector.
3. To install 32 bit in 64 bit windows machines go to  “C:\Windows\SysWOW64\” and run “odbcad32”  as an administrator and configure the ODBC as below.
4. Setup the ODBC data source to your MySQL database
4.1. Go System DSN tab .
4.b. Add the driver
4.c Give the credentials to connect to mysql database and select the database from the list.  Use the “TEST” button to test the ODBC connection .
5. Click OK in the remaining dialog boxes.
“mysql” on the server “localhost″, using the previously created MySQL user “oracle” (the password is also “oracle”).The ODBC data source name is “testmysql1”. If you click the button “Test” you can verify that the connection is working.
6.Configure Listener.ora file
LISTENER2 =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
     )
   )
)
SID_LIST_LISTENER2 =
    (SID_LIST=
        (SID_DESC =
             (SID_NAME =mysqltst)
             (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1\)
             (PROGRAM = dg4odbc)
        )
    )
It tells the listener to use dg4odbc, and there the SID “mysqltst” – see next step how to configure this SID. ORACLE_HOME of course has to be changed to your ORACLE_HOME directory.
Hint: If you encounter problems with dg4odbc try typing in “dg4odbc” at the command line. If the command is not recognized, add the directory $ORACLE_HOME/BIN (this is where dg4odbc should be located) to your path variable.

7. Configure data source / SID for dg4odbc
Go into the directory $ORACLE_HOME/HS/ADMIN and create a file called “initmysqltst.ora”  (The name convention is init + SID.ora – you can create many SIDs to different ODBC data sources). Write just the following line in the file:

HS_FDS_CONNECT_INFO = mysqltest1
This specifies which ODBC data source to use, in our case “test” which we configured in the first step – this should be enough to establish a connection, of course you can configure a lot of other ODBC parameters in this file but I will not go into details here.

8.  Configure tnsnames.ora

Now, in order to be able to create a database link, we also need to create an entry in $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ora:

MYSQLLINK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mysqltst)
    )
    (HS=OK)
  )

Please note, the HS=OK entry is very important, as well as using the same SID we configured in the two steps before, of course. MYSQLLINK is the name we are going to use for creating the database link in step 10.

9. Restart the Oracle Service and the TNS Listener Service
10. Create database Link
Logon to your Oracle database with DBA rights (e.g. as SYS) and create the database link:

SQL> create public database link mysqltest connect to "oracle" identified by "oracle" using 'mysqllink';

11.  Test database Link and finished

Hint: Note that table names in MySQL are case sensitive so user the double quotes to force the correct case of the table names.

SQL> select * from products@mysqltest;

no rows selected

Saturday, May 24, 2014

Step by Step procedure for applying patch set from 11.2.0.3.0 to 11.2.0.3.9 in Linux Standalone

Current version of GI and oracle database is 11.2.0.3

Now, I am upgrading from 11.2.0.3.0 to 11.2.0.3.9 using the following PSU 's

Initially, it was found the Patch 17540582 - 11.2.0.3.9 Patch Set Update  will be for GI, but this is only for DB.
In case for GI, we need to use the below PSU
for GI  Patch number - 17592127  (GI_PSU_Patch_No)
For DB, the patch number is 17540582  - (DB_PSU_Patch_No)
  17735354 (The GI PSU patch includes updates for both the Clusterware home and Database home that can be applied in a rolling fashion.)

So, we will be using 17735354 patch  GI PSU.

1. Refer to Readme files carefully before applying any patches.


Patch files required :

p6880880_112000_Linux-x86-64.zip  - For Opatch utility
p17735354_112030_Linux-x86-64   - for GI and DB



For GI PSU prior to 11.2.0.3.6, you must use the OPatch utility version 11.2.0.3.0 or later to apply this patch.
For GI PSU 11.2.0.3.6 and above, you must use OPatch utility version 11.2.0.3.4 or later..

2. Since our opatch version was less 11.2.0.3.4, we need to upgrade the opatch utility

As oracle user,
a. go to $ORACLE_HOME/OPatch path and take a backup of all the files to a backup location in /home/oracle/bkpopatch/
b. Only the files need to be backed up and not the OPatch directory.
c. Now copy p6880880_112000_Linux-x86-64.zip the file and unzip it in $ORACLE_HOME directory.
d. Once the above steps done, the results should be as follows.

[oracle@vm1 OPatch]$ ./opatch lsinv -detail oh $ORACLE_HOME
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.




Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-20_15-30-46PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-01-20_15-30-46PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


Installed Components (136):

Agent Required Support Files                                         10.2.0.4.3
Assistant Common Files                                               11.2.0.3.0
Bali Share                                                           1.1.18.0.0
Buildtools Common Files                                              11.2.0.3.0
Character Set Migration Utility                                      11.2.0.3.0
Cluster Verification Utility Common Files                            11.2.0.3.0
Database Configuration and Upgrade Assistants                        11.2.0.3.0
Database SQL Scripts                                                 11.2.0.3.0
Database Workspace Manager                                           11.2.0.3.0
Deinstallation Tool                                                  11.2.0.3.0
Enterprise Edition Options                                           11.2.0.3.0
Enterprise Manager Agent                                             10.2.0.4.3
Enterprise Manager Agent Core Files                                  10.2.0.4.4
Enterprise Manager Common Core Files                                 10.2.0.4.4
Enterprise Manager Common Files                                      10.2.0.4.3
Enterprise Manager Database Plugin -- Agent Support                  11.2.0.3.0
Enterprise Manager Database Plugin -- Repository Support             11.2.0.3.0
Enterprise Manager Grid Control Core Files                           10.2.0.4.4
Enterprise Manager plugin Common Files                               11.2.0.3.0
Enterprise Manager Repository Core Files                             10.2.0.4.4
Exadata Storage Server                                               11.2.0.1.0
Expat libraries                                                       2.0.1.0.1
Generic Connectivity Common Files                                    11.2.0.3.0
HAS Common Files                                                     11.2.0.3.0
HAS Files for DB                                                     11.2.0.3.0
Installation Common Files                                            11.2.0.3.0
Installation Plugin Files                                            11.2.0.3.0
Installer SDK Component                                              11.2.0.3.0
JAccelerator (COMPANION)                                             11.2.0.3.0
LDAP Required Support Files                                          11.2.0.3.0
OLAP SQL Scripts                                                     11.2.0.3.0
Oracle 11g Warehouse Builder Required Files                          11.2.0.3.0
Oracle Advanced Security                                             11.2.0.3.0
Oracle Application Express                                           11.2.0.3.0
Oracle Call Interface (OCI)                                          11.2.0.3.0
Oracle Clusterware RDBMS Files                                       11.2.0.3.0
Oracle Code Editor                                                   1.2.1.0.0I
Oracle Configuration Manager                                         10.3.5.0.1
Oracle Configuration Manager Client                                  10.3.2.1.0
Oracle Configuration Manager Deconfiguration                         10.3.1.0.0
Oracle Containers for Java                                           11.2.0.3.0
Oracle Core Required Support Files                                   11.2.0.3.0
Oracle Data Mining RDBMS Files                                       11.2.0.3.0
Oracle Database 11g                                                  11.2.0.3.0
Oracle Database 11g                                                  11.2.0.3.0
Oracle Database 11g Multimedia Files                                 11.2.0.3.0
Oracle Database Deconfiguration                                      11.2.0.3.0
Oracle Database Gateway for ODBC                                     11.2.0.3.0
Oracle Database User Interface                                       2.2.13.0.0
Oracle Database Utilities                                            11.2.0.3.0
Oracle Database Vault J2EE Application                               11.2.0.3.0
Oracle Database Vault option                                         11.2.0.3.0
Oracle DBCA Deconfiguration                                          11.2.0.3.0
Oracle Display Fonts                                                  9.0.2.0.0
Oracle Enterprise Manager Console DB                                 11.2.0.3.0
Oracle Extended Windowing Toolkit                                    3.4.47.0.0
Oracle Globalization Support                                         11.2.0.3.0
Oracle Globalization Support                                         11.2.0.3.0
Oracle Help For Java                                                  4.2.9.0.0
Oracle Help for the  Web                                             2.0.14.0.0
Oracle Ice Browser                                                    5.2.3.6.0
Oracle Internet Directory Client                                     11.2.0.3.0
Oracle Java Client                                                   11.2.0.3.0
Oracle JDBC Server Support Package                                   11.2.0.3.0
Oracle JDBC/OCI Instant Client                                       11.2.0.3.0
Oracle JDBC/THIN Interfaces                                          11.2.0.3.0
Oracle JFC Extended Windowing Toolkit                                4.2.36.0.0
Oracle JVM                                                           11.2.0.3.0
Oracle Label Security                                                11.2.0.3.0
Oracle LDAP administration                                           11.2.0.3.0
Oracle Locale Builder                                                11.2.0.3.0
Oracle Message Gateway Common Files                                  11.2.0.3.0
Oracle Multimedia                                                    11.2.0.3.0
Oracle Multimedia Annotator                                          11.2.0.3.0
Oracle Multimedia Client Option                                      11.2.0.3.0
Oracle Multimedia Java Advanced Imaging                              11.2.0.3.0
Oracle Multimedia Locator                                            11.2.0.3.0
Oracle Multimedia Locator RDBMS Files                                11.2.0.3.0
Oracle Net                                                           11.2.0.3.0
Oracle Net Listener                                                  11.2.0.3.0
Oracle Net Required Support Files                                    11.2.0.3.0
Oracle Net Services                                                  11.2.0.3.0
Oracle Netca Client                                                  11.2.0.3.0
Oracle Notification Service                                          11.2.0.3.0
Oracle Notification Service (eONS)                                   11.2.0.3.0
Oracle ODBC Driver                                                   11.2.0.3.0
Oracle ODBC Driverfor Instant Client                                 11.2.0.3.0
Oracle OLAP                                                          11.2.0.3.0
Oracle OLAP API                                                      11.2.0.3.0
Oracle OLAP RDBMS Files                                              11.2.0.3.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Partitioning                                                  11.2.0.3.0
Oracle Programmer                                                    11.2.0.3.0
Oracle Quality of Service Management (Client)                        11.2.0.3.0
Oracle RAC Deconfiguration                                           11.2.0.3.0
Oracle RAC Required Support Files-HAS                                11.2.0.3.0
Oracle Real Application Testing                                      11.2.0.3.0
Oracle Recovery Manager                                              11.2.0.3.0
Oracle Security Developer Tools                                      11.2.0.3.0
Oracle Spatial                                                       11.2.0.3.0
Oracle SQL Developer                                                 11.2.0.3.0
Oracle Starter Database                                              11.2.0.3.0
Oracle Text                                                          11.2.0.3.0
Oracle Text Required Support Files                                   11.2.0.3.0
Oracle UIX                                                           2.2.24.6.0
Oracle Universal Connection Pool                                     11.2.0.3.0
Oracle Universal Installer                                           11.2.0.3.0
Oracle USM Deconfiguration                                           11.2.0.3.0
Oracle Wallet Manager                                                11.2.0.3.0
Oracle XML Development Kit                                           11.2.0.3.0
Oracle XML Query                                                     11.2.0.3.0
Parser Generator Required Support Files                              11.2.0.3.0
Perl Interpreter                                                     5.10.0.0.2
Perl Modules                                                         5.10.0.0.1
PL/SQL                                                               11.2.0.3.0
PL/SQL Embedded Gateway                                              11.2.0.3.0
Platform Required Support Files                                      11.2.0.3.0
Precompiler Common Files                                             11.2.0.3.0
Precompiler Required Support Files                                   11.2.0.3.0
Provisioning Advisor Framework                                       10.2.0.4.3
RDBMS Required Support Files                                         11.2.0.3.0
RDBMS Required Support Files for Instant Client                      11.2.0.3.0
RDBMS Required Support Files Runtime                                 11.2.0.3.0
regexp                                                                2.1.9.0.0
Required Support Files                                               11.2.0.3.0
Sample Schema Data                                                   11.2.0.3.0
Secure Socket Layer                                                  11.2.0.3.0
SQL*Plus                                                             11.2.0.3.0
SQL*Plus Files for Instant Client                                    11.2.0.3.0
SQL*Plus Required Support Files                                      11.2.0.3.0
SQLJ Runtime                                                         11.2.0.3.0
SSL Required Support Files for InstantClient                         11.2.0.3.0
Sun JDK                                                             1.5.0.30.03
XDK Required Support Files                                           11.2.0.3.0
XML Parser for Java                                                  11.2.0.3.0
XML Parser for Oracle JVM                                            11.2.0.3.0
There are 136 component(s) installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.



As Grid user,
a. go to $ORACLE_HOME/OPatch path and take a backup of all the files to a backup location in /home/grid/bkpopatchgrid/
b. Only the files need to be backed up and not the OPatch directory.
c. Now copy p6880880_112000_Linux-x86-64.zip the file and unzip it in $ORACLE_HOME directory.
d. Once the above steps done, the results should be as follows.


[grid@vm1 OPatch]$ ./opatch lsinv -detail oh $ORACLE_HOME
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-20_15-32-13PM_1.log

Lsinventory Output file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2014-01-20_15-32-13PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure                                           11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


Installed Components (88):

Agent Required Support Files                                         10.2.0.4.3
Assistant Common Files                                               11.2.0.3.0
Automatic Storage Management Assistant                               11.2.0.3.0
Bali Share                                                           1.1.18.0.0
Buildtools Common Files                                              11.2.0.3.0
Character Set Migration Utility                                      11.2.0.3.0
Cluster Ready Services Files                                         11.2.0.3.0
Cluster Verification Utility Common Files                            11.2.0.3.0
Cluster Verification Utility Files                                   11.2.0.3.0
Database SQL Scripts                                                 11.2.0.3.0
Deinstallation Tool                                                  11.2.0.3.0
Enterprise Manager Common Core Files                                 10.2.0.4.4
Enterprise Manager Common Files                                      10.2.0.4.3
Enterprise Manager plugin Common Files                               11.2.0.3.0
Expat libraries                                                       2.0.1.0.1
HAS Common Files                                                     11.2.0.3.0
HAS Files for DB                                                     11.2.0.3.0
Installation Common Files                                            11.2.0.3.0
Installation Plugin Files                                            11.2.0.3.0
Installer SDK Component                                              11.2.0.3.0
LDAP Required Support Files                                          11.2.0.3.0
OLAP SQL Scripts                                                     11.2.0.3.0
Oracle Clusterware RDBMS Files                                       11.2.0.3.0
Oracle Configuration Manager Deconfiguration                         10.3.1.0.0
Oracle Containers for Java                                           11.2.0.3.0
Oracle Core Required Support Files                                   11.2.0.3.0
Oracle Database 11g                                                  11.2.0.3.0
Oracle Database 11g Multimedia Files                                 11.2.0.3.0
Oracle Database Deconfiguration                                      11.2.0.3.0
Oracle Database User Interface                                       2.2.13.0.0
Oracle Database Utilities                                            11.2.0.3.0
Oracle DBCA Deconfiguration                                          11.2.0.3.0
Oracle Extended Windowing Toolkit                                    3.4.47.0.0
Oracle Globalization Support                                         11.2.0.3.0
Oracle Globalization Support                                         11.2.0.3.0
Oracle Grid Infrastructure                                           11.2.0.3.0
Oracle Help For Java                                                  4.2.9.0.0
Oracle Ice Browser                                                    5.2.3.6.0
Oracle Internet Directory Client                                     11.2.0.3.0
Oracle Java Client                                                   11.2.0.3.0
Oracle JDBC/OCI Instant Client                                       11.2.0.3.0
Oracle JDBC/THIN Interfaces                                          11.2.0.3.0
Oracle JFC Extended Windowing Toolkit                                4.2.36.0.0
Oracle JVM                                                           11.2.0.3.0
Oracle LDAP administration                                           11.2.0.3.0
Oracle Locale Builder                                                11.2.0.3.0
Oracle Multimedia                                                    11.2.0.3.0
Oracle Multimedia Client Option                                      11.2.0.3.0
Oracle Multimedia Java Advanced Imaging                              11.2.0.3.0
Oracle Multimedia Locator                                            11.2.0.3.0
Oracle Multimedia Locator RDBMS Files                                11.2.0.3.0
Oracle Net                                                           11.2.0.3.0
Oracle Net Listener                                                  11.2.0.3.0
Oracle Net Required Support Files                                    11.2.0.3.0
Oracle Netca Client                                                  11.2.0.3.0
Oracle Notification Service                                          11.2.0.3.0
Oracle Notification Service (eONS)                                   11.2.0.3.0
Oracle One-Off Patch Installer                                       11.2.0.1.7
Oracle Quality of Service Management (Client)                        11.2.0.3.0
Oracle Quality of Service Management (Server)                        11.2.0.3.0
Oracle RAC Deconfiguration                                           11.2.0.3.0
Oracle RAC Required Support Files-HAS                                11.2.0.3.0
Oracle Recovery Manager                                              11.2.0.3.0
Oracle Security Developer Tools                                      11.2.0.3.0
Oracle Text Required Support Files                                   11.2.0.3.0
Oracle Universal Installer                                           11.2.0.3.0
Oracle USM Deconfiguration                                           11.2.0.3.0
Oracle Wallet Manager                                                11.2.0.3.0
Parser Generator Required Support Files                              11.2.0.3.0
Perl Interpreter                                                     5.10.0.0.2
Perl Modules                                                         5.10.0.0.1
PL/SQL                                                               11.2.0.3.0
PL/SQL Embedded Gateway                                              11.2.0.3.0
Platform Required Support Files                                      11.2.0.3.0
Precompiler Required Support Files                                   11.2.0.3.0
RDBMS Required Support Files                                         11.2.0.3.0
RDBMS Required Support Files for Instant Client                      11.2.0.3.0
RDBMS Required Support Files Runtime                                 11.2.0.3.0
Required Support Files                                               11.2.0.3.0
Secure Socket Layer                                                  11.2.0.3.0
SQL*Plus                                                             11.2.0.3.0
SQL*Plus Files for Instant Client                                    11.2.0.3.0
SQL*Plus Required Support Files                                      11.2.0.3.0
SSL Required Support Files for InstantClient                         11.2.0.3.0
Sun JDK                                                             1.5.0.30.03
Universal Storage Manager Files                                      11.2.0.3.0
XDK Required Support Files                                           11.2.0.3.0
XML Parser for Java                                                  11.2.0.3.0
There are 88 component(s) installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


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

OPatch succeeded.



3. Next, create OCM.rsp

( Response file for Oracle Configuration Manager -
The Oracle Configuration Manager (OCM) is a tool which proactively monitor the customers Oracle environment and provide
this information to My Oracle Support (MOS). The Data collected from the customer environment is uploaded to Oracle Customer
Configuration Repository and replicates this to My Oracle Support so that customer/support can view it. Additinaly MOS analyzes
the Data collected and  informs respective customer about alerts and advise health checks.)


a. Under Grid user, create ocm.rsp file

[grid@vm1 OPatch]$ cd /u01/app/grid/product/11.2.0/grid/OPatch/ocm/bin
[grid@vm1 bin]$ ls -ltrh
total 12K
-rwxr----- 1 grid oinstall 8.9K Nov 27  2009 emocmrsp
[grid@vm1 bin]$ ./emocmrsp
OCM Installation Response Generator 10.3.4.0.0 - Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates.  All rights reserved.

Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (ocm.rsp) was successfully created.
[grid@vm1 bin]$


As Oracle user, create ocm.rsp file

[oracle@vm1 bin]$ ./emocmrsp
OCM Installation Response Generator 10.3.4.0.0 - Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates.  All rights reserved.

Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y
The OCM configuration response file (ocm.rsp) was successfully created.
[oracle@vm1 bin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin

4. Stop the database home on the node.

a. Login as oracle user
b. [oracle@vm1 ~]$ srvctl status database -d orcl
Database is running.
[oracle@vm1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1
c. [oracle@vm1 ~]$ srvctl stop home -o $ORACLE_HOME -s /tmp/statfiledb.out
[oracle@vm1 ~]$ cat /tmp/statfiledb.out
db-orcl
d. [oracle@vm1 ~]$ sysresv

IPC Resources for ORACLE_SID "orcl" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "orcl"

e. [oracle@vm1 ~]$ ps -ef |grep pmon
grid      6437     1  0 12:07 ?        00:00:03 asm_pmon_+ASM
oracle   16113 13667  0 15:59 pts/1    00:00:00 grep pmon
[oracle@vm1 ~]$ srvctl status database -d orcl
Database is not running.


5. As both Oracle and Grid user, copy the file p17735354_112030_Linux-x86-64.zip in /home/oracle/ and /home/grid directories
g. Unzip the zipped files.

[grid@vm1 ~]$ ls -ltrh
total 571M
drwxr-xr-x  3 grid oinstall 4.0K Dec 20 14:55 installables
drwxr-xr-x  3 grid oinstall 4.0K Dec 20 15:30 oradiag_grid
drwxr-xr-x  2 root root     4.0K Dec 23 17:25 bkp
-rw-r-----  1 grid oinstall 2.1M Dec 23 17:40 CORRUPT_TST.272.834899393
-rw-r--r--  1 grid oinstall   21 Jan  9 19:27 README.txt
-rw-r--r--  1 grid oinstall  450 Jan  9 19:27 bundle.xml
-rw-r--r--  1 grid oinstall    0 Jan  9 19:27 atp_lfp
drwxr-xr-x  5 grid oinstall 4.0K Jan  9 19:27 17592127
drwxrwxr-x 11 grid oinstall 4.0K Jan  9 19:27 17540582
-rw-rw-r--  1 grid oinstall  59K Jan 17 10:16 README.html
drwxr-xr-x  7 grid oinstall 4.0K Jan 20 14:54 bkpopatchgrid
-rw-r--r--  1 grid oinstall 569M Jan 20 15:05 p17735354_112030_Linux-x86-64.zip

[oracle@vm1 ~]$ ls -ltrh
total 100K
-rw-r--r--  1 grid   oinstall  89K Dec 23 17:16 CORRUPT_TST.272.834899393
-rw-r--r--  1 oracle oinstall   21 Jan  9 19:27 README.txt
-rw-r--r--  1 oracle oinstall  450 Jan  9 19:27 bundle.xml
-rw-r--r--  1 oracle oinstall    0 Jan  9 19:27 atp_lfp
drwxr-xr-x  5 oracle oinstall 4.0K Jan  9 19:27 17592127
drwxrwxr-x 11 oracle oinstall 4.0K Jan  9 19:27 17540582
-rw-rw-r--  1 oracle oinstall  59K Jan 17 10:16 README.html
drwxr-xr-x  2 oracle oinstall 4.0K Jan 20 12:24 Desktop
drwxr-xr-x  7 oracle oinstall 4.0K Jan 20 14:27 bkpopatch
drwxr-xr-x  2 oracle oinstall 4.0K Jan 21 23:00 backup


6.  Save database home configuration

gi_psu_patch_no/custom/server/gi_psu_patch_no/custom/scripts/prepatch.sh -dbhome <ORACLE_HOME>

Under ORacle user.

/home/oracle/17592127/custom/server/17592127/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1

[oracle@vm1 ~]$ /home/oracle/17592127/custom/server/17592127/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.2.0/dbhome_1
/home/oracle/17592127/custom/server/17592127/custom/scripts/prepatch.sh completed successfully.



7. Stop GI and unlock GI home.

[grid@vm1 17592127]$ srvctl stop asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg', but the force option was not specified



As Grid user,
[grid@vm1 17592127]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'vm1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'vm1'
CRS-2673: Attempting to stop 'ora.LISTENERASM.lsnr' on 'vm1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'vm1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'vm1' succeeded
CRS-2677: Stop of 'ora.LISTENERASM.lsnr' on 'vm1' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'vm1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'vm1'
CRS-2677: Stop of 'ora.asm' on 'vm1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'vm1'
CRS-2677: Stop of 'ora.cssd' on 'vm1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'vm1'
CRS-2677: Stop of 'ora.evmd' on 'vm1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'vm1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[grid@vm1 17592127]$


As root user , execute below commands to unlock GI home . Run the below from $ORACLE_HOME of GRID user
[root@vm1 ~]# cd /u01/app/grid/product/11.2.0/grid
[root@vm1 grid]# cd crs/install
[root@vm1 install]# pwd
/u01/app/grid/product/11.2.0/grid/crs/install

[root@vm1 install]# ./roothas.pl -unlock
Using configuration parameter file: ./crsconfig_params
Successfully unlock /u01/app/grid/product/11.2.0/grid
[root@vm1 install]#



8. Apply GI_PSU Patch for Grid Home

As Grid user.

[grid@vm1 bin]$ echo $ORACLE_HOME
/u01/app/grid/product/11.2.0/grid



$ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -ocmrf /u01/app/grid/product/11.2.0/grid/OPatch/ocm/bin/ocm.rsp

[grid@vm1 ~]$ cd 17592127
[grid@vm1 17592127]$ ls -ltrh
total 12K
drwxr-xr-x  4 grid oinstall 4.0K Jan  9 19:27 etc
drwxr-xr-x  4 grid oinstall 4.0K Jan  9 19:27 custom
drwxr-xr-x 17 grid oinstall 4.0K Jan  9 19:27 files
[grid@vm1 17592127]$ pwd
/home/grid/17592127
[grid@vm1 17592127]$ $ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -ocmrf /u01/app/grid/product/11.2.0/grid/OPatch/ocm/bin/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-20_16-38-42PM_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckSystemSpace" failed.
The details are:
Required amount of space(6601.28MB) is not available.
UtilSession failed:
Prerequisite check "CheckSystemSpace" failed.
Log file location: /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-20_16-38-42PM_1.log

OPatch failed with error code 73

(There was no enough space on the mount point where oracle binaries are hosted - So added the space ) and then continued.

[grid@vm1 17592127]$ $ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -ocmrf /u01/app/grid/product/11.2.0/grid/OPatch/ocm/bin/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-22_09-56-27AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17592127

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/grid/product/11.2.0/grid')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '17592127' to OH '/u01/app/grid/product/11.2.0/grid'

Patching component oracle.crs, 11.2.0.3.0...

Patching component oracle.usm, 11.2.0.3.0...

Verifying the update...
Patch 17592127 successfully applied.
Log file location: /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-22_09-56-27AM_1.log

OPatch succeeded.

9. Validating the patch for GRID

[grid@vm1 17592127]$ cd /u01/app/grid/product/11.2.0/grid/OPatch/
[grid@vm1 OPatch]$ ./opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-22_10-10-12AM_1.log

Lsinventory Output file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2014-01-22_10-10-12AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure                                           11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (1) :

Patch  17592127     : applied on Wed Jan 22 10:02:26 GST 2014
Unique Patch ID:  16995168
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.9 (HAS Components)"
   Created on 9 Jan 2014, 07:26:53 hrs PST8PDT
   Bugs fixed:
     17592127, 17076717, 16619898, 16315641, 15876003, 14275572, 13919095
     13696251, 13348650, 12659561, 16562733, 14305980, 14277586, 13729298
     13987807, 14625969, 16629075, 13825231, 12794268, 13000491, 13498267
     15934834, 11675721, 14082976, 12771830, 13906374, 14515980, 14085018
     13943175, 14102704, 14171552, 12594616, 13879428, 12897902, 12726222
     12829429, 13079948, 13991403, 13090686, 12995950, 13251796, 13582411
     14651272, 12903592, 12990582, 13857364, 16747308, 15856610, 13082238
     16446410, 12947871, 13256955, 13037709, 14535011, 12878750, 16560359
     14048512, 11772838, 13058611, 13001955, 17272731, 13440962, 13727853
     13425727, 12885323, 12870400, 16434983, 14212634, 14407395, 13332363
     14761411, 13430626, 16346413, 14456069, 13811209, 12709476, 14168708
     14096821, 14626717, 13460353, 16206997, 13694885, 12857064, 12899169
     13111013, 12558569, 13323698, 16547309, 10260842, 16613232, 13085732
     16555186, 14332688, 12928658, 10317921, 17172091, 16077216, 13869978
     12914824, 13789135, 12730342, 12950823, 13355963, 13531373, 14268365
     13776758, 12720728, 13620816, 14628188, 13023609, 15874565, 16578706
     17405302, 16735171, 13024624, 13039908, 14525998, 14385860, 15911134
     13036424, 13938166, 15907617, 13011520, 13569812, 12758736, 13001901
     13077654, 13430715, 13550689, 13251609, 13806545, 13634583, 14271305
     12538907, 13947200, 12996428, 13066371, 17043601, 13483672, 13936066
     12897651, 13540563, 12896850, 13241779, 12728585, 12876314, 12925041
     12650672, 12398492, 12848480, 13088512, 13652088, 14226134, 16307750
     12917897, 12975811, 13653178, 17247404, 13371153, 14800989, 16684285
     10114953, 14001941, 11836951, 14179376, 12965049, 14773530, 12765467
     16821005, 12950415, 15998768, 13339443, 13965075, 16210540, 14307855
     12784559, 14242977, 13955385, 12704789, 16206882, 13745317, 16909409
     17305100, 15832129, 13074261, 12971251, 13993634, 13523527, 16876500
     13719731, 13396284, 12639013, 12867511, 13989181, 12959140, 16836877
     14748254, 13912274, 12829917, 12349553, 12849377, 12934171, 13843080
     14496536, 13899736, 13924431, 12680491, 13334158, 15869775, 10418841
     12832204, 13838047, 13002015, 14639430, 11822565, 15920201, 12791719
     13886023, 13821454, 12782756, 14100232, 14186070, 14569263, 12873909
     13845120, 14214257, 12914722, 12842804, 12772345, 12663376, 14059576
     16170117, 13889047, 12695029, 13924910, 13146560, 14070200, 13820621
     14304758, 14382785, 12996572, 13941934, 14711358, 13019958, 13888719
     16463033, 12823838, 13877508, 12983005, 12823042, 16436434, 14494305
     13582706, 13617861, 12825835, 13025879, 13853089, 13410987, 13050800
     13570879, 13247273, 13255295, 14152875, 13912373, 13011182, 13243172
     15978267, 13045518, 12765868, 11825850, 15986571, 13345868, 13683090
     12932852, 13038806, 14588629, 14251904, 13396356, 13697828, 12834777
     13258062, 14371335, 13657366, 12810890, 15917085, 13502441, 14637577
     13880925, 13726162, 14153867, 13506114, 16022372, 12820045, 13604057
     13506408, 13542331, 13263435, 15936101, 14009845, 12827493, 13637590, 13068077



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

OPatch succeeded.
[grid@vm1 OPatch]$


11. Apply DB_PSU patch for GI home
As the GI home owner execute:
$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/<DB_PSU_number>

/u01/app/grid/product/11.2.0/grid/OPatch/opatch apply -oh /u01/app/grid/product/11.2.0/grid/ -local /home/grid/17540582

[grid@vm1 17540582]$ pwd
/home/grid/17540582
[grid@vm1 17540582]$ /u01/app/grid/product/11.2.0/grid/OPatch/opatch apply -oh /u01/app/grid/product/11.2.0/grid/ -local /home/grid/17540582
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid//oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-22_11-45-23AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   13343438  13696216  13923374  14275605  14727310  16056266  16619892  16902043  17540582

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  y



Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/grid/product/11.2.0/grid')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '13343438' to OH '/u01/app/grid/product/11.2.0/grid'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '13696216' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.sysman.console.db, 11.2.0.3.0 ] , [ oracle.sysman.oms.core, 10.2.0.4.4 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '13923374' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.sysman.console.db, 11.2.0.3.0 ] , [ oracle.network.cman, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.network.listener, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '14275605' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.precomp.common, 11.2.0.3.0 ] , [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.client, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '14727310' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.sysman.console.db, 11.2.0.3.0 ] , [ oracle.sysman.oms.core, 10.2.0.4.4 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '16056266' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ] , [ oracle.ovm, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.listener, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.rdbms.deconfig, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '16619892' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.marvel, 11.2.0.3.0 ] , [ oracle.precomp.common, 11.2.0.3.0 ] , [ oracle.precomp.lang, 11.2.0.3.0 ] , [ oracle.sysman.agent, 10.2.0.4.3 ] , [ oracle.sysman.console.db, 11.2.0.3.0 ] , [ oracle.sysman.repository.core, 10.2.0.4.4 ] , [ oracle.xdk, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.xdk.parser.java, 11.2.0.3.0...

Patching component oracle.xdk.rsf, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '16902043' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.idm.oid, 11.2.0.3.0 ] , [ oracle.owb.rsf, 11.2.0.3.0 ] , [ oracle.sysman.console.db, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '17540582' to OH '/u01/app/grid/product/11.2.0/grid'
ApplySession: Optional component(s) [ oracle.sdo, 11.2.0.3.0 ] , [ oracle.precomp.common, 11.2.0.3.0 ] , [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.ordim.client, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.ordim.jai, 11.2.0.3.0...

Verifying the update...
Composite patch 17540582 successfully applied.
Log file location: /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-22_11-45-23AM_1.log

OPatch succeeded.



12. Validating the dB_PSU for GI home

[grid@vm1 OPatch]$ ./opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/grid/product/11.2.0/grid
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/grid/product/11.2.0/grid/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/opatch2014-01-22_12-05-37PM_1.log

Lsinventory Output file location : /u01/app/grid/product/11.2.0/grid/cfgtoollogs/opatch/lsinv/lsinventory2014-01-22_12-05-37PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Grid Infrastructure                                           11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (2) :

Patch  17540582     : applied on Wed Jan 22 11:59:31 GST 2014
Unique Patch ID:  16954971
Patch description:  "Database Patch Set Update : 11.2.0.3.9 (17540582)"
   Created on 7 Jan 2014, 02:20:21 hrs PST8PDT
Sub-patch  16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch  16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
   Bugs fixed:
     13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
     13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
     14409183, 13492735, 12857027, 13496884, 14263036, 14263073, 13015379
     16038929, 17748833, 16563678, 13732226, 13866822, 13742434, 13944971
     12950644, 17748831, 12899768, 13063120, 13958038, 14613900, 13972394
     11877623, 17088068, 13072654, 12395918, 13814739, 17343514, 13649031
     13981051, 12797765, 17333200, 12923168, 16761566, 16279401, 13384182
     13466801, 15996344, 14207163, 13724193, 13642044, 11063191, 13945708
     12797420, 12865902, 15869211, 13041324, 14003090, 16314468, 16019955
     11708510, 14637368, 13026410, 13737746, 13742438, 15841373, 16347904
     15910002, 16362358, 14398795, 13579992, 16344871, 10400244, 14275605
     13742436, 9858539, 14841812, 16338983, 9703627, 13483354, 14207317
     14393728, 12764337, 16902043, 14459552, 14191508, 12964067, 12780983
     12583611, 14383007, 14546575, 15862016, 13476583, 13489024, 17748830
     14088346, 13448206, 16314466, 13419660, 14110275, 13430938, 13467683
     14548763, 12834027, 13632809, 13377816, 13036331, 14727310, 16175381
     13584130, 12829021, 15862019, 12794305, 14546673, 12791981, 13787482
     13503598, 10133521, 12744759, 13399435, 13553883, 14023636, 14762511
     9095696, 14343501, 13860201, 13257247, 14176879, 16014985, 12312133
     14480675, 16306019, 13559697, 9706792, 12974860, 12940620, 13098318
     13773133, 15883525, 16794244, 13340388, 13366202, 13528551, 12894807
     12747437, 13454210, 12748240, 13385346, 15987992, 13923995, 13582702
     14571027, 12784406, 13907462, 13493847, 13857111, 13035804, 16710363
     13544396, 14128555, 8547978, 14226599, 17478415, 17333197, 9397635
     14007968, 12925089, 12693626, 14189694, 12815057, 17761775, 16721594
     13332439, 14038787, 11071989, 14207902, 14062796, 12913474, 14390252
     16314470, 13370330, 14062794, 13358781, 17333202, 12960925, 9659614
     14546638, 13699124, 13936424, 9797851, 14301592, 16794240, 13338048
     12938841, 12620823, 12656535, 12678920, 14488943, 16850197, 14791477
     14062792, 13807411, 16794238, 15862022, 12594032, 13250244, 9761357
     12612118, 14053457, 13527323, 10625145, 15862020, 13910420, 12780098
     13696216, 10263668, 14841558, 16794242, 16944698, 15862023, 16056266
     13834065, 14351566, 13723052, 13011409, 14063280, 13566938, 13737888
     13624984, 16024441, 17333199, 13914613, 17540582, 14258925, 14222403
     14755945, 13645875, 12571991, 14664355, 12998795, 13719081, 14469008
     14188650, 17019974, 13742433, 16368108, 16314469, 12905058, 6690853
     16212405, 12849688, 13742435, 13464002, 13534412, 12879027, 12585543
     13790109, 12535346, 16382448, 12588744, 13916549, 13786142, 12847466
     13855490, 13551402, 12582664, 14262913, 17332800, 14695377, 12912137
     13612575, 13484963, 14163397, 17437634, 13772618, 16694777, 13070939
     14369664, 12391034, 13605839, 16314467, 16279211, 12976376, 12755231
     13680405, 14589750, 13742437, 14318397, 11868640, 14644185, 13326736
     13596521, 13001379, 12898558, 17752121, 13099577, 9873405, 16372203
     16344758, 11715084, 16231699, 9547706, 14040433, 12662040, 12617123
     17748832, 16530565, 12845115, 16844086, 17748834, 13354082, 13397104
     13913630, 16462834, 12983611, 13550185, 13810393, 14121009, 13065099
     11840910, 13903046, 15862017, 13572659, 16294378, 13718279, 13657605
     14480676, 13632717, 14668670, 14063281, 13420224, 13812031, 16299830
     12646784, 14512189, 12755116, 13616375, 17230530, 14035825, 13427062
     12861463, 13092220, 15862021, 13043012, 16619892, 13685544, 15862018
     13499128, 13561750, 12718090, 13848402, 13725395, 12401111, 12796518
     13362079, 12917230, 13042639, 13923374, 14220725, 12621588, 13524899
     14751895, 14480674, 13916709, 14076523, 15905421, 12731940, 13343438
     14205448, 17748835, 14127231, 17082364, 15853081, 14273397, 16844448
     14467061, 12971775, 16864562, 14497307, 12748538, 10242202, 14230270
     16382353, 13686047, 14095982, 17333203, 13591624, 14523004, 13440516
     16794241, 14062795, 13035360, 13040943, 13843646, 16794243, 14841409
     13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
     16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
     14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
     13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404

Patch  17592127     : applied on Wed Jan 22 10:02:26 GST 2014
Unique Patch ID:  16995168
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.9 (HAS Components)"
   Created on 9 Jan 2014, 07:26:53 hrs PST8PDT
   Bugs fixed:
     17592127, 17076717, 16619898, 16315641, 15876003, 14275572, 13919095
     13696251, 13348650, 12659561, 16562733, 14305980, 14277586, 13729298
     13987807, 14625969, 16629075, 13825231, 12794268, 13000491, 13498267
     15934834, 11675721, 14082976, 12771830, 13906374, 14515980, 14085018
     13943175, 14102704, 14171552, 12594616, 13879428, 12897902, 12726222
     12829429, 13079948, 13991403, 13090686, 12995950, 13251796, 13582411
     14651272, 12903592, 12990582, 13857364, 16747308, 15856610, 13082238
     16446410, 12947871, 13256955, 13037709, 14535011, 12878750, 16560359
     14048512, 11772838, 13058611, 13001955, 17272731, 13440962, 13727853
     13425727, 12885323, 12870400, 16434983, 14212634, 14407395, 13332363
     14761411, 13430626, 16346413, 14456069, 13811209, 12709476, 14168708
     14096821, 14626717, 13460353, 16206997, 13694885, 12857064, 12899169
     13111013, 12558569, 13323698, 16547309, 10260842, 16613232, 13085732
     16555186, 14332688, 12928658, 10317921, 17172091, 16077216, 13869978
     12914824, 13789135, 12730342, 12950823, 13355963, 13531373, 14268365
     13776758, 12720728, 13620816, 14628188, 13023609, 15874565, 16578706
     17405302, 16735171, 13024624, 13039908, 14525998, 14385860, 15911134
     13036424, 13938166, 15907617, 13011520, 13569812, 12758736, 13001901
     13077654, 13430715, 13550689, 13251609, 13806545, 13634583, 14271305
     12538907, 13947200, 12996428, 13066371, 17043601, 13483672, 13936066
     12897651, 13540563, 12896850, 13241779, 12728585, 12876314, 12925041
     12650672, 12398492, 12848480, 13088512, 13652088, 14226134, 16307750
     12917897, 12975811, 13653178, 17247404, 13371153, 14800989, 16684285
     10114953, 14001941, 11836951, 14179376, 12965049, 14773530, 12765467
     16821005, 12950415, 15998768, 13339443, 13965075, 16210540, 14307855
     12784559, 14242977, 13955385, 12704789, 16206882, 13745317, 16909409
     17305100, 15832129, 13074261, 12971251, 13993634, 13523527, 16876500
     13719731, 13396284, 12639013, 12867511, 13989181, 12959140, 16836877
     14748254, 13912274, 12829917, 12349553, 12849377, 12934171, 13843080
     14496536, 13899736, 13924431, 12680491, 13334158, 15869775, 10418841
     12832204, 13838047, 13002015, 14639430, 11822565, 15920201, 12791719
     13886023, 13821454, 12782756, 14100232, 14186070, 14569263, 12873909
     13845120, 14214257, 12914722, 12842804, 12772345, 12663376, 14059576
     16170117, 13889047, 12695029, 13924910, 13146560, 14070200, 13820621
     14304758, 14382785, 12996572, 13941934, 14711358, 13019958, 13888719
     16463033, 12823838, 13877508, 12983005, 12823042, 16436434, 14494305
     13582706, 13617861, 12825835, 13025879, 13853089, 13410987, 13050800
     13570879, 13247273, 13255295, 14152875, 13912373, 13011182, 13243172
     15978267, 13045518, 12765868, 11825850, 15986571, 13345868, 13683090
     12932852, 13038806, 14588629, 14251904, 13396356, 13697828, 12834777
     13258062, 14371335, 13657366, 12810890, 15917085, 13502441, 14637577
     13880925, 13726162, 14153867, 13506114, 16022372, 12820045, 13604057
     13506408, 13542331, 13263435, 15936101, 14009845, 12827493, 13637590, 13068077



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

OPatch succeeded.


13. Applying GI_PSU patch for database home.

a.Login as oracle user.
b. Go the directory with patch number for GI
c. opatch napply gi_psu_patch_no/custom/server/ -oh <ORACLE_HOME> -id gi_psu_patch_no -ocmrf

[oracle@vm1 17592127]$ pwd
/home/oracle/17592127
[oracle@vm1 server]$ pwd
/home/oracle/17592127/custom/server

[oracle@vm1 17592127]$ $ORACLE_HOME/OPatch/opatch napply 17592127/custom/server -oh /u01/app/oracle/product/11.2.0/dbhome_1 -id 17592127 -ocmrf /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/ocm.rsp

[oracle@vm1 17592127]$ $ORACLE_HOME/OPatch/opatch napply 17592127/custom/server -oh /u01/app/oracle/product/11.2.0/dbhome_1 -id 17592127 -ocmrf /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-22_10-49-53AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   17592127

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '17592127' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms, 11.2.0.3.0...

Verifying the update...
Patch 17592127 successfully applied.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-22_10-49-53AM_1.log

OPatch succeeded.

14. Validate the GI_PSU for DB home

[oracle@vm1 17592127]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/
[oracle@vm1 OPatch]$ opatch lsinv
-bash: opatch: command not found
[oracle@vm1 OPatch]$ ./opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-22_10-53-30AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-01-22_10-53-30AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (1) :

Patch  17592127     : applied on Wed Jan 22 10:50:52 GST 2014
Unique Patch ID:  16995168
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.9 (HAS Components)"
   Created on 9 Jan 2014, 07:26:53 hrs PST8PDT
   Bugs fixed:
     17592127, 17076717, 16619898, 16315641, 15876003, 14275572, 13919095
     13696251, 13348650, 12659561, 16562733, 14305980, 14277586, 13729298
     13987807, 14625969, 16629075, 13825231, 12794268, 13000491, 13498267
     15934834, 11675721, 14082976, 12771830, 13906374, 14515980, 14085018
     13943175, 14102704, 14171552, 12594616, 13879428, 12897902, 12726222
     12829429, 13079948, 13991403, 13090686, 12995950, 13251796, 13582411
     14651272, 12903592, 12990582, 13857364, 16747308, 15856610, 13082238
     16446410, 12947871, 13256955, 13037709, 14535011, 12878750, 16560359
     14048512, 11772838, 13058611, 13001955, 17272731, 13440962, 13727853
     13425727, 12885323, 12870400, 16434983, 14212634, 14407395, 13332363
     14761411, 13430626, 16346413, 14456069, 13811209, 12709476, 14168708
     14096821, 14626717, 13460353, 16206997, 13694885, 12857064, 12899169
     13111013, 12558569, 13323698, 16547309, 10260842, 16613232, 13085732
     16555186, 14332688, 12928658, 10317921, 17172091, 16077216, 13869978
     12914824, 13789135, 12730342, 12950823, 13355963, 13531373, 14268365
     13776758, 12720728, 13620816, 14628188, 13023609, 15874565, 16578706
     17405302, 16735171, 13024624, 13039908, 14525998, 14385860, 15911134
     13036424, 13938166, 15907617, 13011520, 13569812, 12758736, 13001901
     13077654, 13430715, 13550689, 13251609, 13806545, 13634583, 14271305
     12538907, 13947200, 12996428, 13066371, 17043601, 13483672, 13936066
     12897651, 13540563, 12896850, 13241779, 12728585, 12876314, 12925041
     12650672, 12398492, 12848480, 13088512, 13652088, 14226134, 16307750
     12917897, 12975811, 13653178, 17247404, 13371153, 14800989, 16684285
     10114953, 14001941, 11836951, 14179376, 12965049, 14773530, 12765467
     16821005, 12950415, 15998768, 13339443, 13965075, 16210540, 14307855
     12784559, 14242977, 13955385, 12704789, 16206882, 13745317, 16909409
     17305100, 15832129, 13074261, 12971251, 13993634, 13523527, 16876500
     13719731, 13396284, 12639013, 12867511, 13989181, 12959140, 16836877
     14748254, 13912274, 12829917, 12349553, 12849377, 12934171, 13843080
     14496536, 13899736, 13924431, 12680491, 13334158, 15869775, 10418841
     12832204, 13838047, 13002015, 14639430, 11822565, 15920201, 12791719



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

OPatch succeeded.
[oracle@vm1 OPatch]$


15.  Applying DB_PSU for database home.


 <ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <UNZIPPED_PATCH_LOCATION>/<DB_PSU_number> -ocmrf /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/ocm.rsp


$ORACLE_HOME/OPatch/opatch apply -local -oh $ORACLE_HOME /home/oracle/17540582 -ocmrf /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/ocm.rsp

[oracle@vm1 ~]$ pwd
/home/oracle
[oracle@vm1 ~]$ $ORACLE_HOME/OPatch/opatch apply -local -oh $ORACLE_HOME /home/oracle/17540582 -ocmrf /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-22_11-16-36AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   13343438  13696216  13923374  14275605  14727310  16056266  16619892  16902043  17540582

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2.0/dbhome_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '13343438' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '13696216' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.4...

Verifying the update...
Applying sub-patch '13923374' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.network.listener, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '14275605' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.client, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '14727310' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Patching component oracle.sysman.oms.core, 10.2.0.4.4...

Verifying the update...
Applying sub-patch '16056266' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.network.listener, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.ovm, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.rdbms.deconfig, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '16619892' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.marvel, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sysman.agent, 10.2.0.4.3...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Patching component oracle.sysman.repository.core, 10.2.0.4.4...

Patching component oracle.xdk, 11.2.0.3.0...

Patching component oracle.xdk.parser.java, 11.2.0.3.0...

Patching component oracle.xdk.rsf, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '16902043' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.idm.oid, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...

Patching component oracle.owb.rsf, 11.2.0.3.0...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Verifying the update...
Applying sub-patch '17540582' to OH '/u01/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.sdo, 11.2.0.3.0...

Patching component oracle.ldap.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.ordim.client, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.sdo.locator, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.ordim.jai, 11.2.0.3.0...

Verifying the update...

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0/dbhome_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0/dbhome_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/u01/app/oracle/product/11.2.0/dbhome_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'



OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'


Composite patch 17540582 successfully applied.
OPatch Session completed with warnings.
Log file location: /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-22_11-16-36AM_1.log

OPatch completed with warnings.


16. Validating the DB_PSU for Db home

[oracle@vm1 OPatch]$ ./opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-01-22_12-06-27PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-01-22_12-06-27PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (2) :

Patch  17540582     : applied on Wed Jan 22 11:40:38 GST 2014
Unique Patch ID:  16954971
Patch description:  "Database Patch Set Update : 11.2.0.3.9 (17540582)"
   Created on 7 Jan 2014, 02:20:21 hrs PST8PDT
Sub-patch  16902043; "Database Patch Set Update : 11.2.0.3.8 (16902043)"
Sub-patch  16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)"
Sub-patch  16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)"
Sub-patch  14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)"
Sub-patch  14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
   Bugs fixed:
     13593999, 10350832, 14138130, 12919564, 13561951, 14198511, 13588248
     13080778, 13804294, 16710324, 12873183, 14472647, 12880299, 13369579
     14409183, 13492735, 12857027, 13496884, 14263036, 14263073, 13015379
     16038929, 17748833, 16563678, 13732226, 13866822, 13742434, 13944971
     12950644, 17748831, 12899768, 13063120, 13958038, 14613900, 13972394
     11877623, 17088068, 13072654, 12395918, 13814739, 17343514, 13649031
     13981051, 12797765, 17333200, 12923168, 16761566, 16279401, 13384182
     13466801, 15996344, 14207163, 13724193, 13642044, 11063191, 13945708
     12797420, 12865902, 15869211, 13041324, 14003090, 16314468, 16019955
     11708510, 14637368, 13026410, 13737746, 13742438, 15841373, 16347904
     15910002, 16362358, 14398795, 13579992, 16344871, 10400244, 14275605
     13742436, 9858539, 14841812, 16338983, 9703627, 13483354, 14207317
     14393728, 12764337, 16902043, 14459552, 14191508, 12964067, 12780983
     12583611, 14383007, 14546575, 15862016, 13476583, 13489024, 17748830
     14088346, 13448206, 16314466, 13419660, 14110275, 13430938, 13467683
     14548763, 12834027, 13632809, 13377816, 13036331, 14727310, 16175381
     13584130, 12829021, 15862019, 12794305, 14546673, 12791981, 13787482
     13503598, 10133521, 12744759, 13399435, 13553883, 14023636, 14762511
     9095696, 14343501, 13860201, 13257247, 14176879, 16014985, 12312133
     14480675, 16306019, 13559697, 9706792, 12974860, 12940620, 13098318
     13773133, 15883525, 16794244, 13340388, 13366202, 13528551, 12894807
     12747437, 13454210, 12748240, 13385346, 15987992, 13923995, 13582702
     14571027, 12784406, 13907462, 13493847, 13857111, 13035804, 16710363
     13544396, 14128555, 8547978, 14226599, 17478415, 17333197, 9397635
     14007968, 12925089, 12693626, 14189694, 12815057, 17761775, 16721594
     13332439, 14038787, 11071989, 14207902, 14062796, 12913474, 14390252
     16314470, 13370330, 14062794, 13358781, 17333202, 12960925, 9659614
     14546638, 13699124, 13936424, 9797851, 14301592, 16794240, 13338048
     12938841, 12620823, 12656535, 12678920, 14488943, 16850197, 14791477
     14062792, 13807411, 16794238, 15862022, 12594032, 13250244, 9761357
     12612118, 14053457, 13527323, 10625145, 15862020, 13910420, 12780098
     13696216, 10263668, 14841558, 16794242, 16944698, 15862023, 16056266
     13834065, 14351566, 13723052, 13011409, 14063280, 13566938, 13737888
     13624984, 16024441, 17333199, 13914613, 17540582, 14258925, 14222403
     14755945, 13645875, 12571991, 14664355, 12998795, 13719081, 14469008
     14188650, 17019974, 13742433, 16368108, 16314469, 12905058, 6690853
     16212405, 12849688, 13742435, 13464002, 13534412, 12879027, 12585543
     13790109, 12535346, 16382448, 12588744, 13916549, 13786142, 12847466
     13855490, 13551402, 12582664, 14262913, 17332800, 14695377, 12912137
     13612575, 13484963, 14163397, 17437634, 13772618, 16694777, 13070939
     14369664, 12391034, 13605839, 16314467, 16279211, 12976376, 12755231
     13680405, 14589750, 13742437, 14318397, 11868640, 14644185, 13326736
     13596521, 13001379, 12898558, 17752121, 13099577, 9873405, 16372203
     16344758, 11715084, 16231699, 9547706, 14040433, 12662040, 12617123
     17748832, 16530565, 12845115, 16844086, 17748834, 13354082, 13397104
     13913630, 16462834, 12983611, 13550185, 13810393, 14121009, 13065099
     11840910, 13903046, 15862017, 13572659, 16294378, 13718279, 13657605
     14480676, 13632717, 14668670, 14063281, 13420224, 13812031, 16299830
     12646784, 14512189, 12755116, 13616375, 17230530, 14035825, 13427062
     12861463, 13092220, 15862021, 13043012, 16619892, 13685544, 15862018
     13499128, 13561750, 12718090, 13848402, 13725395, 12401111, 12796518
     13362079, 12917230, 13042639, 13923374, 14220725, 12621588, 13524899
     14751895, 14480674, 13916709, 14076523, 15905421, 12731940, 13343438
     14205448, 17748835, 14127231, 17082364, 15853081, 14273397, 16844448
     14467061, 12971775, 16864562, 14497307, 12748538, 10242202, 14230270
     16382353, 13686047, 14095982, 17333203, 13591624, 14523004, 13440516
     16794241, 14062795, 13035360, 13040943, 13843646, 16794243, 14841409
     13059165, 14062797, 12959852, 12345082, 16703112, 13890080, 17333198
     16450169, 12658411, 13780035, 14062793, 13038684, 16742095, 13742464
     14052474, 13060271, 13911821, 13457582, 7509451, 13791364, 12821418
     13502183, 13705338, 16794239, 15862024, 13554409, 13645917, 13103913, 12772404

Patch  17592127     : applied on Wed Jan 22 10:50:52 GST 2014
Unique Patch ID:  16995168
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.3.9 (HAS Components)"
   Created on 9 Jan 2014, 07:26:53 hrs PST8PDT
   Bugs fixed:
     17592127, 17076717, 16619898, 16315641, 15876003, 14275572, 13919095
     13696251, 13348650, 12659561, 16562733, 14305980, 14277586, 13729298
     13987807, 14625969, 16629075, 13825231, 12794268, 13000491, 13498267
     15934834, 11675721, 14082976, 12771830, 13906374, 14515980, 14085018
     13943175, 14102704, 14171552, 12594616, 13879428, 12897902, 12726222
     12829429, 13079948, 13991403, 13090686, 12995950, 13251796, 13582411
     14651272, 12903592, 12990582, 13857364, 16747308, 15856610, 13082238
     16446410, 12947871, 13256955, 13037709, 14535011, 12878750, 16560359
     14048512, 11772838, 13058611, 13001955, 17272731, 13440962, 13727853
     13425727, 12885323, 12870400, 16434983, 14212634, 14407395, 13332363
     14761411, 13430626, 16346413, 14456069, 13811209, 12709476, 14168708
     14096821, 14626717, 13460353, 16206997, 13694885, 12857064, 12899169
     13111013, 12558569, 13323698, 16547309, 10260842, 16613232, 13085732
     16555186, 14332688, 12928658, 10317921, 17172091, 16077216, 13869978
     12914824, 13789135, 12730342, 12950823, 13355963, 13531373, 14268365
     13776758, 12720728, 13620816, 14628188, 13023609, 15874565, 16578706
     17405302, 16735171, 13024624, 13039908, 14525998, 14385860, 15911134
     13036424, 13938166, 15907617, 13011520, 13569812, 12758736, 13001901
     13077654, 13430715, 13550689, 13251609, 13806545, 13634583, 14271305
     12538907, 13947200, 12996428, 13066371, 17043601, 13483672, 13936066
     12897651, 13540563, 12896850, 13241779, 12728585, 12876314, 12925041
     12650672, 12398492, 12848480, 13088512, 13652088, 14226134, 16307750
     12917897, 12975811, 13653178, 17247404, 13371153, 14800989, 16684285
     10114953, 14001941, 11836951, 14179376, 12965049, 14773530, 12765467
     16821005, 12950415, 15998768, 13339443, 13965075, 16210540, 14307855
     12784559, 14242977, 13955385, 12704789, 16206882, 13745317, 16909409
     17305100, 15832129, 13074261, 12971251, 13993634, 13523527, 16876500
     13719731, 13396284, 12639013, 12867511, 13989181, 12959140, 16836877
     14748254, 13912274, 12829917, 12349553, 12849377, 12934171, 13843080
     14496536, 13899736, 13924431, 12680491, 13334158, 15869775, 10418841
     12832204, 13838047, 13002015, 14639430, 11822565, 15920201, 12791719



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

OPatch succeeded.
[oracle@vm1 OPatch]$


17. Run the post patch script for DB as database home user (oracle).  - Restoring database home configuration

<UNZIPPED_PATCH_LOCATION>/<GI_components_number>/custom/server/<GI_components_number>/custom/scripts/postpatch.sh -dbhome <ORACLE_HOME>

[oracle@vm1 ~]$ cd 17592127
[oracle@vm1 17592127]$ ls
custom  etc  files
[oracle@vm1 17592127]$ cd custom/
[oracle@vm1 custom]$ ls
scripts  server
[oracle@vm1 custom]$ cd server
[oracle@vm1 server]$ ls
17592127
[oracle@vm1 server]$ cd 17592127/
[oracle@vm1 17592127]$ ls
custom  etc  files
[oracle@vm1 17592127]$ cd custom/scripts/
[oracle@vm1 scripts]$ ls
postpatch.sh  prepatch.sh
[oracle@vm1 scripts]$ ./postpatch.sh -dbhome $ORACLE_HOME
Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..
Reading /u01/app/oracle/product/11.2.0/dbhome_1/install/params.ora..
Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap
Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl
Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig
Parsing file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy
Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap
Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl
Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig
Verifying file /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/racgwrap
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvctl
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/srvconfig
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/cluvfy
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/diskmon.bin
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnodes
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/osdbagrp
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/rawutl
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/ractrans
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/srvm/admin/getcrshome
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/gnsd
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/bin/crsdiag.pl
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libhasgen11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclsra11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libdbcfg11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocr11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocrb11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libocrutl11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libuini11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/librdjni11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libgns11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libgnsjni11.so
Reapplying file permissions on /u01/app/oracle/product/11.2.0/dbhome_1/lib/libagfw11.so
[oracle@vm1 scripts]$



18. As root user , run the following from GI home

 <GI_HOME>/rdbms/install/rootadd_rdbms.sh
 <GI_HOME>/crs/install/rootcrs.pl -patch   - For GI home
 <GI_HOME>/crs/install/roothas.pl -patch   - For ORacle restart


 [root@vm1 grid]# cd rdbms/install
[root@vm1 install]# pwd
/u01/app/grid/product/11.2.0/grid/rdbms/install
[root@vm1 install]# ./rootadd_rdbms.sh
[root@vm1 install]# cd ../../crs/install
[root@vm1 install]# ls root*
rootcrs.pl  roothas.pl  rootofs.sh
[root@vm1 install]# ./rootcrs.pl -patch
Using configuration parameter file: ./crsconfig_params
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Start failed, or completed with errors.
Timed out waiting for the CRS stack to start.
[root@vm1 install]# ./roothas.pl -patch
Using configuration parameter file: ./crsconfig_params
CRS-4123: Oracle High Availability Services has been started.

[root@vm1 install]# ps -ef |grep pmon
grid     32450     1  0 12:29 ?        00:00:00 asm_pmon_+ASM
root     32513  6100  0 12:30 pts/3    00:00:00 grep pmon
[root@vm1 install]#


19. As oracle user, restart the database.

[oracle@vm1 scripts]$ srvctl start home -o /u01/app/oracle/product/11.2.0/dbhome_1/ -s /tmp/statfiledb.out
[oracle@vm1 scripts]$ sysresv

IPC Resources for ORACLE_SID "orcl" :
Shared Memory:
ID              KEY
491524          0x00000000
524293          0x00000000
557062          0x42e38fd0
Semaphores:
ID              KEY
524289          0x89a83438
Oracle Instance alive for sid "orcl"
[oracle@vm1 scripts]$



20. Post patch installation for Database

[oracle@vm1 ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 22 12:34:08 2014

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> set lines 150
SQL> set pages 3000
SQL> @?/rdbms/admin/catbundle.sql psu apply

PL/SQL procedure successfully completed.


Function created.


PL/SQL procedure successfully completed.



PL/SQL procedure successfully completed.



Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2014Jan22_12_34_50.log
Apply script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql

PL/SQL procedure successfully completed.

Executing script file...
........
......
...
....


SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
  2    (action_time, action,
  3     namespace, version, id,
  4     bundle_series, comments)
  5  VALUES
  6    (SYSTIMESTAMP, 'APPLY',
  7     SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
  8     '11.2.0.3',
  9     9,
 10     'PSU',
 11     'PSU 11.2.0.3.9');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2014Jan22_12_34_58.log
SQL>


  Run the utlrp.sql
  =====================


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

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2014-01-22 12:47:06

1 row selected.

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
OBJECTS WITH ERRORS
-------------------
                  0

1 row selected.

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0

1 row selected.


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.