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?

Standalone

For a standalone, you can do this:
  1. Oracle Home
  2. [oracle@test ~]$ echo $ORACLE_HOME
    /u01/app/oracle/product/11.2.0

  3. Oracle SID (Instance Name)
  4. [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.
  5. Service Name and Listener Port
  6. 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...

  7. Archivelog Mode and Destination
  8. 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.
  1. Database Names (in this server)
  2. [oracle@primary01 ~]$ srvctl config database
    compdb
    smalldb

  3. Database Details
  4. 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
  5. Service Name
  6. 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...

  7. Patch Level (of all components)
  8. 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 *