DBA_HIST_DATABASE_INSTANCE

In 10g there is a view called DBA_HIST_DATABASE_INSTANCE which has database instance information. It has history on information such as instance startup time, last ASH sample id.

Example:
SQL> column startup_time format a35
SQL> alter session set nls_timestamp_format=’DD-MON-YYYY HH24:MI:SS’;

Session altered.

SQL> SELECT startup_time, last_ash_sample_id FROM dba_hist_database_instance WHERE dbid = 1992878807 order by startup_time;

STARTUP_TIME LAST_ASH_SAMPLE_ID
—————————— ——————
12-AUG-2009 21:12:19 447735
21-AUG-2009 17:48:29 494755
..
27-SEP-2009 15:24:04 3036072
29-SEP-2009 21:44:22 0

8 rows selected.

Advertisements

What are the different options to shutdown?

shutdown (or shutdown normal) – In this method the database shuts down cleanly and oracle will wait for all users process to terminate before shutting down the database, so if there is user with SQL*Plus session that is idle oracle will not terminate till the user session exits it. If user sessions haven’t been terminated then you will see a following message in the alert.log
Shutting down instance: further logons disabled

Active process 2408 user ‘SYSTEM’ program ‘ORACLE.EXE (SHAD)’
SHUTDOWN: waiting for logins to complete.

shutdown immediate – In this mode oracle will kill all existing sessions and rollback all uncommitted transactions. Using this option instance recovery is not needed.

shutdown transactional – In this mode oracle will wait for user to commit their transaction so if the user is running a select statement it will terminate the user session unless it’s part of a transaction that is being run.

shutdown abort – In this mode one forces oracle to crash, in this mode instance require will be required not media recovery. This option can still be used even if any of other above shutdown options are in progress.
Example:
From one of the session, shutdown in progress but for some reason it’s hung even after one looks at the alert log file and trace files.
SQL> shutdown
— after the shutdown abort completes this session also terminates with the following message
ORA-03113: end-of-file on communication channel

From another session one can run shutdown abort
SQL> shutdown immediate;
ORA-10997: another startup/shutdown operation of this instance inprogress
ORA-09968: unable to lock file
SQL> shutdown abort;
ORACLE instance shut down.

When shutdown is in progress users will not able to login to the database. They will receive the following error “ORA-01090: shutdown in progress – connection is not permitted”.

Still for some reason one can’t still shutdown the database the only option that may remain is to kill the PMON process which will forcefully kill the process.

Information on Database in restricted mode.

When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.
SQL> STARTUP RESTRICT;

— shows database is in restricted mode
12:49:30 @> select logins from v$instance;

LOGINS
———-
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;

Grant succeeded.

To revoke access restricted mode access
SQL> REVOKE RESTRICTED SESSION FROM scott;

How to find the users that have restricted session access:
— find users who have been granted through the role (displays 1 level)
SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ — find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

GRANTEE GRANTED
———— ————————————-
SCOTT User (Direct)
SYS DBA (Role)
SYSTEM DBA (Role)

To change from restricted mode to unrestricted/active mode, which can switch without restarting the database.

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
ALLOWED

Also database can be placed in restricted mode by running the following DDL without restarting the database.

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
———-
RESTRICTED

Note: There seems to be an issue when disabling restricted mode in 10.2.0.1, you can try to shutdown from the same session where restricted session was disable was executed from.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction – commit or rollback first

You can work around this by executing shutdown from a new session or executing commit/rollback from the session where restricted session was disabled.

How to tell if the database was started with spfile or pfile?

There are couple of ways of finding if the database was started with spfile or pfile.

One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile.

— belows show database was started by spfile
SQL> show parameter spfile;

NAME TYPE VALUE
—— —— ————————————————
spfile string /u01/apps/oracle/10g/dbs/spfileorcltest.ora

Another way to find would be set the parameter with scope=spfile, if database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”. Note: You can set the new value to be the same as current value of the parameter, it doesn’t have to be a different one.

SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup