Pavan DBA's Blog

The DBA Knowledge Store

CONTACT ME


Folks, you can contact me in below ways…

Personal Mail id : mymailbox.21@gmail.com

Facebook id : Pavan Dba

Twitter id : @pavandba

Linkedin – http://in.linkedin.com/in/pavandba

676 Responses to “CONTACT ME”

  1. Prakash Badhani said

    Hi Pawan,

    I am looking Oracle Dba Training,

    I am BA Student and you can say non IT student Can i do DBA Training and make career in DBA Field.

    Thanks
    Prakash Badhani
    MObile 9899693875
    Mail Genpactprakash@gmail.com

    • Pavan DBA said

      yes, a non IT person can also do dba training. for training details contact admin@thetechmaster.in

  2. Mohanraj said

    I want to learn oracle apps dba . Please provide your suggestions

    • Pavan DBA said

      It is good idea to go with it. I believe you are already an Oracle dba.

  3. Ronald said

    Sir, keep question section column for visitor so that they ask real time or interview ques to you….
    Thank you.

  4. Ronald said

    How to backup database in standby db when small space available, primary db data large?

    • Pavan DBA said

      you can take compressed rman backup. if still space is not sufficient, you need to add space first.

  5. vangapalli ramya said

    Hai sir

    I am looking for Oracle DBA online training .

    I don’t know where to start with .I used to work to CTS as a fresher and after that because of my baby had three years gap .

    So i want to start my carrer again ..can you please help me with what to start and how to prepare .

    Online training s for Unix Linux DBA

    • Pavan DBA said

      please contact admin@thetechmaster.in

  6. mithilesh said

    what is mount point and . IF YOU WANT TO EXPORT SCHEMA OF 100G AND SPACE LEFT ON MOUNT POINT IS 20G THEN WHAT SHOULD YOU DO

    • Pavan DBA said

      mount point is also called as file system which is storage space (just like D drive etc in windows). if there is no space in the mount point, in 11g you can try compression during expdp. but this doesn’t gurantee that 20gb will be sufficient. we need to have more space in the mount point.

  7. arun said

    hi am looking for some one who can give me oracle core and rac dba training with real time examples.

    • Pavan DBA said

      for dba training, plz contact me at mymailbox.21@gmail.com. i will provide more details.

  8. Jhansi said

    Hi pavan sir,
    I completed my b-tech in 2013 with specialization IT, I joined for oracle core dba course, I am planning to go for RAC and apps dba too.
    My question is I am planning to take advanced technology course too to get job quickly, so which course is best, I am totally confused with different peoples different advices , some are suggesting big data(hadoop), some are suggesting exadata, some are suggesting SAP, which course is best for for me?
    For learning exadata , I heard that cost is too high, I am afraid of that too, and one more question, if I get job after learning core dba along with RAC and apps dba, will the company people will provide coaching facility on exadata or big data what ever technology they need?
    one more thing which course is having bright future or opportunities big data or exadata or SAP?
    Which course is suitable for oracle DBA to enhance my future?

    • Pavan DBA said

      depends on the company and project you join, some will provide you learning on exadata. but as of now, dba, apps dba with rac is fine

  9. Balu said

    Hi Pavan,
    I completed my bachelors in 2012.Currently wrking as a Recruiter.I want to shift my career and i am planning to learn either Oracle DBA or Oracle Apps DBA..Please suggest me the best one, which will have a good future

    • Pavan DBA said

      both are having equal opportunities. i suggest to learn both

  10. Ramesh said

    Looking for DBA job in Hyderabad

  11. Ankit Mishra said

    hi sir
    i m doing Mca .and i want to start reading for DBA .i have some basic knowlage of SQL.Could u tell me how to start study for DBA and book for preprastion……?

    Thankssss. sir

    • Pavan DBA said

      you can do self learning by reading oracle documentation and other books. list is there in my blog’s “recommended books” section.
      also there are lot many videos in youtube.

  12. kiruba said

    can you tell me how to recover if controlfiles and redolog files got corrupted at the
    same time , if there is no backup

    • Pavan DBA said

      if u have controlfile trace u can recreate controlfile. during that time u will open db with resetlogs which will re-create redologs also

      • satish said

        I want to know the online classes information
        course: Oracle dba 11g
        batch starting date ,free details

      • Pavan DBA said

        hi satish, batch will be starting approx by 20th july 2016. plz send email to mymailbox.21@gmail.com to know other details.

      • sibu varghese said

        Hello Pavan.. have you tried this example?

        *************************************************************************

        Kiruba said

        July 2, 2015 at 12:06 PM
        0 0 Rate This
        can you tell me how to recover if controlfiles and redolog files got corrupted at the
        same time , if there is no backup

        Reply

        Pavan DBA said

        July 2, 2015 at 11:21 PM
        1 0 Rate This
        if u have controlfile trace u can recreate controlfile. during that time u will open db with resetlogs which will re-create redologs also

        Reply

      • Pavan DBA said

        yes. i did it lot many times

  13. I have a small doubt that how many redolog groups can be created in the database please clear mee.

    Thank U

    • Pavan DBA said

      theoretically unlimited, but practically 1000+

      • Thank u

  14. Sharath kumar Vadla said

    Hi Pavan,

    Greetings of the day.

    This is Sharath working for CTS as a Team Leader for BPO. I heard a lot about you and the skill set that you have is tremendous in DBA. I have around 6.5 years of experience in health care industry (payer and provider – voice & non-voice). But I would like to swap from this stream to IT, which is my goal. So, do you think it is possible to do this course and get in to that stream? I am a laymen of this course. So, need much info about it.. Request your help in this regard.

    Thanks,
    Sharath
    Sharath.vishwa@gmail.com

    • Pavan DBA said

      yes Sharath. it is possible to learn and change the career to IT. If you need any more details, whatsapp me at +971558926727

  15. kishore said

    Hi pavan,

    i faced an interview question and didnt find the answer. question is what is the un editable parameter in pfile. please reply the answer.

    Regards,
    kishore

    • Pavan DBA said

      question is not clear. in pfile we can edit any parameter, but only dynamic parameters will be effected immediately.

  16. Ganesh said

    Hello Sir,

    I am an IT professional having 4.5 years of experience into Application support profile.
    I have cleared my OCA certification for Oracle 11g version by reading theortically.

    I want to secure a job in Oracle DBA domain and willing to take training from you.
    I am based out of mumbai.

    Can you please assist?

    • Pavan DBA said

      hi ganesh, i am not giving training now.

  17. swapniljadhav50@gmail.com said

    want to learn oracle apps dba. did my b.tek in 2010

    • Pavan DBA said

      what help you need?

  18. BALAGOPAL R said

    hi pavan
    i want to do hadoop course from a bast placement oriented institute from chennai or bangalore.so plz tell me some top institutions name…………..

    • Pavan DBA said

      hi i dnt have idea about it

  19. Amarnadha Reddy K said

    Hi Pavan,

    This is Amarnadha Reddy K (empid:484280, Cognizant) ,I am your old student.I got Opertunity in CTS (Chennai) and joined recently in Chennai Location.

    Present i am in PDP, So Please let me know if your having any opertunity in your projects in Hyderbad.

    Thank you so much..

    Regards,
    Amar

    • Pavan DBA said

      good to hear. hope u got project by now

  20. BAJI SHAHEED SHAIK said

    PLEASE HELP ME TO GET JOB IN A FIELD OF DB2 DBA (IBM DATA BASE TOOL ) SIR .

    • Pavan DBA said

      any openings will be posted in my blog itself

  21. Anand T said

    Hi Pavan

    Hope you are doing well. Please help me ,I need a unix shell script to check for multiple RMAN jobs running for DB.if there are multiple RMAN Jobs running then it should be trigger an email .

    Thanks,
    Anand T

    • Pavan DBA said

      u can get one from google

  22. Krish said

    Hi Pavan sir,

    I am a junior DBA willing to take OA exam. Kindly send me the dumps for SQL funda to my mail ID : “deepakbkm.v@gmail.com”. Pleae do the needful.

    Thanks in advance !

    Regards,
    Krish

    • Pavan DBA said

      plz send mail to mymailbox.21@gmail.com

  23. David said

    Hi Sir,

    We have block corruption in our NoArchivelog Mode DB. There is no rman backup available. Please suggest us to fix the block corruption.

    • Pavan DBA said

      any other backup available?

  24. Javeed said

    Sir i am Javeed Ahmad. i have done MCA in 2008 and right now i am working in High Court of J&K as technical support. i want to do Oracle DBA. i need your help. from which place shall i do my DBA. please sir help

    • Pavan DBA said

      u can do it online now from any good institute

  25. kishore said

    Dear Pavan,

    my database SGA Size is 5Gb and my Table size is 10GB. When i SELECT 10GB table How it is going to fit into buffer cache.

    I want to know internal process of how buffer cache will run when table size is greater than SGA size.

  26. Raja.P said

    I need help to prepare for “PeopleSoft Enterprise 9 HRMS Fundamentals (1Z0-218)” exam and I’v sent you an email regarding this. Please reply.

    • Pavan DBA said

      hi raja, i haven’t received your mail. also, i don’t have much idea about functional side.

      • Raja.P said

        I sent mail to your mail id: mymailbox.21@gmail.com. If you are not able to help me, please refer some one from your knowledge whom I can approach and get help from them. It would be grateful to you, if you could do the same.

      • Pavan DBA said

        sure. let me reply

    • bheemaraju said

      hi sir gud morning i am bheemaraju i need oracle rman clonig database steps pls send to my mail bheemaking3@gmail.com

      • Pavan DBA said

        already there in my blog. plz check

  27. Ramakrishna Y said

    hi boss,
    when i am tryng to edit a pfile it doesn’t allow me save the changes it says: “access is denied”

    • Pavan DBA said

      check who is the owner of the file and u need to modify as that user only.

  28. Anand said

    I am stuck at one issue and unable to resolve it. It would be very kind of you if you could stretch some support to me.

    I have oracle db installed into my system and i want other team member to get connected to my db via toad. They have client installed in their machine.

    I am facing error as follows:

    ORA-28547: connection to server failed, probable Oracle Net admin error

    Although I have tried all the solutions given in google but could not have it resolved.

    • Pavan DBA said

      from their machine do they able to do tnsping to server?

  29. Hii i am 2014 I.T. passed out.. currently im working as a oracle forms and reports developer in a company.. im just dealing wit some basics of oracle form builder & reports builder, and also dealing with sql queries.. is my exp is suffiicient to choose DBA career??.. and which DBA specialisation is suits for me.. ?? for further jobs.. pls give ur valuable suggestion sir.. tnx for reading.. rly must..

    • Pavan DBA said

      hi gopi, yes dba will be a good choice for developers.

  30. Nagu Abbadasari said

    Thanks

  31. akhil said

    Hi

    i have to prepare data purging plan for a company based on date column of tables which has multiple indexes and relationship with other tables also.the size of data need to be purged is around 10000K records. there is not on delete cascade feature and no indexes on foreign key constraint.also i need to perform this purging in live database only so please share your thoughts to finalize the purging strategy.

    akhil

    • Pavan DBA said

      you can make indexes unusable and then go ahead for delete. this will run faster. but after delete u need to rebuild indexes. also before starting the activity keep enough space in archivelog destination as it would generate lot many archivelogs.

  32. sreerekha said

    Hi Pavan, I tried to enable Database broker as below.

    DGMGRL> ENABLE CONFIGURATION

    But this operation is not getting completed. It seems like stuck. When checked from another window it gives the below result.

    DGMGRL> SHOW CONFIGURATION

    Configuration – orclprm

    Protection Mode: MaxPerformance
    Databases:
    orclprm – Primary database
    orclstb – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    ORA-16610: command “ENABLE CONFIGURATION” in progress
    DGM-17017: unable to determine configuration status

    Please help me to resolve this situation. I followed your document. The only difference i made is used same host for primary, standby and dg configuration. Is it not possible to do like that.

    • sreerekha said

      Thankyou Pavan. It was listener/tns issue. Resolved it.

    • Pavan DBA said

      did u used VMware machines?

  33. harsha pydi said

    Hi Pavan,

    Recently ,I have tried to deploy the oracle 12c agent installation on windows platform by using the agent pull method.but it is getting failed. I have followed the oracle document.
    I have raised an SR to suggest for a solution. i have given necessary log files to them, but they havent given any kind of response uptill now. its been more than a month.
    I have followed the below document
    http://docs.oracle.com/cd/E29505_01/install.1111/e22624/install_agent.htm

    Could you please post the “Oracle Cloud Control 12c Agent installation on windows 64bit” document if you have any.

    many Thanks,
    Harsha

    • harsha pydi said

      Also please check your email . thanks

    • Pavan DBA said

      http://gavinsoorma.com/2012/06/deploying-the-12c-management-agent-on-windows-using-the-12c-cloud-control-self-update-feature/

      • harsha pydi said

        Dear Pavan,

        SR Team has replied me back to apply the patch which has a default Agent installation problem with windows environment.
        1. I have applied the patch using opatch utility.
        2. “emctl resetTZ agent” has been executed successfully and asked me to run exec mgmt_target.set_agent_tzrgn script in sqlplus using sysman account .
        3. When i was trying to run the script using sysman schema, it throwed me this below error.

        SQL> exec mgmt_target.set_agent_tzrgn(‘hkhgc02mypdb01.aswatson.net:3872′,’Etc/GMT-8’)
        BEGIN mgmt_target.set_agent_tzrgn(‘hkhgc02mypdb01.aswatson.net:3872′,’Etc/GMT-8’); END;

        *
        ERROR at line 1:
        ORA-20233: Invalid agent name hkhgc02mypdb01.aswatson.net:3872
        ORA-06512: at “SYSMAN.MGMT_TARGET”, line 4585
        ORA-06512: at line 1

        Then i looked to trouble shoot by looking this Doc ID 388280.1 and followed it.

        4. I have skipped executing (exec mgmt_target.set_agent_tzrgn) part by following the doc ID 388280.1 and rann emctl start agent command.
        5. Before starting the Agent command , next step is i have managed to run emctl secure agent command and executed successfully.
        6. Now this time i have ran “emctl start Agent”… It was hanged for more than half an hr and the agent didnt started.

        7. For my checking i tried to execute like this below to make sure the Agent is able to connect with OMS…..

        C:\oracle\product\agentbase\agent_inst\bin>emctl verifykey
        Oracle Enterprise Manager Cloud Control 12c Release 2
        Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.

        —————————————————–
        verifykey failed: unable to connect to the agent at https://hkhgc02mypdb01.aswat
        son.net:3872/emd/main/ [hkhgc02mypdb01.aswatson.net]

        —————————————————–

        ANy suggestions on this please. Many Thanks

        Regards,
        Harsha

      • Pavan DBA said

        for windows I observed so many problems… check with oracle MOS docs only.

      • harsha pydi said

        Hi pavan,

        Please ignore my above comment..it has been resolved now. It is becoz of the JDK version the EMCTL was not getting up.. i have upgraded to latest JDK version and startedt the agent.,,its working fine now. Thanks a lot

  34. Vikas said

    Hi Pavan Sir,

    Can we restore oracle dump file in to mysql database OS is OEL 4.1

    • Pavan DBA said

      if u mean to say export dump, it is not possible. u need to use some tools to do that.

      • Vikas said

        Yes export dump, Ok thank you …..

  35. harsha pydi said

    Hi Pavan,

    I got the requirement from the developer team requesting the particular table with the particular partition date export from production server ..
    I am unable to find the exact syntax for expdp for table with jan and feb partitions.

    this is the scenario of their requirement.

    Dump and export table “ASW_AUDIT_TRAIL_2” , 2014 Jan & Feb partitions and China IT will backup them to GZ file server.

    Any suggestions please.. !!

    • harsha pydi said

      Hi Pavan,

      please have a look at this , if this is ok.?

      expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir TABLES=(T1:P1,T1:P2)

      Also check how many partitioned tables in DBA_TABS_PARTITION..?

    • Pavan DBA said

      already provided solution over mail

  36. prathap said

    [applebs2@usirvsdevebs06 ~]$ opatch lsinventory

    Oracle Interim Patch Installer version 1.0.0.0.64
    Copyright (c) 2011 Oracle Corporation. All Rights Reserved..

    Oracle recommends you to use the latest OPatch version
    and read the OPatch documentation available in the OPatch/docs
    directory for usage. For information about the latest OPatch and
    other support-related issues, refer to document ID 293369.1
    available on My Oracle Support (https://myoraclesupport.oracle.com)

    opatch lsinventory command hanged.No logs genrated /.patch_storage.Can you please suggest on this.

    regards,
    MPR

    • Pavan DBA said

      as it is mentioned you need to use latest opatch version. 6880880 – download this patch to upgrade you opatch version.
      see this link – https://community.oracle.com/thread/2365256?tstart=0

  37. prathap said

    hi

    can you please suggest on this.

    SQL> select open_mode from v$database;

    OPEN_MODE
    ———-
    MOUNTED

    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 3389864191 generated at 09/07/2011 06:40:16 needed for thread
    1
    ORA-00289: suggestion :
    /usr2/app/oracle/flash_recovery_area/1_68853_528537076.dbf
    ORA-00280: change 3389864191 for thread 1 is in sequence #68853

    Specify log: {=suggested | filename | AUTO | CANCEL}
    cancel
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: ‘/usr1/app/oracle/oradata/alpha/alpha_system01.dbf’

    ORA-01112: media recovery not started

    regards,
    prathap

    • Pavan DBA said

      already posted article in my blog for this. plz go through it

  38. Vijay Dodla said

    Dear Pavan,

    At first, my thanks for building, managing and maintaining such a wonderful website aimed at helping IT fraternity and especially Oracle DBAs around the globe.
    I just happen to stumble upon it this morning!

    So, I had a chance to glance through your post on 9th Jan 2014 regarding “Truncate Command not releasing anticipated space back to tablespace”.

    My view is that, in practical world, one would hardly allot any time to contemplate and assign specific value to INITIAL storage clause of a segment (be it table, index or any other).
    This is especially given the availability of LMTs and auto/system extent allocation policies whereby Oracle would decide the next extent size based on usage pattern/trend.

    So, if left to default, which I think is 64K, then overall loss/wastage will not be that huge in overall scheme of things. Of course, this depends on how many segments are to be dealt with!

    Regards,
    Vijay

    • Pavan DBA said

      Hi Vijay, ur understanding is correct. but i have seen env in which application team will define INITIAL clause knowingly/unknowingly to a higher value due to their own reasons. so from that point of view, i posted this article.

      also, thank you for your words about my blog.

  39. Hi Pavan,
    i would like to want real time experience and want to attejnd classes for dba and rac plz let me know when it is possible .

    • Pavan DBA said

      already replied to your earlier comment.

  40. hi i would like to learn oracle dba plz let me know wehen ur going to start new classes and to want real time experience

    • Pavan DBA said

      hi, new online batch of mine started last week. if u know sql, u can join from monday onwards. for all other details, contact kanna technologies at 040-40036087 / 8008500064 or training@kannatechnologies.com

  41. prathap said

    HI PAVAN SIR,

    DATABASE—11.2.0.3.0

    APPS————12.1.3

    OS———LINUX-X86-64

    Recently i got error in my PRODUCTION alert log file.I raised SR for this.

    The SR suggested (Please download the patch 14489591 and apply it in the client side) to apply 14489591.

    How to apply this patch in client side?

    HERE IS ERROR DEATAILS BELOW………

    Completed checkpoint up to RBA [0xd29e.2.10], SCN: 5998134606816
    Mon Apr 07 22:50:12 2014
    Completed checkpoint up to RBA [0xd29f.2.10], SCN: 5998134609987
    Mon Apr 07 22:51:39 2014
    Errors in file /u01/PROD/db/tech_st/11.2.0/admin/PROD_usirvsprdedb01/diag/rdbms/prod/PROD/trace/PROD_ora_31894.trc (incident=421201):
    ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
    ORA-03149: Invalid Oracle error code
    Incident details in: /u01/PROD/db/tech_st/11.2.0/admin/PROD_usirvsprdedb01/diag/rdbms/prod/PROD/incident/incdir_421201/PROD_ora_31894_i421201.trc
    Mon Apr 07 22:51:45 2014
    Dumping diagnostic data in directory=[cdmp_20140407225145], requested by (instance=1, osid=31894), summary=[incident=421201].
    Mon Apr 07 22:51:46 2014
    Sweep [inc][421201]: completed

    Regards,
    prathap

    • Pavan DBA said

      u need to apply this patch in the same way as u will be applying patches on database server. u need to identify first your application server and then go through the process. if any more help needed, plz chat with me at mymailbox.21@gmail.com

      • prathap said

        i have applied this patch by using Opatch.BUT my issue did not resloved.Any suggestion on this?

        regards,
        prathap

      • Pavan DBA said

        contact oracle support itself conveying the same.

      • prathap said

        Oracle support provides below solution that i have apply this patch in e-bussiness application side.

        It looks like you have applied the patch in database home. This patch is client side patch and is required to be installed in the middleware in machine usirvsprdebs01.hidglobal.com.
        Application of the patch in database home does not resolve the issue. You need to apply it in the machine usirvsprdebs01.hidglobal.com in the client your application uses. In case you have already applied the patch there also, please upload the relevant logs.

        regards,
        prathap

  42. chinmoy said

    Dear Pavan,

    Please read my explanation & correct me if is there any wrong steps.

    The details is given below,

    Sqlplus scott/tiger@mandb

    SQL>select * from emp;

    SQL>update emp set salary=10000 where empid=5;

    SQL>commit;

    1. sqlplus command is fired, the user process (client) access sqlnet litener.
    2. sqlnet listener confirms that database is open & creates a server process.
    3. server process allocates PGA into memory. Each server process has its
    Own PGA area, PGA contains data & control information of server process.
    PGA ( Private Sql area & Session Memory ).
    4. ‘Connected’ message returned to user.
    5. select * from emp;
    6. select statement is parsed into shared pool ( library cache & data dictionary
    cache) , this is called hard parse because the select statement is not already
    present in shared pool. An executable code is generated for select statement
    in shared pool. Server process got the all necessary information for select
    statement before fetch.
    7. server process takes the data from data file , loads into the buffer cache, puts
    into the most recently used(MRU) area & gives the data to the client via PGA.
    8. update emp set salary=10000 where empid=5;
    9. Before update statement , I want to say that empid=5 has a salary=8000
    10. update statement is parsed & executed in shared pool.
    11. server process checks( via PGA) buffer cache for data is available or not.
    12. In our case data is available in buffer cache.
    13. So data is updated into buffer cache with new salary i.e., 10000 & keep
    old copy i.e., 8000 into undo blocks for rollback.
    14. update is placed into redo log buffer.(
    15. row updated message returned to client via Server Process(via PGA).
    16. commit;
    17. server process sends a commit to redo log buffer , a SCN is assigned.
    18. LGWR process writes redo buffer contents to current online redo log
    file on disk & also write current log sequence no. in control files on disk.
    19. CKPT process updates the header of all control files with new SCN.
    It signals DBWR to write modified blocks (dirty blocks) from buffer
    cache to data file on disk.
    20. commit complete message returned to user.
    21. DBWR process writes modified blocks from buffer cache to data file
    on disk & CKPT updates header of the data file with new SCN.
    22. Undo data blocks associated with this transaction are released from
    Buffer cache.
    23. exit

  43. Hi,I am Asp.net Developer with 5 yrs exp.
    I want to learn Oracle and want a career in Oracle DBA.
    How to proceed.
    I knew basics of Oracle DBA.

    • Pavan DBA said

      if u want totally to shift to dba career, then u need to concentrate on adv topics like tuning, asm, cloning, upgrade, migration, dataguard, asm, rac etc both theory and practically also.

  44. Simli said

    Hiii,

    Why is that Very less databases exist on windows when compared to Unix flavors?

    Thanks in advance

    With Regards

    Simli

    • Pavan DBA said

      because unix flavor OS are more stable than windows.

  45. DB DB said

    Hello Sir ;

    Good morning 1

    In linux os , a user already created by #.
    in this case , how do i find users primary group & secondery group ?

    [root@oel5 ~]# id test
    uid=503(test) gid=504(share) groups=504(share),505(oragroup)

    Thanks in advance ..

    • Pavan DBA said

      504 is primary group and 505 is secondary group

      • DB DB said

        Thanks sir ;

        By looking the answer how do we assume –> whether it is primary or not ?

      • Pavan DBA said

        select database_role from v$database;

  46. DB DB said

    Hello Sir

    Good morning !

    I have a doubt in oracle architecture.

    ” Each user (process) having separate pga memory.”

    Consider 10 users are trying to connect to the database , PGA memory is ( 100 MB) ,

    1) In this case , Does oracle allocate 10 MB per user for every time ?

    2) Is it shared (memory) equally every time ?

    3) If any one user needs additional memory for his operation , what ‘ll oracle do ?

    Consider user is opening 5 sessions at a time ,,

    3) Does oracle allocate ( 10mb memory ) for all sessions ? –

    5) Does oracle allocate (10mb ) for each sessions ?

    Regards

    DB DB

    • Pavan DBA said

      first of all let me correct your statement.

      “each server process (not user process) will have separate pga memory”

      1. yes. if only 10 users are there, every time it do the same
      2. when you say shared memory, do you mean SGA?
      3. oracle will try to reduce any other memory component which is not in use and will allocate that to required memory area
      4. no
      5. yes

  47. raja said

    usually how many databases will be there in an organisation and what will be their size!!!

    • Pavan DBA said

      it varies between clients and project.

  48. Dinesh said

    Hello Sir ;

    I am trying to upgrade my database from 10.2.0.1.0 to 10.2.0.4.0 .
    Before upgrading my local database , i have issued following query..

    SQL > SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

    COMP_NAME VERSION STATUS
    Oracle Database Catalog Views 10.2.0.1.0 VALID
    Oracle Database Packages and Types 10.2.0.1.0 VALID

    Only above two components installed. Other components are not installed – Why so ?

    Eg : (Oracle Workspace Manager , JServer JAVA Virtual Machine, etc ..)

    I did manual database creation. Even i execute utlrp.sql script., no improvement.
    I do not have any invalid objects in my database.

    1) What was the reason for missing components ?

    2) How to resolve this problem ?

    • Pavan DBA said

      if we do create database manually, we will not have option of selecting which components should be there and should not be there. that is the reason you haven’t seen other components.

      if you want, you need to install those components individually by executing some scripts (.sql files) in $ORACLE_HOME/rdbms location. to know which files to execute, u can check google. once you done this you can upgrade your database.

      • Dinesh said

        Ya .. thanks sir. got it.

  49. archana said

    hi sir,
    dis is archana…in my test database ,i have created one table n issued select current_scn,scn_to_timestamp(current_scn) from v$database; it shows like ..

    CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
    ———– —————————————————————————
    209184 22-JAN-14 10.22.24.000000000 AM

    without doing any transaction ,after 17 mins i have queried the same,it results

    CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)
    ———– —————————————————————————
    209542 22-JAN-14 10.39.07.000000000 AM

    without any transaction how scn will increased? what exactly will happen in background regarding scn,,kindly expalin n share if u have any docs or links regarding SCN background functionality/

    • Pavan DBA said

      even though externally we don’t do any transactions, oracle will internally keep on updating dictionary tables. that is the reason why u will see scn is changing.

      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48052838748707

  50. Anand T said

    Hi Pavan,

    I am looking for a process to truncate records in AUD$ table
    Please Help me how to clean up older records(eg:older than Jan 1 2013) in AUD$ table .

    Thanks,
    Anand T

    • Anand T said

      Pavan,

      +++++++
      Topic related to dataguard standy Lag

      On PHYSICAL standy
      SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like ‘apply lag’

      NAME VALUE DATUM_TIME TIME_COMPUTED
      ——————————– ——————– —————————— —————————-
      apply lag +00 05:40:50 01/17/2014 11:11:54 01/17/2014 11:11:56

      In this case archives are applying Primary and standby are in sysnc but there is a lag(05:40:50)

      The requirement is to get an email when lag is more than 30m.

      Thanks,
      Anand.T

      • Pavan DBA said

        is that value (5:40:50) constant always? if not, we shouldn’t worry about that. also what mode is configured for standby?(max perf/max protec/max avail?)
        http://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_1103.htm

      • Anand T said

        Hi pavan ,

        sorry for the late response

        The value is keep changing and but we need to monitor the lag .
        stand by is max performance.

        Thanks,
        Anand T

      • Pavan DBA said

        anything you have identified from primary/standby alert log file?

    • Pavan DBA said

      just write delete statement as below
      SQL> delete aud$ where to_char(timestamp,’DD-MON-YYYY’)=’01-JAN-2013′;

  51. prathap said

    [oratest@pc77 ~]$ lsnrctl status

    LSNRCTL for Linux: Version 11.1.0.7.0 – Production on 17-JAN-2014 10:30:14

    Copyright (c) 1991, 2008, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    TNS-12532: TNS:invalid argument
    TNS-12560: TNS:protocol adapter error
    TNS-00502: Invalid argument
    Linux Error: 101: Network is unreachable

    oratest@pc77 bin]$ ./netca

    Oracle Net Services Configuration:
    Xlib: connection to “:0.0” refused by server
    Xlib: No protocol specified

    java.lang.NullPointerException
    at oracle.ewt.lwAWT.BufferedApplet.(Unknown Source)
    at oracle.net.ca.NetCA.(Unknown Source)
    at oracle.net.ca.NetCA.main(Unknown Source)
    Oracle Net Services configuration failed. The exit code is -1

    HI sir,

    unable start listener and unable to run netca……this software is installed in vmware for practice purpose…

    • Pavan DBA said

      hi prathap, u need to disable xhost using root user with below command
      # xhost +

      after that try running netca with oratest user. it will work. also check if network configuration is done properly in vmware.

  52. DB DB said

    Good Morning sir ,

    I have a question in data guard ( oracle 10g).

    Primary site : primedb

    Standby 1 : clonedb

    standby 2 : dummydb

    Consider i want to configure cascade standby ( clonedb , dummydb)
    Here , my requirements are ,
    primedb should not know dummydb details , dummydb should not know primedb details.

    1) Could you please show one example , how do i set db_file_name_convert & log_file_name_convert parameters in all init.ora files ?

    Thanks in advance ..

    • Pavan DBA said

      i didn’t got what u mean by cascade standby? can u plz explain more?

      • DB DB said

        What i get from oracle documentation regarding cascade standby
        ‘ Cascading standby database is a standby database that receives its redo logs from another standby database, not from the original primary database’

        Primary database is : primedb

        Cascade standby_1 : clonedb

        Cascade standby_2 : dummydb

        I want to ship redo from primary to standby_1 and standby _1 to standby_ 2.
        No direct connections between primary database to standby_ 2. and or standby_ 2 to primary database.

        Thanks sir.

      • Pavan DBA said

        ok. in such case, u need to set db_file_name_convert and log_file_name_convert parameters on standby_2 specifying standby_1 and standby_2 locations

  53. Seshu said

    Hi Pawan,

    I have gone through applying patches document it was relay very helpful thank you for sharing So here i have query In the real time how patch will be applied by using OPATCH or as you had documented the same way the patch will be applied i.e by running “run installer” program.

    And one more query how to uninstall the patch if needed.

    Regards,
    Seshu

    • Pavan DBA said

      hi seshu, there is difference between applying patch and patchset. the document which is there in my blog is for applying patchset n opatch is for applying patch

  54. archana s said

    in real time ,,is it mandatory to take backup of home,db backup,inventory,opatch while applying pacthset update

    • Pavan DBA said

      yes. it is very much required.

  55. DB DB said

    Good Morning Sir ;

    I am DB DB. I have a question about database performance.

    My production table having ‘n’ number of indexes.
    Suppose i want to rebuild anyone index due to increase database performance.

    1) How do i know , which index wants to be rebuild ?

    2) Which one impacts database performance really?

    Table level fragmentation and Index level fragmentation .

    Thanks sir.

    • Pavan DBA said

      1. you can use script mentioned in below link
      https://pavandba.com/2009/12/14/script-to-find-which-indexes-need-to-rebuild/
      2. if your query is using index and index is fragmented, then it will create more impact. otherwise, table fragmentation will impact more.

      • DB DB said

        Thanks sir.

    • Amar said

      Hi Pavan,

      Thanks for sharing ur experience with us.

      I have one requirement, for that need a dynamic script for to identify the which table and index fragmentation is required for Database/schema levels.

      i coudn’t find the script Google and metalink,

      Could you please provide me the script if you have?

      Thanks in Advance.

      Regards,
      Amar

      • Pavan DBA said

        script for to identify the which table and index fragmentation is required for Database/schema levels.

        i didn’t get u by above line. can u plz explain?

  56. kumar said

    i have a table it has an interval partitions . interval partitions was complete how can i add interval partitions on existing table ..
    let me explain about interval partitions please ..

  57. DB DB said

    Hello sir ;

    I am DB DB from chennai..
    When doing hot backup , why we need to specify until cancel option.

    Recover the database using backup controlfile option.
    ————————————————————————-

    SQL> recover database using backup controlfile until cancel;

    Thanks in advance ..

    • Pavan DBA said

      Hi, you are mixing up two things. what you asked is recovery (not hot backup). i believe u r asking about hot backup cloning. am i right?

      • DB DB said

        Yes sir , i am asking about hot backup cloning.
        If we are doing hot backup cloning on same server , don’t we need to issue following query ?
        SQL> recover database using backup controlfile until cancel;

        Thanks sir.

      • Pavan DBA said

        if we are doing on same server, we will recreate control file. in that case, we dont need this command to run. it is needed only when we clone to a different server

  58. Seshu said

    Hi Pawan,

    Could you please explain me in detail about ROLLING PATCHES, and also please explain me how to install the patches in detail.

    Regards,
    Seshu

    • Pavan DBA said

      Hi Seshu, patches can be applied in rolling fashion in rac databases. I hope u r asking about that only. it is nothing but applying patch in this way
      1.shutdown instance on one node
      2.apply the patch
      3.start the instance
      4.do the same for other nodes

      in this mode, we dnt need downtime to apply the patch, so its more effective for prod databases

  59. Rohit said

    Hi Pavan,
    we are planning to confiqure RMAN in our environment, i checked some command of RMAN, when we are taking the RMAN backup, whether it is archive file or database backup , we need to confiqure the channel for the same,
    do we need take the help from OS Admin for the same.

    example:
    1> confiqure channel ch1 device type disk

    2> confiqure channel ch1 device type sbt1;

    i didn’t understand which disk it will take .. or which sbt1 it will take ..

    or we need to do some prior steps before confiqure RMAN..

    please eleborate more about above two command what that means..

    your reply is really appreciated…

    Thanks and Regards

    Rohit

    • Pavan DBA said

      rman will allow you to take backup either to disk or tape. in your example, 1st cmd u showed will take the backup to disk (the default location is flash recovery area. as you haven’t mentioned your database version, i assumed it as 11g).

      your 2nd cmd will take the backup to tape drive.but for this you need to first have netbackup client installed on your database server and also configure backup policy. this will be done by netbackup team or storage team(generally we call by that name)

      moreover, you don’t need to configure channel separately while taking backup, instead you can configure your default device type in rman configuration parameters

      RMAN> show all;
      RMAN> configure default device type to disk; –> this is what it shows originally. if u want to take backup to disk, just continue with this.

      if u want to take into tape, do this change

      RMAN> configure default device type to sbt_tape; (or sbt1 etc)

      • Rohit said

        Hi Pavan,
        Great thanks its very clear now…. 🙂

        Thanks and Regards
        Rohit

  60. Rohit said

    Hi Pavan
    when we are doing re-org for large tables is there any way to know how much re-org has been completed for the table
    most of the time we are doing re-org of table which are having size of 20 to 50 GB .. my manager ask me to check how much % reorg has been done at particuler time.. as i m a Oracle DBA as well as doing the job of DB2 DBA i know the procedure for DB2 .. please let me know the any method for checking the how much % table has been re-org at particular time in oracle..

    Thanks and Regards
    Rohit

    • Pavan DBA said

      can you let me know what method you are using to do re-org of the tables?

      • Rohit said

        for SAP Database we are using BRTOOLS and for Non-SAP database we are using normal alter table tab_name move command;
        alter index ind_name rebuild online;

      • Pavan DBA said

        if your oracle database version is 10g or above, then better use shrink space command for table re-org (command you can get it from google)
        coming to your question, it is usually not possible to find out how much % of re-org completed, bcz oracle will display results only after its completion.

  61. Rohit said

    Hi Pavan,
    Greetings,

    i need to confiqure Dataguard on my two instance RAC environment , please let me know if you have any doc created for the same, also wanted to know the steps for switchover and failover on RAC environment..

    Thanks and Regards
    Rohit

    • Pavan DBA said

      I don’t have any docs, but u can find many in google (also videos in youtube). reg switchover nad failover activity in rac, it will be same as stand alone because you will be shutting down one instance during these activities.

  62. kumar said

    hi pavan

    i have one database around 900gb size .every day i was taken rman backup it was run very slow . it takes around 15-20hrs . how can i improve the backup speed ..?

    • Pavan DBA said

      there can be several ways like scheduling at different time, checking if any other jobs are running during that time in the database, allocating more no.of channels/using more parallelism value, implementing incremental backups etc

  63. dinesh said

    Sir , I am dinesh ( oracle enthusiast )

    I am learning Oracle Architecture . I have two questions.

    1) If i update a table 20 times , updated data will be recorded in data file and each transaction is recorded in redo log buffer. – IS this right ?
    2) If so , exactly what kind of other information is recorded in RLBC ( redo log buffer) ?
    3) Does oracle maintain separate SCN for each and every transactions ?

    A number that uniquely identifies a set of redo records in a redo log file. – This is LSN

    4) What’s is close relationship between LSN Vs SCN ?

    Thanks Sir.

    • Pavan DBA said

      1. updated data will be there in datafile and redo entries generated for each transaction(update in your case) will be there in redo log buffer
      2. redo entry will record information like at what time, in which table, by which user the table has updated etc kind of information
      3. yes
      4. no, your definition for LSN is wrong. LSN is a sequence number given to redolog file and when LGWR switches writing from one redolog to another, then this LSN number will get incremented
      5. there is no relationship between LSN and SCN

      • dinesh said

        Very thanks sir.

  64. rakesh kumar padey said

    on same server i need to do….any mannual by RMAN….I want to minimize downtime…..

    • Pavan DBA said

      if it is same server, u dnt need to use expdp/impdp. u can upgrade the database using upgrade procedure. for docs, check out google

      • rakesh kumar padey said

        wht about dictionary views of enterprise edition which z not used in standard edition ..after upgsradation of db .i wl b rmove or not?wht abt SYSDM USER WHICH Z not supported in standard edition..

      • Pavan DBA said

        you don’t require to worry about them. having them will not cause any harm to database

    • rakesh kumar padey said

      thanku sir…but 1 more question ….how much SGA size we can give ( i mn max SGA size it wll support)

      • Pavan DBA said

        it depends on how many transactions hit your database and other factors.

  65. rakesh kumar padey said

    hallo sir,

    please give me a favour..

    I need to upgrade my database from 10.2.0.1 enterprise edition to 11g statandard edition.
    Our database size is about 2-4 TB.
    I don’t want to use exp/expdp or imp/impdp.

    is there any other alternative then provide me mannuals

    Regards,
    Rakesh Kumar Pandey
    (pandey.oradba@gmail.com)

    • Pavan DBA said

      do you want to do in same server or different server?

  66. krishna said

    Hi pavan i am getting this error while iam running showall cmd in rman n my database version is mentioned below

    RMAN> showall;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found “identifier”: expecting one of: “allocate, amper, alter, advise, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, grant, host, import, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, repair, revoke, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate”
    RMAN-01008: the bad identifier was: showall
    RMAN-01007: at line 1 column 1 file: standard input

    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
    PL/SQL Release 11.1.0.7.0 – Production
    CORE 11.1.0.7.0 Production
    TNS for Linux: Version 11.1.0.7.0 – Production
    NLSRTL Version 11.1.0.7.0 – Production

    • Pavan DBA said

      there should be a space between show and all.
      RMAN> show all;

  67. deepthi said

    Hi Pavan,

    i Installed 12cR1 on my servers how can i upgrade to 12cR3?.

    please reply me .

    Thanks,
    Deepthi

    • Pavan DBA said

      Hi deepthi, 12cR3 is not yet in the market. 12cR1 is the latest one.

  68. kumar said

    how to restore and recover database from tape drive is it any chance to restore . plz give a suggestion on that concept .
    my data was back up directly to tape device .

    • Pavan DBA said

      you need to use channel allocation to tape. rest everything rman will take care

  69. kumar said

    Hi Could you please post a document on Cross-platform migration of databases in your blog

    • Pavan DBA said

      sure. I will post soon to my blog.

    • kumar said

      plz give the solution about this error

      ORA-00474: SMON process terminated with error
      PMON (ospid: 28013): terminating the instance due to error 474
      System state dump is made for local instance
      System State dumped to trace file /u01/app/diag/rdbms/epccb/EPCCB1/trace/EPCCB1_diag_28019.trc
      Thu Oct 17 10:50:46 2013
      ORA-1092 : opitsk aborting process
      Trace dumping is performing id=[cdmp_20131017105046]
      Instance terminated by PMON, pid = 28013

      • Pavan DBA said

        request to check the trace file for more information

  70. Naveed said

    Hello sir.,

    can you please explain me about the following questions,

    1.Can v do the job with export/import which we can do with sql*loader ?
    2.what z d diff btwn sql * loader and export/import ?

    thank you

    • Pavan DBA said

      1. yes we can
      2. sql * loader will help u to load data from a text kind of file. exp/imp will be doing data transfer between oracle databases (not from files)

  71. Naveed Mohammad said

    Hello sir

    I am naveed mohammad oracle dba fresher.One of interviewer asked me about /proc directory can you tell me about /proc directory and how it is useful for DBA

    Thank you

    • Pavan DBA said

      we don’t use it as dba. it is for OS admins to take care.

  72. dineshbabu said

    Hello Sir ;
    I am dinesh from chennai working in customer support service.
    You did great work (this blog is very useful for us)
    I have two questions
    1) what is I/o calls in oracle – ( little brief if possible)
    2) FULL TABLE SCAN can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls ”
    i dont understand above point. what it does actually ?

    Thanks sir;

    • Pavan DBA said

      whenever you run a select statement, oracle will search for data in memory first, if the data is not available in memory, it will scan the database and picks it from there. oracle picking up data from database is called I/O and it is a costlier operation means, it will take time. if table contains more rows then oracle need to do lot many I/O’s from database. if the table doesn’t have index, then it will do much more I/O’s and all this will make the response time to grow by which the result of the select statement will get delayed.

  73. Adolina said

    Hello,

    If Listener.log file destination is full what will happen? Whether connections through the listener will go hang? [DB version 10.2.0.4 & 11.2.0.3]

    • Pavan DBA said

      If listener is not required to write anything then still it will accept connections. otherwise, it will throw error when users trying to connect.

  74. Shekar Gupta said

    Hello,

    Hope you are doing well. I am having couple of queries regarding Patching.

    1)What happens if we do not apply these CPU patches?

    2)In case of global inventoy corrupted, is it possible to apply opatch without re-creating the global inventory by using opatch -no_inventory option?

    Thanks

    • Pavan DBA said

      1. these are security patches and regular functionality of database will not be impacted even if we dnt apply them. but if db’s like in banking sector etc thr culd be prob with security as these days hackers r smart. so it is wise to apply patches in tht kind of situation
      2. even though I have not used that -no_inventory option, u feel without global inventory got fixed, it might not be possible to apply patch. let me check that

  75. naresh kumar said

    sir,

    how to flush a single sql statement from a shared pool?

    Like i have seen from some of the blogs that after getting sql_id, we need to find the address, hash_value.

    To flush that it has some syntax
    >exec dbms_shared_pool.purge(“address”,hash_value”,”?”)

    but in the question mark(?) place, some “c” is written over their. may i know what does it represents

    thnx in advance

    • Pavan DBA said

      C stands for cursor. see this – http://psoug.org/reference/dbms_shared_pool.html

      • naresh kumar said

        thnks sir

  76. naresh kumar said

    Hi sir,

    If backup of archivelog files failed because of missing one of the archived log file in rman. how can v rectify the issue using cross check command. Sir, please explain with some explanation.

    thanks
    nareshkumar

    • Pavan DBA said

      RMAN> crosscheck archivelog all;
      RMAN> backup archivelog all;

      run above commands and it will be successful.

      • naresh kumar said

        Sir,
        if v use RMAN>crosscheck archivelog all;
        RMAN>resync catalog;
        Rman> delete expired archivelog all

        Is both of them results the same thing

      • Pavan DBA said

        yes

  77. Rohit said

    Hi Pavan,
    can u please let me know details steps for oracle 10g installation and database creation on windows system.
    Thanks and Regards
    Rohit

    • Pavan DBA said

      i have a document already under important docs page. plz check thr

  78. naresh kumar said

    Hi pavan,

    what type of questions we generally get in managerial /project round

    thanks
    naresh kumar

    • Pavan DBA said

      questions like
      1. why we need to hire you?
      2. what are your goals in next 5 yrs?
      3. how you will manage the team? etc

      • naresh kumar said

        thnks sir

      • Pavan DBA said

        welcome

  79. Adolina said

    Hello,

    1) Is it necessary to configure /etc/host file on the database server?

    2) Without listener.ora file, is it possible for oracle to know which port to listen when multiple listeners present?

    • Pavan DBA said

      Hi,

      1. it is not necessary if you are resolving the server by ip address. if you are using hostname, then you need it
      2. it is not possible. listener.ora is mandatory.

      • Adolina said

        Thanks for answering my questions.

      • Pavan DBA said

        u are welcome

  80. Naveed said

    Hi Pavan.

    Can you tell me the answer for this as am beginner for oracle dba

    6) In the early morning u came to your office then u login your username and password, when u execute a command sqlplus then it create an error. How u solve that problem?

    • Pavan DBA said

      it depends on what error we are facing. first thing we check is syntax or else the corresponding error.

  81. Anand said

    Hi Pavan,

    I meant to say is how to identify which database process is hogging cpu resource.

    As per your reply we can identify by joining V$process and V$session Right?

    Thank you pavan

    • Pavan DBA said

      yes exactly. we need to take pid from top command and then combine v$session and v$process using spid to get the details. unfortunately we need to do this in all db’s if we have more than one in a server.

      easiest way if to have EM grid control and check the issue.

  82. Kiran Gupta said

    Hi Pavan,

    I am currently working on a Query , the query is taking around 6 mins to execute.

    While investigating i found from the explain plan that there is a full table scan on one particular table( thrice) happening and so i created a composite index which later changed to index scan after which the elapsed time came down to 4 mins which was not upto expectations.

    Then i gathered schema stats and rebuilt the indexes and fortunately the elapsed time came down to around 25 to 35 seconds which was a good result.

    And one thing surprised me, the trace-output showed all the values as ‘ 0 ‘ , Enabled the trace and ran the query again for a couple of times and so but the output was same as below , Please can you help me & clarify !!!

    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    0 bytes sent via SQL*Net to client
    0 bytes received via SQL*Net from client
    0 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    56503 rows processed

    The real problem i have now is , when i check after few days (say 3-5 days) the elapsed time is around 3 to 3.5 mins again for the same query ?
    Can you plz help/suggest me a permanent solution.

    NOTE: I HAVE AROUND 20 QUERIES WHICH ACCESS THE SAME 5-6 TABLES WHICH TAKES MORE THAN 6 MINS AND NEEDS TO BE TUNED.

    Your response and help are appreciated.

    • Kiran Gupta said

      Adding to above the present trace output ::

      Statistics
      ———————————————————-
      615 recursive calls
      0 db block gets
      6073012 consistent gets
      22108 physical reads
      1316 redo size
      8044010 bytes sent via SQL*Net to client
      60782 bytes received via SQL*Net from client
      3768 SQL*Net roundtrips to/from client
      5 sorts (memory)
      0 sorts (disk)
      56505 rows processed

    • Pavan DBA said

      are there frequent DML’s operations being performed on those 5-6 tables? if so, you need to collect stats immediate after that. also, how is your stats job scheduled?

      • Kiran Gupta said

        Pavan ,

        I have manually collected the stats, but not sure if there is any schedule( newjoinee in this proj) , have to check.

        Yes there are around 20 diff queries accessing those tables, could you help me with the process (cmds) to check previous stats info and also with a script to schedule which collects the stats.

        BTW what does the trace output stats convey ( All ‘0’ values – check my prev post) & even the recent trace output??

        Thnx for the swift response.

      • Pavan DBA said

        you can get the prev stats info from dba_tab_stats_history. also, if you would like to know when the stats job executed in the past, you can check in dba_optstat_operations.

        I have already a script in my blog and if you want to use that, may be you need to customize according to your env.
        https://pavandba.com/2009/11/06/script-which-collects-statistics-in-9i10g-for-tables-modified-5/

        but if your db version is 10g or higer, then an automated job will be there already. you can check that info from
        SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = ’GATHER_STATS_JOB’;

        regarding trace file output, i am not sure why it is showing 0 for all and correct values in next trace. but surely 0 will not be correct.

  83. Anand said

    Hi Pavan

    I am getting confused,while using top command it shows multiple processes usage.Actually we have 3 databases in the same server

    how to find out which database process and user session hogging the cpu resources .

    Thanks in advance.

    • Pavan DBA said

      you need to get the process id (pid) and based on that you need to verify in every database about which session is causing the issue. if you have EM or EM grid control, better use that

      • Anand said

        Suppose if we have 10 databases in the same server ,so we need to verify every database right ?
        Is there any way to identify the database process usage using top command at os level.

      • Pavan DBA said

        Hi Anand, as far as i know, there is no way until we write some shell script to check in every database

      • Naveed Mohammad said

        Hi Pavan

        What do you mean by check every database ?

        On Wed, Jun 19, 2013 at 12:31 PM, Pavan DBA’s Blog wrote:

        > ** > Pavan DBA commented: “Hi Anand, as far as i know, there is no way until > we write some shell script to check in every database” >

      • Pavan DBA said

        as you said there are 10 databases, we don’t have option directly to get the database info. we need to connect to every database and check that

  84. Shekar Gupta said

    Hi Pavan,

    While upgrading why we run the gather stats for dictionay?

    • Pavan DBA said

      when we gather stats for dictionary, it will speed up the upgrade process and upgrade is nothing but modifying dictionary objects.
      see this
      http://docs.oracle.com/cd/E11882_01/server.112/e10819/statistics.htm

  85. Naveed Mohammad said

    hai Pawan

    can you explain me this,

    Suppose you have four data file in which two are having 500mb and two are 2 GB they are full, but u come to know that space is free in one data file of 2 GB. Then how will u can reclaim to that data file or table space?

    Thanking you

    • Pavan DBA said

      first u need to identify to what possible value u can resize that and then issue below command

      SQL> alter database datafile ‘path’ resize yourpossibleresizevalue;

      to get the possible resize value, use below script

      https://pavandba.com/tag/possible-resize-value-for-a-datafile/

  86. Shekar Gupta said

    Hi Pavan,

    LOCAL_LISTENER parameter needs to be set in parameter file for dynamic registration if the standard port 1521 is not used. Am i correct in my understanding?

    Does LOCAL_LISTENER parameter have any other functionality?

    • Pavan DBA said

      LOCAL_LISTENER parameter is used in case of dynamic registration whether listener is using 1521 or not. advantage of local_listener will be more in RAC instance rather than stand alone. see this

      http://anuj-singh.blogspot.co.uk/2011/05/how-to-set-locallistener.html

  87. Anand said

    Hi pavan

    In Rac gc cr multiblock requests event is a multi-block read
    this event can indicate an overloaded network connection between the RAC nodes, and general network issues because of the work processing the large-table full-table scan.
    How to tune this issue and how to make it index base scan.

    • Pavan DBA said

      identify that columns which are need to be indexed, create indexes (if not already there). then automatically your queries should do index scan. if still not working, check explain plan and you can also use index hints.

      • Anand said

        Actually In Rac memory is shared between the resource requested instances
        in these situation how can we made index scan in memory while sharing requested resource (cache fusion).

      • Pavan DBA said

        not complete memory is shared in RAC, instead only cache fusion part. Oracle will take care of this scan using current copy. for example, once one instance performs a query execution using index scan that current copy will be shared to other instances through cache fusion.

      • Anand said

        Thanks pavan

      • Anand said

        Hi pavan

        I have created asm diskgroup externel redundancy later i need to make it as normal redundancy

        Is it possible change redundancy level after creating a diskgroup ?

      • Pavan DBA said

        not possible. u need to drop and recreate diskgroup. see this

        https://forums.oracle.com/thread/668772

  88. Adolina said

    Hello,

    I am having one question regarding Listener.

    What is IP=FIRST in listener.ora file?

    • Pavan DBA said

      plz read these links
      https://forums.oracle.com/forums/thread.jspa?threadID=572397
      http://joordsblog.vandenoord.eu/2010/04/ipfirst-in-listenerora.html

      • Adolina said

        Hello,

        Thanks for your reply and thanks for the link you provided.

        I have 1 more question regarding IP=FIRST.

        IP=FIRST is also for single instance databases or only for RAC databases?

      • Pavan DBA said

        normally its for rac database

  89. Anand said

    Hi pavan

    In 2 node rac while adding datafile to tablespace if you forget to metion ‘+’then what will happen whether it is going to create or it throws an error if it creates where exactly located and other node users how to work on that tablespace .what all steps to perform that datafile is usefull for all node users

    • Pavan DBA said

      as far as i know, if we forgot to mention +, then it will be created in ORACLE_HOME/dbs location (I saw this once when my colleague doing). access from other nodes will be impacted because datafile is not in shared location i.e users can access it from the node on which datafile exists in dbs directory. the preferred way is to drop that datafile and create new one in correct location i.e ASM

      • Anand said

        Is there any way to make it that datafile to shared location without dropping
        one more doubt

        In Rac any node is evicted due to network failure then after we rebuild the network .Is there any steps to do manually to access the failure node after rebuilding the network or it will automatically available in cluster group which service is perform this aactivity.

      • Pavan DBA said

        if it is non-ASM, we can use relocating steps. but as it is ASM, we don’t have that option.
        reg node eviction, we don’t need to take any action if it is caused due to network failure.

  90. Sagar said

    Hey Pavan,

    Back with a Query again …..
    Could you please let me know what are the senarios where a “Select” Statment Generate Redo Logs….. I would be much appreciated if you could explain me in leman terms …… Because tried few Blogs… couldn’t catch up their standards……

    Regards

    Sagar kasani

    • Pavan DBA said

      check these links
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44798632736844
      http://vineetsoracleblogspot.blogspot.in/2011/08/does-select-statement-generates-redo.html

      • Sagar said

        Thank you verymuch Pavan For your quick reponse ….. That was a good blog explaing each terms….

  91. Shekar Gupta said

    Hi Pavan,

    Why do we need to run utlu112i.sql script?

    • Pavan DBA said

      that script will tell you whether your current version database is ready for upgrade to 11.2.0.1 or not. also it will give you the steps to complete any pre-requisites before we start with upgrade.

  92. Anand said

    Hi pavan

    when cloning the production database to test environment .how to change the database name
    and why we need to change the DBID in which scenarios we need to change the dbid

    • Pavan DBA said

      that depends on wht type of cloning you perform. if u do cold or hot cloning, then to change db name u need to re-create control file. but in rman cloning, oracle can handle it directly.

      I have not seen any situation when we change DBID. so almost 100% we won’t do that in real time.

      • Anand said

        Thanks for your support

        please suggest me some useful links regarding database migrations using datapump exppdp/impdp and also database refreshing .

      • Pavan DBA said

        http://docs.oracle.com/cd/E11882_01/server.112/e22490/toc.htm

      • Anand said

        Hi pavan
        user mail me i am unable to insert the data in my table . I came to know the user associated datafile was lost .

        how to flashback the database to that particular state or scn
        and how to get the scn of the particular loss of datafile

      • Pavan DBA said

        we cannot get the scn number now. possible way is to flashback database to approximate time of datafile lost. don’t you have a backup? if you have it, just restore the datafile and apply all archives and user data will be back !!!

  93. ABDUL WAJID said

    Hi Pavan its ABDUL WAJID here from bangalore i have completed BE engineering ( CS ) from bangalore in the year 2008
    due to rescission in that year so i have been shifted to telecom industry in 2010 i have been resigned to that. one of my friend is working
    for Mphasis for PUNE location he has teach me ORACLE DBA since he working from home now work from home has been taken off from him
    so as if in the market no job is prefered for oracle dba either u need to go for
    oracle RAC or oracle Apps so becoz i dont have exp at all.. in the dba field i have only taken training for single instance Oracle dba
    so i want to learn ORACLE RAC so i can get better job. and one more thing to be in frank no body will give me job with no exp becoz i am having 5 year gap so i am planning to use fake exp.. i have called to kenn tech so u r handling RAC classes from 20th onwards… i have been left the job in 2010 my financial condition also not good plz suggest me wt to do my no is 8147171512 r else mail me

    i am waiting for your response

    Thanking u in advance

    • Pavan DBA said

      yes. oracle RAC is mandatory for job when u go with exp. I am not the trainer who takes RAC training in kanna tech. I teach only DBA there. RAC is by other trainer. aftr learning rac or apps dba, u need to do job trails.

  94. Sabiha said

    Hii Sir,
    Im Bsc graduate in Computer science.
    Now i want to learn Oracle-Dba.
    So, will there be any job opportunities for me as a fresher after learning that course??
    Please, guide me to choose the best option.

    • Pavan DBA said

      Hi Sabiha,

      In today’s world, competition is more. so learning only oracle DBA will not get you job as fresher. So i recommend to learn any other course like oracle apps dba or oracle rac along with dba to increase your job oppourtunities.

  95. Sree Hari Dubakka said

    Hi DBA Guru,

    My name is Sree Hari .D working in MNC with total 4.3 yrs of experience in Oracle Apps Technical and recently I have been moved to Oracle Apps DBA role. So could you please let me know if I can get enough opportunities as am new to DBA. And also I recently took core DBA training and planning to take Apps DBA from your institute.

    At present I have 3 month of DBA exp…Please suggest and guide me.

    Best Wishes,
    Shri Haari.D

    • Pavan DBA said

      Hi Sreehari,

      First of all I am not DBA Guru… I am regular DBA with very minimal knowledge on concepts.

      Reg openings, yes there are lot of openings in APPS DBA field. For training, you can always contact the numbers provided in the training page.

  96. Anand said

    Hi pavan

    Is it possible to register multiple database version to catalog database and one more question

    Please suggest me how to analyze AWR,ADDM reports
    My query is running slowly its take long time to execute how to tune the issue

    and tell me 2 major performance issues in the database

    • Pavan DBA said

      yes. u can register. but it is not recommended as sometimes you may face trouble.
      reg analyze of reports, in my blog’s “important docs” section, i have already 3 docs listed. you can read them.

      for query running slow, check this link – https://forums.oracle.com/forums/thread.jspa?messageID=1812597

      for pref issues, you can refer to forums.oracle.com

      • Anand said

        Thanks Pavan for your valuable information.

        I have some issues like what are the reasons behind in ora600 error and when this error occurs is there any problem to database.

        How to verify corrupted block and how to restore corrupted block

        i have 200 datafiles existing and i added one more datafile . if the added data file is corrupted, by applying archive logs data will be recovered but the thing is the added data file is exits or else we need to create?

      • Pavan DBA said

        Hi Anand,

        there are lot many reasons why ora-600 will occur (for example any bug hitting database, any code writing error in sql etc). depends on the reason of ora-600, we can consider that as serious problem or not. but always it is better to search in ora-600 lookup tool first and then raise SR with oracle support for this error code. (even though it is serious or not)

        reg corrupted blocks, we have commands like dbv (DBVERIFY) or check corruption in RMAN to find out. If anything found, we have some steps to follow in order to recover that block. you can get those steps from many links in google.

        If the datafile is corrupted, we don’t have backup, then it is better to drop that datafile and then create new one and apply archivelogs.

  97. KUMAR said

    hi pavan

    unexpectedly i was drop one table in my one of the database . i don’t have any backup and my database in no archive log mode
    if any possible to recover

    • Pavan DBA said

      if u r using 10g database, it will be there in recyclebin. you can use below commands

      connect to user with which table dropped
      SQL> show recyclebin
      SQL> flashback table tablename to before drop;

      • KUMAR said

        Thank you pavan

        in case of using 11g is there is any possibility to recovery or restore

      • Pavan DBA said

        it is same as 10g. using recyclebin and flashback command

      • kumar said

        Hi pavan

        i was new to database so
        i have small doubting in my database . my database sys.aud$ tablespace take 1.3gb i want to drop that . in before that drop operation i will take backup using expdp it is possible ? if yes in which user(that means sys user or local user) i was export and import
        please tell me the commands for system user tablespaces

      • Pavan DBA said

        yes. you can take export using expdp with SYS user. but I am bit confused here. Do you want to take export of only AUD$ table or entire SYSTEM tablespace?

      • kumar said

        aud$ table only

      • Pavan DBA said

        expdp directory=directoryname dumpfile=dumpfilename logfile=logfilename tables=’AUD$’
        after entering above command, it will prompt for username, then give ‘/ as sysdba’

        this will take export of aud$ table and then you can truncate it (don’t drop the table)

      • kumar said

        thank you pavan

    • KUMAR said

      when i was get this error
      ORA-00600: internal error code, arguments: [kjucvl:!owner], [], [], [], [], [], [], []
      my data base was improper shutdown and start up again manually how can i rectify this error

      • Pavan DBA said

        please provide your database version and OS version. also, ora-600 is internal error and for this you need to search in my oracle support (this you can do only if you have license)

      • KUMAR said

        database version 11.1.0.6.0 64 bit
        Red Hat Enterprise Linux 5.2

      • Pavan DBA said

        urs is a bug and to fix that u need to upgrade to 11.2.0.1

      • shailendra m shinde said

        SQL>connect to /as sysdba
        SQL>startup mount
        SQL>alter database recover until cancel using backup control file;
        SQL>alter database recover continue default;
        SQL>alter database recover cancel;
        SQL>alter database open reset log

        And my database was alive .. In my case i havent lost any data but i do not guarantee that this method is data loss free (im just a rookie)
        This just brings my database back to life.

      • Pavan DBA said

        may I know for which concept u tried this? (I mean which incomplete recovery scenario) and also the db version?

    • KUMAR said

      how to release dml locks in oracle11g

      • Pavan DBA said

        we dnt have any control on applying/releasing locks. if lock is to be released, then either transaction shld be commit or rollback or we need to kill that session. killing a session is not recommended. so do either commit or rollback

    • KUMAR said

      how find how much memory allocate for single database in ram and how to find memory allocation plz provide steps for that

      • Pavan DBA said

        do u mean how much memory is allocated? (how much memory allocate and how to find memory allocation – these two confused me)

    • KUMAR said

      plz provide any materiel for oracle up gradation from 11.1.0.6 to 11.2.0.3 on Linux environment my database are on cluster environment and ocfs also . i was read your documents.

      • Pavan DBA said

        check this upgrade guide, it contains everything
        http://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm

    • KUMAR said

      plz provide resource allocation management
      concept script

    • KUMAR said

      hi pavan

      my alert log file show number of core dump file .how can i rectify the problem

      • Pavan DBA said

        surely alert log will have info on why those core dump files are getting created. jst for that error message. if not raise a SR with oracle support

    • kumar said

      how to Reclaim system tablespace . my system tablespace has 30 gb but it uses only 5gb i want to reclaim free space from system tablespace .

      • Pavan DBA said

        check out a post in my blog on how to resize datafile to lower value

    • kumar said

      hi pavan

      we have 8 databases . in one of the database was generating large size of archive logs how can i reduce the problem .

      • Pavan DBA said

        archive logs will generate when huge DML statements ran in your database. frankly this is not a unexpected behaviour, so nothing to avoid it. if you want to reduce, you need to tell the users to not run those statements. but we can’t do that. isn’t it?

        so we need to schedule backup of archives in frequent time like every 4 hours, 6 hours etc

    • Ganapathi said

      one of the database have take to lot of time for RMAN backup it will take around 15 to 20hr size of the database was approximately 900gb . how rectify this problem ..plz give the suggestion..it wil take backup of control file and spfile backup nearly 4hr i don’t understand that why it was take that much of time ..

      • Pavan DBA said

        hi, I believe some problem is there at storage level. check with storage team if they did some changes at SAN level. also u can trace the rman session when it is taking backup so that u can know where it is spending much time and why.

  98. Pinaki said

    Hello Pavan,

    Oracle recommends that once you apply PSU you continue to Apply PSU and not switch to CPU. This is mainly just for my interest. Can we apply CPU over PSU?

    Thanks and With regards

    Pinaki

    • Pavan DBA said

      not possible. if really required, we need to deinstall PSU and install CPU

  99. Sagar said

    How to find a whether Oracle Server Software or Oracle client software is installed on the machine. Is there any command we can execute to get this information.

    • Pavan DBA said

      select comp_name,status,version from dba_registry;

      the above will give more component names if it is a database software.

  100. Dear Pavan
    can u tell me how to add an instance in OEM?

    • Pavan DBA said

      I didn’t get you what you mean by adding an instance because OEM will work only for one db at a time. are you talking about grid control?

  101. Anand said

    Hi Pavan

    Myself anand i read your posts i learned so many things from your valuable posts

    Today my question is what is blackout and why we need to set blackout in oem
    i have read some docs still i am not getting clarity in that . could you explain it briefly so that i can get exact picture.

    Thanks & Regards
    Anand T

    • Pavan DBA said

      usually OEM is used to generate alerts for database like when db is down we shld get email etc. but when we are doing some maintenance like patching, we know that db will be down and at tht time we dnt want OEM to generate any alerts. for this we have a facility called blackout.

      if u keep blackout (usually it will be for a duration like 15min, 1 hr etc), then OEM will not generate any alerts.

      • Anand said

        Thanks very much pavan .

      • Pavan DBA said

        u r welcome

  102. Pinaki said

    Hello,

    I have few questions regarding Physical Standby Database.

    1) Does increasing LOG_ARCHIVE_MAX_PROCESS fasten up the apply process?

    2) Logs at standby db are being made by LGWR not by ARCH. Would it still help us increasing the number of log_archive_max_processes ?

    Thanks and with regards

    • Pavan DBA said

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

      1. no. log_archive_max_processes is only on primary side. this will make archiving faster. on standby side anyway oracle will span MRP process if required.
      2. nope. no effect.

  103. Anil kukadeja said

    I hv dne ocp frm ahemdabd. Ri8 nw i m searching d job frm last 1 year in gujarat nd gujrat is not relatd to IT.in gujarat u vl find al d things nd jobs except IT.wht shuld i do i m trying for dba on job portals for south region bt m nt getting responses.

    • Pavan DBA said

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

      it depends on how ur cv looks, r u trying as a fresher etc. plz email me ur cv to mymailbox.21@gmail.com

  104. hima said

    hi
    how much we increase the sga size maximum
    let ram size is 24g .right now sga size 10.
    what are the changes needed to increase the sga size?

    • Pavan DBA said

      Hi Hima, it depends on lot of factors on deciding whether to increase SGA size or not. Usually we do that if we observe performance issues with database.

  105. Pinaki said

    Hello,

    DB Version: 11.2.0.2
    OS version OEL5.4

    1) OS patching will be performed on a server. What kind of things i need to do to verify everything is ok with the database afterwards?

    Thanks and with regards

    • Pavan DBA said

      usually, if required we will shutdown the database, listener, enterprise manager etc for OS patching. So, once patching is done, we need to make sure all the services like listener, EM etc and database is up and running. Once users can be able to connect to database, then u can confirm that everything is fine.

  106. Murali said

    Dear Pavan,
    Do chrash course available for DBA RMAN,RAC( can course be completed in around 15days). I have some basic knowledge on rman, taking manual backups, Creating databases, schemas, tablespaces etc.

    • Pavan DBA said

      Hi Murali, we don’t have any crash courses available.

  107. Anand said

    Hi pavan

    I have a question could you tell how to solve this issue
    Oracle 10g database.One table is dropped one hour ago by mistake and you don’t have the backup and flash back feature is not enabled?How you will able to recover?

    • Pavan DBA said

      without backup it is not possible. if u have backup u can restore it and you can do until time recovery

      • Anand said

        Thank you pavan for giving the solution

  108. subhasish said

    yeah
    Thanx pavan.I will do that

  109. subhasish said

    Hi
    i am interested to go training on RAC +Apps dba from Kanna technologies>prior to that i thought of rebrushing oracle DBA course but duration (2 months) is too high for me as i have to try the job trial,so wanna finish it at the earliest.

    • Pavan DBA said

      Subhasish, the batches which I handle doesn’t have any fast track option. I believe there are fast track batches by other faculty and it is online. If you are interested, you can attend that. for more details, contact 040-40036087 / 8008500064

  110. naresh kumar said

    sir have you ever face a issue related to LISTENER?????i am facing a problem with my ebs server.LISTENER got crashed after i start aplplication.when i use only with database they work fine..plz help i

    • Pavan DBA said

      when you use EBS, there will be two listeners. one will be at application level and other will be at database level. which listener you are talking about?

      also, if it is database listener, please check listener log file and alert log file for more details.

  111. Shuv said

    Hii Pavan,

    I have gone through Oracle 11gR2 data guard concept and administration docs.
    As per the Oracle documentation, init file of the primary database is as follows:

    Example 3-1 Primary Database: Primary Role Initialization Parameters

    DB_NAME=chicago
    DB_UNIQUE_NAME=chicago
    LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’
    CONTROL_FILES=’/arch1/chicago/control1.ctl’, ‘/arch2/chicago/control2.ctl’
    LOG_ARCHIVE_DEST_1=
    ‘LOCATION=/arch1/chicago/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=chicago’
    LOG_ARCHIVE_DEST_2=
    ‘SERVICE=boston ASYNC
    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
    DB_UNIQUE_NAME=boston’
    LOG_ARCHIVE_DEST_STATE_1=ENABLE
    LOG_ARCHIVE_DEST_STATE_2=ENABLE
    REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
    LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
    LOG_ARCHIVE_MAX_PROCESSES=30

    My question is why the value is set as (ONLINE_LOGS,PRIMARY) for LOG_ARCHIVE_DEST_2?

    • Pavan DBA said

      it means LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destination boston. when standby becomes primary, then it uses redolog files as online, so we are mentioning ONLINE_LOGFILES

      • Shuv said

        Hii Pavan,

        Thanks for your reply.

        I have one more question regarding this.

        What will happen if the value is set as (STANDBY_LOGIFLE,STANDBY_ROLE)for LOG_ARCHIVE_DEST_2?

      • Pavan DBA said

        nothing. you cannot perform switchover. thats it

      • Shuv said

        Hii Pavan,

        Thank you for your response and insight.

        When Standby DB becomes primary, we need ORL instead of SRL.

        if i set VALID_FOR=(standby_logfile,standby_role) for log_archive_dest_2 then the standby redo logfiles will be filled and archived instead of online redo log files on standby. So cannot perform switchover. Am i correct in my understanding?

      • Pavan DBA said

        yes

      • Shuv said

        Thanks

  112. naresh kumar said

    Hi sir,

    In an interview, they asked me ” what vil do to end to end instance of a delivery bill” and ” if you cant resolve the ticket what vil you do”. Sir, Please answer questions .

    thanks
    Naresh kumar

    • Pavan DBA said

      if we can’t resolve the ticket, we will pass that to any other team who is responsible for solving it. if it requires any third party attention, then we will keep ticket in pending state

      • naresh kumar said

        if it is related to production database ?. vil raising an SR can solve the issue?

      • Pavan DBA said

        yes. if the issue is related to database and DBA couldn’t able to solve it, then we can take that to oracle support. in that case also we will place ticket in pending state

    • Naresh kumar said

      Sir, what about the first question
      “what vil you for end to end instance of delivery bill?(i thnk it has some grammatical mistakes)

      Thanks
      Naresh Kumar

      • Pavan DBA said

        I am not quite sure about this because it doesn’t involve DBA terminology

  113. KUMAR said

    hi pavan sir
    i was new to rac database . we are using 2node RAC . my problem was database node balancing is not good that means example one node contains 100 sessions another node contain only 5 sessions like that plz give any idea about load balancing

    • Pavan DBA said

      this may be because tnsnames.ora file at application side may contain only one node information. you need to add another line to that tns entry and input and a parameter called load_balance=on. for a sample rac tns entry, refer google.

      • KUMAR said

        my tnsnames.ora file have contain that load_balance=on
        but there is no switching,when ever i was restart my database noad switching is well. after 2days from database restart same problem was occurs, this will occurs in single database only

      • Pavan DBA said

        do you have load balancer configured at networking level? (you can find out this with your network admin). just having load_balance=on is not enough. you need to have that load balancer configured, then only connections will be distributed.

  114. namarta said

    Hi pawan sir I have done MCA in 2010 and oca certification at oracle 9i .due to some reason i had to take break .in 2012 i join niit for oracle 10g right now i wants job in oracle field what i have to do for getting entry level job which will lead me as a racle dba in future

    • Pavan DBA said

      Hi Namrata, frankly now a days to get fresher dba job only DBA knowledge is not enough. so I recommend you to do either oracle apps dba course or oracle RAC and also as oppourtunities will be less, you need to keep on trying for job. in DBA also, you need to have good hands on for advanced topics like dataguard, rman cloning, upgrade etc

  115. Shuv said

    Hii Pavan,

    I have couple of basic questions regarding Standby Database.

    According to the Oracle docs:

    “It is highly recommended that you have one more standby redo log group than you have online redo log groups as the primary database. The files must be the same size or larger than the primary database’s online redo logs. ”

    My questions are:

    1) Why do we need one additional standby redo log group than in primary database?

    Is it because to avoid LGWR waiting on the primary while redo is applied?

    2)What type of problem will i encounter if the SRL size is less than the online redo log file size?

    With Regards

    • Pavan DBA said

      1. In case of 3 online redo log groups, it is recommended to use 4 standby redo log group this is in case if log switching is happening frequently on primary and all 3 standby redo logs are still not completely archived on the standby and 4th can be used here as there will be some delay on standby due to network or slowness of arch on standby.

      2. you may observe performance impact when you make that standby as primary in future

  116. Rohit said

    Hi Pavan,
    can u plz tell me how to find the number of row updated in current running sql as the query is running since long time..
    i googled and found one pl/sql code… but i m not sure it will work..as i its production db.. i m not able to take risk.. can u plz tell me how to find it..

    • Pavan DBA said

      already if the statement is fired, then we cannot be able to know that. otherwise we need to depend on that pl/sql code only. there is no direct way in oracle for that. if u enable auditing on the database, then it is possible.

      • On Tue, Mar 5, 2013 at 8:36 PM, Pavan DBA’s Blog wrote:

        > ** > Pavan DBA commented: “already if the statement is fired, then we cannot > be able to know that. otherwise we need to depend on that pl/sql code only. > there is no direct way in oracle for that. if u enable auditing on the > database, then it is possible.” >

    • sir i have knowledge in coredba …i want to join RAC …tell me wch one is good in RAC OR APPSDBA…….WCH IS BEST INSTUTUE IN HYDERABAD

      • Pavan DBA said

        i recommend to join apps dba (ofcourse if you can do RAC also, then apps dba+RAC is hot in the market). for institute you can contact kanna technologies for apps dba and RAC or sri sai technologies for RAC

  117. hi pavan sir
    How to skip next column and before the column will be deleted ,so please tell me querry in oracle…please send to my mail : kondareddy509@gmail.com

    • Pavan DBA said

      request you to please explain more on what exactly is ur requirement.

      • hi sir…………………..
        actully i have some columns like 123 abc xyz …….>first column
        123 abc pqr….> 2 nd column …etc ,here so many columns ya dis releted. we search only pqr status and we skip from abc to pqr.so how to skip before column from one column to another column….

      • Pavan DBA said

        that depends on how you write query. in your select query use where condition which select only pqr column

  118. Sriniv@s.. said

    Hi pavan sir,

    i have one doubt about ORACLE NETWORKING.i.e suppose i have 3 database in my own pc,can i connect internal databases with out using listener.?

    • Pavan DBA said

      yes. to connect to databases within same server, u dnt need listener. it is required only when u try to connect from remote machine

  119. Shuv said

    Hi Pavan,

    1)What’s the surest way to check whether a database has been cloned successfully?

    Checking RMAN logs is an option. Any other ways to check?

    2)I would like to know whether static entry in the listener.ora file required for the cloned database? DB Version 10.2.0.4

    Thanks in advance

    • Pavan DBA said

      1. we can check the object count between the target database and auxiliary to confirm cloning success.
      2. sometimes, i have seen rman cloning become issue without static entry. so i prefer to use static entry than extproc

  120. rohit said

    hi pavan ..its very simple and nice blog i have seen yet.. i really appreciate ur work..
    i am working as oracle DBA at syntel.
    i have one problem.. that most of the time we get the ticket on high cpu memory.. will u plz clear me out regarding what to do for that as oracle dba… i m using top command but unable to understand what happening..
    waiting for response..

    Thanks and Regards
    Rohit

    • Pavan DBA said

      u can take awr report and check why high cpu is causing, i mean which wait event is triggering this. usually, a bad sql query will cause damage to high cpu

      • Rohit said

        Thanx for valuable reply….

  121. MV Bhaskar said

    Hi Pavan,
    This is Bhaskar. I am a BSC graduate of 1990 bach worked with an INHOUSE team of dealership company as Oracle Developer for 11+ years. And I am noware working now. Can I choose my career as DBA, and try for software jobs? Kindly assist. Will my age and Company will be a problem?

    • MV Bhaskar said

      Sorry 1995 batch and aged 38 years

    • Pavan DBA said

      Hi Bhaskar, I am not sure and cannot advice accurately on this. But, surely software industry won’t consider age and all. You can learn Oracle DBA and try for jobs.

  122. Himanshu said

    Hi Pavan sir,

    I will have new machine with oracle 11g and i have exported dump from oracle 10g (10.2.0.3.0)
    Now i need to import that dump on oracle 11g.

    Is there anything that needs to taken care in this ?

    Thanks,
    Himanshu

    • Pavan DBA said

      it depends on which dump it is. i mean is it full export dump or schema only etc. if it is full, then u need to create same tablespaces(with same size) & users

      • Himanshu said

        Thank you
        I have dump for schema only.

      • Pavan DBA said

        then you have to create that user first on 11g database and then do import

  123. somnath said

    Hiiii pavan,

    I hope you are doing fine. I am somnath from kolkata. I’m new at administrating a database (10gr2 on RHEL). I need your help regarding few questions.

    1)Should I create a smallfile tablespace with one datafile or should i create a smallfile tablespace with several datafiles? Which one is recommended?

    2)Whether datafile should be set as AUTOEXTEND=ON or AUTOEXTEND=OFF? What is advisable?

    3) Is there any relationship between AUTOEXTEND & MAXSIZE?

    Thanks & regards

    Somnath

    • Pavan DBA said

      1. if u r using multiple hard disks in ur server, then multiple datafiles is recommended. otherwise 1 datafile is enough
      2. it depends on how much data that is coming in and whtr in ur company u have 24*7 support or not. i recommend to enable it with maxsize option
      3. yes, whenever u r using autoextend, u need to use maxsize also. (give any value for maxsize. for eg, if ur datafile size is 10G, give maxsize as 20G)

  124. janardhan said

    Hi pavan sir,

    please provide me latest dumps of oracle 11g OCA & OCP

    i wish u Happy sankranti to you and your family members

    have a nice day

    Thanks
    Janardhan

    • janardhan said

      Thanks sir

      • Janardhan said

        Hi pavan sir

        Thanks for dumps

        i clear 11G OCA certification now..

        please provide me gunshot interview question RAC & HA

        Have a nice day

        Thanks
        Janardhan

      • Pavan DBA said

        hi, i don’t have interview q’s in specific

  125. shuv said

    Hiii pavan,

    It is recommended that the SPFILE is shared between all nodes within the cluster, but it is possible that each instance can have its own SPFILE. I would like to know why it is recommended? Is it because shared SPFILE simplifies administration or anything else?

    Thanks in advance

    • Pavan DBA said

      yes. we can have individual spfile for each node. common spfile is preferred because whenever you do changes it will be effected for all instances at a time. if u maintain individual spfile and suppose you changed one parameter in one node and forgot in another, that may lead to some issue. to avoid this, we will maintain shared spfile.

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

  126. Madhava Pai K said

    Dear Sir,

    I am searching for a job in .net field .I have 2.3 year of experience .Currently i am working on classic asp and .net.
    I have only 0.5-0.6 year of experience in .net.
    I want to change my company.Please tell me if there is any vacancy.
    may i know your email id so that i can send my Resume.
    my mail id is kmadhav.pai555@yahoo.in

    Thank You.

    • Pavan DBA said

      hi, any vacancy info i will be posting in my blog itself. you can keep watching this regularly

  127. vamsi said

    Hi Pavan Sir,
    Please refer me if you any openings in cognizant for oracle apps dba(11i/R12).

    regards
    Vams

    • Pavan DBA said

      any requirements, i will post in my blog itself

  128. Ramesh said

    Hi Pavan,

    Great Sharing & Teaching, Keep it continued….

    Am an Oracle DBA with 7 Years and total 10+ IT exp, Looking for the next level . . .

    What should I do next? Your Inputs . . .

    Regards,
    Ramesh

    • Pavan DBA said

      Hi Ramesh, thank you for comments.
      depends on your interest of going to management side or remain in technical side, I can give my inputs better. Please let me know which one you are thinking of…

      • Ramesh said

        Thanks pavan for your prompt response . . .

        I would like to stick on to Technical side . . . Please advice.

        Thanks once again. 🙂

        – Ramesh

      • Pavan DBA said

        on technical side, you can choose to be in a role of service manager(service delivery manager). This will be very good in order to grow technically

      • Ramesh said

        Thanks Pavan. Please let me know how to scale up ?

        How should I prepare for that?

        Ramesh

      • Pavan DBA said

        as far as i know a service delivery manager should be able to have knowledge on all tecnicals concepts like unix administration, DB administration, network etc. I am not saying in depth, but a brief idea. With this knowledge you can promote yourself in current company for the next level as SDM.

  129. ashok said

    hi pavan,
    what was the rman internal mechanism

  130. kishore said

    Hi,
    Please provide the better answer for below interview question.
    Question: What is the most difficult task you faced so far (like in recovery scenario’s)

    • Pavan DBA said

      you can go to forums.oracle.com and there u will find lot of scenarios

  131. VASUDEVAN said

    Whata is stats pack?What is flashback?What is pt?how to do pt?What is dataguard how to apply it?what is patching?how to apply patch?

    • Pavan DBA said

      for all your questions, one place to get answers is oracle documentation or google. some of them are also there is my blog

  132. VASUDEVAN said

    what is the difference between Oralce Parallel server and RAC

    • Pavan DBA said

      till 8i, we used to call it as oracle parallel server and from 9i onwards name was changed to RAC

  133. sai kiran said

    hi pavan,
    Now a days in interview they are stressing on up-gradation and migration (from 10g to 11g) so can you share the documents of up-gradation and migration and some points on this topic so that it will be very helpful to us

    Thanks in Advance

    • Pavan DBA said

      i am going to upload them soon

      • sai kiran said

        thanks pavan and also discuss some errors on core dba and rac dba topics

  134. amar said

    hi this is amar i want to learn oracle dba through online could you pls tell me which institute is better in Hyd..

    • Pavan DBA said

      hi amar, if you wish you can join kanna technologies. i am providing training there. details are in my blog

      • amar said

        actually i was contact by Kanna tech….but they are saying it takes 2more weeks to start the course, is there any other institutes giving online course

      • Pavan DBA said

        i am not sure who can give online classes

  135. kishore said

    hi,
    if database restart what happened to scheduler job. is it automatically start’s or we need to start again.

    • Pavan DBA said

      if the job is running during which database got restarted? then it will not start automatically. it will run for next time

  136. Prashant said

    hello sir… i am a 2012 passout graduate(B.Tech CSE) and completed OCA certification in PL/SQL. Besides i am also have good knowledge of ORACLE DBA (10g) concepts like user mgt., achitechture,backup and recovery,ASM,data guard and performance monitoring and tuning also with basics.
    Now i am worried for getting the answer of ” How to start my career in database ??”. as some are suggesting me to join RAC and without it there is no job for fresher DBA.
    So please guide me sir…. i am also having good acdmics %(75+) till graduation. but not having any vision that how to start…..please help me sir…..

    • Pavan DBA said

      yes. it would be better to acquire RAC knowledge also so that oppoutunities will be more

      • Prashant said

        Without RAC where should i approach for searching job only with DBA skills….???

      • Pavan DBA said

        yes you can. but it depends on how much exp u hav

  137. apoorv said

    hi i wnt know about the best learning institute in hyderabad for oracle dba, also the institute which provide placements……..,as well as certification…………., plz tell which one is better oracle 11g or oracle 10 g

    • Pavan DBA said

      hope we discussed this in chat and you are clear

    • takecare said

      I recommend you and list you the institutes in priority wise from top to bottom,
      Wilshire (madhu sir batch)
      wilshire if any other faculty
      hashzone
      srisai institute
      db expert
      4bs
      unirac solutions (with placement)
      uclid IT School
      dba technologies
      kernel it solutions
      kanna technlogy (pavan dba)
      kyathi technology
      sunmarss institute
      peers technology.

      Caution: I recommend you to join in any of the above mentioned top 8 institutes..If you join in any of listed after 8, there is no problem just you ultimately realize and you are going to join for course once again.

      ***Do you know already these institutes aware of this situation, they will not bother if you come 100 times for same course by paying only one time, why because these institutes almost out of students.

      Take the rite decision by considering the above

      • nandu said

        wilshire is always preferable

      • Dheeraj said

        hi this dheeraj, i completed my course in hashzone..hashzone is one of the best institute in ameerpet..may be after wilshire institue. i dont know about other institutes and also kanna inst.. my friend joined in unirac, institute was not good ..bt they will provide placement

      • sandy said

        How is the orafact institute, as because they are the partner of the oracle and saying for placement also.

      • Pavan DBA said

        as far as i heard or experienced from my trained candidates, orafact is not a good choice. so many of my trained candidates first joined in orafact, completed their course and then re-joined to my batch

  138. Sivaprasath.D said

    Can u share me with important wait events in oracle performance tuning… i browsed ur blog and unable to find info abt that… so can u help me with this topic…

    • Pavan DBA said

      i have some docs in “important docs” page with name “analyzing awr report 1,2,3”. you can go thru them

  139. Sivaprasath.D said

    Hi Pavan,

    Today i found a interesting question about System tablespace and i could’n guess what may be the reason.. i believe u can help me with this.. the question is ..

    ” If there is so much place in system tablespace and it is not used
    it throws an error why?”

    • Pavan DBA said

      first of all question is not that much clear because the answer would be based on what error it is throwing. i assume the error is about space issue and this can happen due to fragmentation in the tablespace. but usually we won’t find this issue with SYSTEM tablespace.

      • Sivaprasath.D said

        thnx for d answer…:-)

  140. kishore said

    Hi,

    I am trying to migrate oracle db form windows to linux,in windows 7 i checked that process is 64-bit but in db level i checked in platform_name column it’s showing 32-bit
    please explain what exact diff for os level and db level version and why it is showing different from os

    • Pavan DBA said

      can you post what query you have used? also what is your oracle version?

  141. kishorecv said

    Hi,
    please let me know the steps to imp 11g to 9i database

    Thanks&Regard’s
    kishorecv

    • Pavan DBA said

      Hi Kishore, it is not possible to import 11g database into 9i version.

      • Keerthi said

        Shall i know is there any other possible ways ??

      • Pavan DBA said

        sorry… i didn’t get you. what you mean by other ways?

  142. ashish said

    hi can u tell me what is differenece between normal restore point and guarantee restore point ……..please explain with example

  143. Dinesh said

    Hi Pavan

    Due to archive destination got full,Moved some archives to another location,when i am running the rman backup it is not able to find the status of the files which are moved,so run the crosscheck command from rman promt.now the backup is success and deleted the archives.
    Now moved that files to original location but the issue is the status of these files is marked as expired in catalog,
    1) Is there any way to mark them
    as available in catalog.

    2)could you please explain the exact steps suitable to this scenario

    • Pavan DBA said

      RMAN> change archivelog all validate;

      try this command by connecting to both target and catalog and let me know if it works

      • Dinesh said

        it’s not working.we need to feed the the expired archives to the catalog once we move it to the original location,but couldn’t able to register them to the catalog.

        looking forward to here from you!!!

      • Pavan DBA said

        can you let me know what is the error you got when you ran above command?

      • shashi said

        hi pavan,
        i have around 80-100 databased i want to monitor the alert log file for errors.do u have any scripts or tool to monitor d erros which displays the time n date along with error
        thanx in advance…
        shashi

      • Pavan DBA said

        hope we discussed this in chat today morning

  144. Gaurav said

    Hi Sir,

    while running . oraenv ….i am getting following error ..Pls suggest..

    oracle@xxxxx:/usr/local/bin>. oraenv
    ksh: @: 0403-041 Parameter not set.

    • Pavan DBA said

      which OS you are using and its version plz?

  145. Rashmi said

    Hi pavan ,

    Can you please provide me help in creating partition on existing tables in the database .any document or link. its urgent .
    Appreciate your help.
    Thanks.

  146. prathap said

    Hi sir,

    1.one of my developer given package and asked me create.when i created it shown error below error.

    LINE/COL ERROR
    ——– ————————————————–
    906/10 PL/SQL: ORA-00942: table or view does not exist
    906/10 PL/SQL: SQL Statement ignored
    909/10 PL/SQL: ORA-00942: table or view does not exist
    909/10 PL/SQL: SQL Statement ignored
    960/3 PL/SQL: ORA-00942: table or view does not exist
    960/3 PL/SQL: SQL Statement ignored
    961/11 PL/SQL: ORA-00942: table or view does not exist
    961/11 PL/SQL: SQL Statement ignored

    then i sent to developer it is showing error.

    2.then the developer told me to create dblink and create.

    3.how to create db link?wat is the syntax?

    regards,
    Prathap

  147. Rashmi said

    Hi Pavan,

    I am facing ” ora-12547 tns lost contact while connecting through toad ” on client machine, all my listeners are ready on the server , even proper entry is made in tnsnames.ora at client end.This is my tns entry ,

    HAWKEYE=
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.3.33)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCL)
    )
    )

    And listener.ora on server,

    LISTENER1 =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = tcp)(HOST = 10.33.3.33)(PORT = 1521))
    (ADDRESS = (PROTOCOL = ipc)(KEY = EXTPROC1521))
    )
    )
    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME= ORCL)
    (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1)
    )
    )

    i am troubleshooting this for past 2 days but all in vain , i am able to connect to other servers through toad succesfully but only this server is causing the problem.
    even tnsping fails ,

    C:\>tnsping 10.33.3.33

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 27-JUN-2
    012 18:07:56

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:
    C:\app\Rashmi_Pardeshi\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

    Used HOSTNAME adapter to resolve the alias
    Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTO
    COL=TCP)(HOST=10.33.3.33)(PORT=1521)))
    TNS-12537: TNS:connection closed

    Plzz help..
    Thanks

    • Pavan DBA said

      in tnsping you should use alias name, not ipaddress.

      what is the result when you issue “tnsping hawkeye”? plz post me

      • Rashmi said

        C:\Users\rashmi>tnsping HAWKEYE

        TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 – Production on 02-JUL-2
        012 14:13:01

        Copyright (c) 1997, 2010, Oracle. All rights reserved.

        Used parameter files:
        C:\app\Rashmi_Pardeshi\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

        Used TNSNAMES adapter to resolve the alias
        Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.3.
        33)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
        TNS-12537: TNS:connection closed

      • Pavan DBA said

        i believe there is some issue with firewall also. so check from that side too or ask your network admin to do that

        see this link also – http://www.dba-oracle.com/t_ora_12537_tns_error.htm

  148. vipin garg said

    Hi Sir,

    I want to know that why Transporting Database is faster than DataPump

    • Pavan DBA said

      because datapump requires lot of pre and post tasks to be done. but in real time we will not use transportable tablespace

  149. manoj pathak said

    hi..
    this is manoj pathak
    if i complet graduation,oracle dba certification.
    ca i got job as DBA… is there P.G copulsary…s or no…. .rply i’m wating

    • Pavan DBA said

      yes you can get job on graduation itself….

  150. ashok said

    Hi Pavan garu
    what is the difference between static listener and dynamic listener.

    • Pavan DBA said

      static listener – which listens to only one database
      dynamic listener – which listens to all the databases on the server

  151. nasir said

    Hi pavan,
    its very nice useful blog for your every dba.. as ur answer is very simple …. and easy to understand..
    i have one question that when we are upgrading DB from 10g to 11g is it necessary to apply patch to DB before.. and if it is necessary then why ? i m kindly waiting for ur reply
    thanks
    Nasir

    • Pavan DBA said

      Hi Nasir, as you have not mentioned the 4 digit version of db, i am assuming you are asking about 10.2.0.1 to 11.2.0.1.

      we cannot upgrade 10.2.0.1 directly to 11.2.0.1. first we need to upgrade db to 10.2.0.4 and then to 11.2.0.1.
      to move db to 10.2.0.4, we need to apply patchset.

      this is how oracle defined their upgrade method

      • nasir said

        Thanks Pavan..

  152. Rashmi said

    Hi Pavan,

    I want to implement backup & recovery at my client end.I have thought of full db backup(cold bkup) every 15 days & incremental level 1 bkup everyday.Can my cold backp act as an incremental level0 backup to restore from incremental bkups.If not how do i figure it out can you plz help because client is more biased towards cold backup.need ur help.Thanks

    • Pavan DBA said

      Hi Rashmi, cold backup cannot be act as level 0 for incremental backup. so that option is ruled out.

      you need to take level 0 incremental backup which support level 1 backup later. Tell to your client the benefits of hot backup with RMAN. some clients will be thinking that cold backup is good and hot backup is not. in such situations, as a DBA, we need to explain them the benefits and make them understand that hot and cold backup are one and same especially in rman.

  153. upendar said

    hi sir,
    how i can convert my standby database to primary when my primary is crashed..plz explain me with example..

    thanks in advance…

    • Pavan DBA said

      Hi Upendar

      we need to use below commands to do failover (run these commands on standby to make it primary)

      select database_role from v$database;
      recover managed standby database finish;
      alter database commit to switchover to primary;
      alter database open;
      select database_role from v$database;

  154. upendar said

    Hi sir,
    what is current_user and global user in database links concept? could you please explain me with an examples?

    thanks in advance…

  155. 9500057638 said

    Hi Pavan,

    Actually am working as a PL/SQL Developer. But am very much interested to work as a DBA. Even i have completed Admin 1 and 2 Oracle 10 G. But i did my OCA on development track. Right now am searching for a DBA position. Can you guide me in preparing for this.
    I have basic knowledge on all DBA concepts. But dont have any handson.
    Am just requesting you to give some suggestions, like what can i put in my resume with this knowledge and am having 3 years of experinces in development.

    Thanks,
    Sunil kmsunil872yahoo.co.in
    +91 9500057638

    • Pavan DBA said

      Hi Sunil, If you have DBA knowledge, then you can put the same in your resume that you worked as developer, but got knowledge on dba also. for more understanding, plz chat with me in gtalk. my id is mymailbox.21@gmail.com

      • shashi said

        hi pavan am shashi am wokring in data center since feb 2008.My role is to taking cold backup of standby and production databases. n i have done switch over activity in windows.now am planning to change the job.i want to know what should i prepare for n from where i should start.please suggest me my id is shashi.mudbagilu@gmail.com

      • Pavan DBA said

        hope we discussed this…

  156. vinod kumar said

    hii sir,
    this is vinod..sir im going to complete my btech within less than a month……..so my area of interests is databases..im still in job trials….so can u please give ur valuable suggestions to get a job especially in the side of databases…and also im not able to find any openings for freshers in the stream of oracle or othr dbs

    • Pavan DBA said

      Hi Vinod, to get job as a fresher DBA, you need to acquire the best knowledge in that concept and also need to have a certification. Comparitively fresher dba jobs will be less as most of the companies are recruiting from campus itself.

      If you have strong concepts and certification, you can still get job in very good MNC’s

  157. Mohammed said

    Dear Pavan

    Great Job!
    What is the Duration for RAC Course !
    When will a New Batch Start Every Month?

    Many thanks

    • Pavan DBA said

      Duration would be 30 days. Regarding the batch details you can find at http://www.kannatechnologies.com

  158. Asif Hussain Khazi said

    Hi Pavan,

    I have installed red hat linux 4 in vmware twice for two different servers and installed oracle 10g. I configuers listener in one server and tnsnamesin another server.
    When I attempted to connect to the server I have got the above error. i have attached the file. Please check this and resolve the error………..
    ~]$sqlplus sys/******@to_prd as sysdba
    Error:
    ORA-01031: insufficient privileges

    Enter username:

    • Pavan DBA said

      as discussed over mail, please setup sqlnet.ora parameters

  159. Balu said

    Hello Sir,

    what are the parameters displayed we get while startup of a database.
    Fixed size, Variable size, Total SGA, Database buffers, Redo Buffers.

    • Pavan DBA said

      Fixed Size
      – Contains general information about the state of the database and the
      instance, which the background processes need to access.
      – No user data is stored here.
      – This area is usually less than 100k in size.

      Variable Size
      This section is influenced by the following init.ora parameters
      shared_pool_size
      large_pool_size
      java_pool_size

      Database Buffers
      – Holds copies of data blocks read from datafiles.
      size = db_block_buffers * block size

      Redo Buffers
      – A circular buffer in the SGA that holds information about changes made to
      the database.
      – Enforced mininum is set to 4 times the maximum database block size for the
      host operating system.

      Total SGA – memory allocated to entire SGA

      • Balu said

        Thank U Sir..

  160. Rahul said

    Hello Sir,

    Recently I attended an interview , I have given the questions which I’m not able to
    answer.Could you please provide the answers for these questions.

    1) If your database block size is 8k then how you take the advantage of 16k without setting non-standard block?

    2) What is start SCN & stop SCN?

    3) Why does Oracle advise against backing up online redo logs with a hot backup?

    4) I have created a table with nologging option.But any insert,update or delete still generates redo, why?

    5) How can we know the exact updation date of a particular database?

    Looking forward for your answers. Thanks in advance

    • Pavan DBA said

      1. as far as i know, it is not possible to do that.
      2. here is one good link to understand about SCN – http://www.dbapool.com/articles/1029200701.html
      3. Because without redologs the backup would be incomplete or inconsistent and it is not possible to recover later.
      4. By default any DML statement will generate redo entires even if we specify nologging. NOLOGGING is applicable only for certain conditions as below
      http://dba-oracle.com/t_nologging_append.htm
      5. I didn’t get your question. updation date means?

  161. Sudhir said

    Hi Pavan,

    Recently I attended interview. He asked me many question among those I didn’t answered the questions mentioned below. Could you please provide the answers for these questions.
    1) when we submit a request it is taking long time, and rest of the queries running properly, what would be the reason.
    2) How can we find out whether the request is scanning full table.
    3) Can u explain about explain plan , its use.
    4) How can u find out the problem is with indexes, how can u resolve.
    5) If a request is running properly in all the peak hours, suddenly its get slow, what would be the reason.
    6) I found a gap nearly 10 to 20 lines gap in alert log file what would be the reason.

    Looking forward for your answers.

    • Pavan DBA said

      1. reason could be so many. we need to check for the reason in phases by doing gather stats check, generating explain plan etc
      2. we can check that from explain plan
      3. explain plan is a report which shows us if the query is using indexes or not
      4. by looking at explain plan. if we see query is using indexes, its fine. otherwise we need to create index on required columns
      5. again reasons can be many.
      6. i have never seen any gap like that. may be someone deleted some lines or oracle faced problem in writing to alert log

      Sorry to say this. but I am not sure how you are attending interviews without basic knowledge on performance tuning.(I estimated your knowledge based on your questions, i may be wrong)

      I recommend you strongly to read performance tuning guide from this link
      http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm

      • Sudhir said

        Thanks for your reply.
        I am a fresher, I am aware of these questions, but not sure, that is the reason, I asked you.

        Thanks for your suggestion and I do my best from the next ….

      • Pavan DBA said

        all the very best

  162. Prathap said

    dear sir,

    i have total 145 invalid objects in my datadbase.i used the below script to compile.

    @$ORACLE_HOME/rdbms/admin/utlrp.sql

    after that it reduces 145 to 103.
    then it is not reduceing the invalid objects.

    SQL> SELECT OWNER, OBJECT_TYPE, COUNT(*)FROM DBA_OBJECTS WHERE STATUS = ‘INVALID’
    2 GROUP BY OWNER, OBJECT_TYPE;

    OWNER OBJECT_TYPE COUNT(*)
    —————————— ——————- ———-
    APPS VIEW 1
    APPS PACKAGE 5
    APPS TRIGGER 1
    APPS FUNCTION 5
    APPS PROCEDURE 38
    APPS PACKAGE BODY 21
    ALCRM PACKAGE 1
    ALCRM PROCEDURE 5
    ALCRM PACKAGE BODY 3
    SCOTT MATERIALIZED VIEW 1
    EUL_US PACKAGE 3

    OWNER OBJECT_TYPE COUNT(*)
    —————————— ——————- ———-
    EUL_US PROCEDURE 3
    EUL_US PACKAGE BODY 6
    PUBLIC SYNONYM 5
    JUNK_PS SYNONYM 4
    OLAPSYS PACKAGE BODY 1

    16 rows selected.

    SQL> select count (*) from dba_objects where status like ‘INVALID’;

    COUNT(*)
    ———-
    103

    how can i compile the remaning invalid objects?

    • Pavan DBA said

      Hello Pratap,

      To compile all the invalid objects in the database, we will use that utlrp.sql script. If even after executing that script, we have invalid objects means, we need to inform application/development team and they will take of them

      • Prathap said

        sir,

        i have compiled using utlrp.sql script.it reduceses to 145 to 103.when i will see next day it again showing 145.what is the cause sir?

      • Pavan DBA said

        cause could be because of some modifications to the underlying tables. As a DBA, we should not worry about them until application team says there is some issue

  163. Rashmi said

    Hi Pavan,

    I need to insert lakhs of rows in a table ,what needs to be done so that i get performance benefit.
    Secondly I need to migrate db from one platform to other.The db_block_size of primary db is 4kb ,how do i restore it on other linux paltform with different block_size of the db.

    Thanks.

    • Pavan DBA said

      Hi Rashmi,

      With my experience, I have not seen any special things to be taken care while inserting huge no.of rows. Only thing we will worrying as a DBA is about huge archivelog generation which we can manage. But still if we want, least is disabling the index which may speed up the process a little bit.

      some other places, you can read that “NOLOGGING” clause may help in speed up process, but I want to say that that is not applicable for DML statements.

      regarding your second question, as the databases are different servers, we can use expdp/impdp to acheive this.

  164. upendar said

    hi sir,
    could you please explain me what is the use of cross check in RMAN with example..

    thanks in advance…..

    • Pavan DBA said

      Hi Upendar,

      suppose we have deleted some backup pieces of rman at OS level due to some space pressure. In this case, still RMAN thinks that files are available. so in order to make rman understand that files are not available, we will use CROSSCHECK command. when we run this, rman will mark the deleted backups as EXPIRED and we can remove that info from the backup list.

      I have given an example below…

      RMAN> list backup;

      List of Backup Sets
      ===================

      BS Key Size Device Type Elapsed Time Completion Time
      ——- ———- ———– ———— —————
      1 17.81M DISK 00:00:01 31-MAR-12
      BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235037
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp

      List of Archived Logs in backup set 1
      Thrd Seq Low SCN Low Time Next SCN Next Time
      —- ——- ———- ——— ———- ———
      1 15 255056 31-MAR-12 272351 31-MAR-12

      BS Key Type LV Size Device Type Elapsed Time Completion Time
      ——- —- — ———- ———– ———— —————
      2 Full 221.68M DISK 00:00:24 31-MAR-12
      BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235057
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
      List of Datafiles in backup set 2
      File LV Type Ckp SCN Ckp Time Name
      —- — —- ———- ——— —-
      1 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/system.dbf
      2 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/sysaux.dbf
      3 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/undo.dbf

      BS Key Type LV Size Device Type Elapsed Time Completion Time
      ——- —- — ———- ———– ———— —————
      3 Full 7.52M DISK 00:00:02 31-MAR-12
      BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235057
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
      Control File Included: Ckp SCN: 272376 Ckp time: 31-MAR-12

      BS Key Size Device Type Elapsed Time Completion Time
      ——- ———- ———– ———— —————
      4 2.50K DISK 00:00:00 31-MAR-12
      BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235125
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp

      List of Archived Logs in backup set 4
      Thrd Seq Low SCN Low Time Next SCN Next Time
      —- ——- ———- ——— ———- ———
      1 16 272351 31-MAR-12 272382 31-MAR-12

      RMAN> exit

      Recovery Manager complete.
      c[oracle@pc1 ~]$ cd /u01/app/oracle/flash_recovery_area/TEST/backupset
      [oracle@pc1 backupset]$ ls -ltr
      total 4
      drwxr-x— 2 oracle dba 4096 Mar 31 23:51 2012_03_31
      [oracle@pc1 backupset]$ cd 2012_03_31/
      [oracle@pc1 2012_03_31]$ ls -ltr
      total 253228
      -rw-r—– 1 oracle dba 18676224 Mar 31 23:50 o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
      -rw-r—– 1 oracle dba 232456192 Mar 31 23:51 o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
      -rw-r—– 1 oracle dba 7897088 Mar 31 23:51 o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
      -rw-r—– 1 oracle dba 3072 Mar 31 23:51 o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp
      [oracle@pc1 2012_03_31]$
      [oracle@pc1 2012_03_31]$ rm *.*
      [oracle@pc1 2012_03_31]$
      [oracle@pc1 2012_03_31]$ rman target /

      Recovery Manager: Release 11.2.0.1.0 – Production on Sat Mar 31 23:52:08 2012

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

      connected to target database: TEST (DBID=2077458425)

      RMAN> crosscheck backup;

      using target database control file instead of recovery catalog
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=30 device type=DISK
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp RECID=1 STAMP=779413837
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp RECID=2 STAMP=779413857
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp RECID=3 STAMP=779413883
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp RECID=4 STAMP=779413886
      Crosschecked 4 objects

      RMAN> delete expired backup;

      using channel ORA_DISK_1

      List of Backup Pieces
      BP Key BS Key Pc# Cp# Status Device Type Piece Name
      ——- ——- — — ———– ———– ———-
      1 1 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
      2 2 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
      3 3 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
      4 4 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp

      Do you really want to delete the above objects (enter YES or NO)? yes
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp RECID=1 STAMP=779413837
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp RECID=2 STAMP=779413857
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp RECID=3 STAMP=779413883
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp RECID=4 STAMP=779413886
      Deleted 4 EXPIRED objects

      • upendar said

        thank u very much sir..

        here i have one more doubt..if backup is in some tape then how it will check..?
        and
        what is mean by catalog repository and RMAN repository? is their any difference between these two?

      • Pavan DBA said

        for tape also process is same and RMAN can detect even if backup is in tape. catalog and RMAN repository are one and same

      • upendar said

        thank u sir…..

  165. Rashmi said

    Hi Pavan,

    Why do we store index & tables in different tablespaces.There is a performance benefit for sure but can you please explain how ?

    Thanks.

    • Pavan DBA said

      Hi Rashmi,

      Generally servers will have multiple hard disks to store the files. the reason is, when we use a single hard disk, it will have only 1 I/O header and if this I/O need to do two tasks, then waiting will be there as I/O hdr can handle one task at a time. But when we have mutliple disks, there will be independent I/O hrds for each disk by which writing can be done parallely. This is called “Optimal Flexible Architecture” in Oracle.

      So, as per this, even if we create two tablespaces (one for table and other for index) in the same hard disk, there is no use. we need to create these two tablespaces in different hard disks and place indexes and tables separately which will reduce IO contention and will increase performance.

      Hope you got some idea now…

      • Rashmi said

        Ya.Thanks.

  166. sivakumar said

    Good Evening sir, I would like to know about all the information about cronjobs, and how create a script to scheduled a job for rman backup ,expdp Etc….. Please do the needful.

  167. upendar said

    Hi sir,
    i have a doubt in RMAN . According to my knowledge RMAN catalog will holds changed values and RMAN backups are stored in the at OS level to a disk or to a tape. is it true? if it is true what is mean by changed values and what they contain exactly?
    thanks in advance…….

    • Pavan DBA said

      Hi Upendar, it is not right. RMAN catalog will store backup info, not changed values

      • upendar said

        thanks for the replay..

  168. Rashmi said

    Hi Pavan,

    We can very easily download and apply the oracle patches.But very few of us are aware of main distinction between a CPU & a PSU patch.Can you please share the basic difference betwween the two.

    Thanks.

    Regards,
    Rashmi.

    • Pavan DBA said

      Hi Rashmi,

      CPU (critical patch update) – it is a collection of bug fix patches which will be released every year.
      PSU (patch set update) – it is above CPU i.e it contains all the bug fix patches which are there in CPU and along with that it also contains other cumulative patches.

      Earlier, only CPU’s used to be released every quarter. but now PSU’s are also getting released every quarter.

      See this link – https://forums.oracle.com/forums/thread.jspa?threadID=1057736

      I will post an article about this soon… thanks for bringing this up 🙂

      • Rashmi said

        Thanks a lot Pavan..Have a good day ahead..

  169. Gaurav Nigam said

    Hi Sir,

    Thanks a ton for such a nice notes.
    I have a question:
    I have to drop a user from production box..
    what are the steps i have to take..?
    like first i need to take logical backup etc….i am little bit confused on roles,view, synonyms..shall i need to delete all these before droping the user….?
    Please help..thanks in advance..:-)

    • Pavan DBA said

      Hi Gaurav,

      when you want to drop a user, first you need to have approval from application team. mandatorily mail confirmation should be there.

      1. take logical backup
      2. take DDL for create user
      3. take info about privileges, roles etc assigned
      4. drop the user

      the above are the steps we need to perform.

      • Gaurav Nigam said

        Thanks Sir…

        One more little help ….i want to learn DBA related scripting, any Suggestions.

        Thanks in advance…

      • Pavan DBA said

        First you can learn shell scripting. (if not already have idea). then automatically you can able to write shell scripts. DBA scripting is nothing but shell scripting.
        for this there is a book called “Unix shell scripting” by “yeshwant kanitkar” which I feel is the easy one to understand

  170. sreenu said

    Hi Pavan,
    SQL runs in 10 seconds in DEV DB but the same query takes 10 mins to run in TEST DB? .and Prod DB also 10 secs

    Can advise me how to resole it? it’s an urjent

    Thanks an advance

    • Pavan DBA said

      Hi Sreenu,

      check if statistics are upto date for the table. generate the explain plan in dev and test and compare them. generally I saw these secenarios only when some indexes are missing etc. so explain plan will tell us about indexes.

  171. kishore said

    Hi,
    i attend many interviews recently,most of the are asking 2 qns regularly…1)what are you facing the top issue…in last 3 months
    2)how to tune bad query

    • Pavan DBA said

      You can read “Expert Oracle 11g admin” book by Sam. R. Alapati where tuning was explained nice. you will get your answers there

  172. kishore said

    Hi,This is kishore,interviewer asked me like that
    in 11g we have memory_target parameter is there.they was asking me what about shared_pool,if i need tune that sh_poo how can u tune without mention sh_pool……….in pfile…tell me the ans

    • Pavan DBA said

      we can tune shared pool by increasing the size of memory_target parameter only. we can get that adivce from EM easily.

  173. Rashmi said

    Hi pavan,

    I am continously facing this error, though i tried everything created new listnr but still not able to cope up, please help

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 2: No such file or directory
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.24.23.52)(PORT=1521)))
    TNS-12535: TNS:operation timed out
    TNS-12560: TNS:protocol adapter error
    TNS-00505: Operation timed out
    32-bit Windows Error: 60: Unknown error

    • Pavan DBA said

      Rashmi, can you please post your listener.ora contents?

      • Rashmi said

        Thanks ..
        I resolved it , it was due to incorrect host which couldn’t identify the listener.

      • Pavan DBA said

        ok good.

    • abhi said

      TNS-12541: TNS:no listener
      TNS-12560: TNS:protocol adapter error
      TNS-00511: No listener

      <<<SERVICES->LISTENER_NAME->START..
      >>>>>

      TNS-12535: TNS:operation timed out
      TNS-12560: TNS:protocol adapter error
      TNS-00505: Operation timed out

      <<<enable tracing,increase time out values,.
      if you are connecting from client computer such error may come…

  174. sivakumar said

    Good Evening sir, this is sivakumar, how to move multiple tables at a time one tablespace to another tablespace?, Please give me in detail explanation.

    • Pavan DBA said

      you need to use dynamic sql… see below
      select ‘alter table ‘||owner||’.’||table_name||’ move tablespace ;’ from dba_tables where owner=’your schema name’;

    • sivakumar said

      sir, is there any other way to do this?

      • Pavan DBA said

        Hi Siva, I don’t think so

  175. NIssar said

    Send me RAC Documents which contains the subject from the Basics of RAC.

  176. abhi said

    Hi pavan
    i have gone through several blogs as like Kamran Agayev’s. tom’s etc…but the need full information is at your blog…as like what are the books to be studied…and i have done database cloning through your given steps nice..and very short…

    i wish to know what are the good books or blogs to my self strong in the Sun SOLARIS 10 as i am beginner for that…??

    i need your help sir….

    • Pavan DBA said

      Hi Abhishek, thank you first of all for your comments on my blog. as we are not Os admin’s I believe we can concentrate more on DBA. Having basics knowledge of Solaris would be fine to survive. For those commands, you can refer to google. Let me know if there is specific reason to be strong in solaris.

  177. Mohammed Shahid Khan said

    Dear Pavan,
    I found very good valuable resources, I need your assitant regarding OEM Grid Control 10gR2 installation on CentOS 5.7
    while checking the kernel parameters for installation i am getting the below two parameters failed

    Checking for semmsl2=250; found no entry. Failed <<<<
    Checking for filemax=65536; found no entry. Failed <<<<

    I can not be able to resolve it, need your expert advise.

    Rgds,
    Shahid

  178. suman said

    HI PAvan SIr,

    Can i get step by step doc to upgrade 10g 2 node rac CRS and database upgrade to 11g.

    Thanks,
    Suman

  179. Rashmi said

    GM Pavan,

    Hope u r fine n doing well.Pavan can u pls provide a good document or some link for PT covering basics ,tuning strategies etc.

    Thanks.

    • Hi Rashmi, I don’t have any docs. But to learn PT, oracle documentation is the good way. link for that is below

      http://www.oracle.com/pls/db102/to_pdf?pathname=server.102%2Fb14211.pdf&remark=portal+%28Books%29

      Mostly in interviews they will ask about AWR report analysis etc. for this i already have 2 docs in “important docs” page of my blog. the doc names are “analyzing statspack report 1 & 2”. even though it says about statspack, it is applicable to AWR report also.

      for strategies, you can refer to the questions asked in oracle forums. (http://forums.oracle.com) (you can do a free registration here)

  180. vinod kumar said

    hi sir ,
    this is vinod.i am a passout of 2012.is it a good idea to do oracle apps as a fresher.how are the oppurtunities and what are the prerequisites to learn oracle apps..sir please give your suggestions

    • Hi Vinod, I am not sure what you asked by Oracle apps. Because Oracle apps contains two divisions. One is technical (which is more into programming) and functional (people call it as apps dba, which is into administration side). But as a fresher oppurtunities will be less for any of the divisons

      pre-requisites to learn oracle apps technical – sql, pl/sql, forms and reports
      pre-requisites to learn oracle apps functional (apps dba) – sql, unix basics & basics of shell scripting, Oracle DBA

      • vinod kumar said

        thank you for the advice sir,

  181. Rashmi said

    Pawan,

    Any idea about database replication & steps involved in it ?? Plz help.
    I browsed on google but couldn’t get the relevant info.Please explain in detail as i have to work with a client on it.
    Thanks.

    • Rashmi, there are several ways to configure database replication like using materialized views, streams, active dataguard (in 11g), golden gate (which is new technology). Out of these I have a doc for stream configuration in my blog. see this

      https://pavandba.files.wordpress.com/2009/11/oracle-streams.doc

      • Rashmi said

        Thanks Pavan.But it seems will have to use golden gate third party tool for repliaction here.

      • Golden gate is no more a third party tool. Now Oracle acquired Golden gate company, so its product from Oracle itself. so you can use it.

  182. Naveen.K said

    Hi,

    Iam naveen i completed my DBA . RIght now iam searching for the jobs on DBA as experience of 3 years.Can i let u know in which locations can i find a jobs on DBA….

    • DBA jobs are across the country. You can follow my job postings in the blog and you can apply for them

  183. sivakumar said

    Sir, this is sivakumar, i would like to know how to design the database, i tried to do that, i faced many prob, one of that it, is
    putting Primary key and Fk. So please tell with one example, with any name of database. so that , i can do that

    • Hi Siva, designing database is one the complex jobs DBA will do (that is why most of the times experienced dba’s will do that). As per your question, I can understand that you are talking about table design (preferably we should not call it as database design). In table design, we should concentrate firstly on constraints and need to decide which columns can be PK or FK etc. The best example you can take always is EMP and DEPT tables in SCOTT schema.

      Suppose we have a student management system which is storing students information for a college, then columns in that table can be like this
      Name
      DOB
      Father_name
      Mother_name
      Branch etc

      now from the above you can see no columns can be PK, so we need to add new column by ourself called “student_id” and make that as PK. this column should also be there in other tables where it will be FK.

  184. Rashmi said

    Hi Pavan,

    Hope you are fine and doing well.Can you plz let me know what query do we need to execute to monitor high loads on db & contension.

    • Hi, generally we need to use TOP command to know high load processes and based on process id, we can identify the sql statement. Even for contention we can use iowait command. But all this will be easy if you use Enterprise Manager in 10g

      • Rashmi said

        Thanks a lot.

      • you are welcome Rashmi

  185. anwar said

    Sir,
    i anwartaz completed B.Tech-2011 with aggregate of 65%.After that i did oracle DBA in ameerpet since 5 months i have been looking for job as a fresher/trainee DBA.i uploaded resume in all job portals and i went so many job consultances but idid’nt got any single chance to attend intrview.know i’m totally frustated could u plz guide me how to get job as fresher.i’m eagrly waiting for ur replay

    • Now a days having Oracle DBA knowledge alone is not sufficient as competition is getting more day by day. so try to learn adv topics like oracle RAC, APPS DBA etc. also if possible, try to do OCP certification which enhances the chances of getting job

  186. rafi said

    hi pavan,
    i read u r blogs. its really helps lot.
    i have some questions
    one is how to restore the table (not using logical backup and flashback technology
    second is how to restore rman backup thats having on diff disk or tapes
    and third is what is locks latches and enques and please provide the performance tuning related docs.

    • Hi Rafi, thanks for your comments and here are the points I know….
      1. Without using logical backup and flashback technology, u can do until time recovery in order to get a table. But this will effect other schema’s data.
      2. either in tape or disk, we need to allocate a channel for that device using “allocate channel for device type” cmd and run “restore database” command (if it is full database)
      3. Lock is a method of restricting a user from modification in order to avoid data corruption. This will be maintained for read consistency. Latch is the same concept, but applied for SGA. see this nice ppt – http://www.google.co.in/url?sa=t&rct=j&q=lock%20vs%20latch&source=web&cd=3&ved=0CDYQFjAC&url=http%3A%2F%2Fnocoug.org%2Fdownload%2F2001-05%2Flatches.ppt&ei=oeQLT9KuAY3zrQeWrKGwBA&usg=AFQjCNEFYgofQIuKyRW29LuzRpOmidkDBA&cad=rja

      Also, for PT always start with PT guide in oracle documentation

  187. prathap said

    Hi sir,

    this is prathap,i have 4 doubts?

    1.archive logs are applying from production to standby…..suppose suddenely logs stoped shipping due to network problem. then wat steps have to take as a dba?

    2.archive logs appling very very slowley?wat type of steps we have to take as a dba?

    3.suppose i deleted 20(by mistake)archive logs in production before applying to standby.then wat type of steps to be take as dba?

    • 1. if it is 9.2.0.1 or above version, then FAL processes will take care of archive log gap when network connection re-establishes
      2. you need to check alert log of standby to check for any further information.
      3. then u need to take incremental backup of primary database and rollforward that to standby. for steps, refer to google

      • prathap said

        hi sir,

        1.we are using 10g …Wat is FAL process duty?

        2.thank u

        3.thank you.

      • On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).

      • prathap said

        hi sir,

        on standby database 400 archivelogs are missed .i transfered that archive logs from production to standby manually by using scp command.then how apply that archive logs in standby?please provide me steps sir…………

      • you need to just make sure MRP process is up and running, rest will be taken care by it. or else, u can run register archivelog (for command check google)

      • prathap said

        thank u

  188. Jagat said

    Deadlock happened intermittent from a single select statements. There is no other second SQL statement. The select statements have parallel processes.What you think what might be the issues

    • Mohanty, I suppose there wouldn’t be deadlock issue for select stmt as they will hold only shared locks. so tracing the session may give more results

      • Jagat said

        Thank Pavan for your reply !

        I checked in the trace file it does not show call stack of use PX operations.
        The trace file also masking the commands which were run to hold Shared or Exclusive locks by the SQL. All deadlock traces are partial trace.
        Is _bloom_pruning_enabled”=FALSE setting really help on this?,We have PSU3,guess it may be a bug not fixed in PSU3 .can you suggest?.S and X locks are conflicting and one prevent the other P0 from making any progress. The P0 sessions try to get hold of same locks in reverse order.

      • I feel it is better to raise a SR (if not already done) before setting that parameter, bcz they can give better option for tracing

  189. Rashmi said

    Hi Pawan,

    Can u plz upload any document for oracle installation on linux platform, with details like
    what are kernel parameters and why do we need them. and so on.

    Thanks
    Rashmi.

    • Rashmi, already there is a doc in “Important docs” page of my blog. You can access at https://pavandba.files.wordpress.com/2009/11/oracle-10g-on-linux.docx

  190. Ravinder Reddy said

    Hi Sir,

    This is Ravinder. I need some help from you.
    recently I got a chance to reorg some tables.
    As per my knowledge we have the following methods.
    1.CTAS
    2.Move
    3.export & import.
    In my case above 3 are not working.
    Here the database version is 8i(8.17).
    table size is 7GB and it contain one long datatype.

    I tried to use move command but it didn’t work bcos of long datatype.
    next trial I export the table with no indexes,no constraints it took almost 30 mins.
    But for import it is taking more than 10 hrs.

    Is there any other way to reorg this table?

    Thanks & Regards,
    Ravinder.

    • Hi Ravinder,

      as per my knowledge, export/import is the possible way since the database is 8i. You can better try to import that table into a test database. once done, you can export from there and import into production. (i believe even if u have import running for more than 10hrs, there will be no issue). Import will take that time because of LONG datatype.

      Try to use buffer, commit parameters while importing

    • Hi Sir,

      Thanks for your reply.
      Will try it.

  191. bhaskar said

    hi sir this is bhaskar student of kanna inst bd-19 batch. plse reply my questions to this mail id bhaskar.kanna1986@gmail.com
    1.What is the differences between oracle 9i and 10g versions of RMAN backup ?
    2.now iam trying on 3.2 years of exp… in interviews they asking in which level ur working L1 or L2 what is L1 and L2

    • 1. there are so many differences. you can find them from net
      2. give them answer as L2

      • bhaskar said

        thanks sir

  192. Shreya said

    Hi Pavan

    I am a aspiring DBA. I have taken coaching at a institute in Ameerpet. I have practised in lab environment. But i am putting 2.8 yrs experience as i was passed out in 2008. In the interview people are asking me several technical questions that i could answer. But some people are asking me questions such as ” TELL ME THE STEPS – – – HOW YOU CONNECT TO THE DATABASE IN UNIX PLATFORM MACHINES? ” I am unable to give the steps. please help me with these basic things. I am a hard worker, but due to some reasons i had not given a try at software job in these years.

    Please help me.

    • I am not sure if you practised on Linux env or not. If you practised, then its same way that we connect to database even on unix.
      export ORACLE_SID=
      sqlplus “/ as sysdba”

      let me know if u have any queries

      • Shreya said

        I have practiced on Linux platform only. RHEL 4 version.
        I say this as the answer, but immediate question they ask me is — ” YOUR TASK IS TO SWITCH ON THE MACHINE AND CONNECT TO THE DATABASE AND CHECK TO WHICH DATABASE YOU ARE CONNECTED “.

        I understand this sqlplus “/ as sysdba”, but I need elaborate steps.

        Please help.

      • Generally switch on the machine is not our task as a DBA. connecting to the database is same i told in prev reply. how to check which database we are connected? we need to use below query for that

        select * from v$instance;
        select name,open_mode from v$database;

      • Shreya said

        This will help me i think.. Thanks a lot Mr.Pavan. I know that there are some tools used to get access to the database like Putty. Can u tell me few other of such type which we generally use.

        Thank you so much once again for the reply.

      • for daily use to connect to server we use putty
        for some other db related tasks we use Enterprise Manager
        for installations etc using GUI we use tools like vncviewer, humming bird, xmanager etc

        more than this any tools using depends on project

      • Shreya said

        Thank you so much for the valuable response. I hope it helps me get a job soon.
        Thanks again Pavan.

      • Shreya said

        Hi pavan,
        Can you please list down the steps ” HOW TO APPLY PATCHES,&, DIFF B/W CPU AND PSU”?
        Is it possible to directly upgrade from 10.2.0.2 to 10.2.0.5?

      • Soon I will upload doc on how to apply CPU and PSU.
        Regarding the difference between them, CPU (critical patch update) which is a quarterly patch released by oracle corp. PSU (patch set update) is a collection of patches and when you apply this your database version will change.

        you can surely do upgrade from 10.2.0.2 to 10.2.0.5. for this you need to apply 10.2.0.5 patchset update

      • Shreya said

        Hi

        I am waiting for you to upload the above mentioned doc.
        Meanwhile, I would like to know ” What is a monitoring tool? What monitoring tools do a DBA use in real time? ”

        Thanks in advance.

      • Hi Shreya, I got too busy with office work and couldn’t be able to upload it. Let me do it soon
        About monitoring tool, generally we will use it for performance monitoring of the database. even though there are so many 3rd party tools available in the market, I just saw DBA’s using 10g Enterprise Manager only to do this….

  193. sridhar said

    Sir this is sridhar.mukta..

    In a team how many members have metalink access? and who wil download the patch and analysis? this question was asked in oracle corporation.. i could not give the successful answer..and what happens if we happen patch?

    He asked a question me that any issues you faced in patching? then i was answer that “pre requisite patch was missed”. he asked that is it ur mistake r ur team problem? r any analysis problem? who will analyse patch ?

    sir please give me a brief answer “what happens in real time before apply a patch”?

    Regards,
    sridhar.mukta