Oracle dba Interview Questions

1.how to know which databases are using asm instance
2.show parameter in exp
3.check the status of the  exp dump file
4.restore datafile to perticuler location using rman
5.how to check size of a directory in linux
6.status of  partitioned disk after it assigened to asm disk group
7.how to stop job in expdp
8.how many types of load balances available in RAC

9.when we issue startup upgrade command what will happen
10.what is db replay
11.compression
12.rman 11g features
13.total db size
14.rman backup size and time
15.logical backup size,time and schema size
16.where can we find node membership polling machanism in RAC
17.how much database size  grows daily

18.are the idexes are always benficial or not
19.how to stop crs without connecting to root user
20.how to see listener status if the listener is passwor protected
21.how to improve performance of a impdp command
22.what is default time and location for ocr backup
23.difference between cpu,psu,bug fixes patch

24.How to check backup type in list backup command.
25.Can we rename asm instance
26.Asm instance control file
27.Can we create more than one asm instance on one box
28.What is conflict check in opatch

29.Crs should be up or down to take voting disks backups using dd command
30.Why expdp is faster than normal exp
31.Difference between active dataguard and normal dataguard
32.Will rman create temporary tablespace after cloing or restore process.
33.Difference between 10g load balance machanism and 11gr2 load balance machanism

34.What is the use of pwd file
35.Why mrp process runs in single on standby 3 node rac
36.Name of view that stores plsql compilation errors
37.Diffrence between cloning and refresh
38.How to demonstrate to the developper taht if they created index on perticular column it would hep
39.Order of background process when we issue startup command
40.What is the shortest way to restore a table if we have only rman backup of db size 1.5tb
41.How will you recover spfile if we dont have backup and it got corrupted
42.How does scan identify that node is a least loaded node
43.How to change the rebalance value in asm
44.Where cluster checks the details of asm diskgroups before starting the css process and pass these names to kfed
45.Which process starts 1st after issuing  crs start command
46.What will be state of database if we kill dwr background process
47.How to find scan name of a cluster
48.How to find number of nodes in cluster from os level with out using rac or database commands
49.How to find location of inventory file
50.What information contains in inventory.xml file
51.What is use of napply in opatch
52.What is use of CONSISTENT parameter in exp
53.How to find sql query of  session if  i have only  os process id
54.How to create spfile for asm instance

Check the logfiles of all servers from one server using ssh

Some time we need to check backups logs or any other logs from all servers daily  . For this we need to login to each server and verify logfiles . This script automatically log into each server and check logfiles .

1)
ssh 10.168.1.10  "VAR1=\"/\`crontab -l|grep -v \"#\"|grep rman_full|cut -d\"/\" -f2-\`\";cd \$(dirname \`grep LOG_FILE= \$VAR1|cut -d\"=\" -f2-\`);head \`ls -tr full_*|tail -n1\`;tail \`ls -tr full_*|tail -n1\`;grep \"RMAN-00569\" \`ls -tr full_*|tail -n1\`;echo \"   \"; echo \"Logfile Name Is -----> \`ls -tr full_*|tail -n1\`\" "

2)
ssh 10.168.1.50  "VAR1=\"/\`crontab -l|grep -v \"#\"|grep rman_full|cut -d\"/\" -f2-\`\";VAR2=\"/\`grep saas5 \$VAR1|cut -d\"/\" -f2-\`\";cd \$(dirname \`grep LOG_FILE= \$VAR2|cut -d\"=\" -f2-\`);head \`ls -tr full_saas5*|tail -n1\`;tail \`ls -tr full_orcl5*|tail -n1\`;grep \"RMAN-00569\" \`ls -tr full_saas5*|tail -n1\`;echo \"   \"; echo \"Logfile Name Is -----> \`ls -tr full_orcl5*|tail -n1\`\""

Expdp parameters Include and Query

1) Expdp  with Include.

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log include=TABLE:\"LIKE \'%2013%\'\"

or

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log
include=table:"in(select table_name from user_tables where table_name like 'ITEM_'||to_char(sysdate-1,'mmddyy')||'%')"

