Tablespace free space in  Database :

set numformat 9,999,999,999
col TblspNm for a28 heading ‘TABLESPACE NAME’
col curMb for 9,999,999 heading ‘CURRENT|TOTAL (MB)’
col MaxMb for 9,999,999 heading ‘MAX|TOTAL (MB)’
col TotalUsed for 9,999,999 heading ‘CURRENT|USED (MB)’
col TotalFree for 9,999,999 heading ‘CURRENT|FREE (MB)’
col UPercent for 999 heading ‘   CURR| % FULL’;
col UPercent for a7 heading ‘   CURR| % FULL’;

ttitle skip 1 right  ‘Page: ‘ format 99 sql.pno skip 1 –

center ‘TABLESPACE USAGE STATUS REPORT’ skip 2

select distinct
a.tablespace_name “TblspNm”,
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) “MaxMb”, SUM(a.bytes)/1024/1024 “CurMb”,(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024)) “TotalUsed”,
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) – (SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))) “TotalFree”,
lpad(round(100*(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))/(SUM(decode(b.maxextend, null, a.bytes/1024/1024, b.maxextend*8192/1024/1024)))),5) || ‘ %’
“UPercent”
from dba_data_files a, sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) “Free”
FROM dba_tablespaces d,dba_free_space c
where d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
group by a.tablespace_name, c.”Free”/1024
order by round(100*(SUM(a.bytes)/1024/1024 – round(c.”Free”/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc
;

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

Session Details :

select ‘ Sid, Serial#,Aud sid : ‘|| s.sid||’ , ‘||s.serial#||’ ,  ‘||
s.audsid||chr(15)|| ‘     DB User / OS User : ‘||s.username||
‘   /   ‘||s.osuser||chr(10)|| ‘    Machine – Terminal : ‘||
s.machine||’  –  ‘|| s.terminal||chr(10)|| s.module||’-‘||s.action ||
‘        OS Process Ids : ‘||
s.process||’ (Client)  ‘||p.spid||’ – ‘||p.pid||’ (Server)’|| chr(10)||
‘   Client Program Name : ‘||s.program||chr(10)||
‘SESSION STATUS  : ‘||s.status||’   ‘||chr(10)||
‘MINUTES :    ‘||round(S.LAST_CALL_ET/60)   “Session Info”
from v$process p,v$session s
where p.addr = s.paddr
and s.sid = &sid;

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

Tracing :

alter session set events ‘10046 trace name context forever, level 12’;  – Session level
alter system set events ‘10046 trace name context forever, level 12’; – System level

alter system set events ‘10046 trace name context off’;

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

Patchset applied :

col ACTION format a15
col NAMESPACE format a15
col version format a10
col COMMENTS format a42
col BUNDLE_SERIES format a20
col action_time format a30
select * from sys.registry$history;

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

Locks :

set linesize 80
col sess for a15
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||trim(sid) sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request
/

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

Row getting blocked .

select
do.object_name,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#,
dbms_rowid.rowid_create ( 1, data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from
v$session s,
dba_objects do
where sid=&sid and ROW_WAIT_OBJ# = do.OBJECT_ID;

 

select * from  <table_name> where rowid=’row id from above query’;

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

Using date to retrive data :

Using Timestamp works :

select * from v$active_session_history where sample_time between to_timestamp(’06-20-2014 3:00:41.911 PM’,’MM-DD-YYYY hh:mi:ss.ff PM’) and
to_timestamp(’06-20-2014 3:03:57.421 PM’,’MM-DD-YYYY hh:mi:ss.ff PM’)

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

Database Recovery :

recover database using backup controlfile until time ’01-OCT-2014 02:00:00′;

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

Datafile rename command generation :

select ‘alter database rename file ‘||””||name||””||’ to ‘||””||regexp_replace(name,’/u21′,’/u67′)||””||’;’from v$datafile where name like ‘%/u21%’;

select ‘alter database rename file ‘||””||name||””||’ to ‘||””||regexp_replace(name,’/u09′,’/ilmdu02′)||””||’;’from v$datafile where name like ‘%/u09%’;

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

“Read by other session” wait event  :

To find the sessions waiting on blocks.

SELECT p1 “file#”, p2 “block#”, p3 “class#”
FROM v$session_wait WHERE event = ‘read by other session’;

After finding blocks object involved by running this query :

SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks – 1;

 

Baselining Queries :

To create Sql SET :
___________________

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => ‘2y4jv1yavr6gq_03222019’,
description => ‘SQL Tuning Set for loading plan into SQL Plan Baseline for sql id 2y4jv1yavr6gq’);
END;
To load the plans to the sql set :
__________________________________

DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>61600, end_snap=>61853,basic_filter=>’sql_id = ”2y4jv1yavr6gq”’,attribute_list=>’ALL’)
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> ‘2y4jv1yavr6gq_03222019’, populate_cursor=>cur);
CLOSE cur;
END;
/

Verify the plans got loaded to sql Set :
_________________________________________

SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => ‘2y4jv1yavr6gq_03222019’)
);

