Installing Oracle Application Express 4.2

Oracle APEX is a free browser based tool to develop web applications with SQL and PL/SQL for desktops and mobile devices. It is installed by default in all oracle database editions.

To verify APEX version.
SQL> SELECT schema, version FROM dba_registry WHERE comp_id = 'APEX';

SCHEMA       VERSION
------------------------------ ------------------------------
APEX_040000       4.0.2.00.09

To verify Oracle XML DB Protocol Server Port.
SQL> SELECT dbms_xdb.gethttpport FROM dual;

GETHTTPPORT
-----------
       8080

Disable Oracle XML DB protocol Server.
SQL> EXEC dbms_xdb.sethttpport(0);

Backup existing APEX binaries.
$ mv $ORACLE_HOME/apex $ORACLE_HOME/apex_040000

Unzip APEX software into ORACLE_HOME.
$ unzip apex_4.2.3_en.zip -d $ORACLE_HOME
$ cd $ORACLE_HOME/apex

SQL> CREATE TABLESPACE APEX_040200 DATAFILE '/u01/app/oracle/oradata/apex_423_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

SQL>@apexins.sql APEX_040200 APEX_040200 TEMP /i/

When APEX installs, it creates the below database accounts.
APEX_042000 - Owns APEX schema and metadata
FLOWS_FILES - Owns APEX uploaded files
APEX_PUBLIC_USER - Minimum privileged account used with APEX Listener or Oracle HTTP server with mod_plsql.

Change ADMIN account password.
SQL> @apxchpwd.sql

Configure the Embedded PL/SQL Gateway.
SQL> @apex_epg_config.sql /u01/app/oracle/product/11.2.0/xe
SQL> ALTER USER anonymous ACCOUNT UNLOCK;

If you are upgrading APEX, update the images directory.
SQL> @apxldimg.sql /u01/app/oracle/product/11.2.0/xe

SQL> ALTER USER apex_040200 ACCOUNT UNLOCK;
SQL> ALTER USER apex_public_user ACCOUNT UNLOCK;
SQL> ALTER USER flows_files ACCOUNT UNLOCK;
SQL> ALTER USER xdb ACCOUNT UNLOCK;

Set database parameters for APEX.
SQL> ALTER SYSTEM SET job_queue_processes=20 SCOPE=both;
SQL> ALTER SYSTEM SET shared_servers=5 SCOPE=both;    --> Embedded PL/SQL Gateway uses shared server architecture.

Enable Oracle XML DB  Protocol Server. The Embedded PL/SQL Gateway runs in the Oracle XML DB Protocol Server in the Oracle Database.
SQL> EXEC dbms_xdb.sethttpport(8080);

Enable access for remote users (optional).
SQL> EXEC dbms_xdb.setListenerLocalAccess(l_access => FALSE);

Enable network services in Database 11g by granting connect privileges to any host for APEX_040200 user.

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;

Login as ADMIN to Administrative Services for creating a workspace and APEX users at
http://locahost:8080/apex/apex_admin

Login as ADMIN to INTERNAL workspace at
http://localhost:8080/apex

Drop older version of APEX.
SQL> DROP USER apex_040000 CASCADE;

APEX Listener is a J2EE based alternative for Oracle HTTP Server (OHS) with mod_plsql.
To use APEX Listener, Disable Oracle XML DB Protocol Server.

SQL> EXEC dbms_xdb.sethttpport(0);

Configure RESTful Services.
SQL> @apex_rest_config.sql

When configuring RESTful Services, it creates two additional database accounts.
APEX_LISTENER - To query RESTful Services definitions.
APEX_REST_PUBLIC_USER - To call RESTful Services definitions.

Unzip APEX Listener software into ORACLE_HOME.
$ mkdir $ORACLE_HOME/apexlsnr
$ unzip apex_listener.2.0.5.287.04.27.zip -d $ORACLE_HOME/apexlsnr
$ cd $ORACLE_HOME/apexlsnr

Set the location for configuration files.

