Using SQL Tuning Advisor from command line

Farhat Zaman All Posts

I recently imported a large table which contains user log data. After import there was not index and table was not analyzed.

I ran a select query

SELECT * FROM USER_LOG WHERE LOG_USER_ID =’JOHN’;

This took 6 seconds to retrieve 70000+ rows

Then i ran

SELECT count(*) FROM USER_LOG WHERE LOG_USER_ID =’JOHN’;

Now i want to use sql tuning advisor to get tuning recommendations and plan to tune this sql.

I created below tuning task.

#############################################################################################

declare
    tempstring  varchar2(900);
    task_id   varchar2(200);
    begin
       tempstring := ‘SELECT * FROM USER_LOG WHERE LOG_USER_ID =”JOHN”’;
       task_id := dbms_sqltune.create_tuning_task(sql_text => tempstring, task_name=>’SQLTUNE3′);
       dbms_sqltune.execute_tuning_task(‘SQLTUNE3’);
    end;
    /

###############################################################################################

select dbms_sqltune.report_tuning_task(‘SQLTUNE3’) from dual; 

Below is the out put

###############################################################################################

GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name   : SQLTUNE3
Tuning Task Owner  : FARHAT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 07/09/2015 13:41:03
Completed at       : 07/09/2015 13:42:40

——————————————————————————-
Schema Name: FARHAT
SQL ID     : bpby9tjfzn5x3
SQL Text   : SELECT * FROM USER_LOG WHERE LOG_USER_ID =’JOHN’

——————————————————————————-
FINDINGS SECTION (3 findings)
——————————————————————————-

1- Statistics Finding
———————
  Table “FARHAT”.”USER_LOG” was not analyzed.

  Recommendation
  ————–
  – Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => ‘FARHAT’, tabname =>
            ‘USER_LOG’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

  Rationale
  ———
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

2- SQL Profile Finding (see explain plans section below)
——————————————————–
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 96.53%)
  ——————————————
  – Consider accepting the recommended SQL profile to use parallel execution
    for this statement.
    execute dbms_sqltune.accept_sql_profile(task_name => ‘SQLTUNE3’,
            task_owner => ‘FARHAT’, replace => TRUE, profile_type =>
            DBMS_SQLTUNE.PX_PROFILE);

  Executing this query parallel with DOP 32 will improve its response time
  96.53% over the original plan. However, there is some cost in enabling
  parallel execution. It will increase the statement’s resource consumption by
  an estimated 10.97% which may result in a reduction of system throughput.
  Also, because these resources are consumed over a much smaller duration, the
  response time of concurrent statements might be negatively impacted if
  sufficient hardware capacity is not available.

  The following data shows some sampled statistics for this SQL from the past
  week and projected weekly values when parallel execution is enabled.

                                 Past week sampled statistics for this SQL
                                 —————————————–
  Number of executions                                                   0
  Percent of total activity                                              0
  Percent of samples with #Active Sessions > 2*CPU                       0
  Weekly DB time (in sec)                                                0

                              Projected statistics with Parallel Execution
                              ——————————————–
  Weekly DB time (in sec)                                                0

3- Index Finding (see explain plans section below)
————————————————–
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 54.72%)
  ——————————————
  – Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index FARHAT.IDX$$_497A0001 on FARHAT.USER_LOG(“LOG_USER_ID”);

  Rationale
  ———
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run “Access Advisor”
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-

1- Original
———–
Plan hash value: 3169137855

———————————————————————————-
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT  |              | 77112 |   449M|   146K  (1)| 00:29:18 |
|*  1 |  TABLE ACCESS FULL| USER_LOG | 77112 |   449M|   146K  (1)| 00:29:18 |
———————————————————————————-

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“LOG_USER_ID”=’JOHN’)

2- Using New Indices
——————–
Plan hash value: 2445436647

———————————————————————————————-
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————-
|   0 | SELECT STATEMENT            |                | 77112 |   449M| 66307   (1)| 00:13:16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| USER_LOG   | 77112 |   449M| 66307   (1)| 00:13:16 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_497A0001 | 77112 |       |  1704   (1)| 00:00:21 |
———————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

   2 – access(“LOG_USER_ID”=’JOHN’)

3- Using Parallel Execution
—————————
Plan hash value: 858866093