SELECT
plan_hash_value
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => ‘2y4jv1yavr6gq_03222019’)
);
Create baseline for the sql to be forced :
_________________________________________
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => ‘2y4jv1yavr6gq_03222019′,
basic_filter=>’plan_hash_value = ”3086047068”’
);
END;
/
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘%2y4jv1yavr6gq%’;
exec DBMS_SHARED_POOL.PURGE (‘000000057AA369F8,3267995062′,’C’);


-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/script_creation/table_indexes_ddl.sql
-- Author       : Tim Hall
-- Description  : Creates the index DDL for specified table, or all tables.
-- Call Syntax  : @table_indexes_ddl (schema-name) (table-name or all)
-- Last Modified: 16/03/2013 - Rewritten to use DBMS_METADATA
-- -----------------------------------------------------------------------------------
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
   -- Uncomment the following lines if you need them.
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);
   --DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
END;
/

SELECT DBMS_METADATA.get_ddl ('INDEX', index_name, owner)
FROM   all_indexes
WHERE  owner      = UPPER('&1')
AND    table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

SQL> show error package body BPM_SEMANTIC_PROJECT
Errors for PACKAGE BODY BPM_SEMANTIC_PROJECT:

LINE/COL ERROR
——– —————————————————————–
21/7     PL/SQL: Statement ignored
21/7     PLS-00201: identifier ‘PROCESS_LETTERS.INSERT_BPM_SEMANTIC’ must
be declared

56/7     PL/SQL: Statement ignored
56/7     PLS-00201: identifier ‘PROCESS_LETTERS.UPDATE_BPM_SEMANTIC’ must
be declared

 

Deployment hanging for PL/SQL package and lock is “library cache pin” .Find who is blocking who using below query.

select decode(lob.kglobtyp, 0, ‘NEXT OBJECT’, 1, ‘INDEX’, 2, ‘TABLE’, 3, ‘CLUSTER’,
4, ‘VIEW’, 5, ‘SYNONYM’, 6, ‘SEQUENCE’,
7, ‘PROCEDURE’, 8, ‘FUNCTION’, 9, ‘PACKAGE’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’,
13, ‘TYPE’, 14, ‘TYPE BODY’,
19, ‘TABLE PARTITION’, 20, ‘INDEX PARTITION’, 21, ‘LOB’,
22, ‘LIBRARY’, 23, ‘DIRECTORY’, 24, ‘QUEUE’,
28, ‘JAVA SOURCE’, 29, ‘JAVA CLASS’, 30, ‘JAVA RESOURCE’,
32, ‘INDEXTYPE’, 33, ‘OPERATOR’,
34, ‘TABLE SUBPARTITION’, 35, ‘INDEX SUBPARTITION’,
40, ‘LOB PARTITION’, 41, ‘LOB SUBPARTITION’,
42, ‘MATERIALIZED VIEW’,
43, ‘DIMENSION’,
44, ‘CONTEXT’, 46, ‘RULE SET’, 47, ‘RESOURCE PLAN’,
48, ‘CONSUMER GROUP’,
51, ‘SUBSCRIPTION’, 52, ‘LOCATION’,
55, ‘XML SCHEMA’, 56, ‘JAVA DATA’,
57, ‘SECURITY PROFILE’, 59, ‘RULE’,
62, ‘EVALUATION CONTEXT’,
‘UNDEFINED’) object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = ‘library cache pin’
order by lock_mode_held desc
/

