RDBMS -- Startup Upgrade & Startup Migrate + a tip for running utlrp.sql

Today, I want to give a quick info about those fancy startup modes, called startup upgrade and startup migrate..

Actually, there is no big differences between these startup modes and the regular/default startup mode. The only difference in these modes are some events and some initialization parameters that are set  during the startup.

These modes are generally required while doing big and sensitive operations like database upgrades.
By setting those parameters and events, Oracle starts itself in a restricted mode to build a suitable environment for executing upgrade scripts or scripts like catproc and catalog.

Even for running utlrp.sql, it is recommended to be in startup upgrade mode. That is, while compiling the database objects with utlrp, there shouldn't be any concurrent compilation attempts to the related database objects.. When there is a concurrent attempt there, we may end up a deadlock and our invalid count could dramatically increase suddenly. (in order to prevent it, we disable our custom compilation jobs and compilation cron jobs before utlrp run, as well)

To understand this relation better, you can take a look at the utlrp.sql issue that I 've recent dealed with -> http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/ORA-04068-existing-state-of-packages-has-been-discarded-td7030.html

This is an interesting topic, isn't it?

So,  when a database is started in MIGRATE mode, the following ALTER SYSTEM  commands will be set automatically: ( note that, these parameters and events may change according to the database release and version)

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

The release 10.1 added "startup upgrade" in place of the startup migrate.

Beginning with Oracle 10g, the following additional ALTER SYSTEM commands will also
be executed in addition to the setting above:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='' SCOPE=MEMORY;
ALTER SESSION SET EVENTS='10933 trace name context off'