Notes for Oracle as External database in IM&P

For a while I’ve been running an Oracle XE as my external database for chat persistence and message archiving. Trying to remember how to add an additional database when it was time configure Managed File Transfer was a pain for sure. Hopefully these notes will help me the next time!

[oracle@HOSTNAME ~]$ sqlplus / AS SYSDBA
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 6 13:37:44 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> CREATE TABLESPACE DB_CHATFILE DATAFILE '/u01/app/oracle/oradata/XE/db_chatfile.dbf' SIZE 100M
  2  AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT
  3  LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> CREATE USER CHATFILE IDENTIFIED BY "pAsSw0rd" DEFAULT TABLESPACE db_chatfile TEMPORARY
  2  TABLESPACE "TEMP" QUOTA UNLIMITED ON db_chatfile ACCOUNT UNLOCK;

User created.

SQL> GRANT DBA TO CHATFILE;

Grant succeeded.

SQL> ALTER SYSTEM SET service_names='XE, CHATCOMP, CHATPERS, CHATFILE';

System altered.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
[oracle@HOSTNAME dbs]$ exit

Once configured, verify that the newly configured database show up using lsnrctl;

[root@HOSTNAME bin]# lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-JUL-2015 13:42:07

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                06-JUL-2015 13:29:24
Uptime                    0 days 0 hr. 12 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/HOSTNAME/.../log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FQDN)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FQDN)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CHATCOMP" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "CHATFILE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "CHATPERS" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully
[root@HOSTNAME bin]#

The Cisco documentation does mention that it should be possible with a more restrictive grant, but that is something I’ll leave for another day to verify.