:

How to Upgrade your Primary and Physical Standby Databases

In our environment we have one primary DB and two Data Guard DBs, both at different physical locations. The set up looks like this:

  • Primary DB, 10.2.0.4
  • Physical standby DB 1, 10.2.0.4
  • Physical standby DB 2, 10.2.0.4

Our aim is to upgrade the DBs to 11g, but should the upgrade fail on our primary DB, or we want to roll it back, we don’t want to have to rebuild the physical standby databases. Is this possible? Let’s do some research to find out…

I should point out at this stage that all of our databases are running on Windows server 2003 SP2.

Upgrading Data Guard, Step-by-Step

If we just had a primary DB to upgrade and rollback the plan would be very simple and would look something like this:

  • Take a snap of the database LUNs after a clean shutdown of the DB
  • Run in the upgrade
  • Use the snap to rollback if the upgrade failed
  • If the upgrade was successful the snap can be dropped

Due to the added complexity in having the physical standby databases in our configuration, we also need to consider how we would rollback any changes to them if they were upgraded at the same time as the primary. Or, should we leave them as they were at the point in time before the 11g upgrade? At this stage this is what we were thinking/asking ourselves:

Note: We didn’t have the technology available to use the same snap approach on the physical standby databases.

  • If logs have been applied to the physical standby databases during the upgrade what will happen to them when we rollback the primary?
  • If we stop the physical standby databases from applying archived logs, then rollback the primary and start shipping logs again will that work?
  • Will the physical standby databases be able to just start off from where they left off after we’ve rolled back the primary database?

In order to answer these questions I thought the best way would be to do some testing. I decided that I wanted to test out the method of pausing log apply to the physical standby DBs, take a snap of the primary DB LUN, upgrade the primary DB from 10g to 11g, rollback to the snap of the LUN, bring the primary DB back up and start the log apply again. We thought that this approach should work in theory but weren’t 100% sure about it.

Here is a list of exactly what I will be walking through in the example below:

  • Data Guard configuration on 10.2.0.4 (majority will also apply to 11g)
  • Primary DB RMAN back up for creating your physical standby database
  • Restoring the RMAN backup to create the physical standby
  • Configuring and enabling archived redo log shipping from primary to standby
  • Implementation of the rollback strategy outlined above
  • A simulated failure during an upgrade – just some load in the database
  • The process for reverting to the snapped version of the LUN

Why am I using SAN Snap Technology and not Oracle Flashback?

My personal preference is using SAN snap technology over Oracle’s flashback approach. Why? For several reasons:

  1. Oracle Flashback seems to use a large amount of space when changes are made to the database
  2. In the past I have used a guaranteed restore point while performing an upgrade from 10g to 11g, and the restore point got corrupted so I couldn’t revert back to it
  3. Only a clean shutdown of the DB is required for the SAN snap; no DB changes at all. Clean, quick and less error prone

Hopefully these guidelines are self-explanatory for a semi experienced Oracle DBA. If you have any questions please feel free to contact me or add a question in the comments section at the bottom of this page and I will try to help you out.

Guide to Creating and Configuring a Physical Standby Database with Data Guard

RMAN backup and Archive Log Mode

The first stage is to take an RMAN level 0 backup which you will use later to restore and create your physical standby database. I’m assuming that you don’t want to take the database down to complete the backup so hopefully your database is already running in ARCHIVELOG mode? You can check with this query:

   SELECT log_mode
   FROM v$database;

If your primary database is not in ARCHIVELOG mode you can either take a cold backup, i.e. while the database is down, or put then database in ARCHIVELOG mode. Have a read of how to put your database into archivelog mode if you are not sure how to do it

Enable Force Logging in your Database

Data Guard works on the basis that every transaction which occurs on the primary database is logged into the REDO stream and therefore shipped across to the physical standby database and subsequently applied. If there are transactions which are not logged in the REDO this breaks your standby database. You can, and have to, enforce logging using this command:

   ALTER DATABASE FORCE LOGGING;

The next step is to take an RMAN level 0 backup. Here are the commands you can use:

   rman target sys/<pwd>@<SID>
   run
   {
   backup as compressed backupset incremental level = 0 database plus archivelog;
   }

The size of your database will determine how long the backup takes. The RMAN files will be around 20% of the size of your database. Then copy all the files across to your physical standby database server ready to import.

