Oracle DBA Interview Questions and Answers

70 Replies
Looking for oracle database administration (DBA) jobs? Then this book will provide complete details on interview Oracle DBA administration interview questions and answers. This book helps you in cracking your interview & acquire dream career as Oracle (DBA) Administrator. This book is a perfect companion to stand ahead above the rest in today’s competitive job market.Sections to be discussed:ORACLE – DATABASE ARCHITECTUREORACLE – PATCHING, CLONING & UPGRADEORACLE – ASMORACLE – RACORACLE – DATAGUARDORACLE – PERFORMANCE TUNINGORACLE – EXADATA

Amazon.com:
https://www.amazon.com/Oracle-DBA-Interview-Questions-Answers-ebook/dp/B086J79FQN/ref=as_li_ss_tl?keywords=oracle+interview+questions&qid=1585600800&sr=8-3&linkCode=ll1&tag=shausa02-20&linkId=0f5c21b7dec8790dd65a87d1bbc0d9ee&language=en_US
Amazon.ca:
https://www.amazon.ca/Oracle-DBA-Interview-Questions-Answers-ebook/dp/B086J79FQN/ref=as_li_ss_tl?keywords=oracle+interview+questions&qid=1585600800&sr=8-3&linkCode=ll1&tag=yousha0b-20&linkId=2b0e0f801234b84bfafbd15e7282291d&language=en_CA

