Yet Another Oracle DBA Blog: Installing APEX V5 into a 12C Pluggable Database

The new version of APEX has just been released; I had a play when it was the Early Adopter version online and it is a big change from V4 so want to have a deeper look now it’s available to download (http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html).

I’d just installed 12C with a container database (CDB) so thought I’d install the new APEX here, but a bit of research showed that 12C works a bit differently than earlier versions - http://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-in-12c-white-paper-2046726.pdf

Basically APEX V4 is installed with database 12C, and it keeps some core objects in the CDB, and each Pluggable Database (PDB) has some APEX objects with hooks into the CDB APEX engine. So upgrading to APEX V5 in 12C isn’t as simple as previous versions (overwrite the APEX folder and run the install SQL).

https://docs.oracle.com/database/121/HTMIG/db_pluggable.htm#HTMIG29169

For a start, if you have already created some applications in PDBs, then you can’t upgrade. You have to de-install the core version, and then install the new version into the PDBs as required. Any existing applications in the PDBs will break. You can install V5 into the CDB I later read, so google if that’s what you want to do. But it might be handy to have a V4.2 in one PDB and V5 in another for development purposes.

So basically if you intend to use APEX in 12C and want to upgrade at a later stage, you need to know this and plan for it by de-installing APEX from the CDB as soon as the database has been installed:

Note:

If you run apexremov_con.sql after PDBs have been added to the CDB then Application Express uninstalls from all of the PDBs. Therefore, any applications defined in any of the PDBs will be removed. If you run apxremov.sql then Application Express is only removed from the root container. However, every PDB is left in an invalid state and will cause errors when trying to open the PDB.

So I’m going to do this – de-install V4.2 from everything and then install V5 into the PDB.

First of all I’ll create a new PDB to play with. I called this APEXV5.

Either sign onto SQLPlus, or use EM Express (see my earlier post) to create the new PDB.

I had already created a test PDB so I just cloned that:

alter pluggable database ANDYPDB close immediate;

alter pluggable database ANDYPDB open read only;

create pluggable database APEXV5 from ANDYPDB file_name_convert=(‘D:\oradata\ANDYPDB\’,’D:\oradata\APEXV5/’);

alter pluggable database ANDYPDB close immediate;

alter pluggable database ANDYPDB open read write;

alter pluggable database APEXV5 open read write;

Alternatively, to create a new one from the pdbseed database:

create pluggable database apexv5
admin user apexv5 identified by password
roles (dba)
default tablespace apexv5
datafile 'D:\oradata\apexv5\apexv5_01.dbf' size 100m autoextend on
file_name_convert = ('D:\oradata\orcl\pdbseed\','D:\oradata\apexv5\') storage (maxsize 2G)
path_prefix = 'D:\oradata\apexv5\';

Next step, take a backup before removing the installed APEX version. My database is running in no archivelog mode, so I need to stop it and then take a backup.

I just did a shutdown immediate, then did an RMAN backup to disk.

rman target /

startup mount

backup database;

Restarted the database. One thing with 12C – pluggable databases don’t start automatically when the container starts unless you tell them to.

So either manually start them, or set the state to restart automatically.

While it’s running:

alter pluggable database PDB_NAME save state;

You can see if pluggable databases restart by looking here:

select * from dba_pdb_saved_states;

Restart the CDB, and then the PDBs, then save the state:

Oh, the save state thing is V12.1.0.2 – for the earlier release the way to do this is to create a trigger. Do a Google search to find out how.

OK, back to the APEX upgrade.

De-install the current release from the CDB and PDBs. Navigate to the $ORACLE_HOME/apex directory and start SQLPlus, then run the SQL to remove APEX from everywhere by logging in as sysdba:

@apxremov_con

If you left a PDB closed like I did (oops), you’ll get an error:

Opened it and tried again.

So, APEX has been de-installed. Exit from SQLPlus and navigate up (cd ..) otherwise you won’t be able to rename the folder.

The upgrade to V5 is the same as other versions – move the existing $ORACLE_HOME/apex folder to apex_v4 as a backup, then copy the V5 zip file to the $ORACLE_HOME and run the install script.

unzip apex_5.0_en.zip

to extract the files to the new ‘apex’ directory.

Once extracted, you need to install it into the PDB (or PDBs).

Navigate to the apex directory, sign into SQLPlus and set the PDB to where you want to install V5:

Run the installer, same command as earlier versions:

@apexins sysaux sysaux temp /i/

This can take a while.

When complete:

Once installed, run the apxchpwd to set the ADMIN password:

Make this a temporary one because it asks you to change it as soon as you login to the web page, and it has to be complex otherwise it will just reject it.

You also need to create a USERS tablespace (as I discovered when the install failed..), so either log into the EM Express page for this container and add it (see my previous post), or run this SQL:

CREATE SMALLFILE TABLESPACE USERS DATAFILE 'd:\oradata\apexv5\users_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Now install APEX REST services or the EPG.

I decided to use the EPG for speed (I attempted the REST but had some issues that I couldn’t be bothered fighting with today).

Open a DOS prompt and navigate to the $ORACLE_HOME/apex directory and connect to SQLPlus as sysdba

Don’t forget that this is all happening in a container, so set that first:

SQL> alter session set container=apexv5;

Session altered.

Then run the config SQL, remembering NOT to enter the APEX directory otherwise it fails. I also changed the port from 8080, don't like to leave the default.

SQL> @apxconf

PORT

----------

8080

Enter values below for the XDB HTTP listener port and the password for the Appli

cation Express ADMIN user.

Default values are in brackets [ ].

Press Enter to accept the default value.

================================================================================

This script can be used to change the password of an Application Express

instance administrator. If the user does not yet exist, a user record will be

created.

================================================================================

Enter the administrator's username [ADMIN]

User "ADMIN" exists.

Enter ADMIN's email [ahorne@mga.com.au]

Enter ADMIN's password []

Changed password of instance administrator ADMIN.

Enter a port for the XDB HTTP listener [ 8080] 8085

...changing HTTP Port

Unlock the anonymous account

SQL> alter user anonymous account unlock;

User altered.

Run the config script

SQL> @apex_epg_config.sql d:\app\oracle\product\12.1.0\dbhome_1

PL/SQL procedure successfully completed.

. Loading images directory: d:\app\oracle\product\12.1.0\dbhome_1/apex/images

Directory created.

PL/SQL procedure successfully completed.

Commit complete.

Directory dropped.

timing for: Load Images

Elapsed: 00:05:45.46

PL/SQL procedure successfully completed.

Commit complete.

SQL>

You now need to run this SQL to allow access – note that scripts you find on the web to do this will probably have APEX_040200 as the schema – this is out of date, the schema in V5 is ‘APEX_050000’

DECLARE

ACL_PATH VARCHAR2(4000);

BEGIN

-- Look for the ACL currently assigned to '*' and give APEX_050000

-- the "connect" privilege if APEX_0050000 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_050000',

'connect') IS NULL THEN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

'APEX_050000', 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_050000', TRUE, 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

END;

COMMIT;

Now try to login to the APEX admin page (and note that it’s ‘http’ and not ‘https)’:

http://localhost:8085/apex/apex_admin

(Note, if you get that very annoying pop up box that asks for a user/password and says 'Site says XDB', try running @apex_epg_config.sql d:\app\oracle\product\12.1.0\dbhome_1 in the container again)

Taaa Daaa

It looks a lot different from V4.2, so go and have a play.

It’s probably a good idea to back up the database again unless you want to have to redo this.

I'll write some simple apps when I get the time.

Monday, May 18, 2015

Installing APEX V5 into a 12C Pluggable Database