(De)install Apex in 11GR2 challenge


Recently two new databases set up had been checked by QA process and they did not make that check due to the fact that Apex was setup in the sysaux tablespace. Of course it is important to follow the standards so some  extra work  would be needed to pass  the checks . House rules state that for Apex installation a dedicated tablespace will be needed. Time to play and make things right with two scenarios.  Scenario 1 will be faulty installation with a deinstall and an install after. Scenario 2 will be an install( During which action noticed that Oracle text is a mandatory component that needs to be in place before install Apex). Below notes I have used  to investigate and fix the challenge. As always  Google is you friend when exploring such solutions.

Being curious by nature of course had to explore two approaches. Primarily installing a new part to an already existing database can be done with DBCA. As another option it is still a good challenge to see if you can do it yourself by using the command line too. Hmm does it still show that I grew up with Oracle 7.3.4 as a starters kit ?


First lets explore if there is a current installation of Apex in place.

col  comp_name format a60
set lines 144
select COMP_NAME,status from dba_registry order by 1;





As part of activities looked on  the Web and found below Blog.  Big Thank you to the author Matthias Hoys for documenting:

De-installing Apex in 11gr2

So in my case would have set Environment to Oracle 11gr2 and  move to the apex directory before  starting sqlplus .

cd /opt/oracle/product/11203_ee_64/db/apex/
sqlplus /nolog
connect / as sysdba
Once the scripts has completed check again:

no rows selected

Now let’s do it proper and create a dedicated tablespace for apex installation first:

create tablespace APEX datafile ‘+CRMMST_DATA01’ size 4096M autoextend off

Note. If you settle for  uniform size tablespace, make sure your extend-size is big enough. I had the issue that install will fail  if extend size is too small (apex install will fail with  ORA-60019). Error Detail:

