This section describes tasks that you need to complete Oracle Application Express configuration after you finish installing the Oracle Real Application Clusters (Oracle RAC) software.
This chapter contains the following topics:
If you had a previous Oracle Application Express installation, then after the Oracle RAC installation completes, you need to restart the Oracle Application Express processes that you stopped before you began the installation, such as the listener. In addition, restart Oracle HTTP Server.
If you are already running Oracle Application Express 3.0, then check the Oracle Application Express page on the Oracle Technology Network (OTN) at the following URL for information about patch set releases or later versions of Oracle Application Express:
http://www.oracle.com/technology/products/database/application_express/index.html
Upgrading to Oracle Database 11g will not patch an Application Express 3.0 instance to Application Express 3.0.1.
Oracle Application Express requires access either to the embedded PL/SQL gateway, or to Oracle HTTP Server and mod_plsql. Review the following information to select an HTTP server.
This section contains the following topics:
Oracle recommends that you use Oracle HTTP Server with modplsql, as it permits you to specify a connection in the service name format, so that one HTTP Server can access all nodes. This section contains instructions for configuring that HTTP server.
Oracle recommends that you do not select the embedded PL/SQL gateway option for Oracle RAC installations. The embedded PL/SQL gateway uses an HTTP Server built into the database instance, and because of this, it does not take advantage of the Oracle RAC shared architecture.
To use an embedded PL/SQL gateway as the HTTP server with RAC, the gateway must be configured on each node, and users of Oracle Application Express would have to specify which node they would like to access in the URL. If the node fails, then the embedded PL/SQL gateway fails as well, and the service is not failed over to surviving nodes.
Oracle HTTP Server uses the mod_plsql plug-in to communicate to the Oracle Application Express engine within Oracle Database. It functions as a communication broker between the Web server and the Oracle Application Express objects in Oracle Database. More specifically, it maps browser requests into database stored procedure calls over a SQL*Net connection.
Note that this configuration consists of three tier architecture: a Web browser, Oracle HTTP Server (ohs) with mod_plsql, and an Oracle database containing Oracle Application Express.
Whether you are loading a new installation or upgrading from a previous release, you must copy the images directory from the top level of the $ORACLE_HOME/apex directory to the location on the file system containing the Oracle home for Oracle HTTP Server.
This section includes the following topics:
During an upgrade, you must overwrite your existing images directory. Before you begin the upgrade, to ensure that you can revert to the previous version, Oracle recommends that you create a copy of your existing images directory for Oracle Application Express, indicating the release number of the images (for example, images_2_0).
To locate the path to the images directory on the file system, locate the text alias /i/ in one of the following files, depending on your existing version release:
Oracle9i HTTP Server release 2—see the httpd.conf file.
Oracle HTTP Server 11g—see the dads.conf file.
Oracle Application Server 10g—see the marvel.conf file.
When you locate the images directory path, Oracle recommends that you copy the existing images directory to a backup location. Doing this enables you to revert to the previous release, if that becomes necessary.
After you copy the existing images directory, use the following command syntax to copy the apex\images directory from the Oracle Database 11g home to the existing images directory path, overwriting the existing images:
Existing Oracle Application Server 10g
cp -rf ORACLE_BASE/ORACLE_HOME/apex/images\ ORACLE_BASE/ORACLE_HTTPSERVER_HOME/Apache
Existing Oracle HTTP Server 11g:
cp -rf ORACLE_BASE/ORACLE_HOME/apex/images\ ORACLE_BASE/ORACLE_HTTPSERVER_HOME/ohs
In the preceding syntax examples:
ORACLE_BASE is an Oracle software base directory, in which Oracle home directories are installed, such as u01/app/oracle.
ORACLE_HTTPSERVER_HOME is the existing Oracle Application Server or Oracle HTTP Server Oracle home
ORACLE_HOME is the Oracle Database 11g Oracle home
For example:
cp -rf u01/app/oracle/10_2/db_1/apex/images \ u01/app/oracle/OAS_11_0/ohs
After installation, copy the directory apex/images, using a command similar to the following:
cp -rf ORACLE_BASE/ORACLE_HOME/apex/images\ ORACLE_BASE/ORACLE_HTTPSERVER_HOME/ohs
This section describes how to configure different versions of Oracle HTTP Server with mod_plsql in a new installation.
Topics in this section include:
Configuring Oracle HTTP Server Release 9.0.3 in a New Installation
Configuring Oracle HTTP Server or Oracle Application Server in a New Installation
Oracle highly recommends that you configure and use a Secure Sockets Layer (SSL) to ensure that passwords and other sensitive data are not transmitted in clear text in HTTP requests. Without the use of SSL, passwords potentially could be exposed, compromising the security of your Application Express instance.
SSL is an industry standard protocol that uses RSA public key cryptography in conjunction with symmetric key cryptography to provide authentication, encryption, and data integrity.
In Oracle HTTP Server release 9.0.3, the file wdbsvr.app contains information about the Database Access Descriptor (DAD) to access Oracle Application Express. A DAD is a set of values that specify how the Oracle HTTP Server component modplsql connects to the database server to fulfill an HTTP request.
To change the password for the ADMIN account:
Change your working directory to $ORACLE_HOME/apex.
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:
$ sqlplus sys/SYS_password as sysdba
Enter the following command followed by the new password:
@apxxepwd.sql password
For example to change the password to apex1234:
@apxxepwd.sql password apex1234
To specify the password in the DAD file, use the following procedure to the password for the database user APEX_PUBLIC_USER:
Start SQL*Plus, and connect to the database where Oracle Application Express is installed as SYS. For example:
$ sqlplus sys/SYS_password as sysdba
Enter the following SQL statement, where PASSWORD is the new password:
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY PASSWORD
To create the DAD, modify the file wdbsvr.app, and add an entry for Oracle Application Express.
To modify the wdbsvr.app file:
Using a text editor, open the file wdbsvr.app, which is in the path ORACLE_BASE/ORACLE_HTTPSERVER_HOME/modplsql/cfg/
Add an entry for Oracle Application Express using the following syntax. Only change the settings indicated in italics.
[DAD_htmldb] connect_string = localhost:1521:orcl password = apex username = apex_public_user default_page = apex document_table = wwv_flow_file_objects$ document_path = docs document_proc = wwv_flow_file_mgr.process_download reuse = Yes enablesso = No stateful = STATELESS_RESET nls_lang = American_America.AL32UTF8
Where:
connect_string refers to the host ID, port number, and Oracle9i database where Oracle Application Express was installed. Use the format host:port:sid.
If you have an Oracle9i version of Oracle HTTP Server you want to use, and it is installed in the same Oracle home as the database you specified for use with Oracle Application Express, then leave this parameter blank.
password is the password for the Oracle Application Express ADMIN account you specified in "Changing the Password for the APEX_PUBLIC_USER Database User".
nls_lang determines the language setting of the DAD. The character set portion of the nls_lang value must always be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8.
If either the territory portion or the language portion of the NLS settings contains a space, then you must wrap the value in double quotes, as shown in the following example:
nls_lang = "ENGLISH_UNITED KINGDOM.AL32UTF8"
You can find information about your database's NLS settings by querying the view NLS_DATABASE_PARAMETERS as shown in the following example:
SELECT parameter,value FROM nls_database_parameters WHERE PARAMETER = 'NLS_CHARACTERSET';
Leave the remaining settings, including the username setting, as they appear in the preceding example.
Save and exit the wdbsvr.app file.
You need to modify the httpd.conf file to include an alias that points to the file system path where you copied the images directory. You may also need to modify the httpd.conf file to add two new MIME types to support SQL Workshop.
To modify the httpd.conf file:
Using a text editor, open the following file:
ORACLE_BASE/Apache/Apache/conf/httpd.conf
Add an alias entry that points to the file system path where you copied the images directory. For example:
alias /i/ "/home/oracle/OraHome1/Apache/Apache/images/"
Add the following lines to the httpd.conf file, if they are not already in the file:
AddType text/xml xbl AddType text/x-component htc
If you are upgrading from Oracle HTML DB 2.0, then these MIME types should be defined in the file already.
Save and exit the httpd.conf file.
Restart the Oracle HTTP Server with the changed DAD setting.
To stop and restart the Oracle HTTP Server, enter commands using the following syntax, where ORACLE_BASE is the path to the Oracle base directory:
ORACLE_BASE/Apache/Apache/bin/apachectl stop ORACLE_BASE/Apache/Apache/bin/apachectl start
Note:
If Oracle HTTP Server is set to listen on port 1024 or lower, then you must run the stop and start commands as a privileged user, such asroot.See Also:
Oracle HTTP Server Administrator's GuideOracle Application Express must have access to Oracle HTTP Server with mod_plsql. Perform the postinstallation steps in this section if one of the following conditions is true:
You are running Oracle HTTP Server 11g or Oracle Application Server 10g.
Oracle HTTP Server is installed in an Oracle home.
You have not previously configured Oracle HTTP Server to work with Oracle Application Express.
These instructions do not apply if you are running Oracle HTTP Server release 9.0.3. For more information on configuring Oracle HTTP Server release 9.0.3, see "Configuring Oracle HTTP Server in a New Installation".
If these instructions do apply to your installation, then complete the following steps:
Note:
Within the context of this section, the Apache home directory (ORACLE_HTTPSERVER_HOME) is the location where Oracle HTTP Server is installed.You must change the password for the Oracle Application Express ADMIN account.
To change the password for the ADMIN account:
Change your working directory to $ORACLE_HOME/apex.
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:
$ sqlplus sys/SYS_password as sysdba
Using the following syntax, enter the new password:
> @apxxepwd.sql password
For example, to change the password to apex1234:
> @apxxepwd.sql apex1234
When configuring Oracle HTTP Server for Oracle Application Express in a new installation, the database user APEX_PUBLIC_USER must be an unlocked account. To unlock the account for database user APEX_PUBLIC_USER, complete the following steps:
Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS. For example:
$ $ORACLE_HOME/bin/sqlplus
SQL> CONNECT SYS as SYSDBA
Enter password: SYS_password
Run the following command:
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
To specify the password in the DAD file, use the following procedure to change the password for the database user APEX_PUBLIC_USER:
Start SQL*Plus, and connect to the database where Oracle Application Express is installed as SYS. For example:
$ sqlplus sys/SYS_password as sysdba
Enter the following SQL statement, where PASSWORD is the new password:
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY PASSWORD
If this is a new installation of Oracle Application Express, then you must edit the dads.conf file.
To edit the dads.conf file:
Using a text editor, open the dads.conf file for your release:
Oracle Application Server 10g
ORACLE_HTTPSERVER_HOME/Apache/modplsql/conf/dads.conf
For example:
$ vi /u01/product/oas/Apache/modplsql/conf/dads.conf
Oracle HTTP Server 11g:
ORACLE_HTTPSERVER_HOME/ohs/modplsql/conf/dads.conf
For example:
$ vi /u01/product/oas/ohs/modplsql/conf/dads.conf
Copy the following code text into the dads.conf file.
In the text, replace the variables $ORACLE_HOME, host, port, service_name, and apex_public_user_password with values appropriate for your system. Note that apex_public_user_password is the password that you defined in the section "Changing the Password for the APEX_PUBLIC_USER Database User".
Alias /i/ "ORACLE_BASE/ORACLE_HTTPSERVER_HOME/ohs/images" AddType text/xml xbl AddType text/x-component htc <Location /pls/apex> Order deny,allow PlsqlDocumentPath docs AllowOverride None PlsqlDocumentProcedure wwv_flow_file_mgr.process_download PlsqlDatabaseConnectString host:port:service_name ServiceNameFormat PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8 PlsqlAuthenticationMode Basic SetHandler pls_handler PlsqlDocumentTablename wwv_flow_file_objects$ PlsqlDatabaseUsername APEX_PUBLIC_USER PlsqlDefaultPage apex PlsqlDatabasePassword apex_public_user_password Allow from all </Location>
Locate the line containing PlsqlNLSLanguage.
The PlsqlNLSLanguage setting determines the language setting of the DAD. The character set portion of the PlsqlNLSLanguage value must be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8. For example:
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
Save and exit the dads.conf file.
To stop and restart Oracle HTTP Server, run the following commands:
ORACLE_HTTPSERVER_HOME/opmn/bin/opmnctl stopproc ias-component=HTTP_Server ORACLE_HTTPSERVER_HOME/opmn/bin/opmnctl startproc ias-component=HTTP_Server
The Oracle Application Express interface is translated into German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese. A single instance of Oracle Application Express can be installed with one or more of these translated versions. At runtime, each user's Web browser language settings determine the specific language version.
The translated version of Oracle Application Express should be loaded into a database that has a character set that can support the specific language. If you attempt to install a translated version of Oracle Application Express into a database that does support the character encoding of the language, the installation may fail or the translated Oracle Application Express instance may appear corrupt when run. The database character set AL32UTF8 supports all the translated versions of Oracle Application Express.
You can manually install translated versions of Oracle Application Express using SQL*Plus. The installation files are encoded in AL32UTF8.
Note:
Regardless of the target database character set, to install a translated version of Oracle Application Express, you must set the character set value of theNLS_LANG environment variable to AL32UTF8 prior to starting SQL*Plus.The following examples illustrate valid NLS_LANG settings for loading Oracle Application Express translations:
American_America.AL32UTF8 Japanese_Japan.AL32UTF8
Whether you are installing for the first time or upgrading from a previous release, you must run the script load_lang.sql to run a translated version of Oracle Application Express.
To install a translated version of Oracle Application Express:
Set the NLS_LANG environment variable, making sure that the character set is AL32UTF8. For example:
Bourne or Korn Shell
$ NLS_LANG=American_America.AL32UTF8$ export NLS_LANG
C Shell
% setenv NLS_LANG American_America.AL32UTF8
Start SQL*Plus and connect to the target database as SYS.
Run the following statement:
> ALTER SESSION SET CURRENT_SCHEMA = FLOWS_03000;
Run the appropriate language specific script. For example:
> @load_de.sql
The installation scripts are located in subdirectories identified by a language code in the unzipped distribution $ORACLE_HOME/apex/builder. For example, the German version is located in $ORACLE_HOME/apex/builder/de and the Japanese version is located in $ORACLE_HOME/apex/builder/ja. Within each of these directories, there is a language loading script identified by the language code (for example, load_de.sql or load_ja.sql).
JOB_QUEUE_PROCESSES determines the maximum number of concurrently running jobs. In Oracle Application Express release 3.0, transactional support and SQL scripts require jobs. If JOB_QUEUE_PROCESSES is not enabled and working properly, then you cannot successfully run a script.
This section includes the following topics:
You can view the number of number of JOB_QUEUE_PROCESSES using one of the following methods:
After installing or upgrading Oracle Application Express to release 3.0, you can view the number of JOB_QUEUE_PROCESSES in the installation log files.
You can view the number of JOB_QUEUE_PROCESSES on the About Application Express page.To view the About Application Express page:
Log in to Oracle Application Express.
On the Administration list, click About Application Express.
The current number JOB_QUEUE_PROCESSES displays at the bottom of the page.
You can view the number of JOB_QUEUE_PROCESSES from SQL*Plus by running the following SQL statement:
> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
You can change the number of JOB_QUEUE_PROCESSES by running a SQL statement in SQL*Plus, using the following procedure:
Log in to the database as SYSDBA using SQL*Plus.
In SQL*Plus run the following SQL statement, where the variable number represents the number of processes you want to set:
> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = number
For example, running the statement ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20 sets JOB_QUEUE_PROCESSES to 20.
The parameter PlsqlDatabasePassword specifies the password for logging in to the database. You can use the utility dadTool.pl to obfuscate passwords in the dads.conf file.
You can find the dadTool.pl utility in the following directory:
ORACLE_HTTPSERVER_HOME/ohs/modplsql/conf
You access the Oracle Application Express home page in a Web browser. To view or develop Oracle Application Express applications, the Web browser must support JavaScript and the HTML 4.0 and CSS 1.0 standards.
This section contains the following topics
In the Oracle Application Express development environment, users log in to a shared work area called a workspace. Users are divided into four primary roles:
Workspace administrators are users who perform administrator tasks specific to a workspace such as managing user accounts, monitoring workspace activity, and viewing log files.
Developers are users who create and edit applications. Developers can have their own workspace or share a workspace.
End users have no development privileges. You define end users so that they can access applications that do not use an external authentication scheme.
Oracle Application Express administrators are superusers that manage an entire hosted instance using the Application Express Administration Services application.
How you set up Oracle Application Express depends upon your user role. If you are a developer accessing a hosted development environment, then an administrator must grant you access to a workspace. If you are an Oracle Application Express administrator, then you must perform the following steps:
Log in to Oracle Application Express Administration Services. Oracle Application Express Administration Services is a separate application for managing an entire Oracle Application Express instance. You log in using the ADMIN account and password created or reset during the installation process.
Specify a provisioning mode. In Oracle Application Express Administration Services, you need to determine how the process of creating (or provisioning) a workspace will work in your development environment.
Create a Workspace. A workspace is a virtual private database allowing multiple users to work within the same Oracle Application Express installation while keeping their objects, data and applications private. Each workspace has a unique ID and name. An Oracle Application Express administrator can create a workspace manually or have users submit requests.
Log in to a Workspace. Once you create a workspace in Oracle Application Express Administration Services, return to the Oracle Application Express Login page and log in to that workspace.
See Also:
Oracle Database 2 Day + Application Express Developer's Guide or "Quick Start" on Oracle Database Application Express User's GuideBy default, the ability to interact with network services is disabled in Oracle Database 11g release 1 (11.1). To use Oracle Application Express with Oracle Database 11g release 1 (11.1), you need to use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privilege to any host for the FLOWS_03000 database user. Failing to grant these privileges results in issues with the following:
Sending outbound mail in Oracle Application Express
Users can call methods from the APEX_MAIL package, but issues arise when sending outbound e-mail.
Using Web services in Oracle Application Express.
Using PDF/report printing
Searching for content in online Help (that is, using the Find link).
The following example demonstrates how to grant connect privileges to any host for the FLOWS_03000 database user:
Note:
To run the examples, the compatible initialization parameter of the database must be set at least to11.1.0.0.0. In Oracle 11g Database, the parameter is already set by default. However, you will have to set this parameter if you are upgrading to Oracle 11g Database from a prior version.See Also:
"Creating and Configuring an Oracle Database" in the Oracle Database Administrator's Guide for information about changing database compatible initialization parameters
SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- Before checking the privilege, make sure that the ACL is valid
-- (for example, does not contain stale references to dropped users).
-- If it does, the following exception will be raised:
--
-- ORA-44416: Invalid ACL: Unresolved principal 'FLOWS_03000'
-- ORA-06512: at "XDB.DBMS_XDBZ", line ...
--
SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
FROM XDB.XDB$ACL A, PATH_VIEW P
WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
EQUALS_PATH(P.RES, ACL_PATH) = 1;
DBMS_XDBZ.ValidateACL(ACL_ID);
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'FLOWS_03000',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'FLOWS_03000', TRUE, 'connect');
END IF;
EXCEPTION
-- When no ACL has been assigned to '*'.
WHEN NO_DATA_FOUND THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
'ACL that lets power users to connect to everywhere',
'FLOWS_03000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
If you receive the error ORA-44416: Invalid ACL, then use the following query to identify the invalid access control list (ACL):
REM Show the dangling references to dropped users in the ACL that is assigned
REM to '*'.
SELECT ACL, PRINCIPAL
FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
NACL.ACLID = ACE.ACLID AND
NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);
After you identify the invalid ACL, run the following script to fix the ACL:
DECLARE
ACL_ID RAW(16);
CNT NUMBER;
BEGIN
-- Look for the object ID of the ACL currently assigned to '*'
SELECT ACLID INTO ACL_ID FROM DBA_NETWORK_ACLS
WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;
-- If just some users referenced in the ACL are invalid, remove just those
-- users in the ACL. Otherwise, drop the ACL completely.
SELECT COUNT(PRINCIPAL) INTO CNT FROM XDS_ACE
WHERE ACLID = ACL_ID AND
EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);
IF (CNT > 0) THEN
FOR R IN (SELECT PRINCIPAL FROM XDS_ACE
WHERE ACLID = ACL_ID AND
NOT EXISTS (SELECT NULL FROM ALL_USERS
WHERE USERNAME = PRINCIPAL)) LOOP
UPDATE XDB.XDB$ACL
SET OBJECT_VALUE =
DELETEXML(OBJECT_VALUE,
'/ACL/ACE[PRINCIPAL="'||R.PRINCIPAL||'"]')
WHERE OBJECT_ID = ACL_ID;
END LOOP;
ELSE
DELETE FROM XDB.XDB$ACL WHERE OBJECT_ID = ACL_ID;
END IF;
END;
/
REM commit the changes.
COMMIT;
When the ACL has been fixed, run the first script in this section to apply the ACL to the FLOWS_03000 user. See "Granting Connect Privileges".