Pavan DBA's Blog

The DBA Knowledge Store

Important Docs

This page allows me to share good technical docs that I came across and hope you will also learn the best about Oracle Database.




Step by Step document for Physical Database creation

Dataguard best practises

project-lockdown – a wonderful doc about security of database

RMAN 10g features

Performance Tuning

Analyzing AWR/statspack report-1

Analyzing AWR/Statspack report-2

Analyzing AWR/statspack report-3

Functionality of Undo & Redo

10gdba complete features

RMAN Tuning with memory buffers

Oracle Installation Documents


Oracle 10g Installation on OEL 4.7

Oracle 11g installation on Linux





11gRAC Installation doc

Oracle 10gRAC On Linux Using VMware Server

Oracle Database 11G Installation on Solaris 10



how to configure DG broker and observer

Video Tutorial for creating physical standby database

Redo Internals

Oracle Listener TNS Security





Add a Database Instance on the New Node

Oracle Streams


Automation of Database on Linux

Oracle Database Licensing

RMAN & Media Manager Troubleshoot

RMAN Cloning using Recovery Catalog

step by step document for patchset installation

step by step document for patchset installation and Manual DB upgrade

Capacity Planning with Oracle

Transaction Internals

338 Responses to “Important Docs”

  1. mani said

    Which process is responsible for writing data into dumpfile?

    • Pavan DBA said

      it is datapump worker process

  2. bhavanisankar said

    please send oracle dba roles and responsibulites to

    • Pavan DBA said

      you can find them in google

  3. Madhu said

    Hi Pavan sir,

    I have searched something about ORACLE DBA in google, luckily i got your website… i have found answer for that… then i started going through the all your posts till end. I came to know many things from your blog. Its simply awesome sir… you are really doing great job sir. Its really helpful….

    • Pavan DBA said

      thank you very much madhu

      • Mir Sadath Ali said

        Thank you very much pavan sir, I newly entered in dba, my background ccna networking, I really appreciate your job for helping students. Sir, in your blog mostly 10g docs and few are there about 11g, could you plz update your blog about full doc 11g and 12c….it will be very kind of you….

      • Pavan DBA said

        thanks. will do it

  4. uday paritala said

    Redolog file will be having 3 states current,active,inactive.
    In noarchivelog mode active status available or not?

    • Pavan DBA said

      yes. still active status will be there even though there is no significance for it in noarchive log mode

  5. Hi Pavan,

    I need some clarification on undo table space.

    Actually size of my undo table space is 16GB.
    Undo retention period 24hrs.
    Undo retention was NOGUARANTEE.

    If you see below unexpired blocks are almost equal to 16GB.


    16537.5 UNEXPIRED
    0.625 EXPIRED
    0.25 ACTIVE

    1)If any one tries to load data then they will face error ORA-30036: unable to extend segment undo table space or not?

    2) Did i have to wait for 24hrs to get clear of this undo tablespace as per retention?

    3) As we can’t flush this undo tablespace. Is there any way other than recreating undo to clear undo tablespace?

    4) As retention was no guarantee will oracle will use unexpire blocks for further transactions or not?

    Sai Krishna M

    • Pavan DBA said

      please find answers inline

      1)If any one tries to load data then they will face error ORA-30036: unable to extend segment undo table space or not?
      yes. they will get error

      2) Did i have to wait for 24hrs to get clear of this undo tablespace as per retention?
      yes. till that time blocks will remain

      3) As we can’t flush this undo tablespace. Is there any way other than recreating undo to clear undo tablespace?
      you can create new undo tablespace and make it default. or resize your existing undo tablespace

      4) As retention was no guarantee will oracle will use unexpire blocks for further transactions or not?
      oracle will make use of only expired blocks

      • Hari said

        Hi Pavan Sir,

        am new for this, and i finish the oracle 11g DBA course & SQL which is best way to start my Carrier SQl or Oracle. am trying job for since 6 months but i didn’t get single interview call, how can i get a job where i can search the job..


      • Pavan DBA said

        Hi Hari, plz drop an email to me – or chat with me on my facebook id – Pavan DBA

  6. Mohammed Ishaq said

    Presently I am working I T tech support
    i have four years experienced Network admin but i am switch to Oracle dba and apps dba
    so please tell me best institute and giving the placement.

    • Pavan DBA said

      for training u can contact i am not sure about placements.

      • Mohammed Ishaq said


  7. narayanreddy said

    Iam narayana reddy, I have learning DBA in ameerpet. Plz explain Rman briefly….
    .i have some little doubts on Rman.

  8. Murali said

    Hi Pavan,

    Hope your doing Good ! i am an Ex- Student from Kanna Technologies

    I have one question regarding capacity planning.

    We have 8i,9i Databases in our environment and we are and frequently getting space issues — DB size exceeded.

    I would like to know how we can estimate the DB growth for Next 6 Months and how do we plan the Storage.

    Is there any standards where we can estimate the growth and plan the storage .. ?

    We have 30GB databases …


    • Pavan DBA said

      hi murali, you can collect tablespace usage every week for a month and save that in either excel or any other format. after a month, u can check avg of tablespaces between beginning of month and also end of month by which u will get to know how much space is occupied in 1 month.

      suppose you got 10GB as value then for 6 months u can say 60GB approx is required. also plz refer to this document

  9. Suriyaakumar G M said

    Hi Pavan,
    This is Suriyaakumar… I am completing my in Database Systems @ IIIT Srirangam. I have basic theoritical and practical knowledge about oracle. I am willing to work in either oracle or mysql. May i know any oppurtunities exist in syntel…I am basically from chennai . Thanks in advance

    With Regards,

    • Pavan DBA said

      there are lot many openings. its good for career

  10. Prem Kumar C said

    HI Pavan,

    Please send the oracle 11g DUMP for OCP certification.


    • Pavan DBA said

      plz chat with me on

  11. Sanjay said

    Dear Pavan,

    First of all tank you sharing ur knowledge for us… It is so encouraging for us to learn things in your blog..

    My Needs are:

    I am new to this carrier, recently we have implemented dataguard and now we are planning to upgrade our DB from to 11.2 in solaris 10 also to install RMAN concept.

    In your blog i could see document only for windows OS. Kindly request your assistance and guide me with some documents or video tutorials to upgrade the DB from 10.2 to 11.2 in solaris and to implement RMAN.

    Thanks in advance.. Waiting for your valuable response…

    Sanjay G

    • Pavan DBA said

      similar steps are there for unix. also rman is in built. not req separately to configure

  12. Can I import a table from unrestored (a dump file) which is stored on my E drive without restoring this dump thanks a lot

    • Pavan DBA said

      hi, your question is not clear to me. can you plz explain a bit more? what u mean by unrestore?

  13. aabid janwari said

    hi sir
    i have completed my bba and now pursuing mba and want to learn about oracle dba and its architeture plz recommand some links

  14. Srinivas said

    I’m a PL/SQL with Linux Developer working in MNC at Hyd. I have 4 yrs of exp. Right now i’m planning to upgrade technology. Could you please suggest any Oracle related technologies.

    PL/SQL developer can upgrade to oracle DBA ?

    Thank You…

    • Pavan DBA said

      yes. dba would be good choice to developers.

  15. Mohan yadav said

    Hi sir
    I have done Rightnow pursueing oracle dba. What do u suggest best for future.

    • Pavan DBA said

      plz learn oracle rac and apps dba also

  16. sridhar.L said

    hi pavan, can you please tell me, on what basis indexes are created?

  17. Naveen said

    Hi Sir,

    Could you please let me know how to track user sql statements(queries performed by user to the database) with respect auditing?
    I have googled it but not getting the exact result what I am expecting. Plz help me on this..


    • Pavan DBA said

      you need to enable auditing and then use below command
      SQL> audit all by schema name;

      if you want specific statement, then use
      SQL> audit create table;

  18. Kumar Vish said

    Informative… Thanks for sharing

  19. naveen said

    when i am trying to connect oracle from linux prompt with following command
    ]$sqlplus / as sysdba
    i am getting error like
    -bash:sqlplus:command not found please give solution for this

    • Pavan DBA said

      u need to set environment variables. plz check google or my installation document in “important docs” section to get the steps.

  20. shaik said


  21. Ankur Dayama said

    Hi Pavan,

    Thanks for writing such a useful blog.Please help to upgrade a db from standard edition to enterprise edition.Also need a step by step doc for standby database creation.

    Ankur Dayama

    • Ankur Dayama said

      One small mistake its not

    • Pavan DBA said

      r u doing it in same server or diff server? i have already posted this in my blog. see this link

      standby creation document is there in my blog. check out in important docs section.

  22. Santhosh said

    Hi Pavan
    I am Santhosh , again I am asking same question , I didn’t get any solution from your side. I tried for RAC installation on Linux (VMware ),
    I created shared storage and mounted the directory in both nodes, after that nodes are rebooting, even though I set same time in both nodes. Please solve this issue , I am trying this since last 15 days.

    • Pavan DBA said

      r u following any document for installing?

  23. Santhosh said

    Hi Pavan
    I am installing oracle 11gr2 on Linux in VMware , I configured up to shared storage , and mount shared directory in both the nodes , then im ready to install clusterware , at that time machines are rebooting even I set same time in both nodes, I am unable to solve the issue because its rebooting in fraction of seconds even not to wait enter the username,password.

    • Pavan DBA said

      have u verified log files? if so what is the error msg thr?

  24. ellareddy said

    Hi sir, This is Ellareddy
    Today I faced this issue in my office,This is 3rd time i am getting same problem

    user got following error :

    Intermittent SQLException: OALL8 is in an inconsistent state.

    I checked alertlog file ,it had following message , i got the answer from is raise a SR to oracle support but i bounce the database working fine.
    .wt is permanent fix with raise SR.

    ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+81] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
    Wed Feb 12 10:07:35 2014
    Errors in file /oraeng/admin/uops/udump/uops_ora_17303.trc:
    ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+81] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
    Wed Feb 12 10:09:28 2014
    Errors in file /oraeng/admin/uops/udump/uops_ora_17307.trc:
    ORA-07445: exception encountered: core dump [_intel_fast_memcmp()+81] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
    Wed Feb 12 10:09:59 2014

    • ellareddy said

      i got the answer from google is raise a SR to oracle support.
      .wt is permanent fix without raising SR to oracle .

      • Pavan DBA said

        hi, for ora-7445/ora-600 and ora-700 errors, apart from raising SR, we don’t have any other way to get the permanent fix.

    • ellareddy said

      wt is the solution to :Intermittent SQLException: OALL8 is in an inconsistent state

      • Pavan DBA said

        you need to raise SR and get the solution from oracle corporation itself. even i can’t able to tell.

  25. udhaya said

    Its really helpful for me to understand where we i lost in my previous interview. Thanks.
    If anyone wants to Learn oracle in Chennai go to the Besant Technologies which is No.1 Training Institute in Chennai.

    • Ravi Kumar said

      Hai pavan Sir,

      This is ravi kumar.ur ex-student..presently i am working as Mysql dba since 2 there any openings in Mysql dba positions.plz let me know

      • Pavan DBA said

        hi ravi, nice to see you. as you know any openings, i will be posting to my blog. so keep on checking here.

  26. Prasanna Narayanan said

    Hi Pavan, Good Day! I am 9i OCP DBA and planning to upgrade to 11g OCP. I am referring Oracle 11g New features document and 1z0-055 study guide. Could you please share 1z0-055 exam dumps.

    Thanks & Regards,
    Prasanna Narayanan.

  27. kishore said

    4+ exp oracle dba interview questions on real time issues plz send me

    • Pavan DBA said

      interview q’s already in my blog. real time issues, u can get it from oracle forums

  28. Venkatesh said

    Hello PavanDba..!

    I have 1.8 yrs of experience as Mysql Dba. Now I wanted to switch to Oracle DBA. I undergone one of interview, he asked, why u wanted to switch over to oracle. I think I was unable to give satisfactory answer to them.
    Can you help me out, how to face such questions..??

    • Pavan DBA said

      you can say that you are passionate to do oracle dba than MySQL dba or u can say I am interested in multiple database technologies

    • sure said

      @echo off
      @echo *****************************************************************
      echo Script:Generating MSSQL Entitlement Report
      set mydate=%date:~4,2%-%date:~7,2%-%date:~10,4%-%time:~0,2%%time:~3,2%
      echo DATE:%mydate%
      setlocal EnableDelayedExpansion

      rem setting the environment

      set PATH=%PATH%;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\

      @echo ********************* SQL SERVER DETAILS *******************

      rem passing input values

      set /p ServerName=Enter Server Name :
      if “%ServerName%”==”” (
      echo Servder Name should not be blank
      goto servername

      @echo *************** SQL SERVER ENVIRONMENT *********************
      echo 1.PRODUCTION
      echo 2.DEVELOPMENT
      echo 3.UAT
      echo 4.LAB
      echo 5.COB
      set /p ServerEnv=Enter Server Environment :

      if “%ServerEnv%”==”” (
      echo Server Environment should not be blank
      goto environment
      if %ServerEnv%==1 (
      set Env=PRODUCTION
      )else if %ServerEnv%==2 (
      set Env=DEVELOPMENT
      )else if %ServerEnv%==3 (
      set Env=UAT
      )else if %ServerEnv%==4 (
      set Env=LAB
      )else if %ServerEnv%==3 (
      set Env=COB
      )else (
      echo Invalid Environment
      goto environment

      rem setting output and log file names
      set mydate=%date:~4,2%-%date:~7,2%-%date:~10,4%-%time:~0,2%%time:~3,2%
      set LOGFILE=BATCHSCRIPT-LOG-MSSQL-%Env%-%mydate%.log
      set OUTFILE=OUTPUT_%ServerName%-MSSQL-%Env%-%mydate%.txt
      set COMPARE=VERIFICATION-%ServerName%-MSSQL-%ENV%-%mydate%.log

      rem Redirecting input details to log file
      echo ***********************************************>%LOGFILE%
      echo Date : %date%
      echo. >>%LOGFILE%
      echo Server Name : %ServerName%>>%LOGFILE%
      echo Server Environment : %Env%>>%LOGFILE%
      echo Server Type : MS-SQL>>%LOGFILE%
      echo User Name : %USERDOMAIN%\%USERNAME%>>%LOGFILE%

      echo check log file “%LOGFILE%”

      rem checking master.sql and alldbs.sql files are exist or not
      IF NOT EXIST master.sql. (
      echo master.sql not found.. Trerminating script>>%LOGFILE%
      echo master.sql not found.. Terminating Script…
      exit /b

      IF NOT EXIST alldbs.sql. (
      echo alldbs.sql not found.. Terminating script>>%LOGFILE%
      echo alldbs.sql not found.. Terminating Script…
      exit /b

      rem checking database connectivity
      echo checking database connectivity>>%LOGFILE%
      echo exit|sqlcmd -W -h-1 -E -S %ServerName% >>temp.log 2>&1

      if %ERRORLEVEL%==1 (
      type temp.log >>%LOGFILE%
      echo Press any key to exit
      pause > nul
      exit /b

      rem script for getting database names
      echo set nocount on >db.sql
      echo go>>db.sql
      echo select name from sysdatabases where name not in(‘tempdb’)>>db.sql
      echo go>>db.sql
      echo exit>>db.sql

      rem connecting to MSSQL and getting database names
      sqlcmd -E -W -h-1 -S %ServerName% -i db.sql -o dbnames.txt

      echo DATABSES List >%COMPARE%
      echo ************************* >>%COMPARE%
      type dbnames.txt >>%COMPARE%
      echo. >>%COMPARE%
      echo. >>%COMPARE%
      echo Databse Connection Status>>%COMPARE%
      echo ************************* >>%COMPARE%

      rem script for getting Sql Server Version
      echo set nocount on >db1.sql
      echo go>>db1.sql
      echo select SUBSTRING(@@VERSION,23,4) >>db1.sql
      echo go>>db1.sql
      echo exit>>db1.sql

      sqlcmd -E -W -h-1 -S %ServerName% -i db1.sql -o version.log

      set /p version=>%LOGFILE%
      echo Fetching Data…>>%LOGFILE%

      rem Excuitng script in master database
      sqlcmd -E -W -h-1 -s “|” -S %ServerName% -d master -i %script1% -v DB_ENV=’%Env%’ > temp1.log 2>&1
      if !ERRORLEVEL!==1 (
      echo Not Connected To database : master >>%COMPARE%
      echo. >>%LOGFILE%
      echo Error connecting to database : master >>%LOGFILE%
      echo ———————————————>>%LOGFILE%
      type temp1.log >>%LOGFILE%
      )else (
      echo Connected To Database : master >>%COMPARE%
      type temp1.log >>%OUTFILE%

      rem run the script in all databases
      for /f “tokens=*” %%a in (dbnames.txt) do (
      sqlcmd -E -W -h-1 -s “|” -S %ServerName% -d %%a -i %script1% -v DB_ENV=’%Env%’ > temp1.log 2>&1
      if !ERRORLEVEL!==1 (
      echo Not Connected To database : %%a >>%COMPARE%
      echo. >>%LOGFILE%
      echo Error connecting to database : %%a>>%LOGFILE%
      echo ———————————————>>%LOGFILE%
      type temp1.log >>%LOGFILE%
      )else (
      echo Connected To Database : %%a >>%COMPARE%
      type temp1.log >>%OUTFILE%

      echo. >>%LOGFILE%
      echo. >>%LOGFILE%
      echo find final report in “%OUTFILE%”>>%LOGFILE%
      echo find final report in “%OUTFILE%”
      del temp.log
      del temp1.log
      del db.sql
      del db1.sql
      del version.log
      del dbnames.txt
      echo Press any key to exit
      pause > nul

      • Pavan DBA said

        can u tell me what this script is for? seems sql server database related

  29. Dominic savio said

    Hi pavan. I have installed linux on VM Ware. I am not able to connect linux server to putty. Please help me.

    • Pavan DBA said

      check ur network settings. also u should select “bridged networking” in network adapter. for more details, check the video in my blog.

  30. Ravi said

    Hi Sir,

    I am facing this problem and i am not able to connect sys. I have been tried all google view.

    ORA-12528: TNS:listener: all approriate instances are blocking new connections

    Please help me..

    • Pavan DBA said

      its usually because DB_UNIQUE_NAME is not set correctly. check that first. if still you are facing issue, then you can add UR=A in tns entry. for sample tns entry, check google with “UR=A in tns”

      • Ravi said

        Hi Sir,
        I have already check add to UR=A in tns but same problem is coming.

      • Pavan DBA said

        you need to set ur db_unique_name value correctly. then this issue will not arise

    • tangy said

      Hello Ravi

      I recieved this problem earlier when my database is in nomount and trying to access it with sys@dbname. remotely. If you are doing the same while clonning, please add a static entry to the database in listener (SID_NAME = and restart the listener. This would resolve the issue.

      Please let me know what is your case if it is not above

  31. ellareddy said

    hi sir
    Interviewer is asking me the following questions ,plz give me u r answer

    1) How u r database mapping to ur application?
    2) How to justify u r redolog files are adequate to ur database ?

    • Pavan DBA said

      1. using tns entry or any other protocol
      2. by checking log switches occurring per hour

      • ellareddy said

        Thanq sir

  32. ellareddy said

    hi sir,
    i am getting following error in alert log file.
    Thread 1 cannot allocate new log, sequence 55998
    Checkpoint not complete

    i Checked in google for answer, is this correct answer (alter system set archive_lag_target=0 scope=both;)

    • Pavan DBA said

      that’s just a message and you can ignore it

    • tangy said

      If feel it can’t be ignored, Please generate a AWR report and find how much time is your database waiting for this event. If it is massive then increase the redo log size or increase the redo log groups or increase no of db writers.

  33. ellareddy said

    Hi sir,i am x stu of u r kt.
    I am getting this error in alert log file …………. wt is d solu?
    Memory Notification: Library Cache Object loaded into SGA
    Heap size 3067K exceeds notification threshold (2048K)

    • Pavan DBA said

      that’s a message for dba, not a error. so you can ignore it

    • tangy said

      It depends on how often do you get.

  34. Ahmed said

    Hi Pavan,

    I am an ex student of KT, I hope you are doing good.

    I need your expertise to resolve this issue.

    We have a production database on RHEL 4.0,ORACLE and we would like to do a refresh of production into RHEL 5.0,ORACLE for sandbox.

    I did the following but facing difficulty to proceed further. could you please help me out here.

    1)took cold backup of Production DB which is on

    2)transferd the dump to sandbox which is on

    3)created similar directory structure in sandbox and copied the DB files,control files and redolog files respectively.

    4) edited Pfile by removing adump,bdump,sga_max_size and adding diagonostic_dest and sga_target values.

    5) startup
    Error: unable to process initialisation parameter
    and it was taking $ORACLE_BASE/11.2.0/dbs/initprod.ora

    It gave me the below error
    “LRM-00101: unknown parameter name ‘diagonostic_dest'”
    ORA-01078: failure in processing system parameters

    I commented this parameter and started the db.

    6) startup upgrade
    It started in restricted mode.

    7) alter system set disable restricted session.

    Error: it gave an error to run utlu112i.sql for tz_version time record.

    10) @?/rdbms/admin/utlu112i.sql
    Error: ORA-04023: Object SYS.STANDARD could not be validated or authorized

    I cannot move forward….could you please help me with this.

    Thanks in advance.

    • Pavan DBA said

      you can’t do refresh between different versions of database like from 10g to 11g through cold/hot/rman backup. if you have different versions, then best way is to use datapump export/import.

      • Ahmed said

        Thanks for ur reply. I did the same and addressed this Issue.

        Pavan, One more thing. whenever i login to ur website, it usually hangs on me and takes sometime to load. whenever i try to type something it takes very long to appear in the comments box. So, I have to type in a notepad and paste it in the comments sections. Is this a problem from my browser side or some kind of regular behaviour from the website itself.

        Thanks for all ur help.

      • Pavan DBA said

        no. it might be problem with your browser as its working fine with many people

  35. piyush kumar said

    hi sir

    my self piyush kumar i am completed my IT in 2007 and MCA in 2010 both are distance mode now i want to make my career in DBA tell me is it possible or not for me please sir help me

    • Pavan DBA said

      yes. it is possible.

  36. Jayender said

    Hi Pavan sir,
    I have one concern in installation process on linux and windows can please clarify my questions.
    1)while we are installing any oracle version on linux OS we must to create ORACLE Group and user but installing on windows OS y we are not creating the same before we install the Oracle.

    • Pavan DBA said

      in windows also one group by name ora_dba will get created. but this will happen automatically in windows. in linux it won’t happen auto because linux is more secured than windows.

  37. sadan said

    Hi Pavan .i want learn oracle dba course .please let me know the best institute to learn the dba .

    • Pavan DBA said

      I am not sure from which location you are. if you are from hyd, you can try kanna technologies. I am providing dba training there. for more details, look the page in my blog on training

  38. subhasish said

    Dear Pavan
    Can u specify what are the topics you are covering on advance DBA course

    • Pavan DBA said

      sorry for replying late as i am out of city last week.

      configuring physical standby, logical standby
      upgrading both primary and standby database from 10g to
      how to apply patches on standby db
      active cloning, active dataguard etc

      the above r some of the adv topics tht will be covered in course

      • venkat123 said

        hru? iam fine. sir my name is venkat iam a .net developer i have 3.8 years of exp on that . MY question is am i change to oracle dba platform please give me advise it is help ful 2 my to my carriear

      • Pavan DBA said

        hi, as you are on development side, i think changing career is not good at this moment of time. if you wish you can change the technology and be continue on programming side. but if you are really interested on dba side, once you learn it, u need to start the job from the beginning which means all these 4 yrs of exp u have will become waste.

  39. Hii Pavan
    Plz answer these basic question:
    How oracle manage connection of sql* plus to oracle database both on the server and remotely from client machine

    • Pavan DBA said

      can you plz explain wht is mean by managing connections?

      • subha19821 said

        what i meant is :howconnection is made b/w sql*plus and oracle database from same node where database server is and from remote client as well
        Thanks a ton in advance

      • subha19821 said

        I mean how connection is made b/w sql*plus and oracle database

      • Pavan DBA said

        connection in local node:
        1. when provided with login credentials, pmon process will verify data dictionary and will authenticate the user
        2. if authentication is successful, then it will allow the connection.
        3. finally server process for that user connection will get created.

        connection from remote node:
        1. after giving login details, oracle will check for tns alias in tnsnames.ora to understand which server it need to connect.
        2. after reading tns entry, it will attempt a connection to that host (database server) using tcp/ip
        3. this info will be sent to listener which will accept connection and sent to pmon for authentication
        rest of the steps are same as in local node.

  40. […] Important Docs […]

  41. iliyas said

    sir when is your next batch starting for Oracle DBA

    • Pavan DBA said

      it is starting from 4th march (coming monday)

  42. Venkatesh Appajigol said

    Hi Pavan,

    could you please send me the Iz0-055 dumps planning for ocp 11g, since i am 9i ocp certified.

    Thanks for your help in advance


  43. Iliyas said

    hello sir……i have completed MSC(Comp) in 2010….as i have 2 years of gap, is their any course in oracle where a fresher with no experience can get a job easily, bcoz when we go for oracle DBA everyone asking min 2years of experience

    • Pavan DBA said

      i am nt sure about any such course, bcz most of the courses need some exp

  44. sridevi said

    hi Pavan sir i need 1z0 007 9i sql latest dumps please provide me dumps

  45. Ravi said

    Hi Sir,
    Please let me know that I upgrade our database from and upgraded sucessfully but
    DBCA utilities not working or nor run command.
    If I go
    start -> program ->oracel-oradb10g_home1 -> configuration and migration tools -> dbca
    not working.. please suggest me…

    • Pavan DBA said

      check ur environment varilables like oracle_home path and ORACLE_HOME/bin exist in path variable. i feel this is causing issue in your case

      • Ravi said

        Hi Sir,

        oracle_home path is correct.


      • Pavan DBA said

        for which problem you are talking about?

      • Naveed Mohammad said

        Hi sir Can u tell me the best institutions for Oracle apps DBA In hyderabad. Thanking u sir

      • Pavan DBA said

        you can try kanna technologies or techzone

      • Naveed Mohammad said

        thank you pawan sir

  46. Manju said

    Hello Pavan,

    I am bit confused about a requirement from users; I have several schema’s in 11gR2 database. The user needs rights only on specified schema’s and not on all schema’s!

    I mean for ex: I have Oracle schema A, B, C & D
    and users with name 1, 2 & 3

    User 1 is required to have all privileges only on the objects of schema A & B
    User 2 is required to have all privileges only on the objects of schema C & D
    User 3 is required to have all privileges only on the objects of schema A & D

    Could you please guide how to solve this.
    Thank you.

    • Pavan DBA said

      i feel it is better to create roles. you need 3 roles. 1st role u will to grant with privileges on tables of A&B n then grant that role to user 1. same u can do for other two users.

  47. kanthi said

    Hi Pavan,
    Gd Morning, I have a confusion in undo tablespace,let me explain in clear…one of the user is inserting bulk data at a time that time undo is using more space and prompt error like unable to extend the undo tablespace to 8mb so i add some more space to the undo later some moment user having same problem for the undo,then i checked in the dba_undo_extents it explains Active blocks occupy 10Gb,expired blocks occupy 4 Gb,and unexpired blocks occuply 22Gb and the undo_retention=900 sec, My question is how to clear unexpired blocks from undo tablespace, is this error is occur because of retention policy? could i need to increase undo_retention if i increased is there any issue will occur with database? previously my undo_retention is 20000 and i changed it to 900 sec..

    Sir please help me in this cases.

    • Pavan DBA said

      Hi Kiran,

      transactions will make use of space in undo till they get commit. the issue you are facing is, user is doing bulk insert and it is not getting required amount of free space in the undo tablespace. as u said, u have added some space after user got the error. still user facing issue means, the amount you added is not sufficient. still u need to add more as this is bulk insert.

      we cannot make unexpired blocks to be cleared. unexpired blocks will be cleared automatically when user issues commit/rollback.
      for this there are 2 solutions….
      1. add more space to undo
      2. ask user to issue commit in between. for eg, if user is inserting 10 lac rows, ask to commit after every one lac. by this unexpired blocks will become expired and oracle will reuse them again. if user don’t give commit, then it will not make undo blocks as expired.

      undo_retention doesn’t have any impact on the error you are facing. you said you decreased the value from 20000. when it is at 20000, did u faced this error during this bulk insert?

      • kiran said

        Hi sir thanks a lot,
        after user send the error message then i login into database and check for the undo_retention thier it is 20000 thats why i changed to 900 sec but i din’t get better result.

        Sir could i keep undo_retention= gurantee or need to keep old value i.e 20000 if i keep these values is there any slow performance in database side?

      • Pavan DBA said

        if u dnt get error when using value of 20000, then u can change to that only. reg database performance, we can’t until we set that value

  48. krish said

    thnk u

  49. kishore said

    can i install 11g rac on vmware player 4.0.1

    • Pavan DBA said

      yes we can.

      sorry culdn’t reply u bcz i am on leave since 3 weeks

  50. krish said

    hi pavan,
    can you help how to configureRAC in 2 node and single node in laptop using vmware workstation i.e.,step by step .pl send asap

    • Pavan DBA said

      plz refer below link

      sorry culdn’t reply u bcz i am on leave since 3 weeks

  51. pavankumar said

    hi sir,
    I serched performance tuing these 3 links opm4.pdf tuing otn new1.pdf slow database.pdf

    404 — File not found.
    You can create your own free blog on
    plz tell me how to open these links


  52. Naveed Mohammad said

    Very usefull. Thanks a lot

  53. ashok said

    hi sir, this is reg- oracle11g instlln on linux:
    in one of the screens where, when i click check & fix again,
    it is asking to run a script “/tmp/CVU_11.” as root user. so when i run it (as root user)
    ” -bash: /tmp/CVU_11. No such file or directory”
    & on checking the reqd file is not avbl in the directory called ‘/tmp/CVU_11.”
    so how to tackle this issue, for now i chose ignore all & proceeding…
    awaiting ur reply on this…

    • Pavan DBA said

      it usually won’t happen. just check if you are using correct name or not. bcz linux is case sensitive

    • hari said


      /tmp/CVU_11. this is the file you should run. May be you would have run runfixup and not

  54. sreekanh said

    This is sreekanth ..working as a test eng in a cmmi level 3 company.
    looking for securable platform..
    Oracle DB2 IS real platform im i looking for?
    plz help

    • Pavan DBA said

      sorry your question confused me. Oracle and DB2 are two different streams. Oracle DBA is definitely a good career option to choose for.

    • takecare said

      hii sreekanth, It is upto your interest if you really interest on learn database concepts.,If you have good understanding about db i will recommend you to join db related courses.
      If you choose oracle, it is high end database vendor and having same competition in the market. But every db having the its own architecture, if you learn oracle you easily upgrade your skills to db2 as well as teradata which are having similar architecture like oracle. If you choose sql server dba it is the vendor of microsoft it is no comparative to oracle.

  55. vemuri said

    Hi sir,
    While cloning oracle home i used the following syntax for creating and extracting tar ball to remote machine but i am getting an error and unable to create a tar ball and the error i am getting is cowardly refusing to create empty archive.The command used is
    tar -cvf 10ghome | ssh dba3 “cd /data1; tar -xvf -“.Plz help me

    • Pavan DBA said

      are you trying to copy the tar file in the same command? if so you need to use scp

  56. Dear Pavan,

    Could you please help me

    i try to connect to server database “tnsping dup”
    but i got an error

    tnd – 12560 protocol adaptor error

    but i check server listener on pro. work and also check “tnsping dup” its on work

    how to i will salve this problem

    pls help



    • Pavan DBA said

      can you please let me know your db and os versions? also OS from which you are doing tnsping

  57. venkat said

    Hi pavan,
    please provide any ticketing tool doc and what are the tools most of the companies using

    • Pavan DBA said

      hi venkat, i don’t have a doc for that and feel not req also bcz using it doesn’t need any doc.
      there are many tools companies will use, but mostly used one is remedy

  58. p roy said

    hi pawan
    im a big fan u .plz kepp helping like this…… completed my core dba.. as im fresher in this field plz help how to get a job

    • Pavan DBA said

      thank you very much. to get a fresher job as dba is a bit difficult task until unless you have OCP certification and knowledge on advanced topics like RAC, dataguard etc. so try to learn them if not already done.

  59. anup said

    i want complete core dba material ……………

    • Pavan DBA said

      hi, you can refer to which is oracle’s official documentation

  60. maaz said


    im getting this error after doing hotbackup

    ORA-01157: cannot identify/lock data file 6
    ORA-01110: data file 6: ‘/u01/app/oracle/oradata/test01.dbf’

    as my test01.dbf file is still in the same location

    • Pavan DBA said

      have you confirmed that the file is in same location using “ls -ltr /u01/app/oracle/oradata/test01.dbf” command? If not please do so. I am sure somewhere, mistake occurred

  61. kiran said

    hello sir,
    I need a steps for to export a single row from a table and that is located in differnet server (UAT) and the same which i exported that should be imported in differnet server(PROD).But the usernames are lying in both the UAT and PROD

    • Pavan DBA said

      you need to use QUERY option in export which will allow you to take single row export provided the where condition. for syntax, you can refer google

  62. Ahmed said

    Hi Pavan,

    I see the following lines in the alert log of my standby Database. Just wanted to ask you is this “Normal” or is it some kind of error which needs to be addressed.

    Tue May 15 15:45:27 2012
    Managed Standby Recovery not using Real Time Apply
    Parallel Media Recovery started with 8 slaves
    ORA-279 signalled during: ALTER DATABASE RECOVER standby database …
    Media Recovery Canceled
    Tue May 15 15:45:32 2012
    ALTER DATABASE RECOVER standby database
    Media Recovery Start
    started logmerger process

    Thanks In Advance.

    • Pavan DBA said

      yes this is normal

  63. f insta said

    Hi Pavan ,

    Just now i installed Oracle10g on Red Hat 4 but the problem is iam facing the error at the time of installing , i ignore that errors but completion is successful.After that i set batch profile ready to connect to sql as a sysdba , but it is not connected just prompt will comw back, What should i do , Please help me.

    • Pavan DBA said

      Hi Mahesh, it would be better if u let me know what error u got during installation. generally we should not supposed to ignore the errors during installation, otherwise it will lead to these kind of probs.

      also about connecting, please copy and paste the steps what you have done along with error message (if any)

      • Mahesh said

        Thank you for reply sir , iam issue the command at linux level : sqlplus / as sysdba , it is not connected to sql prompt just redirected to linux what should i do.

      • Pavan DBA said

        I feel there is issue with your installation. I recommend to do fresh oracle installation again….

  64. Ahmed said

    Hi Pavan,

    we are migrating our database from 10g enterprise edition to 11g standard edition.
    i have exported 14 schema`s using export utility from 10g, created a new db in 11g now, what do i need to do in the new database? Do i need to create the 14 users of the same name and create the tablespacesand then import it or can i do it directly without creating it. could you please help me with the Imp command that i can use to import it.

    Also will the partioned tables in enterprise edition have any impact on standard edition database?

    Looking forward to hear from you


    • Pavan DBA said

      Hi Ahmed, steps to follow are

      1. take export of 14 schemas from 10g EE database
      2. create all the tablespaces with the same name in 11g SE database.not necessary to create any users
      3. import the dump file with below command

      impdp directory=”directory name” dumpfile=”dump file name” logfile=”log file name” schemas=”provide all 14 schema names separated by ,”

      I believe partitioning is not supported in standard edition. as far as i know, it is there only in enterprise edition. so you may face issues during import

  65. krishna kumar said

    hello sir,
    i want to install oracle 11g R2 on rhel 6.1 32 bit .Can u tell the name of the package required and the installation guide.
    reply me asap

    • Pavan DBA said

      the advantage of 11g installation is, you can start the installation and at product pre-requisite page, oracle will list you what are the packages still it need to have in order to have successful installation.

      I found this link is nice –

  66. mohammedosman said

    Sir, please upload document from creating dataguard from RAC.

    • Pavan DBA said

      Hi Osman,

      I will upload it soon…. as there are some final touches need to be done

  67. kanthi said

    Hello Sir,
    Good afternoon,
    Sir I need a steps regarding schema refresh using datapumps.(In detail).I tried in internet
    but i din’t get correct method,plz help me sir?

    • for schema refresh you need to take export and do import. commands are similar to below

      expdp directory= dumpfile= logfile= schemas=”
      impdp directory= dumpfile= logfile= remap_schema=’old schema:new schema’

  68. abhiram said

    Hi Pavan,
    i got following error.
    create table test(n number(3,2));
    insert into test values(123.89);
    ORA-01438: value larger than specified precision allowed for this column

    NUMBER(3,2) doest allow value like 123.89 or 12.89.
    NUMBER(4,2) doest allow value like 123.89 or 12.89.
    but Number(5,2) allow 123.89 value.

    could u explain wat exactly Number(3,2) ?

    • ORA-01438:
      value larger than specified precision allowed for this column
      Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.
      Action: Enter a value that complies with the numeric column’s precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.

      but in your case, i can see that number(3,2) should accept 123.89. I need to have exact steps what you did?

  69. abhiram said

    Hi Pavan,

    suppose i have table ” Employees”
    And i created composite index on this table columns

    CREATE INDEX employees_ix
    ON Employees (last_name, job_id, salary);

    last_name col has high cardinality
    job_id col has high cardinality
    salary has low cardinality

    so could u tell me at wat time the index access done by query?

    1. only ” last_name ” used in query?
    2. both ” last_name ” and ” job_id ” used in query ?
    3. both ” last_name ” and ” salary ” used in query ?
    4. both “salary” and ” job_id” used in query ?
    5. only ” job_id ” used in query?
    6. only “salary” used in query?
    7. Only All three cols involve in query?

    7th option is correct but accord into oracle documentation even high cardinality cols use composite index with out specifying low cardinality cols in query .

    how this is possible can u justify?

    • As per my knowledge in any of the situation you use column in where condition, index will be used. also there are some rules defined on when query will use index. see this URL

  70. abhiram said

    Hi Pavan Sir,
    currently im working in one mnc bangalore as PL/Sql developer and i haue knowledge on CORE DBA and RAC with APPS 11i/R12 and Perl and Unix shell scripting but i want become good Performance Tuning expert currently i have 2+ exp as pl/sql developer.
    could please give valuable advice regarding what are fundamentals require and procedures to folow and any books i need read to get practical knowledge on tunig.

    • The only book that will give entire information is Oracle documentation. read “performance tuning guide” from

      • abhiram said

        thanks u sir……..

      • welcome

  71. Md.Asfi Alam said

    Hi Pavan Sir,

    I have got the chance to work as a linux admin in kuwait for cherrytec, so need your help in makeing the descission that should i join & will it be a good company to work with as read many thing abt cherrytec on net want to confirm it form you that how long you are working with this company & will be good for the career growth.

    • Hi, First of all I am not working in that comp. I am working in India. So not sure abour the company and its profile….

      • MD Asfi Alam said

        i have asked to you because you have posted some of jobs in your blog which belong to cherrytec group, that is why i have asked you….

      • Ok, no problem

      • MD Asfi Alam said


        As adviser i ask you to suggest than what will you suggest….

        need your help….
        Please help

      • Hi Asfi, As I don’t about the company, I cannot exactly say about this oppurtunity. But as it is for abroad location, you can think of. But do find out about the company before taking a decision

      • MD Asfi Alam said

        Thank You Sir….

      • you are welcome

  72. chandar said

    some times its throwing error , OS not found. I tried no of times but i instal Red Hat 4 its taken , i can’t understand this , plz help me.

    • I remember it will ask for manual partition in one of the screens. also if u r practising oracle10g/11g, then RHEL 4 is best than 5

      • i agree with u sir , but present situations we have to know everything as a DBA, i tried that no of times , it’s not asking manual partitions , iam allocating 60gb for Red Hat 5, it’s taken defaultly 5gb for boot and home , another whole memory taken one drive means ( one partition), my request is how to create new part ions from old one .

      • I can remember that we can do manual partition, but forgot which screen. The problem I tried installing 10g on RHEL 5 only once and we need to apply so many rpm’s which I feel not comfortable and changed to RHEL 4. Coming to learning, generally this OS installation will be done by Unix admin’s in real time, so not to much worry about it

      • chandar said

        Thank u very much sir.

  73. chandar said

    hi sir , i had instal Redhat 5 through vmware workstation 7, defaultly its taking partitions , but i wanna do manual partition , so plz help me.
    Actually problem is after creating the virtual machine its

  74. raghu said

    hi sir this is raghu(ur bd23 student) plz tell what is the difference between OCA and OCP

    • OCA – Oracle Certified Associate. generally this is treated as you are a learner of Oracle technology
      OCP – if you have this, it means you got some expertise on Oracle technology

  75. harsha Pydi said

    Just looked all ur comments … it is so helpful for refering..!!!

    • thank you so much Harsha

  76. Manohar said

    Hi sir, This manohar, please tell me, how to know “how many standby database configure in a environment”

    Thank you sir,

    • we can check that using log_archive_dest_1,2,3….. etc parameters. If we have only one standby dest_1 and dest_2 will only be configured. If we have 3 standby’s then dest_1,2,3 will be configured…. just like that

    • suman said

      In 10g you can configure 9 standby db’s, in 11g it increased up to 30.

      • Pavan DBA said

        yes you are right….

  77. Manohar said

    Hi sir, this is manohar am searching as 3+ yrs of dba. In every interview they are asking about “daily activities”. I have some confuse to explain it. Plz tell me some real time daily activities.

    Thank you sir,

  78. kiran said

    Hello Sir,
    good evening
    In a table 14 records are there and i insert one more record in that table,How could i fire a query
    to get recent inserted row.

    • Hi Kiran,

      Please read this

      • kiran said

        Thank Q very much sir…)

      • you are welcome Kiran

  79. madhu said

    Hi pavan, plz try to send answers

    What are the various options to create more than 1 instance?
    How would recover online redo log files?
    What are node apps?
    What is row migration and row chaining?
    What are the various options to shut down and startup explain all?
    How can you change database id?
    How can u recover a datafile which doesn’t have backup?
    How can u switch your logfile in rac database , if you are using hot backup?
    How can u disable or enable rac?
    How can u convert rac database to standalone or standalone database to rac?
    What is catcluster.sql?
    How will u recover the rman backup which has been deleted physically by system admin?
    What are dataguard protection modes?
    What are the main parameters to configure physical standby?
    What is the use of lo_archive_max_process?
    What is fal_server and fal_client?
    What is standby_file_management?
    What is segment space management?
    What is use of parameters in exp/imp?
    ignore,consistent,owner,direct,buffer and etc?
    how will u recover ur database if u have lost one of your log member and what happens when u lost it?
    What are the kernel parameters used to install rac?
    Why do u share private ip on all the nodes?
    How many ip’s do u require do install rac?
    What are different kinds of cluster file system in rac?
    How can u configure ocfs2?
    What is oifcfg, ocrcheck, ocrdump, ocr, voting disk?
    How will u recover voting disk,ocr ?
    What will u recover voting disk which doesn’t have backup?
    How many kinds of backup can u have ocr backup and voting disk?
    What are crs daemons?
    What are rac background process?
    When ur cluster nodes gets rebooted? Scenarios.
    How can u relocate ur rac vip?
    How to check whether ur standby is in sync to primary or not? Various
    What is the use of duplicate command in rman?
    What are main features of rman in 10g and 11g?
    What is use of gsd and vip?
    What is the use of srvctl? Some command also they may ask
    What is ifconfig?
    What kind of errors do u face when ur standby is not in sync with primary? Scenario
    What is use of doing force_logging?
    What kind of files would be take automatically with rman backup?
    How can u recover database if one of the archive is missing using rman?
    How to check incarnations?
    How many voting disk can u have and how many ocr files can u have and what could be there sizes?
    How will u check version of ur clustersoftware?
    What are the main parameters needed to convert ur standalone database to rac database?
    How many ways are there to convert standalone database to rac database?
    What is use of ons?
    What is taf?
    Why do we need instance specific undo tablespaces?
    What is process to delete node in rac and to addnode in rac?
    How can u analyze awr report?
    How can u maintain connectivity between all the rac nodes?
    What is load balancing?
    What is difference in using standby and using rac?

    Thanks ,

    • Hi Madhu, i hope these are a copy and paste of some interview q’s
      I am sure you can find all the answers for these questions in google. Have you tried that first?

      • madhu said


      • renna said

        Great ANS.


  80. kiran said

    Thank Q sir

  81. kiran said

    Hello sir,
    good morning,
    If I drop sysaux & undo tablespace,what will happen to the database?

    • some of the features like AWR etc will not work if you drop sysaux tablespace. Transactions will start using system tablespace as undo if you drop default undo tablespace

  82. arun said

    Hello pavan hw r u? Hpe u r superb.pavan u r blog is superb,u solve all the problems vich v face in dba but pavan can u plz advice for those people who r freshers including me who have jst completed oracle 11g dba n searching for jobs.Everywhere they r asking experience, as u have also started u r career as a fresher in this field,can u plz suggest wat v shld do 2 get job? Plz guide us n reply soon

    • Hi Arun,

      Thanks for your comments. Frankly saying getting a fresher DBA job is a tough one. All we need is to have patience and not leave any chance. we need to keep on trying with our friends for references, upload resume in all job sites and daily or atleast very frequently we need to apply for jobs that are posted in those job sites. this is all what we can do from our side….

  83. sridhar said

    Hi sir, this is sridhar.mukta. please provide me rman cloning document.

    Thank u sir,

    • its already there in my “Important docs” page…. plz take a look

  84. kiran said

    Hello sir,gd mrg
    Hope you are doing great,
    One of the interviewer ask question like this,how you check tkprof,i.e it is in instance level or in database level?will
    you please clear it?

    • TKPROF is the report which gives information on how a sql statement is working in diff phases of sql (parse, execution and fetch). so it shows both at instance and database level

      • kiran said

        Thank Q sir…)

  85. Hi sir,
    Explain me what is cursor sharing and list out parameters related to that.
    Srinivas k

    • this parameter is used to share cursors (a memory area to store value in the buffer/cache) between different sql statements. this will help in repeating I/O’s. for more info read this

      • thanks for the reply

  86. kiran said


    2.what are the roles other then connect,resource which are given to the programmers,business analysists in real world.

    • 1. I feel there should be some change to be done in sqlnet.ora file. plz check in google for solutions n steps
      2. appln team will tell what roles or privileges they require. we will follow that

      • kiran said

        can you please tell me by your experience what appln team told you in your previous assignments for roles given to programmers,etc.just giveme a real time example.

      • I din’t remember exactly because they will ask us to assign system or object level privs. sometimes they may ask to assign DBA role also….. but we need to give that only after mail approval from higher authorities like our manager etc

  87. kiran said

    Hello pavan
    I have a question, in most of the interviews they are asking about the directory structure of the database.can you please explain me how will be the structure of databases will be in different companies you worked.

    can you please tell me in DETAIL like how you answer tell me about your oracle dba experience.


    • Hi Praveen,

      I believe when they are asking about directory structure, may be they want to know whether your database is having Optimal Fliexible Architecture(OFA) is implemented or not.

      Based out of my exp, I saw a environment where people used
      /d01, /d02, /d03 – for datafiles
      /d04, /d05 – for redologfiles
      /d06,/d07 – for controlfiles
      /arch – for archives etc

      Here we need to identify that all the above mentioned mount points are different hard disks….. Hope that is clear. If not please post me your concern

      • kiran said

        pavan it helped me in understanding that we have different hard disks/mount points to store CRD files,but what about the logical structures like tablespaces.Can you please take a example in your company and explain in detail how the structure and size will be.
        what about the flash recovery area?




      • What I had explained in last reply is in my previous assignment. The project which I am working now has databases on ASM. So there will be no directory structure in separate. Even in most of companies there will be no specific structure will be followed.

        flash recovery area will be part of your Oracle home path and will be defined during DB creation. But I have not seen people using it either to store archives (generally archives will be stored on separate mount point as I told as per OFA) or backups (because backup will be taken to tape in real time)

        when someone is asking about your DBA experience all you need to tell is in what companies you worked and on what projects and mainly what is the role and responsibilities you handled there.

      • kiran said

        pavan what about the tablespaces, like can you give me example from your previous assignements like how the DBA’S will plan about the tablespace like the number of tablespaces,size,etc.

      • Hi Praveen, There will be no specification in no. of tablespaces and its size. Sometimes Application team will tell you how many tablespaces should be there and whats their size. But most of the times we need to decide that. You can have any no of tablespaces with what ever be the size. but always we need to make sure that datafiles are spreaded across multiple disks

  88. prathap said

    hi sir



    Number of Instances: 3




    i want to do coldbackup clonning how can i procced………………………… seeing above information u can understand it is a RAC …………..please provide me a steps for this clonning…………….


    • Hi Prathap, can you let me know if u r cloning from RAC to single node? or RAC to RAC?

      • prathap said

        Hi sir,

        iam clonning(cold backup) from RAC TO RAC…………………

      • 1. Install clusterware and DB software on the target nodes
        2. Take cold backup of source RAC database including spfile or pfile
        3. copy all the files including spfile or pfile to the locations in target (I hope you are using same directory structure in source and target. If not see my post in cloning category –
        4. start your instances in node by node

    • prathap said

      thanks sir…………………can u please suggest me good indian blog for APPSDBA ………………… u…………….bcoz pavan sir is not maintaining appsdba blog……………………………..


      • I found bit more interesting. but not sure if it contains all apps concepts as i am not apps dba to understand the posts there

      • prathap said




      • select trigger_name,status from dba_triggers where status=’INVALID’;

  89. Ravinder Reddy V said

    Hi Sir,

    I have one more question.

    How do we find the delay in the dataguard?

    • select sequence#,archived,applied from v$archived_log;

      the above will tell you which are shipped and which are not….

  90. Ravinder Reddy V said

    Hi Sir,

    This is Ravinder.

    I have one question that when we login to the database using
    sqlplus “/ as sysdba”
    it wont promt for the sys password. But I need to set it to promt for the sys password.

    • Hi Ravinder, its not possible to set a prompt to ask password for SYS user when u r there on the oracle db server…. u may give a try with trigger (but i am not sure how far it will work)
      also may i know the reason why u need this?

      • Ravinder Reddy V said

        Hi Sir,

        Thanks for your reply. These questions are asked in one of my interview.

      • you are welcome Ravinder…

  91. nagendra said

    hai sir,

    i am nagendra, i am regularly following ur blog,it is very nice.

    i have a doubt about partioned tables,how to remove partion on a single column(table is also partioned) ?

    please give the solution.

    thank you

    • i hope your question is somthing like this. If i have a table partitioned on column A and B. Now how to remove B column on which already table is partitioned.
      Am i right?

      If so, then i believe we need to re-org table i.e need to create dummy table with only partitioned on A and insert the data, then drop original table. ofcourse, am not sure… am also checking on this

  92. Hai sir,I have one doubt.please send some useful information about toad and putty.

    • Hi Mahender, there are lot of docs available in google. just search them and start reading them….

  93. Thiru said

    Dear Pavan,

    We are on “32-bit,Window 2003 Server” and planning for an upgrade from to

    Could you please help me with any related documents.


    • I don’t have any specific document. But following are the brief steps

      1. install software in other oracle home (it should not be in same location as
      2. Download patchset from metalink and apply it. (there will be a readme document for installing this patch. you can follow that)
      3. start DBUA from home. this upgrade assistant will help you in upgrading database.

      This link will help you alot –

      I prefer you can upgrade to which is the new version.

      • Thiru said

        Thanks for the guidance Pavan…It will help me a lot for sure….

      • you are always welcome

  94. sai krishna said

    Hi sir,
    I would like to know about oracle cpu patches. Up to my knowledge for every three months oracle releases this patches for security related issues.Recently at july 17 also oracle released this patches and i am not able to understand what’s the patch number /patch id released at that date how to know this patch numbers can i download this patches for practice purpose if so how can i ?

    • Hi Sai,

      you are right. patch number will be changed and it depends on paltform. for example below are patch numbers for different oracle versions on unix OS CPU Patch 12419321 CPU Patch 12419278 CPU Patch 12419265 CPU Patch 12419258 CPU Patch 12419249

      You can download these patches only if you have my oracle support (metalink) access which is a licensed online support by oracle…..

      • sai krishna said

        thank’s sir

  95. sai krishna said

    My production database acting as a standby database and standby database as primary. If i want to upgrade my production database from 10g to 11 total downtime required is 10 hrs but client wants to do with the task with in 5 hrs what will u do at that time? and can we configure primary database in 11g and standby database in 10g will the binaries of 11g recognized by 10g

    • Hi Sai, in such case you can make standby available for users and upgrade primary to 11g, then rebuild standby with new 11g primary. this will reduce the downtime.
      10g standby will not recognize the 11g binaries….

      • sai krishna said

        In continuation what about the archives generated at standby, during the primary in down time, The archives will not be applied to primary from standby right and if i upgrade the primary to 11g there will be some log gap naa how to resolve this?

      • thats the reason, its better to upgrade both of them at same time. you can check that from upgrade doc. check in metalink also for docs…

  96. ramesh said

    Hi pavan,

    What is the importance of orapwd file in networking?


    • that file is used to authenticate a user who is connecting as sysdba from a remote machine to the db. read more from oracle docs

  97. venkat said

    hai pavan, plz try to send answers .

    1.what is the diff between cmm5 d cmm3?
    2. What is your typical day like?
    3. What DBA activities did you to do today?
    4. Do u consider yourself a development DBA or a production DBA and why ?
    5. Are you a nuts – n- bolts DBA or a tools-n-props DBA?
    6. Give the reasoning behind using an index ?
    7. what is the difference between the Archivelog mode and noarchivelog mode?
    8. what column differentiates the v$views to the GV$views and how ?
    9. explain an ora-01555?
    10. differentiate between $oracle_home and $oraclebase?
    11. How would u determine time zone under which a database was operating?
    12. you have 4 instances running on the same unix box. How can you determine which shared memory and semaphores are associated with which instance?
    13. what is the recommended interval at which to run statspack snapshots, and why?
    14. How would u edit ypur CRONTAB to schedule the running of /test/ to run every other day at 2PM?
    15. How would u begin to troubleshoot an ORA -3113 error?
    16. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s a failed “, how do u approach troubleshooting this issue?
    17. How would you extract DDl of a table without using GUI tool?
    18. you’re getting high “busy buffer waits “ – how can you find what’s causing it?
    19. what query tells u how much space a table space named “test” is taking up,
    and how much space is remaining?
    22 Database is hung . Old and new user connections alike hang on impact. What do
    you do / Your SYS SQLPLUS session is able to connect?

    • 1. those are levels assigned to companies to differentiate them based on some standards
      2. search in google
      3. search in google DBA, bcz they will face lot of issues daily in google
      6. index will make searching faster
      7. archivelog mode will helps us in database recovery up to the point without data loss
      8.inst_id will occur when server process unable to identify old data from undo or new data from datafiles
      10.oracle_base is location whr you install oracle s/w. oracle_home is home path for database
      11.chk in nls_database_parameters view
      12.generally we will not look for it
      13.its out dated. now we are in 10g n it will be done every 1 hour
      14. using crontab -e command
      15. chk if any other session is connected or chk if mount point is full
      16.we can check in views related to JOBS
      17.using dbms_get.ddl package
      18.there are no free buffers in buffer cache, it may occur due to small size
      19. dba_data_files n dba_free_space
      20.chk why it is hung. most common reason is archives got full or sessions exceeded the limit of SESSIONS parameter in pfile or spfile

      i prefer you read oracle docs where you will get entire knowledge on the q’s you asked.

      • venkat said

        thanku very much

  98. poornadba7 said

    HI Pavan,

    hope u r doing excellent..i am working as DBA and mostly i worked on linux environment but i’ve changed my company recently. But here they working on windows ..i am finding difficult to this windows..can u let me know ur suggestions how i can cope up quickly for this windows..paging and how can we run suggets me

    • Hi Poorna,

      as you might have observed by now that basic admin of database is same either in windows or linux. only difference will be in scripting and some OS commands. so i suggest you to refer to windows commands in google. it will be very helpful.

      • poornadba7 said

        Thanks allot Pavan,

        Actually i need to configure 2 node windows 2003 cluster in the next week. Can u help me out as i was never configured clustering.


      • Hi, i hope you are talking about installing oracle RAC cluster. if so you can get good docs from google.

  99. Neeti said

    Hi Pavan
    How to recover the control filerif all mirror copy lost and no backup is there ? Tell me all steps

  100. kumar said

    Hi Pavan…
    Which background process will write alertlog file?

    • I suppose you asked these q’s thru mail. I answered them… plz check

      • nasir said

        Which background process will write alertlog file?

      • Pavan DBA said

        Nasir, every background process will have authority to write into alert log file

  101. kumar said

    What is difference between incident management and change management? With examples……

  102. bheeshma said

    hi pavan ,
    how can we know that this error belongs to user or core or background in alert log file .bcoz when am practising some error came like control files not found .then where should i search that in alert log i mean in which dump.

    • Alert log always will give you trace file location in case error contains more information. depends on location like bdump, udump etc you can decide which trace file it is. In your case, you need to fisrt search in alert log file. (but i assume there will not be any trace file for the same). I think you know that alert log will be there in bdump

      • bheeshma said

        k thanks for the reply ..cheers

  103. Thiru said

    Hi Pavan,

    At the time of auditing,we found that PUBLIC in database have DBA role by default.we can see that by this query,”select * from dba_role_privs where GRANTEE =’PUBLIC’;”Later I found that it will be created at the time DB creation.My question is If I revoke the DBA role from PUBLIc,will it affect any of DB operations.

    Please help me..


    • Hi Thiru,

      It is very dangerous to have DBA role to PUBLIC schema as it will reflect the same for all the users in the database. As you said, yes PUBLIC schema will be created during Db creation. But someone might have granted this DBA role to public without knowing its affect.

      Please remove it immediately and revoking will not have any affect on the database.

      If want to know, how granting privs to PUBLIC is dangerous, see this

      • Thiru said

        Thanks Pavan….

        Thank you very much for reply with explanation…

        Keep rockin…

  104. kumar said

    Hi Pavan sri,
    This is Srimannarayana from Hyd.Today only i see u’r site.It is excellent for DBA’s.First of all i congrats u for sharing the knowledge to others.I request to u to keep the details how to face the interview as a fresher and an experience guys.Prepare the list for this like…
    1.Self Introduction
    2.Roles & Responsibilities
    3.Topic wise FAQs and related indepth concepts.
    4.Ora errors and real time problems u r faced and challenging issues.
    Put heading like “face the Interview easily”

    I hope u do this well within a days.

    • Hi Sriman, its a nice suggestion. but in google, already you will be getting all these questions with answers. I have that doc. Let me upload that into my “Important docs” section

  105. Thiru said

    Hi Pavan,

    You are rocking as usual….keep rockin.

    Im using windows 32 bit os with Database.I need to develop a script that will monitor tablespace size and sent an alert to email.I did the same in linux but I doesnot know in windows.

    Please help me out.


    • Hi, the best way is to do that through EM. It will be easy to monitor and get alerts…..

      • Thiru said

        Thanks Pavan….

  106. Samyak said

    Hi Pavan ,
    Nice blog and awesome docs…looked at RAC installation doc … really good

    thanks for share …

    • thank you Samyak…

  107. Hi Pavan,
    Please upload doc regarding ‘How to connect two virtual machines in VMWARE and configuring RMAN on VIrtual MAchines’.

    • sure, will upload them soon

  108. praveen said

    pavan thanks for the great blog that you have created.can you please tell me how to refresh data from production to development database in windows as well as in linux?

    • hi, it depends on type of refresh. if you are refreshing a single schema, always its preferred to use export/import or datapump. if it is entire database, these days we are using RMAN cloning very widely. otherwise hot or cold backup can also be used.

      • praveen said

        can you please provide a document or step by step procedure for datapump and rman cloning also.
        Thanks for the reply it cleared my doubt but i wanted to do it practically in my testdatabase.

      • For datapump, you can refer to oracle documentation. all we need to do is expdp help=y and start practising all the options. For RMAN cloning, you can refer to a doc in my “important docs” page

  109. abhiram said

    thank you sir for earlier answers
    When a redo log file is full, LGWR will move to the
    next log group.
    if i haue 3 redo log groups each contains 2 log files,if lgwr writing to the first log group. will it(lgwr) move to second log group after writing 2 log members or 1 log member ?
    does lgwr assign same log sequence number to log members with in redo log group ?

    • 1. LGWR will move to 2nd redolog group only after writing into both the members in 1st group.
      Here we need to understand how LGWR will write into them. LGWR will not write to members within group in sequential order, rather it will write parallely. (but LGWR writing parallely will happen only if Asynchronous IO is enabled at OS level). If LGWR writes sequentially and a crash occurs, we will be loosing some data

      2. yes. Oracle assigns same number. More clearly to say common log sequence number will be there for all the members in same group

  110. shiva said

    Hi Sir

    This is shiva
    Trying to get DBA job

    i’m very thankful to you for your blog n imp docs
    i’m learning some new things by watching your blog..
    and i also i’m getting knwldge in interview point of view

    Thanks Alot…

    • Thank you very much Shiva for your comments…. Wish you all the best !!!

  111. abhiram said

    If the database is running in ARCHIVELOG mode and the log file group to which the member belongs is not archived, then the member cannot be we know names of this log members any view is support or not ?

    • I had give query for your ealier question. the same query applies here. You can drop a redolog group only if its status is INACTIVE. if you see CURRENT or ACTIVE, then you cannot drop it

  112. abhiram said

    hi pavan sir, i need to add online redo log group to production database according to restriction add additional redolog group and drop existing one,but how i know if im tring drop redolog group is online(lgwr writing ) or not ?

    • select group#,status from v$log; – this view will tell the status, if you see some group as CURRENT that means LGWR is currently writing into that group

  113. baji said

    How to get size of a table? and difference b/w index rebuild online and index rebuild?

    • checking table size
      SQL> select sum(bytes/1024/1024) from dba_segments where segment_name=’table_name’; —> this will give size in MB

      Rebuild online will allow you to issue DML statement on the table where rebuild doesn’t

  114. baji said

    how to know all privileges of a particular user? how to know sql text that is entering by users?(which view contains sql_text column?)

    • you can use below six views

      2. you can get that info from v$sql view. you can also enable auditing for tracking user’s sql statement

  115. baji said

    what is the importance of OraInventory? what is importance of FAL_SERVER and FAL_CLIENT parameters in Dataguard? How to check the load on database at db level and os level?

    • OraInventory will store all the oracle products installed on a particular server. So during upgrade or migrations, it will help us to know how many homes existing etc kind of information.

      FAL_SERVER and FAL_CLIENT are used to sync the standby database with primary in case archives are not shipping to standby location because of network failure etc.

      Load at OS can be checked using top command. on database its better to use EM if version is 10g

  116. baji said

    Hi sir,
    can we alter sga_target parameter without altering sga_max_size?(sga_max_size=10g and sga_target=6g….. i want to alter my sga_target to 8g)….
    and i want to thank you because i am getting more knowledge on oracle dba by watching your blog… thanks alot.

    • yes you can alter it without touching sga_max_size. only when you want to change sga_max_size value, you require a restart of the database as it is a static parameter

  117. baji said

    How can we change “sys” user password? If checkpoint is not functioning well then what we have to do? can we change check point occurring time?(default is 3 sec.)

    • Hi Baji,

      We can change sys password in two ways
      1. SQL> alter user sys identified by ;
      2. recreating password file using orapwd utility
      $ orapwd file=orapw password= force=y

      Regarding checkpoint, it is not by default 3 sec. Checkpoint time can be changed using log_checkpoint_interval till 8i and fast_start_mttr_target from 9i. Also, if checkpoint is not functioning, it will leave the reason in alert log file. so we need to check there for the reason.

      • baji said

        we have to login as which user to change password of sys?

      • you can login as system user. but if you are on the server, you can use
        $ sqlplus “/ as sysdba” – this will not ask any password

  118. bharadwaj komaragiri said


    It’s very much appreciable if we can have Oracle 11g installation on Linux doc

    • hi, i will upload that doc in a day or two into my “Important docs” page

  119. baji said

    Please tell me some patching and cloning issues that you have faced? it will be useful to me.

    • hi baji, i would be posting some articles in the blog soon… keep watching

  120. baji said

    hi sir,
    what is the importance of ‘oratab’ directory? and how to find how many cpu’s are there in our system (i know only one way, that is cat proc/cpuinfo), is there any other way?

    • oratab file will be used to identify how many databases are there totally on the server, irrespective whether they are up or down. Also DBCA will use the info from oratab to find any database information like SID, ORACLE_HOME etc.

      Reg cpu’s we will also use the same command and its the easy way i believe…

      • shushmith said

        u can find from os level ps -ef| grep cpuinfo

  121. Dileep said

    Hi sir,this is Dileep looking for a Apps DBA job,your blog is helping out a lota.You are doing a gr8 a job.Keep blogging…

    • thank you Dileep for your words….

  122. baji said

    hi sir,
    what are the daily activities of a Oracle appsDBA. I am trying for a job as oracle appsDBA. please help me.

    • Hi Baji, unfortunately I am not yet into apps dba. so not sure of daily activities. but i think you can get the things by googling it

  123. Sai Preetam said

    Hi Pawan,
    Its really great that some one like you is helping out people like me in every aspect view DBA point view.
    I am very new to the DBA community and I would request you to share with me the starting guide for Oracle DBA. I would love to attend your sessions, but my time and situation doesnt permit me to do so


    Sai Preetam

    • Hi Sai,

      Thanks for your kind words, but i am not doing any great thing here…. just sharing my knowledge and experience.
      Its good to know that you are looking into DBA. For starting phase, I recommend to read a book by SAM.R.ALAPATI titled as “Expert Oracle database 10g administration”. (publisher is APRESS)

      You are always welcome to attend the sessions, but i too feel bad about your timings….

  124. vemuri said

    hi sir,
    what is the meaning of capacity plan in storage management

    • Capacity planning is nothing but checking free space in all the tablespaces of a database and make it ready for future space usage to avoid last minute space constraint problems…

  125. sandeep said

    may i know the difficult task you faced as oracle DBA

    • handling multiple projects at same time for different clients is typical task that i handled till now which am doing it now 🙂 if you are asking for technical side, they are so many like db recoveries, handling performance problems etc

  126. Rafiq said

    Great contribution ever made by an Oracle Expert from hyderabad.
    Most helpful blog one can find.
    Thanks a lot pavan.

    • Hi Rafiq,

      Thanks a lot for your comments, but am not a expert still trying to acheive it

  127. sudhir said

    I need to learn Oracle RAC(real application clusters) from next month(oct 2010) first week, i have no time spend more then 10 days, please advise me any crash course available that time, send me fees details.
    Thank’s & Regards

    • i had sent mail on this to you

      • pavan said

        Could you also let me know , if there is some one to train me on RAC + Golden gate.
        i am eagerly waiting on someone to help me out on this issue.
        Could also email me the info

      • Hi, as of now i don’t know who can train on golden gate. KANNA TECHNOLOGIES is planning to start RAC also. you can leave your mobile number in their enquiry form, so that they will intimate when the batch starts

  128. gandhi said

    sr i get like ubove when im installing 10g, after i typed command
    vi /etc/systel.conf now what can i do sr…
    how set the kernel parameters…..
    plz tell me the process sr im folloing ur 10g installation process….

    • i dropped a mail to u on this. if still having problem, chat with me in gtalk

  129. gandhi said

    # Kernel sysctl configuration file for Enterprise Linux
    # For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
    # sysctl.conf(5) for more details.

    # Controls IP packet forwarding
    net.ipv4.ip_forward = 0

    # Controls source route verification
    net.ipv4.conf.default.rp_filter = 1

    # Do not accept source routing
    net.ipv4.conf.default.accept_source_route = 0

    # Controls the System Request debugging functionality of the kernel
    kernel.sysrq = 0

    # Controls whether core dumps will append the PID to the core filename.
    # Useful for debugging multi-threaded applications.
    kernel.core_uses_pid = 1
    Type :quit to exit Vim

    • pratap said

      Hi Pavan,
      This is great as ur helping us (updating the blog every day),a small request it wud be much helpfull for us ….if u post erros/problem which u will face every day ….like opening database,issue with RMAN,when u add datafile/TB ,when u take backup….in a separate TAB , it wil be much help full for us
      Who just joined in the company or who are attending for interviews

      • Hi Pratap, thanks for your comments

        ofcourse i am trying my best to post all those what you asked and some are already there. just browse in categories…all you need is to go through each article

        i am sure it will help alot

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: