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.

1. Take a list of the invalid objects from dba_objects view
2. Reorg the statspack environment (drop it and recreate it)
3. 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.

Conclusion

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 !

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

1. Login to the database (sqlplus '/ as sysdba ) and issue the following command
2. alter system set events '1031 trace name errorstack level 3';
3. Let the application team retry their application startup command/script
4. It fails again, but this time a trace file will be generated in the user_dump_destination directory(udump)
5. Goto the udump directory and view the stack trace.
6. Login to the database (sqlplus '/ as sysdba ) again to turn off the stack trace
7. alter system set events '1031 trace name context off';

In our case the stack trace had the following contents

ksedmp: internal or fatal error
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.

How to rename a datafile which has blank characters or whitespace

Introduction

One of my earlier posts has the procedure that describes how to rename a datafile. A similar but slightly different procedure is when a datafile has newline or whitespace character in it. Obviously its not a great practice to create a datafile with newline or whitespace characters is in it. This post describes how to remove a newline or any special characters in datafiles.

Problem

In our production database there is a datafile that has 1 newline and 4 whitespace characters in it. It was created long ago by a DBA by mistake. Due to these special characters, every database clone procedure has a special task to rename this datafile before creating the target database's controlfile. This extra manual step can be avoided if the datafile is renamed in the production database.

Solution

1. select file_name
from dba_data_files
where file_name like '%appsprod_custcpsinx03%';

/dbf07/oracle/appsproddata/
custcpsprod_inx03.dbf

2. Note the above newline and the whitespace characters.

3. select dump(name)
from dba_data_files
where file_name like '%custcpsprod_inx03.dbf%'
DUMP(NAME)--------------------------------------------------------------------------------Typ=1 Len=57: 47,100,98,102,48,55,47,111,114,97,99,108,101,47,97,112,112,115,112,114,111,100,100,97,116,97,47,10,32,32,32,32,97,112,112,115,112,114,111,100,95,99,117,115,116,99,112,115,105,110,120,48,51,46,100,98,102

4. The above dumps all the ascii equavalent values of the characters in the datafile.
5. http://www.columbia.edu/kermit/ascii.html can be referred for ascii values and their equavalents.

6. 10 is newline and 32 is whitespace. Therefore the above datafile has 1 newline and 4 whitespace characters.
7. Shutdown the database
8. cd /dbf07/oracle/appsproddata/
9. ls –lrt *appsprod_custcpsinx03.dbf
10. -rw-r----- 1 oracle dba 3145736192 Nov 29 19:35
appsprod_custcpsinx03.dbf
11. mv *appsprod_custcpsinx03.dbf appsprod_custcpsinx03.dbf
12. Login as 'sqlplus '/ as sysdba' and issue 'startup mount'
13. Verify the existing datafile entry in the controlfile – select name from v$datafile where name like ‘%appsprod_custcpsinx%’; - It still has the old entry in the controlfile
14. alter database rename file '/dbf07/oracle/appsproddata/
appsprod_custcpsinx03.dbf '
to
'/dbf07/oracle/appsproddata/appsprod_custcpsinx03.dbf'

15. In the above alter database statement, exactly one newline and exactly 4 whitespace characters have to be entered. Otherwise controlfile will not recognize the datafile.

16. alter database open

Conclusion

Whereas Oracle allows a datafile to have any number of newline or whitespace characters, it is not a great practice to create datafiles with these special characters and as noticed above it is not a straightforward rename exercise. Renaming any datafile requires a database bounce or atleast a 'tablespace offline' and this means downtime for business.

10g Certification IZ0-040

I obtained my 10g Certification last week. Pending since long, it completes a major requirement for that real 10g DBA. Since i am already 9i Certified Professional, upgrading to 10g wasn't such a big deal.

I found passing the 4 exams of the initial Oracle 9i OCP quite a big thing, especially dealing with the amount of books and experience needed to pass the exam. Its like passing one of your engineering semester exams as part of your Bachelor's degree. However 10g certification is going to be easy for any existing 9i certified professional. I am posting my experience with the exam here.

1. 60 questions. Atleast 44 answers have to be correct.
   
2. Questions will be independent of 10g R.1 and 10g R.2 releases. You can still pass the exam if you are just experienced in 10g R.1 and not 10g R.2 release.
3. Only new features will be tested here (including some 9i advanced new features). If one wants to pass this exam easily, one needs to study thoroughly the 10 new features guide found here.

Oracle listener password

Ever been in a situation where your database listener has been shutdown by an end user ? Yes , thats right, the default database listener configuration in 9i comes *WITHOUT* a password and any client or end user who obtains the listener name and a TNS entry(listener port and host name) , can shutdown the database server's listener sitting from that client machine !!! . All the places that i worked so far had their database server listeners' unsecured when i first checked them.

Quarterly database and apps CPU patches are recommended to fix security gaps and loopholes, but database listener security is often overlooked and easiest thing to break.

Problem



Database listeners are not always password protected. They are vulnerable to security attacks from any host in the network with a TNS information of the target database and an oracle client software. The TNS connection information is already widely distributed to users for e.g to run queries using TOAD or SQL*Navigator etc., so any user can easily *stop* database listeners inadvertently thereby comprising new database connections.

Below is a simulation of how these listeners can be stopped from a client machine
1) Obtain target database TNS connection descriptor
2) Put the connection descriptor in a client machine’s tnsnames.ora
3) Login to the client machine and issue ‘lsnrctl stop db listener name’

