A pluggable database is a self-contained, fully-functional Oracle database. We often need to move the pluggable database from one CDB to another CDB.
This will be a unplug/plug operation where outage will depend on the location of source and destination CDB
(1) When moving between servers with shared storage, such as is typically the case in Cloud environments when the PDB is being moved for load balancing, this outage can be very brief because it’s only a metadata operation – there’s no need physically to move the datafiles themselves.
(2) When moving the PDB from one data center to another will have more outage as all the data must be physically moved. For large databases, depending on the capabilities of the underlying network, this process may take considerable time.
With Oracle database Release 12.2 ,PDB Relocate is a new method of moving a PDB between CDBs. This is introduced in 12.2 to allow Oracle Database Cloud Services to meet both performance and availability components of Service level agreements with customers. On premise database has the same functionality also
PDB Relocate is built “on top” of Refreshable PDB technology in Oracle database 12c release 2. which in turn built on top of hot clone. These all are dependent on the local undo feature in Oracle database Release 12.2
Local Undo in Oracle Database 12c R2(12.2)
When independent listeners are involved, transparent connection forwarding may also be involved in PDB relocation
PDB relocate is achieved in two steps
1. Relocate PDB to the new server
create pluggable database PDB1 from PDB1@dblink relocate ;
2. Open PDB in its new location
COLUMN name FORMAT A30
SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB1';
NAME OPEN_MODE
------- --------------
PDB1 MOUNTED
The PDB is opened in read-write mode to complete the process.
ALTER PLUGGABLE DATABASE pdb1 OPEN;
Checking status now
SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB1';
NAME OPEN_MODE
------- --------------
PDB1 READ WRITE
Now lets take a look at the detailed description of each steps
Step 1
It involves Hot cloning the PDB from its original location to its desired new location.When this step is complete, there will be two transactionally consistent copies of this PDB, one in the source server and one in the target server. For the duration of the operation, processing continues uninterrupted on the database in its original location. Users of an application or applications connected to the database will be unaware that a relocation is underway.
This is the same as creation of a Refreshable PDB
Refreshable PDB in Oracle Database 12.2
All existing application connections, and new connections made during this step, continue to be to the PDB in its original location.
The data file copy and iterative redo apply runs in the background and can be executed in advance of the final relocate operation, for example, initiate the relocate on Friday and open the relocated PDB on Sunday before the business week begins
Step 2
This step actually completes the relocation. It’s largely transparent to the application user, although at the end of the operation, connections to the PDB will have been switched from its original location to the new location. An important component of this step is to ensure that all transactions committed in the PDB in its source location are preserved in the target location.
Oracle processed this in four step process
(a) PDB on destination is Open read-only. So it is immediately available there for query purposes, while connections attempting DML will “spin”.
So Read-only connections are immediately forwarded to the new hosting CDB TNS LISTENER and new read-write connections are forwarded to the new hosting CDB TNS LISTENER where they spin until the Destination PDB is opened in a transaction consistent state.
The behavior of any queries against the PDB in its target location – and it’s only queries that are allowed at this stage – is exactly as it would have been had they been performed in the PDB in its source location at same time as PDB was refreshed.
(b) Connection forwarding. All connections must be “drained” from the source location and re-established in the target location. New connection requests are forwarded to the target location and made to the PDB there. Modern, well-designed applications typically interact with the underlying database via a connection pool, which can support large numbers of application users via a small number of (relatively expensive – in terms of computing resources database connections. As transactions complete and these connections are released to the connection pool, the connections are closed. Longer running transactions or poorly written applications may result in connections which are not naturally released in a timely manner. After a brief interval, these connections are automatically terminated and re-established at the destination. This process is performed incrementally, so as to avoid a login “storm” at the destination.
(c) Application of incremental transactions in target location. Any redo data that has accumulated for that source PDB between these times is copied to the destination and applied there. At this stage, the target PDB will be an exact copy of the source PDB as of time t2. However, this includes both committed transactions and uncommitted transactions. What is required now is to rollback the uncommitted transactions. This is achieved by applying the undo for all these uncommitted transactions. Because we have ensured that no transactions have occurred in the origin, we can see that the destination has now caught up.
(d) Open destination read-write. The datafiles are now deleted from the origin.
How to check if Local Undo and archivelog is in Use
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
SQL>
How to check file copy progress during step 1
The FILE COPY progress for all online provisioning operations can be monitored by querying the view v$session_longops
select opname, message from v$session_longops
OPNAME MESSAGE
------------ -------------------------
kpdbfCopyTaskCbk kpdbfCopyTaskCbk: /uabc/app/oracle/or
adata/cdb1/CDB : 104448 out of 104448 Blocks done
Important points about Connecting Forwarding
The details of connection forwarding depend on the configuration of listener(s) at the PDB’s source and target locations. There are three possibilities:
(a) Shared listener. This situation would typically involve a relocation between CDBs in the same server, such as would apply if the PDB is being relocated to a CDB with a different set of options installed. In this case the PDB is re-registered with the listener in its new location.
(b) Cross-registered listeners. This situation is typical of relocation between servers within a data center, perhaps for load balancing purposes. In this case the PDB is re-registered in its new location with both listeners.
(c) Independent listeners with no cross-registration. This will typically be the situation when relocating a PDB between data centers; perhaps from a data center on a customer’s premises to an Oracle Database Cloud Service.
With no cross-registration, this situation is complicated by the fact that the listener in the target location has no “knowledge” of the source CDB and vice versa.
In this case we offer two levels of availability; “high” and “maximum”.
High availability is aptly named because indeed the PDB is more or less immediately available in its new location for direct connections. The syntax for a high availability PDB relocation is as follows:
create pluggable database My_PDB from My_PDB@DBLink relocate availability high;
This statement should be issued from the target CDB. (Availability high is the default.)
Maximum availability goes a step further by automatically forwarding connections made to the PDB in its original location to its new location. The syntax for a maximum availability PDB relocation is as follows:
create pluggable database My_PDB from My_PDB@DBLink relocate availability max;
This statement should be issued from the target CDB.
The relocate command when issued with the relocate availability max clause will relocate the Source PDB from Serv1 CDB1 to the destination Serv2 CDB2 and will update the listener service definition on the original host CDB1 TNS LISTENER service entry with the following:
$oracle@serv1-cdb1: lsnrctl services
Service “app1" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Handler(s):
"COMMON" established:0 refused:0 state:ready
FORWARD SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=Serv2.corp.com
(PORT=1521))
It is now important to guard against the possibility of creating another PDB in the original location with the same name as the PDB that has been relocated. If this were allowed to happen it could result in a very confusing situation To avoid this, when performing a maximum availability PDB relocation, Oracle create a “tombstone” in the source CDB. This is a metadata entry in the source CDB, which may be seen by querying v$containers from the Root container of the source CDB. The “tombstone” PDB will be visible, with a status of “relocated”.
The preserved tombstone is a PDB artifact unique to the online relocate operation where there is no shared SQLNET LISTENER network and the availability max clause is used. The tombstone preserves the PDB name space in the old hosting CDB so that a PDB of the same name cannot be created in the CDB until the SQLNET LISTENER service connection forwarding is removed. This includes the relocation back to the original hosting CDB while the tombstone is still present.
But this is not expected to be a permanent situation. As described above, a maximum availability PDB relocation involves automatic connection forwarding. This enables clients to continue to connect to the application database without having to make any connect string changes. However, this does involve an extra network “hop”. It is anticipated that, at some convenient juncture, application connect strings can be changed to
provide direct connections to the database in its new location. At this point, it is possible simply to drop the tombstone PDB from the origin. Thereafter, there would be no impediment to relocating the PDB back to its original container if so desired.
Hope you like this article on PDB relocate in Oracle database 12c Release 2
Related Articles
Top Oracle 12c New Features for developers : This post has the compilation of the top Oracle 12c New Features for developers with brief description and usage notes to help
Oracle Database 12c :How to Create the container database : How to Create the container database in Oracle 12c using DBCA or manually using sql scripts, with OMF, Important things to check before creating
Hot PDB cloning in Oracle database 12.2 : Check out this post for detailed description on Hot PDB cloning in Oracle Database release 12.2.How oracles makes it work in the background
Top 10 oracle 12c new features :Check out this post for the top oracle 12c new features.Oracle 12c is the latest release of oracle database for cloud enablement.
oracle database 19c new features :Most significant oracle database 19c new features are Auto indexing,sql Quarantine,ADDM support for Pluggable database, privilege analysis etc.
Oracle Database Interactive Quick Reference Guide | 11g & 12c