$ java -jar apex.war configdir $ORACLE_HOME/apexlsnr

Configure APEX Listener.

$ java -jar apex.war

This Listener instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/u01/app/oracle/product/11.2.0/xe/apexlsnr
Nov 30, 2013 11:41:34 PM oracle.dbtools.common.config.cmds.ConfigDir execute
INFO: Set config.dir to /u01/app/oracle/product/11.2.0/xe/apexlsnr in: /u01/app/oracle/product/11.2.0/xe/apexlsnr/apex.war
Nov 30, 2013 11:41:38 PM oracle.dbtools.common.config.file.ConfigurationFolder logConfigFolder
INFO: Using configuration folder: /u01/app/oracle/product/11.2.0/xe/apexlsnr/apex
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:XE
Enter the database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to enter passwords for the RESTful Services database users (APEX_LISTENER,APEX_REST_PUBLIC_USER), 2 to use the same password as used for APEX_PUBLIC_USER or, 3 to skip this step [1]:2
Nov 30, 2013 11:46:55 PM oracle.dbtools.common.config.file.ConfigurationFiles update
INFO: Updated configurations: defaults, apex, apex_al, apex_rt
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1

Enter the APEX static resources location or Enter to skip:/u01/app/oracle/product/11.2.0/xe/apex/images

Enter the HTTP port [8080]:

If you see "503 - Service Unavailable" message, reset the password and unlock the account for APEX_PUBLIC_USER.
SQL> ALTER USER apex_public_user IDENTIFIED BY <above_specified_password> ACCOUNT UNLOCK;

Configure an Administrator User to administer using SQL Developer.

$ java -jar apex.war user adminlistener "Listener Administrator"

Standalone mode doesn't support HTTPS. Disable the default behaviour to use OAuth2 in Non HTTPS Environments.

$ cd $ORACLE_HOME/apexlsnr

Add the following setting to the end of the file default.xml just before the </properties> tag.
<entry key="security.verifySSL">false</entry>

Start APEX Listener in standalone mode.

$ java -jar apex.war

To stop APEX Listener Server in standalone mode, press Ctrl-C.

To configure additional databases.
1. Create a database pool pointing to the database.
    java -jar apex.war setup --database <db_pool>
2. Create a new mapping rule.
    java -jar apex.war map-url --type base-path --workspace-id <workspace> /<path_prefix> <db_pool>
The URL path starts with /apex/<path_prefix> will be mapped to the database.
 

Installing Oracle 11g Database Express on Ubuntu 13.10

Oracle 11g Database XE is a free edition of oracle database. XE uses only a single CPU and only one instance with the maximum memory of 1GB. The maximum user data in Oracle Database XE is 11GB.

$ sudo apt-get install alien libaio1

$ unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
$ cd Disk1
$ sudo alien --scripts -d oracle-xe-11.2.0-1.0.x86_64.rpm
$ sudo dpkg --install oracle-xe-11.2.0-1.0.x86_64.deb

It creates oracle user and installs oracle binaries in /u01/app/oracle

Add oracle user to sudo group.
$ sudo adduser oracle sudo

Create password for oracle.
$ sudo passwd oracle

On Ubuntu, /run/shm is mounted and /dev/shm is a link to it. But /dev/shm should be mounted to avoid ORA-00845: MEMORY_TARGET not supported on this system.

$ sudo vi /etc/rc2.d/S01shm-mount

#! /bin/sh
case "$1" in
       start)
                rm /dev/shm 2>/dev/null
                mkdir /dev/shm 2>/dev/null
                mount -t tmpfs shmfs -o size=2048m /dev/shm
                ;;
       *)
                echo error
                exit 1
                ;;
esac

$ sudo chmod 755 /etc/rc2.d/S01shm-mount

$ sudo mkdir /var/lock/subsys

Change the path for awk in /etc/init.d/oracle-xe

if [ -z "$AWK" ]; then AWK=/bin/awk; fi
to
if [ -z "$AWK" ]; then AWK=/usr/bin/awk; fi

Create XE database.
$ su - oracle
$ sudo /etc/init.d/oracle-xe configure

Press to accept the defaults
Specify the HTTP port that will be used for Oracle Application Express [8080]:

Specify a port that will be used for the database listener [1521]:

Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Confirm the password:

Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n

Starting Oracle Net Listener...Done

Configuring database...Done

Starting Oracle Database 11g Express Edition instance...Done

Installation completed successfully.

You can find the database creation logs in $ORACLE_HOME/config/log

The password for the INTERNAL and ADMIN Oracle Application Express user accounts is initially the same as the SYS and SYSTEM user accounts.

For Bash shell, enter the environment variables into the .bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=XE
export NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export PATH=$ORACLE_HOME/bin:$PATH

To start listener and database as oracle user.
$ su - oracle
$ lsnrctl start
$ sqlplus / as sysdba
SQL> startup

To stop database and listener as oracle user.
$ su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate
$ lsnrctl stop

Migrating the database to ASM Disk Group

If Block Change Tracking is configured, disable it.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Set CONTROL_FILES to the ASM locations and start the instance.

SQL> ALTER SYSTEM SET CONTROL_FILES='+DATADG','+FRADG' SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT

Restore controlfile into the new ASM location.
RMAN> CONNECT TARGET
RMAN> RESTORE CONTROLFILE FROM '<old_controlfile_path>';

Backup the database into the ASM disk group.
RMAN> ALTER DABASE MOUNT;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATADG';

Switch all datafiles and tempfiles to the new ASM location.

RMAN> SWITCH DATABASE TO COPY;

Backup the read only tablespaces into the ASM disk group.
SQL> ALTER TABLESPACE <tablespace_name> OFFLINE;
RMAN> BACKUP AS COPY TABLESPACE <tablespace_name> FORMAT '+DATADG';
RMAN> SWITCH TABLESPACE <tablespace_name> TO COPY;
SQL> ALTER TABLESPACE <tablespace_name> ONLINE;

RMAN> RUN {
          SET NEWNAME FOR TEMPFILE 1 TO '+DATADG';
          ...
          SWITCH TEMPFILE ALL;
          }


Open the database.

RMAN> ALTER DATABASE OPEN;

Drop online redo log groups and re-create them in ASM disk group.

SQL> select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
SQL> ALTER DATABASE ADD LOGFILE GROUP 3 ('+DATADG','+FRADG') SIZE 500M;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
SQL> ALTER SYSTEM CHECKPOINT;

Repeat the above step for other logfile groups.

Set archive log destination to the ASM location.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'location=+FRADG' SCOPE=BOTH;

Relocate spfile to an ASM disk group.

SQL> CREATE PFILE='$ORACLE_HOME/dbs/initORCL.ora' FROM spfile;
SQL> CREATE SPFILE='+DATADG/ORCL/spfileORCL.ora' FROM PFILE='$ORACLE_HOME/dbs/initORCL.ora';

Shutdown database.
SQL> SHUTDOWN IMMEDIATE

Set ASM environment and modify the oracle restart configuration for spfile.
$ srvctl modify database -d ORCL -p '+DATADG/ORCL/spfileORCL.ora'

Maximum Availability Architecture

PREREQUISITES :

1. The primary RAC database in archivelog mode.
2. Grid Infrastructure and Oracle software were installed on standby node.
3. spfile on shared filesystem.
4. Create dump directories adump etc on standby node if needed.

1. Place the primary database in force logging.
SQL> alter database force logging;

PREPARE PRIMARY DATABASE


Configure the primary database initialization parameters to support both the primary and standby roles.

SQL> alter system set log_archive_config='dg_config=(ORCL,ORCLS)' scope=both ;
SQL> alter system set log_archive_dest_1='LOCATION=/u05/ORCL/arch MANDATORY REOPEN=30 VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=ORCL' scope=both;
SQL> alter system set log_archive_dest_2='SERVICE=ORCLS LGWR ASYNC reopen=15 max_failure=20 net_timeout=15 VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLS' scope=both;

