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 out there in Oracle. Right here, we’ll talk about just a few of them. The supply and goal database will 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 primarily based on firm coverage. 

Conditions

• The goal database model ought to identical or larger than the supply database.
• The supply database needs to be accessible from the goal.
• The diploma of parallelism needs to be calculated correctly.
• Conscious of DBA privileged username/password on the supply to create DB hyperlink
• The encryption secret is totally different from the person password. Should have entry to the encryption key – it might be both database or tablespace or desk degree
• The person within the distant database that the database hyperlink connects to, should have the CREATE PLUGGABLE DATABASE privilege.
• The character set on supply and goal needs to be suitable.

Recognized Points

• Tablespace could also be in a giant file.
• Tablespace could also be encrypted.
• Utilizing a database hyperlink, the goal database ought to be capable to entry the supply database. Create an Entry Management Listing (ACL) or whitelist the IP deal with and port if required.
• To entry the DB hyperlink, both enter the supply database data in TNSnames.ora or give a full connection string.
• Steady community connectivity between supply and goal
• RMAN jobs could intrude with refreshable cloning.
• Port from supply to focus on needs to be opened to repeat the recordsdata 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 could fluctuate by community visitors and velocity. 

A number of of the approaches are as follows:

Strategy 1: Unplug and Plug the PDB

On this method, unplug the PDB into an XML file, copy knowledge recordsdata and XML recordsdata 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 a want to shut it.

ALTER PLUGGABLE DATABASE  
     UNPLUG INTO '

Step 2

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

Step 3

Plug within the PDB to focus on. It is going to 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;

Strategy 2: Knowledge Pump Export (expdp) and Knowledge Pump Import (impdp)

Utilizing knowledge pump export and import, you may copy the PDB from one database to a different. That is greatest for an offline copy of the PDB. Throughout on-line copying, there could also be overhead to repeatedly generate the information pump file primarily based 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 knowledge pump export on the supply.

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

Step 4

Transfer the recordsdata from the supply to the goal manually. Please use a challenge project-approved methodology to switch the recordsdata. 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 knowledge on the goal database utilizing knowledge 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;

Strategy 3: Refreshable Clone

On this method, create the PDB on the goal database and refresh it after each sure interval utilizing the database hyperlink. This can be a web-based operation. The final part would be the offline operation, the place you want to convey the supply PDB in read-only, refresh manually, and open the goal PDB.

Step 1

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

SELECT depend(*) cnt FROM v$pdbs 
  WHERE identify="" 
    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

Verify 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 track.

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

Step 5 

Verify the PDB refresh lag standing on track.

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

ALTER PLUGGABLE DATABASE  REFRESH MODE MANUAL;

Step 8

Full the refresh on track (the ultimate refresh).

ALTER SESSION SET CONTAINER = ;
ALTER PLUGGABLE DATABASE REFRESH;

Step 9

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

Step 10

Set refresh mode to none on track.

ALTER PLUGGABLE DATABASE  
    REFRESH MODE NONE;

Step 11

Open the PDB in read-write mode on track.

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

DROP DATABASE LINK Target_to_Source_DBLink;

Strategy 4: Relocate PDB Utilizing DBCA

The Database Configuration Assistant (DBCA) can be utilized to relocate a pluggable database (PDB) in Oracle 19c and above. It is going to be a web-based operation. For this, the PDB needs to be in archivelog mode and native undo needs to be enabled.

Step 1

Create a standard person 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 person.

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: Put up-Steps

• If the supply database model is decrease than the goal database, improve the PDB (apply the information patch).
• Ensure that the invalid object depend is identical in each supply and goal databases.
• If the supply and goal PDB are on the identical host, verify the service is pointing to the proper PDB.
• Clear up the PDB from the supply database primarily based on the corporate retention coverage.