This stop command from the client machine will stop the database listeners on the server
Solution

Password protection is the solution for this problem. Passwords can be either encrypted or unencrypted. For maximum security the database listeners should be password protected with encryption.

Implementation

Setting Encrypted Password

1) Login to each of the database instance server host as oracle owner
2) lsnrctl
3) LSNRCTL> set current_listener listener_name3) LSNRCTL> change_password
Old password : Hit Enter KeyNew password : Type the new passwordReenter new password: Retype the new password4) LSNRCTL>set password
Password: Enter the newly created password5) LSNRCTL> save_config listener name
This will create a new passwords parameter in the $TNS_ADMIN/listener.ora and it is encrypted in the file. The old file will be backed up with a listener.bak extention.

Resetting the password if its forgotten

1) Login to the database instance server as oracle owner
2) kill -9 listener pid3) Open the listener.ora and delete the passwords_listener_name line
4) lsnrctl
5) LSNRCTL> set current_listener listener name
6) LSNRCTL> start
7) LSNRCTL> change_password
Old password: Hit Enter key
New password: Type a new passwordReenter new password: Retype the new password
8) LSNRCTL> set password
Password: Enter the newly typed password again
9) LSNRCTL> save_config listener name

Changing an existing password

1) Login to the database instance server as oracle owner
2) kill -9 listener pid
3) lsnrctl
4) LSNRCTL> set current_listener listener name
5) LSNRCTL> start
6) LSNRCTL> change_password
Old password: Enter old passwordNew password: Type a new password
Reenter new password: Retype the new password
7) LSNRCTL> set password
Password: Enter the new password again
8) LSNRCTL> save_config listener name

Listener administration with a password

start

Starting the listener does not require a password. A listener can be started in the conventional method without setting any password

stop

Stopping the listener always requires a password.

1) lsnrctl
2) LSNRCTL>set password
Password: Enter the password3) stop listener name

status

A password is also required for finding the status of a listener

1) lsnrctl
2) LSNRCTL>set password
Password: Enter the password
3) status listener name
Testing

1) Obtain the database TNS descriptor information
2) Put the connection descriptor in a client machine’s tnsnames.ora
3) Login to the client machine and issue ‘lsnrctl stop ’
The stop command will fail with the following message.

TNS-01169: The listener has not recognized the password

Uninstall (Status Quo)

