11 March, 2009

Checking the status of a database

Most DBAs seem to be in the habit of using the command "ARCHIVE LOG LIST" to check a database (instance's) archivelog and archiver status.
However, this presupposes that you connect AS SYSDBA.
Similarly, many other postings I see on forums also assume either of
a. CONNECT / AS SYSDBA on the SQLPlus command line
b. Connection via a GUI (OEM or TOAD), where, I suspect, the connection is still done with AS SYSDBA.

I dislike using AS SYSDBA and prefer to use an account with less powerful privileges. An account with SELECT on specific views is generally sufficient to monitor a database. Such an account may have SELECT ANY DICTIONARY (or, *more preferably* SELECT_CATALOG_ROLE if you need to query only views and not query SYS tables directly) if you need it to be able to query any/all data dictionary and performance views. An account with such privileges can do no harm.

As a DBA, your first responsibility is towards the Data. As far as possible, work in such a manner that restricts you from harming/damaging data even inadvertently.

Logging in with an account with the DBA role should not be very frequent.
Logging in AS SYSDBA should be rare - very rare, in my opinion.

Here's an example where I do not need DBA and SYSDBA and I can check the status of a database. This method is particularly useful when you are monitoring multiple databases from a single "monitoring station", connected over SQL*Net :


SQL*Plus: Release - Production on Wed Mar 11 23:15:56 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user query_database identified by query_database;

User created.

SQL> grant create session to query_database;                      

Grant succeeded.

SQL> grant select on v$database to query_database;
grant select on v$database to query_database
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

SQL> grant select on v_$database to query_database;

Grant succeeded.

SQL> grant select on v_$instance to query_database;

Grant succeeded.

SQL> connect query_database/query_database@MYREMOTEDB
SQL> select log_mode, archiver from
 2  (select log_mode from v$database), (select archiver from v$instance);

------------ -------


Once I have an account called "QUERY_DATABASE" (or "MONITORING_ACCOUNT" or, even if you really need to use this "DBSNMP"), I do NOT need to login with the DBA role or the SYSDBA privilege for most monitoring scripts / queries.

No comments: