A) How to connect SQL Developer Tools with Oracle Autonomous Cloud DB and how to Export Dump from local Db and import to Oracle Autonomous Cloud DB

-

 A)    How to connect SQL Developer Tools with Oracle Autonomous Cloud DB  and how to Export Dump from local Db and import to Oracle Autonomous Cloud DB

 

Useful Link

 

https://blogs.oracle.com/apex/how-to-move-your-apex-applications-to-autonomous-database

 

https://blogs.perficient.com/2019/12/17/how-to-import-oracle-db-dumps-into-adw-using-sql-developer/

 

https://oracle-base.com/articles/vm/oracle-cloud-autonomous-data-warehouse-adw-import-data-from-object-store

 

1)    Connect Sql Developer with Oracle Autonomous Cloud DB

 

è Go to Oracle Cloud Infrastructure and login with your credential

 

 

Go to  Autonomous Transaction Processing

 

 

Click on Autonomous Database which you have created

 

 

Click on Service Console

 

 

go to Administration and Download the client Credential (Wallet) key

 

 

 

 

 

 

Enter the Password and click on download load button for wallet credentials.

 

 

System will download with .zip extension

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now go to your local  Sql Developer

 

 

 

1)    Give Connection Name :- MyCloud

2)    Authentication Type:- Default

3)    User Name:- <apex  Admin User >

4)    Password : <Apex Admin Password>

5)    Connection Type:- Cloud Wallet

6)    Configuration File:- <Wallet>.zip

 

 

 

 

 

2)    Now Restore the Local Dump file to oracle cloud DB

Go to Object Storage and create new bucket to upload the exported dump

 

Perform Expdp command in your local system with attached  parameter

 

T:\>expdp blzlive/blzlive@cispdb directory=backup dumpfile=blzive.dmp exclude=cluster,db_link parallel=2

 

 

 

Make sure your compartment got selected and then click on create bucket button to create your bucket to hold the dump object

 

 

 

Click on created bucket cis_backup  and upload the dump file

 

 

 

 

 

 

 

View Object will show the details of DMP object URL  (Copy that URL for future use)

 

Go to Pre- Authentication Request to create the Key with URL

 

 

 

 

 

 

 

 

 

Select Object Radio and mentioned .dmp object Name which you have uploaded.

 

 

Click on Create- Pre Authenticated Request and copy the  URL

 

 

 

3)    Token Gernation

 

A)    Generate the Token  

 

 

                        Click User Setting à Go to Resources à Click on Auth Tokens

 

 

Click on Generate Token and Copy that Token Key

 

 

 

Copy the Token  +G4VkW}EVM_CwgkUxsQC

 

 

B)    Get User Name

 

 

 

 

 

C)    Go to SQL Developer and create credential for access the object.dmp path as URL

 

Perform   DBMS_CLOUD.CREATE_CREDENTIAL

 

 

Create Credential

 

BEGIN

  DBMS_CLOUD.CREATE_CREDENTIAL(

    credential_name => ‘BLZLIVE’,

    username => ‘oracleidentitycloudservice/mazen.manasseh@perficient.com’, ( Please Check Stage B)

    password => ‘<Enter the Authentication token here>’ );  (Pls check Stage A)

END;

 

Remove credential

 

BEGIN

   dbms_cloud.drop_credential(credential_name => 'DEF_CRED_NAME');

END;

/

 

 

 

 

 

 

D)    Import the data

 

 

 

Right click on Import Job  and select Data Pump Import Wizard

 

 

Select

Type of Import :- Schemas

Choose Input File = Credential:Blzlive (which you have created on Stage A )

File Name or URL =  Pre Authenticated Requested URL

      

https://objectstorage.me-jeddah-1.oraclecloud.com/p/<key>/n/axvj9dbvct4v/b/CIS_BACKUP/o/Test.DMP

 

Click on  Next

 

 

            Add Source & Destination

             Normally we use to mentioned in Impdp syntax like remap_schema:blizve:blzlive

 

 

Note:- If Destination User has not created then system will create automatically. Later pleas perform Sql> alter user blzive identified by <password>

 

Click on Next to perform option

 

Click on Next for schedule or perform current

 

 

 

Click on finished. Data will restore.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Export Your Apps with the APEX Application Archive productivity app

 

The APEX Application Archive productivity app ships with Application Express and allows you to quickly create archives of your applications and workspace resources. These archives are stored in tables which are created in your local schema.  The convenience of this approach is that you will be archiving your APEX application app definitions in tables in your local schema, and now with a single schema export from your database, you can easily move both applications and data at once.  In your Autonomous Database, you will simply install the same APEX Application Archive app, and this will allow you to easily restore these applications in your new workspace.

 

Sign in to your on-premises workspace

From the App Builder home page, click Create and choose Productivity App

Choose APEX Application Archive and click Install App

Click Next and Install App

Click Run App icon

Accept the defaults and click Complete Setup

Click Archive Applications

Ensure all applications are in the right side of the shuttle, click Next and then Create Archive.

        

 

 

Export Your Workspace Schema

 

The next step is to migrate your workspace schema from your on-premises APEX instance to Autonomous Database. You use Data Pump to do the schema level export that will include the database user and the schema. Per the Autonomous Database documentation, the recommended parameters to use for expdp are as follows:

 

exclude=cluster,db_link

