DBA INTERVIEW Qs.

June 26, 2014 Comments Off


Few interview questions which may be asked in the interview for DBAs.

 

Patching

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.

RMAN

1) What is sync command in RMAN.

2) How to restore control file from RMAN command.

3) What is reset logs option.

DG:

1) How you will create Physical DB?

2) How you will stop and start the recovery
RAC:

1) How you will stop two instances of a cluster where 4 nodes.

2) What is the SCAN?
Performance tuning

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.

 Recovery: 

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?

How to compile synonyms.

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

spool select_all.sql

select ‘select count(1) ‘ || OWNER||’.’||OBJECT_NAME || ‘;’
from
dba_objects where
STATUS = ‘INVALID’ and
OBJECT_TYPE in ( ‘SYNONYM’ )
order by
OBJECT_TYPE, OBJECT_NAME;
spool off

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';

How to generate 10053 trace for any sql.

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 !>
EXIT;

the output will be in user_dump_dest

 

Note1:-

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.

 

 

 

 

User has no SELECT privilege on V$SESSION

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’));

PLAN_TABLE_OUTPUT
——————————————————————————–
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>;

 

Re-creating the AWR repository.

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).

startup restrict

2) run below commands one by one.
@?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
@?/rdbms/admin/catawrtb.sql
@?/rdbms/admin/execsvrm.sql <– only for 11g.
@?/rdbms/admin/utlrp.sql

3) shutdown database and restart.

shutdown immediate;

startup
My observation:-

a) Restricted mode is not required.

b) Bouncing is not required.

 

 

Few points on Oracle statistics.

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>’);

HOW TO CHECK HISTOGRAMS PRESENT FOR A TABLE OR NOT

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 ‘

‘;

Even you can owner also.

I thought of putting my entire RD, so that i can refer in the future.

ground preparation.

1) create a table test(srno number, name varchar2(20));

2) insert into test values(&srno, ‘&name’));

3) collect the stats with col size 1.

4) check the stats.

5) collect the stats with col size auto.

6) check the stats.

7) do some select using srno.

8) collec the stats with col size auto.

9) check the stats.

10) collect the status with col size with some value > 1

11) check the stats.

Observations:-

1) No entries will be there in DBA_TAB_COL_STATISTICS table if we do not run stats collection irrespective of col size.
2) Even if i specify auto in collection, it will not collect the histograms until there is a real need.
3) Even if i specify more num of buckets, it will take only as needed.

Please fidn the entire log here with. i have done the testing in 11g database.

SQL> CREATE TABLE TEST(SRNO NUMBER, NAME VARCHAR2(20));

Table created.

SQL> insert into test values(&srno, ‘&name’)
2 ;
Enter value for srno: 1
Enter value for name: 111
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(1, ‘111’)

1 row created.

SQL> /
Enter value for srno: 2
Enter value for name: 22
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(2, ’22’)

1 row created.

SQL> /
Enter value for srno: 3
Enter value for name: 33
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(3, ’33’)

1 row created.

SQL> /
Enter value for srno: 4
Enter value for name: 44
old 1: insert into test values(&srno, ‘&name’)
new 1: insert into test values(4, ’44’)

1 row created.

SQL> commit;

Commit complete.

SQL> 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 ‘TEST';SQL> SQL> SQL> SQL>

no rows selected

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size 1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size auto’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> select * from test where srno=&1
2 ;
Enter value for 1: 2
old 1: select * from test where srno=&1
new 1: select * from test where srno=2

SRNO NAME
———- ——————–
2 22

SQL> /
Enter value for 1: 3
old 1: select * from test where srno=&1
new 1: select * from test where srno=3

SRNO NAME
———- ——————–
3 33

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for all columns size 1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST NONE SRNO 1

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for columns size 2 srno’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST NONE NAME 1
SYSTEM TEST HEIGHT BALANCED SRNO 2

SQL> exec dbms_stats.gather_table_stats(ownname=>’system’,tabname=>’test’, method_opt=>’for columns size 5 srno for columns size 4 name ‘,cascade=>true);

PL/SQL procedure successfully completed.

SQL> 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 ‘TEST';SQL> SQL> SQL> SQL>

OWNER TABLE_NAME HISTOGRAM COLUMN_NAME NUM_BUCKETS
————— ——————– ————— —————————— ———–
SYSTEM TEST HEIGHT BALANCED NAME 4
SYSTEM TEST HEIGHT BALANCED SRNO 4

SQL>

Follow

Get every new post delivered to your Inbox.