Tuesday 10 March 2015
A. Install XE 11g
1. Download Oracle XE 11g for Windows 32-bit
2. Unzip the downloaded file OracleXE112_Win32.zip.
3. Move inside folder Disk1 -> and run setup.exe file (Double Click file).
4. Verify if XE installed.
From Run, type cmd.
In cmd window, connect as sys with sysdba
Command: sqlplus / as sysdba
select name, open_mode, log_mode from v$database;
5. Type exit in sqlplus to quit.
B. Install APEX4.2
1. Download APEX4.2 from Oracle Site for Windows 32-bit.
a. Run -> Type cmd -> In cmd window, login in sqlplus as sys with sysdba
Command: sqlplus / as sysdba
If above command fails then try this: sqlplus /@//localhost:1521/XE as sysdba;
b. Find the path where data files are stored.
Command: select file_name from dba_data_files;
c. Create tablespace and give the obtained file path as data file path.
Command: create tablespace apex datafile ‘C:\ORACLEXE\APP\ORACLE\ORADATA\XE\apexdata01.dbf’ size 1G autoextend on;
4. Type exit to quit sqlplus.
6. Now, in this path, login in sqlplus as sys with sysdba
Command: sqlplus / as sysdba
If above command is not working try this : sqlplus /@//localhost:1521/XE as sysdba;
7. Run the script apexins.sql
Command: @apexins.sql apex apex TEMP /i/;
Where apex=apex tablespace , TEMP= temporary tablespace, /i/=image path
This starts installation of APEX4.2. It takes about 30 minutes to complete installation.
/* For Information…………………………………………………………………………………………………………………………………….
When Oracle Application Express installs, it creates three new database accounts:
· APEX_040200 - 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 is used for Oracle Application Express configuration with Oracle Application Express Listener or Oracle HTTP Server and mod_plsql.
If you configured RESTful Web services, then these additional accounts will be created:
· APEX_REST_PUBLIC_USER - The account used when invoking RESTful Services definitions stored in Oracle Application Express.
· APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
-------------------------------------------------------------------------------------------------------------------------------------*/
8. After installation, log file gets generated in your apex folder location.
Log file has following entry:
8. After installation, log file gets generated in your apex folder location.
Log file has following entry:
9. Now, check the validity of installation.
In cmd, login in sqlplus with sys as sysdba and run the below command:
10. Change the Password for the ADMIN Account
Now, in cmd window, go to and inside apex folder using cd
Login as sys with sysdba
Execute the below script:
@apxchpwd.sql
It will prompt for admin password. Give it.
11. Configure RESTful Services (Optional)
a. Now, in cmd window, go to and inside apex folder using cd
b. Login as sys with sysdba
c. Execute the below script:
@apex_rest_config.sql
When prompted, enter a password for the APEX_LISTENER and
APEX_REST_PUBLIC_USER accounts.
APEX_LISTENER ->xxx
APEX_REST_PUBLIC_USER -> xxx
/* For information……………………………………………………………………………………………………………………………………………………..
When configuring RESTful Services in Oracle Application Express, it creates two new database accounts:
· APEX_LISTENER - The account used to query RESTful Services definitions stored in Oracle Application Express.
· APEX_REST_PUBLIC_USER - The account used when calling RESTful Services definitions stored in Oracle Application Express.
---------------------------------------------------------------------------------------------------*/
12 . Unlock APEX_PUBLIC_USER account
a. Login as sys with sysdba
b. Execute below commands:
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxx;
b. Execute below commands:
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY xxx;
13. View JOB_QUEUE_PROCESSES from SQL*Plus
a. Login as sys with sysdba
b. Execute below command:
SELECT VALUE FROM v$parameter WHERE NAME = 'job_queue_processes'
ALTER SYSTEM SET JOB_QUEUE_PROCESSES =20;
14. Grant Connect Privileges
a. Login as sys with sysdba
b. Copy-Paste below code in sqlplus and run it (/ command).
DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 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;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', 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_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
a. Login as sys with sysdba
b. Copy-Paste below code in sqlplus and run it (/ command).
DECLARE
ACL_PATH VARCHAR2(4000);
BEGIN
-- Look for the ACL currently assigned to '*' and give APEX_040200
-- the "connect" privilege if APEX_040200 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;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040200',
'connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
'APEX_040200', 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_040200', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;
15. Follow this step only if error arise in Step 14
Troubleshooting an Invalid ACL Error
If you receive an ORA-44416: Invalid ACL error after running the previous script, use the following query to identify the invalid 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);
Next, run the following code 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;
16. Load images (Specify the image directory):
a. Now, in cmd window, go to and inside apex folder using cd
b. Login as sys with sysdba
c. Execute the below script:
Command: @apxldimg.sql
When prompted, enter value for 1 (image directory )
eg: C:\apex4.2
OR
@apxldimg.sql C:
1 17. Enable Oracle XML DB HTTP Server
In sqlplus, execute the below command:
EXEC DBMS_XDB.SETHTTPPORT(8080);
Commit;
1 18. Launch APEX4.2 using below URL:
1 19. If you are not able to access Apex URL with IP address then Make global Access to Apex URL
EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
This procedure restricts all listener end points of the XML DB HTTP server to listen either only on the localhost interface (when l_access is set to TRUE) or to listen on both localhost and non-localhost interfaces (when l_access is set to FALSE).
2 20 . SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
User altered.
ALTER USER anonymous ACCOUNT UNLOCK;
ALTER USER xdb ACCOUNT UNLOCK;
ALTER USER apex_public_user ACCOUNT UNLOCK;
ALTER USER flows_files ACCOUNT UNLOCK;
SQL> select username,account_status from dba_users;
SQL> alter user APEX_040200 identified by Jade_123;
SQL> alter user xdb identified by Jade_123;
SQL> alter user FLOWS_FILES identified by Jade_123;
SQL> alter user APEX_040000 account lock;
3 21. If the Apex URL is only working on local machine but not on another.
Then do the firewall setting for the port that you have set in above steps.
Control Panelà Windows Firewall àAdvance Setting àclick on Inbound Rules à Add new Rule by right click on Inbound Rules àspecify the port numbers that are involved such as 8080 of apex, 1521 of 11g.
