Oracle Worklog

Just another WordPress.com weblog

Reducing Patch Downtime in Oracle EBS

Note: This content majorly based on the article Top 7 Ways of Reducing Patching Downtimes for Apps by Steven Chan and few other supporting articles.

1. Overview

Patching an Oracle Applications environment is a major maintenance activity which will generally account to significant percentage of planned downtime of production environments. This patching could be as simple as applying a one-off patch to fix a specific bug to as huge as applying a maintenance pack. In this document we will discuss various options that can be utilized to reduce the patching downtime of Oracle Applications environments.

2. Techniques to Reduce Patching Downtime

Oracle Applications provides various features that can utilized to decrease the amount of downtime required to perform patching. Some of these options require investment of extra resources in terms of extra servers and storage, while others require efficient usage of existing adpatch options and other ad utilities with proper analysis & planning.

While adopting these methods one should note that not all methods will decrease the patching time. Patch downtime majorly depends on the type of patch that we are applying, the kind of activity that the patch is performing and the patch levels of existing environment. One should also note that patching activity depends equally on both database tier resources and on application tier. Careful analysis of patching bottlenecks is vital in deciding the right mix of techniques to ensure maximum reduction of patching downtime.

It is also mandatory to note that the one can only decrease the patch downtime but not eliminate it. Very few patches can be applied as hot patches with any downtime. Often, patch README files do not mention whether a patch can be applied as a hot patch or not. One should do a complete analysis of patch activity to identify whether a patch can be as a hot patch or not.

Below are the techniques recommended by Oracle to decrease the patching downtime…

2.1 Using Staged Application File system

The Staged Application File System approach allows us to perform as many changes as possible in an offline Apps environment, and defers taking down production environment only for the final database patches tasks. Using this approach, one can apply new patches to an exact clone of production E-Business Suite environment. This can be done while production system is still running. The staged Applications environment is then used to run database updates and APPL_TOP changes into production environment.

Perquisites

  1. Topology – A staged Applications system should duplicate the topology of your Production system. Each physical APPL_TOP of your Production system must exist in your staged system.
  2. Patch Level –11.5.10 or AD.I.1 is required. For environments prior to 11.5.10 – patch 3285255

Steps Involved

Below are the high level steps involved with this process.

  1. Prepare Source Environment
    1. Maintain Snapshot information
    2. Ensure minimum patch levels as per pre-requisites
  2. Prepare Staged environment (Should be exact replica of PROD topology)
  3. Apply necessary patches to Staged environment
  4. Update Production system
    1. Update production database
    2. Update production APPL_TOP
  5. Post patching steps
    1. Export patch history on Staged environment
    2. Import patch history on Production environment.

Only step 4 requires production environment to be down and steps 4-a and 4-b can be executed in parallel. This will ensure that overall time spent required to perform patching is minimal.

Pros

  1. Can be used for patches of any size and complexity.
  2. Significant reduction for patch down time for huge patches.

Cons

  1. Requires double hardware resources – number of servers and storage.
  2. Complexity involved in the initial setup phase.

Evaluating the benefit

Below example will help us understand how Staged Applications environment will help us in reduced down time.

Example 1:

Below is an example of a one-off patch application. This patch is majorly DB bound and does little activity on the Application file system.

From this report, we can observe that 20% of total patch time is spent on Administration phase which will involve tasks like forms generation and reports generation.

These reports can be generated on the Staged Application file system (R12 Only) before hand to assess the exact amount of time that can be gained by using the Staged Application File system approach.

Phase Name Start Time End Time Elapsed Jobs
Seq

5/12/2011 9:45

5/12/2011 9:45

0:00:00

2
Tab

5/12/2011 9:45

5/12/2011 9:45

0:00:02

2
tbm: Create

5/12/2011 9:45

5/12/2011 9:45

0:00:00

2
Pls

5/12/2011 9:45

5/12/2011 9:45

0:00:01

5
Vw

5/12/2011 9:45

5/12/2011 9:45

0:00:13

8
Plb

5/12/2011 9:45

5/12/2011 9:45

0:00:09

11
daa+52

5/12/2011 9:45

5/12/2011 9:45

0:00:02

1
Dat

5/12/2011 9:45

5/12/2011 9:45

0:00:06

1
dat+1

5/12/2011 9:45

5/12/2011 9:45

0:00:12

2
upg+70

5/12/2011 9:45

5/12/2011 9:55

0:09:57

1
Administration

5/12/2011 9:56

5/12/2011 9:57

0:00:20

5
Administration

5/12/2011 9:57

5/12/2011 10:00

0:03:08

23
Total

5/12/2011 9:45

5/12/2011 10:00

0:15:10

63

References:

2.2 Use a shared application-tier file system

Creating a multi-node system with a shared application tier file system saves patching time in multiple Application Node environments, as we apply patch only once – on the primary node. This ensures minimal downtime as all adpatch actions are performed only once.

This diagram is a sample representation of a Shared Application Tier file system environment in R12 environment.

References

  • Sharing the Application Tier File System in Oracle Applications Release 11i [ID 233428.1]
  • Sharing The Application Tier File System in Oracle E-Business Suite Release 12 [ID 384248.1]
  • How To Apply Patches On Shared Application Tier File System Environment [ID 745580.1]

2.3 Use Distributed AD

Distributed AD is a parallel processing feature that can reduce downtime by efficiently utilizing all the available resources on a shared application file system. AD Administration and Auto Patch run on the primary node and direct workers running on that node and other nodes in the system. The AD Controller utility controls and monitors the actions of the workers that you specify.

This parallel processing makes use of managers, which direct the actions of worker processes. The manager assigns each worker a processing job and monitors its progress. When a worker completes a job, the manager assigns it another until all jobs are complete. AD Administration and AutoPatch can be directed to distribute processing tasks across multiple remote machines in a multi-node system. This type of parallel processing operation is called Distributed AD. It further reduces the time to complete a maintenance task by utilizing the processing capabilities of all of the nodes in the system.

Because the AD workers create and update file system objects as well as database objects, Distributed AD can only be used on systems that are using a Shared Application Tier File System to ensure that files are maintained in a single, centralized location.

Pre-requisites

  • AD.H or higher
  • Shared Application File system

Pros

  • Increased utilization of OS resources on all nodes of Shared Application systems
  • Works efficiently in environments where the middle tier resources are a bottleneck for activities like forms generation.

Cons

  • Complex to implement. Requires manual intervention in adpatch operations
  • Will only work for application tier operations. If DB tier is having resource shortage, performance improvement will not be significant.
  • Assigning more number of workers is not always a solution if the number of workers is not the bottleneck. Proper care must be taken while deciding the number of workers.

References

2.4 Merge multiple patches using AD Merge Patch

Merging patches saves time because the AutoPatch overhead of starting a new session is eliminated for those patches that are consolidated.  Duplicate linking, generating or database actions are run once only.  If two patches update the same file, AD Merge Patch will save time by applying only the latest one.  Patches can — and should — be merged with their listed prerequisite patches.

Some guide lines while considering Merge Patch:

  • Oracle DBA (AD) patches may change the AutoPatch utility itself. Always merge AD patches separately from non-AD patches.
  • Merge patches with their listed prerequisite patches to make patch application easier.
  • Individual patches can be merged with aggregated patches such as minipacks, maintenance releases, and release update packs.
  • Merged patches can be merged with other patches. This may be useful when maintaining a “current” patch which can be applied to other systems in order to bring them up to the same level.
  • Merging a patch performs any necessary system-specific character set translation.

2.5 Run AD Patch in Non-Interactive Mode

Automating the patching process by applying patches in non-interactive mode will help us reduce the delays introduced by manual intervention. This process allows you to store the responses to the patching prompts in a defaults file, and then specify the name of this file when you run AutoPatch. This is particularly helpful when applying huge number of patches.