SQL> alter system set log_archive_dest_state_1='enable' scope=both;
SQL> alter system set log_archive_dest_state_2=defer scope=both;
SQL> alter system set log_archive_max_processes=10 scope=both;

SQL> alter system set fal_server='ORCLS' scope=both;
SQL> alter system set fal_client='ORCL1' scope=both sid='ORCL1'; --- Deprecated in 11gR2
SQL> alter system set fal_client='ORCL2' scope=both sid='ORCL2'; --- Deprecated in 11gR2

SQL> alter system set db_file_name_convert='/u3/baeifspdat','/baeifst_dbf/oradata/baeifst' scope=spfile;
SQL> alter system set log_file_name_convert='/u1/baeifst/redo',/baeifst_log1/oradata/baeifst','/u2/baeifst/redo','/baeifst_log2/oradata/baeifst' scope=spfile;
SQL> alter system set standby_file_management='AUTO' scope=both;

SQL> alter system set db_unique_name='ORCL';

SQL> alter database set standby database to maximize availability;



3. Create pfile on primary and copy password file and pfile to secondary.

SQL> create pfile from spfile;


PREPARE STANDBY DATABASE

Modify db_unique_name,fal_server, Log_archive dest, audit_file_dest,control_files,service_names etc.

set db_file_name_convert='/baeifst_dbf/oradata/baeifst','/u3/baeifspdat' scope=spfile;

set log_file_name_convert='/baeifst_log1/oradata/baeifst','/u1/baeifst/redo','/baeifst_log2/oradata/baeifst','/u2/baeifst/redo' scope=spfile;


SQL> startup nomount pfile=

SQL> create spfile from pfile;

SQL> shutdown immediate

CREATE STANDBY DATABASE

Copy datafiles in hot backup mode and stand by controlfile to standby.


ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/control_stby.ctl';


6. Add databse resouce to cluster.
$ srvctl add database –d -o -p ;
$ srvctl add instance –d –i –n ;
$ srvctl add instance –d –i –n ;

7. Mount standby database.
$ srvctl start -d -o mount

Create the standby redo logs to support the standby role on both primary and standby. The recommended number of standby redo logs is one more than the number of online redo logs for each thread.
SQL> alter database add standby logfile thread 1 group 11 ('redo11a','redo11b') size 512M; --> same size of online redo log. One extra group upto group 14.
SQL> alter database add standby logfile thread 2 group 15 ('redo15a','redo15b') size 512M; --> One extra group upto group 18.



8. Start managed recovery on standby database.
SQL> recover managed standby database disconnect from session;

9. Enable log shipping on primary.
alter system set log_archive_dest_state_2=enable scope=both;



-----------------------------------------------------------------

Check the number and group numbers of the redo logs.
SQL> select * from v$log;

Check the standby redo logs.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Verify Destinations are valid on both.

SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <= 2;



Verify redo apply on primary.

SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID <= 2;



Verify gaps in redo on primary.

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;



Verify Error on both.

SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;





select thread#,sequence#,archived,applied from v$archived_log order by 1,2;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;

Administering ASM Disk Groups

ASM simplifies the administration of oracle related files in disk groups which provides secure storage with striping and mirroring capabilities. All disks in an ASM disk group should be of same size and have similar performance characteristics. As per ASM best practices, two disk groups are recommended.

DATADG - spfile, control file, redo log member and datafiles.
FRADG   - control file, redo log member, archived logs, flashback logs, RMAN backups and Data Pump dump file sets.

When the Storage Administrator presents the LUNs to OS as disk devices, the DBA can create the ASM disk groups.

Connect to ASM instance.
$ sqlplus / as sysasm

SQL> set pages 200 lines 150
SQL> col path form a50
SQL> SELECT name, path, header_status, os_mb FROM v$asm_disk;

