Oracle DBA notes

The following quick-reference notes cover common Oracle DBA tasks for maintaining an OTM on-premise database. These activities are typically performed by a DBA rather than an application administrator.

Start the database after an OS reboot:

Log in to the OS as a super user and start the Oracle Listener process. The Listener receives client connection requests and routes traffic to the database server.

lsnrctl start

Connect to the database as sysdba and issue the STARTUP command:

sqlplus / as sysdba

STARTUP;

To stop the database:

SHUTDOWN IMMEDIATE;

Reset password for a DB schema or user:

ALTER USER GLOGOWNER IDENTIFIED BY GLOGOWNER;

Unlock a DB account:

ALTER USER GLOGDBA ACCOUNT UNLOCK;

Recompile OTM DB invalid objects:

Log in to the server where OTM is installed, switch to the script8 directory, and connect as glogowner:

cd $OTM_HOME/glog/oracle/script8
ls recom*
sqlplus glogowner/glogowner@OTMDB

@recompile_invalid_objects.sql

To verify all objects are fixed:

SELECT object_name, owner, object_type, status
FROM   all_objects
WHERE  owner  = 'GLOGOWNER'
AND    status = 'INVALID';

This should return no rows.

Init.ora file:

Oracle DB configuration parameters are maintained in the init.ora file located at:

$ORACLE_HOME/dbs

For OTM, set open_cursors to greater than 3000 in this file. The default value of 300 is insufficient for OTM workloads.

Kill locked DB sessions:

The DBA_DDL_LOCKS table shows sessions holding DDL locks by schema name. Use it together with V$SESSION to identify SID and serial number:

SELECT vs.sid, vs.serial#
FROM   dba_ddl_locks ddl,
       v$session vs
WHERE  ddl.owner      = 'GLOGOWNER'
AND    vs.sid         = ddl.session_id;

To identify your own current session (e.g. from TOAD or SQL Developer) before killing anything:

SELECT sys_context('USERENV', 'SID') FROM dual;

To kill a session (as sysdba on the OS):

ALTER SYSTEM KILL SESSION 'sid,serial#';

Compile all objects in a schema:

EXEC DBMS_UTILITY.compile_schema(schema => 'GLOGOWNER');

Get all active DB sessions and their SQL:

SELECT s.username, s.sid, s.osuser, t.sql_id, sql_text
FROM   v$sqltext_with_newlines t,
       v$session s
WHERE  t.address    = s.sql_address
AND    t.hash_value = s.sql_hash_value
AND    s.status     = 'ACTIVE'
AND    s.username  <> 'SYSTEM'
ORDER BY s.sid, t.piece
/

Access Oracle Enterprise Manager (EM) Console:

Log in to the server as OS super user. Start or stop the DB Console:

cd $ORACLE_HOME/bin
./emctl start dbconsole
./emctl stop dbconsole

Find the hostname and EM port:

sqlplus / as sysdba

SELECT host_name FROM v$instance;

The EM port is in $ORACLE_HOME/install/readme.txt. Access the console at:

https://otm-server:1158/em

The first time you access it, the browser may throw a certificate exception — add the exception to continue.

EM user account issues:

Log in to EM using the SYSMAN user ID. Ensure SYSMAN and DBSNMP are not locked and have no password expiry:

SELECT username, account_status, lock_date, expiry_date, profile
FROM   dba_users
WHERE  username IN ('SYSMAN', 'DBSNMP');

ALTER USER SYSMAN  ACCOUNT UNLOCK;
ALTER USER DBSNMP  ACCOUNT UNLOCK;
ALTER PROFILE DEFAULT            LIMIT password_life_time UNLIMITED;
ALTER PROFILE MONITORING_PROFILE LIMIT password_life_time UNLIMITED;

To reset the SYSMAN password:

$emctl setpasswd dbconsole

Enter the SYSMAN password when prompted, then restart the console:

cd $ORACLE_HOME/bin
./emctl stop dbconsole
./emctl start dbconsole

TNS Names file path:

Log in to the server where the Oracle DB is installed:

cd $ORACLE_HOME/network/admin
vi tnsnames.ora

This file contains the DB connection details including hostname and port. To test connectivity by SID:

tnsping OTMDB

Remove password expiry for default DB users:

Connect as sysdba and check the current profile for a user:

SELECT p.profile AS "Profile",
       p.limit   AS "Limit"
FROM   dba_profiles p,
       dba_users u
WHERE  u.username          = 'SYSMAN'
AND    u.profile           = p.profile
AND    p.resource_name     = 'PASSWORD_LIFE_TIME';

If DEFAULT is the profile returned, remove the expiry:

ALTER PROFILE DEFAULT            LIMIT password_life_time UNLIMITED;
ALTER PROFILE MONITORING_PROFILE LIMIT password_life_time UNLIMITED;