Reference:

  • AD Command Line Options for Release R12 [ID 1078973.1]

2.6 Effective use of AD Patch options

AD Patch provides various options which can be used effectively to avoid repetitive tasks performed by AD Patch. These options will have greater benefit particularly if we are applying multiple patches separately. Alternatively we can also use merge feature of ad patches to avoid repeated execution of ad patches. Below are some of the options which can be used to optimize the adpatch runtime.

nocompiledb

This option will stop compilation of database INVLID objects after every patch.

nomaintainmrc

This option will stop maintenance of MRC schema operations after every patch.

nocompilejsp

This option will suppress compilation of jsp files.

nogenerateportion

Suppresses generate driver actions like genform, genpll…

3. Special Consideration for NLS patches

If the Oracle Applications environment is enabled with multiple NLS languages, NLS translation versions of all patches needs to be applied. This will significantly increase the overall downtime of the environment and thus needs to be handled appropriately. Below are the two most prominent techniques used for with NLS patches to reduce the downtime.

3.1 Merge NLS (Translation) Patches and Apply Them During Uptime

If there are multiple patches for multiple languages, merge all US patches into a single patch. Then, merge the NLS translation patches for each active language into a single patch for each language. Apply the US patches first during downtime. Then, the merged NLS translation patches can be applied during uptime.

3.2 Translation Synchronization

The Translation Synchronization patch feature provides a quick way to synchronize existing translations with the American English file versions on given Applications instance. By applying just one patch for each language, once can bring your translations up to current Applications patch level. You can also choose to get the latest translations to bring your translations up-to-date.

The basic concept involves preparing a single manifest file for your Applications instance.  This manifest reflects the language software content for all the active languages on your current Applications system.  The manifest will be used to generate customized patches for your system.

This feature reduces the installation time and complexity for synchronizing language installations. It also provides a simple way for you to update your existing translations, after major patching activities.

Prerequisites

  • 11i environments – 11i.AD.I.2 with patch 5837664 or 11i.AD.I.6
  • R12 environments – R12.AD.A.1

References

  • Requesting Translation Synchronization Patches [ID 252422.1]

4. Considerations for Database Tier

Often database tier operations of adpatch consume majority of the downtime. To ensure that this down time is reduces one should optimize the initialization parameters of database for patching specific workload. This will ensure that the database performance is optimally tuned for different kind of workloads like patching and normal operations. For example: Increasing the SGA /PGA sizing related parameters.

5. References

June 13, 2011 Posted by | Oracle EBS | , , , , | Leave a Comment

Automate SM/OPTSTAT component purging in SYSAUX tablespace

This post is a followup of my earlier post on handling SM/OPTSTAT component growth in SYSAUX tablespace.

Edit: Oracle has released patch 8553944  to fix this issue. Please check My Oracle Support for patch availability for your environment.

In our environment we have used a simple PL/SQL procedure to gradually purge this SM/OPTSTAT component data. As the purge process is resource intensive it was important for us to perform this purge activity in smaller chunks. Below is the PL/SQL procedure that was developed by one of my colleague (Naveen.KNS) . He gladly accepted to share this code with the user community. Thanks to Naveen!

CREATE OR REPLACE PROCEDURE "SYSTEM"."SYSAUX_PURGE_AUTO_P"
IS
HIST_DATE DATE;
TARG_DATE DATE;
RENT_DATE NUMBER;
LIMIT_DATE DATE;
BEGIN
SELECT TRUNC(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) INTO HIST_DATE FROM DUAL;
SELECT HIST_DATE + 2 INTO TARG_DATE FROM DUAL;
SELECT TRUNC(DBMS_STATS.GET_STATS_HISTORY_RETENTION) INTO RENT_DATE FROM DUAL;
SELECT SYSDATE - RENT_DATE INTO LIMIT_DATE FROM DUAL;
IF TARG_DATE < LIMIT_DATE THEN
DBMS_STATS.PURGE_STATS(TARG_DATE);
END IF;
END ;

