RELOCATE PDB IN ORACLE 12.2

RELOCATE PDB IN ORACLE 12.2


Prerequisite:

Local Undo should be enabled on both Source and Target CDB's.
Both Databases should be in archivelog mode.



Source CDB:
==========
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

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

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

SQL> SELECT log_mode FROM   v$database;

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

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


Target CDB:
==========

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30

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

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

SQL> SELECT log_mode FROM   v$database;

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

CREATE PUBLIC DATABASE LINK clone_link CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user USING 'TEST12C';


CREATE PLUGGABLE DATABASE TESTPDB2 FROM TESTPDB2@clone_link RELOCATE;


SQL> CREATE PLUGGABLE DATABASE TESTPDB2 FROM TESTPDB2@clone_link RELOCATE;

Pluggable database created.

Elapsed: 00:00:17.59

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 TESTPDB2                       MOUNTED

SQL> alter pluggable database TESTPDB2 open;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 TESTPDB2                       READ WRITE NO


 
Observations:

During relocation Source PDB is available in read write mode for all transactions.

After sucessfull relocation Target PDB will be in mount state (Still we will be able to access source PDB).

When we run alter pluggable database TESTPDB2 open in Target CDB it will delete datafiles from source pdb.


Source alert log: (while opening Target(relocated pdb))

TESTPDB2(3):JIT: pid 26809 requesting stop
Pluggable database TESTPDB3 closed
TESTPDB2(3):JIT: pid 26809 requesting stop
Pluggable database TESTPDB2 closed
2018-01-08T23:49:17.144749+05:30
Deleted Oracle managed file /u05/oracle/data/TEST12C/6248C77A4DD8677DE0536F08D20A979A/datafile/o1_mf_temp_f57f9jgw_.dbf
Deleted Oracle managed file /u05/oracle/data/TEST12C/6248C77A4DD8677DE0536F08D20A979A/datafile/o1_mf_undotbs1_f57f9jgw_.dbf
Deleted Oracle managed file /u05/oracle/data/TEST12C/6248C77A4DD8677DE0536F08D20A979A/datafile/o1_mf_sysaux_f57f9jgv_.dbf
Deleted Oracle managed file /u05/oracle/data/TEST12C/6248C77A4DD8677DE0536F08D20A979A/datafile/o1_mf_system_f57f9jgt_.dbf
2018-01-08T23:53:11.316370+05:30


In Target alert log:

CREATE PLUGGABLE DATABASE TESTPDB2 FROM TESTPDB2@clone_link RELOCATE
2018-01-08T23:34:24.373537+05:30
Opatch validation is skipped for PDB TESTPDB2 (con_id=4)
2018-01-08T23:34:39.832787+05:30
TESTPDB2(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database TESTPDB2 with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000002fa0a4
****************************************************************
2018-01-08T23:34:41.467251+05:30
Applying media recovery for pdb-4099 from SCN 4119870 to SCN 4119891
Remote log information: count-1
thr-1, seq-52, logfile-/u05/oracle/fra/TEST12C/foreign_archivelog/TESTPDB2/2018_01_08/o1_mf_1_52_2718626603_.arc, los-4116496, nxs-18446744073709551615
TESTPDB2(4):Media Recovery Start
2018-01-08T23:34:41.468659+05:30
TESTPDB2(4):Serial Media Recovery started
2018-01-08T23:34:41.529039+05:30
TESTPDB2(4):Media Recovery Log /u05/oracle/fra/TEST12C/foreign_archivelog/TESTPDB2/2018_01_08/o1_mf_1_52_2718626603_.arc
2018-01-08T23:34:41.707953+05:30
TESTPDB2(4):Incomplete Recovery applied until change 4119891 time 01/08/2018 23:36:20
2018-01-08T23:34:41.718674+05:30
TESTPDB2(4):Media Recovery Complete (orcl)
Completed: CREATE PLUGGABLE DATABASE TESTPDB2 FROM TESTPDB2@clone_link RELOCATE
2018-01-08T23:36:52.674037+05:30


alter pluggable database TESTPDB2 open
2018-01-08T23:36:53.314248+05:30
Applying media recovery for pdb-4099 from SCN 4119891 to SCN 4119980
Remote log information: count-1
thr-1, seq-52, logfile-/u05/oracle/fra/TEST12C/foreign_archivelog/TESTPDB2/2018_01_08/o1_mf_1_52_2718626603_.arc, los-4116496, nxs-18446744073709551615
TESTPDB2(4):Media Recovery Start
2018-01-08T23:36:53.314820+05:30
TESTPDB2(4):Serial Media Recovery started
2018-01-08T23:36:53.372561+05:30
TESTPDB2(4):Media Recovery Log /u05/oracle/fra/TEST12C/foreign_archivelog/TESTPDB2/2018_01_08/o1_mf_1_52_2718626603_.arc
2018-01-08T23:36:53.524451+05:30
TESTPDB2(4):Incomplete Recovery applied until change 4119980 time 01/08/2018 23:38:32
2018-01-08T23:36:53.543585+05:30
TESTPDB2(4):Media Recovery Complete (orcl)
TESTPDB2(4):Autotune of undo retention is turned on.
2018-01-08T23:36:53.819343+05:30
TESTPDB2(4):Undo initialization finished serial:0 start:720963204 end:720963204 diff:0 ms (0.0 seconds)
TESTPDB2(4):Database Characterset for TESTPDB2 is AL32UTF8
TESTPDB2(4):Opatch validation is skipped for PDB TESTPDB2 (con_id=0)
TESTPDB2(4):Opening pdb with no Resource Manager plan active
2018-01-08T23:36:55.866368+05:30
TESTPDB2(4):JIT: pid 8627 requesting stop
2018-01-08T23:36:59.292286+05:30
Applying media recovery for pdb-4099 from SCN 4119980 to SCN 4120053
Remote log information: count-1
thr-1, seq-52, logfile-/u05/oracle/fra/TEST12C/foreign_archivelog/TESTPDB2/2018_01_08/o1_mf_1_52_2718626603_.arc, los-4116496, nxs-18446744073709551615
TESTPDB2(4):Media Recovery Start
2018-01-08T23:36:59.292871+05:30
TESTPDB2(4):Serial Media Recovery started
2018-01-08T23:36:59.343540+05:30
TESTPDB2(4):Media Recovery Log /u05/oracle/fra/TEST12C/foreign_archivelog/TESTPDB2/2018_01_08/o1_mf_1_52_2718626603_.arc
2018-01-08T23:36:59.551408+05:30
TESTPDB2(4):Incomplete Recovery applied until change 4120053 time 01/08/2018 23:38:36
2018-01-08T23:36:59.561784+05:30
TESTPDB2(4):Media Recovery Complete (orcl)
TESTPDB2(4):[8627] Successfully onlined Undo Tablespace 2.
TESTPDB2(4):Undo initialization finished serial:0 start:720969050 end:720969059 diff:9 ms (0.0 seconds)
TESTPDB2(4):Database Characterset for TESTPDB2 is AL32UTF8
2018-01-08T23:37:00.373119+05:30
TESTPDB2(4):[8627] Successfully onlined Undo Tablespace 2.
TESTPDB2(4):Undo initialization finished serial:0 start:720969733 end:720969759 diff:26 ms (0.0 seconds)
Opatch validation is skipped for PDB TESTPDB2 (con_id=4)
TESTPDB2(4):Deleting old file#28 from file$
TESTPDB2(4):Deleting old file#29 from file$
TESTPDB2(4):Deleting old file#30 from file$
TESTPDB2(4):Adding new file#17 to file$(old file#28)
TESTPDB2(4):Adding new file#18 to file$(old file#29)
TESTPDB2(4):Adding new file#19 to file$(old file#30)
TESTPDB2(4):Successfully created internal service testpdb2 at open
****************************************************************
Post plug operations are now complete.
Pluggable database TESTPDB2 with pdb id - 4 is now marked as NEW.
****************************************************************
TESTPDB2(4):Pluggable database TESTPDB2 dictionary check beginning
2018-01-08T23:37:00.599266+05:30
TESTPDB2(4):Pluggable Database TESTPDB2 Dictionary check complete
TESTPDB2(4):Database Characterset for TESTPDB2 is AL32UTF8
TESTPDB2(4):Opatch validation is skipped for PDB TESTPDB2 (con_id=0)
TESTPDB2(4):JIT: pid 8627 requesting full stop
2018-01-08T23:37:02.319388+05:30
TESTPDB2(4):Opening pdb with no Resource Manager plan active
Pluggable database TESTPDB2 opened read write
Completed: alter pluggable database TESTPDB2 open
2018-01-08T23:41:44.428383+05:30






Comments

  1. Anonymous20 April 2022 at 04:38

    Relocate Pdb In Oracle 12.2 >>>>> Download Now

    >>>>> Download Full

    Relocate Pdb In Oracle 12.2 >>>>> Download LINK

    >>>>> Download Now

    Relocate Pdb In Oracle 12.2 >>>>> Download Full

    >>>>> Download LINK dk

    ReplyDelete

Post a Comment

Popular posts from this blog

Useful OEM Queries to get Target details from OEM Repository

List Targets with TNS Listener ports configured : SELECT mgmt$target.host_name , mgmt$target.target_name , mgmt$target.target_type , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$target.target_name = mgmt$target_properties.target_name ) AND ( mgmt$target.target_type = mgmt$target_properties.target_type ) and ( mgmt$target.target_type = 'oracle_listener' ) and ( mgmt$target_properties.property_name = 'Port' ); Devora02       LISTENER_ora02                       oracle_listener Port 1529 Devora01       LISTENER_ora01                       oracle_listener Port 1529 Devora04       LISTENER_ora04                       oracle_listener Port 1529 List Machine_Names, CPU Count & Database Verion for Licensing SELECT mgmt$target.host_name , mgmt$target_properties.property_name , mgmt$target_properties.property_value FROM mgmt$target , mgmt$target_properties WHERE ( mgmt$
Read more

WARNING: Subscription for node down event still pending' in Listener Log

Image
These messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). In a non-RAC environment it is recommended to disable this subscription.   This feature was introduced in Oracle 10g. SOLUTION Set the following parameter in the listener.ora: SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFF Where <listener_name> should be replaced with the actual listener name configured in the LISTENER.ORA file. SUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name> parameter is to be placed by itself on an empty line. It will be necessary to restart or reload the listener following the addition of this parameter. This will prevent the messages from being written to the log file and may also prevent the TNS Listener from hanging periodically.  See (10g only) NOTE 340091.1 Intermittent TNS Listener Hang, New Child Listener Process Forked Please Note: Setting SUBSCRIBE_FOR_NODE_DOWN_<listener_n
Read more

ORA-65139: Mismatch between XML metadata file and data file

Error: CDB$ROOT@EBTUPRDC> CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse; CREATE PLUGGABLE DATABASE EBTUSPRDPDB1 using '/home/oracle/EBTUSPRDC.xml' nocopy tempfile reuse * ERROR at line 1: ORA-65139: Mismatch between XML metadata file and data file /u05/odb/ORADATA_1/EBTUSPRDC/datafile/system.1531.807800993 for value of fcpsb (2230502002 in the plug XML file, 2230505492 in the data file) Fix: Don't open Non-CDB database until we complete pdb creation other wise we will get above error. Shutdown immediate; startup mount; alter database open read only; create xml file. shutdown immediate; Connect to CDB and Create Pluggable database .
Read more