Error :

Unable to allocate flashback log of 16563 blocks from
current recovery area of size 322122547200 bytes.
Recovery Writer (RVWR) is stuck until more space
is available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.

Drop the guranteed restore point .

SQL> drop restore point “11203_091314”;

How to enable and disable Oracle  partition in oracle 9i to 11gr2

Partitioning is only available in Enterprise Edition of the Oracle Database. It is not available in Standard Edition or in Standard Edition One or in Express Edition.

Oracle Partitioning is divided into System and User Partitioning. Oracle Partitioning is an Extra cost option for User Partitioning.

Check partition is enabled or not :

select * from v$option where parameter=’Partitioning’;

sqlplus connection

select name, version, last_usage_date,currently_used from DBA_FEATURE_USAGE_STATISTICS where upper(name) like ‘%PARTITION%’;

To find all non-system partitioned tables, use
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE,
FROM DBA_PART_TABLES
WHERE OWNER <> ‘SYSTEM’ AND OWNER <> ‘SYS’;

– DBA_PART_INDEXES contains information on INDEX objects that are partitioned and / or subpartitioned:

To find all non-system partitioned indexes, use:
SELECT OWNER, INDEX_NAME, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE
FROM DBA_PART_INDEXES
WHERE OWNER <> ‘SYSTEM’ AND OWNER <> ‘SYS’;

Confirmation of partition installation by below method.

1)

$ cd <location of OUI>

$ ./runInstaller

click on “Installed Products”
select the ORACLE_HOME
click on “Expand All”
Review the list or products installed

2)

Use the opatch utility:
$ export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory -detail

All processes running from the ORACLE_HOME including but not limited to databases, listeners, intelligent agents, etc. must be shutdown/stopped before issuing these commands

Disable partition between 9.2.0.x through 11.1.0.6.x :
For example to enable/disable Oracle Partitioning:
Enable
——
$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk part_on ioracle

Disable
——-
$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk part_off ioracle

Product/Component
Automated Storage Management     asm_on             asm_off
Oracle Data Mining         dm_on             dm_off
Database Vault             dv_on             dv_off
Oracle OLAP             olap_on         olap_off
Oracle Label Security         lbac_on         lbac_off
Oracle Partitioning         part_on         part_off
Real Application Cluster     rac_on             rac_off
Real Application Testing     rat_on             rat_off

Starting with 11.2

Enable
——–
$ chopt enable partitioning

Disable
———
$ chopt disable partitioning

Product/Component         Option
Oracle Data Mining           dm
Database Vault                   dv
Oracle OLAP                      olap
Oracle Label Security         lbac
Oracle Partitioning          partitioning
Real Application Testing     rat

Document : 1468698.1  , 948061.1

SRVCTL :

Display the Current Policy  :

Example :

grid@cluster04>srvctl config database -d test
Database unique name: test
Database name: test
Oracle home: /u01/app/oracle/product/11.2.3/laeb
Oracle user: oracle
Spfile: +TEST/test/spfiletest.ora
Domain: maximus.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: MANUAL
Server pools: test
Database instances: test1,test2
Disk Groups: TEST
Mount point paths:
Services:
Type: RAC
Database is administrator managed

Modify policy on existing database

grid@cluster04>srvctl modify database -d test -y AUTOMATIC
PH: .    DB: DB: test1
/home/grid
grid@cluster04>srvctl config database -d test
Database unique name: test
Database name: test
Oracle home: /u01/app/oracle/product/11.2.3/laeb
Oracle user: oracle
Spfile: +TEST/test/spfiletest.ora
Domain: maximus.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances: test1,test2
Disk Groups: TEST
Mount point paths:
Services:
Type: RAC
Database is administrator managed

Example :

Specify a Policy for a New Database

srvctl add database -d test -y AUTOMATIC -o $ORACLE_HOME -d DATA

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