Tip: when logged onto RMAN use the SHOW ALL command to see what level of parallelism you will be using for your backup. You may find that you can ramp this up to speed up your backup time.

Primary Database Initialisation Parameter Changes for Data Guard Configuration

Here I list down what changes I made to the init.ora parameters on my primary database. ORCL is the primary DB name and ORCLDG is the physical standby database name. The file locations will differ only by a change in the name which reflects that DB name. For example, D:\Oracle\ORCL will become D:\Oracle\ORCLDG.

-- This shows that there are two databases in the Data Guard configuration
alter system set log_archive_config='DG_CONFIG=(ORCL,ORCLDG)' scope=both;

-- Ensures log archive destination 1 is enabled
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

-- Configures archived REDO log shipping to the Data Guard DB (ORCLDG)
alter system set log_archive_dest_2='SERVICE=ORCLDG ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCLDG' scope=both;

-- Ensures log archive destination 2 is enabled
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both;

-- This value must be the same on both databases in order for the logs to ship
alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

-- This specifies the format for the archive logs
alter system set LOG_ARCHIVE_FORMAT=%t_%s_%r.arc scope=both;

-- decide on how many processes will be shipping the logs. More processes equals more CPU on the primary DB
alter system set LOG_ARCHIVE_MAX_PROCESSES=4 scope=both;

-- Configure the Fetch Archive Log server to the Data Guard DB
-- Only require after standby switchover or failover
alter system set FAL_SERVER=ORCLDG scope=both;

-- Converts all file names from ORCLDG to ORCL
-- Only require after standby switchover or failover
alter system set DB_FILE_NAME_CONVERT='ORCLDG','ORCL' scope=spfile;

-- Converts all REDO log file names from ORCLDG to ORCL
-- Only require after standby switchover or failover
alter system set LOG_FILE_NAME_CONVERT='ORCLDG','ORCL' scope=spfile;

-- Enforces the addition of files to the physical standby DB automatically when they are added to the primary DB
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Standby Control File and Initialisation Parameters

This next section involved creating a standby control file for your new physical standby database and configuring the initialisation parameters.

-- Run this against the primary DB to create the parameter standby control file
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\temp\ORCL.ctl';

-- This is to be run against the primary DB to create a basis for the parameter file for the standby database
CREATE PFILE='C:\temp\initORCL.ora' FROM SPFILE='G:\Oracle\oradata\ORCL\admin\pfile\spfileORCL.ora;

Copy both of these files across to the physical standby database server.

The next section involves modifying the parameter file so that it can be used to start the physical standby database.

-- The database name remains the same as the primary
DB_NAME=ORCL

-- This changes to be the name of your physical standby DB
DB_UNIQUE_NAME=ORCLDG

-- Change if the file paths have changed between your primary and standby DB.
CONTROL_FILES='D:\ORADATA\ORCLDG\CONTROL01.CTL','D:\ORADATA\ORCLDG\CONTROL02.CTL'

-- Converts all occurrences of ORCL to ORCLDG for the database file names
DB_FILE_NAME_CONVERT='ORCL','ORCLDG'

-- Converts all occurrences of ORCL to ORCLDG for REDO log file names
LOG_FILE_NAME_CONVERT='ORCL','ORCLDG'

-- Location for the archived REDO logs which are shipped over from the primary DB
LOG_ARCHIVE_DEST_1='LOCATION=O:\flash_recover_ORCL\ArchLogs VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCLDG'

-- Required for when/if the standby DB changes to run as the primary DB, otherwise not needed
LOG_ARCHIVE_DEST_2='SERVICE=ORCL ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'

-- Ensures that both archive log destinations are enabled
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

-- This points to the name of the primary database for resolving archive log gaps
FAL_SERVER=ORCL

We’re making good progress. So far we have configured all of the init.ora parameters for the primary and standby DBS. We’ve also configured them so that a switchover and/or a failover can take place.

Let’s take a look at the next step which is…

TNSNames, Listener and Windows service Configuration Steps

    1. Configure the TNSNames files on the primary and physical standby databases