——————————————————————————————————————
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
——————————————————————————————————————
|   0 | SELECT STATEMENT     |              | 77112 |   449M|  5079   (1)| 00:01:01 |        |      |            |
|   1 |  PX COORDINATOR      |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000     | 77112 |   449M|  5079   (1)| 00:01:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |              | 77112 |   449M|  5079   (1)| 00:01:01 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| USER_LOG | 77112 |   449M|  5079   (1)| 00:01:01 |  Q1,00 | PCWP |            |
——————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

   4 – filter(“LOG_USER_ID”=’JOHN’)

——————————————————————————-

lets implement above recommendations

Create index

create index FARHAT.IDX$$_497A0001 on FARHAT.BAS_USER_LOG(“LOG_USER_ID”);

Gather Statistics

execute dbms_stats.gather_table_stats(ownname => ‘FARHAT’, tabname =>
            ‘BAS_USER_LOG’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

SELECT * FROM USER_LOG WHERE LOG_USER_ID =’JOHN’

Now the query time reduces to 158m seconds

SELECT COUNT(*) FROM USER_LOG WHERE LOG_USER_ID=’JOHN’

Now the query time reduces to 46m seconds

#############################################################################################

Killing Oracle Session from database and OS level

Farhat Zaman Database, Oracle, RAC

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SQL> SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != ‘BACKGROUND’;

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
———- ———- ———- ———- ———- ———————————————
         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of “marked for kill”. It will then be killed as soon as possible.

In addition to the syntax described above, you can add the IMMEDIATE clause.

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it’s worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

ALTER SYSTEM DISCONNECT SESSION

The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.

SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immediately.

The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.

SQL> alter system disconnect session ‘30,7’;

alter system disconnect session ‘30,7’
                                     *
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.

C:\> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

Warning: If you are using the Multithreaded Model in Oracle 12c, you should not attempt to kill operating system processes. To know why, read this.

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.

% kill spid

If after a few minutes the process hasn’t stopped, terminate the session using the following.

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

Monitoring Tablespace growth from sql

Farhat Zaman All Posts

set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100

column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10

select * from dba_data_files;

select
file_name “Filename”,
tablespace_name “Tablespace”,
round(bytes/1024/1024 ,2) “Current Size (MB)”,
autoextensible “Autoextend”,
round(increment_by*8192/1024/1024 ,2) “Autoextend Size (MB)”,
round(maxbytes/1024/1024 ,2) “Max Size (MB)”
from dba_data_files;

select
        file_name “Filename”,
        tablespace_name “Tablespace”,
        round(bytes/1024/1024 ,2) “Current Size (MB)”,
        autoextensible “Autoextend”,
        round(increment_by*8192/1024/1024 ,2) “Autoextend Size (MB)”,
        round(maxbytes/1024/1024 ,2) “Max Size (MB)”
from dba_data_files
where tablespace_name = ‘SYSTEM’; 

The following will show you the total size and percentage usage of each tablespace, order by percentage:

select T1.TABLESPACE_NAME,
       round(T1.BYTES/1024/1024 ,2) “Used Size (MB)”,
       round(T2.BYTES/1024/1024 ,2) “Free Size (MB)”,
       round(T2.largest/1024/1024 ,2) “Largest Datafile (MB)”,
       round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) “Used %”
  from
  (
   select TABLESPACE_NAME,
   sum(BYTES) BYTES
   from dba_data_files
   group by TABLESPACE_NAME
  )
   T1,
  (
   select TABLESPACE_NAME,
   sum(BYTES) BYTES ,
   max(BYTES) largest
   from dba_free_space
   group by TABLESPACE_NAME
  )
   T2
   where T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
   order by ((T1.BYTES-T2.BYTES)/T1.BYTES) desc;
 
 
If you do not want to convert the bytes into MB or whatever, use the following. Otherwise you will get the high values as power of 10 (eg. 3.4360E+10):

column maxbytes format 9,999,999,999,999
column bytes format 9,999,999,999,999
column user_bytes format 9,999,999,999,999

Tracking Cursors usage

Farhat Zaman Database, Oracle, Tuning

Total cursors open, by session:

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = ‘opened cursors current’
and username = ‘FARHAT’;

Here’s how to find open cursors that have been parsed. You need to be logged in as a user with access to v$open_cursor and v$session.

COLUMN USER_NAME FORMAT A15

SELECT s.machine, oc.user_name, oc.sql_text, count(1)
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2
ORDER BY count(1) DESC
;

This select shows you the actual SQL code that opened the cursors

select  sql_text, count(*) as “OPEN CURSORS”, user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

You can filter your users

