19c Failed Upgrade restart Process

19c Failed Upgrade restart Process 

Step 1 . Set the env to Oracle 19C

. ./dbenv.csh_19

Env | grep ORA

Check catupgrd.sql

cd $ORACLE_HOME/rdbms/admin

ls -l catupgrd.sql

Step 2 . Startup database in Upgrade mode .

Sqlplus / as sysdba

Startup upgrade;

Exit

Step 3. cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -R -l $ORACLE_HOME/cfgtoollogs catupgrd.sql

After this Dbs will go down some time so depends upon situation you have to action.

So to start this upgraded database in 19c please ensure that 19c env is  set, properly.

 

Step 4 .

Run utlusts.sql, the Post-Upgrade Status Tool, which provides a summary of the status of the upgrade in the spool log.

You can run utlusts.sql any time before or after you complete the upgrade, but not during the upgrade.

SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql

Step 5 .

Check for invalid objects , if they exists run $ORACLE_HOME/rdbms/admin/utlrp.sql

Step 6 .

Now go back to 12c env and complete the post upgrade activity ----> No need

Note . Please note the DST sometimes is not upgraded from 26 to 32 when we follow the above process . You need to manually upgrade the DST in case it is not upgrade from 26 to 32.

Steps to check and upgrade DST-

Step 1 . Check the DST version

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME              VALUE

----------------------     ---------------------

DST_PRIMARY_TT_VERSION     26

DST_SECONDARY_TT_VERSION   0

DST_UPGRADE_STATE          None

Oracle 19c should have DST as 32 , and if you see 26 , that means it has not been upgraded. This will also be reported in post check script output.

Check the latest timezone

SELECT DBMS_DST.GET_LATEST_TIMEZONE_VERSION FROM DUAL;

GET_LATEST_TIMEZONE_VERSION

--------------------------------------------

32

Step 2 . Upgrade of DST TMZ is done in 2 part ( prepare and upgrade window)

Prepare .

During the prepare window, you can run any of the following scripts present in the $ORACLE_HOME/rdbms/admin directory to check how much data will need to be updated in the database during the time zone data upgrade:

.       utltz_countstats.sql

.       This script shows the optimizer statistics of num_rows of all the tables having TIMESTAMP WITH TIME ZONE (TSTZ) data.

.       Note:

.       Run the utltz_countstats.sql script only when the database optimizer statistics are up to date, else run the utltz_countstar.sql script. If you run the utltz_countstats.sql script, then you need not run the utltz_countstar.sql script.

Step 3 .

Upgrade Window

During the upgrade window, you can run the following scripts present in the $ORACLE_HOME/rdbms/admin directory to upgrade the time zone data in the database:

1.      Run the utltz_upg_check.sql script from the $ORACLE_HOME directory:

2.      spool utltz_upg_check.log

3.      @utltz_upg_check.sql

spool off

The following information is displayed on the screen after successful execution of the script:

CopyINFO: A newer RDBMS DST version than the one currently used is found.

INFO: Note that NO DST update was yet done.

INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.

INFO: Note that the utltz_upg_apply.sql script will

INFO: restart the database 2 times WITHOUT any confirmation or prompt.

The script also writes the following information in the alert.log file:

utltz_upg_check sucessfully found newer RDBMS DSTv new_time_zone_version and took number_of_minutes minutes to run.

If the utltz_upg_check.sql script displays the following error, check the previous message displayed on the screen and proceed accordingly.

ORA-20xxx: Stopping script - see previous message...

4.      Run the utltz_upg_apply.sql script from the $ORACLE_HOME directory after the utltz_upg_check.sql script is executed successfully:

5.      spool utltz_upg_apply.log

6.      @utltz_upg_apply.sql

spool off

Note:

The following are the prerequisites for running the utltz_upg_apply.sql script:

.       No application should query or insert time zone data in the database during the time zone upgrade process.

.       In an RAC environment, the RAC database must be started as a single database instance.

.       In a multitenant environment, all the PDBs must be shut down before running the utltz_upg_apply.sql script on the CDB.

Also, note the following:

.       The utltz_upg_apply.sql script automatically restarts the database multiple times during its execution.

.       The utltz_upg_apply.sql script generally takes less time to execute than the utltz_upg_check.sql script.

The following information is displayed on the screen after successful execution of the utltz_upg_apply.sql script:

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this sqlplus session.

INFO: Do not use it for timezone related selects.

The TZ_VERSION column in the Registry$database table now gets updated with the new time zone version.

If the script displays the following error message, then check the previous message displayed on the screen and proceed accordingly.

ORA-20xxx: Stopping script - see previous message...