Purpose of PDB Relocation
This technique is the fastest way to move a PDB with minimal or no down time. Otherwise, unplugging the source PDB requires a PDB outage until the PDB is plugged in to the target CDB.
When moving a PDB between data centers, or from an on-premises environment to a cloud environment, all the data must physically move. For large PDBs, this process may take considerable time, possibly violating availability components of an SLA. PDB relocation eliminates the outage completely. You can relocate the PDB without taking the application offline, changing the application, or changing network connection strings.
Overview of Plug & Play features on PDBs:
With the release of Oracle Multitenant, one of the main benefits is the portability of each individual Pluggable Database (PDB).
It is possible to move the self-contained contents of a single PDB from one Container Database (CDB) to another.
In the initial release of Oracle RDBMS 12cR1 this required the PDB to be unplugged from the source CDB and plugged into the destination CDB which could have the potential for extensive downtime.
With 12cR2 the concept of PDB Relocation was introduced, allowing movement of a PDB from one container to another through two SQL commands where the physical copy of the files is performed while the source PDB remains accessible to users and applications.
The final phase of relocation introduces a short downtime of less than 10 minutes to complete the operation.
The primary use cases for PDB Relocation are from a planned maintenance perspective:
•Performing load balancing by moving a PDB and its associated resource utilization to a new CDB/host.
•Changing High Availability (HA) tiers for the PDB, such as moving from a lower service level requirement tier to a higher one.
•Affecting an upgrade of an individual PDB while leaving all existing PDBs at the existing version.
Each of these operations is a physical move of the PDB from one container to another.
There is no reorganization of the PDB or its data as part of this move.
This post will explain the process for relocating a PDB with minimal down time, describing:
•Prerequisites for relocate
•Preparation steps
•The steps performed during the two phases of relocation
•Post relocation steps
🚩SOLUTION🚩
Prerequisites
•The source and destination CDBs must be Oracle Database 12cR2 or later.
It is highly recommended they are Oracle Database 19c or later.
•The source and destination CDBs platforms must be same endian type.
•The source CDB must be in ARCHIVELOG mode.
•Apply the following patches to the Oracle Homes for both the source and destination container databases (CDBs):
◦Required
▪ Patch 29469563 – Fixes an issue where PDB hot clones fail with ORA-15001, included in 19.9 and later
▪ Patch 26001677 – Implements REFRESH MODE for PDB relocate, included 19.8 and later
◦Required for environments with Transparent Data Encryption (TDE)
▪Patch 29175638 - Adds support for INCLUDING SHARED KEY clause to avoid ORA-46659 errors.
▪Patch 32220709 - Ensures shared PDB master keys display in v$encryption_keys, included in 19.14 and later.
◦Highly recommended
▪ Patch 28374604 – Deletes partial redo logs created during refresh operations, available 19.1 and later
•Ensure the Oracle Homes for both the source and destination CDBs have matching versions and patch inventories.
This ensures that no additional steps are required after opening the PDB at the destination.
•Ensure the source and destination CDBs have the same Oracle Options (e.g. Oracle Spatial, Oracle Label Security, Oracle JVM, etc) installed and the same versions of the options.
•Both the source and destination CDBs must be using Local Undo, each PDB has its own set of undo tablespaces.
•Use the STANDBYS=NONE clause to defer the recovery of the PDB in destination standby databases when the destination CDB is part of a Data Guard environment.
It is currently not possible to automatically maintain standby databases during PDB relocation operations, deferring recovery allows redo apply at any standby databases to continue.
To enable recovery you will need to restart redo apply when synchronizing a standby with the relocated PDB’s datafiles. Use the instructions in Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant to enable recovery of the PDB after relocation has completed.
The following process has been tested in both Oracle Exadata Database Service (ExaCS) and Oracle Exadata Database Service Cloud.
PDB operations performed outside of the Console via SQL will not reflect in the Console until a subsequent sync with the OCI control plane, they should be visible in the console and API-based tools within 45 minutes of completion.
This is expected behavior.
For Data Guard configurations, PDBs that are in deferred recovery status will not appear in the Console, only PDBs with recovery enabled appear in the Console.
After enabling recovery of a PDB, the status change will appear in the Console on a subsequent sync of the OCI control plane.
Platform and Character Set Prerequisites
You must meet the following prerequisites:
- The platforms of the source CDB and the destination CDB must meet the following requirements:
- They must have the same endianness.
- The database options installed on the source platform must be the same as, or a subset of, the database options installed on the destination platform.
- If the character set of the destination CDB is not AL32UTF8, then the source CDB and destination CDB must have compatible character sets and national character sets.
If the character set of the destination CDB is AL32UTF8, then this requirement does not apply.
Note:Oracle Multitenant does not support a LOB in one container from being accessed by a container with a different character set using data links, extended data links, or the CONTAINERS() clause. For example, if the CDB root and salespdb have different character sets, then a CONTAINERS() query run in the CDB root should not access LOBs stored in salespdb.
Performing a Pluggable Database (PDB) Relocate
Steps to Prepare for the Relocate
- On the target destination environment, ensure connectivity from all database instances back to the source CDB.
The following are examples of TNS aliases for both the source and destination CDBs.
In Oracle RAC environments, these alias definitions must be accessible to all database instances in both the source and destination environments and should use the SCAN name as the host.
The following shows example entries:
DestinationCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <destination_scan_name>)(PORT = <listener_port>))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <destination service name for CDB>)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
) ) )
SourceCDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <source_scan_name>)(PORT = <listener_port>))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = <source service for CDB>)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
) ) )
Create a common user in the source cdb$root. This user must have CREATE CDB and SYSOPER roles in addition to being granted SELECT on CDB_PDBS.
In the following example, "c##” is required in the user name to denote a common user.
The grants are required to allow the common user to perform PDB relocation.
On Source CDB:
SQL> alter session set container=cdb$root;
SQL> create user c##<user> identified by <password>;
User created.
SQL> grant create session, resource to c##<user> container=all;
SQL> grant create pluggable database to c##<user> container=all;
SQL> grant sysoper to c##<user> container=all;
SQL> alter user c##<user> set container_data=all container=current;
SQL> grant select on cdb_pdbs to c##<user>;
Create a database link in the destination CDB connecting to the common user just created in the source CDB and verify connectivity
On Destination CDB:
SQL> alter session set container=cdb$root;
SQL> create database link <link name> connect to c##<user> identified by <password> using '<source CDB TNS Alias>';
Database link created.
SQL> select count(*) from dual@<link name>;
COUNT(*)
----------
1
Modify memory and session settings at the destination CDB for items like Shared Pool, PGA Aggregate Target and Parallel Query Servers to prepare for the new PDB(s) being relocated.
This will vary based on the workload characteristics of the PDB from the source CDB.
Save the state of all PDBs to be relocated in all instances.
This will ensure that services currently running in the source CDB/PDB will start when the PDB is opened at the destination as part of Phase 2 of relocation.
On SourceCDB:
SQL> alter session set container=cdb$root;
SQL> alter pluggable database <pdbname> save state instances=all;
For environments running Grid Infrastructure (GI), this will option will only be used for the duration of the relocate operation.
Once the PDB is relocated and services defined at the destination, the saved state will be discarded.
Set ALLOW_MULTIPLE_REDIRECTS_<listener_name>=YES for all listeners on the destination in the listener.ora and reload the listeners.
For RAC environments only SCAN listeners should be added in each node’s listener.ora in addition to configuring for all 3 SCAN listeners.
Using this setting allows the source CDB to use LISTENER CONNECTION FORWARDING and forward connection attempts at the source CDB/PDB to the destination CDB/PDB.
For SCAN listeners you should only issue the reload on the node which that particular listener is running.
ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN1=YES
ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN2=YES
ALLOW_MULTIPLE_REDIRECTS_LISTENER_SCAN2=YES
For each host running a SCAN listener, connect to the host and reload the appropriate SCAN listener.
[Destinationhost]$ lsnrctl reload listener_scan1
Configuring LISTENER CONNECTION :
FORWARDING is only required when the source and destination CDBs do not share listeners.
If the source and destination CDBs share listeners, upon restart the PDB services will register as part of the destination CDB on the same listeners so no forwarding is necessary.
For Oracle Real Application Cluster (RAC) and Oracle Restart environments, gather the information for all services defined for the CDB via srvctl.
Run the following command for each service defined for the PDB.
Although the database contains all services defined for the PDB, their definitions in the Oracle Cluster Registry (OCR) contain additional settings that are not stored with the PDB catalog.
These settings must be retrieved and the definitions entered in CRS for the PDB as part of the destination CDB.
To determine which services reside in the PDB, connect to the source PDB and run the following query.
Do not query for the default service (NAME=’<PDBNAME>’) nor services owned by SYS. (NAME like ‘SYS.%’).
On Source CDB:
SQL> alter session set container=<pdbname>;
SQL> select name from dba_services where name <> ‘<PDBNAME>’ and name not like ‘SYS.%’;
For each service returned in the previous query, retrieve the information from CRS.
[sourcehost]$ srvctl config service –d <source CDB unique name> -s <source PDB service name>
In the case of Oracle RAC or Oracle Restart environments, for each of the services retrieved above add them to the OCR for the destination CDB/PDB combination.
This can be done prior to creation of the PDB at the destination site.
[destinationhost]$ srvctl add service –d <destination CDB db_unique_name> -pdb <pdbname> -service <service name>….<additional settings>
Steps to Perform Phase 1 of the Relocate – PDB Remains Available
This phase copies the files from the source to the destination. The time to complete the relocate depends on the size of the PDB and the number of parallel processes available. The PDB(s) remain open and accessible during this time.
Multiple relocates can be run concurrently in different sessions.
If opting to run multiple relocates concurrently, for Real Application Cluster environments balance the sessions over multiple instances.
The command will run for as long as it takes to copy the files from the source CDB to the destination CDB.
The file copies are automatically done in parallel using parallel query (PQ) slaves from the destination CDB. The file copies will follow normal priority assignment to PQ slaves, it is not possible to reduce or raise priority of access to PQ slaves.
Note that the PARALLEL clause can be used on the RELOCATE command but can only be used to RESTRICT the number of slaves that are used for the file copy operation.
It is not possible to increase slaves beyond that defined by PARALLEL_MAX_SERVERS in the destination CDB.
Use of the REFRESH MODE clause is highly recommended. This allows the destination copy of the PDB to be automatically periodically refreshed (redo from the source CDB is shipped and applied to the relocated PDB’s datafiles) until you are ready to complete the operation. REFRESH MODE had the potential to significantly shorten the application downtime required when the relocated PDB is being opened at the destination.
A REFRESH MODE value of 30 minutes is recommended to balance resource utilization (shipping and applying the redo) and downtime (maximum volume of redo that will be required at PDB open time).
Connect to the destination CDB root and issue the relocate command.
The following example shows includes the handling of Transparent Data Encryption (TDE) keys.
The keys for the PDB will be transported from the source CDB keystore to the destination CDB keystore as part of the relocate process by specifying the destination CDB keystore password on the relocate command.
The keystore password needs to be supplied even for AUTOLOGIN keystores.
For environments not using TDE do not specify the KEYSTORE IDENTIFIED BY “<PASSWORD> INCLUDING SHARED KEY” clauses.
Note that the name of the PDB being created must match the name of the source PDB.
On Destination CDB:
SQL> create pluggable database <pdbname> from <pdbname>@<link name>
relocate availability max
keystore identified by "<destination TDE keystore password>"
including shared key
refresh mode every 30 minutes standbys=none;
Sample of silent:
./dbca -silent
-relocatePDB
-sourceDB remcdb1
-remotePDBName rempdb1
-remoteDBConnString
remcdb1host:1521/reminst
-remoteDBSYSDBAUserName remSYS
-remoteDBSYSDBAUserPassword
remsyspwd
-dbLinkUsername c##adminuser_remcdb1
-dbLinkUserPassword pwd4dblinkusr
-sysDBAUserName locSYS
-sysDBAPassword locsyspwd
-pdbName relpdb1
The above command will cause the files belonging to the PDB at the source CDB to be copied to the destination CDB, using whatever PQ slaves are available at the destination to perform the copies.
The copies will be done similar to RMAN SECTIONSIZE processing in that multiple PQ slaves can operate on a single file, the command will determine how large the sections should be.
The command will not return control until all file copies have completed.
While this is occurring the source PDB is still accessible to application and end user activity.
To limit the number of PQ slaves the command is using, add the PARALLEL <pq slave count> clause to the end of the command.
Any value of <pq slave count> larger than the PARALLEL_MAX_SERVERS initialization parameter setting in the destination CDB will be ignored, the command will default back to PARALLEL_MAX_SERVERS.
The AVAILABILITY MAX clause enables listener connection forwarding for this PDB after the relocation has completed.
When the relocated PDB is opened at the destination, a “tombstone” PDB will remain at the source CDB until it is manually dropped.
The tombstone has minimal size (tablespaces for system and sysaux) and does not open.
If the AVAILABILITY MAX clause is omitted, the source database will not perform listener connection forwarding.
The clause can be omitted if the source and destination share the same listener network.
Specifying REFRESH MODE EVERY NN MINUTES enables a background process to periodically (every NN minutes) retrieve redo generated at the source CDB and apply the redo for the source PDB to file copies of that PDB at the destination.
Using this clause when there will be a large time gap between Phase 1 and Phase 2 can significantly reduce the down time required to complete Phase 2 in addition to more accurately bounding the amount of down time you can expect.
To monitor the progress of the file copies, connect to cdb$root in another session in the destination CDB and query v$session_longops. The following is a short example of the output:
SQL> select opname, sofar, totalwork, time_remaining, message from v$session_longops where time_remaining > 0;
OPNAME SOFAR TOTALWORK TIME_REMAINING
------------- ------- ---------- --------------
MESSAGE
kpdbf CopyTaskCbk-368 9103 131072000 215967
kpdbf CopyTaskCbk-368: <source_file_name> 11: 9103 out of 131072000 Blocks done
kpdbf CopyTaskCbk-368 68847 131072000 26639
kpdbf CopyTaskCbk-368: <source_file_name> 11: 68847 out of 131072000 Blocks done
If issues arise during this first phase of relocation, it is possible to drop the copy of the PDB at the destination and rerun Phase 1.
However, if Phase 1 has completed and any instance of the destination CDB is bounced, the SAVE STATE will cause the PDB open on instance restart and automatically start Phase 2 of the relocate.
On Destination CDB(if errors occurred on relocating)
SQL> alter session set container=cdb$root;
SQL> drop pluggable database <pdbname> including datafiles;
Steps to Perform Phase 2 of the Relocate – Application Downtime Occurs
At the destination site, open the PDB.
This will cause the following to occur:
•Refresh of the PDB, retrieving redo generated at the source CDB since the last refresh and applying it to the destination PDB copies of the datafiles.
•Quiesce of the source PDB
This closes the PDB in the source CDB.
What is Quiescing:
A quiesced CDB allows only DBA transactions, queries, fetches, or PL/SQL statements.
Application downtime begins here.
•Retrieve the last redo generated at the source CDB and apply to the destination PDB copies of the datafiles, making the source and destination identical.
This is the redo generated by activity on the source PDB from the time Phase 2 started until the source PDB was quiesced.
•Open of the PDB at the destination
•Start of the services that were running as part of the SAVE STATE.
This will not add the services to CRS for the new CDB/PDB combination.
To complete the relocate:
Connect to the destination CDB root and issue the following:
On Destination CDB:
SQL> alter session set container=cdb$root;
SQL> alter pluggable database <pdbname> open instances=all;
This command will take a few moments to execute depending on how much redo must be retrieved and applied from the source CDB, in addition to impact of any latency in the network between the source and destination CDBs.
Steps to Perform after the Relocate has Completed
Check for plug in violations for the PDB
Plug in violations are only detected as part of PDB open.
Fixing these issues may require a bounce of the PDB.
Connect to the CDB root at the destination and issue:
On Destination CDB:
SQL> alter session set container=cdb$root;
SQL> select * from pdb_plug_in_violations where name =’<PDBNAME>’ and status <> 'RESOLVED' and type <> 'WARNING';
This will retrieve any violations that have not been resolved during the PDB open.
Note that many violations (e.g. issues with different SPFILE settings between source and destination, detected patch mismatches even when the patches applied between the source and destination are an exact match) will be resolved on next PDB open.
If you did not do so as part of Steps to Prepare for the Relocate, in the case of Oracle RAC or Oracle Restart environments, for each of the services retrieved above add them to the OCR for the destination CDB/PDB combination.
[destinationhost]$ srvctl add service –d <destination CDB db_unique_name> -pdb <pdbname> -service <service name>….<additional settings>
Modify connect strings for all applications and users to point to the new PDB location.
If the source and destination CDBs are part of the same network, there is no need to modify connect strings.
The following steps should only be done once all connect strings have been modified and you no longer need to use LISTENER CONNECTION FORWARDING
In the case of Oracle RAC or Oracle Restart environments, for each of the services retrieved above remove them from the OCR for the source CDB.
[sourcehost]$ srvctl remove service –d <source CDB db_unique_name> -service <service name>
Note this should not be done until the connect strings have been modified to point applications and users to the new location of the PDB.
Drop the tombstone PDB from the source CDB using the INCLUDING DATAFILES clause
On Source CDB:
SQL> alter session set container=cdb$root;
SQL> drop pluggable database <pdbname> including datafiles;
For environments using CRS to maintain services, connect to the destination CDB and discard the saved state information for the PDB.
On Destination CDB:
SQL> alter session set container=cdb$root;
SQL> alter pluggable database <pdbname> discard state instances=all;
If no additional relocates are to be performed at this time, remove the ALLOW_MULTIPLE_REDIRECTS* entries from the destination listener.ora files and reload the listeners.
For SCAN listeners you should only issue the reload on the node which that particular listener is running.
[Destinationhost]$ lsnrctl reload listener_scan1
For Data Guard environments, we should follow the instructions in Making Use Deferred PDB Recovery and the STANDBYS=NONE Feature with Oracle Multitenant that enable recovery of the PDB at the destination standby and I will explain in next posts.
More info:
https://docs.oracle.com/en/database/oracle/oracle-database/19/multi/relocating-a-pdb.html#
No comments:
Post a Comment