Time for action – performing a rolling upgrade using the transient logical standby database

We will now see a step-by-step approach to upgrade a database from 11.2.0.1 to 11.2.0.3.

  1. Ensuring protection mode and compatibility: Ensure the protection mode is in either maximum performance or maximum availability.
    SQL> select * from v$version;
BANNER
-----------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select protection_mode from v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

    The COMPATIBLE initialization parameter should be same as the software release version. Once we upgrade to the new release and after all the post checks, we can change the compatible parameter value.

    SQL> show parameter compatible
NAME              TYPE     VALUE
----------------- -------- -------------
compatible        string   11.2.0.0.0
  2. Disabling the Data Guard broker: If the database is managed with the Data Guard broker, disable it; we can enable it after the successful upgrade of the database.
    SQL> show parameter dg_broker_start
NAME               TYPE      VALUE
------------------ --------  -------
dg_broker_start    boolean   FALSE
  3. Enabling flashback in the primary and standby databases: Now check flashback database status and then enable it on both primary and standby databases:
    SQL> select db_unique_name,flashback_on from v$database;
DB_UNIQUE_NAME  FLASHBACK_ON
--------------- ------------------
INDIA_UN        NO
SQL> alter database flashback on;
Database altered.
SQL> select db_unique_name, flashback_on from v$database;
DB_UNIQUE_NAME  FLASHBACK_ON
--------------- ------------------
turkey_un       YES

    On the alert log, you will get the following:

    Sun Dec 30 21:42:53 2012
alter database flashback on
Starting background process RVWR
Sun Dec 30 21:42:57 2012
RVWR started with pid=20, OS id=21651
Sun Dec 30 21:43:18 2012
Allocated 3981120 bytes in shared pool for flashback generation buffer
Sun Dec 30 21:43:33 2012
Flashback Database Enabled at SCN 955828
Completed: alter database flashback on

    Tip

    In 11gR2, we no longer need to restart database to the mount state in order to enable or disable flashback on a primary database. Therefore, we can enable/disable flashback when the database is in the read-write mode. However, we can't enable or disable flashback on a standby database when MRP is running (ORA-01153: an incompatible media recovery is active). In order to perform this on the standby database, we must stop Redo Apply.

  4. Creating a guaranteed restore point on the primary and standby databases: Create a guaranteed restore point on both the primary and standby databases. We may need to flash back the database to this point in case of any failures during the upgrade.
    SQL> create restore point Rolling_Upgrade_Turkey guarantee flashback database;
Restore point created.
SQL> select name,guarantee_flashback_database,scn from v$restore_point;
NAME                           GUA        SCN
------------------------------ --- ----------
ROLLING_UPGRADE_TURKEY         YES     972018

    On the alert log, you will get the following:

    Sun Dec 30 22:09:19 2012
Created guaranteed restore point ROLLING_UPGRADE_TURKEY

    Now create a guaranteed restore point on the standby database. In order to create a restore point, we must cancel the recovery.

    SQL> alter database recover managed standby database cancel;
Database altered.
SQL> create restore point Rolling_Upgrade_India guarantee flashback database;
Restore point created.
  5. Creating a log miner dictionary on a primary database: This package enables supplemental logging on the primary database, which ensures that the updates contain enough information to identify each modified row that is needed for a logical standby configuration.
    SQL> execute dbms_logstdby.build;
PL/SQL procedure successfully completed.

    In the alert log, add the following:

    SUPLOG:  unique = ON, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Completed: alter database add supplemental log data (primary key, unique index) columns
alter database add supplemental log data for procedural replication
SUPLOG: Previous supplemental logging attributes at scn = 998811
  6. Converting the physical standby database into a logical standby database: Now convert the physical standby database into a logical standby database with the KEEP IDENTITY clause so that the database name and DBID remain the same as those in the primary database.
    SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount exclusive;
Database mounted.
SQL> alter database recover to logical standby keep identity;
Database altered.

    On the alert log, you will get the following:

    Online log /u01/app/oracle/oradata/orcl/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1003598
Mon Dec 31 00:49:04 2012
Setting recovery target incarnation to 3
RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
Completed: alter database recover to logical standby keep identity
  7. After completing the conversion, open the database and check for the new database role.
    SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
  8. Starting SQL Apply and monitoring the apply status: On the new logical standby database, issue the following command to start SQL Apply:
    SQL> alter database start logical standby apply immediate;
