HOL 18c – Fallback – Issues During Upgrade

Protection for issues during upgrade

In this part you’ll use two techniques to protect your database for issues happening during the upgrade. Or simply, if you’d like to test multiple times.

You will evaluate two options: Partial Offline Backups and Guaranteed Restore Points.

1. Partial Offline Backup

A partial offline backup is used for protection against failures during the upgrade or for testing purposes to avoid the restoration of an entire database environment. You can change the COMPATIBLE parameter if you want with this technique. But don’t do it in the lab now as you’ll use two techniques in parallel.

  • Very large databases where restoring just a small piece of the database is faster than an entire restore
  • Databases who are – on purpose – in NOARCHIVELOG mode, hence you can’t do an online backup and restore
  • Standard Edition databases where you can’t use Guaranteed Restore Points

For a Partial Offline Backup as fallback strategy, you’ll have to put all your user and data tablespaces into read-only mode, then create an offline backup of the “heart” of your database.

  1. At first, set the USERS tablespace read-only and then SHUTDOWN the database:
    .

    sqlplus / as sysdba

    alter tablespace USERS read only;
    insert into SYSTEM.TRACKING_TAB values (1,'partial offline backup');
    commit;
    shutdown immediate
    exit

  2. Then copy the “heart of the database” to a backup location
    Execute the “copyFTEX.sh” script:
    .

    ./home/oracle/scripts/backupFTEX.sh

    ##
    ## This is for INFORMATION only 
    ## Don't execute - this is all done by the copyFTEX.sh above
    ## --
    ## The script copies redologs, controlfiles and all files for UNDO, TEMP, SYSTEM and SYSAUX
    ## to: /home/oracle/fast_recovery_area/FTEX/bck
    
    cp /u02/oradata/FTEX/*.ctl /home/oracle/fast_recovery_area/FTEX/bck
    cp /u02/oradata/FTEX/*.log /home/oracle/fast_recovery_area/FTEX/bck
    cp /u02/oradata/FTEX/sys*.dbf /home/oracle/fast_recovery_area/FTEX/bck
    cp /u02/oradata/FTEX/temp*.dbf /home/oracle/fast_recovery_area/FTEX/bck
    cp /u02/oradata/FTEX/undo*.dbf /home/oracle/fast_recovery_area/FTEX/bck
  3. Startup the database in 18c and upgrade it
    .

    . ftex18
    sqlplus / as sysdba

    startup upgrade
    exit

    dbupgrade -l /home/oracle/logs

  4. After 5 minutes, CTRL-C the upgrade and try the fallback
    .
    Once the upgrade is running, wait until the first phases have been completed, then hit:

    CTRL+C

    ...
    **************   Catproc Procedures   **************
    Parallel Phase #:13   [FTEX] Files:94   Time: 12s
    Restart  Phase #:14   [FTEX] Files:1    Time: 0s
    Parallel Phase #:15   [FTEX] Files:117  Time: 20s
    Restart  Phase #:16   [FTEX] Files:1    Time: 1s
    Serial   Phase #:17   [FTEX] Files:17   Time: 3s
    Restart  Phase #:18   [FTEX] Files:1    Time: 0s
    *****************   Catproc Views   ****************
    Parallel Phase #:19   [FTEX] Files:32 ^Ccatcon::catcon_HandleSigINT: Signal INT was received.

    The upgrade failed.

  5. Now first of all, try out the RESUME of the upgrade driver:

    dbupgrade -R -l /home/oracle/logs

    The upgrade should start from where it had been stopped.

    *******Upgrade being restarted on database FTEX from failed phase 19*******
    
    ------------------------------------------------------
    Phases [19-108]         Start Time:[2018_08_03 15:24:17]
    ------------------------------------------------------
       Time: 2s
    *****************   Catproc Views   ****************
    Parallel Phase #:19   [FTEX] Files:32   Time: 31s
    Restart  Phase #:20   [FTEX] Files:1    Time: 0s
    Serial   Phase #:21   [FTEX] Files:3    Time: 19s
    Restart  Phase #:22   [FTEX] Files:1    Time: 0s
  6. Let the upgrade fail a second time:

    CTRL+C

    Restart  Phase #:20   [FTEX] Files:1    Time: 0s
    Serial   Phase #:21   [FTEX] Files:3    Time: 19s
    Restart  Phase #:22   [FTEX] Files:1    Time: 0s
    Parallel Phase #:23   [FTEX] Files:24   Time: 283s
    Restart  Phase #:24   [FTEX] Files:1    Time: 1s
    Parallel Phase #:25   [FTEX] Files:12 ^Ccatcon::catcon_HandleSigINT: Signal INT was received.
  7. Then SHUTDOWN the database and RESTORE it.
    Check the content of the TRACKING_TAB:

    sqlplus / as sysdba

    shutdown immediate
    exit

    ./home/oracle/scripts/restoreFTEX.sh
    . ftex
    sqlplus / as sysdba

    startup
    select * from TRACKING_TAB;
    exit

Now you completed the first exercise.

2. Flashback to a Guaranteed Restore Point

By far the best and most simple technique to protect your databases are Guaranteed Restore Points. But it can only used when the following requirements are all met:

  • Database must be in ARCHIVELOG mode
  • Enterprise Edition database (or XE or PE)
  • Don’t change COMPATIBLE

This is the overview on how to fallback with a guaranteed restore point GRP1 which allows you to flashback your database – many times.

  1. Turn on ARCHIVELOG mode:
    .

    . ftex
    sqlplus / as sysdba

    shutdown immediate;
    startup mount
    alter database archivelog;
    alter database open;
    archive log list

  2. Set a Guaranteed Restore Point
    .

    insert into SYSTEM.TRACKING_TAB values (2,'guaranteed restore point');
    commit;
    create restore point GRP1 guarantee flashback database;
    shutdown immediate
    exit

  3. Switch to the 18c environment and upgrade the FTEX database:
    .

    . ftex18
    sqlplus / as sysdba

    startup upgrade
    exit

    dbupgrade -l /home/oracle/logs

    This will now take 15-30 minutes depending on your hardware equipment..

    sqlplus / as sysdba

    @?/rdbms/admin/utlrp.sql
    @/u01/app/oracle/cfgtoollogs/FTEX/preupgrade/postupgrade_fixups.sql
    insert into SYSTEM.TRACKING_TAB values (3,'upgrade completed');
    commit;
    select * from TRACKING_TAB;

  4. Set a new GRP and then flashback to the GRP to before-upgrade. The FLASHBACK DATABASE happens in the new environment, the OPEN of the database (in this case READ ONLY – alternative would be OPEN RESETLOGS) in the source environment:
    .

    create restore point GRP2 guarantee flashback database;
    shutdown immediate
    startup mount
    flashback database to restore point GRP1;
    shutdown immediate
    exit

    . ftex
    sqlplus / as sysdba

    startup open read only;
    select * from TRACKING_TAB;

    Do you recognize that the database has been flashed back to “before upgrade” in less than a minute? You could open it RESETLOGS and repeat the upgrade.

  5. But FLASHBACK DATABASE works in all directions, backwards and forward (even though forward may take a bit longer now):
    .

    shutdown immediate
    exit

    . ftex18
    sqlplus / as sysdba

    startup mount
    flashback database to restore point GRP2;
    alter database open resetlogs;
    select * from TRACKING_TAB;

    Even though you opened the database with OPEN RESETLOGS you can repeat the FLASHBACK DATABASE operations as often as you’d like.

  6. Take also note of the components which exist now in the database:
    .

    select COMP_ID, STATUS from DBA_REGISTRY order by COMP_ID;

  7. Clean up and drop the restore points as otherwise at some point you’ll run out of archive space. In addition you’ll turn off ARCHIVELOG mode now as we won’t need it for the next exercises:
    .

    drop restore point GRP1;
    drop restore point GRP2;
    shutdown immediate
    startup mount
    alter database noarchivelog;
    alter database open;
    archive log list
    exit

You successfully completed the second part of the Fallback Strategies lab.
.