1) Login to the database instance server as oracle owner
2) kill -9 listener pid
3) Open the listener.ora and delete the passwords_listener_name line
4) lsnrctl
5) LSNRCTL> set current_listener listener name
6) LSNRCTL> start

Caveats

1) A client can still stop/status a listener after setting the password explicitly. Hence its always recommended to choose a strong password with encryption.

How to Change APPS Password without running ADCONFIG

Any other ORACLE database user password can be changed simply by the 'alter user username identified by xxxxx' command. However the APPS user password change procedure in an Oracle Applications 11i instance has got a few more additional steps. In this post i will outline all the steps required without running the adconfig utility as well as the verification process after changing the password.

 

APPS password change procedure WITHOUT running adconfig

1. Login to any of the admin tiers of the 11i instance
2. FNDCPASS apps/old_pwd 0 Y system/sys_pwd SYSTEM APPLSYS new_pwd
3. Edit $ORACLE_HOME/reports60/server/CGIcmd.dat and update with the new password
4. Login to all of the middle tiers of the 11i instance (or just one of them if its a shared appl tier configuration)
5. Edit the following files and update them with the new password
6. $FND_TOP/bin/appsweb.cfg
7. $FND_TOP/resource/wfmail.cfg
8. $ORACLE_HOME/reports60/server/CGIcmd.dat
9. $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app
10. Recreate any database links that are owned by the apps user
11. Update any 3rd party tools, interfaces or scripts that have the password hardcoded

APPS password change procedure WITH adconfig

1. Login to any of the admin tiers of the 11i instance
2. FNDCPASS apps/old_pwd 0 Y system/sys_pwd SYSTEM APPLSYS new_pwd
3. Backup any customizations in the configuration files
4. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME and run adautocfg.sh
5. Restore the customizations to the configuration files
6. Login to all of the middle tiers of the 11i instance (or just one of them if its a shared appl tier configuration)
7. Backup any customizations in the configuration files
8. cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME and run adautocfg.sh
9. Restore the customizations to the configuration files
10. Recreate any database links that are owned by the apps user
11. Update any 3rd party tools, interfaces or scripts that have the password hardcoded

Verification

1. Login to the database as apps user
2. Complete the AOL/J diagnostic test. This webpage asks for the new apps password and tries to complete a jdbc connection. http://11i_web_URL:port/OA_HTML/jsp/fnd/aoljtest.jsp

Conclusion

There may be lot of customizations in the configuration files that are generated by the adautocfg.sh. Saving and restoring them each and every time adautocfg.sh is run is the key to a successful apps password change procedure when adconfig is run (adautocfg.sh). If one is not sure about all the customizations in the configuration files, one can choose the first procedure above WITHOUT running adconfig.

RMAN Duplication to a new host using Veritas Netbackup in Oracle 9i

We use Veritas Netbackup 5.1 as the Media Management Software for creating our production tape backups. Yesterday, I successfully created a RMAN DUPLICATE database on a different host using these tape backups .I am documenting the procedure below

NAMING CONVENTIONS

1)RMAN target = source database of the RMAN backups
2)duplicate database = destination database that needs to be created from the source database backup
3)auxiliary database = duplicate database
6)RMAN repository = separate schema (RMAN catalog) in a separate database that contains source database’s backup details
7)dev7 is the name of the duplicate database to be created
8)Media Manager Server = Netbackup Media Manager Server.
9)Media Manager Client = source database host
10)PROD = source database name
11)DEV = duplicate database name
12)RMANCT = RMAN catalog database name
13)RMANPROD = RMAN catalog repository schema name

CREATE DESTINATION DATABASE osuser and group SIMILAR to SOURCE DATABASE osuser and group

One of the requirements of RMAN duplication using VERITAS NETBACKUP is that the destination database's osuser and group should be similar to what it is in the source database node

e.g If source database ORACLE_HOME owner is oracle:dba, the destination database ORACLE_HOME owner should also be oracle:dba

MAKE BACKUP TAPES accessible to DESTINATION HOST