Cluster verification utility .

Verify Integrity of ASM instance.
cluvfy comp asm -n cluster04,cluster05 -verbose

grid@cluster04>cluvfy comp asm -n cluster04,cluster05 -verbose
Verifying ASM Integrity
Task ASM Integrity check started…
Starting check to see if ASM is running on all cluster nodes…
ASM Running check passed. ASM is running on all specified nodes
Starting Disk Groups check to see if at least one Disk Group configured…
Disk Group Check passed. At least one Disk Group configured
Task ASM Integrity check passed…
Verification of ASM Integrity was successful.

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

PATCHING  :

Patch the system where  Grid infrastructure and database  is not in shared filesystem .No ACFS filesystem used. Below is the example of patching and patching output .

OPATCH AUTO – Shutdown the database and applies the patch to the grid environment.

Example : Both Grid and Database environment .It has to be invoked per node basis.

Node 1 :
/u01/software/patches/16495688_112034
grid@cluster04>sudo opatch auto /u01/software/patches/16495688_112034 -ocmrf /u01/app/grid/product/11.2.0.3/isg/OPatch/ocm/bin/ocm.rsp
Password:
Executing /u01/app/grid/product/11.2.0.3/isg/perl/bin/perl /u01/app/grid/product/11.2.0.3/isg/OPatch/crs/patch11203.pl -patchdir /u01/software/patches -patchn 16495688_112034 -ocmrf /u01/app/grid/product/11.2.0.3/isg/OPatch/ocm/bin/ocm.rsp -paramfile /u01/app/grid/product/11.2.0.3/isg/crs/install/crsconfig_params
/u01/app/grid/product/11.2.0.3/isg/crs/install/crsconfig_params
/u01/app/grid/product/11.2.0.3/isg/crs/install/s_crsconfig_defs

This is the main log file: /u01/app/grid/product/11.2.0.3/isg/cfgtoollogs/opatchauto2013-05-16_12-27-36.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/grid/product/11.2.0.3/isg/cfgtoollogs/opatchauto2013-05-16_12-27-36.report.log

2013-05-16 12:27:36: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/grid/product/11.2.0.3/isg/crs/install/crsconfig_params
patch /u01/software/patches/16495688_112034/16495688/custom/server/16495688  apply successful for home  /u01/app/oracle/product/11.2.3/test
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘cluster04’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.CRSDG.dg’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.REDO.dg’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.TEST.dg’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.LISTENER_1535_testUAT_testTRN_LADMSUAT_LADMUAT_testDR.lsnr’ on ‘cluster04’
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘cluster04’ succeeded
CRS-2672: Attempting to start ‘ora.scan3.vip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.LISTENER_1535_testUAT_testTRN_LADMSUAT_LADMUAT_testDR.lsnr’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster04.vip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘cluster04’ succeeded
CRS-2672: Attempting to start ‘ora.scan2.vip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.cluster04.vip’ on ‘cluster04’ succeeded
CRS-2672: Attempting to start ‘ora.cluster04.vip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.TEST.dg’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.REDO.dg’ on ‘cluster04’ succeeded
CRS-2676: Start of ‘ora.scan3.vip’ on ‘cluster05’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster05’
CRS-2676: Start of ‘ora.cluster04.vip’ on ‘cluster05’ succeeded
CRS-2676: Start of ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster05’ succeeded
CRS-2676: Start of ‘ora.scan2.vip’ on ‘cluster05’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster05’
CRS-2676: Start of ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.CRSDG.dg’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘cluster04’
CRS-2677: Stop of ‘ora.asm’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘cluster04’
CRS-2677: Stop of ‘ora.ons’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘cluster04’
CRS-2677: Stop of ‘ora.net1.network’ on ‘cluster04’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘cluster04’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.crf’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘cluster04’
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘cluster04’
CRS-2677: Stop of ‘ora.evmd’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.crf’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘cluster04’
CRS-2677: Stop of ‘ora.cssd’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘cluster04’
CRS-2677: Stop of ‘ora.gipcd’ on ‘cluster04’ succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘cluster04’
CRS-2677: Stop of ‘ora.gpnpd’ on ‘cluster04’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘cluster04’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully unlock /u01/app/grid/product/11.2.0.3/isg
patch /u01/software/patches/16495688_112034/16495688  apply successful for home  /u01/app/grid/product/11.2.0.3/isg
CRS-4123: Oracle High Availability Services has been started.

