Login Oracle Instance Even When sysdba Cannot Do So

This blog is based on:

 

sqlplus -prelim "/as sysdba"

 

When archiver is stuck, users with DBA role is not able to login. Because new session logging on generates audit records or update SEQ$ tables to get new values from a sequence for populating AUDSID. These operations are protected by redo logs, archiver is stuck. Hence, DBA users are not allowed to login. However, SYSDBA/SYSOPER users are still able to logon. This is because SYSDBA connections do not generated any audit records to the database tables (that's why there's the audit_file_dest directory where SYSDBA audit files will be written) and SYSDBA connections also don't get their AUDSID from a sequence but use a hardcoded value (0xFFFFFFFF) instead.

 

When you log on normally (even as SYSDBA), this is what happens:
1.A new Oracle process is started (either by the listener or by local sqlplus if using the local BEQ connection)
2.The new process attaches to SGA shared memory segments (so it could access all the needed SGA structures)
3.The new process allocates process and session state objects and initializes new session structures in SGA

 

sqlplus -prelim skips step 3.

 

We can't query regular tables nor even V$ views, because we aren't really logged on! Because of the prelim option we do not have all the structures for query execution set up.

 

We're allowed to run:

oradebug hanganalyze 3
----hanganalyze trace in UDUMP generated by my preliminary connection's process.
oradebug setmypid
oradebug dump systemstate 10