As stated earlier, the ORCL DB is the primary and ORCLDG is the physical standby. Here are the TNSNames entries:

    ORCL.DEV.INT.COM =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(Host = ORCLlistener)(Port = 1521))
         )
        (CONNECT_DATA =
          (SERVICE_NAME = ORCL.DEV.INT.COM)
          (SERVER = DEDICATED)
        )
      )

    ORCLDG.DEV.INT.COM =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(Host = ORCLDGListener)(Port = 1521))
         )
        (CONNECT_DATA =
          (SERVICE_NAME = ORCLDG.DEV.INTCOM)
          (SERVER = DEDICATED)
         )
       )

I am using a DNS entry for the HOST in the above TNSNames configuration. You can use an IP address if you would prefer.

Also, please ensure that SQLNET access between the two servers is possible otherwise log shipping will not work.

    1. Listener entries also need to be added to both DB servers

If you don’t have listeners already you will need to create them. You can just set up the configuration in the listener.ora parameter with the settings below, modified to your environment, if you don’t have any existing listener entries there already.

    -- Data Guard Listener Configuration
    11GDGLIST =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.63.41.57)(PORT = 1521)
      )
    )

    SID_LIST_11GDGLIST =
      (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = ORCLDG.DEV.INT.COM)
        (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL)
      )
    )

    -- Primary DB Listener Configuration
    11GLIST =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.60.41.57)(PORT = 1521))
      )
    )

    SID_LIST_11GLIST =
      (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = ORCL.DEV.INT.COM)
        (ORACLE_HOME = D:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = ORCL)
      )
    )

As you may have noticed, the listener configuration for the Data Guard listener uses the SID_NAME = ORCL and not ORCLDG. Remember that the primary database name is the same as the physical standby, it just differs by the db unique name which is ORCLDG.

    1. Data Guard Windows Service
    D:\oracle\product\11.2.0\dbhome_1\oradim - NEW -SID ORCLDG -STARTMODE m

You should always specify the full path to any Oracle executable files to avoid any confusion over using an incorrect version if you use a different oracle home by default on that server.

    1. Password file creation
    D:\oracle\product\11.2.0\dbhome_1\orapwd file='D:\ORADATA\ORCLDG\pwdORCL.ora' password=change_on_install

I would like to refer you to another article I have written which is entitled ORA-16191 and relates to change data capture. The interesting part of this article is that I couldn’t get log shipping to work for one of my physical standby databases due to this error and the resolution was to copy the password file from the primary database to the physical standby DB and that resolved it.

  1. SPFILE creation on the physical standby database
SET ORACLE_SID=ORCL

    SQLPLUS "sys/change_on_install as sysdba"

    STARTUP NOMOUNT PFILE='D:\ORADATA\ORCLDG\ADMIN\PFILE\initORCL.ora'

    CREATE SPFILE='D:\ORADATA\ORCLDG\ADMIN\PFILE\SPFILEORCL.ora' FROM PFILE='D:\ORADATA\ORCLDG\ADMIN\PFILE\initORCL.ora'