Node 2 :

grid@cluster05>sudo opatch auto /u01/software/patches/16495688_112034 -oh /u01/app/grid/product/11.2.0.3/isg -ocmrf /u01/app/grid/product/11.2.0.3/isg/OPatch/ocm/bin/ocm.rsp
Password:
Executing /u01/app/grid/product/11.2.0.3/isg/perl/bin/perl /u01/app/grid/product/11.2.0.3/isg/OPatch/crs/patch11203.pl -patchdir /u01/software/patches -patchn 16495688_112034 -oh /u01/app/grid/product/11.2.0.3/isg -ocmrf /u01/app/grid/product/11.2.0.3/isg/OPatch/ocm/bin/ocm.rsp -paramfile /u01/app/grid/product/11.2.0.3/isg/crs/install/crsconfig_params
/u01/app/grid/product/11.2.0.3/isg/crs/install/crsconfig_params
/u01/app/grid/product/11.2.0.3/isg/crs/install/s_crsconfig_defs

This is the main log file: /u01/app/grid/product/11.2.0.3/isg/cfgtoollogs/opatchauto2013-05-16_14-21-22.log
This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/grid/product/11.2.0.3/isg/cfgtoollogs/opatchauto2013-05-16_14-21-22.report.log

2013-05-16 14:21:23: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/grid/product/11.2.0.3/isg/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘cluster05’
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.CRSDG.dg’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.REDO.dg’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.test.db’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.LISTENER_1535_testUAT_testTRN_LADMSUAT_LADMUAT_testDR.lsnr’ on ‘cluster05’
CRS-2677: Stop of ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.scan3.vip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.scan2.vip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.scan3.vip’ on ‘cluster05’ succeeded
CRS-2672: Attempting to start ‘ora.scan3.vip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.LISTENER_1535_testUAT_testTRN_LADMSUAT_LADMUAT_testDR.lsnr’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster05.vip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.scan2.vip’ on ‘cluster05’ succeeded
CRS-2672: Attempting to start ‘ora.scan2.vip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.cluster05.vip’ on ‘cluster05’ succeeded
CRS-2672: Attempting to start ‘ora.cluster05.vip’ on ‘cluster04’
CRS-2677: Stop of ‘ora.REDO.dg’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.test.db’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.TEST.dg’ on ‘cluster05’
CRS-2677: Stop of ‘ora.TEST.dg’ on ‘cluster05’ succeeded
CRS-2676: Start of ‘ora.scan3.vip’ on ‘cluster04’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster04’
CRS-2676: Start of ‘ora.scan2.vip’ on ‘cluster04’ succeeded
CRS-2672: Attempting to start ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster04’
CRS-2676: Start of ‘ora.LISTENER_SCAN3.lsnr’ on ‘cluster04’ succeeded
CRS-2676: Start of ‘ora.cluster05.vip’ on ‘cluster04’ succeeded
CRS-2676: Start of ‘ora.LISTENER_SCAN2.lsnr’ on ‘cluster04’ succeeded
CRS-2677: Stop of ‘ora.CRSDG.dg’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘cluster05’
CRS-2677: Stop of ‘ora.asm’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘cluster05’
CRS-2677: Stop of ‘ora.ons’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘cluster05’
CRS-2677: Stop of ‘ora.net1.network’ on ‘cluster05’ succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘cluster05’ has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.crf’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.asm’ on ‘cluster05’
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘cluster05’
CRS-2677: Stop of ‘ora.mdnsd’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.crf’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.evmd’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.asm’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘cluster05’
CRS-2677: Stop of ‘ora.ctssd’ on ‘cluster05’ succeeded
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘cluster05’
CRS-2677: Stop of ‘ora.cssd’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘cluster05’
CRS-2677: Stop of ‘ora.gipcd’ on ‘cluster05’ succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘cluster05’
CRS-2677: Stop of ‘ora.gpnpd’ on ‘cluster05’ succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘cluster05’ has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully unlock /u01/app/grid/product/11.2.0.3/isg
patch /u01/software/patches/16495688_112034/16495688  apply successful for home  /u01/app/grid/product/11.2.0.3/isg
CRS-4123: Oracle High Availability Services has been started.

