Oracle: Migrate PDB to One other Database – DZone – Uplaza

If you wish to migrate or relocate the PDB from one database to a different, there are a number of choices obtainable in Oracle. Right here, we are going to focus on just a few of them. The supply and goal database might be in a standalone database, RAC, cloud, or autonomous database. After verifying the PDB is on the goal, open it for buyer entry and take away it from the supply database based mostly on firm coverage. 

Conditions

• The goal database model ought to identical or larger than the supply database.
• The supply database ought to be accessible from the goal.
• The diploma of parallelism ought to be calculated correctly.
• Conscious of DBA privileged username/password on the supply to create DB hyperlink
• The encryption key’s completely different from the consumer password. Should have entry to the encryption key – it could be both database or tablespace or desk degree
• The consumer within the distant database that the database hyperlink connects to, will need to have the CREATE PLUGGABLE DATABASE privilege.
• The character set on supply and goal ought to be appropriate.

Recognized Points

• Tablespace could also be in a giant file.
• Tablespace could also be encrypted.
• Utilizing a database hyperlink, the goal database ought to have the ability to entry the supply database. Create an Entry Management Listing (ACL) or whitelist the IP handle and port if required.
• To entry the DB hyperlink, both enter the supply database info in TNSnames.ora or give a full connection string.
• Steady community connectivity between supply and goal
• RMAN jobs might intrude with refreshable cloning.
• Port from supply to focus on ought to be opened to repeat the information or to entry the DB hyperlink
• Distant CDB makes use of native undo mode. In any other case, distant PDB could also be opened in read-only mode. 
• Copy/cloning/synchronization between supply and goal might differ by community visitors and velocity. 

Just a few of the approaches are as follows:

Method 1: Unplug and Plug the PDB

On this strategy, unplug the PDB into an XML file, copy information information and XML information to the goal host, and create a pluggable database utilizing the XML file. Discover summary steps within the picture proven beneath:

The steps are:

Step 1

Unplug the PDB on the supply. As we’re unplugging the PDB, there isn’t any want to shut it.

ALTER PLUGGABLE DATABASE  
     UNPLUG INTO '/source_pdb.xml';

Step 2

Copy the XML file and information information on the goal host manually. For Home windows, copy, ftp, or related instructions can be utilized. For Linux, scp , rsync, ftp, or related instructions can be utilized.

Step 3

Plug within the PDB to focus on. It can retain the parameters from the supply PDB.

CREATE PLUGGABLE DATABASE  
    USING '/source_pdb.xml'
    NOCOPY TEMPFILE REUSE;

Step 4

Open the PDB in read-write mode on the goal.

ALTER PLUGGABLE DATABASE  
    OPEN
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    SAVE STATE
    INSTANCES=ALL;

Method 2: Information Pump Export (expdp) and Information Pump Import (impdp)

Utilizing information pump export and import, you may copy the PDB from one database to a different. That is finest for an offline copy of the PDB. Throughout on-line copying, there could also be overhead to repeatedly generate the info pump file based mostly on Oracle System Change Quantity (SCN). The goal PDB ought to exist on the goal database. An summary is proven within the picture beneath:

Step 1

Create a database listing on the supply.

CREATE OR REPLACE DIRECTORY export_dir AS '';

Step 2

Grant the listing on the supply.

GRANT READ, WRITE ON DIRECTORY export_dir TO system;

Step 3

Carry out information pump export on the supply.

expdp system/password@ 
    full=Y 
    listing=export_dir 
    dumpfile=.dmp 
    logfile=expdp_.log

Step 4

Transfer the information from the supply to the goal manually. Please use a mission project-approved technique to switch the information. For Linux, this may be achieved by way of rsync, scp, or ftp instructions.

Step 5

Create a listing on the goal database.

CREATE OR REPLACE DIRECTORY import_dir AS '';

Step 6

Grant the listing on the goal database.

GRANT READ, WRITE ON DIRECTORY import_dir TO system;

Step 7

Import information on the goal database utilizing information pump import.

impdp system/password@ 
    full=Y 
    listing=import_dir 
    dumpfile=.dmp 
    logfile=impdp_.log

Step 8

Clear up the directories on the supply and goal databases.

-- Supply
drop listing export_dir;
-- Goal
drop listing import_dir;

Step 9

Open the PDB in read-write mode on the goal database.

ALTER PLUGGABLE DATABASE  
    OPEN 
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    SAVE STATE 
    INSTANCES=ALL;

Method 3: Refreshable Clone

On this strategy, create the PDB on the goal database and refresh it after each sure interval utilizing the database hyperlink. This will likely be an internet operation. The final section would be the offline operation, the place it’s worthwhile to deliver the supply PDB in read-only, refresh manually, and open the goal PDB.

