AutoUpgrade Resume Job – What are your options?

AutoUpgrade greatly simplifies the database upgrades, however many times the upgrade can fail. Let’s look at the various AutoUpgrade Resume options and when and how to use them. AutoUpgrade tool offers two distinct modes of operation: console mode and no console mode.

• Console mode (Default) provides an interactive interface where the upgrade can be monitored in real-time.
• No console mode (ie with –no console option) allows for automated, unattended upgrade

Regardless of the mode, the auto-upgrade log files can be reviewed post-upgrade to ensure everything proceeds smoothly. So we started the upgrade from 12.1 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=/             
upg1.sid=ORCL                                              
upg1.source_home=/u01/app/oracle/product/12.1.0/db_4  
upg1.target_home=/u01/app/oracle/product/19.3.0/db_1 
upg1.start_time=NOW    
upg1.upgrade_node=host901
upg1.run_utlrp=yes                                  
upg1.timezone_upg=yes                              

AutoUpgrade Resume

The upgrade failed with the errors below. Since we were using console mode – we did not exit our console. Instead, we used a second ssh session to review the auto-upgrade log files.

The log was /u01/app/oracle/cfgtoollogs/autoupgrade/ORCL/ORCL/104/. Investigating the error (MOS Noe ID 2641766.1) we realized that the problem was that an SPM auto-evolve job was in progress. We followed the note to clean up the job. The thing to note here is that it is a good idea to pause this job before starting the upgrade and resume it post the db upgrade to avoid the error in the first place.

Identifier CATPROC 24-05-18 10:10:46
SCRIPT    = [/u01/app/oracle/product/19.3.0/db_1/rdbms/admin/a18.sql]
ERROR     = [ORA-13647: Setting of parameter ALTERNATE_PLAN_LIMIT is disallowed during task execution.
ORA-06512: at line 17
ORA-06512: at "SYS.PRVT_ADVISOR", line 5014
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 1287
ORA-06512: at "SYS.PRVT_ADVISOR", line 4906
ORA-06512: at line 2
]

Now the question was this. We had resolved the underlying problem, but could we just resume the Autoupgrade Job? The answer to that is yes. Since we had our console in the first session we could use the below command to resume the job. The job was completed successfully on resumption. The -a 60 option runs the status command in a loop every 60 seconds.

upg> status -job 104 -a 60

upg> resume -job 104

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 which will attach the session and attempt a resume, you can monitor the progress and attempt a resume as required.

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

Now what if you want to restore the database as it was before the start of the upgrade? ie back to 12c in our case. Then you will need to use the restore option. This will flashback the database to the Guaranteed Restore Point and open the database for you. You can then clear the recovery data if needed and restart the upgrade

upg> restore -job 102

Job 102[ORCL1] in stage [DBUPGRADE] has the status [ERROR]

Are you sure you want to restore? All progress will be lost [y/N] y

$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config autoupgrd-config.txt -clear_recovery_data -jobs 102

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

Once the upgrade has been completed be sure to gather dictionary stats and review the various other post upgrade steps. Resuming a failed Oracle upgrade with the AutoUpgrade tool can save time and you can efficiently get your database upgrade back on track.