Tuesday, June 13, 2006

APEX and Full Exp/Imp DB

Today I had to create a new database, exactly as an other one.
APEX was installed on that (source) database, so I was curious to see if it would work on the target as well... and yes it almost did work!

To reproduce what I did:
1. Take a full export of source database
2. Create new database and do a full import into that database
3. Recompile all invalid objects (in $ORACLE_HOME/rdbms/admin - SQL>@utlrp)
4. Test APEX (after changing/adding DAD if necessary)

My APEX didn't work correctly... I saw that there were some invalidated packages.
To correct APEX, these are the grants the invalid packages needed (grant by sys):
>grant execute on SYS.DBMS_LOCK to flows_020000;
>grant execute on SYS.DBMS_FLASHBACK to flows_020000;
>grant execute on SYS.DBMS_SYS_SQL to flows_020000;

I also needed to change the password of my workspace users (admin, ...) to be able to login again.
You can also create a new admin user by following script:

    alter user FLOWS_020000 account unlock; (as system)
    -> connect to the FLOWS_020000 user and run this procedure
    begin
    wwv_flow_api.set_security_group_id(p_security_group_id=>10);
    wwv_flow_fnd_user_api.create_fnd_user(
    p_user_name => 'admin2',
    p_email_address => 'dimitri.gielis@telenet.be',
    p_web_password => 'admin2') ;
    end;
    /
    commit;
    alter user FLOWS_020000 account lock; (as system)


Now everything is up-and-running again! APEX is great!

5 comments:

  1. Which user did you use to do the export SYS or SYSTEM ?

    ReplyDelete
  2. Hi Dimitri,

    i also want to import export Apex database.

    can you please provide me steps to complete all this successfully.

    Regards
    Wahid

    ReplyDelete
  3. Hi Dimitri,

    I also want to export and import the Apex database for one of the migration process.
    please provide me steps to complete this task successfully.

    Regards
    Wahid

    ReplyDelete
  4. Hi Wahid,

    It's not recommended to export the APEX_XXXXXX schema, in fact there are more schemas used by APEX.
    I recommend using the ExportUtility that you find in the utilities folder and export all workspace and applications.
    Next in your new environment you install APEX, and import the workspaces and applications.
    That way they have the same ids.

    Hope that helps,
    Dimitri

    ReplyDelete