This code will purge two days of old statistics in one run. One can schedule this procedure using any scheduling mechanism in off peak hours.

Do note that this process will ONLY purge the information but will not release the free space. You might have to perform re-org of necessary tables to reclaim the space.

Till next time…

May 31, 2011 Posted by | Database Monitoring | , , | 4 Comments

Release Content Documents for Oracle Applications 12.2

Oracle has released these documents. My Oracle Support document “Oracle E-Business Suite Releases 12.1 and 12.2 Release Content Documents [ID 1302189.1] “ has link to the necessary documents. There is quite a bit of interesting information that any Apps DBA would be excited about in those documents.

As updated in some popular blogs here and here, R12.2 will be available pretty soon. There are quite a few interesting features that one can try in 12.2 version. One should consider upgrading to 11gR2 version at the earliest to get a glimpse of those interesting features.

More details soon.

May 2, 2011 Posted by | Uncategorized | Leave a Comment

2010 in review

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads Minty-Fresh™.

Crunchy numbers

Featured image

A helper monkey made this abstract painting, inspired by your stats.

A Boeing 747-400 passenger jet can hold 416 passengers. This blog was viewed about 1,300 times in 2010. That’s about 3 full 747s.

In 2010, there were 7 new posts, not bad for the first year!

The busiest day of the year was November 16th with 26 views. The most popular post that day was Handling SM/OPTSTAT component growth in SYSAUX tablespace.

Where did they come from?

The top referring sites in 2010 were forums.oracle.com and google.com.

Some visitors came searching, mostly for ora-01400: cannot insert null into (“applsys”.”wf_local_roles_stage”.”notification_preference”), sm/optstat, oracle sysaux full, oracle database growth using dba_hist, and dba_hist_seg_stat.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Handling SM/OPTSTAT component growth in SYSAUX tablespace July 2010
1 comment

2

Free space in Auto extensible tablespaces February 2010

3

Measuring Database Space Growth February 2010

4

Resolving block corruption issues in R12 instance after clone June 2010
One execution per script April 2010

January 2, 2011 Posted by | Uncategorized | Leave a Comment

Handling SM/OPTSTAT component growth in SYSAUX tablespace

From Oracle 10g onwards, there is serious bug ( As per oracle, a feature ), due to which the SYSAUX tablespace will grow continuously. There is only way to limit this growth and it is not clearly documented anywhere. Below are the notes I prepared in this regard.

Below information is applicable to all versions from 10.1.0.2 to 11.2.

Edit: Oracle has released patch 8553944  to fix this issue. Please check My Oracle Support for patch availability for your environment.

Whats the issue:

1.From 10gR1 onwards, Oracle will maintain the backup of the optimizer statistics automatically, even if you don’t ask it to do so.

2.This backup information is stored in SYSAUX tablespace and tables involved in this are as below:

WRI$_OPTSTAT_OPR                         TABLE
WRI$_OPTSTAT_AUX_HISTORY                 TABLE
WRI$_OPTSTAT_TAB_HISTORY                 TABLE
WRI$_OPTSTAT_IND_HISTORY                 TABLE
WRI$_OPTSTAT_HISTGRM_HISTORY             TABLE
WRI$_OPTSTAT_HISTHEAD_HISTORY            TABLE

3.Default retention of this information is obtained with below command:

select dbms_stats.get_stats_history_retention from dual;

4.Default value for this setting is 30 days and can be decreased using the below command:

exec dbms_stats.alter_stats_history_retention(10);

However,

5.Purging of this information is not scheduled using any job mechanism. Its not present in DBA_JOBS and DBA_SCHEDULER_JOBS. This is handled by MMON background process internally.