create table wwv_mig_forms (
ERROR at line 1:
ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8

ORA-60019: Creating initial extent of size string in tablespace of extent size string
Cause: Creation of SECUREFILE segment failed due to small tablespace extent size.
Action: Create tablespace with larger extent size and reissue command.
## my ts was uniform size 64K when created dedicated ts. Recreated ts with uniform size 1M.

Install Apex manually  (with Oracle Text in place):

## make sure you are in this directory:
cd /opt/oracle/product/11203_ee_64/db/apex 
@/opt/oracle/product/11203_ee_64/db/apex/apexins apex apex temp /i/

Since more than one script will run, made sure to head to the Apex directory in my Oracle installation before running  the starting script in sqlplus.  My new created dedicated  tablespace apex  is part of  parameters needed. ( @apexins.sql tablespace_apex tablespace_files tablespace_temp images ).


Install Apex manually (with Oracle Text  not in place):

In case Oracle Text is not installed in the database, the apex install will complain about that. In such case you will have to install Oracle Text first.

Oracle Text:

cd /opt/oracle/product/11203_ee_64/db/ctx/admin
Start your sqlplus session and run:
spool /tmp/catctx.log

Among the parameters username,tablespace to create objects into, temp, account status after install.

After that installed Apex:

## make sure you are in this directory:
cd /opt/oracle/product/11203_ee_64/db/apex 
@/opt/oracle/product/11203_ee_64/db/apex/apexins apex apex temp /i/

## check status :



## Another issue and blushing when in the end  turned out i had proper tablespace NOT in place. Ran the script with the parameters but the tablespace had not been created:


APPLICATION 4411 - APEX  - System Messages Set Credentials... begin * ERROR at line 1: 
ORA-04063: package body "APEX_030200.WWV_FLOW_API" has errors ORA-06508: PL/SQL: could not find program unit being called: 
"APEX_030200.WWV_FLOW_API" ORA-06512: at line 4 
## and did notice this package remained invalid even after remove part of apex. 
Solution: drop package htmldb_system; drop public synonym htmldb_system; 

Mandatory Aftercare (last man standing).
Check for invalid objects and recompile them.
select owner ,object_name,object_type from dba_objects where  status <> ‘VALID’ order by 1,2;


and check again .


Happy reading,




Btw. Loved this post by one of the great bloggers in general and on this Apex  in particular :

Oracle-Base oracle-application-express-apex-4-2-installation

Transport Tablespace as Mig to with rman.


For one of the projects the question came in to investigate and set up a Real application cluster database with an extra challenge that the migration had to be done cross-platform from Oracle on Solaris platform to on Linux. From application provider came the suggestion to investigate a back-up – restore scenario with an upgrade on the new server ( Linux environment). Due to the fact that the Source environment was 10.20.3 on Solaris and due to fact we were heading towards a Rac cluster environment on on Linux that suggestion was  the first that was send to the dustbin.

Normal export  / import was the second scenario that was explored. Of course this is a valid scenario  but given  the fact that the database was more than 1.x TB not exactly the most favorite way to bring  the data across. But whit scripting and using multiple par-files  and or with moving  partitioned data across in waves would be a  fair plan-b.

From reading though Had put my mind to the use of  transportable tablespaces as a way forward with this challenging question.


As preparation for the job requested to have Nas filesystem mounted between the source Server (MySunServer) holding the 10G database and the target Server (MyLinuxcluster). This Nas filesystem  would hold  the datapumps to be created, to hold the scripts and parfiles  / config files as was suggested  based on Mos Note ( 1389592.1 ). Nas system was / read-writable from both  servers. The  perl scripts that come with the note will support in the transport of the tablespaces but also help in  the convert of big endian to little endian And as a bonus in my case will do the copy into ASM.

Due to the layout of the database in the source environment  Rman was chosen as the best way forward with the scenario.

As a preparation an 110204 Rac database was set up on the target cluster. This  database only to hold the normal tablespaces and a smal temporary tablespace for the users. ( In TTS solution the name of the data tablespaces that come across to the new environment may not exist in the new environment). All data- application users have been pre created on the new environment with a  new – default user tablespace.

Details & Comments

Configuration file for the Perl scripts:

This is a file  that is part of the unzipped file from the Mos note. It needs to be setup to match your specific needs.  Will only show settings  I have used and  its comments:

## Reduce Transportable Tablespace Downtime using Incremental Backups
## (Doc ID 1389592.1)

## Properties file for xttdriver.pl

## See documentation below and My Oracle Support Note 1389592.1 for details.
## Tablespaces to transport

## Specify tablespace names in CAPITAL letters.

## Source database platform ID

## platformid

## Source database platform id, obtained from V$DATABASE.PLATFORM_ID


## srclink

## Database link in the destination database that refers to the source

## database. Datafiles will be transferred over this database link using
## dbms_file_transfer.

## Location where datafile copies are created during the “-p prepare” step.

## This location must have sufficient free space to hold copies of all
## datafiles being transported.


## backupformat

## Location where incremental backups are created.


## Destination system file locations

## stageondest

## Location where datafile copies are placed by the user when they are

## transferred manually from the souce system. This location must have
## sufficient free space to hold copies of all datafiles being transported.


# storageondest

## This parameter is used only when Prepare phase method is RMAN backup.

## Location where the converted datafile copies will be written during the

## "-c conversion of datafiles" step. This is the final location of the
## datafiles where they will be used by the destination database.
## backupondest

## Location where converted incremental backups on the destination system

## will be written during the "-r roll forward datafiles" step.

## NOTE: If this is set to an ASM location then define properties

##      asm_home and asm_sid below. If this is set to a file system
##       location, then comment out asm_home and asm_sid below

## asm_home, asm_sid

## Grid home and SID for the ASM instance that runs on the destination


## Parallel parameters


## rollparallel

## Defines the level of parallelism for the -r roll forward operation.

## If undefined, default value is 0 (serial roll forward).

## getfileparallel

## Defines the level of parallelism for the -G operation


## desttmpdir

## This should be defined to same directory as TMPDIR for getting the

## temporary files. The incremental backups will be copied to directory pointed
## by stageondest parameter.


Below in a Table format you will see the steps performed with comments.

Steps do qualify for

  • I for Initial steps – activities
  • P for Preparation
  • R for Roll Forward activities
  • T for Transport activities

Server column shows where the action needs to be done.

Step Server What needs 2 b done
I1.3 Source Identify the tablespace(s) in the source database that will be transported ( Application owner needs to support with schema owner information) :


I1.5 Source + Target In my case project offered an nfs filesystem which i could use : Nfs filesystem : /mycomp_mig_db_2_linux
I1.6 Source Together with the Mos note cam  this zip file : Unzip rman-xttconvert.zip.
I1.7 Source Tailor the extracted file xtt.properties file on the source system to match your environment.
I1.8 Target As the oracle software owner copy all xttconvert scripts and the modified xtt.properties file to the destination system. This was not needed since we used the nas filesystem.
P1.9 Source + Target On both environments set up this:

export TMPDIR= /mycomp_mig_db_2_linux/MYDBP/scripts.

P2B.1 Source perl xttdriver.pl -p
Note. Do Not use ]$ $ORACLE_HOME/perl/bin/perl this did not work
P2B.2 Source Copy files to destination. N/A since we use NFS
P2B3 Target On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, copy the rmanconvert.cmd file created in step 2B.1 from the source system and run the convert datafiles step as follows:
[oracle@dest]$ scp oracle@source:/home/oracle/xtt/rmanconvert.cmd /home/oracle/xtt N/A since we use NFS.
perl/bin/perl xttdriver.pl –c
R3.1 Source On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the create incremental step as follows:
perl xttdriver.pl –I
R3.3 Target [oracle@dest]$ scp oracle@source:/home/oracle/xtt/xttplan.txt /home/oracle/xtt
[oracle@dest]$ scp oracle@source:/home/oracle/xtt/tsbkupmap.txt /home/oracle/xtt
 Since we are using Nas shared filesystem no need to copy with scp  between source and target.
perl xttdriver.pl -r
R3.4 Source On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, run the determine new FROM_SCN step as follows:
perl xttdriver.pl –s
R3.5 Source 1.     If you need to bring the files at the destination database closer in sync with the production system, then repeat the Roll Forward phase, starting with step 3.1.
2.     If the files at the destination database are as close as desired to the source database, then proceed to the Transport phase.
T4.0 Source As found in note : Alter Tablespace Read Only Hanging When There Are Active TX In Any Tablespace (Doc ID 554832.1). A restart of the database is required to have no active transactions. Alternative during off hours . Actually during a first test with one dedicated tablespace with only one  object it took more than 7 hrs. Oracle seems to look and wait  for ALL active transactions, not only the ones that would impact   the object in the test tablespace i worked with.
T4.1 Source On the source system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the source database, make the tablespaces being transported READ ONLY.
alter tablespace MYDB_DATA read only;
alter tablespace MYDB_EUC_DATA read only;
alter tablespace MYDB_EUC_INDEX read only;
alter tablespace MYDB_INDEX read only;
alter tablespace MYTS read only;
alter tablespace USERS read only;
T4.2 Source Repeat steps 3.1 through 3.3 one last time to create, transfer, convert, and apply the final incremental backup to the destination datafiles.
perl xttdriver.pl -i
T4.2 Target [oracle@dest]$ scp oracle@source:/home/oracle/xtt/xttplan.txt /home/oracle/xtt
[oracle@dest]$ scp oracle@source:/home/oracle/xtt/tsbkupmap.txt /home/oracle/xtt
perl xttdriver.pl –r
T4.3 Target On the destination system, logged in as the oracle user with the environment (ORACLE_HOME and ORACLE_SID environment variables) pointing to the destination database, run the generate Data Pump TTS command step as follows:
perl xttdriver.pl –e
The generate Data Pump TTS command step creates a sample Data Pump network_link transportable import command in the file xttplugin.txt. It will hold list of all the TTS you have configured and all its transport_datafiles in details.
Example of that generated file : cat xttplugin.txt
impdp directory=MYDB_XTT_DIR logfile=tts_imp.log \
network_link=TTSLINK.PROD.NL transport_full_check=no \
transport_tablespaces=MYCOMPTTS ,A,B,C\
Note in our example once edited we chmodded   xttplugin.txt with 744 and ran it as script .
T4.3 Source After the object metadata being transported has been extracted from the source database, the tablespaces in the source database may be made READ WRITE again, if desired.
T4.4 Target At this step, the transported data is READ ONLY in the destination database.  Perform application specific validation to verify the transported data.
Also, run RMAN to check for physical and logical block corruption by running VALIDATE TABLESPACE as follows:
In rman:
validate tablespace MYDB_DATA, MYDB_EUC_DATA, MYDB_EUC_INDEX, MYDB_INDEX, MYTS, USERS check logical;
T4.5 Target alter tablespace MYDB_DATA read write;
alter tablespace MYDB_EUC_DATA read write;
alter tablespace MYDB_EUC_INDEX read write;
alter tablespace MYDB_INDEX read write,
alter tablespace MYTS read write;
alter tablespace USERS read write;
T5 Source + Target Cleanup of NFS filesystem.
Put Source Db in restricted mode as a fallback after the go live for couple of days then put it to tape and decommission it;