4 Replies Latest reply: Dec 19, 2012 10:24 AM by user2638923 RSS

    Oracle Restart turning on Active Data Guard

    user2638923
      Hello


      Version: 11.2.0.3, Enterprise Edition
      OS: RHEL6
      Setup:
      1x single instance primary
      1x single instance physical standby
      FSFO enable
      Mode = Max Availability



      I have setup a database with a physical standby as above. Being 11gR2 I have installed Grid Infrastructure first and added both databases into Oracle Restart. Also created a service which is started on either database becoming primary.

      However, I am finding the databases prone to opening in "READ ONLY WITH APPLY" which is Active Data Guard as far as I'm aware. We have not paid for this so I'd like to stop it happening before I move onto the live machines (currently in test).

      I think this is something to do with the Oracle Restart settings but cannot see exactly what I've done wrong. I added the database resources like this:
      $> srvctl add database -d prim1 -o /home/path -r PRIMARY -s OPEN
      $> srvctl add database -d stby1 -o /home/path -r PHYSICAL_STANDBY -s MOUNT

      If I switch over from prim1 to stby1 then the role part moves i.e.
      $> srvctl config database -d stby1
      on the standby host will show
      Database role: PRIMARY
      but the start option remains as mount (and likewise it remains as open on the old primary host):
      Start options: mount

      So an example:

      host1 is up and prim1 is runnning on it as primary. Restart knows the database is primary and has a desired start of "open". If I reboot host1 then the failover occurs successfully to host2 and stby1 database comes up as primary. The problem is, when host1 comes back up, prim1 is started as:

      DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
      ------------------------------ -------------------- ----------------
      prim1 READ ONLY WITH APPLY PHYSICAL STANDBY

      which I am thinking is due to Restart ostill having this: "Start options: open"

      This also happens if I have switched over first so it's not just a by product of an untidy reboot.


      This is probably a simple oversight on my part but I’m currently stumped by it. Any ideas welcome at this stage.



      thanks
      Mark
        • 1. Re: Oracle Restart turning on Active Data Guard
          mseberg
          Hello;

          I believe this :

          srvctl config database -d stby1

          Should be :

          srvctl modify database -d stby1 -s mount

          When the default start mode is set to mount, Active Data Guard will not be enabled when managed recovery is invoked.

          srvctl config database -d stby1

          Best Regards

          mseberg
          • 2. Re: Oracle Restart turning on Active Data Guard
            user2638923
            Hello mseberg


            Thanks for your quick reply.

            I believe I have done that already:

            $> srvctl add database -d stby1 -o /home/path -r PHYSICAL_STANDBY -s MOUNT

            or does the modify make a difference?

            I am using
            $> srvctl config database -d stby1
            to display the configuration values


            If "add" and "modify" are the same then the problem remains because prim1 database always opens in "READ ONLY WITH APPLY" and the standby only ever opens as far as "MOUNTED". If however I've misunderstood what you're advising then please let me know.

            What I'm really looking for is for the primary (whether it be prim1 or stby1) to open READ/WRITE and the standby (whether it be prim1 or stby1) to only go as far as MOUNTED. It seems like the Grid Infrastructure is gettinng some of the information (it knows when the role has changed because it starts up the service properly) but it doesn't affect the open mode.


            thanks
            Mark
            • 3. Re: Oracle Restart turning on Active Data Guard
              mseberg
              Mark;


              I would try the modify command.

              srvctl modify database -d stby1 -s mount

              Followed by this :

              srvctl config database -d stby1


              You could also specify the start mode as a parameter to the SRVCTL START DATABASE command :

              srvctl start database -d stby1 -o mount

              srvctl start database -d prim1 -o open

              Best Regards

              mseberg

              Edited by: mseberg on Dec 17, 2012 8:30 AM
              • 4. Re: Oracle Restart turning on Active Data Guard
                user2638923
                Hello mseberg

                Sorry for the delay in replying. I did some further testing and was about to reply when I decided to raise an SR with Oracle about this.

                It seems that this is a known issue (see support.oracle.com article [ID 1436313.1]). However it is not acknowledged as a bug and is in the list of enhancements i.e. may be changed in the next release.

                From the article:
                "In 11g the Broker does not change the Startmode in Oracle Restart as expected because the Broker does not care for the Status of the Database any more."

                The article then suggests using a script (outline example given) to check the value for the start option and amend it if the database had failed/switched over. The advice in the SR was to manually change the start option following/before a switchover.

                Neither of these (it seems to me) offers a) a solution for a failover (which can be automated to happen without DBA intervention) or b) a proper switchover method. The fact that the Grid Control switchover command needs to be followed/preceeded by running a script or logging onto the database host to use SRVCTL seems to me an imcomplete solution.

                We will discuss it further with Oracle. I wanted to post back my findings as you spent some time on my problem - thanks again for that. If I receive an update I will let you know. Until then I will leave the question marked as unanswered because I have not finished the SR yet.


                thanks
                Mark