6.As per the description from ML Note: SYSAUX Grows Because Optimizer Stats History is Not Purged [ID 1055547.1], MMON will do this activity once in 24 hrs and if it cannot complete this in 5 mins, it leaves the task.

7.NO messages will be written in alert log or anywhere else to indicate that this purge did not happen. With every passing day, the probability of the failure increases and it will come to a stage, where automatic purging will never complete by MMON successfully.

8.Alternate provided by Oracle for this is to run, DBMS_STATS.PURGE_STATS program to purge this stats information. This can be done manually and is safe. No timeouts involved here. only a manual termination or abnormal failure will stop the purge.

What else am I missing here?

9.Even though we set the retention, it may happen that the due to purge failure, we will accumulate huge amount of statistics history in SYSAUX, making it to grow continuously.

10.As the purge failure is not written anywhere, we might miss the fact that there is a process which is consuming space regularly.

11.PURGE_STATS process will perform a delete from these tables. Hence the free space is not released back to tablespace resulting in free space fragmentation inside this table.

How do I know that I’ve this issue?

12.Find out your present retention value using the below statement:

select dbms_stats.get_stats_history_retention from dual;

13.Find out the oldest statistics history using below statement:

select dbms_stats.get_stats_history_availability from dual;

14.Compare the above two and you’ll know what to do?

What should I do now?

15.Identify the retention in the system at the earliest and change to a proper value. 30 days is default. However, lesser values would not do much harm.

16.Turn off the Autoextend on the SYSAUX at the earliest to ensure that the tablespace doesn’t grow out of bounds and finally become complete unmanageable.

17.Start scheduling the PURGE_STATS at the earliest and keep SYSAUX tablespace size in control.

18.Perform re-org of the related tables to reclaim the space.

Why should I care?

19.With every passing day, the size of the table grows bigger and bigger, finally becoming a top space consumer. Efforts to be put in for cleanup will increase exponentially . DO it to day in 1hr or do it in 5hr tomorrow.

20.PURGE_STATS procedure does a plain delete from command with a specific where clause. This leaves lot of mess as the process will complete fully or doesn’t do any thing at all. No COMMITS at regular intervals in this code.

21.For an average database of size 150G, in 6 months time , you may end up with 100M rows in this table. Clean up of this much data is always a pain.

Any tips?

22.Identify what is the oldest information you have in the stats history using the below command.

select dbms_stats.get_stats_history_availability from dual;

23.If the information is too old, start purging the information in smaller chunks. remember that if you cancel the process in between, it will do a complete rollback before coming out.

24.Use the below command to know on which dates there was huge stats generation.

select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by  trunc(SAVTIME) order by 1;

25.Start with oldest date and invoke below command

exec dbms_stats.purge_stats(to_date('01-JAN-2010','DD-MON-YYYY'));

NOTE: Please note that this process is resource intensive. Do not perform in business hours.

26.Once the amount of data comes down to a reasonable limit schedule it in off-peak hours, as a regular maintanance activity.

27.Keep an eye on the amount of fragmentation and consider re-org process if needed.

July 24, 2010 Posted by | Database Monitoring | , , | 10 Comments

Wild behavior of wildcards

While playing with files on one of Linux server I’ve observed strange behavior with wild card “*”.

[oraxxx@vision test]$ cat a1.txt
1
[oraxxx@vision test]$ cat a2.txt
2

[oraxxx@vision test]$ ls
a1.txt  a2.txt
[oraxxx@vision test]$ cp a*.txt
[oraxxx@vision test]$ echo $?
0

This command is expected to fail as we did not provide any destination folder
But it got succeeded as the shell has translated a*.txt to “a1.txt a2.txt”
Finally “cp a*.txt” translated to “cp a1.txt a2.txt”

[oraxxx@vision test]$ cat a1.txt
1
[oraxxx@vision test]$ cat a2.txt
1

Little more play revealed more interesting behavior.

[oraxxx@vision test]$ mkdir a3
[oraxxx@vision test]$ ls -ltr
total 24
-rw-r--r-- 1 oraxxx dba    2 Jun 22 04:31 a1.txt
-rw-r--r-- 1 oraxxx dba    2 Jun 22 04:31 a2.txt
drwxr-xr-x 2 oraxxx dba 4096 Jun 22 10:26 a3
[oraxxx@vision test]$ cp a*
[oraxxx@vision test]$ echo $?
0
[oraxxx@vision test]$ ls -l `pwd`/a3
total 16
-rw-r--r-- 1 oraxxx dba 2 Jun 22 10:26 a1.txt
-rw-r--r-- 1 oraxxx dba 2 Jun 22 10:26 a2.txt

This behavior is valid as long as the wildcard character results in only 2 files. It is also valid if the last one resulted is a directory.
We just need to be careful while using commands similar to these. You never know when you’ll overwrite an important file.

June 22, 2010 Posted by | Shell Scripting | , | 1 Comment

One execution per script

We use crontab quite a lot to configure regular monitoring scripts in our environments. Most of these scripts are custom built and developed over time. Recently we have faced an issue. In this case the database was hung including our monitoring scripts. There were multiple instances of same script executing rather hung which were making the situation worst. Our challenge here was to create a logic which will ensure that there can be only one execution of script at a time on the system. Below was our answer to the issue. I thought may be every one needs such a solution.

#!/bin/sh

. /home/monitor/bin/scripts/standard.env
self=`basename $0`
if [ -f $script_log_home/$self.pid ]
then
        pi=`ps -ef | fgrep -f $script_log_home/$self.pid | grep -v grep | wc -l`
        if [ $pi -eq 0 ]
        then
                echo "FYI.. \n For $self script PID file is present but the process isn't. Removing the PID file and proceeding with monitor" | mailx -s "$hostname: Monitor continuing despite PID file existence for $self. Please crosscheck."  $maillist
        else
                echo "Alert: Previous run of monitor $self didn't complete. Please address ASAP." | mailx -s "ALERT: $hostname : Previous run of $self didn't complete."  $maillist
                exit 1
        fi
fi
echo " $$ " > $script_log_home/$self.pid

<... Script logic goes here ...>

rm $script_log_home/$self.pid

Please let us know your comments on this logic.

April 2, 2010 Posted by | Shell Scripting | , | 1 Comment

Measuring Database Space Growth

Keeping track of database space growth in production environments is a must-to-do job of every DBA. Maintaining and reporting information of historical database growth is always a task that involves good amount of planning and scheduling various jobs in the database. To achieve this many DBA’s have used a common method which includes;

  1. Create a temporary staging area to store the space usage statistics of database at the required level of detail (segment / tablespace / database / user)
  2. Schedule a weekly/monthly job to generate a consolidated report
  3. Schedule a job to purge old information from the staging table to avoid excessive space usage.

While this approach works effectively after proper testing, it has good amount of manual work involved. Also creating objects in a production database and scheduling jobs to run against always requires approval from management.

Starting from 10g, Oracle has provided us with a feature to achieve this without much overhead. Most importantly, we can use this with out incurring any extra license cost. This technique is used by many DBA’s but not much documentation is available over the internet for easy use.

Database Space growth using DBA_HIST tables:

Oracle AWR by default collects the information about the segment growth periodically. This information can be queried using views DBA_HIST_SEG_STAT. Below is a small code snippet which can be used to view the database growth with 1hr intervals.

Declare
	v_BaselineSize	number(20);
	v_CurrentSize	number(20);
	v_TotalGrowth	number(20);
	v_Space		number(20);
	cursor usageHist is
        	select a.snap_id,
			SNAP_TIME,
			sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
		from
			(select SNAP_ID,
				sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
			from DBA_HIST_SEG_STAT
			group by SNAP_ID
			having sum(SPACE_ALLOCATED_TOTAL) <> 0
			order by 1 ) a,
			(select distinct SNAP_ID,
				to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
			from DBA_HIST_SNAPSHOT) b
		where a.snap_id=b.snap_id;
Begin
	select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
	select sum(bytes) into v_CurrentSize from dba_segments;
	v_BaselineSize := v_CurrentSize - v_TotalGrowth ;

	dbms_output.put_line('SNAP_TIME           Database Size(MB)');

	for row in usageHist loop
        	v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024);
		dbms_output.put_line(row.SNAP_TIME || '           ' || to_char(v_Space) );
	end loop;
end;

The above code extracts information from DBA_HIST tables. Most of the DBA_HIST tables are part of Diagnostics Pack and should be accessed only if you have the license. But Oracle documentation has excluded the above accessed views from the diagnostics pack and these can be accessed with out any special license.

Below is the snippet from the Oracle licensing document for reference:

All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables, except for these views that you can use without the Diagnostic Pack license: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT.

Segments with highest growth (Top n):

Below is a query which can be used to query segments with highest growth. This will also report the present size of the segment which is very useful in identifying the growth percecntage.

SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
	t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",
	(SELECT sum(bytes)/(1024*1024)
	FROM dba_segments
	WHERE segment_name=o.object_name) "Total Size(MB)"
FROM DBA_OBJECTS o,
	( SELECT TS#,OBJ#,
		SUM(SPACE_USED_DELTA) growth
	FROM DBA_HIST_SEG_STAT
	GROUP BY TS#,OBJ#
	HAVING SUM(SPACE_USED_DELTA) > 0
	ORDER BY 2 DESC ) s,
	v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
AND rownum < 51
ORDER BY 6 DESC
/

The above query can be customized to find top segments in a specific tablespace or for a specific user as well.

By using DBA_HIST tables to find database growth has many advantages over the traditional method.

  • No need to schedule any jobs for data collection. AWR will do it for you automatically
  • No need to think about old data purging as it is done automatically.
  • No extra licensing cost as this feature is available automatically.


February 26, 2010 Posted by | Database Monitoring | , | 4 Comments

Free space in Auto extensible tablespaces

For any database administrator monitoring the free space is one of the primary jobs. Even though enterprise manager and db console will help the DBA with its monitoring capabilities and manageability, there are few gaps which need to be managed by DBA manually. I had to face one of such situation.

Environment was as below:

  • 11g ASM, 10g Databases
  • Database with more than 300 tablespaces and 1500+ data files.
  • Most of the data files are in auto-extend mode.
  • Sizes of tablespaces vary across wide range with smallest tablespace being 200MB in size and largest being more than 200GB.

For this environment our challenge was to maintain at least 10% of free space on all tablespaces. Management needs to be alerted well in advance to initiate the process of allocating SAN luns for ASM. As most of the data files were in auto extend mode, GRID control was not the best fit. As an immediate solution we have developed below script which provides the exact free space in each tablespace after considering the growth possible via Auto-extend.

WITH my_ddf AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes &gt; BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   my_ddf.tablespace_name,
         ROUND (SUM (my_ddf.mysize) / (1024 * 1024)) totsize,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) dfs,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) totfree,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (my_ddf.mysize)
                 * 100
               ) perc
    FROM my_ddf, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE my_ddf.file_id = dfs.file_id(+)
         AND my_ddf.tablespace_name NOT LIKE '%UNDOTB%'
GROUP BY my_ddf.tablespace_name
ORDER BY 6 DESC

Above script will report the free space percentage in each tablespace correctly after considering the growth possible by Auto-Extend. Thought of sharing this with community as I couldn’t find a similar script in the net.

Edit: It seems there are few hits for this requirement in the net. Some how I might have missed them. One of them was from Oracle Magazine.

February 23, 2010 Posted by | Uncategorized | , , | 1 Comment

« Previous Entries    

Follow

Get every new post delivered to your Inbox.