4 Replies Latest reply: Dec 12, 2012 11:14 AM by CKPT RSS

    Data-guard conceptual help needed

    user4566776
      Hello Experts,

      We have 2-node RAC Physical standby DG. I got the following doubts in my mind just to clear my concept:-

      1. Can we perform Hang analyze on the DG site, when it is on managed recovery and of-course in read-only mode? if not then Q2?

      2. If both of my DG nodes (in a 2-node RAC DG) are in a hung state while the managed recovery was active, can I do sqlplus sql> set _prelim on and enable oradebug hanganalyze or do I have any other option apart from restarting the DB

      3. IF the Primary RAC (2-node) hangs then if we take systemstate dump / Hanganalyze from the DG site
      I guess, we will not get meaningful results as Hanganalyze will use Kernel system calls of the Server on the DG site and systemstatsdump will dump the SGA of the secondary site instance's SGA

      4. I am using a non-RAC primary and Secondary site and non-ASM disk
      I have my Primary mount -points as /Oradata/Prim1 = 1 TB and Physical standby DG - real time apply mount-point as /Oradata/Seco1 =.8 TB & /Oradata/Seco2 = .2TB and my Primary DB is now on size expansion from .79 TB to .95 TB overnight. I am not intending not to take any down-time nor am I able to extend the disks on the Primary for any reason. what can be done wisely to keep the DR site in close sync?

      Regards,

      Syvalli

      Edited by: user4566776 on Dec 12, 2012 6:04 AM
        • 1. Re: Data-guard conceptual help needed
          CKPT
          We have 2-node RAC Physical standby DG. I got the following doubts in my mind just to clear my concept:-

          1. Can we perform Hang analyze on the DG site, when it is on managed recovery and of-course in read-only mode? if not then Q2?
          2. If both of my DG nodes (in a 2-node RAC DG) are in a hung state while the managed recovery was active, can I do sqlplus sql> set _prelim on and enable oradebug hanganalyze or do I have any other option apart from restarting the DB
          Yes you can perform hang analyze and even you can generate active session history report if DR is on read-only mode.
          SQL> select database_role from v$database;
          
          DATABASE_ROLE
          ----------------
          PHYSICAL STANDBY
          
          SQL> 
          
          SQL> oradebug setmypid;
          Statement processed.
          SQL> oradebug unlimit;
          Statement processed.
          SQL> 
          
          SQL> oradebug hanganalyze 3;
          Hang Analysis in /u02/app/oracle/diag/rdbms/CKPT_un/CKPT/trace/CKPT_ora_5113.trc
          SQL> 
          SQL> oradebug dump systemstate 266;
          Statement processed.
          SQL> 
          
          [oracle@oracle-stby ~]$ tail -10 /u02/app/oracle/diag/rdbms/CKPT_un/CKPT/trace/CKPT_ora_5113.trc
            Holder:
            ----------------------------------------
            SO: 0x8ca2c8b8, type: 83, owner: 0x6000c478, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
             proc=0x90488f50, name=circuit holder, file=kmc.h LINE:2615, pg=0
            (circuit holder) disp = 0x8ca2c830 (0, 1), proc = (0x90488f50, 1)
          END DISPATCHER DUMPS
          END OF SYSTEM STATE
          
          *** 2012-12-12 20:03:44.100
          Oradebug command 'dump systemstate 266' console output: <none>
          [oracle@oracle-stby ~]$ 
          3. IF the Primary RAC (2-node) hangs then if we take systemstate dump / Hanganalyze from the DG site
          I guess, we will not get meaningful results as Hanganalyze will use Kernel system calls of the Server on the DG site and systemstatsdump will dump the SGA of the secondary site instance's SGA
          Yes, the hang analysis we should perform from the instance where you have problem. you can use OS level trace if database not accessible by using How to interpret OS system traces [ID 1429678.1]
          4. I am using a non-RAC primary and Secondary site and non-ASM disk
          I have my Primary mount -points as /Oradata/Prim1 = 1 TB and Physical standby DG - real time apply mount-point as /Oradata/Seco1 =.8 TB & /Oradata/Seco2 = .2TB and my Primary DB is now on size expansion from .79 TB to .95 TB overnight. I am not intending not to take any down-time nor am I able to extend the disks on the Primary for any reason. what can be done wisely to keep the DR site in close sync?
          Can you tell me one thing? After size expanded in primary from .79 TB to .95 TB does your standby is out of SYNC?
          If you do not have space, then you should add some space to the disk to avoid any interruption to production. Or check whether you can adjust HWM on primary database using below method. http://www.oracle-ckpt.com/how-to-shrink-datafiles-adjusting-hwm/

          Hope this helps
          • 2. Re: Data-guard conceptual help needed
            user4566776
            Hello Ckpt,

            Thanks for answering my first 3 -questions.
            well,
            in Q4) There is no space crunch on Prod not even on standby but in Prod 1 TB of space is available in a single disk -/oradata/Prim1 whereas in standby site I have the 1 TB split into 2 disks /oradata/seco1 and /oradata/seco2.
            The idea is to use the available space and engage DG features / concepts to overcome this constraint.
            • 3. Re: Data-guard conceptual help needed
              mseberg
              Hello;

              Can you explain exactly what you mean by :

              what can be done wisely to keep the DR site in close sync?

              I for one don't understand if you have a lag or are concerned about pressure on your disk space.

              Best Regards

              mseberg
              • 4. Re: Data-guard conceptual help needed
                CKPT
                user4566776 wrote:
                Hello Ckpt,

                Thanks for answering my first 3 -questions.
                well,
                in Q4) There is no space crunch on Prod not even on standby but in Prod 1 TB of space is available in a single disk -/oradata/Prim1 whereas in standby site I have the 1 TB split into 2 disks /oradata/seco1 and /oradata/seco2.
                The idea is to use the available space and engage DG features / concepts to overcome this constraint.
                If you have two mount points on standby, Then move some of the datafiles from one mount point to another mount point.
                Or you can put standby file management to Manual and manage the datafies every time whenever you add them, it is difficult to manage.You can refer the link to create datafile on standby if any datafile added in primary whenever there is space not exist. http://www.oracle-ckpt.com/unnamed-file-in-standby-after-adding-new-file-to-primary-ora-01111-ora-01110-ora-01157/

                But i suggest you to move datafiles from one mount point to another mount point then you can manage standby datfiles automatically by below command after copying datafile from OS.
                SQL> alter database rename file 'location' to 'location;

                Hope this helps.