Thursday, December 24, 2015

brief technical note on images export import to new apex 41 database from hpux 11gr2 to linux 11gr2

We created a database on our new platform, installed apex in it with apexins.sql

Then we made a full export, we imported everything except apex_040100 user
 
Next we created workspaces and applications in the new environment.
We exported these workspace and applications with scripting from the internet.
the apexexport utility in apex41 gave empty files as a result.

INFO APEXEXport

$ cd ..apex41/apex/utilities
$ export CLASSPATH=.:${ORACLE_HOME}/oui/jlib/classes12.jar
$ read wachtwoord
# exporteer eerst de WORKSPACES middels test_export
$ java  oracle.apex.APEXExport -db  HOSTNAME:1521:DATABASE_SERVICE -user system -password $wachtwoord -instance

Gave me the export of the applications
But the workspace files were empty, THIS is a bug.

https://apexplained.wordpress.com/2012/03/20/workspace-application-and-page-export-in-plsql/

I used to export the workspaces.
I installed the procedures and then used :

REM desc PRO_EXPORT_WORKSPACE
REM PROCEDURE PRO_EXPORT_WORKSPACE
REM  Argument Name                  Type                    In/Out Default?
REM  ------------------------------ ----------------------- ------ --------
REM  P_WORKSPACE_ID                 NUMBER                  IN
REM  P_INCLUDE_TEAM_DEV             BOOLEAN                 IN
REM  P_ORA_DIR                      VARCHAR2                IN
REM
REM MY_ORA_DIR
REM
col workspace_id format 999999999999999999
declare
cursor c_ws is
select workspace_id from apex_workspaces where workspace_id > 11 ;
r_ws c_ws%rowtype ;
begin
 for r_ws in c_ws
 loop
   PRO_EXPORT_WORKSPACE( r_ws.workspace_id , TRUE,'MY_ORA_DIR') ;
 end loop ;
end;
/


We exported the source data with datapump to file dp_EVERYTHING_%U.dmp

expdp full=y DIRECTORY=EXP_DB DUMPFILE=dp_EVERYTHING%u LOGFILE=EXP_DB:dp_EVERYTHING.log FILESIZE=20G PARALLEL=4 FLASHBACK_TIME=SYSTIMESTAMP
We created a database directory called IMP_DB in the target database.

$ cat images_impdp.par

DUMPFILE=dp_EVERYTHING%U.dmp
parallel=4
directory=IMP_DB
table_exists_action=append
tables=FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$


$ cat images_impdp_WWV_FLOW_REPOSITORIES_TO_DIKPATER.par







DUMPFILE=dp_EVERYTHING%U.dmp
parallel=4
directory=IMP_DB
tables=APEX_040100.WWV_FLOW_CSS_REPOSITORY,APEX_040100.WWV_FLOW_IMAGE_REPOSITORY,APEX_040100.WWV_FLOW_HTML_REPOSITORY

remap_schema=(APEX_040100:DIKPATER)
remap_tablespace=(SYSAUX:USERS)

SQL> alter table flows_files."WWV_FLOW_FILE_OBJECTS$" disable all triggers ;
$ impdp parfile=images_impdp.par

SQL> alter table flows_files."WWV_FLOW_FILE_OBJECTS$" enable all triggers ;

Now we handle :

 apex_040100.WWV_FLOW_IMAGE_REPOSITORY
 apex_040100.WWV_FLOW_CSS_REPOSITORY
 apex_040100.WWV_FLOW_HTML_REPOSITORY

!!! create a user DIKPATER ( in my case) to hold the 3 REPOSITORY tables temporary.
!!! with tablespace quota ...and create table

$ impdp parfile=images_impdp_WWV_FLOW_REPOSITORIES_TO_DIKPATER.par

SQL> select * from all_objects where lower(object_name)like '%wwv_flow_%repository%'
and owner = 'APEX_040100' and object_type = 'TABLE'

SQL> select * from apex_040100.WWV_FLOW_CSS_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_CSS_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_CSS_REPOSITORY
select * from dikpater.WWV_FLOW_CSS_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_CSS_REPOSITORY
enable all triggers ;

SQL> select * from APEX_040100.WWV_FLOW_IMAGE_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_IMAGE_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_IMAGE_REPOSITORY
select * from dikpater.WWV_FLOW_IMAGE_REPOSITORY
where (flow_id, image_name, security_group_id) not in ( select flow_id, image_name, security_group_id from apex_040100.WWV_FLOW_IMAGE_REPOSITORY) ;
SQL> alter table apex_040100.WWV_FLOW_IMAGE_REPOSITORY
enable all triggers ;

SQL> alter table apex_040100.WWV_FLOW_HTML_REPOSITORY disable all triggers ;
SQL> insert into apex_040100.WWV_FLOW_HTML_REPOSITORY
select * from dikpater.WWV_FLOW_HTML_REPOSITORY ;
SQL> alter table apex_040100.WWV_FLOW_HTML_REPOSITORY
enable all triggers

Now the images are imported and ready to use.
I found many hits on the internet about missing images after import, they suggest just to import WWV_FLOW_FILE_OBJECTS$ alone, but in my opinion it is not enough.
I cannot find if this is a supported method, but maybe this can be helpfull.

Regards.

Dik Pater

EM12c Metric Extension output



I made a Metric Extension called dblink.
Al it does is :
SELECT  d.name ||':'
  ||':'  || dbl.owner
  ||':'  || dbl.db_link
  ||':'  || dbl.username
  ||':'  || dbl.host
  ||':'  || dbl.created RIJ
, 'DBLINKINFO:' text
FROM dba_db_links dbl,
  v$database d

Next I added rij as the key column and tekst as the value column.

Next Next Finish
Deploy to targets

And now you can see ALL your database links from a single point of view in your EM12C repository database.

select entity_name,metric_key_value, count(*)
from GC_METRIC_STR_VALUES a
where metric_group_label='dblink'
group by entity_name,metric_key_value

Regards

Dik Pater
The Netherlands