Database altered.

    On the alert log, you will get the following:

    Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_DICTIONARY$ have been marked unusable
Indexes of table  SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME='transport lag';
NAME          VALUE           TIME_COMPUTED
------------- --------------- ------------------------------
transport lag +00 00:00:00    12/31/2012 00:59:25

    If any active DDLs/DMLs are in progress, you can monitor them using v$logstdby_state.

    SQL> select state from v$logstdby_state;
STATE
-------------
APPLYING
SQL> /
STATE
-------------
IDLE
  9. Upgrading a logical standby database: Stop sending redo on the primary database by changing the remote destination status to defer.
    SQL> alter system set log_archive_dest_state_2='defer';
System altered.
SQL> select dest_id,status from v$archive_dest where dest_id=2;
        DEST_ID STATUS
--------------- ---------
              2 DEFERRED

    Stop SQL Apply from the logical standby database.

    SQL> alter database stop logical standby apply;
Database altered.

    On the alert log, you will get the following:

    Mon Dec 31 01:06:16 2012
LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed
Completed: alter database stop logical standby apply
  10. Create another restore point prior to the upgrade.
    SQL> create restore point Rolling_Upgrade_India2 guarantee flashback database;
Restore point created.
SQL> select name from v$restore_point;
NAME
-----------------------------------
ROLLING_UPGRADE_INDIA
ROLLING_UPGRADE_INDIA2
  11. Now the database version is 11.2.0.1. Install the new ORACLE_HOME locations of 11.2.0.3 and upgrade the database after setting the environment variables to point to the new home, 11.2.0.3. Then run the upgrade scripts as shown in the following code:
    [oracle@oracle-stby ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 31 01:14:12 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
Database mounted.
Database opened.
SQL> @?/rdbms/admin/catupgrd.sql
DOC>##############################################################
DOC>##############################################################
DOC>
DOC>   The first time this script is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
.............
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM                This forces user to start a new sqlplus session in order
SQL> REM                to connect to the upgraded db.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application 
Testing options

    On the alert log, you will find the following:

    Database Characterset is WE8MSWIN1252
Updating 11.2.0.1.0 NLS parameters in sys.props$
-- adding 11.2.0.3.0 NLS parameters.
.............
Mon Dec 31 01:30:10 2012
SERVER COMPONENT id=CATPROC: timestamp=2012-12-31 01:30:10
SERVER COMPONENT id=RDBMS: status=VALID, version=11.2.0.3.0, timestamp=2012-12-31 01:30:15
  12. Now start the upgraded logical standby and check for the registry components' status from DBA_REGISRY.
    SQL> select comp_name, status from dba_registry;
COMP_NAME                                STATUS
---------------------------------------- -----------
OWB                                      VALID
Oracle Application Express               VALID
Oracle Enterprise Manager                VALID
OLAP Catalog                             VALID
Spatial                                  VALID
Oracle Multimedia                        VALID
  13. Starting SQL Apply: After the successful upgrade, we'll enable redo transport from the primary database and start SQL Apply on the logical standby database.

    Enable redo transport by running the following statement on the primary database.

    SQL> alter system set log_archive_dest_state_2='enable';
System altered.
  14. On the primary database, perform some DML transactions for verification.
    SQL> select count(*) from packt;
       COUNT(*)
---------------
             14
SQL> insert into packt select * from packt;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from packt;
       COUNT(*)
---------------
             28
  15. Start SQL Apply on the standby database and check for the number of rows from packt.
    SQL> alter database start logical standby apply immediate;
Database altered.
SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME       DATABASE_ROLE
-------------------- ----------------
INDIA_UN             LOGICAL STANDBY
SQL> select count(*) from scott.packt;
  COUNT(*)
----------
        28
  16. Switchover to upgraded 11.2.0.3: Until this step, there is no downtime on the production database. Now perform the switchover steps as shown in the following code:

    First issue the switchover command from the primary database.

    SQL> alter database commit to switchover to logical standby;
Database altered.
SQL> select db_unique_name,switchover_status,open_mode from v$database;
DB_UNIQUE_NAME  SWITCHOVER_STATUS    OPEN_MODE
--------------- -------------------- --------------------
turkey_un       NOT ALLOWED          READ WRITE

    On the alert log, you will find the following:

    Mon Dec 31 02:22:20 2012
NSA2 started with pid=26, OS id=12227
Beginning log switch checkpoint up to RBA [0x34.2.10], SCN: 1009787
...........
LOGSTDBY: Switchover complete (TURKEY)
LOGSTDBY: enabling scheduler job queue processes.
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to logical standby
  17. Now issue the switchover command from the upgraded logical standby database:
    SQL> select db_unique_name,switchover_status,open_mode from v$database;
DB_UNIQUE_NAME       SWITCHOVER_STATUS    OPEN_MODE
-------------------- -------------------- --------------------
INDIA_UN             TO PRIMARY           READ WRITE
SQL> alter database commit to switchover to logical primary;
Database altered.
SQL> select db_unique_name,switchover_status,open_mode from v$database;
DB_UNIQUE_NAME       SWITCHOVER_STATUS    OPEN_MODE
-------------------- -------------------- --------------------
INDIA_UN             NOT ALLOWED          READ WRITE
  18. Retransforming into the physical standby database: Now the new logical standby is running under Oracle lower patch set level (11.2.0.1) as a transient logical standby database, and it cannot receive and apply redo from the new primary database. Let's convert it into the old physical standby database state.
    SQL> alter system set log_archive_dest_state_2='defer';
System altered.

    Now flash back to the restore point before the upgrade.

    SQL> select db_unique_name,database_role from v$database;
DB_UNIQUE_NAME  DATABASE_ROLE
--------------- ----------------
turkey_un       LOGICAL STANDBY
SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> select name from v$restore_point;
NAME
------------------------------
ROLLING_UPGRADE_TURKEY
SQL> flashback database to restore point ROLLING_UPGRADE_TURKEY;
Flashback complete.
SQL> shutdown immediate
ORACLE instance shut down.
  19. Starting a logical standby database from new version binary (11.2.0.3): Copy PFILE/SPFILE, the password file, and network configuration files to the new installed ORACLE_HOME location and start the database in the MOUNT status.
    [oracle@oracle-primary ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 31 02:43:45 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount
Database mounted.
SQL> alter database convert to physical standby;
Database altered.

    Shut down instance and start up in the MOUNT status using the following code:

    SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
Database mounted.
  20. Enabling redo transport from a primary database and starting to recover on a standby database: Issue the following command from the new primary database to send redo data to the new physical standby database.
    SQL> alter system set log_archive_dest_state_2='enable';
System altered.

    Now start Redo Apply on the standby database to apply all the redo of the upgrade script.

    SQL> alter database recover managed standby database using current logfile disconnect;
Database altered.

    On the alert log, you will see the following:

    Mon Dec 31 02:51:28 2012
MRP0 started with pid=26, OS id=13970
MRP0: Background Managed Standby Recovery process started (TURKEY)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
...........
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Completed: alter database recover managed standby database using current logfile disconnect

    The previous errors are expected; if any archives are unable to fetch, copy the archive logs from the primary database and then catalogue them with the database using the RMAN command catalog start with 'arch location'.

    Mon Dec 31 04:08:50 2012
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (TURKEY)
.................... 
Media Recovery Log /u01/app/oracle/flash_recovery_area/TURKEY_UN/archivelog/2012_12_31/1_55_803436544.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/TURKEY_UN/archivelog/2012_12_31/1_56_803436544.dbf
Media Recovery Waiting for thread 1 sequence 57
  21. Verifying the upgraded standby database: Now the standby database is completely synchronized with the primary database.

    On the primary database, add the following:

    SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            56

    On the standby database, add the following:

    SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
            56

    Verify once if all the components of the registry are valid.

    SQL> @?/rdbms/admin/utlu112s.sql
Component                   Current      Version     Elapsed Time
Name                        Status       Number      HH:MM:SS
.
Oracle Database 11.2 Post-Upgrade Status Tool           12-31-2012 04:18:17
.
Component                   Current      Version     Elapsed Time
Name                        Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.3.0  .................
Gathering Statistics
.                                                                00:03:27
Total Upgrade Time: 00:46:51
PL/SQL procedure successfully completed.

Tip

So far the downtime is only for the switchover. However, at this point we moved the production database to the standby server. In order to keep clients connected to the new primary database, the connection failover should be configured for the database clients.

What just happened?

We have seen how to perform a rolling upgrade using the transient logical standby database with very little downtime. With this method, 96 percent of upgrade downtime can be avoided.

Have a go hero – one last switchover

If you want to use your original primary server as the production server, you should perform a switchover again, to move the primary server into the original server.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset