HOL 18c – Fallback – Issues After Upgrade

Protection for issues after upgrade

In this part you’ll use again two techniques to protect your database, but this time for issues happening after the upgrade. You can call this also “Downgrade“.

1. Downgrade with a Full Database Export and Import

For this part you’ll just start the export from the 18c database after upgrading.

  1. Run the full database export:
    .

    . ftex18
    sqlplus / as sysdba

    insert into SYSTEM.TRACKING_TAB values (4,'full export downgrade');
    commit;
    select * from TRACKING_TAB;
    exit

    expdp system/oracle DIRECTORY=EXP18 DUMPFILE=down.dmp LOGFILE=down.log VERSION=12.1 FULL=Y REUSE_DUMPFILES=Y EXCLUDE=STATISTICS LOGTIME=ALL

    The important part is that the VERSION parameter tells Data Pump to create an export in the format a database of “VERSION” will understand.

  2. In this case you will downgrade into a 12.1 Pluggable Database. You need to create the PDB first.
    .

    . cdb1
    sqlplus / as sysdba

    startup
    create pluggable database PDB3 admin user adm identified by adm file_name_convert=('/u02/oradata/CDB1/pdbseed','/u02/oradata/CDB1/pdb3');
    alter pluggable database PDB3 open;
    create directory IMP18 as '/home/oracle/IMP';
    grant read, write on directory IMP18 to public;
    exit

    impdp system/oracle@PDB3 DIRECTORY=IMP18 DUMPFILE=down.dmp LOGFILE=impdown.log LOGTIME=ALL

This was a quick exercise. Of course it would take longer the more data and objects your database contains. Especially LOB data types can be crucial.

2. Downgrade with the downgrade scripts

In this final exercise you’ll use a powerful technique, the database downgrade with downgrade scripts.

  1. Set a marker in the database
    .

    . ftex18
    sqlplus / as sysdba

    insert into SYSTEM.TRACKING_TAB values (5,'database downgrade');
    commit;

  2. Run the downgrade script
    .

    shutdown immediate
    startup downgrade
    set echo on termout on serveroutput on timing on
    spool /home/oracle/logs/downgrade.log
    @?/rdbms/admin/catdwgrd.sql
    shutdown immediate
    exit

  3. Switch to the source environment and start he bootstrap reload script
    .

    . ftex
    sqlplus / as sysdba

    startup upgrade
    set echo on termout on timing on
    spool /home/oracle/logs/relod.log
    @?/rdbms/admin/catrelod.sql
    shutdown immediate

  4. Final steps and checks
    .

    startup
    @?/rdbms/admin/utlrp.sql
    select * from TRACKING_TAB;
    select count(*) from DBA_OBJECTS where STATUS=’INVALID’;
    select COMP_ID, STATUS from DBA_REGISTRY order by COMP_ID;

    The downgrade should have removed the XDB component as well. Did it?

Congratulations. You completed all 4 steps of the Fallback Strategies Hands-On Lab.

Feel free to upgrade again!