1 2 Previous Next 17 Replies Latest reply on Feb 29, 2012 4:05 AM by Shivananda Rao

    Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade

    ray_h
      Hello.

      I'm trying to upgrade a primary and physical standby database from 11.2.0.1 to 11.2.0.2 on Linux (64 bit). The databases are on seperate, but identical (in terms of their config) servers. Data Guard is facilitating the synch.

      I should add, that I've deviated slightly from the recommended way of upgrading with this configuration (http://docs.oracle.com/cd/E11882_01/server.112/e25608/upgrades.htm), and instead have decided to upgrade the primary first, and make sure it works before synching to the standby. Not my call, but the rationale for this is that it was deemed too risky, so it seemed sensible at the time.

      BVCLONE = primary
      BVCLONBY = physical standby

      The upgraded primary database works fine.
      I then installed the db software in a new ORACLE_HOME on the standby server.
      Then mounted the physical standby
      Then started redo apply on the physical standby:
      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
      I then made sure that DG was happy:
      DGMGRL> show database bvclonby
      
      Database - bvclonby
      
        Role:            PHYSICAL STANDBY
        Intended State:  APPLY-ON
        Transport Lag:   0 seconds
        Apply Lag:       0 seconds
        Real Time Query: OFF
        Instance(s):
          bvclonby
      
      Database Status:
      SUCCESS
      But now I can't open the standby database:
      SQL> alter database open read only;
      alter database open read only
      *
      ERROR at line 1:
      ORA-01092: ORACLE instance terminated. Disconnection forced
      ORA-00704: bootstrap process failure
      ORA-39700: database must be opened with UPGRADE option
      Process ID: 20247
      Session ID: 11 Serial number: 3
      Now, that error message doesn't make sense as it implies running the upgrade scripts on the standby.

      and in any case, if I do try and startup in upgrade mode, I get this:
      SQL> startup upgrade
      ORACLE instance started.
      
      Total System Global Area 4175568896 bytes
      Fixed Size            2233088 bytes
      Variable Size         1962937600 bytes
      Database Buffers     2181038080 bytes
      Redo Buffers           29360128 bytes
      Database mounted.
      ORA-16003: standby database is restricted to read-only access
      which I'd expect to see.

      I've checked that both databases have the same version number: 11.2.0.2:

      standby:
      SQL> show parameter db_unique_name
      
      NAME                         TYPE      VALUE
      ------------------------------------ ----------- ------------------------------
      db_unique_name                    string      BVCLONBY
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE     11.2.0.2.0     Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      Primary:
      SQL> show parameter db_unique_name
      
      NAME                         TYPE      VALUE
      ------------------------------------ ----------- ------------------------------
      db_unique_name                    string      BVCLONE
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE     11.2.0.2.0     Production
      TNS for Linux: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
      It seems like a no-win scenario. On one hand, you can only open in upgrade mode, on the other, you can only open in read only mode! Of course, I've considered (and am looking into) the possibility that Oracle's error messages are pretty misleading and the problem could be something else completely.

      But if anyone has any ideas about what's going on here, I'd be very grateful of you could let me know.

      Thanks,
      Ray
        • 1. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
          mseberg
          Hello;

          The first thing that jumps out is you are mixing SQL and Data Broker. Once you start using broker you cannot do that.

          Keep in mind broker will try to start your Standby so even if your path is OK, Broker may not be. So you SQL showing the versions is not apples to apples ( it may be, but based on your errors I'm thinking No. )


          Let finish reading your post and I will comment more here.

          Not a good idea on the Standby (the Standby Upgrade happens from REDO, not like this )
          startup upgrade
          Please shutdown the Standby database. ( and make sure all profiles point to the new Oracle home for the user owning the Oracle software )

          Pretty sure this is it

          What I think happened is you started the Standby on the OLD Oracle home.

          ( For example on mine Linux, I had to update the bash profile )

          We can fix this. ( Otherwise oracle would not think it needed an Upgrade )


          After you shutdown the Standby you should do the following :
          1. Make sure your NEW path to the upgraded Oracle home is set on the Standby.
          2. Make sure a password file is in the new ORACLE_HOME dbs folder.
          3. Copy you last spfile for the Standby to the new ORACLE_HOME dbs folder

          I also copied the listener.ora and tnsnames.ora to the new Oracle home network location. And I restarted the listener on the Standby server.




          Mostly all you have to do on the Standby server is install the new software. Once the redo starts shipping the rest will take care of itself.

          If you did not disable broker before the upgrade then I would consider this:

          How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

          And then recreate after you resolve the upgrade issue.


          Best Regards

          mseberg

          Edited by: mseberg on Feb 22, 2012 1:43 PM

          Overview of Patching on Data Guard

          Disable log shipping from the Primary
          Shutdown Standby
          Install 11.2.0.2 on Standby
          Startup Standby in recovery mode (do NOT run catpatch.sql at the standby)
          Shutdown Primary
          Install 11.2.0.2 on Primary
          Run catpatch.sql on Primary
          Re-enable log shipping
          Monitor the redo apply from Primary to Standby --- this will also upgrade the Standby data dictionary

          Later

          Also check this note out. While the patch is smaller it shows DGMGRL commands you need to use when patching.


          How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]

          Edited by: mseberg on Feb 22, 2012 1:53 PM
          • 2. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
            Shivananda Rao
            Did you copy the spfile/pfile of the standby database to new ORACLE_HOME and mounted the standby database with that ?

            I suspect your standby database is using the spfile of old ORACLE_HOME

            Refer this http://docs.oracle.com/cd/E11882_01/server.112/e17023/upgrade_appx.htm#CEGIABCH
            • 3. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
              CKPT
              Ray,
              ORA-39700: database must be opened with UPGRADE option
              As Mseberg & Shivanand mentioned first check Have you started your database on new ORACLE_HOME(11.2.0.2) ?

              I suggest you to do the following

              Disable Broker as of now on both ends.

              Connect to Primary:-
              Check that Catupgrd.sql executed successfully?
              either from
              $opatch lsinventory
              SQL> select * from dba_registry;

              If not again rerun catupgrd.sql from upgrade mode.(log_archive_dest_state_2=defer)
              Later put database in open status and perform couple of log switches. (log_archive_dest_state_2=enable)

              From Standby:-

              post
              $echo $ORACLE_HOME
              $echo $PATH

              1) Once Primary upgraded successfully, after installation of Standby home(11.2.0.2) Make sure you have changed in your .bash_profile & env
              2) Later put database in mount status.
              3) Start MRP.

              Now check alert log files for any errors.

              Because you run any upgrade script it consists of SQL's(DDL/DML/...) , those will be in form of archives will be applied on stanbdy.
              So one your standby is in SYNC with primary. Then try to open in Open read only mode.

              Post your findings.
              • 4. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                ray_h
                First of all - thanks all for replying.

                I'd already copied the password file from the old ORACLE_HOME to the new one on both servers.
                I'd also copied the pfile from the old ORACLE_HOME to the new one, and created an spfile from it, again, on both servers.
                All my environment settings are correct:

                From the Primary server:
                oracle@stage-ora1b:/home/oracle> echo $ORACLE_HOME
                /u01/app/database/product/11.2.0.2/db1
                oracle@stage-ora1b:/home/oracle> echo $PATH
                /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/u01/app/database/product/11.2.0.2/db1/bin:/opt/dell/srvadmin/bin:/home/oracle/bin
                From the Standby server:
                oracle@stage-ora2b:/u01/app/database/product/11.2.0.2/db1/dbs> echo $ORACLE_HOME
                /u01/app/database/product/11.2.0.2/db1
                oracle@stage-ora2b:/u01/app/database/product/11.2.0.2/db1/dbs> echo $PATH
                /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/u01/app/database/product/11.2.0.2/db1/bin:/opt/dell/srvadmin/bin:/home/oracle/bin
                After running SELECT * FROM DBA_REGISTRY on Primary, I could see that a load of invalid objects. So I re-ran catupgrd,sql. Post-upgrade looked a lot better:
                SQL> select comp_id, status from dba_registry;  
                
                COMP_ID                  STATUS
                ------------------------------ -----------
                CATALOG                  VALID
                CATPROC                  VALID
                JAVAVM                      VALID
                CATJAVA                  VALID
                
                SQL> alter system set log_archive_dest_state_2 = 'ENABLE';
                
                System altered.
                
                SQL> alter system archive log current;
                
                System altered.
                
                SQL> alter system archive log current;
                
                System altered.
                
                SQL> exit
                I then mounted the standby database:
                SQL> startup nomount;
                ORACLE instance started.
                
                Total System Global Area 4175568896 bytes
                Fixed Size              2233088 bytes
                Variable Size           1962937600 bytes
                Database Buffers      2181038080 bytes
                Redo Buffers             29360128 bytes
                SQL> alter database mount standby database;
                
                Database altered.
                It then failed when I tried to start MRP:
                SQL> recover managed standby database disconnect from session;
                ORA-01153: an incompatible media recovery is active
                I then noticed that the DG broker was enabled on the standby (it wasn't on Primary), so I disabled this and tried again, with the same result:
                SQL> show parameter dg
                
                NAME                         TYPE      VALUE
                ------------------------------------ ----------- ------------------------------
                dg_broker_config_file1               string      /u01/app/database/product/11.2
                                               .0.2/db1/dbs/dr1bvclonby_DG1.d
                                               at
                dg_broker_config_file2               string      /u01/app/database/product/11.2
                                               .0.2/db1/dbs/dr2bvclonby_DG2.d
                                               at
                dg_broker_start                boolean      TRUE
                SQL> alter system set dg_broker_start = false;
                
                System altered.
                
                SQL> alter database recover managed standby database disconnect;
                alter database recover managed standby database disconnect
                *
                ERROR at line 1:
                ORA-01153: an incompatible media recovery is active
                I've seen a few threads on this, suggesting I restart the database. I'm not so sure that will help. Does anyone have any thoughts?
                • 5. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                  CKPT
                  Hi,

                  Once MRP is started, even if you disable broker the MRP is already running.

                  do as follows:-


                  SQL> alter database recover managed standby database cancel;
                  alter database recover managed standby database disconnect from session;

                  Once it SYNC, then check the registry in standby after opening in read only mode.
                  • 6. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                    mseberg
                    Ray;

                    I don't understand the SQL with Broker :
                    SQL> recover managed standby database disconnect from session;
                    ORA-01153: an incompatible media recovery is active
                    Once you start using Broker you must always use Broker. If you use SQL, broker will put things back the way it see it.

                    By mixing Broker and SQL you are making this harder to fix and its still fixable.

                    How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

                    Best Regards

                    mseberg
                    • 7. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                      ray_h
                      SQL> alter database recover managed standby database cancel;
                      alter database recover managed standby database cancel
                      *
                      ERROR at line 1:
                      ORA-16136: Managed Standby Recovery not active
                      According to this I don't need to do anything...is there anything else I can try?
                      • 8. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                        mseberg
                        Ray;

                        If you are 100 percent sure of this and that your Linux profiles are pointing to the correct Oracle home then start recovery on the Standby.

                        I assume the listener is up on the Standby.

                        Any post any error that might occur when you try it.

                        I would still do this as it has been nothing but trouble for you so far.

                        How to Safely Remove a Data Guard Broker Configuration [ID 261336.1]

                        Best Regards

                        mseberg
                        1 person found this helpful
                        • 9. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                          CKPT
                          ray_h wrote:
                          SQL> alter database recover managed standby database cancel;
                          alter database recover managed standby database cancel
                          *
                          ERROR at line 1:
                          ORA-16136: Managed Standby Recovery not active
                          According to this I don't need to do anything...is there anything else I can try?
                          First make sure you disabled Broker on both sites. refer Mseberg mentioned note to disable
                          Now
                          check for the active processes

                          $ps -ef|grep mrp
                          $ps -ef|grep dmon

                          Kill if any one are running, then start MRP again.
                          • 10. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                            ray_h
                            Hello.

                            We actually noticed an ORA-00600 when attempting a log file switch. It appears there is a fix for this in patch 11724916 (ironically, this patch is the reason we are upgrading in the first place, to fix a seperate issue).

                            Unfortunately, the readme for this patch says that applying this patch after the database has started after a re-run of the upgrade can also cause further ORA-00600's, which we've seen (see readme for bug 10373381).

                            So, we're going to:

                            - Revert back to 11.2.0 on the primary
                            - Re-apply the upgrade on the primary without starting it
                            - Re-apply patch 11724916 to the primary
                            - Rebuild the standby from scratch

                            It is a little extreme but I don't see a better way than this. I'll update this thread with where we are in case anyone else has a related problem.

                            Edited by: ray_h on 24-Feb-2012 05:35
                            • 11. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                              Seberg
                              You should get Oracle professional services to help you.
                              • 12. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                                CKPT
                                Cooper wrote:
                                You should get Oracle professional services to help you.
                                You mean to say Oracle support help
                                or
                                OP should not get help from OTN who are professional? Please clarify
                                • 13. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                                  Seberg
                                  This is what I mean :

                                  http://www.oracle.com/partners/en/knowledge-zone/industries/professional-services-029959.htm

                                  Reading this thread I see several issues that need to be resolved.
                                  • 14. Re: Standby won't open after 11.2.0.1 to 11.2.0.2 upgrade
                                    ray_h
                                    I've already created an SR with MOS. They've not covered themselves in glory so far, and seem most interested in closing out open tickets, regardless of whether the problem is fixed or not.

                                    In a lot of cases I've found forums like these to be much better than MOS, primarily as you're dealing with people who've often dealt with issues like these first-hand.
                                    1 2 Previous Next