Introduction

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 ?

Details

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;

or

SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = 'APEX';

VERSION
------------------------------
3.2.1.00.12

Deinstall

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
@/opt/oracle/product/11203_ee_64/db/apex/apxremov.sql

Once the scripts has completed check again:

SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = 'APEX';

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
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO UNIFORM SIZE 1M;

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
@catctx.sql CTXSYS SYSAUX TEMP NOLOCK;

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 :

SQL> SELECT STATUS FROM DBA_REGISTRY
WHERE COMP_ID = ‘APEX’; 2

STATUS
———–
VALID

## 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; 
Retry.

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;

@?/rdbms/admin/utlrp

and check again .

Happy reading,

Mathijs

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

Introduction:

For one of the projects the question came in to investigate and set up a 11.2.0.4 Real application cluster database with an extra challenge that the migration had to be done cross-platform from Oracle 10.2.0.3 on Solaris platform to 11.2.0.4.0 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.

Requirements:

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:

xtt.properties:

## 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.

tablespaces=MYDB_DATA,MYDB_EUC_DATA,MYDB_EUC_INDEX,MYDB_INDEX,MYTS,USERS

##tablespaces=MYCOMPTTS

## Source database platform ID

## platformid

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

platformid=2

## 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.

srclink=TTSLINK

## 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.

dfcopydir=/mycomp_mig_db_2_linux/mybox/rman

## backupformat

## Location where incremental backups are created.

backupformat=/mycomp_mig_db_2_linux/mybox/rman

## 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.

stageondest=/mycomp_mig_db_2_linux/mybox/rman

# 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.

storageondest=+MYDBP_DATA01/mydbp/datafile

## 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

backupondest=+MYDBP_DATA01/mydbp/datafile

## asm_home, asm_sid

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

asm_home=/opt/crs/product/11204/crs

asm_sid=+ASM1

## Parallel parameters

parallel=8

## rollparallel

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

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

rollparallel=2

## getfileparallel

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

getfileparallel=4

## 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.

desttmpdir=/mycomp_mig_db_2_linux/MYDBP/scripts

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) :
		tablespaces=MYDB_DATA,MYDB_EUC_DATA,MYDB_EUC_INDEX, MYDB_INDEX,MYTS,USERS

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
P2B.1	Source	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.
R3.5	Source	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.
T4.2	Source	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\
		transport_datafiles=’+MYDBP_DATA01/mycomptts_152.xtf’

		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.
T5	Source + Target	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;

Mathijs Bruggink