In the process of configuration, initiation and recovery of Oracle database backup, many Oracle database commands are needed. In this chapter, I will sort out the commands for your reference.

Oracle user and instance related commands

Linux/Unix platform

# ps -ef | grep [p]mon
orauxdb   18751     1  0 13:47 ?        00:00:00 ora_pmon_uxdb
oraprod   19026     1  0 13:49 ?        00:00:00 ora_pmon_prod

Each "pmon" process has an Oracle instance name. Each instance may run as a different Unix user.

Windows platform

Powershell's get service (aka "gsv") cmdlet can be used to find instance names on Windows:

PS> gsv OracleService*
Status   Name               DisplayName
------   ----               -----------
Running  OracleServiceORCL  OracleServiceORCL
Running  OracleServiceVS... OracleServiceVSAORA

Oracle services typically run under the SYSTEM account, while Commvault users are typically administrators. Just make sure that the Commvault user is a member of the ora DBA group

> net localgroup ora_dba
Alias name     ora_dba
Comment        Oracle DBA Group

Members

-------------------------------------------------------------------------------
Administrator
NT AUTHORITY\SYSTEM

Query Oracle Home path

Linux/Unix platform

# su - oracleuser
$ . oraenv                    ← Source the oraenv script and switch to the appropriate sid
ORACLE_SID = ? sid
$ echo $ORACLE_HOME           ← Check the ORACLE_HOME environment variable
/u01/oracle/product/11.2.0/dbhome_1

You can also get this information from the oratab file:

# grep sid /etc/oratab        ← The oratab file is not required but most installs will have one
sid:/u01/oracle/product/11.2.0/dbhome_1:Y

Windows platform

The following PowerShell command displays the Oracle home path:

PS> (gp HKLM:\software\oracle\KEY*).ORACLE_HOME
C:\app\oracle\product\11.1.0\db_1

Check the RMAN process of HUNG

Run the following query and check for sessions that appear to be invalid (for example, older login times).

$ sqlplus / as sysdba
SQL> select sid, serial#, status, to_char(logon_time, 'YYYY MM DD HH24:MI:SS'), program from v$session where program like '%rman%';
       SID    SERIAL# STATUS   TO_CHAR(LOGON_TIME, PROGRAM
---------- ---------- -------- ------------------- ------------------------------------------------
         9        591 INACTIVE 2013 11 06 12:41:50 rman@lx64ora3.unixdb.lab (TNS V1-V3)
       125       2503 INACTIVE 2013 11 06 12:41:50 rman@lx64ora3.unixdb.lab (TNS V1-V3)

View archive log delete policy

$ rman target /
RMAN> show archivelog deletion policy;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';

Query listening status

$ lsnrctl status

Find Oracle alarm log

The alert log is usually located at $oracle? Base / diag / RDBMS / dbname / instance / trace / alert? Sid.log, but can be changed. If it is not in the default location, check the diagnostic dest parameter:

$ sqlplus / as sysdba
SQL> show parameter diag
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle

Find archive log file path

$ sqlplus / as sysdba
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /nu01/oracle/archive
Oldest online log sequence     4120
Next log sequence to archive   4122
Current log sequence           4122

In some cases, the archive target will be set to use DB recovery file dest. In this case, look for the value of "DB \ recovery \ file \ dest":

SQL> show parameter db_recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------------
db_recovery_file_dest                string      /u01/oradata/fast_recovery_area
db_recovery_file_dest_size           big integer 4182M

Check TNS resolution

$ tnsping sid

Block Change Tracking

To check if block change tracking is enabled, run the following SQLPlus query.

$ sqlplus / as sysdba
SQL> select status from v$block_change_tracking;
STATUS
----------
DISABLED

Check sysdba / sysbackup privileges

Use the following query to check if oracle users have sysdba privileges

$ sqlplus / as sysdba
SQL> select username,sysdba from v$pwfile_users;
USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE
SYSDG                          FALSE
SYSKM                          FALSE
MRBACKUP                       FALSE

Starting with Oracle 12c, there is a sysbackup role available for backup. This role is supported from Commvault V11.

SQL> select username,sysdba,sysbackup from v$pwfile_users;
USERNAME                       SYSDB SYSBA
------------------------------ ----- -----
SYS                            TRUE  FALSE
SYSDG                          FALSE FALSE
SYSBACKUP                      FALSE TRUE
SYSKM                          FALSE FALSE
C##MRBACKUP                    FALSE TRUE

Check Oracle archive log mode

For online backup, the database must be running in ARCHIVELOG mode. If running in non archive mode, only offline backups are allowed.

$ sqlplus / as sysdba
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     6
Current log sequence           8

Oracle compression

To see if Oracle compression is enabled, log in to RMAN and check the configuration of the SBT? Tape device.

$ rman target /
RMAN> show device type;
RMAN configuration parameters for database with db_unique_name CVUNIXDB are:
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

Above, compression is enabled on SBT? Tape, but disabled on DISK. Note that Commvault backups use SBT_TAPE devices.

If the SBT? Tape device is not listed at all, compression is not used.