Security Scan :

HP WebInspect  —  web application security testing solution  used  mainly analyzing threats ,vulnerablilities of the  OS and  Application  by providing URL  and login credentials of the application .

Change Management Tools :

SVN Repository ,Designer Repository ,CA Software change manager (Harvest change management)  – change management software used for tracking version of the deployment codes.

Tracing :

Note 395525.1 How to Enable Oracle SQLNet Client, Server, Listener, Kerberos and External procedure Tracing from Net Manager
NOTE:374116.1 – How to Match Oracle Net Client and Server Trace Files

Windows: Unable to Tnsping or Connect Using SQL*Plus From a Remote Client (Doc ID 1472931.1)

Client and server tracing.

Client sqlnet.ora

TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = C:\app\oracle\client\11gR2\network\log
TRACE_TIMESTAMP_CLIENT = ON
TRACE_UNIQUE_CLIENT = ON
DIAG_ADR_ENABLED= OFF

Server TESTDB03 sqlnet.ora

grid@testdb03>more sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/grid/product/11.2.0.4/test/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
TRACE_LEVEL_CLIENT = SUPPORT
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
TRACE_LEVEL_SERVER = SUPPORT

SQLNET.EXPIRE_TIME = 1

TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = server
TRACE_DIRECTORY_SERVER = /u01/app/grid/product/11.2.0.4/test/network/log
TRACE_TIMESTAMP_SERVER = ON
TRACE_UNIQUE_SERVER = ON
DIAG_ADR_ENABLED= OFF
ADR_BASE = /u01/app/oracle

Monitoring :

Get Proactive with Oracle E-Business Suite [ID 1388190.1]

ASM and RAC  :

RAC: Frequently Asked Questions [ID 220970.1]

11gR2(11.2) RAC TAF Configuration for Admin and Policy Managed Databases [ID 1312749.1]

Creating ASM physical standby from non ASM primary [ID 752360.1]

RAC and Oracle Clusterware Best Practices and Starter Kit (Solaris) [ID 811280.1]

RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) [ID 810394.1]

Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa [ID 252219.1]

Master Note for Automatic Storage Management (ASM) [ID 1187723.1]

How to Prepare Storage for ASM [ID 452924.1]

How to Move ASM Storage to New RAC Servers [ID 884325.1]

ASM Storage Is slower than non-ASM Storage (regular filesystems) [ID 1497100.1]

 Security :

How to Change the DBID and the DBNAME by using NID [ID 224266.1]

An Introduction to PKI and SSL [ID 264080.1]

How To Redirect all HTTP Requests To HTTPS Requests in OHS [ID 603796.1]

11g Grid Control: How to Configure the HTTPS Console Port to 443 so that the URL can be Accessed without using the Port Number [1340051.1]

Master Note for SSL Configuration in Fusion Middleware 11g [ID 1218695.1]

Administration :

New 11g Default Jobs [ID 755838.1

How To Restrict Users To Use Only Internet Explorer or Firefox With E-Business Suite (Doc ID 825093.1)

Adding Multiple Ports in SCAN listener

Step By Step Guide To Create Physical Standby Database Using RMAN Backup and Restore (Doc ID 469493.1)

http://fiedizheng.wordpress.com/2011/09/23/how-to-add-second-port-to-scan/

RAC and Clusterware Best practices and starter kit [ID 810394.1]

11g New Feature: Health monitor [ID 466920.1]

Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI [ID 1221705.1]

Upgrade  and Migration :

Migration Of An Oracle Database Across OS Platforms [ID 733205.1]

Upgrading and Migration Oracle Database ID [1351112.2]

 Oracle 11gR2 Upgrade Companion [ID 785351.1]

 Upgrade Advisor: E-Business Suite (EBS) Upgrade from 11.5.10.2 to 12.1.3

Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [ID 1276368.1]

Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)