70 thoughts on “Oracle DBA Interview Questions and Answers”

  1. saurabh srivastav

    Hi Sir,
    I read your notes..can you please guide me ….
    I have a 2 year experience in Oracle DBA and my last salary package was 2.46. Now I’m in Bangalore and searching a job in Oracle DBA. so tell me sir whats the chances and what will be next package? please sir guide me what should can i do?

    Reply ↓
    3. Amir

      Hello Everyone!

      I am looking to start my career as a DBA. Can anybody let me know, from where I need to start?
      Currently, I am working as a application support engineer and I am based in Delhi.
      Your support is highly appreciated..!

      Thanks in advance!

      Best regards,
      Amir

      Reply ↓
  11. RAm

    Hi shanoj,
    Iam working as a support Engineer L1(since 14 months) in some xyz company where
    i was told to work on oracle dba activities and iam OCA Certified, Iam i eligible
    to attend interviews as a oracle DBA as my designation is Support Engineer in Present Company.
    Please Suggest me ASAP.
    Regards
    Ram

    Reply ↓
  12. D

    HI RAm,
    You can work as Oracle DBA if you have knowledge and practical experience. Designation does not matter, profile matter a lot.

    Thanks,
    D

    Reply ↓
  15. Ali

    Good blog..and good question …and answer but not cover the installation of oracle part in linux…any way every thing is good..
    THANKS
    ALI

    Reply ↓
  16. Santhosh

    Hi Shanoj,

    Need your assistance. I have completed my BCA in 2011 and currently working in xyz company in BPO field. I have joined DBA course recently. Is there a chances of getting a job as a fresher in Oracle DBA.

    Thanks for your prompt reply.

    Regards,
    Santhosh

    Reply ↓
    1. shanojkumar Post author

      It is not that essay to get in, but possible….more you know ….your luck factor also will increase..
      Try hard…learn and practice you will get into your dream carrier/job

      Reply ↓
      1. Dharmendra singh

        Hi Shanoj,

        Need your assistance. I have completed my BCA in 2011 and currently working ERP functional in manufacturing company. I have complete DBA course recently. Is there a chances of getting a job as a fresher in Oracle DBA.
        i am searching a DBA job fresher level.

        Thanks for your prompt reply.

        Regards,
        Dhrmendra
        9711354420

  17. shiva arya

    Thanks Shanoj .. it was very helpful .
    i am having more than 2 years of experience as a oracle Dba and looking for a job change .
    could you please suggest me how to prepare for the interview .

    thanks for your help !!

    Reply ↓
  21. Amir

    I am looking to start my career as a DBA. Can anybody let me know, from where I need to start?
    Currently, I am working as a application support engineer and I am based in Delhi.
    Your support is highly appreciated..!

    Thanks in advance!

    Best regards,
    Amir

    Reply ↓
  22. Ram

    Hi Shanoj,
    I got new Laptop with 4GB RAM and 1TB Harddisk with windows 8 platform by default,
    where Iam try to install Linux on virtual machine Iam getting error as “Not enough physical memory is available to power on this virtual machine” where i couldn’t able to install linux,I assigned 2gb RAM and 80GB harddisk for VMware.
    Is that problem with windows8 or ?
    Please help me out ASAP
    Regards
    Ram

    Reply ↓
  23. Ujjal Mukherjee

    Hi Shanoj,
    this stuff is really great for any candidate to prepared for an interview .i would like to suggest you to add more interview question and answer on advance oracle DBA regarding RMAN Flashback backup etc on this same it would be also helpful to other
    regards
    Ujjal

    Reply ↓
  25. Atul Markan

    Hi,

    From last many months and days i was looking some good stuff. Again and again I was searching good interview questions but i was getting normal questions but yesterday again i search and got a good stuff.

    Its a really good explanation for each question but the good thing is you mention those question and answer which is currently asking by interviewer. Really appreciated your work.

    Keep it up.

    Thanks
    Atul Markan

    Reply ↓
  26. Dev

    Hi Sir i am a 2014 fresher .and i want start my carrier in Oracle DBA. Now situation of TI industry
    so comparative.How i will i prepare for interview .should i join course or not .how can i get job
    in this field please guide me sir.

    Reply ↓
  27. Vikas

    Hi Shanoj,
    This is pretty much good stuff to go through. You have done a great job.

    Anyway, I’ve some set of questions for you, so appreciate if you can share your email id or skype or any, so that can discuss first and later, we’ll post here.

    Looking forward to your prompt revert.

    Thanks in advance.

    Reply ↓
  28. Karthik

    Hi Shanoj,
    I am working as a Oracle DBA .It’s a very good posting.
    You have covered almost all the topics related to Oracle DBA interview.
    You have covered Oracle database Architecture,Oracle Core DBA activities,Oracle RAC,RMAN,Oracle Data Guard,oracle Patching.

    Can you please post more Questions related to Oracle Backup’s and Recovery Methods ,
    Oracle Performance Tuning and Oracle ADDM,AWR,ASH reports

    Reply ↓
  29. Samir Sharma

    Nice questions and answers. Thanks and appreciate your effort. Could you provide us real time scenario based and tricky questions answers with explanations like Oracle DBA Interview Questions & Answers book by Gitesh Trivedi of kendba.com? I think those kinds of questions are also very useful for clearing technical interviews.

    Reply ↓
  32. Hari Sudhan Vasudevan

    Hi Shanoj,

    This article is very informative. I came across each and every line in it. Thanks a lot for ur Post.
    I would like to follow your posting. One again Thanks Much 🙂 🙂 🙂

    Reply ↓
  33. mukesh dutt panday

    Hi sir.
    I have 2.2 year exp as oracle dba. Sir are you help me to find the job.your notes is awsome
    And very helpfull

    Thankyou sir

    Reply ↓
  35. Gangdhar.K

    Hi Shanoj,

    I am very much Interest to learn RAC. Can you please suggest best institute for the same in Hyderabad. Thanks you.

    Regards,
    Gangadhar.K

    Reply ↓
  36. Gangdhar.K

    Hi Shanoj,

    Can you please explain how the switching will happen from primary database to standby in case of any disaster/failure in data guard concept.

    Regards,
    Gangadhar.

    Reply ↓
    1. shanojkumar Post author

      Hi Gangadar,

      Activating the Standby Database – Switchover Method

      The main advantage of a graceful switchover is that it avoids the resetlogs operation. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.

      1. Prerequisites :-

      There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

      2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (PRIMARY_DB & STANDBY_DB)

      SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

      NAME TYPE VALUE

      ———————————— ———– ————-

      job_queue_processes integer 10

      SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

      3. In PRIMARY database check the database role. (PRIMARY_DB server)

      SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

      NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

      ——— —————- ——- ——————– ———– ———- ——————–

      AIRMAN PRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

      4. In STANDBY database check the database role. (STANDBY_DB server)

      SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

      NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

      ——— —————- ——- ——————– ———– ———- ——————–

      AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

      5. Shutdown the PRIMARY database. (PRIMARY_DB server)

      SQL> shutdown immediate

      Database closed.
      Database dismounted.
      ORACLE instance shut down.

      6. Open the PRIMARY database in RESTRICTED mode. (PRIMARY_DB server)

      SQL> startup restrict

      ORACLE instance started.

      Total System Global Area 252777660 bytes
      Fixed Size 451772 bytes
      Variable Size 218103808 bytes
      Database Buffers 33554432 bytes
      Redo Buffers 667648 bytes
      Database mounted.
      Database opened.

      7. Archive the current log on the PRIMARY database. (PRIMARY_DB server)

      SQL> alter system archive log current;

      System altered.

      8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (PRIMARY_DB & STANDBY_DB)

      SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

      THREAD# MAX(SEQUENCE#)
      ———- ————–
      1 1934

      Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.

      9. Initiate the switchover on the PRIMARY database. (PRIMARY_DB)

      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

      10. Once the step above has completed, log on to the STANDBY database and issues the following command. (STANDBY_DB)

      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

      11. Immediately return to the FORMER PRIMARY database and issue a shutdown and mount the NEW STANDBY database. (PRIMARY_DB server)

      SQL> shutdown immediate
      SQL> startup mount;

      12. On the NEW PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (STANDBY_DB server)

      SQL> alter database open;

      Database opened.

      13. Verify the new STANDBY’S status. (PRIMARY_DB server)

      SQL> select name, database_role from v$database;

      NAME DATABASE_ROLE
      ——— —————-
      PHYSICAL STANDBY

      14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (PRIMARY_DB & STANDBY_DB)

      SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

      NAME TYPE VALUE

      ———————————— ———– ————-

      job_queue_processes integer 0

      SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;

      15. Put the NEW STANDBY/FORMER PRIMARY database into managed recovery mode. (PRIMARY_DB)

      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

      Database altered.

      16. Test the communications for archive by performing a log switch. (STANDBY_DB server)

      SQL> alter system switch logfile;

      System altered.

      SQL> alter system switch logfile;

      System altered.

      Now check whether these logs are applying in the NEW STANDBY and also check the listener status.

      17. On the NEW PRIMARY database instance, take a backup if possible. (STANDBY_DB server)

      Switch back from SZB location (STANDBY_DB) to KJ location orginal primary sever (PRIMARY_DB)

      Switch back to Orginal Primary Database – Switchover Method

      1. Prerequisites :-

      There is no loss of any archive logs that haven’t been (yet) applied to the standby database.

      2. Set job_queue_processes value to 0 in both (PRIMARY and STANDBY) (PRIMARY_DB & STANDBY_DB)

      SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

      NAME TYPE VALUE

      ———————————— ———– ————-

      job_queue_processes integer 10

      SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 scope=both;

      3. In New PRIMARY database check the database role. (STANDBY_DB server)

      SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

      NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

      ——— —————- ——- ——————– ———– ———- ——————–

      AIRMAN PRIMARY NONE SESSIONS ACTIVE 4106602309 READ WRITE MAXIMUM PERFORMANCE

      4. In New STANDBY database check the database role. (PRIMARY_DB server)

      SQL> select NAME,DATABASE_ROLE,GUARD_STATUS,SWITCHOVER_STATUS, SWITCHOVER#,OPEN_MODE,PROTECTION_MODE from v$database;

      NAME DATABASE_ROLE GUARD_S SWITCHOVER_STATUS SWITCHOVER# OPEN_MODE PROTECTION_MODE

      ——— —————- ——- ——————– ———– ———- ——————–

      AIRMAN PHYSICAL STANDBY NONE SESSIONS ACTIVE 4106602309 MOUNTED MAXIMUM PERFORMANCE

      5. Shutdown the New PRIMARY database. (STANDBY_DB server)

      SQL> shutdown immediate

      Database closed.
      Database dismounted.
      ORACLE instance shut down.

      6. Open the New PRIMARY database in RESTRICTED mode. (STANDBY_DB server)

      SQL> startup restrict

      ORACLE instance started.

      Total System Global Area 252777660 bytes
      Fixed Size 451772 bytes
      Variable Size 218103808 bytes
      Database Buffers 33554432 bytes
      Redo Buffers 667648 bytes
      Database mounted.
      Database opened.

      7. Archive the current log on the New PRIMARY database. (STANDBY_DB server)

      SQL> alter system archive log current;

      System altered.

      8. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following. (PRIMARY_DB & STANDBY_DB)

      SQL> select thread#, max (sequence#) from v$archived_log where APPLIED=’YES’ group by thread#;

      THREAD# MAX(SEQUENCE#)
      ———- ————–
      1 1934

      Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.

      9. Initiate the switchover on the New PRIMARY database. (STANDBY_DB)

      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

      10. Once the step above has completed, log on to the New STANDBY database/Orginal Primary and issues the following command. (PRIMARY_DB)

      SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

      11. Immediately return to the FORMER PRIMARY database/Orginal STANDBY and issue a shutdown and mount the Orginal STANDBY database. (STANDBY_DB server)

      SQL> shutdown immediate
      SQL> startup mount;

      12. On the Orginal PRIMARY/OLD STANDBY, you can now open the database as the PRIMARY database. (PRIMARY_DB server)

      SQL> alter database open;

      Database opened.

      13. Verify the orginal STANDBY’S status. (STANDBY_DB server)

      SQL> select name, database_role from v$database;

      NAME DATABASE_ROLE
      ——— —————-
      PHYSICAL STANDBY

      14. Set job_queue_processes value to 10 in both (PRIMARY and STANDBY) (PRIMARY_DB & STANDBY_DB)

      SQL>SHOW PARAMETER JOB_QUEUE_PROCESS

      NAME TYPE VALUE

      ———————————— ———– ————-

      job_queue_processes integer 0

      SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;

      15. Put the Orginal STANDBY/FORMER PRIMARY database into managed recovery mode. (STANDBY_DB)

      SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

      Database altered.

      16. Test the communications for archive by performing a log switch. (PRIMARY_DB server)

      SQL> alter system switch logfile;

      System altered.

      SQL> alter system switch logfile;

      System altered.

      Now check whether these logs are applying in the Orginal STANDBY and also check the listener status.

      17. On the Orginal PRIMARY database instance, take a backup if possible. (PRIMARY_DB server)

      Hopefully,
      ———————————–
      Shanoj Kumar V
      Mob. 0091 9003028807 | Chennai | India
      http://ae.linkedin.com/pub/shanoj-kumar/14/845/343
      http://www.facebook.com/shanoj.kumar
      https://shanojkumar.wordpress.com/

      On Mon, Sep 1, 2014 at 11:55 PM, WordPress wrote:

      New comment waiting approval on shanojkumar

      Gangdhar.K commented on Oracle DBA Interview Questions and Answers

      Oracle DBA Interview Q & A There will be interview conducted for each project in TCS (both by the TCS project tech. leads …

      Hi Shanoj,

      Can you please explain how the switching will happen from primary database to standby in case of any disaster/failure in data guard concept.

      Regards,
      Gangadhar.

      Approve Trash | Mark as Spam

      More information about Gangdhar.K

      IP: 72.163.217.102, bgl11-dmz-wsa-1.cisco.com
      E-mail: gangs2012@gmail.com
      URL:
      Whois: http://whois.arin.net/rest/ip/72.163.217.102
      Thanks for flying with WordPress.com

      Reply ↓
  37. Gangdhar.K

    Thank you Shanoj.

    Please explain, What is the role of data guard broker and observer in the above?

    Regards
    Gangadhar. K

    Reply ↓
  39. motion sensor switch

    My brother recommended I might like this blog. He used
    to be totally right. This put up actually made my day. You can not consider simply how so much
    time I had spent for this information! Thank you!

    Reply ↓

  40. Pingback: Data Backup And Recovery Interview Questions

  47. Leo

    Hi Shanojkumar,
    Excellent, excellent Job!!!. I really appreciate your hard work from bottom of my heart. Wow!!!
    God bless your family.

    Thanks a bunch.
    Leo

    Reply ↓
  48. shweta

    Hi sir this is shweta, I completed course in oracle dba ,now I am looking for job,sir can u help me how I should prepare for the interview as a fresher

    Reply ↓
  51. kumari moni

    its awsum sir. i am enriched by good knowledge after your post..please update more on these for 3+ year experiencd guys,as looking for job change.

    Reply ↓
  55. Sujeet Shrivastwa

    Hi shanoj sir,
    Myself sujeet graduate in B.sc (math) 2015 and now joined ORACLE DBA CLASS.What should I do to get job as a fresher.Thanks
    Regards
    Sujeet Kumar

    Reply ↓

Leave a reply to kumari moni Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.