In an unfamiliar environment, if there's only oracle or grid account is available for DBA to use, how can he to know everything about the database in a short time?
Single-Instance
For a standalone, you can do this:
Oracle Home
[oracle@test ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0
Oracle SID (Instance Name)
[oracle@test ~]$ echo $ORACLE_SID
ORCL
If there's no $ORACLE_SID, you may check smon, the background process of the instance is alive or not in order to know Oracle SID, if the instance is up and running.
[oracle@test ~]$ ps -ef | grep smon
oracle 5304 1 1 19:47 ? 00:00:00 ora_smon_ORCL
The last identifiable name ORCL is the instance name ($ORACLE_SID), now running on this server.
Please notice that the instance may not be up and running. If the instance is not startup, there will be no result when you grep smon from the process name. You may check the pfile or spfile name for SID. They're usually formatted as:
init<SID>.ora
spfile<SID>.ora
We list all files in dbs:
[oracle@test ~]$ ls -l $ORACLE_HOME/dbs
...
-rw-r----- 1 oracle oinstall 3584 Nov 14 2012 spfileORCL.ora
-rw-r----- 1 oracle oinstall 3584 Mar 21 2014 spfileCATDB.ora
In this case, Oracle SID in this server are ORCL and CATDB.
Service Name and Listener Port
After knowing $ORACLE_SID, you can login the database through OS authentication, and then do further queries:
[oracle@test ~]$ export ORACLE_SID=ORCL
[oracle@test ~]$ sqlplus "/ as sysdba"
...
SQL> show parameter service_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string ORCL
But service_names may not be all registered with the listener. You should use lsnrctl to make sure the services and which port to listen to.
[oracle@test ~]$ lsnrctl status
...
Service "ORCL" has 1 instance(s).
Instance "ORCL", status READY, has 1 handler(s) for this service...
Archivelog Mode and Destination
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 198693
Current log sequence 198695
As you can see, the database is NOARCHIVELOG.
RAC
For a RAC environment, don't bother to echo $ORACLE_HOME or $ORACLE_SID. It's more informative by using SRVCTL.
Database Names (in this server)
[oracle@primary01 ~]$ srvctl config database
compdb
smalldb
Database Details
Let's get down to the nitty-gritty from the above information.
[oracle@primary01 ~]$ srvctl config database -d compdb
Database unique name: compdb
Database name: compdb
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/COMPDB/PARAMETERFILE/spfile.289.887733743
Password file: +DATA/COMPDB/PASSWORD/pwdcompdb.276.887729933
Domain: example.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: primdb1,primdb2
Configured nodes: primary01,primary02
Database is administrator managed
Now you know the following things from the above:
- Database unique name
- Oracle home location ($ORACLE_HOME)
- Spfile location
- Password file location
- Database role
- ASM disk group name
- OSDBA and OSOPER
- Instance names of all nodes ($ORACLE_SID)
- Hostnames of all nodes
Service Name
You may already notice that the database default service name is not registered with srvctl. You have to find out by login as grid.
[grid@primary01 ~]$ lsnrctl status
...
Service "compdb.example.com" has 1 instance(s).
Instance "primdb1", status READY, has 1 handler(s) for this service...
Patch Level (of all components)
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production