Let’s now restore the RMAN level 0 backup which we took from our primary DB. You should copy this over to the Data Guard server before starting the restore, ideally.

    SET NLS_DATE_FORMAT=YYYY-MM-DD:HH24:MI:SS
    RMAN TARGET sys/change_on_install@ORCLDG
    ALTER DATABASE MOUNT;

    -- You need to catalog the RMAN files so that they can be used to restore the database
    CATALOG START WITH 'O:\Oracle\flash_recover_ORCLDG\flashback\ORCL\BACKUPSET\2012_05_29';

    -- This command will help you set the new names for the files
    -- You should run this against the primary DB and then use the output in the RMAN command block below
    SELECT 'set newname for datafile '''||file_name||''' to '''||replace(file_name,'ORCL','ORCLDG')||''';' as datafile
    FROM dba_data_files;

    -- Here we allocate two channels for the restore operation.
    -- Use more if you have a fast server and can afford to use more CPU and disk I/O
    -- The set newname statements are from the statement above which you ran against the primary DB
    -- The TAG is the RMAN level 0 backup
    -- The files should all be restored to the locations specified by the set newname commands

    run
    {
    ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT 'O:\Oracle\flash_recover_ORCL\flashback\ORCL\BACKUPSET\2012_05_29\';
    ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT 'O:\Oracle\flash_recover_ORCL\flashback\ORCL\BACKUPSET\2012_05_29\';
    set newname for datafile 'D:\ORADATA\ORCL\DATA\SYSTEM01.DBF' to 'D:\ORADATA\ORCLDG\DATA\SYSTEM01.DBF';
    set newname for datafile 'D:\ORADATA\ORCL\DATA\UNDOTBS01.DBF' to 'D:\ORADATA\ORCLDG\DATA\UNDOTBS01.DBF';
    set newname for datafile 'D:\ORADATA\ORCL\DATA\SYSAUX01.DBF' to 'D:\ORADATA\ORCLDG\DATA\SYSAUX01.DBF';
    set newname for datafile 'D:\ORADATA\ORCL\DATA\USERS01.DBF' to 'D:\ORADATA\ORCLDG\DATA\USERS01.DBF';
    RESTORE DATABASE FROM TAG 'TAG20120529T110507';
    RECOVER DATABASE;
    SWITCH DATAFILE ALL;
    }

    -- Now we can check that the primary DB can ship the logs to the standby:

    -- Run against the primary DB
    ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=both;

    -- Run on the Standby DB
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
There you have it, it should now be shipping logs. You can use this query to see if the logs are being applied at the standby database:
    set lines 120
    set pages 1000
    alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

    SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME
    FROM V$ARCHIVED_LOG
    WHERE FIRST_TIME > SYSDATE -3
    ORDER BY SEQUENCE#;

If they are not being registered and/or applied, check out the alert log and see what if there are any errors. Once you are happy that the logs are shipping and applying to your physical standby database it’s time to look at the next step which is the more interesting part and the reason that we configured the standby database in the first place.

Snap, Simulate Failure and Rollback

These next steps should run us through all of the steps required to simulate what would happen in an upgrade scenario. I am trying to test out a failback plan for my Oracle 10g to 11g upgrade, but for the purpose of this time I will just use some very small table creation and insert to simulate some kind of work having have happened on the primary database.

    1. log file switch on primary database
  ALTER SYSTEM SWITCH LOGFILE;
    1. Check to see if the log has been applied to the physical standby database
set lines 120
     set pages 1000
     alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

     SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME
     FROM V$ARCHIVED_LOG
     WHERE FIRST_TIME > SYSDATE -3
     ORDER BY SEQUENCE#;
    1. DEFER shipping of the archived REDO logs
     ALTER SYSTEM SET log_archive_dest_state_2=DEFER SCOPE=both;
    1. Shut the primary database down cleanly
SHUTDOWN IMMEDIATE;
    1. Take a snap of the primary database LUN(s) after it has been shutdown, which the storage engineers had to do for me
    2. Start the primary database back up
STARTUP
    1. Add some data or change something in the primary database to simulate load or the upgrade in our case
CREATE TABLE testing (
    ID NUMBER(10),
    NAME VARCHAR2(25));

    begin
    for i in 1..100000 loop
    insert into testing values (1,'Hello');
    end loop;
    end;
    /   
    COMMIT;
    1. Now pretend that something went wrong and you needed to rollback the changes
SHUTDOWN IMMEDIATE
    1. Get the storage engineer to revert back to the original snap of the LUN

IMPORTANT: Delete any archivelogs which have been created since the snap so that they are not shipped and applied to the standby database

    1. Start the database again
STARTUP
    1. Enable log file shipping
ALTER SYSTEM SET log_archive_dest_state_2=ENABLE SCOPE=both;
    1. Enable log apply services at the physical standby database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
    1. Now for the final test, to see if the archive logs are shipping and applying to your physical standby database
set lines 120
     set pages 1000
     alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

     SELECT SEQUENCE#, APPLIED, FIRST_TIME, NEXT_TIME
     FROM V$ARCHIVED_LOG
     WHERE FIRST_TIME > SYSDATE -3
     ORDER BY SEQUENCE#;

If the test was successful you should see that all of the log file are shipping to the physical standby database and applying again. This worked for me in my test so it process that this is a viable solution to the failback approach when upgrading your database from 10g to 11g and you have physical standby databases in place. I assume that this will work just as well for logical standby databases, but I am not familiar with how they work and are configured so I can’t verify that statement.

As always, drop me a message or leave a comment below if you have any questions and I always welcome feedback.

Good luck,
Rob

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *