9 Replies Latest reply: Jun 27, 2012 12:12 PM by user548445 RSS

    failed rolling upgrade

    user548445
      Hello,

      I have a dataguard configuration with one primary and one physical standby (no RAC) on a different server. I was attempting to do a rolling upgrade with a recent PSU patch and after converting the physical standby to a logical standby we could not get the SQL Apply to start. We were receiving the message "SQL APPLY NOT ON" from teh v$logstdby_state view. We found the issue to be that there were some enabled primary key constraints on some LOGMNR tables that were supposed to be disabled. Fixing this on the primary was no problem, but how do we get these changes to the standby since SQL APPLY is not working? Also how can one get a logical standby back to a physical standby if one wishes to abort the upgrade or start over?

      What I ended up doing was dropping the logical standby database and creating a new physical standby and starting over from there. I'm hoping there was an easier way someone could suggest in case I come across this in the future. I'm new to dataguard.

      Thanks in advance for your time and your help.

      Regards,
      Nicole

      Edited by: user548445 on Jun 9, 2012 12:04 PM
        • 1. Re: failed rolling upgrade
          mseberg
          Hello;

          For the most part when using a PSU patch, I would avoid this problem. You question is "suggest in case I come across this in the future". So what I'm saying is this :

          "Don't use a rolling upgrade for a patch this small" What I would do is this :

          1. Disable log shipping from the Primary
          2. Shutdown Standby
          3. Install PSU on Standby - Just the software
          4. Startup Standby in recovery mode - do NOT run any SQL at the standby
          5. Shutdown Primary
          6. Install PSU on Primary
          7. Run Upgrade SQL at Primary
          8. Re-enable log shipping
          9. Monitor the redo apply from Primary to Standby - this will also upgrade the Standby data dictionary


          So in my humble opinion the rolling upgrade is more trouble than its worth. Save these steps for next time and test for yourself.

          Best Regards

          mseberg
          • 2. Re: failed rolling upgrade
            user548445
            I would love to avoid the rolling upgrade but my client wants no more than 5 minutes of downtime. We have done the upgrade before as you suggest and it is much easier and entirely less complicated.
            • 3. Re: failed rolling upgrade
              mseberg
              Understood.

              Here's a source I trust on the subject :

              http://gavinsoorma.com/2010/03/11g-release-2-rolling-upgrade-using-transient-logical-standby-database/

              Not a the same type of patch, but I believe many of the steps are the same.

              Tough client.

              Best Regards

              mseberg
              • 4. Re: failed rolling upgrade
                CKPT
                I have a dataguard configuration with one primary and one physical standby (no RAC) on a different server. I was attempting to do a rolling upgrade with a recent PSU patch and after converting the physical standby to a logical standby we could not get the SQL Apply to start. We were receiving the message "SQL APPLY NOT ON" from teh v$logstdby_state view. We found the issue to be that there were some enabled primary key constraints on some LOGMNR tables that were supposed to be disabled. Fixing this on the primary was no problem, but how do we get these changes to the standby since SQL APPLY is not working? Also how can one get a logical standby back to a physical standby if one wishes to abort the upgrade or start over?
                Some confusion over here, You want to convert to logical standby after applying PSU?
                Then how you were receiving "SQL APPLY NOT ON" ? Please post clearly. may be am confused?
                What I ended up doing was dropping the logical standby database and creating a new physical standby and starting over from there. I'm hoping there was an easier way someone could suggest in case I come across this in the future. I'm new to dataguard.
                I have some questions here

                1) Have you applied PSU patch?
                2) Is your logical standby is functioning?
                -- if not what are the errors?
                3) If you decided to decommission it, then Consider building new physical standby database, Apply PSU patch, then convert it to Logical standby

                Please review how to Apply CPU patch on Dataguard configuration, of-course it is applicable even for PSU patch also.
                http://www.oracle-ckpt.com/applying-cpu-patch-on-dataguard-physical-standby-configuration-11gr2/
                How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]

                Upgrading Oracle Database with a Logical Standby Database In Place [ID 437276.1]

                Converting Physical standby to logical standby:-
                http://avdeo.com/2010/05/04/converting-physical-standby-to-logical-oracle-dataguard-10g/

                HTH....

                Forgot to add one more point on roll upgrade patching, yes you can perform with minimal downtime..
                You can check readme.html which is very much detailed and that document even applicable for RAC & non-RAC too.

                Edited by: CKPT on Jun 12, 2012 3:40 PM
                • 5. Re: failed rolling upgrade
                  user548445
                  Let me try to clear up the confusion.... My goal was to do a rolling upgrade for the latest PSU patch. As one of the earliest steps in the rolling upgrade process I converted my physical standby database to a logical standby (with the keep identity clause). The next step was to start the sql apply. This is the step that failed. Therefore I could not continue with the rolling upgrade. To fix my problem I wanted to get the logical standby back to a physical standby but am not sure if there is any way to do that. So in the end I built a new physical standby. So my question is there a way to return to a physical standby if you've converted to a logical standby?
                  • 6. Re: failed rolling upgrade
                    mseberg
                    Hello;

                    So before you convert to a logical you have to create a restore point on the Standby database and make sure you have enough FRA to use flashback database to that point.

                    Once you flashback database to that restore point.

                    You shutdown

                    shutdown immediate;

                    startup mount;

                    alter database convert to physical standby;

                    Shutdown again, startup mount and restart the apply.

                    The restore point is the Key.


                    create restore point pre_upgrade_A guarantee flashback database;



                    Best Regards

                    mseberg
                    • 7. Re: failed rolling upgrade
                      CKPT
                      user548445 wrote:
                      Let me try to clear up the confusion.... My goal was to do a rolling upgrade for the latest PSU patch. As one of the earliest steps in the rolling upgrade process I converted my physical standby database to a logical standby (with the keep identity clause). The next step was to start the sql apply. This is the step that failed. Therefore I could not continue with the rolling upgrade. To fix my problem I wanted to get the logical standby back to a physical standby but am not sure if there is any way to do that. So in the end I built a new physical standby. So my question is there a way to return to a physical standby if you've converted to a logical standby?
                      So you have not applied any PSU rolling upgrade yet because of issues after converting your physical standby to logical standby.
                      You mentioned "The next step was to start the sql apply. This is the step that failed" --> Have you mentioned here what is the error?
                      May be it can be resolved, Then no need to convert it back to physical standby.

                      You can follow the procedure what Mseberg mentioned if you want to switch the role of database.


                      Once you fix this issue, then you can move on to apply Patch on Logical standby.
                      Refer this note to apply Upgrading Oracle Database with a Logical Standby Database In Place [ID 437276.1]
                      • 8. Re: failed rolling upgrade
                        user548445
                        mseberg,

                        Thank you for your post, it is the most helpful so far! We created a restore point on the primary before starting the upgrade process but did not create one on the physical standby. I believe that is an optional step, but now we will be sure to do that.

                        I tested out your solution but had many difficulties along the way. I created the restore point on the physical standby, I converted it to a logical standby, I started the sql apply, I flashed back the database, I converted it back to a physical standby, I bounced the database, and then I started the apply. Everything appeared fine, until I checked my dataguard broker configuration. it told me that there was a lag. I checked the archive logs and they were not getting applied. I checked that they were all getting shipped and they were. I tried "alter database recover managed standby database using current logfile disconnect from session;", still they were not getting applied.

                        Then I found error ORA-19906 in the alert log:

                        oerr ora 19906
                        19906, 00000, "recovery target incarnation changed during recovery"
                        // *Cause: While a media recovery was active, a new incarnation was detected
                        // by the server due to inspection or cataloging of archived logs
                        // or backup files.
                        // *Action: If you want recovery to use the new incarnation, restart recovery.
                        // This is the most common action on a standby database when RESETLOGS
                        // is done in primary. If you do not want recovery to use the new
                        // incarnation,
                        // change the recovery destination using RMAN's RESET DATABASE
                        // TO INCARNATION <incarnation#> command.
                        // To see which incarnations are available for this target database,
                        // query V$DATABASE_INCARNATION or use RMAN's LIST INCARNATION
                        // command.

                        I checked the primary and found it was incarnation 12 where as the standby was incarnation 13. I ran the rman command to reset the incarnation. Checked the standby again and found it to be on the right incarnation but still the logs were not getting applied. Then I got an error about the control file not being a standby control file. I fixed that, still no apply. Also at some point I tried alter database open reset logs. Tried "alter database recover managed standby database using current logfile disconnect from session;" once more and finally success.

                        Did I go wrong somewhere? I didn't stop the SQL Apply before the flashback. do you think that could have caused the problem? If all of these steps are necessary it's probably faster to recreate the standby.

                        Nicole
                        • 9. Re: failed rolling upgrade
                          user548445
                          The error had to do with some primary key constraints on some logmnr tables being enabled when they should have been disabled. I could correct the problem on the primary but since sql apply was not working I could not get those constraints enabled on the logical standby. That is why I needed to go back to a physical standby.