Step 1

Affirm whether or not PDB is opened on the supply database.

SELECT depend(*) cnt FROM v$pdbs 
  WHERE title="" 
    AND open_mode="READ WRITE" 
    AND restricted='NO'

Step 2

Create a database hyperlink on the goal database.

CREATE DATABASE LINK Target_to_Source_DBLink 
    CONNECT TO SYSTEM 
    IDENTIFIED BY  
    USING 'Source_IP_Address:Source_Port/Source_CDB';

Step 3

Affirm whether or not the database hyperlink is accessible on the goal.

SELECT sysdate FROM twin@Target_to_Source_DBLink

Step 4

Create a refreshable clone PDB on course.

CREATE PLUGGABLE DATABASE 
    FROM @Target_to_Source_DBLink 
    REFRESH MODE EVERY  MINUTES 
    KEYSTORE IDENTIFIED BY  
    create_file_dest=;

Step 5 

Test the PDB refresh lag standing on course.

SELECT 
    last_refresh_scn,
    solid(scn_to_timestamp(last_refresh_scn) as date) refresh_Time 
FROM dba_pdbs 
  WHERE pdb_name=""

Step 6

Make PDB read-only mode (all cases) on supply.

ALTER PLUGGABLE DATABASE  
    CLOSE IMMEDIATE
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    OPEN READ ONLY 
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    SAVE STATE 
    INSTANCES=ALL;

Step 7

Set refresh mode to handbook on course.

ALTER PLUGGABLE DATABASE  REFRESH MODE MANUAL;

Step 8

Full the refresh on course (the ultimate refresh).

ALTER SESSION SET CONTAINER = ;
ALTER PLUGGABLE DATABASE REFRESH;

Step 9

Look forward to a while to complete the handbook refresh. Lag might be verified utilizing the question talked about above in step 5.

Step 10

Set refresh mode to none on course.

ALTER PLUGGABLE DATABASE  
    REFRESH MODE NONE;

Step 11

Open the PDB in read-write mode on course.

ALTER PLUGGABLE DATABASE  
    CLOSE IMMEDIATE 
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    OPEN
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    SAVE STATE 
    INSTANCES=ALL;

Step 12

Drop the database hyperlink on course.

DROP DATABASE LINK Target_to_Source_DBLink;

Method 4: Relocate PDB Utilizing DBCA

The Database Configuration Assistant (DBCA) can be utilized to relocate a pluggable database (PDB) in Oracle 19c and above. Will probably be an internet operation. For this, the PDB ought to be in archivelog mode and native undo ought to be enabled.

Step 1

Create a standard consumer within the goal database.

CREATE USER c##remote_clone_user 
    IDENTIFIED BY  
    CONTAINER=ALL;
GRANT 
    CREATE SESSION, 
    SYSOPER, 
    CREATE PLUGGABLE DATABASE 
  TO c##remote_clone_user 
  CONTAINER=ALL;

Step 2

Confirm the distant CDB is in native undo mode and archivelog mode on the goal database.

SELECT property_name, property_value 
FROM database_properties 
WHERE property_name="LOCAL_UNDO_ENABLED";

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             TRUE

SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

Step 3

Add a TNS entry within the supply database.

TARGET_CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TARGET_CDB)
    )
  )

Step 4

Create a public database hyperlink within the native CDB, pointing to the distant CDB.

CREATE PUBLIC DATABASE LINK target_to_source_db_link 
    CONNECT TO c##remote_clone_user 
    IDENTIFIED BY  
    USING 'TARGET_CDB';

Step 5

Login to the supply host and change to Oracle consumer.

Step 6

Set the atmosphere variable or set the oracle_sid atmosphere variable to the supply database.

Step 7

Run dbca (database configuration assistant) command in silent mode.

dbca -silent 
    -relocatePDB 
    -pdbName  
    -sourceDB  
    -remotePDBName  
    -remoteDBConnString :/target_cdb 
    -remoteDBSYSDBAUserName sys 
    -remoteDBSYSDBAUserPassword  
    -dbLinkUsername c##remote_clone_user 
    -dbLinkUserPassword 

Step 8

Open the goal PDB.

ALTER PLUGGABLE DATABASE  
    OPEN
    INSTANCES=ALL;
ALTER PLUGGABLE DATABASE 
    SAVE STATE
    INSTANCES=ALL;

Conclusion: Submit-Steps

• If the supply database model is decrease than the goal database, improve the PDB (apply the info patch).
• Be sure that the invalid object depend is identical in each supply and goal databases.
• If the supply and goal PDB are on the identical host, affirm the service is pointing to the right PDB.
• Clear up the PDB from the supply database based mostly on the corporate retention coverage.