Performance Tuning :

Oracle Performance guide (390374.1)

How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)

What else can influence the Performance of the Database [ID 148462.1]

Interpreting Histogram Information [ID 72539.1]

Interpreting Explain plan [ID 46234.1]

What are Latches and What Causes Latch Contention [ID 22908.1]

Troubleshooting I/O-related waits [ID 223117.1]
How to Read PGA Memory Advisory Section in AWR and Statspack Reports [ID 786554.1]
How to Use AWR reports to Diagnose Database Performance Issues [ID 1359094.1]
How to Read Buffer Cache Advisory Section in AWR and Statspack Reports. [ID 754639.1]
‘Log File Sync’ problem on a Sun Server: A Typical Source for LOGFILE SYNC Performance Problems [ID 1205673.1]
Troubleshooting I/O-related waits [ID 223117.1]

Note:33838.1 Determining the execution plan for a distributed query
Note:236026.1 Display Execution Plans with dbms_xplan.display
Note:235530.1 Recommended Method for Obtaining a Formatted Explain Plan
Note:31101.1 SCRIPT: DIAG – Obtaining Formatted Explain Plan Output
Note.41634.1 TKPROF and Problem Solving
Note.209197.1 Using Statspack to Record Explain Plan Details
Note.237287.1 How To Verify Parallel Execution is running
Note:179518.1 Partition Pruning and Joins

Note:235530.1 Recommended Method for Obtaining a Formatted Explain Plan
Note:31101.1 SCRIPT: DIAG – Obtaining Formatted Explain Plan Output
Note.41634.1 TKPROF and Problem Solving

Selected Performance Related Seminars from Oracle Openworld (OOW) (Doc ID 1380043.1)

Troubleshoot :

Oracle Application Server 10g with Oracle E-Business Suite Release 11i Troubleshooting (Doc ID 295606.1)

Note 395525.1 How to Enable Oracle SQLNet Client, Server, Listener, Kerberos and External procedure Tracing from Net Manager

NOTE:374116.1 – How to Match Oracle Net Client and Server Trace Files

Errors :

ORA-01555 “Snapshot too old” – Detailed Explanation (Doc ID 40689.1)

Master Notes :

Siebel Server Administration Troubleshooting Guide (Doc ID 477442.1)

Master Note For Oracle Audit Vault (Doc ID 1199033.1)

Master Note – Oracle GoldenGate (Doc ID 1298817.1)

Master Note – Oracle GoldenGate: Initial Load Techniques and References (Doc ID 1311707.1)

Oracle Fusion Middleware 11g – Getting Started FAQ (Doc ID 858748.1)

Oracle Fusion Middleware (FMW) SOA 11g Infrastructure Database: Installation, Maintenance and Administration Guide (Doc ID 1384379.1)

Uninstall :

De install from local home :

$ORACLE_HOME/deinstall/deinstall —–> execute the script  you will be prompted like given below.

oracle@rsmxdb07>./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /home/oracle/oraInventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################## CHECK OPERATION START ########################
Install check configuration START

Checking for existence of the Oracle home location /u01/app/oracle/product/11.2.0/lamb
Oracle Home type selected for de-install is: SIDB
Oracle Base selected for de-install is: /u01/app/oracle
Checking for existence of central inventory location /home/oracle/oraInventory

Install check configuration END

Network Configuration check config START

Network de-configuration trace file location: /home/oracle/oraInventory/logs/netdc_check1117711111921917818.log

Specify all Single Instance listeners that are to be de-configured [LISTENER_1535_LAMBPRD]:

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /home/oracle/oraInventory/logs/databasedc_check4035179407527384756.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [lambprd]:

###### For Database 'ladmprd' ######

Single Instance Database
The diagnostic destination location of the database: /u01/app/oracle/diag/rdbms/lambprd
Storage type used by the Database: FS
Database file location: /u20/oracle/oradata/lambprd
Flash recovery area location: Does not exist
database spfile location: /u01/app/oracle/product/11.2.0/laeb/dbs/spfilelambprd.ora

