Using AutoUpgrade for Oracle Database Upgrades

Posted by



  • What if something goes wrong during your database upgrade with AutoUpgrade? 
  • What happens if we exit the console accidentally?
  • What if the ssh connection lost during AutoUpgrade? 

    Don't worry, one of the advantages of AutoUpgrade is its resumability and restorability. Lately autoupgrade has become the preferred method to upgrade databases over DBUA. The AutoUpgrade utility is designed to automate the upgrade process, before starting upgrades, during upgrade deployments, during postupgrade checks and configuration migration. The AutoUpgrade console provides a set of commands to monitor the progress of AutoUpgrade jobs. The console starts by default when you run the AutoUpgrade utility, and is enabled or disabled by the parameters console and noconsole.

    AutoUpgrade configuration files contain all the information required to perform Oracle Database upgrades. We started the upgrade from 12c to 19c using the following configuration file.

    $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config autoupgrd-config.txt -mode deploy
    ## autoupgrd-config.txt
    global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
    upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
    upg1.sid=ORCL
    upg1.source_home=/u01/app/oracle/product/12.1.0.2
    upg1.target_home=/u01/app/oracle/product/19.3.0.0
    upg1.start_time=NOW
    upg1.upgrade_node=hostname
    upg1.run_utlrp=yes
    upg1.timezone_upg=yes
    upg1.target_version=19 --Only required when the target Oracle database version is 12.2

The upgrade failed with the below errors. 

upg> 
-------------------------------------------------
Errors in database [ORCL]
Stage     [DBUPGRADE]
Operation [STOPPED]
Status    [ERROR]
Info    [
Error: UPG-1400
UPGRADE FAILED [ORCL]
Cause: Database upgrade failed with errors

Log file showed the upgrade itself completed successfully. But datapatch had an issue in the post upgrade phase.

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Error: prereq checks failed!
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
Prereq check failed, exiting without installing any patches.

We followed Doc ID 1602089.1 and recreated the OPATCH directories and queried the inventory.

SQL> create or replace directory  OPATCH_SCRIPT_DIR as '/u01/app/oracle/product/12.2.0.1/QOpatch';

Directory created.

SQL> create or replace directory  OPATCH_LOG_DIR as '/u01/app/oracle/product/12.2.0.1/QOpatch';

Directory created.

SQL> create or replace directory OPATCH_INST_DIR as '/u01/app/oracle/product/12.2.0.1/OPatch';

Directory created.

SQL> select * from OPATCH_XML_INV;

XML_INVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance> <ora

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERIABLE_INVENTORY
--------------------------------------------------------------------------------
OK

 Resume the AutoUpgrade

Since we were using console mode the session was still active. After resolving the xml inventory issue we resumed the upgrade job. 

upg> resume -job 102

upg> status -job 102 -a 20

In case, you did not have the console open, or executed the job in the no-console mode you can still get the job back. Run the job in deploy mode again which will resume the upgrade.

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config autoupgrd-config.txt -mode deploy

Restore the database

For some reason if you want to restore the database as it was before the start of the upgrade i.e is 12c in this case,you will need to use the restore option which will complete the flashback to the guaranteed restore point.

upg> restore -job 102
 Job 102[ORCL] in stage [DBUPGRADE] has the status [ERROR]
 Are you sure you want to restore? All progress will be lost [y/N] y

To clear recovery data in AutoUpgrade, you can use the command-line parameter clear_recovery_data. This removes the recovery checkpoint, which allows AutoUpgrade to start fresh when launched on specified or all databases. 

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config autoupgrd-config.txt -clear_recovery_data -jobs 102
Once the recovery job(s) data was modified, The modified jobs will start from scratch.
 
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config autoupgrd-config.txt -mode deploy
Resuming an AutoUpgrade session is very simple. Just start AutoUpgrade with the same command line. It identifies the previous AutoUpgrade session, and resumes automatically. All the previous work is recovered, and the upgrade will resume from where it was stopped.

Comments

Post a Comment