1)Contact your NETBACKUP system administrators to the source database BACKUP TAPES accessible to DESTINATION HOST

2)Verify by the following command

a) Login oracle@destination_db_host
b) /usr/openv/netbackup/bin/bplist -C source_db_host -t 4 -l -R /

The above bplist command should list all the backup sets that are in the RMAN catalog

Prepare Duplicate database

1)Login oracle@destination_db_host
2)Copy the source database's initSID.ora to the destination host and add a new parameter DB_FILE_NAME_CONVERT. For e.g if the source database files are in /oracle/prod/d1 and /oracle/prod/d2 directories and if the corresponding restoration paths in the destination host are to be /oracle/dev/d1 and /oracle/dev/d2 respectively,

DB_FILE_NAME_CONVERT=(/oracle/prod/d1/,/oracle/dev/d1/,/oracle/prod/d2,/oracle/dev/d2/)

3)sqlplus '/ as sysdba'

4)startup nomount

5)Edit the $TNS_ADMIN/tnsnames.ora and add connection descriptors for the source database PROD and its catalog database RMANCT

Run the DUPLICATE command

RMAN always restores from the latest tape backups by default to create new database with the DUPLICATE command.

1)Login to oracle@destination_host

export NB_ORA_CLIENT=source_host

2)Connect to RMAN

rman LOG=/tmp/client/dev.log
connect target sys/***@prod
connect catalog rmanprod/****@rmanct
connect auxiliary /


3)Issue the Duplicate commands

RUN
{
allocate auxiliary channel t1 type 'SBT_TAPE';
allocate auxiliary channel t2 type 'SBT_TAPE';
SEND 'NB_ORA_SERV=media manager server, NB_ORA_CLIENT=source_host';
duplicate
target database to dev
pfile=/ora_base/oracle/devdb/9.2.0/dbs/initdev.ora
logfile
'/oracle/dev/logs/dev_log1.dbf' size 256M,
'/oracle/dev/logs/dev_log2.dbf' size 256M,
'/oracle/dev/logs/dev_log3.dbf' size 256M
}


4)Verify the dev alert log and the RMAN log /tmp/client/dev.log
5) The whole restore process will happen over the network depending on the network speed and the source database size

Restoration of only archive logs from RMAN

1) Login oracle@destination_host
2) select * from v$instance ( it should show ‘MOUNTED’ status)
3) shutdown immediate
4) Login oracle@source_host
5) Restore the archivelog files with a separate RMAN command.

rman
connect target /
connect catalog rmanprod/****@rmanct
run
{
allocate channel t1 type 'SBT_TAPE';
set archivelog destination to '/home/oracle/';
restore archivelog
from time "to_date('09/24/07 8:00:00', 'mm/dd/yy hh24:mi:ss')" until time "to_date('09/24/07 13:00:00', 'mm/dd/yy hh24:mi:ss')";
}

7)Verify the restored archive logfiles in /home/oracle/
8)Copy them to destination node using rcp or scp command
9)Login to destination node oracle@destination_host
10)Mount the destination database dev and recover manually using the 'recover database until cancel using backup controlfile' command
11)Type ‘cancel’ after applying the necessary archive logfiles to make the destination database consistent
12) alter database open resetlogs
13) Create the temporary tablespace

How to create a duplicate database until a specific time in the RMAN repository

The procedure remains the same except one more ‘set until’ command. The set until time command has to be put in the run block just before the duplicate command to create the duplicate database upto a specific point in time.

set until time= "to_date('mm/dd/yyyy hh24:mi:ss', 'MM/DD/YYYY HH24:MI:SS')";

Affect of Duplicate command on Recovery Catalog

There is no affect of duplication on the recovery catalog. RMAN generates a new DBID for the duplicate database. Unless this DBID is registered in the catalog, RMAN does not create or update any records for the newly created duplicate database.

It is not necessary to register the newly created duplicate database in the RMAN catalog unless the newly created duplicate database wants to use RMAN for its own backups