Karma DBA

Calculate the size of the RMAN backup

2009-04-17T04:59:00.000-07:00

Yesterday I went to see the backup team to see the ways we can reduce the cost of the backup ( recession time ...cost cutting ;)

I was told by backup team that oracle is taking lot of resources w.r.t tape ..thus they asked me to find out for each database what is the size of the backup ..

It is very difficult to get the size of the rman backup's directly but following SQL will help u to get the approx size in TB about the rman backup sets in tape . This is valid only for 9i ..It may work in 10G also but not checked and tested !!!

select sum(blocks*block_size)/1024/1024/1024/1024 from rc_backup_datafile ;

select sum(blocks*block_size)/1024/1024/1024/1024 from RC_BACKUP_REDOLOG ;

Above SQL's have to be run in sqlplus in RMAN catalog database ..

Hope this helps to determine the cost of tape's

How to Block TOAD session in Production Database

2009-04-16T04:40:00.001-07:00

Hi,

As the TOAD usuage increases , There are issues with performance of the database . We have seen that TOAD users are one of the reasons for causing performance issue. Users , I mean developers, Login to Production database using TOAD and fire N number of Queries . If the Query uses Parallelism , Then Parallel processes are linked to the users . Thus when a process requires a parallel process , Oracle is not able to allocate the necessary parallel processes as requested .. thus performance issue . Also we have seen TOAD users can use locking issue which may impact the overnight batch issues

Thus , I was asked to see how to restrict the TOAD access to the Production databases . There are two ways to do this :

Not to allow TOAD session

To kill the TOAD session after some interval

I think first option is the best option ....

To do this , We have to write a simple trigger ....

CREATE OR REPLACE TRIGGER block_toad
AFTER LOGON
ON DATABASE
DECLARE
--Declare a cursor to find out the program
--the user is connecting with.
CURSOR user_prog IS
SELECT program FROM v$session
WHERE audsid=sys_context('USERENV','SESSIONID');
--Assign the cursor to a PL/SQL record.
user_rec user_prog%ROWTYPE;
BEGIN
OPEN user_prog;
FETCH user_prog INTO user_rec;
IF user_rec.program IN ('TOAD.exe')
THEN
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login with TOAD');
END IF;
CLOSE user_prog;
END;
/

Similar process can be used to block other tools ...

Hope this helps DBA to manage the TOAD access to Production databases ;)

Oracle Database 11G : New Features for DBA

2007-11-13T05:31:00.000-08:00

In Next few days , I am going to start writing on Oracle new database version 11G . Following topics will be covered and most of the topics will be related to DBA's.

Some of topics coverered will be :

Oracle 11G : Installation and Upgrade Enhancements
Oracle 11G : Storage Enhancements
Oracle 11G : Intelligent Infrastructure Enhancements
Oracle 11G : Performance Enhancements
Oracle 11G : Partitioning and Storage-Related Enhancements
Oracle 11G : RMAN Enhancements
Oracle 11G : Flashback and Logminer
Oracle 11G : Diagnosability Enhancements
Oracle 11G : Database Replay
Oracle 11G : Data Recovery Advisor
Oracle 11G : Security New Features
Oracle 11G : Oracle SecureFiles
Oracle 11G : SQL Performance Analyzer
Oracle 11G : SQL Plan Management
Oracle 11G : Automatic SQL Tuning

Do visit soon for updates !!!

Oracle Software : 32 Bit or 64 Bit

2007-11-06T06:49:00.000-08:00