parallel=2

schemas=schema_name

dumpfile=export.dmp

 

expdp blzlive/blzlive@cispdb directory=backup dumpfile=blzive.dmp exclude=cluster,db_link parallel=2

 

The following example exports the PROJECTS schema which is associated with an on-premises workspace using the recommended Data Pump parameters from the Autonomous Database documentation. You should export the schema using a privileged user that can create a database user. Make sure the user doing the export is granted read and write on the default DATA_PUMP_DIR directory.

 

Create APEX Workspace on Autonomous Database Matching Name and ID for your on-premises Workspace

Sign in to your on-premises workspace

Click the ? icon in the upper right, choose About, and then copy your Workspace ID

        

Log in to your Autonomous Transaction Processing or Autonomous Data Warehouse service console Click Development on the left side, then click the APEX link.

The Application Express Administration Services sign-in page will appear. If you already created a workspace, the workspace sign-in page will appear instead. In this case, click the Administration Services link to proceed.

Enter the Autonomous Database administrator (ADMIN) password and click Sign In to Administration.

                       

        

In Administration Services, click Create Workspace and select the Database User that you imported and make sure the Workspace Name matches your on-premises workspace. Expand the Advanced section and enter the same Workspace ID as you copied in

Step 2. Click Creat Workspace.

Click the link of the workspace name in the success message.  Alternatively, you can click the ADMIN username in the upper right and click Sign Out. Sign in to the workspace you just created. Click Set APEX Account Password and enter the same password you just used to sign in to Oracle APEX.

 

 

Remove database objects

1.    Sign in to your Oracle Autonomous Transaction Processing or Oracle Autonomous Data Warehouse service console.

2.     Upload the following script and run it.

 

drop package eba_archive;
drop package eba_archive_fw;
drop sequence eba_archive_seq;
drop table eba_archive_users          cascade constraints;
drop table eba_archive_errors         cascade constraints;
drop table eba_archive_access_levels  cascade constraints;
drop table eba_archive_notifications  cascade constraints;
drop table eba_archive_error_lookup;
drop table eba_archive_preferences;

Click App Builder, then click Install a Productivity or Sample App.

 

1.    Find the APEX Application Archive app in the App Gallery list and click Install App, click Next, then click Install App.

 

2.    Repeat this process for all workspaces you wish to import into your Autonomous Database.

 

Comments

Post a Comment

Popular posts from this blog

Telegram Integration with Oracle

-
 How to Integrate Telegram with Oracle Apex 1) create Telegram Account in your Mobile like whatsapp 2) Ones you register in telegram apps serach for BotFather 3) To Create New Bot u need to enter below command  /newbot  Alright, a new bot. How are we going to call it? Please choose a name for your bot. Macsbot Good. Now let's choose a username for your bot. It must end in `bot`. Like this, for example: TetrisBot or tetris_bot. MacsOmanBot Done! Congratulations on your new bot. You will find it at t.me/MacsOmanBot. You can now add a description, about section and profile picture for your bot, see /help for a list of commands. By the way, when you've finished creating your cool bot, ping our Bot Support if you want a better username for it. Just make sure the bot is fully operational before you do this. Use this token to access the HTTP API:<Here you will prompt with your api> 4) /mybots (this command will return the list of bots which you have created) ...
Read more

The username or password for the connection pool named |apex||, are invalid, expired, or the account is locked

-
  1. Execute the following command from SQL Plus as the SYS user:  Conn sys/Pass@xe as sysdba  (connect as container if 12c then  ALTER SESSION SET container = CDB$ROOT;) Alter user ords_public_user identified by xxxx; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; Connect as PDB Conn sys/Pass@xepdb1 as sysdba (if 12c  ALTER SESSION SET container = PDB1;) Alter user APEX_PUBLIC_USER identified by xxxx; Alter user APEX_REST_PUBLIC_USER identified by xxxx; Alter user APEX_LISTENER identified by xxxx; 2. Unlock the users: ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK; ALTER USER APEX_LISTENER ACCOUNT UNLOCK; ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK; 3. Now create a profile for the APEX related users: Create profile apex_public_profile limit password_life_time unlimited; 4. Assign the profile to the APEX users: alter user apex_public_user profile apex_public_profile; alter user apex_rest_public_user profile apex_public_pr...
Read more

Enabled SQL Developer Web (SDW) & ORDS Database API ORDS 19.4

-
SQL Developer Web (SDW)  ORDS 19.2 to 19.4 Migration /  Basic Setup to Enable ORDS Database API Bsic Information  Installing Oracle REST Data Services https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-673D35DD-9811-42F8-A500-4F494F79C470 https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.4/aelig/installing-REST-data-services.html#GUID-2C42D982-BF08-48E3-A4C6-3FE1A78287E3 Using Oracle® SQL Developer Web https://docs.oracle.com/en/database/oracle/sql-developer-web/19.2.1/sdweb/about-sdw.html#GUID-AF7601F9-7713-4ECC-8EC9-FB0296002C69 How to Configure / Run ORDS for SQL Developer Web and APEX in Standalone Mode: ORDS 19.4 + (Doc ID 2628004.1) GOAL This document is an overview on how to configure and run ORDS standalone for SQL Developer Web using the command line interface. It also includes the steps related to setting up APEX, since the ability to run APEX as well as SQL Develo...
Read more