select  sql_text, count(*) as “OPEN CURSORS”, user_name from v$open_cursor
WHERE USER_NAME=’FARHAT’
group by sql_text, user_name order by count(*) desc;

You can find Sum of Number of Cursors opened by all users connected to database

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
 s.username, s.machine
 from v$sesstat a, v$statname b, v$session s
 where a.statistic# = b.statistic# and s.sid=a.sid
 and b.name = ‘opened cursors current’
 group by s.username, s.machine
 order by 1 desc;

How to find an Oracle Process which is consuming most of memory and CPU

Farhat Zaman All Posts

To find out which use is consuming most of memory you can use below query

SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr
order by
b.pga_used_mem desc

or you can use below filter to limit results to a specific user

–a.username=’FARHAT’
b.spid = ‘6956’;

You can convert bytes into MBs using below command

SELECT a.username, a.osuser, a.program, b.spid,b.pga_used_mem/1024/1024 “PGA USED (MB)”, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM v$session a, v$process b WHERE a.paddr = b.addr
order by
b.pga_used_mem desc

———————————————————

CPU USAGE TRACKING

Below query can identify user which is consuming more CPU Resources

select 
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
from
   v$session ss, 
   v$sesstat se, 
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like ‘%CPU used by this session%’
and
   se.SID = ss.SID
and 
   ss.status=’ACTIVE’
and 
   ss.username is not null

order by VALUE desc;  


After identifying culprit session you can kill it using Oracle commands or OS

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != ‘BACKGROUND’;

Kill the culprit session

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of “marked for kill”. It will then be killed as soon as possible.

In addition to the syntax described above, you can add the IMMEDIATE clause.

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it’s worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

SET LINESIZE 200

COLUMN username FORMAT A15

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;


ALTER SYSTEM DISCONNECT SESSION



The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.

SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION ‘sid,serial#’ IMMEDIATE;

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immediately.

The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.

SQL> alter system disconnect session ‘30,7’;

alter system disconnect session ‘30,7’
                                     *
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

SQL>
This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.

C:\> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

Warning: If you are using the Multithreaded Model in Oracle 12c, you should not attempt to kill operating system processes. To know why, read this.

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.

% kill spid

If after a few minutes the process hasn’t stopped, terminate the session using the following.

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.


% ps -ef | grep ora

Some other useful linux commands

ps -U oracle -l

ps -U oracle -F

To check top resource consuming sessions

top

ps -A
ps -e


pstree

Bulk vs Conventional data Loading in Oracle for Large tables

Farhat Zaman Database, Oracle, Tuning

DECLARE
TYPE t_bas_user_log IS TABLE OF BAS_USER_LOG%ROWTYPE;
myuser_tab   t_bas_user_log := t_bas_user_log();
start_time  number;
end_time   number;
BEGIN
— Populate a collection – 100000 rows
SELECT * BULK COLLECT INTO myuser_tab FROM base_user_log1;
EXECUTE IMMEDIATE ‘TRUNCATE TABLE myuser_log’;
Start_time := DBMS_UTILITY.get_time;
FOR i in myuser_tab.first .. myuser_tab.last LOOP
 INSERT INTO myuser_log –(product_id, product_name, effective_date)
 VALUES  (myuser_tab(i).LOG_SERIAL_NUM      ,
myuser_tab(i).LOG_USER_ID        ,
  myuser_tab(i).LOG_ACTION_TYPE     ,
  myuser_tab(i).LOG_DATE           ,
  myuser_tab(i).TABLE_NAME         ,
  myuser_tab(i).FIELD_NAME_STRING  ,
  myuser_tab(i).OLD_VALUE_STRING   ,
  myuser_tab(i).NEW_VALUE_STRING   ,
  myuser_tab(i).CMP_NUM             ,
  myuser_tab(i).TRANS_SERIAL        ,
  myuser_tab(i).ACCOUNT_YEAR  )    ;
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Conventional Insert: ‘||to_char(end_time-start_time));
EXECUTE IMMEDIATE ‘TRUNCATE TABLE myuser_log’;
Start_time := DBMS_UTILITY.get_time;
FORALL i in myuser_tab.first .. myuser_tab.last
 INSERT INTO myuser_log VALUES myuser_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE(‘Bulk Insert: ‘||to_char(end_time-start_time));
    COMMIT;
END;

Image below shows that there is a huge difference between Bulk and conventional data loading.

Patching RAC (Grid Plus Database) / Bundle Patch not full release

Farhat Zaman Database, Oracle, RAC


