What would be the first thing you would do if an end user complains that performance is poor?

I always used to come up with this question on forums and well as from the Application support, and a time with my first interview in India.
As for the answer to the question, one should touch upon all these points:
- Check alert log, for any errors and UN even entries
- Check for archive log destination/file system full
- Check for database locks # Click here for a notes on locks
- Check server performance (CPU/IO)
- Check network performance
- Check if statistics is up to date
- If new SQL, then tune SQL (maybe take trace)
- Check if any parameters have been modified recently
- Check if any new updates as been done from the Application team.

And few more from Oracle Online Documentation as well
1) Identifying and Gathering Data on Resource-Intensive SQL # Oracle Doc
2) Oracle9i Database Performance Tuning Guide and Reference # Oracle Doc
I recommend reading the documents from Oracle, which give clear understanding of all the above. Please add lines if you I miss something which is useful.

Some SQL Statements - For Day to Day tasks

1) To grep & Drop user objects before Import
select 'drop 'object_type' 'object_namedecode(object_type,'TABLE','
cascade constraints;',';')
from user_objects
where object_type!='INDEX'/
********************************************************************
PCT_FREE, PCT_USED
SQL> select TABLE_NAME, PCT_FREE, PCT_USED, EMPTY_BLOCKS, CHAIN_CNT from dba_tables
2 where CHAIN_CNT > 0;
TABLE_NAME PCT_FREE PCT_USED EMPTY_BLOCKS CHAIN_CNT
------------------------------ ---------- ---------- ------------ ----------
BBN_MAS_LIQ_DET_TSY_TMP 10 40 1977 392
ORD_LEDGER 10 40 1982 6609
************************************************************************
SQL> select object_name,object_type from user_objects where status='INVALID';
******************************************************************************

Oracle Applications DBA (Apps DBA)

Hmm as long from now being a core DBA from past 3 years I want move to Apps. Apps DBA is what Applications DBA is said to be. I want to assimilate my self for Application expertise and as well as with Oracle 10 G Rel 2.

In my searches I found this reliable site for Applications http://www.appsdba.com/ hope this will be very useful for Apps DBA.

I always found of increasing my errands and promoting myself with the market. But what Apps DBA does? Is this the question I always asks myself and hope this is the most common question for a core DBA who wants to upgrade for Apps? Yes! Hope so! :- )
I want to solve this in my blog – hope it helps to know the responsibilities of being an Apps DBA.

What duties, if any, are different for an Apps DBA versus those of an Oracle 8/9i DBA?

Oracle APPS DBA's duties are almost same as of Oracle DBA but your work will be more specific to the Architecture (or I should say Environment) and OFA of Oracle Application. Apart from that there are few more things you should know like applying patches or adadmin utility and some basic functionality of SYSADMIN responsibility of APPS.

An APPs DBA knows all that a regular DBA knows plus the following:

Application fundamentals such as concepts and architecture

Application Installation and configuration

Patching and Upgrades

Patch Management and Version Control

Customization Updates

Cloning requirements.

This can be frequent in an Apps environment- Concurrent Managers - Forms/Report Servers

Web Servers

all the "ad" utilities

all the database restrictions for the applications.

That is so that the installation is still supportable and conforms to all vendor (Oracle) requirements.

All special application related regular and maintenance functions. Such as month end and period end functions.

Understand any backup and recovery constraints. Such as for table imports (NOT) and partial recoveries.

Special requirements and restrictions for tuning, example optimizer and index requirements.

Printer Configuration

User Access AdministrationThis could be a matter for further discussion as it could be argued that all DBA's should be aware of the above or equivalent for any database application (Oracle or otherwise).

These are two core answers I found for being a Apps DBA.

Resolving a Database Failure

Resolving a Database Failure

Phase I: Diagnose the problem
Phase II: Restore appropriate files
Phase III: Recover the database
Phase IV: Back up the database

Phase I: Diagnose the problem

The first phase is to research the nature of the failure. Use V$ views, data dictionary views, trace and log files, basic operating system commands and Oracle Enterprise Manager to diagnose the problem.

Determine if the database instance is available and the database is open.

Attempt to start the instance.

Shutdown the instance if a problem occurs while starting it or when opening the database.

