Our target was to Install Apex with below details:
Apex version: 4.0.2
Install Option: Full Development, we need this installation for development
HTTP Server: "Application Express Listener" "Standalone Mode", want to avoid installation of extra application server
Database: Oracle 11.2 Enterprise
Java: jdk1.6.0_10 [for apex listener]
Below is the prepared action plan I have prepared according to the docs provided with Apex:
1. Requirements Check list:
1.1 DB: >= 10.2.0.3
1.2 SHARED_POOL_SIZE='100M'
1.3 Disk Space Requirement
- Oracle Application Express software files on the file system: 1 GB if using full download (apex_4.0.2.zip).
- Oracle Application Express tablespace: 185 MB
- SYSTEM tablespace: 100 MB
- Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB
1.4 Oracle XML DB Requirement
1.5 Oracle Text Requirement
1.6 HTTP Server Requirements
- We will go for Oracle "Application Express Listener" because:
a. seperation from DB [Embaded PL/SQL gateway, runs in the database as part of the XML DB HTTP Protocol Listener]
b. no need of extra plagin [Oracle HTTP Server, uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database]
2. Install appex:
2.1 Download Oracle Appex and upzip to the desired location[http://www.oracle.com/technology/products/database/application_express/download.html]
2.2 Remove the password complexity from the default profile of DB
2.3 Create tablespace for appex [TBS_APEX]
2.4 for full development installation go to unzipped folder of appex and use below:
SQL> @apexins tablespace_apex tablespace_files tablespace_temp images
tablespace_apex: is the name of the tablespace for the Oracle Application Express application user.
tablespace_files: is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp: is the name of the temporary tablespace.
images: is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
for our case we used below:
SQL> conn / as sysdba
SQL> @apexins TBS_APEX TBS_APEX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.5 Change the Password for the ADMIN Account [When prompted enter a password for the ADMIN account]
SQL> conn / as sysdba
SQL> @apxchpwd
2.6 unlock APEX_PUBLIC_USER and change its password
SQL> conn / as sysdba
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password
3. install appex listener:
3.1 Download Oracle Application Express Listener and unzip to the desired location [http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html]
3.2 for installation we choose "Standalone Mode" as we donot need any exra components
3.4 start appex listener[we are using jdk1.6.0_10, failed to run using jre 1.3 & 1.5]:
> "C:\Program Files\Java\jdk1.6.0_10\bin\java" –jar -Dapex.images=[apex home]/images [appex listener home]\apex.war
-When prompted, specify the location of the folder containing the static resources used by Oracle Application Express
we used the images location created in the unziped apex folder:..../apex/images
- when prompt for username of "APEX Listener Administrator" we kept the default:[adminlistener] and gave the password [prompt only for the first run]
- when prompt for username of "APEX Listener Manager" we kept the default:[managerlistener] and gave the password [prompt only for the first run]
then go to "http://[ip or hostname]:[port, default is 8080]/apex/listenerConfigure" and configure APEX Listener:just give the connection details and apply
for more configuration refer to docs comes with APEX Listener
3.5 Enable Network Services in Oracle Database 11g:
- Failing to grant these privileges results in issues with:
a. Sending outbound mail in Oracle Application Express.Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.
b. Using Web services in Oracle Application Express.
c. PDF/report printing.
d. Searching for content in online Help (that is, using the Find link).
3.6 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user[as sysdba]:
-----------------------BEGIN SCRIPT-------------------------------------
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.
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, ensure 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 'APEX_040000'
-- 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, 'APEX_040000','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040000', 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',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
-----------------------------END SCRIPT----------------------------------
3.7 Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:
- Run the following command:
SQL > conn / as sysdba
SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
- If no value is returned by above, then create a new database role as shown in the following example:
SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;
SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;
- use the Oracle Text API to grant permission database role with the following statement:
SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');
4. JOB_QUEUE_PROCESSES:
4.1 JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs.
4.2 In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs
4.3 use below to check and set value for job_queue_processes:
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = [number];
5. Creating a Workspace Manually:
5.1 go to "http://hostname:port/apex/apex_admin" and login using "admin" [if will prompt for passwrod change, change it according to the rule]
5.2 Manage Workspaces > Create Workspace
- enter a workspace name and description and click Next >
- For Re-use existing schema, select No
Neter a schema name & password and quota
Click Next >
- Enter the Workspace administrator information and click Next >
- Create Workspace
6. Creating Oracle Application Express Users:6.1 go to "http://hostname:port/apex/apex_admin" and login using "admin"
6.2 create user > enter necessary info > create
7. Logging in to Your Workspace:
7.1 goto "http://hostname:port/apex"
7.2 enter the following:
Workspace field - Enter the name of your workspace
Username field - Enter your user name
Password field - Enter your case-sensitive password
7.3 login
Apex version: 4.0.2
Install Option: Full Development, we need this installation for development
HTTP Server: "Application Express Listener" "Standalone Mode", want to avoid installation of extra application server
Database: Oracle 11.2 Enterprise
Java: jdk1.6.0_10 [for apex listener]
Below is the prepared action plan I have prepared according to the docs provided with Apex:
1. Requirements Check list:
1.1 DB: >= 10.2.0.3
1.2 SHARED_POOL_SIZE='100M'
1.3 Disk Space Requirement
- Oracle Application Express software files on the file system: 1 GB if using full download (apex_4.0.2.zip).
- Oracle Application Express tablespace: 185 MB
- SYSTEM tablespace: 100 MB
- Oracle Application Express tablespace for each additional language (other than English) installed: 75 MB
1.4 Oracle XML DB Requirement
1.5 Oracle Text Requirement
1.6 HTTP Server Requirements
- We will go for Oracle "Application Express Listener" because:
a. seperation from DB [Embaded PL/SQL gateway, runs in the database as part of the XML DB HTTP Protocol Listener]
b. no need of extra plagin [Oracle HTTP Server, uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database]
2. Install appex:
2.1 Download Oracle Appex and upzip to the desired location[http://www.oracle.com/technology/products/database/application_express/download.html]
2.2 Remove the password complexity from the default profile of DB
2.3 Create tablespace for appex [TBS_APEX]
2.4 for full development installation go to unzipped folder of appex and use below:
SQL> @apexins tablespace_apex tablespace_files tablespace_temp images
tablespace_apex: is the name of the tablespace for the Oracle Application Express application user.
tablespace_files: is the name of the tablespace for the Oracle Application Express files user.
tablespace_temp: is the name of the temporary tablespace.
images: is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
for our case we used below:
SQL> conn / as sysdba
SQL> @apexins TBS_APEX TBS_APEX TEMP /i/
When Oracle Application Express installs it creates three new database accounts:
APEX_040000 - The account that owns the Oracle Application Express schema and metadata.
FLOWS_FILES - The account that owns the Oracle Application Express uploaded files.
APEX_PUBLIC_USER - The minimally privileged account used for Oracle Application Express configuration with Oracle HTTP Server and mod_plsql or Oracle Application Express Listener.
2.5 Change the Password for the ADMIN Account [When prompted enter a password for the ADMIN account]
SQL> conn / as sysdba
SQL> @apxchpwd
2.6 unlock APEX_PUBLIC_USER and change its password
SQL> conn / as sysdba
SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password
3. install appex listener:
3.1 Download Oracle Application Express Listener and unzip to the desired location [http://www.oracle.com/technology/products/database/application_express/html/apex_listener_download.html]
3.2 for installation we choose "Standalone Mode" as we donot need any exra components
3.4 start appex listener[we are using jdk1.6.0_10, failed to run using jre 1.3 & 1.5]:
> "C:\Program Files\Java\jdk1.6.0_10\bin\java" –jar -Dapex.images=[apex home]/images [appex listener home]\apex.war
-When prompted, specify the location of the folder containing the static resources used by Oracle Application Express
we used the images location created in the unziped apex folder:..../apex/images
- when prompt for username of "APEX Listener Administrator" we kept the default:[adminlistener] and gave the password [prompt only for the first run]
- when prompt for username of "APEX Listener Manager" we kept the default:[managerlistener] and gave the password [prompt only for the first run]
then go to "http://[ip or hostname]:[port, default is 8080]/apex/listenerConfigure" and configure APEX Listener:just give the connection details and apply
for more configuration refer to docs comes with APEX Listener
3.5 Enable Network Services in Oracle Database 11g:
- Failing to grant these privileges results in issues with:
a. Sending outbound mail in Oracle Application Express.Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.
b. Using Web services in Oracle Application Express.
c. PDF/report printing.
d. Searching for content in online Help (that is, using the Find link).
3.6 Granting Connect Privileges
The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user[as sysdba]:
-----------------------BEGIN SCRIPT-------------------------------------
DECLARE
ACL_PATH VARCHAR2(4000);
ACL_ID RAW(16);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040000
-- the "connect" privilege if APEX_040000 does not have the privilege yet.
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, ensure 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 'APEX_040000'
-- 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, 'APEX_040000','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,'APEX_040000', 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',
'APEX_040000', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
-----------------------------END SCRIPT----------------------------------
3.7 Enable Indexing of Online Help in Oracle Database 11gR2 and Higher:
- Run the following command:
SQL > conn / as sysdba
SQL> SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
This returns either NULL or the database role which is granted the ability to use an Oracle Text URL datastore.
- If no value is returned by above, then create a new database role as shown in the following example:
SQL> CREATE ROLE APEX_URL_DATASTORE_ROLE;
SQL> GRANT APEX_URL_DATASTORE_ROLE to APEX_040000;
- use the Oracle Text API to grant permission database role with the following statement:
SQL> EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');
4. JOB_QUEUE_PROCESSES:
4.1 JOB_QUEUE_PROCESSES determine the maximum number of concurrently running jobs.
4.2 In Oracle Application Express release 4.0, transactional support and SQL scripts require jobs
4.3 use below to check and set value for job_queue_processes:
SQL> SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = [number];
5. Creating a Workspace Manually:
5.1 go to "http://hostname:port/apex/apex_admin" and login using "admin" [if will prompt for passwrod change, change it according to the rule]
5.2 Manage Workspaces > Create Workspace
- enter a workspace name and description and click Next >
- For Re-use existing schema, select No
Neter a schema name & password and quota
Click Next >
- Enter the Workspace administrator information and click Next >
- Create Workspace
6. Creating Oracle Application Express Users:6.1 go to "http://hostname:port/apex/apex_admin" and login using "admin"
6.2 create user > enter necessary info > create
7. Logging in to Your Workspace:
7.1 goto "http://hostname:port/apex"
7.2 enter the following:
Workspace field - Enter the name of your workspace
Username field - Enter your user name
Password field - Enter your case-sensitive password
7.3 login
No comments:
Post a Comment