Hi,
We had a problem where some package consisting of several procedures were used to execute in few seconds in one DEV1 environment and the same one was taking more time in the other DEV2 environment.?
Soln: In this scenario :
Step 1:
-----------
I used the TOAD tool to compare the two schema i.e one in DEV1 and other in DEV2.
Step 2:
---------
In the TOAD tool we need to go to =>DBA tab and then use compare schema option.
Open the two schemas from the different Databases you want to compare.Select the objects type to compare =>indexes ,functions,...
Step 3:
-----------
Select the compare tab.This will compare the selected objects.I found few indexes were missing.I recreated the missing indexes by taking the script from the sync sript tab.
Now when I asked our developer to reexecute thee it executed with the lesser time.Hence the issue was resolved.I found the TOAD tool to be useful in many ways in helping DBA getting scripts easliy.
Best regards,
Rafi.
Monday, June 28, 2010
Dictionary views and Auditing enabling
When you don't remember view name and only the key word:
---------------------------------------------------------
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
Enabling/Disabling auditing:
---------------------------------
Step 1:
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE; =>To enable auditing and direct audit records to the database audit trai
ALTER SYSTEM SET audit_trail=None SCOPE=SPFILE;
Step 2:Shut down the Database since AUDIT_TRAIL is the static parameter.
SQL>shutdown immediate
Step 3: Again start the Database for changes to take effect.
SQL>startup.
---------------------------------------------------------
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
Enabling/Disabling auditing:
---------------------------------
Step 1:
COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.
AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE; =>To enable auditing and direct audit records to the database audit trai
ALTER SYSTEM SET audit_trail=None SCOPE=SPFILE;
Step 2:Shut down the Database since AUDIT_TRAIL is the static parameter.
SQL>shutdown immediate
Step 3: Again start the Database for changes to take effect.
SQL>startup.
Tuesday, June 8, 2010
MOST IMPORTANT DBA & APPS DBA INTERVIEW QUESTIONS
Hi ,
Below is the list of favourite interview questions and answers:
1)Describe the hot backup mechanism?Why we say more redos generate during hot backup?
(SONY,TESCO,ROBERT BOSCH
Ans: When we give the below command:
SQL>Alter tablespace/database TS_NAME begin backup;
The following things happen:
1)A Checkpoint occurs
2)A timestamp is started.
3)Datafile header gets freezed but the DBWR process can write the data or the server process reads the data to the other portion of the datafile.
Here we come across some interesting things happening:
1)As Users can continue to work on the database when the database is in hotbackup mode,at the same time we say more redos are generated.
Let me tell you how we confirm the tablespace is in hot backup mode:
SQL>select status from v$backup;
SQL> select file#||' '||status||' '||time from v$backup;
If the status = active,It is in hotbackup mode.
If the status = not active,It is not in hotbackup mode.
We say when the tablespace is in hot backup mode more redos are generated:
Let me tell you what exactly what this concept is,when we issue the command ALTER TABLESPACE TS_NAME BEGIN BACKUP,Users are doing the normal transaction activities in the form of insert/update/delete so when the OS command CP is issued to copy the file to backup destination at that time if there is ANY UPDATE is issued or any change happen to the block than the ENTIRE IMAGE OF THE BLOCK IS COPIED TO REDOLOG FILES.This happen frequently whenever there is change happening to the block,so each time any change happening to the block the ENTIRE IMAGE OF THE BLOCK IS COPIED TO THE REDOLOG FILE FOR RECOVERY PURPOSE so we say in HOT BACKUP MORE REDOS ARE GENERATED.
There is one more interesting thing happening as CP is a dummy command it doesn't know the change happened in the block when there is some data written by DBWR so the block in such a state during the hot backup is known as "SPLIT BLOCK" or "FRUCTURED BLOCK".Thats why we say the hot backup is inconsistent backup.
When we issue the command:
SQL>ALTER TABLESPACE/DATABASE TBS_NAME end backup;
The datafile headers get open and the normal database operations can go on as it is.
Hope it helps .........
2)Describe the steps for upgrading the Database?(SONY,ORACLE)
Refer my previous posts.
3)How can you determine the CPU usage,IO usage ?
Ans: IN Operating system:
top -c can give up the %CPU usage.
iostat can give up the blocks read/write per second detail information along with the iowait.
IN Database:
A Join query of v$session and v$process can help in getting the CPU and process related details as below:
select s.username “Oracle User”,s.osuser “OS User”,i.consistent_gets “Consistent Gets”,i.physical_reads “Physical Reads”,s.status “Status”,s.sid “SID”,s.serial# “Serial#”,
s.machine “Machine”,s.program “Program”,to_char(logon_time, ‘DD/MM/YYYY HH24:MI:SS’) “Logon Time”,w.seconds_in_wait “Idle Time”, P.SPID “PROC”,
name “Stat CPU”, value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and ‘SQL*Net message from client’ = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like ‘%cpu%’
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;
4)Describe the apps file system?(ROBERT BOSCH)
Ans: Refer the below link for ORACLE 11i/12 filesystem:
http://becomeappsdba.blogspot.com/2007/01/oracle-apps-r12-file-system-changes.html
5)How you tune your query?(COGNIZANT,ORACLE,SONY)
Ans:Refer my previous post.
In general:
1)Generate the execution plan with the help of tool like EXPLAIN PLAN,SQL_TRACE,DBMS_PROFILER
2)Gather the statistics
3)Check the joins and joins order in the query.
4)Check the where clause conditions
5)Check the index usage details
6)Use or guide developers for using various hints to alter the execution plans.
6) Where do we see concurrent manager log files?
Ans: $cd $APPLCSF/$APPLLOG
7)How is clonning/Migration done?(YODLEE,SONY)
Ans The below details by Burleson is quite helpful:
database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.
STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 2: Shutdown the old database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:
Old:
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.
Old:
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile
STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile
STEP 9: Start the new database
@db_create_controlfile.sql
STEP 10: Place the new database in archivelog mode
Below is the list of favourite interview questions and answers:
1)Describe the hot backup mechanism?Why we say more redos generate during hot backup?
(SONY,TESCO,ROBERT BOSCH
Ans: When we give the below command:
SQL>Alter tablespace/database TS_NAME begin backup;
The following things happen:
1)A Checkpoint occurs
2)A timestamp is started.
3)Datafile header gets freezed but the DBWR process can write the data or the server process reads the data to the other portion of the datafile.
Here we come across some interesting things happening:
1)As Users can continue to work on the database when the database is in hotbackup mode,at the same time we say more redos are generated.
Let me tell you how we confirm the tablespace is in hot backup mode:
SQL>select status from v$backup;
SQL> select file#||' '||status||' '||time from v$backup;
If the status = active,It is in hotbackup mode.
If the status = not active,It is not in hotbackup mode.
We say when the tablespace is in hot backup mode more redos are generated:
Let me tell you what exactly what this concept is,when we issue the command ALTER TABLESPACE TS_NAME BEGIN BACKUP,Users are doing the normal transaction activities in the form of insert/update/delete so when the OS command CP is issued to copy the file to backup destination at that time if there is ANY UPDATE is issued or any change happen to the block than the ENTIRE IMAGE OF THE BLOCK IS COPIED TO REDOLOG FILES.This happen frequently whenever there is change happening to the block,so each time any change happening to the block the ENTIRE IMAGE OF THE BLOCK IS COPIED TO THE REDOLOG FILE FOR RECOVERY PURPOSE so we say in HOT BACKUP MORE REDOS ARE GENERATED.
There is one more interesting thing happening as CP is a dummy command it doesn't know the change happened in the block when there is some data written by DBWR so the block in such a state during the hot backup is known as "SPLIT BLOCK" or "FRUCTURED BLOCK".Thats why we say the hot backup is inconsistent backup.
When we issue the command:
SQL>ALTER TABLESPACE/DATABASE TBS_NAME end backup;
The datafile headers get open and the normal database operations can go on as it is.
Hope it helps .........
2)Describe the steps for upgrading the Database?(SONY,ORACLE)
Refer my previous posts.
3)How can you determine the CPU usage,IO usage ?
Ans: IN Operating system:
top -c can give up the %CPU usage.
iostat can give up the blocks read/write per second detail information along with the iowait.
IN Database:
A Join query of v$session and v$process can help in getting the CPU and process related details as below:
select s.username “Oracle User”,s.osuser “OS User”,i.consistent_gets “Consistent Gets”,i.physical_reads “Physical Reads”,s.status “Status”,s.sid “SID”,s.serial# “Serial#”,
s.machine “Machine”,s.program “Program”,to_char(logon_time, ‘DD/MM/YYYY HH24:MI:SS’) “Logon Time”,w.seconds_in_wait “Idle Time”, P.SPID “PROC”,
name “Stat CPU”, value
from v$session s, v$sess_io i, v$session_wait w, V$PROCESS P, v$statname n, v$sesstat t
where s.sid = i.sid
and s.sid = w.sid (+)
and ‘SQL*Net message from client’ = w.event(+)
and s.osuser is not null
and s.username is not null
and s.paddr=p.addr
and n.statistic# = t.statistic#
and n.name like ‘%cpu%’
and t.SID = s.sid
order by 6 asc, 3 desc, 4 desc;
4)Describe the apps file system?(ROBERT BOSCH)
Ans: Refer the below link for ORACLE 11i/12 filesystem:
http://becomeappsdba.blogspot.com/2007/01/oracle-apps-r12-file-system-changes.html
5)How you tune your query?(COGNIZANT,ORACLE,SONY)
Ans:Refer my previous post.
In general:
1)Generate the execution plan with the help of tool like EXPLAIN PLAN,SQL_TRACE,DBMS_PROFILER
2)Gather the statistics
3)Check the joins and joins order in the query.
4)Check the where clause conditions
5)Check the index usage details
6)Use or guide developers for using various hints to alter the execution plans.
6) Where do we see concurrent manager log files?
Ans: $cd $APPLCSF/$APPLLOG
7)How is clonning/Migration done?(YODLEE,SONY)
Ans The below details by Burleson is quite helpful:
database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.
STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 2: Shutdown the old database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:
Old:
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.
Old:
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile
STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile
STEP 9: Start the new database
@db_create_controlfile.sql
STEP 10: Place the new database in archivelog mode
Subscribe to:
Posts (Atom)