I was with a client who had asked me to install oracle on linux 64 bit . It was quite a small task and as a Oracle Consultant it is very easy but the problem I had that ClientDBA had copied the software from CD to server and left and none knew whether the software was 32 bit or 64 bit :(

I could have made my life easy by downloading the correct software from OTN and transfer it to server but again the problem was with the network as it was remote installation .

How to find whether the software is 32 bit or 64bit :

Go to Disk3/stage/Components/oracle.options.odm/9.2.0.4.0/1/DataFiles/ in the installation kit.

% cd Disk3/stage/Components/oracle.options.odm/9.2.0.4.0/1/datafiles

Copy lib.1.1.jar file in a new directory, outside the installation kit

% cp lib.1.1.jar $TMP

Unzip the file

% unzip lib.1.1.jar

execute "file" command for dmndm.o

% file dmndm.o

This is a file specific to DataMining. If this file is 32 bit, then your software is 32bit. If the file is 64bit, then the software is 64bit

How to Load file from one Character Set to EBS in UTF8

2007-10-02T02:21:00.000-07:00

It a very common to load file (data) from one character set to another character set.

Here I will give u an example:

We have created a csv file from a database which has got following NLS properties:

NLS_LANG=American_America.WE8ISO8859P1

Now we have to load the file in EBS using concurrent manager and the NLS details of the EBS are as follows

NLS_LANG=American_America.UTF8

If the source file has got some special characters (Western European characters), you will find that complete file is not loaded in EBS as the file generated is not in UTF8 character set.

Errors u may get are

Record 144: Rejected - Error on table "GL"."GL_INTERFACE", column REFERENCE10.
Multibyte character error.
Record 145: Rejected - Error on table "GL"."GL_INTERFACE", column REFERENCE10.
Multibyte character error.

Table "GL"."GL_INTERFACE":
11968 Rows successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

To resolve this issue, we have to do the following:

After the file is created and transferred to EBS server, it has to be converted to UTF8 character set before loading in EBS.

How to convert the Character set of a file

adncnv

e.g. to convert a file from WE8ISO8859P1 to UTF8:

$AD_TOP/bin/adncnv AP_loaddata1_f_we8.dat WE8ISO8859P1 AP_loaddata1_f.dat UTF8

(this converts the AP_loaddata1_f_we8.dat file from Western European character set to UTF8 character set ).

You should then copy the resulting file to the directory from where you intend to run the sql*loader utility.

More details can be found in Metalink Note 135691.1

Discoverer Issue after Upgrade to 11.5.10.2

2007-10-02T02:01:00.000-07:00

Recently we upgraded our EBS from 11.5.9 to 11.5.10.2 and found that whenever user clicks on Discoverer plus from the application, it always point to discoverer viewer which was not correct.

Reason for this issue is:

There is a profile option called ICX: Discoverer use Viewer which is set to YES by default. Hence Discoverer Viewer is always started even if discoverer plus is requested.

Hence we need to change the value of ICX: Discoverer use Viewer profile option to NO and bounce Apache for users to discover plus from EBS Application

Hope this helps

Oracle Expert Program

2007-04-16T01:30:00.000-07:00

Oracle Education has recently started new concept known "Oracle Expert" in certification which I think is very good as this will help us going for specialization within Oracle.

There are many streams which DBA's or Oracle Apps DBA can go for :

Oracle Database 10g: Managing Oracle on Linux Certified Expert
Oracle Database 10g: Real Applications Clusters Administrator Certified Expert
Oracle 11i Workflow Certified Expert
Oracle 11i System Administrator Certified Expert

The first two will be released soon in Beta version but the later ones ( For Oracle Apps DBA) have been released in Production.

For details , Visit Oracle Certification website:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=145

Cloning of Oracle Application

2007-04-02T03:38:00.000-07:00

This is one of the most usual tasks given to Apps DBA and from my past exp., DBA's spend more than 70% of time in Cloning.

Cloning Oracle Apps 11i is done through a tool given by Oracle known as RAPID CLONE and it is very easy to use but it does not do all the bits....

When u clone the env, there is a ref of the source env in the database in FND_NODES tables , thus after clone if u visit the OAM page , u will see both source nodes and target nodes . Also u will be some concurrent managers which have got ref to source nodes.

If u want to get rid of the source nodes , Do the following :Follow the Rapid clone document (Metalink note : 230672.1)

Prepare the Source System ( Pre Clone Steps )
Copy the Source System to the Target System
Configure the Target System
o Configure the target system database server
o After the database is created ,connect as apps and execute the following
EXEC FND_CONC_CLONE.SETUP_CLEAN;
Commit;
o Above cmd will clean the FND_NODES table and does something more than that i.e.

c lear the concurrent manager tables etc.
o Run the autoconfig.sh again on the database tier

And there after follow the doc Metalink note: 230672.1

Oracle 11i Applications Technology Certification Update

2007-04-01T03:49:00.000-07:00

Good news from Oracle for Oracle Apps DBA regrading certification .

Now it is easy to get certification for Oracle Apps DBA if u have 10G OCP or 9i OCP .

After careful review from the beta exams and DBA/customer feedback , the exams 1Z1-231 Implement Oracle Workflow 11i and 1Z1-232 Oracle 11i System Administration have been reapportioned so that the resulting credentials better fit their corresponding job roles.

Now if u have 9i or 10G OCP , u have to appear for one Apps exams to get OCP for Oracle 11i Applications Database Administrator Certified Professional ..

For more details , Please visit Oracle website

http://www.oracle.com/global/us/education/certification/appsdba.html

How to find the Oracle Developer version ...

2007-03-30T02:29:00.000-07:00

It is a very common question to find out the version of the Oracle Developer installed with Oracle Apps .What version , how to find out the version number and how to upgrade it.

This is required if u are planning to upgrade the oracle application database from 9i to 10G. One step is to upgrade the developer version

To find out the developer version ...Set the Apps env and use the following command :

Run $ORACLE_HOME/bin/f60gen help=y to display various component versions

How to upgrade the Oracle Developer version ?

Follow Oracle Metalink note : 125767.1 : Upgrading Developer 6i with Oracle Applications 11i

Quite simple....takes around 30 mins ...

2007-03-29T02:46:00.000-07:00

I dedicate this BLOG to the following :

My mentor and my Guru Sandeep Pandya for giving me directions in learning Oracle Application and gave full support during my learning curve as Oracle DBA and Oracle Apps DBA.
While working with him , I learnt not only Oracle but other softskills like ITIL concepts , presentation skills.

My wife Anshul for her support and patience, inspite lot of personal time I spent in Office working late...

My kidsApoorv and Anvi ...

2007-03-29T02:40:00.000-07:00

About me;

Vivek is Director of Karma DBA and has over 10 years of experience as an Oracle DBA and 6 years as an Oracle Apps DBA managing a variety of database systems, DBA teams and infrastructure projects. Worked within pan-European organisations in various sectors including Telecoms, top-tier consultancy, retail/marketing and banking.

About Karma DBA

A UK based organisation offering consultancy and remote support services for Oracle Applications and Oracle Technology based systems. Our in-depth experience of managing Oracle technologies and projects will compliment your internal or partner resources to improve productivity, efficiency and reduce cost of ownership. Visit www.karma-dba.co.uk