Check the trace files and alert log files.

Determine the appropriate recovery method:

- Closed database recovery
- Open database, offline tablespace recovery
- Open database, offline tablespace, individual datafile recovery
- Cancel-based recovery
- Time-based recovery
- Change-based recovery


Phase II: Restore Appropriate Files

Before you perform a recovery scenario, determine what files to restore and what state the instance and database must be in to perform the recovery. Remember that the objective is to minimize down time and loss of data, so do not restore files if it is not necessary.

Phase III: Recover the Database

Once the appropriate files are restored, initiate your recovery operation. After completing the recovery, note any proactive measures that can be taken to prevent that type of failure in the future.

Phase IV: Back up the Database

Not all recovery operations require a database backup when they are complete. However, determine whether your database needs to backed up and, if so, perform another backup.

Archive Log

Steps in changing database from no archive mode to archive mode:

sql/>shutdown normal/immediate/transactional (You can use any value)

sql/>startup mount

sql/>alter database archivelog

sql/>alter database open

Go for Full Database backup


Keep in mind a user must have ALTER SYSTEM PRIVILEGE to alter the archivelog mode of the database.

Setting the database in Archivelog mode doesnot enable the Archiver (ARCN) process!

AUTOMATIC & MANUAL Archiving

Set the parameter file for LOG_ARCHIVE_START=TRUE

Hmm mean while you can do multiple arcn process

LOG_ARCHIVE_MAX_PROCESSES

And a maximum of ten ARCN processes can be specified and its dynamic

Sql/>alter system set LOG_ARCHIVE_MAX_PROCESSES=3;

Well if you find more about this let me know.

Team Oracle

http://www.oracle.com/cluboracle/teamoracle/index.html

Are you with Oracle Team ???

Flying High with Team Oracle

RMAN Recovery Manager - Oracle 9i

Steps For Configuring Recovery Catalog

Step 1 :
Create A Seperate Database Or Tablespace For Storing RMAN Catalog.
In This Case We Are Storing The Recovery Catalog In The Same Database.

SQL> Create Tablespace Rman_Tablespace Datafile
2 'C:\Sql\Junept\Datafile\Rman01.dbf' Size 20m Autoextend On Next 1m;
Tablespace created.

Step 2 :
Create A Rman Repository Schema.
SQL> Create User Rman_User Identified By Rman Default Tablespace Rman_Tablespace
2 Temporary Tablespace Temp Quota Unlimited On Rman_Tablespace;
User created.

Step 3 :
Grant Privileges To The Rman User Setting The Default Tablespace To The One Create In Step 1
SQL> Grant Connect,Resource To Rman_User;
Grant succeeded.
SQL> Grant Recovery_Catalog_Owner To Rman_User;
Grant succeeded.

Step 4 :
Create The Catalog Using RMAN
Invoke Rman Utility As Below :
$ORACLE_HOME\Bin> RMAN Catalog Rman_User/Rman@Service
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to recovery catalog database
recovery catalog is not installed
RMAN>
Now At The Rman Prompt Create The Catalog Into Tablespace Created In Step 1
RMAN> Create Catalog Tablespace Rman_Tablespace;
recovery catalog created
Note : The Execution Of The Above Command Will Create Several Base Tables, View, Synonyms ,
Packages, Etc .
RMAN> Exit

Step 5 :
Now Register Your Database Into The Recovery Catalog.
RMAN_USER SQL> Select * From DB_Database;
no rows selected
The Above Statement Proves That Your Database Is Not Yet Registered Into The Recovery Catalog.
C:\>Rman Target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: JUNEPT (DBID=1185895820)
RMAN> Connect Catalog Rman_User/Rman@Service
connected to recovery catalog database
RMAN> Register Database;
database registered in recovery catalog (A)
starting full resync of recovery catalog
full resync complete
Note : The Above Command Updates All The Base Tables For Storing The Database Information Which Is Resistered.
To Prove The Above Said Issue The Following Commands :
RMAN_User SQL> Select Name From Rc_Database;
RMAN_User SQL> Select Tablespace_Name,Name From Rc_Datafile;
RMAN_User SQL> Select Name From Rc_Redo_Log;
/* The Configuration Of Recovery Catalog Is Completed. */