I posted a few months ago about the availability of blockchain tables in 19c, and a fundamental mantra with anything associated with blockchain is the guarantee that data cannot be tampered with. Thus when blockchain tables first became available, the questions were naturally asked were along the lines of:

• “Can a DBA or SYSDBA mess with the data?”

and steps have been taken to ensure that even privileged users do not get the ability to tamper with the data. For example, normally SYS can do anything to the database, but not so with some of the internal blockchain structures

SQL> select * from sys.BLOCKCHAIN_TABLE$;

no rows selected

SQL> show user
USER is "SYS"
SQL> delete from sys.BLOCKCHAIN_TABLE$;
delete from sys.BLOCKCHAIN_TABLE$
                *
ERROR at line 1:
ORA-05738: operation not allowed on the Oracle internal table

But there is one scenario where the SYS user has almost complete rights to all of the data dictionary, and that is when the database is in upgrade mode, because typically means the dictionary itself is being modified. How does the database protect itself from malicious intent if an administrator runs a “startup upgrade” command?

The developers thought about this, and added some protections to the database to handle this. If you have a blockchain table defined and you attempt to start the database in upgrade mode, you’ll see the following:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2097150624 bytes
Fixed Size                  9030304 bytes
Variable Size             704643072 bytes
Database Buffers         1375731712 bytes
Redo Buffers                7745536 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-05748: database open in upgrade mode failed due to nonempty SYS.BLOCKCHAIN_TABLE$ table 
Process ID: 42264
Session ID: 369 Serial number: 57176

The reason this is (or as we’ll see shortly, was) a good approach is that anyone that has patched the database lately will have noticed the improvements that have been made in this requirements for upgrade mode. Look at most patch notes, for example, the 19.12 patch below

and you’ll see that datapatch no longer requires the database to be in upgrade mode. You simply open it in normal mode and run datapatch.

However, there is a place where we still need to start the database in upgrade mode, and that is to update the time zone file. After the preparation phase completes, the database is opened in upgrade mode and encounters this problem:

SQL> @?\rdbms\admin\utltz_upg_apply.sql

Session altered.

INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv35 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2097150624 bytes
Fixed Size                  9030304 bytes
Variable Size             704643072 bytes
Database Buffers         1375731712 bytes
Redo Buffers                7745536 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-05748: database open in upgrade mode failed due to nonempty SYS.BLOCKCHAIN_TABLE$ table

In future releases, this will not be a problem because from 21c we have introduced zero downtime update of the time zone data, so perhaps we jumped the gun a little with this over zealous blockchain table check at startup. Note that even if you drop the blockchain table, you still will not be able to start the database in upgrade mode. But before you panic that your database is never coming back up, rest assured that normal startup is still fine.

So if you are in the particular circumstance where

• you are using blockchain tables,

• you are on 19c with RU version 19.10 or 19.11,

• you need to modify your time zone file definitions

then we recommend you hold off applying a time zone update until you move to 19.12, where we have fixed the inconsistency. From 19.12, you can start the database in upgrade mode even if you have blockchain tables.

Footnote: The same information applies here for pluggable databases