NAME    PATH                           HEADER_STATUS  OS_MB
-----------  ----------------------------      ---------------------------    ------------  
                /dev/rdsk/emcpower1a  CANDIDATE               17399
                /dev/rdsk/emcpower2a  CANDIDATE               17399
                /dev/rdsk/emcpower3a  CANDIDATE               17399
                /dev/rdsk/emcpower4a  CANDIDATE               17399
                /dev/rdsk/emcpower5a  FORMER                    34807
                /dev/rdsk/emcpower6a  CANDIDATE               34807


SQL> CREATE DISKGROUP datadg EXTERNAL REDUNDANCY
          DISK  '/dev/rdsk/emcpower1a', '/dev/rdsk/emcpower2a', '/dev/rdsk/emcpower3a';

SQL> SELECT name, path, header_status, os_mb FROM v$asm_disk;

NAME                    PATH                       HEADER_STATUS   OS_MB
----------------------    ----------------------------      ---------------------------   ----------------
 DATADG_0000   /dev/rdsk/emcpower1a  MEMBER                     17399  
 DATADG_0001   /dev/rdsk/emcpower2a  MEMBER                     17399
 DATADG_0002   /dev/rdsk/emcpower3a  MEMBER                     17399
                           /dev/rdsk/emcpower4a  CANDIDATE                 17399
                           /dev/rdsk/emcpower5a  FORMER                      34807
                           /dev/rdsk/emcpower6a  CANDIDATE                 34807

Add same size candidate disk to the disk group.
SQL> ALTER DISKGROUP datadg ADD DISK '/dev/rdsk/emcpower4a';

To drop the disk from disk group.
SQL> ALTER DISKGROUP datadg DROP DISK DATADG_0003;

SQL> SELECT operation, state, power, est_minutes FROM v$asm_operation;

OPERATION     STATE   POWER  EST_MINUTES
------------------      ----------   -----------     ---------------------
REBAL              DROP              1                     23

Watch the state of DATADG_0003 device as DROPPING and free_mb is changing with the time.
SQL> SELECT name, path, total_mb, free_mb, state FROM v$asm_disk;

Alternatively at command prompt.
$ asmcmd lsdg

ASM instance startup mounts the disk groups and shutdown unmounts those.
To manually mount and unmount the disk group(s).
SQL> ALTER DISKGROUP DATADG mount;
SQL> ALTER DISKGROUP DATADG dismount;
SQL> ALTER DISKGROUP ALL mount;
SQL> ALTER DISKGROUP ALL dismount;

To view disk group compatibility.
SQL> SELECT name, compatibility, database_compatibility FROM v$asm_diskgroup;

Create ASM instance

$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/grid
$ orapwd file=$ORACLE_HOME/dbs/orapwd+ASM password= entries=20
$ mkdir -p $ORACLE_BASE/admin/+ASM
$ cd $ORACLE_BASE/admin/+ASM

Create init+ASM.ora file.

+ASM.__large_pool_size=20971520
+ASM.__shared_pool_size=515899392
##+ASM.asm_diskgroups='DATADG','FRADG'  #Manual Dismount
*.asm_power_limit=1
*.diagnostic_dest='/u01/app/oracle'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
$ export ORACLE_SID=+ASM
$ sqlplus / as sysasm

SQL> startup nomount pfile='$ORACLE_HOME/dbs/init+ASM'

Creating ASM Disks

The ASM will be created on unformatted disk partitions.

As the root user, determine which devices can be partitioned.
# cat /proc/partitions

Create the partition for each disk. Select n for action, p for primary partition, 1 for partition number, default values for First and Last cylinder and w to write the partition table.
# fdisk /dev/sda

Configure Oracle ASM library driver. Answer default user and group as oracle and dba, y for Start Oracle ASM library driver on boot, y for Scan for Oracle ASM disks on boot.
# oracleasm configure -i

Load Oracle ASM module and mount oracleasm filesystem /dev/oracleasm.
# oracleasm init

Create ASM disk label for each disk.
# oracleasm createdisk DATA1 /dev/sda1

Check the disks are visible.
# oracleasm listdisks

Check the disks are mounted in oracleasm filesystem.
# ls -l /dev/oracleasm/disks