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 :
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.
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:
Post a Comment