Since the last few days i am only hearing discouraging news about the US economic slowdown and a possible recession in the coming days , but there is one news item that will bring a smile to all the Oracle DBAs !
According to The Dice Report, the latest snapshot on the technology job market published by dice.com for the month of March 2008, Oracle Database is the skill thats the most in demand. This is certainly heartening especially for the Oracle DBA community.
Click here for more details....
Friday, March 14, 2008
Oracle Business Intelligence (Discoverer 10g) Linux Migration
Introduction
As part of the ongoing Linux Migration project, we successfully migrated the Oracle Business Intelligence (Discoverer 10g) from HPUX to Linux last weekend. The Discoverer migration is straightforward for the most part and many steps can be done well in advance to avoid increased system downtime during the cutover. Most of the procedure involves a new linux installation and configuration and only a part of the procedure involves “actual migration” of files from the existing source HPUX platform to the target linux platform. Below are the high level steps that we followed.
Advance Steps
- Our existing version on HPUX is 10.1.2.0.2. We downloaded the same Linux x86 version from the OTN site. Oracle Discoverer 10g is also called Oracle Business Intelligence SE (Standard Edition).
- Install the Linux 10.1.2.0.2 software on the target linux host.
- Create $ORACLE_HOME/discoverer/secure directory
- To provide transparency and a much more friendly user URL to end users, we got a DNS alias for the linux host. After the installation, $ORACLE_HOME/chgip/scripts/chgiphost.sh -mid is run to update the discoverer installation's configuration files with the DNS alias (Compare it with adautocfg.sh in Oracle E-Business Suite 11i.
Cutover Steps (Downtime)
- Shutdown the discoverer processes on the existing HPUX platform.
- Migrate the discoverer preferences from the source HPUX to the target Linux platform. Discoverer preferences are settings that define the Discoverer environment and control the behavior of Discoverer Plus and Discoverer Viewer. Whenever an individual user changes preferences in discoverer, thet are stored in a hidden file $ORACLE_HOME/discoverer/.reg_key.dc. This file contains values in a platform specific format and therefore needs to be converted to the target platform format.
- Backup the target linux platform’s $ORACLE_HOME/discoverer/.reg_key.dc
- Copy the $ORACLE_HOME/discoverer/.reg_key.dc from source platform to Linux as hpux_reg_key.dc
- On Linux , cd $ORACLE_HOME/discoverer/util and run the below conversion script
- perl convertreg.pl hpux_reg_key.dc $ORACLE_HOME/discoverer/.reg_key.dc
3. Apply any one-off patches on Linux that have been applied in source platform
4. Migrate tnsnames.ora and dbc files from source to Linux.
5. Start the linux discoverer processes (opmnctl startall and emctl start iasconsole)
6. Oracle Discoverer is now LIVE on Linux !
Friday, February 29, 2008
tar gzip and gunzip command examples
Introduction
This article gives you some examples of tar, gzip and gunzip commands on Linux operating system.
How do you take an export of a database that is 100GB in size whereas the disk space available to store the export dump is only 20GB ? Similarly how do you make a tar ball of the E-Business Suite APPL_TOP that is already 50GB in size whereas the disk space available is only 15GB ?. We are in the process of migrating our existing Oracle installations from the HPUX platform to Linux and for the most part, we spend time in transferring files between the platforms. 'Create and Compress' is one option that i have used successfully and i will illustrate the syntax in this post.
Full database export using parallel compression
- Login to source HPUX database host
- mknod db_full_exp.dmp p
- gzip < db_full_exp.dmp > db_full_exp.dmp.gz & nohup exp system/***** parfile=params.lst file=db_full_exp.dmp &
- Copy the above db_full_exp.dmp.gz to the target Linux database server
- gunzip db_full_exp.dmp.gz
- imp system/***** parfile=params.lst file=db_full_exp.dmp
The Oracle Applications 11i E-Business Suite Linux Migration involves copying several source directories from the source platform to the target Linux platform. The below example deals with the $APPL_TOP copy. Same syntax can be used for other directories that should be migrated. Please replace the "PIPE" with the actual PIPE symbol on the keyboard.
- On source HPUX node, cd $APPL_TOP
- tar cvf - . PIPE gzip > /any_directory_having_free_space/appl_top_files.tar.gz
- Copy the appl_top_files.tar.gz to the target Linux node
- On target Linux node, cd $APPL_TOP (directory should be empty)
- gunzip -c appl_top_files.tar.gz PIPE tar xvf -
A database administrator should automate processes and tasks and that extra knowledge in programming comes a long way in accomplishing these goals. Mastery in advanced unix commands like awk and sed will also help eliminate manual tasks for the most part. Ease in shell scripting or perl scripting will help one spend less time in routine tasks thereby giving more time for other important and priority activities.
Tuesday, February 19, 2008
Oracle Index Monitoring Benefits
Introduction
There are many new features available in Oracle 9i and 10g database releases. Out of the these many new features, a few standout from the crowd and 'Index Monitoring' is one of them. It is introduced in the 9i release and its one of my favorites in Oracle. Whereas the benefits are enormous, the risks are minimal to zero.
All database indexes need to be maintained in Oracle whether they are being used by a SQL query execution plan. The primary purpose of creating indexes is to reduce the cost of an sql operation thereby increasing performance and response times in the database. Sometimes database developers create indexes that will be never be used by Oracle.
Example 1 :- Creating index on a column that will be used only in the select clause and never used in the where clause of an sql statement (non-driving columns).
Example 2 :- Another common example is when the cost based optimizer decides that a table access or an alternate index/composite index is a better access path than using the index in question .
One of the most easy to implement Oracle 9i features (and 10g) has been 'Index Monitoring'. The feature identifies all unused indexes in the database and updates a data dictionary view with this information. A DBA in coordination with the development team can review these results and proceed to drop these indexes.
Benefits of dropping unused indexes
1. Faster DML operations
Database table insert/update/delete statements can be executed quickly without the need for Oracle to insert/update/delete the corresponding indexes in the background.
2. Reduced Database size
Indexes occupy the next highest storage after tables in most transactional databases. If large indexes are found unused, they can help reduce the overall database size.
3. Less RedoLog consumption
All indexes update/insert/delete get logged (if they are created with the default LOGGING option - which is the norm rather than an exception ) in the redologs. If unwanted/redundant indexes are dropped, less redologs are switched per day thereby increasing overall database performance by a good multiple.
4. Reduced archive space
More redologs caused by unused indexes mean more archivelog files.
5. Faster database backups
Dropping unused indexes will decrease the database size. Lesser sized databases take less time for their backup.
6. Reduced recovery time
Less archive logs translates to lesser archive logs to apply in database recovery operations.
Implementation Procedure
1. Identify all hot custom indexes which are of greater than a threshold size (e.g 100MB)
2. For each of the above indexes, the following command need to be executed
alter indexmonitoring usage;
3. Let Oracle monitor the indexes usage for 24 hrs. The data dictionary view v$object_usage gets updated if the index has been used atleast once in the last 24 hrs. (Here 24 hrs is just an example. Some sites may need 48 hrs of monitoring or even upto 7 days of monitoring. )
4. Turn off index monitoring using executing the following command
alter indexnomonitoring usage;
5. Get all unused indexes reported in the view v$object_usage
Are there any dependencies ? Is downtime needed for Index Monitoring ?
Index Monitoring has no dependencies with any database operations. It can be dynamically implemented with a very little overhead. There is also no downtime required for the implementation.
Caveats
1. Implementing Index Monitoring in development and test databases may not yield good results or may not be used as a basis for production implementation. Whereas several indexes may be shown as unused in development databases, the same indexes may be used more frequently in production because of the higher transaction rate.
2. Only custom indexes need to be considered for index monitoring. Its not advisable to monitor indexes that come in a pre-seeded database similar to the Oracle 11i E-Business Suite database.
3. The actual Index Monitoring is entirely done by Oracle without a DBA intervention. In ideal cases, Oracle may not even report any unused/redundant index. But this is very rare and happens only in perfect database design cases. So the index monitoring exercise may end up without any unused/redundant indexes in perfect design databases.
4. Sometimes an index is used exclusively for a weekend job or a monthend job to improve its performance. Exercise sufficient caution before dropping unused indexes reported in v$object_usage. Confirmation has to be taken from the index owner that there is no special purpose for the concerned index.
Conclusion
Database upgrades and migrations to new releases have to be performed with a pro-active frame of mind instead of a reactive frame of mind. I have seen many Oracle upgrades and migrations performed just to meet Oracle support deadlines or to meet a prerequisite for other software components. New features like Index Monitoring come with the least risks and lot of benefits. If you are starting a 'reorg' exercise in your database, Index Monitoring can be a good starting point.
There are many new features available in Oracle 9i and 10g database releases. Out of the these many new features, a few standout from the crowd and 'Index Monitoring' is one of them. It is introduced in the 9i release and its one of my favorites in Oracle. Whereas the benefits are enormous, the risks are minimal to zero.
All database indexes need to be maintained in Oracle whether they are being used by a SQL query execution plan. The primary purpose of creating indexes is to reduce the cost of an sql operation thereby increasing performance and response times in the database. Sometimes database developers create indexes that will be never be used by Oracle.
Example 1 :- Creating index on a column that will be used only in the select clause and never used in the where clause of an sql statement (non-driving columns).
Example 2 :- Another common example is when the cost based optimizer decides that a table access or an alternate index/composite index is a better access path than using the index in question .
One of the most easy to implement Oracle 9i features (and 10g) has been 'Index Monitoring'. The feature identifies all unused indexes in the database and updates a data dictionary view with this information. A DBA in coordination with the development team can review these results and proceed to drop these indexes.
Benefits of dropping unused indexes
1. Faster DML operations
Database table insert/update/delete statements can be executed quickly without the need for Oracle to insert/update/delete the corresponding indexes in the background.
2. Reduced Database size
Indexes occupy the next highest storage after tables in most transactional databases. If large indexes are found unused, they can help reduce the overall database size.
3. Less RedoLog consumption
All indexes update/insert/delete get logged (if they are created with the default LOGGING option - which is the norm rather than an exception ) in the redologs. If unwanted/redundant indexes are dropped, less redologs are switched per day thereby increasing overall database performance by a good multiple.
4. Reduced archive space
More redologs caused by unused indexes mean more archivelog files.
5. Faster database backups
Dropping unused indexes will decrease the database size. Lesser sized databases take less time for their backup.
6. Reduced recovery time
Less archive logs translates to lesser archive logs to apply in database recovery operations.
Implementation Procedure
1. Identify all hot custom indexes which are of greater than a threshold size (e.g 100MB)
2. For each of the above indexes, the following command need to be executed
alter index
3. Let Oracle monitor the indexes usage for 24 hrs. The data dictionary view v$object_usage gets updated if the index has been used atleast once in the last 24 hrs. (Here 24 hrs is just an example. Some sites may need 48 hrs of monitoring or even upto 7 days of monitoring. )
4. Turn off index monitoring using executing the following command
alter index
5. Get all unused indexes reported in the view v$object_usage
Are there any dependencies ? Is downtime needed for Index Monitoring ?
Index Monitoring has no dependencies with any database operations. It can be dynamically implemented with a very little overhead. There is also no downtime required for the implementation.
Caveats
1. Implementing Index Monitoring in development and test databases may not yield good results or may not be used as a basis for production implementation. Whereas several indexes may be shown as unused in development databases, the same indexes may be used more frequently in production because of the higher transaction rate.
2. Only custom indexes need to be considered for index monitoring. Its not advisable to monitor indexes that come in a pre-seeded database similar to the Oracle 11i E-Business Suite database.
3. The actual Index Monitoring is entirely done by Oracle without a DBA intervention. In ideal cases, Oracle may not even report any unused/redundant index. But this is very rare and happens only in perfect database design cases. So the index monitoring exercise may end up without any unused/redundant indexes in perfect design databases.
4. Sometimes an index is used exclusively for a weekend job or a monthend job to improve its performance. Exercise sufficient caution before dropping unused indexes reported in v$object_usage. Confirmation has to be taken from the index owner that there is no special purpose for the concerned index.
Conclusion
Database upgrades and migrations to new releases have to be performed with a pro-active frame of mind instead of a reactive frame of mind. I have seen many Oracle upgrades and migrations performed just to meet Oracle support deadlines or to meet a prerequisite for other software components. New features like Index Monitoring come with the least risks and lot of benefits. If you are starting a 'reorg' exercise in your database, Index Monitoring can be a good starting point.
Friday, February 1, 2008
Oracle Applications 11i Middle Tiers Linux Migration
We successfully migrated our Oracle Applications 11i (11.5.10.2) middle tiers (forms and web) from HP-UX to Linux during the weekend. We followed the standard Metalink Note :- 238276.1 for the Linux Migration. Our admin tiers and database instances were on HP-UX and these were left alone during the migration. Overall it has been a smooth cutover, however we did face a SEV 1 issue during the GOLIVE activities. We were able to get help within the maintenance window and thereby were able to release the instance on time. The details are below
ORA-01403: No Data Found...
After completing all the tasks and starting the applications on the target linux servers, none of the forms were opening. Every click resulted in a "ORA-01403: No Data Found" error. We reviewed all the tasks and everything was run fine. However we were convinced that this error could be a result of just one file or one wrong parameter. Soon after a thorough search on the Oracle Metalink site, we found an article that says an incorrect custom.pll can cause this error while trying to open any form or click on any form button. After getting this vital hint, we were able to demonstrate that its indeed the custom code that is causing the problem. The following method was used to confirm this.
- Login to forms
- If "ORA-01403: No Data Found" occurs on every form click or form button click, then escape the error window, Goto Help menu => Diagnostics => custom code => The option here will be NORMAL. Choose OFF.
- Now navigate forms again. The error no longer showed up after turning off custom code.
After narrowing down to the bottom of the problem, we soon found that a pll file added along with CUSTOM.pll for some changes was not compiled on linux. The problem went away after compiling this pll too.
Conclusion
adadmin program compiles only the standard code and hence custom code compilation has to be taken care of seperately after the standard code compilation. Failure to compile any custom code files can result in the above problem.
Friday, January 11, 2008
Oracle STATSPACK recreation
Introduction
The PERFSTAT schema (and tablespace) contains all the database’s performance statistics. However over a period of time, this tablespace can grow to a large size and the stored statistical data can be purged to make room for more freespace in this tablespace. Occasionally a complete reorganization of the PERFSTAT tablespace can be performed to delete all the historical statistics data, thus reclaiming disk space consumed for this tablespace.
At first glance, it is easy to think that this reorg procedure can be done hot (while the database applications are up and running). But its not quite true. This post explains the "behind the scenes" part of STATSPACK reorganization and why its not a straightforward procedure.
Background
Our 9.2.0.6 Oracle Applications database's PERFSTAT tablespace grew more than 50GB in size with all the thousands of statspack snaps in the schema and its just the right time for a purge and reorganization of this tablespace. Since perfstat schema is not used by any other jobs except the statspack.snap cronjob, it looked like a straight forward operation and which can be done online. Based on this flawed reasoning, the reorg was completed in our development instance. Soon there were users complaining that they were getting java exceptions once they login to Oracle Applications 11i. All the exceptions were due to the DBMS_UTILITY package getting invalidated. Because statspack could not have done this (this was the flawed assumption until then), we guessed it could have been a result of bad code migration from the applications team.
Whats the unexpected
There were no changes nor were any code migrations from the applications team. Upon further investigation, it was identified that the reinstallation of STATSPACK environment (spcreate.sql) invalidates critical DBMS packages including DBMS_UTILITY, DBMS_SNAPSHOT, DBMS_IREFRESH, UTL_RECOMP, DBMS_STATS, DBMS_PRVTAQIP, DBMS_PCLXUTIL etc and we recompiled them immediately to solve the problem. The metalink note 392352.1 mentions this is an expected outcome, but nothing of that sort is written in the official guide.
There is no "big change" or "small change". Every change should be given due respect and tested properly before GOLIVE or else expect the unexpected !
The PERFSTAT schema (and tablespace) contains all the database’s performance statistics. However over a period of time, this tablespace can grow to a large size and the stored statistical data can be purged to make room for more freespace in this tablespace. Occasionally a complete reorganization of the PERFSTAT tablespace can be performed to delete all the historical statistics data, thus reclaiming disk space consumed for this tablespace.
At first glance, it is easy to think that this reorg procedure can be done hot (while the database applications are up and running). But its not quite true. This post explains the "behind the scenes" part of STATSPACK reorganization and why its not a straightforward procedure.
Background
Our 9.2.0.6 Oracle Applications database's PERFSTAT tablespace grew more than 50GB in size with all the thousands of statspack snaps in the schema and its just the right time for a purge and reorganization of this tablespace. Since perfstat schema is not used by any other jobs except the statspack.snap cronjob, it looked like a straight forward operation and which can be done online. Based on this flawed reasoning, the reorg was completed in our development instance. Soon there were users complaining that they were getting java exceptions once they login to Oracle Applications 11i. All the exceptions were due to the DBMS_UTILITY package getting invalidated. Because statspack could not have done this (this was the flawed assumption until then), we guessed it could have been a result of bad code migration from the applications team.
Whats the unexpected
There were no changes nor were any code migrations from the applications team. Upon further investigation, it was identified that the reinstallation of STATSPACK environment (spcreate.sql) invalidates critical DBMS packages including DBMS_UTILITY, DBMS_SNAPSHOT, DBMS_IREFRESH, UTL_RECOMP, DBMS_STATS, DBMS_PRVTAQIP, DBMS_PCLXUTIL etc and we recompiled them immediately to solve the problem. The metalink note 392352.1 mentions this is an expected outcome, but nothing of that sort is written in the official guide.
- Take a list of the invalid objects from dba_objects view
- Reorg the statspack environment (drop it and recreate it)
- Take a new list of the invalid objects and compare it with the list taken above. Recompile the new objects that are invalid after the reorg.
There is no "big change" or "small change". Every change should be given due respect and tested properly before GOLIVE or else expect the unexpected !
Friday, December 21, 2007
How to generate Oracle trace
Introduction
The real test to measure a database administrator's troubleshooting skills is during P1 issues and emergencies. We were in such a situation for one of our batch processing 10g database the other day. The batch processing application failed to start after the regular restart every sunday. Upon further investigation the application logfile contained 'ORA-01031: insufficient privileges' error. Looking at it for the first time made me think it was a "table/synonym access or package execution permission that was missing'. But its practically not possible to find the user and database object that is causing this problem. Time was running out and the batch processing has come to a halt because of this issue. At this time i realized i can use the ORA-error stack trace option to find the source of the error.
Procedure
ORA-01031: insufficient privileges
Current SQL statement for this session:
alter system set optimizer_index_cost_adj =20
Bang on Target !!! The error is because the application start program was running 'alter system set optimizer_index_cost_adj =20'. Any 'alter system' command should be run by only privileged users (sysdba, and sometimes system depending on the type of command). The application team admitted to have inadvertently added this 'alter system' command instead of the planned 'alter session' command. Soon the error was corrected and the batch processing started again.
Caveats
Disable the error tracing as soon as the problem is reproduced and logfile is generated in the user dump directory. Failure to disable the stack trace in a timely fastion can fillup the user dump destination directory.
Conclusion
Database wide errors(such as a backup failure or a tablespace out of freespace) are reported in the alertlog and therefore easy to identify leading to a faster rootcause identification. However ORA-errors caused by user sessions are not reported in database server logfiles and hence the rootcause/source of error is not easy to find out. Enabling a stack trace for such an ORA error will help the database produce a logfile in the udump directory which can be analyzed to find the source. A DBA can heave a sigh of relief after resolving a production priority or emergency issue, but a speedy resolution and identification of rootcause goes a long way in increasing the DBA's reputation.
The real test to measure a database administrator's troubleshooting skills is during P1 issues and emergencies. We were in such a situation for one of our batch processing 10g database the other day. The batch processing application failed to start after the regular restart every sunday. Upon further investigation the application logfile contained 'ORA-01031: insufficient privileges' error. Looking at it for the first time made me think it was a "table/synonym access or package execution permission that was missing'. But its practically not possible to find the user and database object that is causing this problem. Time was running out and the batch processing has come to a halt because of this issue. At this time i realized i can use the ORA-error stack trace option to find the source of the error.
Procedure
- Login to the database (sqlplus '/ as sysdba ) and issue the following command
- alter system set events '1031 trace name errorstack level 3';
- Let the application team retry their application startup command/script
- It fails again, but this time a trace file will be generated in the user_dump_destination directory(udump)
- Goto the udump directory and view the stack trace.
- Login to the database (sqlplus '/ as sysdba ) again to turn off the stack trace
- alter system set events '1031 trace name context off';
In our case the stack trace had the following contents
ksedmp: internal or fatal errorORA-01031: insufficient privileges
Current SQL statement for this session:
alter system set optimizer_index_cost_adj =20
Bang on Target !!! The error is because the application start program was running 'alter system set optimizer_index_cost_adj =20'. Any 'alter system' command should be run by only privileged users (sysdba, and sometimes system depending on the type of command). The application team admitted to have inadvertently added this 'alter system' command instead of the planned 'alter session' command. Soon the error was corrected and the batch processing started again.
Caveats
Disable the error tracing as soon as the problem is reproduced and logfile is generated in the user dump directory. Failure to disable the stack trace in a timely fastion can fillup the user dump destination directory.
Conclusion
Database wide errors(such as a backup failure or a tablespace out of freespace) are reported in the alertlog and therefore easy to identify leading to a faster rootcause identification. However ORA-errors caused by user sessions are not reported in database server logfiles and hence the rootcause/source of error is not easy to find out. Enabling a stack trace for such an ORA error will help the database produce a logfile in the udump directory which can be analyzed to find the source. A DBA can heave a sigh of relief after resolving a production priority or emergency issue, but a speedy resolution and identification of rootcause goes a long way in increasing the DBA's reputation.
Subscribe to:
Posts (Atom)