Ask me

If you have a question or suggestion for a blog post, please leave a comment below.

If you need help regarding any issues with your database,  you can post it here — I will try to resolve it and get back to you.

Regards

Anju Garg

129 thoughts on “Ask me

  1. Hi Anju Madam,

    I would like to request to post an article on Adaptive cursor sharing. I read various article but i am still confused with it behavior. Your explanation with example make thing very clear to understand.

    Thanks
    Jamsher

  2. Hi Anju, the explanation on 11g RAC features is simply superb.I could able to remember things as I read on it without memorizing it. Thank for your time on sharing those.

  3. Hi Anju,

    My question is on Cardinality in row source operation. Part of my tkprof output is below.
    Please let me know the difference between card=108878 and rows (1st) value 12406832 shown. I know cardinality is the number of rows flown from one operation to other, but here what is rows (1st) .

    Rows (1st) Rows (avg) Rows (max) Row Source Operation
    ———- ———- ———- —————————————————
    12406832 12406832 12406832 TABLE ACCESS BY INDEX ROWID PWORKSPACEOBJECT (cr=984297 pr=979363 pw=0 time=39203597 us cost=29209 size=3810730 card=108878)
    12406979 12406979 12406979 INDEX RANGE SCAN PIPWORKSPACEOBJ_0 (cr=46839 pr=46834 pw=0 time=4248986 us cost=873 size=0 card=217859)(object id 69829)
    0 0 0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=99965 size=144804412 card=6295844)

    Thanks always for your help

    Regards
    Rakesh

      1. Thank you very much for providing the article link , I still has a question on rows and card values in rows source operation of trace output , in explain plan output we consider the card value as the number of rows processed in that operation , but in below case whats the difference between rows( value =258025) and card (value 229711) .Rows is number of rows returned by that row source operation, does card also signify the same?

        Rows Row Source Operation
        ——- —————————————————
        258025 PARTITION RANGE ALL PARTITION: 1 28 (cr=18811 pr=756 pw=0 time=664296 us cost=536 size=6661619 card=229711)
        258025 TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=18811 pr=756 pw=0 time=465129 us cost=536 size=6661619 card=229711

        Thanks always for your time in helping us

        Regards,
        Rakesh

        1. Hi Rakesh,

          card(inality) refers to the no. of rows as estimated by optimizer whereas rows are the actual no. of roes returned by the operation.

          Regards
          Anju GArg

  4. Hi i have a problem with the installation of Oracle Grid Infrastructure, in execution of file root.sh appears the next error, hope you help me thanks

    Failed to create or upgrade OLR
    thanks
    miguel

    1. Pls try this :

      With root user.
      chmod 755 /etc/oracle

      ### Deconfigure failed root.sh execution

      $GRID_HOME/crs/install/roothas.pl -deinstall -force

      Re-run root.sh

      Hope it helps

      Anju Garg

  5. Hi, thanks to reply, i did that but i have the same problem, this is the entire problem

    /grid/11.2/bin/ocrconfig.bin: error when loading shared libraries: libclntsh.so.11.1: cannot open shared object file : no such file or directory

    *Failed to create or update OLR

    thanks
    miguel

  6. hi again, i executed $GRID_HOME/crs/install/roothas.pl -deinstall -force, but appears unknown option: deinstall

    thanks
    miguel

    1. Pls give me thr output of following commands :
      id (as user performing GI install)
      ls -l /dev/oracleasm/Disks/*
      echo $ORACLE_HOME
      echo $ORACLE_BASE

      Regards
      Anju

  7. Hi, can you help me with the next issue i have a problem with installation of that “/usr/sbin/oracleasm init”, when execute appears the next message:
    Loading module “oracleasm”: failed
    Unable to load module “oracleasm”

    i have installed asm
    oracleasm-2.6.18-164.el5xen-2.0.5-1.el5.i686
    oracleasmlib-2.0.4-1.el6.i686
    oracleasm-2.6.18-164.el5debug-2.0.5-1.el5.i686
    oracleasm-support-2.1.8-1.el5.i386
    oracleasm-2.6.18-164.el5PAE-2.0.5-1.el5.i686
    oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686

    the version of kernel is:
    2.6.32-279.el6.i686
    thanks

    1. Hi,

      It seems that oracleasm rpm version does not match that of your kernel .
      Download oracleasm rpm’s of same version as kernel and retry.

      Hope it helps

      Regards
      Anju GArg

  8. Hi,

    I do not have good knowledge in oracle performance tuning. can you please post step by step sql query tuning. For example, get a query from application team. general steps for query tuning. Please post ASAP. it will be useful for me. you can also send it to my mail id.
    saravvanon88@yahoo.co.in. Please do it as a help.

  9. Hi
    Newbie in Oracle RAC 11gr2. Can you help me in understanding the basic for Cluster/Network and ASM and also the working of it

  10. hI Anuj,
    your explaination Gpnp is mind blowing-never seen anybody explain it like this-so impressive-would like to know if you have written anything on asm-multipathing etc
    you are amazing

    thnx selwyn

    1. Hi Selwyn,

      Thanks for your time. I have not written anything on asm-multipathing etc till now.
      Your comments and suggestions are always welcome.
      By the way, the name is Anju and not Anuj.

      Regards
      Anju Garg

    1. Hi!

      Performance Tuning has 3 stages :
      Monitoring – Monitor your Database for any performance problems
      Diagnosis – Try to find out the reason behind performance degradation
      Tuning – Resolve the issue.

      You need to learn these stages .

      Regards
      Anju Garg

  11. Hi Anju,

    Thank you for your posts and sharing in-depth knowledge with us. I have a question and it would be great if you could help answer it. The question is when we keep voting disks and ocr in an ASM diskgroup with external redundancy, and if for some reason the voting disks is not reachable or becomes unavailable (due to I/O issue for eg), will the crs crash? How will external redundancy help in this case? I understand that if we have normal redundancy, ASM will use the mirrored copy, but need to understand how ASM works to provide HA when the redundancy is external.

    Thanks
    Saanchi

    1. Hi Saanchi,

      Thanks for your time. If voting disk and OCR are on ASM diskgroup with external redundancy, ASM does not provide redundancy. If voting disk becomes unreachable, crs will crash. In this case, you will have to recover voting disk from its backup which now resides in OCR backup itself.

      Regards
      Anju Garg

  12. I would like to request you that kindly detail explanation about types of buffer in database buffer cache and working of them.
    Thanks..

    Nisha Srivastava.

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

    1. Hi Akhil,

      SInce on delete cascade feature is not there, you will need to delete child records prior to parent records. Hence you could:

      — Identify all the children in order of hierarchy
      — Delete records from the tables going up in the hierarchy
      — After delete operation is over, you may need to run segment advisor on the tables / dependent indexes since they might have become fragmented as the result of delete.
      — Depending on the recommendation of the segment advisor, you may
      . Rebuild indexes
      . Reorganize tables

      Note: Since you are deleting 10000K records based on date column, things would be much easier if you partition your table on date column with local indexes. In that case, you can simply truncate a partition which will be more efficient as redo will not be generated. Of course it depends upon the requirement of your application.

      Hope it helps.

      Regards
      Anju Garg

  14. Hello Madam , Request you to please post regarding SQL tunning. As I do get confuse when big query. Request you to explain these terms in your way which I find the simplest way to understand complex cocepts :) :) Thanks a lot

  15. Hi Anju,
    very good article on Flashback on Primary and standby in 11G. I am going to follow your steps to test how to flashback primary and standby. my question to you is this:
    I have a dataguard environment (in 11GR2) where i have the primary and two standbys. The whole environment is managed by the DG Broker. My application team is going to do a massive upgrade which will modify data in many tables. i am going to setup a Guaranteed restore point (GRP) on both the primary and standby databases. now, if there are any issues on the primary, they will ask me to flash it back to the GRP. I would really appreciate your input on how i should proceed if i want to flashback the primary.
    1. do I just do flashback on PMY and the standby will automatically flashback too?
    2. Do I have to make changes in the DG configuration? I hope not.
    3. Do I have to restore from an RMAN backup (just like you showed in your article?)

    If you just highlight the general steps, I wuold be greatful.
    Thank you.

    1. 1. As I have demonstrated, if you flashback primary, standby will diverge from primary as its SCN# will be ahead of primary. You will have to flashback standby as well.
      2. You need not make any changes to DG configuration.
      3. I could not locate where I have restored from RMAN backup.

      Regards
      Anju Garg

  16. I have gone thru your articles. I need tuning steps also I need document for sql tuning and awr report reading document.

  17. hi mam,

    can you tell why ohasd is introduced from 11g,what are the benefits of it.since crsd done all works in oracle 10g and which we put init/tab.can you tell the reason for ohasd.

  18. Hi I need solution for one of my problem we have primary and physical standby setup on windows. Actually logs are transferring from primary to secondary but they are not applying. I tried lot of things but still problem is there. when ever I recover it always ask for archive files which are no longer exists. Please suggest solution

  19. Hi Anju garg ;

    I tried database cloning from one server to another server.

    MY target and auxiliary db verisons are : 10.2.0.5.0
    but unfortunately getting following error.
    ORA-01130: database file version 10.2.0.5.0 incompatible with ORACLE version 10.2.0.1.0

    ON TARGET SERVER

    RMAN> duplicate target database to ‘hrms’ nofilenamecheck;

    Starting Duplicate Db at 20-NOV-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_AUX_DISK_1
    channel ORA_AUX_DISK_1: sid=156 devtype=DISK

    contents of Memory Script:
    {
    set until scn 711356;
    set newname for datafile 1 to
    “/u01/app/oracle/oradata/hrms/system01.dbf”;
    set newname for datafile 2 to
    “/u01/app/oracle/oradata/hrms/undotbs01.dbf”;
    set newname for datafile 3 to
    “/u01/app/oracle/oradata/hrms/sysaux01.dbf”;
    set newname for datafile 4 to
    “/u01/app/oracle/oradata/hrms/users01.dbf”;
    set newname for datafile 5 to
    “/u01/app/oracle/oradata/hrms/sample01.dbf”;
    restore
    check readonly
    clone database
    ;
    }
    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting restore at 20-NOV-14
    using channel ORA_AUX_DISK_1

    channel ORA_AUX_DISK_1: starting datafile backupset restore
    channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00001 to /u01/app/oracle/oradata/hrms/system01.dbf
    restoring datafile 00002 to /u01/app/oracle/oradata/hrms/undotbs01.dbf
    restoring datafile 00003 to /u01/app/oracle/oradata/hrms/sysaux01.dbf
    restoring datafile 00004 to /u01/app/oracle/oradata/hrms/users01.dbf
    restoring datafile 00005 to /u01/app/oracle/oradata/hrms/sample01.dbf
    channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/HRMS/backupset/2014_11_16/o1_mf_nnndf_TAG20141116T000753_b6h7d2nb_.bkp
    channel ORA_AUX_DISK_1: restored backup piece 1
    piece handle=/u01/app/oracle/flash_recovery_area/HRMS/backupset/2014_11_16/o1_mf_nnndf_TAG20141116T000753_b6h7d2nb_.bkp tag=TAG20141116T000753
    channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:14
    Finished restore at 20-NOV-14
    sql statement: CREATE CONTROLFILE REUSE SET DATABASE “HRMS” RESETLOGS ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
    LOGFILE
    GROUP 1 SIZE 50 M ,
    GROUP 2 SIZE 50 M ,
    GROUP 3 SIZE 50 M
    DATAFILE
    ‘/u01/app/oracle/oradata/hrms/system01.dbf’
    CHARACTER SET WE8ISO8859P1

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of Duplicate Db command at 11/20/2014 07:46:01
    RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
    ORA-01130: database file version 10.2.0.5.0 incompatible with ORACLE version 10.2.0.1.0
    ORA-01110: data file 1: ‘/u01/app/oracle/oradata/hrms/system01.dbf’

    ON AUXILIARY SERVER I HAVE CHECKED IT
    ——————————————————-

    COMP_NAME STATUS
    —————————————- ———–
    Oracle Database Catalog Views VALID
    Oracle Database Packages and Types VALID
    Oracle Workspace Manager VALID
    JServer JAVA Virtual Machine VALID
    Oracle XDK VALID
    Oracle Database Java Packages VALID
    Oracle Expression Filter VALID
    Oracle Data Mining VALID
    Oracle Text VALID
    Oracle XML Database VALID
    Oracle Rules Manager VALID

    COMP_NAME STATUS
    —————————————- ———–
    Oracle interMedia VALID
    OLAP Analytic Workspace VALID
    Oracle OLAP API VALID
    OLAP Catalog VALID
    Spatial VALID
    Oracle Enterprise Manager VALID

    17 rows selected.

    THANKS IN ADVANCE !!
    R. CHANDRA (BIHAR)

    1. It appears that your source database is 10.2.0.1 and target software version is 10.2.0.5.
      Install 10.2.0.5 software on th t destination server and then retry.

      Regards
      Anju Garg

  20. Hi Anju, I have a question on RAC connection failover and load balancing. This is for 10gR2 2 node RAC instance.

    My question is, if the VIP of node 2 is relocated to node 1(either manually or if the server goes down) then, the existing sessions will be rolled back and applications will have to reconnect so that all the connections are routed to node1 (TAF not configured). Is that correct? ie there will be no manual redirection of connections from node 2 to node 1.

    When node 2 comes back up, what will happen to the existing connections in node 1 ? Are they routed to node 2 so that the connections are balanced across the two nodes? Basically, Will oracle load balance the existing connections when node 2 is back up?

    Thanks
    Saanchi

    1. Hi Saanchi

      You are correct. There will be no manual redirection of connections from node 2 to node 1 if node2 goes down and TAF has not been configured.When node2 comes back, existing connections will not be routed to node2.

      Hope this helps.
      Regards
      Anju

  21. Hi Anju,

    I am interested in learning 12c , have a laptop with 4GB RAM will that we sufficient to play around 12c or need a higher configuration, I want to try out concepts like Golden gate and Datagaurd on 12C please advise

    Regards
    Dev

    1. Hello Dev,

      You could manage with 4GB if you configure primary / standby on the same machine and have non-ASM DB. In case you want separate VM’s for pri / sby, 4GB won’t sufficient. You need at least 8GB. In case you want to use cloud control and ASM as well, you will need 16 GB.

      Best of luck

      Regards
      Anju Garg

  22. Good Morning,

    Iam stuck in a situation. My oracle production database is on 10g now. While the database was up and running, it went down all of a sudden and was not getting opened although geeting mounted. Throws an error while opening the database and the error says “datafile 36 I/O error and it seems that the header of this datafile got corrupted. Although the database is a production but has not been on Archive log mode. There is neither any hot nor cold backup of the database except a logical backup.
    Steps i followed:
    Tried to recover the header corrupted datafile 35 but the recovery prompt does not come. At mount stage, i executed, sql>recover database using backup controlfile until cancel; but did not work since db is not opening in resetlog as well.
    Then client said that he does not need datafile 35 and datafile 36. These 2 datafiles basically belong to one tablespace.
    So i recreated the Control file after removing these 2 files from control file. Th edb got mounted but still not opening. Now the error is :
    System01.dbf is not consistent as per the control file and needs a recovery.

    Since there was an urgent need to bring up th edatabase by hook and crook, so i added “_allow_resetlogs_corruption” parameter and bounced the database and then took an export backup.
    Then cleanly shut down the database and removed this parameter and started up the database and its opened now.
    But those 2 datafiles which i removed from conrol file are now appearing as “Missing01.dbf” and “missing02.dbf” files.

    Please suggest how to get rid of this situation. Its really urgent since its a production database.

    Sincerely
    Manmohan Bhakuni

  23. Hi, Anju. I liked your post on changing the SQL*Plus prompt within CDBs/PDBs. I’m going to be giving a presentation on Mutlitenant databases at a conference next month. Would you mind if I were to share that technique, as long as I attribute it to you?

    Thanks!

    1. Hi Ken

      Thanks for your time and feedback. It will be an honour if you share this technique during the presentation.
      Do refer to my blog if you feel appropriate.

      Thanks and Regards
      Anju Garg

  24. Hi Anju
    I am really impressed reading your blog as its very very helpful.
    Today I am looking out for a very good document for installing the 12c Extended RAC Cluster on Oracle Linux with physical standby.
    (And also the prerequites required for installing the same.
    Kindly let me know if you have any good notes.
    Thanks

  25. Good Morning M’am,

    Recently i upgraded on of my producation database from 10g to 11.2.0.4 (Patch 10). The database is successfully upgraded but now the issue is that im able to take an export backup of the live schema using EXP utility successfully but unable to Import using IMP utility in Train schema. Im exporting in upgraded 11.2.0.4 database and importing in the same database but in another TRAIN schema.

    Troubleshoot: After googling, i came to know that there is an issue with a DATA_TYPE=”ANYDATASET”.

    Solution provided is that there is a need to UPGRADE all those Tables which are having “ANYDATASET” data_type columns.

    SET echo off
    SET feedback off
    SET term off
    SET pagesize 0
    SET linesize 80
    SET newpage 0
    SET space 0
    spool d:\kit\upgrd.SQL
    SELECT DISTINCT ‘alter table ‘||owner||’.’||table_name||’ upgrade;’ FROM DBA_TAB_COLUMNS b WHERE DATA_TYPE=’ANYDATASET';
    spool OFF

    BuT when we execute the UPGRADE table command, we come across the below error:

    ‘ALTERTABLE’||OWNER||’.’||TABLE_NAME||’UPGRADE;’
    ——————————————————————————–
    alter table OPERA.CACHED$_OWSAVAIL_RESULTS upgrade;

    SQL> alter table OPERA.CACHED$_OWSAVAIL_RESULTS upgrade;
    alter table OPERA.CACHED$_OWSAVAIL_RESULTS upgrade
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [kkbogdp1], [], [], [], [], [], [],
    [], [], [], [], []

    SQL>

    PLEASE SHARE YOUR EXPERTISE SO THAT MY TRAIN SCHEMA IS UP AND RUNNING

    ITS LITTLE URGENT.

    sINCERELY,
    manmohan

  26. just a quick question about point in time recovery of a primary database and flashing back the standby:
    Do we need to stop log apply on the standby while we do PITR on the Primary?

  27. Good Afternoon M’am,

    Hope u r doing good.

    thanks a lot for your timely response.

    just a quick concern…

    We have added 1 more temporary file in the TEMP tablespace in the primary database(now we have two tempfiles in temp tablespace in primary database) but the newly added temp2 file has not got generated automatically in the Standby server.

    1. So is it a normal behaviour and we need to add this temp file in Standby server?
    2. Also, we resized the Temp01.dbf file in primary server from 1gb to 2gb but in the standby server, it shows only 1 gb size.

    Need ur expert assistance.

    1. Hi Manmohan,
      It is good to know that I was able to help you.
      For your queries, kindly refer to the following links :
      http://support.dbvisit.com/entries/88727217-Adding-Temp-tablespace-datafiles
      http://www.idevelopment.info/data/Oracle/DBA_tips/Standby_Database/SD_3.shtml
      I have not tried the scenarios described by you but based on the info in above links,
      – When you add/resize a tempfile on primary, tempfile definitions are not stored/updated in the “SYS.FILE$” dictionary table. Because adding a tempfile on the primary does not update “SYS.FILE$”, there is no redo generated. So recovery on the standby cannot create/resize the tempfile. Hence, you need to add/resize tempfile on standby manually.

      Hope it helps.

      regards
      Anju

  28. Good Evening,

    Thanks a lot for sparing moments from your official chores in order to entertain all of our concerns.

    Sincerely,
    manmohan

  29. Hi Anju,
    Firstable I would said that I really appreciate your blog.excellent.It is giving me a better understanding of the Oracle database.
    To indrocuce myself,I have been working in Oracle On demand 12years as applications system administrator but one year ago I started to work for AT&T network as database administrator because I had a background as DBA before .
    I have your seen your posts regarding shared pool tuning and would like to ask where I can find the description of the memory structures x$kgllk, x$kglpn and x$kglob columns,in order t understand the relationship between them.

    Best regards
    Taf (not ‘Transparent Application failover’ But ny nickname)

  30. Good Evening,

    First of all thanks a lot for your timely response.

    I have got a concern regarding oracle DG server hardware.

    My production database is running on HP-Proliant G7 server (physical server) and has Windows 2008R2 as an OS and 11.2.0.4 as an oracle Database. Now, my management wants to implement a DG in our environment.

    Option 1:
    We have a spare high end IBM server which they want me to use it for the DG server. This server already as 2008R2 OS.
    Q: Can i use this IBM server since i have a doubt as what i know is that the Physical Hardware MUST always be IDENTICAL to the Primary hardware server which is HP-proliant in my case. Is it possible to proceed in this Hardware.

    Option 2:
    We have also one high end HP-proliant G8 server but few low-end applications are running on this. We are thinking of configuring VMWARE on this server and then shall have the similar 2008R2 OS in VMWARE.
    Q: Should i use this option or its not possible. Although this HP server is similar to the HP server of primary database but its G8 whereas the primary is on G7.

    Please suggest since very soon this project is going to come and im going to get night mares.

    Sincerely,
    manmohan bahkuni

    1. Hi Manmohan

      Pls refer to following link:

      https://docs.oracle.com/cd/E11882_01/server.112/e41134.pdf

      As of Oracle Database 11g, Data Guard provides increased flexibility for Data Guard
      configurations in which the primary and standby systems may have different CPU
      architectures, operating systems (for example, Windows & Linux), operating system
      binaries (32-bit/64-bit), or Oracle database binaries (32-bit/64-bit).
      This increased mixed-platform flexibility is subject to the current restrictions
      documented in the My Oracle Support notes 413484.1 and 1085687.1 at
      http://support.oracle.com.
      Note 413484.1 discusses mixed-platform support and restrictions for physical
      standbys

      Hope it helps!
      Regards
      Anju

      1. Good Morning,

        Thanks for an update.

        Honestly speaking, my query is still not cleared although i have gone thru both the documents and which are little confusing.

        So to be concise and precise, my production server is HP-Proliant(g7) hardware server with Win2008R2 as an OS.

        So, may i use an IBM hardware server with Win2008R2 as an OS for creating a Physical Standby database?

        Please assist. Its little urgent.

        Sincerely,
        manmohan Bhakuni

        1. Good morning Manmohan,

          We can have standby with different hardware but in this case, you might have to compromise with db performance after switchover or failover.
          Clients do use such standby only to save the cost .

          Regards
          Anju

          1. Good Morning,
            Thanks for an update.

            Yes, certainly agreed with you. Actually, this IBM server cost is same as the cost of HP proliant server. basically, they had bought this IBM server for another project but that project has gone on hold, thus want to make use of this IBM server for Data Guard physical standby purpose. As far as performance is concerned, client will be ok since the s/o or f/o shall be for a short period of time.

            Once again thanks a lot for your precious information.

            This evening, im going to give them Green Signal in order to build the Physical standby 11g on the IBM server.

            I was always thinking that the Hardware server MUST always be SAME for Primary server and Data Guard server. Now i think, that i had been wrong.

            Highly appreciated and you saved my day.

            Sincerely,
            Manmohan Bhakuni

  31. HI,
    Recently migrated schema from oracle 11g to 12c. After went the database to test. I encounter performance issue. All the queries running extremely. while analyzing this, i have been generated explain plan. in 11g it is running good and taking low cpu and cost. But 12c its cost and cordiality is high. Due this i have been change optimizer_features_enable parameter from 12.1.0.2 to 11.2.0.4 and then optimizer is creating plan was same as 11g.

    could you please help on what is issue here any why same qurey is taking high cost when it was in 12c.

    1. Hi Saravana

      Kindly ensure that you have gathered statistics for all the objects (Tables / Indexes / Partitions) after migration.

      Pls send query / execution plan in 11g / 12c .

      Regards
      Anju

      1. Thanks Anju!
        Due this issue,Just i export schema stats and drop old stats for a schema and gather stats for a schema. Here i’m sharing the log, how i trace this error. could please help me to fix this issue.

        SQL> sho parameter optimizer_features_enable

        NAME TYPE VALUE
        ———————————— ———– ——————————
        optimizer_features_enable string 12.1.0.2
        SQL> alter session set optimizer_features_enable=’11.2.0.2′;

        Session altered.

        SQL> sho parameter optimizer_features_enable

        NAME TYPE VALUE
        ———————————— ———– ——————————
        optimizer_features_enable string 11.2.0.2
        SQL>
        SQL> set timi on
        SQL> explain plan for
        select A.P_ID, X.P_ALT_ID, A.P_SORT_NAM, A.P_NAM, A.P_TY_CD, A.P_DEA_NUM, A.P_BORDER_CD,
        A.P_APPL_DT, A.P_DBA_NAM, A.G_TEST_IND, B.G_ADR_LINE1_AD, B.G_ADR_CITY_NAM,
        B.G_ADR_STATE_CD, B.G_ADR_ZIP5_CD, B.G_ADR_PHONE_NUM, A.P_FED_TAX_ID, B.G_ADR_TY_CD,
        X.P_ALT_ID_CD, X.P_ALT_ID_TY_CD, A.G_AUD_TS MAIN_AUD_TS, A.G_AUD_USER_ID MAIN_USER_ID,
        2 B.G_AUD_TS ADDR_AUD_TS, B.G_AUD_USER_ID ADDR_USER_ID
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum select * from table(dbms_xplan.display);
        PLAN_TABLE_OUTPUT
        ——————————————————————————————————————————————————————————————————–
        Plan hash value: 802184744

        ———————————————————————————————–
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
        ———————————————————————————————–
        | 0 | SELECT STATEMENT | | 500 | 101K| 7115 (1)| 00:00:01 |
        |* 1 | COUNT STOPKEY | | | | | |
        | 2 | NESTED LOOPS OUTER | | 501 | 101K| 7115 (1)| 00:00:01 |
        | 3 | NESTED LOOPS | | 417 | 53376 | 5873 (1)| 00:00:01 |
        | 4 | TABLE ACCESS BY INDEX ROWID| PROVDRTB | 571K| 54M| 1322 (0)| 00:00:01 |
        |* 5 | INDEX FULL SCAN | PROVDRTB_IF3 | 1250 | | 91 (0)| 00:00:01 |
        |* 6 | TABLE ACCESS BY INDEX ROWID| PXREFXTB | 1 | 28 | 4 (0)| 00:00:01 |
        |* 7 | INDEX RANGE SCAN | PXREFXX2 | 2 | | 2 (0)| 00:00:01 |
        | 8 | TABLE ACCESS BY INDEX ROWID | PLADDRTB | 1 | 80 | 3 (0)| 00:00:01 |
        |* 9 | INDEX RANGE SCAN | PLADDRTB_IF1 | 1 | | 2 (0)| 00:00:01 |
        ———————————————————————————————–

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

        1 – filter(ROWNUM select count(*)
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum
        SQL> sho parameter optimizer_features_enable

        NAME TYPE VALUE
        ———————————— ———– ——————————
        optimizer_features_enable string 12.1.0.2
        SQL> explain plan for
        select A.P_ID, X.P_ALT_ID, A.P_SORT_NAM, A.P_NAM, A.P_TY_CD, A.P_DEA_NUM, A.P_BORDER_CD,
        A.P_APPL_DT, A.P_DBA_NAM, A.G_TEST_IND, B.G_ADR_LINE1_AD, B.G_ADR_CITY_NAM,
        B.G_ADR_STATE_CD, B.G_ADR_ZIP5_CD, B.G_ADR_PHONE_NUM, A.P_FED_TAX_ID, B.G_ADR_TY_CD,
        X.P_ALT_ID_CD, X.P_ALT_ID_TY_CD, A.G_AUD_TS MAIN_AUD_TS, A.G_AUD_USER_ID MAIN_USER_ID,
        B.G_AUD_TS ADDR_AUD_TS, B.G_AUD_USER_ID ADDR_USER_ID
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum select * from table(dbms_xplan.display);

        PLAN_TABLE_OUTPUT
        ——————————————————————————————————————————————————————————————————–
        Plan hash value: 3194642267

        ——————————————————————————————————-
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
        ——————————————————————————————————-
        | 0 | SELECT STATEMENT | | 6 | 1320 | 29 (4)| 00:00:01 |
        | 1 | SORT ORDER BY | | 6 | 1320 | 29 (4)| 00:00:01 |
        |* 2 | COUNT STOPKEY | | | | | |
        | 3 | NESTED LOOPS OUTER | | 6 | 1320 | 28 (0)| 00:00:01 |
        |* 4 | FILTER | | | | | |
        | 5 | NESTED LOOPS OUTER | | 5 | 700 | 13 (0)| 00:00:01 |
        |* 6 | TABLE ACCESS FULL | PXREFXTB | 5 | 140 | 3 (0)| 00:00:01 |
        | 7 | TABLE ACCESS BY INDEX ROWID | PROVDRTB | 1 | 112 | 2 (0)| 00:00:01 |
        |* 8 | INDEX UNIQUE SCAN | PROVDRX1 | 1 | | 1 (0)| 00:00:01 |
        | 9 | TABLE ACCESS BY INDEX ROWID BATCHED| PLADDRTB | 1 | 80 | 3 (0)| 00:00:01 |
        |* 10 | INDEX RANGE SCAN | PLADDRTB_IF1 | 1 | | 2 (0)| 00:00:01 |
        ——————————————————————————————————-

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

        2 – filter(ROWNUM
        SQL> select count(*)
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum SQL>
        SQL>

      2. Thanks Anju Garg!
        Yes I drop old stats and gather in 12c db.
        Please find explain plan as below,
        SQL> sho parameter optimizer_features_enable

        NAME TYPE VALUE
        ———————————— ———– ——————————
        optimizer_features_enable string 12.1.0.2
        SQL> alter session set optimizer_features_enable=’11.2.0.2′;

        Session altered.

        SQL> sho parameter optimizer_features_enable

        NAME TYPE VALUE
        ———————————— ———– ——————————
        optimizer_features_enable string 11.2.0.2
        SQL>
        SQL> set timi on
        SQL> explain plan for
        select A.P_ID, X.P_ALT_ID, A.P_SORT_NAM, A.P_NAM, A.P_TY_CD, A.P_DEA_NUM, A.P_BORDER_CD,
        A.P_APPL_DT, A.P_DBA_NAM, A.G_TEST_IND, B.G_ADR_LINE1_AD, B.G_ADR_CITY_NAM,
        B.G_ADR_STATE_CD, B.G_ADR_ZIP5_CD, B.G_ADR_PHONE_NUM, A.P_FED_TAX_ID, B.G_ADR_TY_CD,
        X.P_ALT_ID_CD, X.P_ALT_ID_TY_CD, A.G_AUD_TS MAIN_AUD_TS, A.G_AUD_USER_ID MAIN_USER_ID,
        2 B.G_AUD_TS ADDR_AUD_TS, B.G_AUD_USER_ID ADDR_USER_ID
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum select * from table(dbms_xplan.display);
        PLAN_TABLE_OUTPUT
        ——————————————————————————————————————————————————————————————————–
        Plan hash value: 802184744

        ———————————————————————————————–
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
        ———————————————————————————————–
        | 0 | SELECT STATEMENT | | 500 | 101K| 7115 (1)| 00:00:01 |
        |* 1 | COUNT STOPKEY | | | | | |
        | 2 | NESTED LOOPS OUTER | | 501 | 101K| 7115 (1)| 00:00:01 |
        | 3 | NESTED LOOPS | | 417 | 53376 | 5873 (1)| 00:00:01 |
        | 4 | TABLE ACCESS BY INDEX ROWID| PROVDRTB | 571K| 54M| 1322 (0)| 00:00:01 |
        |* 5 | INDEX FULL SCAN | PROVDRTB_IF3 | 1250 | | 91 (0)| 00:00:01 |
        |* 6 | TABLE ACCESS BY INDEX ROWID| PXREFXTB | 1 | 28 | 4 (0)| 00:00:01 |
        |* 7 | INDEX RANGE SCAN | PXREFXX2 | 2 | | 2 (0)| 00:00:01 |
        | 8 | TABLE ACCESS BY INDEX ROWID | PLADDRTB | 1 | 80 | 3 (0)| 00:00:01 |
        |* 9 | INDEX RANGE SCAN | PLADDRTB_IF1 | 1 | | 2 (0)| 00:00:01 |
        ———————————————————————————————–

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

        1 – filter(ROWNUM select count(*)
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum
        SQL> sho parameter optimizer_features_enable

        NAME TYPE VALUE
        ———————————— ———– ——————————
        optimizer_features_enable string 12.1.0.2
        SQL> explain plan for
        select A.P_ID, X.P_ALT_ID, A.P_SORT_NAM, A.P_NAM, A.P_TY_CD, A.P_DEA_NUM, A.P_BORDER_CD,
        A.P_APPL_DT, A.P_DBA_NAM, A.G_TEST_IND, B.G_ADR_LINE1_AD, B.G_ADR_CITY_NAM,
        B.G_ADR_STATE_CD, B.G_ADR_ZIP5_CD, B.G_ADR_PHONE_NUM, A.P_FED_TAX_ID, B.G_ADR_TY_CD,
        X.P_ALT_ID_CD, X.P_ALT_ID_TY_CD, A.G_AUD_TS MAIN_AUD_TS, A.G_AUD_USER_ID MAIN_USER_ID,
        B.G_AUD_TS ADDR_AUD_TS, B.G_AUD_USER_ID ADDR_USER_ID
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum select * from table(dbms_xplan.display);

        PLAN_TABLE_OUTPUT
        ——————————————————————————————————————————————————————————————————–
        Plan hash value: 3194642267

        ——————————————————————————————————-
        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
        ——————————————————————————————————-
        | 0 | SELECT STATEMENT | | 6 | 1320 | 29 (4)| 00:00:01 |
        | 1 | SORT ORDER BY | | 6 | 1320 | 29 (4)| 00:00:01 |
        |* 2 | COUNT STOPKEY | | | | | |
        | 3 | NESTED LOOPS OUTER | | 6 | 1320 | 28 (0)| 00:00:01 |
        |* 4 | FILTER | | | | | |
        | 5 | NESTED LOOPS OUTER | | 5 | 700 | 13 (0)| 00:00:01 |
        |* 6 | TABLE ACCESS FULL | PXREFXTB | 5 | 140 | 3 (0)| 00:00:01 |
        | 7 | TABLE ACCESS BY INDEX ROWID | PROVDRTB | 1 | 112 | 2 (0)| 00:00:01 |
        |* 8 | INDEX UNIQUE SCAN | PROVDRX1 | 1 | | 1 (0)| 00:00:01 |
        | 9 | TABLE ACCESS BY INDEX ROWID BATCHED| PLADDRTB | 1 | 80 | 3 (0)| 00:00:01 |
        |* 10 | INDEX RANGE SCAN | PLADDRTB_IF1 | 1 | | 2 (0)| 00:00:01 |
        ——————————————————————————————————-

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

        2 – filter(ROWNUM
        SQL> select count(*)
        from P_XXXX_TB X left outer join P_PRXX_TB A on X.P_ID = A.P_ID left outer join P_LOCN_ADR_TB B on X.P_ID = B.P_ID
        where A.P_SORT_NAM like ‘%Joh%’ and X.P_PROV_DESC_CD = ‘D’ and rownum SQL>
        SQL>

  32. Hi Anju,

    Could you please post an article on ACFS on LINUX. Though I read about it but still I am not fully sure about it’s behavior and have several question on it.
    It would be highly appreciable if you post on ACFS.

  33. I just read about Checkpoint on your blog. All teachers and authors must learn from you. Most people think they appear wise just because they complicate things. The shortest path between 2 points is a straight line. That is you. You don’t try to impress people but just convey the idea directly.
    My advice to you. Please write as many books as you can. Researchers, teachers and students will learn the art of knowing and being modest to pass it to others.
    I am serious. Please Write a book on many Oracle concepts, issues and even your professional experiences.
    I have around 12+ years Oracle experience.

    May God bless you.

    1. Thanks for your time and feedback. Currently, I am involved in writing articles for All Things Oracle. If Almighty wishes, I might write a book in future.
      Let HIM decide and guide.

      regards
      Anju

  34. Dear Anju Garg, could you please share a little bit infomation about usage of ASMFD in real production systems. Is it works as well as ASMLIB ? Did you faced any additional problems/bugs redarding ASMFD ? Can you suggest it for production systems in general ?
    Thank you and Best Regards.
    Sergo.

  35. hi sir,

    I want to configure rac 11g r2 on my laptop..host os is windows…i want to do it on vmware & linux with 2 node..please provide me step by doc..

  36. Hello Anju,

    Could you please share Oracle 11gr2 RAC setup installation with DNS Document.
    Include OS configuration, DNS IP which is public,private,scan IP,VIP in DNS & ASM installation. On my home Desktop DNS, Node1 Node2 on linux.

  37. Hi Mam,

    Amazing work. I would like to check if you are training for golden gate. If you are i am very much interested to join. Plz let me know

    Thanks

    1. Thanks Anish for your time and feedback. Unfortunately I do not provide training for Golden Gate. I provide training for various core DBA courses like RAC, Dataguard, Performance Tuning, RMAN, SQL Tuning etc.

      Regards
      Anju

  38. Hi Anju,

    Greetings, good day.

    I’m Unix admin, and i would like to learn oracle DB along with RAC. could you please guide me the steps how i can start learning oracle DB, could you please point me PPT for beginners.

    Thanks
    Veera

    1. Hi Veera,

      To learn Oracle DB, first you need to learn SQL, Database Administration workshop-I and Database Administration workshop-II. These three modules will teach you basic administration of Oracle Database. Subsequently, you can learn advanced courses like RAC, Performance Tuning, data Guard, SQL Tuning etc.

      Regards
      Anju

  39. Hi Mam,

    I have just started with my ORACLE 11g,
    as i know tht the bg process dbwr write the dirty blocks to the datafiles..
    and i read tht whtever changes we wanted to do in data so it will first update in buffer cache and from there it will update to datafiles directly..
    so wht is the use of redo log files wht is the action it tkes as it cums to redo logs..

    1. Hi Ravi,
      Whenever changes are made to the data
      – Before image of data is preserved in undo
      – Changes are recorded in redo log buffer
      – Buffers are dirtied in the buffer cache
      Datafiles may be updated by DBWR asynchronously later on only after redo buffers have been flushed to redo log files.

      Redo logs are required
      – During instance recovery to apply the changes which were committed by the user but had not been written to data files by database writer when the instance crashed.
      – To recover the datafile(s) after having been restored from backups

      Hope it helps

      Regards
      ANju

  40. Dear Anju,

    How does one do “NIC-Teaming on Windows for Oracle 11gR2 RAC”, the same as you would “NIC-Bonding on *NIX”?

    You advised on this on your website link:- “http://oracleinaction.com/nic-bonding/”.

    Thanks,
    Mike.

  41. Dear Anju,

    Hope my message finds you in best of your time and health.
    I have attended the OTNYatra 2016 in Gurgaon and wanted to extend my wishes to you for the excellent information sharing you did.
    I have though one question , you are an oracle trainer so do you take batches offline or is it limited to corporate training as is. I am a oracle developer by profession and looking for some guidance in performance tuning.

    Hope to hear from you soon.

    have a great day ahead!!!

    Regards
    Krishan

  42. Hi Anju Madam

    I am frequently visiting your blog it’s really awesome blog. I have query recording table defragmentation.

    alter table tab1 shrink space compact — just reorganizationg rows
    alter table tabl1 shirnk space — just reseting HWM

    in this case no index rebuild happended so do we need to rebuild index separately get benefit from clustering factor

    1. Hi BAsheer,

      Thanks for your time and feedback.
      Index rebuild will reduce the cost of accessing the index. It won’t affect the clustering factor. To improve the clustering factor of index, you need to reorganize data in the table in the order of index key. Please refer to my following article:
      http://allthingsoracle.com/defragmentation-can-degrade-query-performance/

      Hope it helps!
      Your comments and suggestions are always welcome.

      Regards
      Anju Garg

    1. Hi Saran,

      Oracle documentation is the best. I had followed Murali Vallath’s “Oracle 10g RAC: Grid, Services & Clustering” to learn RAC basics followed by oracle documentation.
      Referred to various blogs time to time.

      Regards
      Anju Garg

  43. Hi,

    We are trying to move our development database to Oracle12c PDB. We have a hybrid application where both .NET and Oracle Forms 6 exist. Migrations from Oracle Forms 6 are ongoing. Now, while .NET has no issues connecting to the new Oracle12c PDB, Oracle Forms 6 codes are asking for schema name prefix in every table/view object being accessed. Note that this is happening through the Oracle Forms development environment and not at runtime. Forms are connecting to PDB normally at runtime. Strange!!

    Is there any way I can connect Oracle Forms 6 to 12c PDB? I tried the following –

    1. TNSNAMES.ORA is correct where ServiceName=PDB name. (Oracle Forms runtime is connecting using same TNS)

    2. I have my CDB listener configured on 1525 and no other application (Toad/SQL Developer, .NET WinForms, ASP.NET) is having problem in connecting

    3. I configured an “AFTER LOGON” trigger in the PDB where I am running the following
    EXECUTE IMMEDIATE ‘ALTER SESSION SET CONTAINER = pdbname';
    EXECUTE IMMEDIATE ‘ALTER SESSION SET CURRENT_SCHEMA = userschema';

    Nothing is working. Any help is highly appreciated.

    Rajarshi

      1. Thanks for replying Anju.

        Yes I did check the privileges for the MAIN user (the schema name). We tried granting all privileges and connects to MAIN but still nothing worked.

        I also tried creating a different database (non-CDB) where I created a db link to the 12c PDB and created synonyms against the PDB objects, and then allowing Oracle Forms 6 to connect to the non-CDB instead of the PDB. Assuming that this might trick Forms in believing that it is working with a non-CDB only. But that also resulted in forms crashing. Toad/SQL Developer all are connecting seamlessly with the synonyms.

        The funniest part is the Forms 6 application runtime is connecting without any problem but the Forms Developer IDE is not compiling any form against the PDB.

        Running from pillar to post but no clue whatsoever anywhere on internet. I know Oracle Forms 6 is a dead product but have to live with that for at least another year more.

        Rajarshi

  44. Hi Anju,

    Thank you very much for all the detail you share here. I have learnt a lot while reading your blogs. I highly appreciate your support to new Oracle DBA like me’

    Can you please share the detail of Cluster startup sequence and at what steps OLR, GPNP profile, OCR, VD and other information read.

    Regards,
    Harpreet

      1. Thanks for reply. I have gone thru the blogs you mentioned above.. All are well explained.
        Can you please post on performance tuning of single instance and RAC.

        Thanks and Regards.
        Harpreet

  45. Hi Anju,
    How come the webinar or video for “Mastering Disaster Recovery with Oracle Data Guard and Oracle Database Server 12c” is not available? Does it require a registration?
    Thank you for your time.

    1. The number of channels available (whether allocated in a RUN block or configured in advance) for use with a device at the moment that you run a command determines whether RMAN will read from or write to that device in parallel while carrying out the command. Failing to allocate the right number of channels adversely affects RMAN performance during I/O operations.

      Hi Hemanth,

      Pls refer to following link:
      https://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmconc1.htm#i1006489

      Number of RMAN channels affect the degree of parallelism for a backup or restore command.
      As a rule, the number of channels used in carrying out an individual RMAN command should match the number of physical devices accessed in carrying out that command. If manually allocating channels for a command, allocate one for each device; if configuring automatic channels, configure the PARALLELISM setting appropriately.

      When backing up to tape, you should allocate one channel for each tape drive. When backing up to disk, allocate one channel for each physical disk, unless you can optimize the backup for your disk topography by using multiple disk channels. Each manually allocated channel uses a separate connection to the target or auxiliary database.

      Hope it helps.

      Your comments and suggestions are always welcome.

      Regards
      Anju Garg

Leave a Reply to Anju Garg Cancel reply