Export: Release 11.2.0.1.0 - Production on Tue Dec 31 05:42:19 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "RESEARCH"."SYS_EXPORT_SCHEMA_01":  research/******** directory=dp_dir dumpfile=tabs_research_31dec13.dmp log tabs_research_31dec13.log include=TABLE:"LIKE '%2013%'"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 30.57 GB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

2)  Expdp with Include and query

expdp test/test directory=dp_dir dumpfile=tabs_test_31dec13.dmp logfile=tabs_test_31dec13.log include=TABLE:\"LIKE \'%2013%\'\"  query="where bus_date between to_date('01-DEC-13 00:00:00','DD-MON-YY HH24:MI:SS') and to_date('31-DEC-13 00:00:00','DD-MON-YY HH24:MI:SS')"

Enabling Explain Plan at Session level

SQL>  set autotrace traceonly explain;
SQL>
SQL> select count(0) from tab;

Execution Plan
----------------------------------------------------------
Plan hash value: 1728992512

-----------------------------------------------------------------------------------
| Id  | Operation               | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |     1 |    44 |   106   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE         |         |     1 |    44 |            |          |
|*  2 |   FILTER                |         |       |       |            |          |
|   3 |    NESTED LOOPS OUTER   |         |  1270 | 55880 |   106   (1)| 00:00:02 |
|*  4 |     HASH JOIN           |         |  1270 | 49530 |    31   (4)| 00:00:01 |
|   5 |      INDEX FULL SCAN    | I_USER2 |    93 |  2046 |     1   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN   | I_OBJ5  |  1270 | 21590 |    29   (0)| 00:00:01 |
|   7 |     TABLE ACCESS CLUSTER| TAB$    |     1 |     5 |     1   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN  | I_OBJ#  |     1 |       |     0   (0)| 00:00:01 |
|   9 |    NESTED LOOPS         |         |     1 |    29 |     2   (0)| 00:00:01 |
|* 10 |     INDEX SKIP SCAN     | I_USER2 |     1 |    20 |     1   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN    | I_OBJ4  |     1 |     9 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
              "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11
              AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
              "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
              BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5) AND
              ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE
              ' OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','curren
              t_edition_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2"
              WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND
              "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
   4 - access("O"."OWNER#"="U"."USER#")
   6 - access("O"."SPARE3"=USERENV('SCHEMAID') AND "O"."TYPE#">=2 AND
              "O"."LINKNAME" IS NULL AND "O"."TYPE#"<=5)
       filter("O"."TYPE#"<=5 AND "O"."TYPE#">=2 AND "O"."LINKNAME" IS NULL)
   8 - access("O"."OBJ#"="T"."OBJ#"(+))
  10 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere
              nv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('usere
              nv','current_edition_id')))
  11 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND
              "O2"."OWNER#"="U2"."USER#")

SQL>

Shell script to send alert for database status check

#! /bin/bash
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export HOST_NAME=`hostname`
export MAIL_FILE=/home/oracle/dba_scripts/dbstatcheck.log
export MAIL_ID_LIST="database-team-vsoft@vsoftcorp.com"
export ALERT_LOG=/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
dbchk=`sqlplus -s / as sysdba <set feed off lines 1000 pages 200 tab off space 1 head off FLUSH OFF TRIMSPOOL ON echo off verify off termout off
select status from v\\$instance;
exit
EOF`
dbchk1=`echo $dbchk|sed s/^$//`
echo $dbchk1
if [ "$dbchk1" != "OPEN" ]
then
echo "database is not open"
echo "Sending recent errors in alert log : ${ALERT_LOG} " > $MAIL_FILE
echo "############################################################" >> $MAIL_FILE
grep -A 3 -B 3 "ORA-" ${ALERT_LOG}|tail -n 20 >> $MAIL_FILE

cat $MAIL_FILE|mail -s "### Severity High : DATABASE IS DOWN : ${HOST_NAME}:${ORACLE_SID} ###" "${MAIL_ID_LIST}"
fi