Search My Oracle Blog

Custom Search

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 :


ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - 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 10.2.0.4.0 - 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
Connected.
SQL> select log_mode, archiver from
 2  (select log_mode from v$database), (select archiver from v$instance);

LOG_MODE     ARCHIVE
------------ -------
ARCHIVELOG   STARTED

SQL>  


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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016