Environment: 11gRAC/DB (11.2.0.3) , Windows Server 2008 R2 x64 Edition
Patch:13965211 – 11.2.0.3 Patch 6 for Windows 64 bit, download patch from metalink.
This patch is RAC rolling upgradable for GRID, and RDBMS homes. 
You should administer one Oracle product, one Oracle home at a time, for its particular Bundle Patch Update.

DB 11.2.0.3 Patch 6 includes all bugs fixed in 11.2.0.3 Patch 1 to Patch 5. It must be applied on top of the 11.2.0.3 base release or on top of any earlier bundle patch from 11.2.0.3 Patch 1 to Patch 5.
The order of patching in RAC install is GRID_HOME, then RDBMS_HOME 
OPatch Info:
Oracle recommends that you use OPatch utility release 11.2.0.1.9 or later. You can download it fromMy Oracle Support with patch 6880880.

Patch Installation Instructions
Patching GRID Home

  1. Before Patching GRID_HOME, stop OCR related resources, Oracle HASD Services (OHASD) Services and all Oracle Services: 
    > srvctl stop service -d  
             -s  -i 
    > srvctl stop instance -d DBNAME -i INSTANCE -o abort
    > crsctl stop crs
    make the backup of the Oracle Home being patached (eg; d:\app...)
  2. To verify all resources have been oved to the other nodes, run the following command from another node with GRID running: 
    > crsctl status resource -t

     

  3. Then use the Computer Management Services GUI or the net stop command to stop ALL Oracle services running on that node:
        net stop OracleService
        net stop OracleTNSListenerLISTENER_
        net stop OracleTNSListenerLISTENER_SCANn
        net stop OracleDBConsole
        net stop OracleASMService
        net stop “Oracle VSS Writer Service”
        net stop OracleREMExecService
        net stop OracleOHService
        net stop “Oracle Object Service”
        net stop OracleClusterVolumeService
        net stop ocfs (required only for OCFS installation)
        net stop orafenceservice
        Note: An explicit ‘srvctl start listener -n ‘ should be issued after patching
  4. Set the ORACLE_HOME environment variable properly
    D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch>set ORACLE_HOME=D:\app\11.2.0.3\grid
  5. Go to the directory where you downloaded the patch:
    D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch>cd D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64
    First check the inventory for the existing patches
    D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch\opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : D:\app\11.2.0.3\grid
Central Inventory : C:\Program Files\Oracle\Inventory
   from           : n/a
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.3.0
Log file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\opatch2012-05-26_10-47-13AM_1.log

Lsinventory Output file location : D:\app\11.2.0.3\grid\cfgtoollogs\opatch\lsinv\lsinventory2012-05-26_10-47-13AM.txt

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

Oracle Grid Infrastructure                                           11.2.0.3.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


Rac system comprising of multiple nodes
  Local node = P-HQ-CL-OR-11
  Remote node = P-HQ-CL-OR-12

——————————————————————————–

OPatch succeeded.

  1. D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64>cd 13965211
  2. D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch\opatch apply -local
  1. opatch will attempt to apply the patch on all the nodes in the cluster sequentially. However will prompt for user input to start patching other nodes. At this time ensure that the Oracle services on target node are stopped before proceeding to patch. Once the opatch session completes patching this node, start the services and proceed applying patch to the next node.
  2.  Once the opatch session completes patching GRID_HOME on a node, run the following before proceeding to patch GRID_HOME on the next node. %ORACLE_HOME%\bin\acfsroot installD:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>%ORACLE_HOME%\bin\acfsroot install
    ACFS-9300: ADVM/ACFS distribution files found.
    ACFS-9307: Installing requested ADVM/ACFS software.
    ACFS-9308: Loading installed ADVM/ACFS drivers.
    ACFS-9327: Verifying ADVM/ACFS devices.
    ACFS-9157: Detecting driver ‘oracle oks’.
    ACFS-9157: Detecting driver ‘oracle advm’.
    ACFS-9157: Detecting driver ‘oracle acfs’.
    ACFS-9309: ADVM/ACFS installation correctness verified.

  3. After this you can start the CRS or go for the RDBMS home patch without starting it, I started.
    D:\app\11.2.0.3\grid\BIN>crsctl start crs
    CRS-4123: Oracle High Availability Services has been started.

    D:\app\11.2.0.3\grid\BIN>crsctl check crs
    CRS-4638: Oracle High Availability Services is online
    CRS-4537: Cluster Ready Services is online
    CRS-4529: Cluster Synchronization Services is online
    CRS-4533: Event Manager is online
     
  4. Verify the patch installation

    5. opatch lsinventory 

    Patching RDBMS Home

    1. Before Patching RDBMS_HOME, the GRID and ASM services can be running.
      Stop OCR related resources and Oracle Services on the current node as listed below:
      > srvctl stop service -d -s -i > srvctl stop instance -d -i -o abort
      D:\app\11.2.0.3\grid\BIN>srvctl stop instance -d rac -i rac1
    2. Set ORACLE_HOME
      D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>set ORACLE_HOME=D:\app\farif\product\11.2.0.3\dbhome_1
    3.  Apply patch
      D:\Sources\11.2.0.3\Patches\p13965211_112030_MSWIN-x86-64\13965211>D:\Sources\11.2.0.3\Patches\p6880880_112000_MSWIN-x86-64_optach\OPatch\opatch apply 
    4. Start the DB instances
      D:\app\11.2.0.3\grid\BIN>srvctl start instance -d rac -i rac1

      D:\app\11.2.0.3\grid\BIN>

      5. Post installation steps

      After installing the patch, perform the following actions:
      For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catwinbundle.sql as follows:

      d %ORACLE_HOME%\Bundle\Patch6
      > sqlplus /nolog 
      SQL> CONNECT / AS SYSDBA 
      SQL> STARTUP
      SQL> @catwinbundle.sql
      SQL> QUIT

C:\Users\farif>cd D:\app\farif\product\11.2.0.3\dbhome_1\bundle\patch6


D:\app\farif\product\11.2.0.3\dbhome_1\bundle\patch6>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 27 11:30:19 2012

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


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

SQL>

SQL> @catwinbundle.sql

…
…
…
SQL> ALTER SESSION SET current_schema = SYS;

Session altered.

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     6,
 10     ‘WINBUNDLE’,
 11     ‘11.2.0.3 BP 6’);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
D:\app\farif\cfgtoollogs\catbundle\catbundle_WINBUNDLE_RAC_APPLY_2012May27_11_31_37.log
SQL>

Check all dependent objects are valid and if not then compile them by using 
utlrp.sql
SQL> select count(*) from dba_objects where status ='INVALID';

  COUNT(*)
----------
     20083
Note: 
You must execute the steps in “Post Installation Steps” for any new database that was created by any of the following methods:

  • Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)
  • Using a script that was created by DBCA that creates a database from a sample database
  • Cloning a database that was created by either of the two preceding methods, and if “Post Installation Steps”   was not executed after Bundle Patch6 was applied

AutoConfig Usage

Farhat Zaman EBS R12

AutoConfig for APPS tier

Execute environment file
cd $APPL_TOP
. ./VIS_ebs.env

Run autoconfig
cd $APPL_TOP/admin/scripts
adautocfg.sh

===========================================================

AutoConfig for Database Tier

Set Environment

$DB_HOME/VIS_ebs.env

Full Path
/u01/oracle/VIS/db/tech_st/11.1.0/VIS_ebs.env

Run AutoConfig

$DB_HOME/appsutil/scripts/VIS_ebs/adautocfg.sh

Full Path
/u01/oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_ebs/adautocfg.sh

Using Service in Oracle RAC environment

Farhat Zaman Database, Oracle, RAC

10g

To add a service

Command: srvctl add service -d db_name -s service_name -r pref_insts [-a avail_insts] [-P TAF_policy]
Command: srvctl add service -d db_name -s service_name -u {-r “new_pref_inst” | -a “new_avail_inst”}
Example: srvctl add service -d RAC -s PRD -r RAC01,RAC02 -a RAC03,RAC04
Example: srvctl add serv -d CRM -s CRM -r CRM1 -a CRM3 -P basic

To remove a service

Command: srvctl remove service -d db_name -s service_name [-i inst_name] [-f]
Example: srvctl remove serv -d rac -s sales
Example: srvctl remove service -d rac -s sales -i rac01,rac02
To start a service

Command: srvctl start service -d db_name [-s service_names [-i inst_name]] [-o start_options]
Command: srvctl start service -d db_name -s service_names [-o open]
Command: srvctl start service -d db_name -s service_names -o nomount
srvctl start service -d db_name -s service_names -o mount
srvctl start serv -d dwh -s dwh

To stop a service

Command: srvctl stop service -d db_name [-s service_names [-i inst_name]] [-f]
srvctl stop serv -d dwh -s dwh

To view the status of Service

