Oracle Shutdown – Force Shutdown as Root Without Logins

The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.

There are 3 SQL*Plus shutdown commands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:

1. shutdown normal or just shutdown: waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open. This used to be the only shutdown mode that did not cancel running transactions.
2. shutdown transactional: disconnects sessions once currently running transactions complete, preventing new transactions from beginning.
3. shutdown immediate: disconnects all sessions immedately and rolls back interrupted transactions before shutting down. Note that the disconnections are immediate, but the shutdown may not be as any interrupted transactions may take time to roll back.

The fourth mode of shutdown is shutdown abort. This is like pulling the power cord - the instance stops now without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide says:

This mode is intended for emergency situations, such as when no other form of shutdown is successful.

All the examples you give perform a checkpoint as part of the shutdown [normal] or shutdown immediate so explicit checkpointing is presumably to reduce the time required for recovery.

general advice:

• Do not use shutdown normal.
• Use shutdown transactional for attended shutdown only, when you want to minimise cancelled transactions (attended only because this kind of shutdown is not guaranteed to shut the database down at all if timeouts are breached).
• Use shutdown immediate for unattended shutdown or when you do not care about currently running transactions.
• Do not use shutdown abort (plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to minimise downtime this mode may be appropriate.

AUDIT_TRAIL is an initialisation parameters and needs a database restart. Anyway by default AUDIT_TRAIL is set to DB. This could be enough.

With this AUDIT_TRAIL you have just to issue:

AUDIT SELECT TABLE 

to start the audit on any table for any select. As you stated this activity has an overhead from the database point of view. you have to pay attention to you SYSAUX tablespace growth where by default resides the sys.aud$ table. To move sys.aud$ you have to do:

SQL> BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
 audit_trail_location_value => 'AUDIT_TBS');
END;
/

FGA is much more than AUDIT and lets you specify the conditions necessary for an audit record to be generated. Furthermore FGA policies are programatically bound to the object (table, view).

Apart from this you should consider a different solution like an third party tool for DAM (Direct Access Monitoring). Oracle has its own solution today called Oracle Firewall. Otherwise you can check for other products like IBM Guardium, DBProtect, etc.