June 26, 2014 Comments Off
Few interview questions which may be asked in the interview for DBAs.
1) What is the user of PSU.
2) How you will plan for PSU.
3) What are the pre steps for patching.
4) What are the post_steps for patching.
5) I said, as a part of pre-req, we will take the backup of ORACLE_HOME then the question from her was
that why we need to take the backup of ORACLE_HOME.
1) What is sync command in RMAN.
2) How to restore control file from RMAN command.
3) What is reset logs option.
1) How you will create Physical DB?
2) How you will stop and start the recovery
1) How you will stop two instances of a cluster where 4 nodes.
2) What is the SCAN?
1) If user complaining that the sql is running fine till yesterday and today it is performing very poorly, how you will solve this problem. what is the approach you will start.
2) What you the method you will approach if you find “DB sequential read” in the top 5 events.
3) In which scenarios, you will consider to increase shared_pool_size.
4) In which scenarios, you will consider to tune pga_aggregate_target.
5) How you will avoid disk sorts.
6) How you will tune undo tablespace.
7) How you will enable the trace for a session.
1) What is instance recovery? how recovery will happen.
2) What is incomplete recovery and how you will open the database after incomplete recovery?
3) How to recover a data file?
April 5, 2014 Comments Off
Today we did successfully one upgrade and as a part of that we found many synonyms got invalidated. So following have been used.
1) To recompile NON-PUBLIC SYNONYMS use below script.
set pagesize 0
set linesize 79
set verify off
set echo off
select ‘select count(1) ‘ || OWNER||’.’||OBJECT_NAME || ‘;’
STATUS = ‘INVALID’ and
OBJECT_TYPE in ( ‘SYNONYM’ )
2) To re-compile PUBLIC synonyms we use below script.
select ‘alter public synonym ‘||object_name||’ compile;’ from all_objects where status=’INVALID’ and owner=’PUBLIC’ and object_type=’SYNONYM’;
March 30, 2014 Comments Off
As a part of our tunning activity we may need to generate trace for different SQLs. Following sqls may be handy during the same.
alter system set trace_enabled=true;
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier=’10053′;
ALTER SESSION SET events ’10053 trace name context forever, level 1′;
EXPLAIN PLAN FOR
<Your Query Here. Remember to force hard parse !>
the output will be in user_dump_dest
1) Please note <level 1> can be changed as per your requirement.
2) Please note in 11g, trace will not be generated becuase of bug, trace_enabled will be false. It should be allways true.
March 29, 2014 Comments Off
Today when i was working on a support request, i was asked to get the explain plan after execution of the SQL using below SQL.
<Execute the Problem Sql Statement>
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(dbms_xplan.display_cursor(FORMAT=>’ALLSTATS LAST’));
When i run above command, i am getting below error.
SQL> select * from table(dbms_xplan.display_cursor(FORMAT=>’ALLSTATS LAST’));
User has no SELECT privilege on V$SESSION
Solution is as follows.
grant the select privs on
v_$session, v_$sql_plan_statistics_all,v_$sql_plan, v_$sql to user. so the problem will be resolved.
to give select, run below command.
grant select on v_$sql to <username>;
grant select on v_$sql_plan to <username>;
grant select on v_$sql_plan_statistics_all to <username>;
grant select on v_$session to <username>;
March 24, 2014 Comments Off
Re-creating the AWR repository.
following are the steps to follow.
1) start the DB in restricted more(Not compulsary).
2) run below commands one by one.
alter system flush shared_pool;
@?/rdbms/admin/execsvrm.sql <– only for 11g.
3) shutdown database and restart.
a) Restricted mode is not required.
b) Bouncing is not required.
March 24, 2014 Comments Off
How to delete statistics of a table or schame.
Use below method to deelete
1) check the existing stats. if possible store them.
select * from dba_tab_col_Statistics where table_name like ‘<TABLE_NAME>’ and owner=”;
2) EXEC DBMS_STATS.DELETE_TABLE_STATS (OwnName => ‘OWNER’,TabName => ‘TABLE_NAME’);
3) select * from dba_tab_col_Statistics where table_name like ‘<TABLE_NAME>’ and owner=”;
this should not return any records.
How to lock statistics.
you can use below method to lock the stats.
EXEC dbms_stats.lock_table_stats(”, ‘<TABLE_NAME>’);
March 20, 2014 Comments Off
How to check weather histograms available for a particular table or not.
Today i was asked to check weather histograms are available or not, immediately i googled and found a way for the same. You can use below command to check weather histograms got created or not.
set lines 120
set pages 120
column owner format a15
column table_name format a20
SELECT OWNER, TABLE_NAME, HISTOGRAM,column_name, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS where table_name like ‘