Command: srvctl status service -d db_name [-s service_names] [-f] [-v] [-S level]
srvctl status service -d rac -s rac

TO enable the service at cluster startup

Command: srvctl enable service -d db_name -s service_names [-i inst_name]
srvctl enable service -d apps -s apps1

To disable the service at cluster startup

Command: srvctl disable service -d db_name -s service_names [-i inst_name]
Example: srvctl disable serv -d rac -s rac -i rac1

To view the config service

Command: srvctl config service -d db_name [-s service_name] [-a] [-S level]
Command: srvctl config service -d db_name -a — -a shows TAF configuration
Example: srvctl config service -d TEST -s test PREF:TST1 AVAIL:TST2

To modify the service

Command: srvctl modify service -d db_name -s service_name -i old_inst_name -t new_inst_name [-f]
Command: srvctl modify service -d db_name -s service_name -i avail_inst_name -r [-f]
Command: srvctl modify service -d db_name -s service_name -n -i preferred_list [-a available_list] [-f]
Command: srvctl modify service -d db_name -s service_name -i old_inst_name -a avail_inst -P TAF_policy
Example: srvctl modify serv -d PROD -s DWH -n -i I1,I2,I3,I4 -a I5,I6

To relocate service

Command: srvctl relocate service -d db_name -s service_name -i old_inst_name -t target_inst [-f]
To Get/set the environment for Service

Command: srvctl getenv service -d db_name -s service_name -t name_list
Command: srvctl setenv service -d db_name [-s service_name] {-t “name=val[,name=val,…]” | -T “name=val”}
Command: srvctl unsetenv service -d db_name -s service_name -t name_list

11gR2

Command: srvctl add service -d db_unique_name -s service_name
-g server_pool [-c {UNIFORM|SINGLETON}] [-k network_number]
[-l [PRIMARY|PHYSICAL_STANDBY|LOGICAL_STANDBY|SNAPSHOT_STANDBY]
[-y {AUTOMATIC|MANUAL}] [-q {TRUE|FALSE}] [-j {SHORT|LONG}]
[-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}]
[-m {NONE|BASIC}] [-P {BASIC|NONE|PRECONNECT}] [-x {TRUE|FALSE}]
[-z failover_retries] [-w failover_delay]

To add service with service goals

Example: srvctl add service -d rac -s rac1 -q TRUE -m BASIC -e SELECT -z 180 -w 5 -j LONG

To add service wih preconnect taf

Command: srvctl add service -d db_unique_name -s service_name -u {-r preferred_list | -a available_list}
Example: srvctl add serv -d rac -s report -r rac01,rac02 -a rac03 -P PRECONNECT

To start service

Command: srvctl start service -d db_unique_name [-s “service_name_list” [-n node_name | -i instance_name]] [-o start_options]
Example: srvctl start serv -d rac -s rac
Example: srvctl start service -d rac -s rac -i rac2

To stop service

srvctl stop service -d db_unique_name [-s “service_name_list”] [-n node_name | -i instance_name] [-f]
srvctl stop service -d rac -s rac
srvctl stop serv -d rac -s rac -i rac2

To view service

srvctl status service -d db_unique_name [-s “service_name_list”] [-f] [-v]
srvctl status service -d rac -s rac -v

To modify the service

Command: srvctl modify service -d db_unique_name -s service_name
[-c {UNIFORM|SINGLETON}] [-P {BASIC|PRECONNECT|NONE}]
[-l {[PRIMARY]|[PHYSICAL_STANDBY]|[LOGICAL_STANDBY]|[SNAPSHOT_STANDBY]} [-q {TRUE|FALSE}] [-x {TRUE|FALSE}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z failover_retries] [-w failover_delay] [-y {AUTOMATIC|MANUAL}]

Command: srvctl modify service -d db_unique_name -s service_name -i old_instance_name -t new_instance_name [-f]
Command: srvctl modify service -d db_unique_name -s service_name -i avail_inst_name -r [-f]
Command: srvctl modify service -d db_unique_name -s service_name -n -i preferred_list [-a available_list] [-f]

Example: srvctl modify service -d rac -s rac -i rac1 -t rac2
Example: srvctl modify serv -d rac -s rac -i rac1 -r
Example: srvctl modify service -d rac -s rac -n -i rac1 -a rac2
To relocate the service

Command: srvctl relocate service -d db_unique_name -s service_name {-c source_node -n target_node|-i old_instance_name -t new_instance_name} [-f]
Example: srvctl relocate service -d rac -s rac -i rac1 -t rac3