The details of database(s) ladmprd have been discovered automatically. Do you still want to modify the details of ladmprd database(s)? [n]:

Database Check Configuration END

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /home/oracle/oraInventory/logs/emcadc_check.log

Checking configuration for database ladmprd
Database ladmprd is being managed by the Enterprise Manager Grid Control Agent located at /u01/app/oracle/product/10.2.0/em/agent10g
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /home/oracle/oraInventory/logs//ocm_check369.log
Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for de-install is: /u01/app/oracle/product/11.2.0/laeb
Inventory Location where the Oracle home registered is: /home/oracle/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER_1535_LAMBPRD
The following databases were selected for de-configuration : lambprd
Database unique name : lambprd
Storage used : FS
Will update the Enterprise Manager configuration for the following database(s): lambprd
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/home/oracle/oraInventory/logs/deinstall_deconfig2011-11-10_05-54-35-PM.out'
Any error messages from this session will be written to: '/home/oracle/oraInventory/logs/deinstall_deconfig2011-11-10_05-54-35-PM.err'

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /home/oracle/oraInventory/logs/emcadc_clean.log

Updating Enterprise Manager Grid Control Agent targets for database lambprd
Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /home/oracle/oraInventory/logs/databasedc_clean5405584761734952309.log
Database Clean Configuration START lambprd
This operation may take few minutes.
Database Clean Configuration END lampbrd

Network Configuration clean config START

Network de-configuration trace file location: /home/oracle/oraInventory/logs/netdc_clean5250175349498392053.log

De-configuring Single Instance listener(s): LISTENER_1535_LAMBPRD

De-configuring listener: LISTENER_1535_LAMPBRD
    Stopping listener: LISTENER_1535_LAMBPRD
    Listener stopped successfully.
    Deleting listener: LISTENER_1535_LAMBPRD
    Listener deleted successfully.
Listener de-configured successfully.

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /home/oracle/oraInventory/logs//ocm_clean369.log
Oracle Configuration Manager clean END
Oracle Universal Installer clean START

Detach Oracle home '/u01/app/oracle/product/11.2.0/laeb' from the central inventory on the local node : Done
Delete directory '/u01/app/oracle/product/11.2.0/laeb' on the local node : Done

The Oracle Base directory '/u01/app/oracle' will not be removed on local node. The directory is in use by Oracle Home '/u01/app/oracle/product/10.2.0/db1'.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

Oracle install clean START

Clean install operation removing temporary directory '/var/tmp//install' on node 'rslamb07'

Oracle install clean END

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################
Updated Enterprise Manager configuration for database ladmprd
Successfully de-configured the following database instances : lambprd
Following Single Instance listener(s) were de-configured successfully: LISTENER_1535_LADMBPRD
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u01/app/oracle/product/11.2.0/lamb' from the central inventory on the local node.
Successfully deleted directory '/u01/app/oracle/product/11.2.0/lamb' on the local node.
Oracle Universal Installer cleanup was successful.

Oracle install successfully cleaned up the temporary directories.
#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Packaging Incident oracle : 
>adrci 
set homepath  diag/rdbms/<sid>/db_unique_name
show problem 
show incident -p "problem_key='ORA 7445'"
ex: adrci> ips pack incident 194890 in /tmp
Fiel : /tmp/ORA7445kp_20120305152328_COM_1.zip

 

OEM grid control 11g  Maintenance : 

Start : 

$MIDDLEWARE_HOME/oms11g/bin/emctl  start oms 

Ex :server :marble.nmhc.com 

cd  /u01/app/oracle/middleware/oms11g/bin

./emctl start oms 

Stop : 

$MIDDLEWARE_HOME/oms11g/bin/emctl stop oms 

Ex : 

cd  /u01/app/oracle/middleware/oms11g/bin

./emctl stop oms

Log files : 

<EM_INSTANCE_BASE>/em/<OMS_NAME>/sysman/log 

Ex : 

/u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log

emoms.log

emctl.log

emoms.trc