Skip to content

How to Know Everything about an Unfamiliar Database

  • by

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

Leave a Reply

Your email address will not be published. Required fields are marked *