Category Archives: RDBMS

Oracle DB Express is the new replacement for Oracle DB Console in the Oracle 12c database. Here are some first steps to get started.

Oracle DB Express makes use of what is called the “XDB Protocol Server”, which among others supports the Oracle APEX standalone PL/SQL Gateway. For people who are unknown still how to set this up, hereby some ideas how to get started with this cool new administrative alternative.

First enable the HTTP functionality of the XDB Protocol Server. You are allowed to do this via the SYS account or have the XDBADMIN role.

• Check if it is enabled via:

[oracle@orcl12 ~]$ sqlplus / AS sysdba
 
SQL*Plus: Release 12.1.0.1.0 Production ON Wed Jun 26 11:21:23 2013
 
Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, Advanced Analytics AND REAL Application Testing options
 
SQL> SELECT dbms_xdb_config.gethttpport() FROM dual;
 
DBMS_XDB_CONFIG.GETHTTPPORT()
----------------------
		     0

• Or via on the command prompt:

 
[oracle@orcl12 ~]$ lsnrctl STATUS
 
LSNRCTL FOR Linux: Version 12.1.0.1.0 - Production ON 26-JUN-2013 11:23:48
 
Copyright (c) 1991, 2013, Oracle.  ALL rights reserved.
 
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl12.homework.LOCAL)(PORT=1521)))
STATUS OF the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR FOR Linux: Version 12.1.0.1.0
START DATE                26-JUN-2013 11:12:38
Uptime                    0 days 0 hr. 11 MIN. 11 sec
Trace Level               off
Security                  ON: LOCAL OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl12/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl12.homework.LOCAL)(PORT=1521)))
Services Summary...
Service "orcl12.homework.local" has 1 instance(s).
  Instance "orcl12" STATUS READY, has 1 handler(s) FOR this service...
Service "orcl12.homework.local" has 1 instance(s).
  Instance "orcl12", STATUS READY, has 1 handler(s) FOR this service...
The command completed successfully

Via the missing HTTP/RAW entry in the listener status overview or the return value “0″ while using DBMS_XDB_CONFIG, you can check that the listener is not listening for HTTP calls. Be also aware that DBMS_XDB_CONFIG is now, from Oracle 12c and onwards, the package to use to configure XDB, instead of the deprecated functions and procedures in DBMS_XDB.

• Enable, for example on port 8080, and check HTTP access:

SQL> EXEC dbms_xdb_config.sethttpport(8080);
 
PL/SQL PROCEDURE successfully completed.
 
SQL> SELECT dbms_xdb.gethttpport() FROM dual;
 
DBMS_XDB.GETHTTPPORT()
----------------------
		  8080

Be aware that assigning port numbers, like port 80, under 1024 need privileged OS user rights like root or administrator privileges. Most of the time, when installing on Windows, the Oracle software owner has these rights, but when on Linux and others, extra steps have to be taken to get this working. See the Oracle XMLDB Developer Guide documentation on how to achieve this.

I normally register this functionality with the listener immediately via a “alter system register” and enable 5 shared servers for it, so browser response is a bit more snappy. You need SYS or DBA role privileges to do the following:

SQL> ALTER system register;
 
System altered.
 
SQL> ALTER system SET shared_servers=5 scope=BOTH;
 
System altered.

If you check via DBMS_XDB_CONFIG or the listener status now, you will see your changes and the listener actively listening on port 8080 for HTTP calls.

SQL> SELECT dbms_xdb_config.gethttpport() FROM dual;
 
DBMS_XDB_CONFIG.GETHTTPPORT()
-----------------------------
			 8080

and the listener status output will now show…

 
[oracle@orcl12 ~]$ lsnrctl STATUS
 
LSNRCTL FOR Linux: Version 12.1.0.1.0 - Production ON 26-JUN-2013 11:23:48
 
Copyright (c) 1991, 2013, Oracle.  ALL rights reserved.
 
Connecting TO (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl12.homework.LOCAL)(PORT=1521)))
STATUS OF the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR FOR Linux: Version 12.1.0.1.0
START DATE                26-JUN-2013 11:12:38
Uptime                    0 days 0 hr. 11 MIN. 11 sec
Trace Level               off
Security                  ON: LOCAL OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl12/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl12.homework.LOCAL)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcl12.homework.LOCAL)(PORT=8080))(Presentation=HTTP)(SESSION=RAW))
Services Summary...
Service "orcl12.homework.local" has 1 instance(s).
  Instance "orcl12" STATUS READY, has 1 handler(s) FOR this service...
Service "orcl12.homework.local" has 1 instance(s).
  Instance "orcl12", STATUS READY, has 1 handler(s) FOR this service...
The command completed successfully

In Oracle 12c, authentication is now supported for “basic” and “digest” authentication besides the already supported TCP and TCPS.

Digest Access Authentication Access to the Repository

Users can now access Oracle XML DB Repository using digest access authentication (also known as digest authentication), in addition to basic authentication. This provides encryption of user credentials (name, password, etc.) without the overhead of complete data encryption.

Note that user credentials are case-sensitive. In particular, a user name to be authenticated must exactly match the name as it was created (which by default is all uppercase).

You can now connect to DB Express via (default) http://localhost:8080/em or, instead of “localhost”, the TCP/IP number of your database server or (even better), while using the Fully Qualified Domain Name for your database server. In my case that could have been: http://orcl12.homework.local:8080/em

Issues…?

Most of the problems to get this started are caused by incorrect server name resolution, that is that the listener can not find or is bound to the incorrect hostname of your database server. The easiest way to check this is to stop the listener via “lsnrctl stop”, remove (temporarily) or rename the listener.ora file in (most of the time) $ORACLE_HOME/network/admin, start the listener without a listener.ora file in place via “lsnrctl start” and check after a while how the “listener status” recognizes your Fully Qualified Domain Name (FQDN). If the hostname reference is different than expected, alter the content of you host in the listener.ora file or create an extra entry in your hosts file (/etc/hosts) in the following manner: {TCP/IP Number} {Fully Qualified Domain Name} {Hostname}. So for example “10.1.0.11 myserver.domain.local myserver”. In most cases, that should do the trick…

Have fun.