Monday, December 22, 2014

Oracle 10g/11g:Install & deploy interim patch ::19148376 for bug fixes

Oracle 10g/11g:Install & deploy interim patch ::19148376 for bug fixes


Dear User,

In this post, We will apply small sized interim patch for bug fixes that affect the business.
This interim patch specially developed for specific operating system level.
This interim patch doesn't affect any major release or update the software  binaries.
Its developed to fix the particular ORA errors comes in production to affect the problems.

This patch can be deployed easy steps to follow.

Pr-requisites.

1.  PSU Patch 11.2.0.4.0 AIX64(any other OS) Bit database software

2.  Free space of around 20 GB for taking oracle binary Backup and installing Patch sets during patching

3.  Take TAR Backup of the ORACLE_HOME post DATABASE stop

4.  Root, Grid, Oracle OS user password

5.  OPatch utility version 11.2.0.4.0 or later

6.  Hot/Cold backup of database.
 


 1.Check the running database.

[oracle@dbexprt /home/oracle]$ps -ef | grep pmon


oracle  7667950        1   0   Jul 15      -  1:52 asm_pmon_+ASM
oracle  7667950        1   0   Jul 15      -  1:52 asm_pmon_dbexperdb
  oracle 44565652 39716272   0 06:01:08  pts/0  0:00 grep pmon

2.Check Database home for running databases.

cat /etc/oratab.

3.Shutdown the listeners.

ps -ef|grep tns or ps -ef | grep lsn

[oracle@dbexprt /home/oracle]$lsnrctl stop LISTENER

4.Shutdown databases.

Shutdown ASM database (if configured.)

set ORACLE_HOME= /grid11g/11.2.0.4/product/apps/grid

sqlplus  / as sysasm
sql>shutdown

5.Take TAR backup of oracle binaries.

mkdir -p /backup/TAR/oracle_home_122214

cd /backup/TAR/oracle_home_122214
 [oracle@dbexprt /home/oracle]$echo $ORACLE_HOME
/orahome/11.2.0.4/product/11.2.0.4

tar -cvf ./file_name.tar /orahome/11.2.0.4/product/11.2.0.4


6.Check patch history.

 opatch lsinventory

7.Check opatch per-requisite.

Go to patch locaton .

mkdir -p /backup/patch/patch_122214

cd /backup/patch/patch_122214

unzip downloded patch.

change location to  uncompressed patch.

cd /backup/patch/patch_122214/19148376

e.g. opatch prereq CheckConflictAgainstOHWithDetail –phBaseDir (Patch Path)

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/patch/patch_122214/19148376



If any of the patch is conflict with current patch, need to check with MOS team for the same.

Else we can continue for apply the patch.

--Refer readme file from downloaded patch.

8.Apply patch.

[oracle@dbexpert /backup/patch/patch_122214/19148376]$ opatch apply


if this command is not worked then use full opatch location.

[oracle@dbexpert /backup/patch/patch_122214/19148376]$ /orahome/11.2.0.4/product/11.2.0.4/Opatch/opatch apply

Check opatch lsintventory.


9.Post deploy activity.

Start Listeners
Start ASM Database
Start Databases.



******Approx downtime of 2-3 hours****************





Saturday, August 16, 2014

Oracle 11g:SYSAUX Tablespace purging when abnormally increases by size

Oracle 11g:SYSAUX Tablespace purging when abnormally increases by size



  It is very obvious when we enable feature of sql profiler by
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES or increases the retention period of awr reports.

We can get the all info of space utilization of sysaux tablespace by awrinfo.sql .

Collect the space occupied by objects.

Report o/p look something like this...


####################################################### 
(I) AWR Snapshots Information 
######################################################## 

***************************************************** 
(1a) SYSAUX usage - Schema breakdown (dba_segments) 
***************************************************** 
| 
| Total SYSAUX size 36,037.3 MB ( 88% of 40,937.9 MB MAX with AUTOEXTEND OFF ) 
| 
| Schema SYS occupies 35,717.3 MB ( 99.1% ) 
| Schema SYSMAN occupies 123.3 MB ( 0.3% ) 
| Schema SYSTEM occupies 93.1 MB ( 0.3% ) 
| Schema MDSYS occupies 56.1 MB ( 0.2% ) 
| Schema OLAPSYS occupies 15.6 MB ( 0.0% ) 
| Schema ORDDATA occupies 10.3 MB ( 0.0% ) 
| Schema WMSYS occupies 7.4 MB ( 0.0% ) 
| Schema CTXSYS occupies 6.3 MB ( 0.0% ) 
| Schema EXFSYS occupies 3.9 MB ( 0.0% ) 
| Schema DBSNMP occupies 3.1 MB ( 0.0% ) 
| Schema ORDSYS occupies 0.6 MB ( 0.0% ) 
| Schema DMSYS occupies 0.3 MB ( 0.0% ) 
| Schema APPQOSSYS occupies 0.3 MB ( 0.0% ) 
| 
******************************************************** 
(1b) SYSAUX occupants space usage (v$sysaux_occupants) 
******************************************************** 
| 
| Occupant Name Schema Name Space Usage 
| -------------------- -------------------- ---------------- 
| SQL_MANAGEMENT_BASE SYS 31,764.9 MB 

| SM/AWR SYS 2,371.9 MB 
| EM SYSMAN 123.3 MB 
| SM/ADVISOR SYS 108.1 MB 
| SM/OPTSTAT SYS 92.9 MB 
| LOGMNR SYSTEM 91.6 MB 
| SDO MDSYS 56.1 MB 
| AO SYS 31.8 MB 
| XSOQHIST SYS 31.8 MB 
| JOB_SCHEDULER SYS 22.2 MB 
| XSAMD OLAPSYS 15.6 MB 
| SM/OTHER SYS 10.8 MB 
| ORDIM/ORDDATA ORDDATA 10.3 MB 
| SMON_SCN_TIME SYS 7.4 MB 
| WM WMSYS 7.4 MB 
| TEXT CTXSYS 6.3 MB 
| EXPRESSION_FILTER EXFSYS 3.9 MB 
| PL/SCOPE SYS 3.8 MB 
| EM_MONITORING_USER DBSNMP 3.1 MB 
| LOGSTDBY SYSTEM 1.4 MB 
| STREAMS SYS 1.0 MB 
.. 
.. 
************************************************************* 
(1d) SYSAUX usage - Unaccounted space in registered schemas 
************************************************************* 
| 
| This section displays unaccounted space in the registered 
| schemas of V$SYSAUX_OCCUPANTS. 
| 
| Unaccounted space in SYS/SYSTEM 1,270.7 MB 
| 
| Total space 1,270.7 MB 
| 
************************************* 
(2) Size estimates for AWR snapshots 
************************************* 
| 
| Estimates based on 60 mins snapshot INTERVAL: 
| AWR size/day 52.6 MB (2,245 K/snap * 24 snaps/day) 
| AWR size/wk 368.3 MB (size_per_day * 7) per instance 
| 
| Estimates based on 24 snaps in past 24 hours: 
| AWR size/day 52.6 MB (2,245 K/snap and 24 snaps in past 24 hours) 
| AWR size/wk 368.3 MB (size_per_day * 7) per instance 
| 

********************************** 
(3a) Space usage by AWR components (per database) 
********************************** 

COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX% 
--------- --------- ------ ------------ ---------- ----------- ---------------- 
ASH 1,525.3 64.3 1,444 33.8 236.8 90% : 10% 
FIXED 462.1 19.5 437 10.2 71.7 55% : 45% 
EVENTS 154.8 6.5 146 3.4 24.0 52% : 48% 
SQL 78.1 3.3 74 1.7 12.1 66% : 34%


Based on report we estimate that  
| SQL_MANAGEMENT_BASE taking more space.
 



So 1st  we must delete any unwanted SQL Plan Baselines from the SMB.

Use below query to remove sql plan baselines from dataabase in chunks.


declare
pgn number;
begin
for I in (select distinct sql_handle from dba_sql_plan_baselines where rownum<1000)
loop
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle);
end loop;
end; 


Alternatively change the query by monthwise deletion in parallel session.




declare
pgn number;
begin
for I in (select distinct sql_handle from dba_sql_plan_baselines where trunc(created,'MM')=trunc(sysdate-30,'MM'))
loop
pgn := dbms_spm.drop_sql_plan_baseline(sql_handle=>i.sql_handle);
end loop;
end;

Do it for sysdaete-60,90,180...

Now we need to shrink sysaux occupants.


lter table "SYS"."SQL$" enable row movement; 
alter table "SYS"."SQL$TEXT" enable row movement; 
alter table "SYS"."SQLOBJ$AUXDATA" enable row movement; 

alter table "SYS"."SQL$" shrink space cascade; 
alter table "SYS"."SQL$TEXT" shrink space cascade; 
alter table "SYS"."SQLOBJ$AUXDATA" shrink space cascade; 

alter table "SYS"."SQL$" disable row movement; 
alter table "SYS"."SQL$TEXT" disable row movement; 
alter table "SYS"."SQLOBJ$AUXDATA" disable row movement; 

alter table "SYS"."SQLOBJ$" shrink space cascade; 
alter table "SYS"."SQLOBJ$DATA" shrink space cascade; 

alter table "SYS"."SQL$" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQL$TEXT" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQL$TEXT" modify lob ("SQL_TEXT") (shrink space cascade); 
alter table "SYS"."SQLOBJ$" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQLOBJ$DATA" modify lob ("COMP_DATA") (shrink space cascade); 
alter table "SYS"."SQLOBJ$DATA" modify lob ("SPARE2") (shrink space cascade); 
alter table "SYS"."SQLOBJ$AUXDATA" modify lob ("SPARE2") (shrink space cascade);   

Now you may also disable
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESto false.

Reset awr report retention period to 7 days or more.

For complete info by metalink note. 

https://support.oracle.com/epmos/faces/DocumentDisplay?id=1499542.1