Oracle Database 12c以上的多租户功能,给我们带来了很多便利,对DBA来说大大简化了管理维护的工作量。在12c以上使用CDB的DataGuard环境中,我们有时候可能会需要做PDB的迁移,比如从一个负载比较高的数据库迁移到一个负载低的Dataguard环境中。有时候,在DataGuard环境中,我们还可能会面临主库的PDB出现异常的情况。我们可以从备库上把这个PDB迁移到一个新的容器数据库中做恢复。在DG Broker中使用migrate pluggable database命令可以很方便的做数据库的迁移。需要满足数据库的版本、补丁、compatible、数据库的选件一致且数据库使用的存储是共享等条件。下面的内容摘自Note 2088201.1部分内容,包含了两种方式Migration和Failover,两种方式的命令都是一样的,一个是在主库上操作,一个是在备库上做操作。
Migration Steps
Migration is used to move a PDB from one container database to
another. The process will unplug the PDB from the primary database of a
Data Guard configuration and plug it into a primary database of another
Data Guard configuration. The source primary must share database file
storage with the destination primary and the manifest file created by
the unplug operation on the source must be directly available to the
destination primary either through shared storage (such as an NFS mount)
or by connecting to source and destination instances running on the
same node.
Migration allows you to move a PDB to a higher version database, but
not to a database of a lower version. Note that during migration to a
higher version database the migrate command leaves the PDB closed at the
destination to allow you to perform necessary steps required to
complete any PDB upgrades. Migrations can occur to a database with the
same or higher COMPATIBLE setting, but migrating to a database with a
lower COMPATIBLE setting is not allowed.
Service creation in the destination database must be performed manually and, if using Oracle Grid Infrastructure, can be added via srvctl prior to performing the migration. Any changes required for application and end user connect strings must be performed manually as well.
The steps to perform a migration are as follows:
- 1.Although not required, stop all services on both the source primary database and any standby database(s) pertaining to the PDB to be migrated
- 2.If the source configuration has standby databases running in Active Data Guard mode and you do not have Patch 25616359 applied, close the PDB on all instances of those standbys. If they are not closed, redo apply will fail and cannot be successfully restarted until the PDB is closed allowing redo apply to process the PDB drop.
-
3.Start a dgmgrl session connecting to the source configuration
primary database. You must connect to the source database as SYSDBA
using something similar to the following:
$ dgmgrl
DGMGRL> connect sys as sysdba -
4.Once connected, execute the migrate command
DGMGRL> migrate pluggable database soe3 to container destprim using '/home/oracle/destprim/soe3.xml' connect as sys@”desthost/destprim”;If you leave off the connect clause, you will get prompted for connection information. Be sure to specify the alias or Oracle EZConnect string on the username prompt.
-or-
DGMGRL> migrate pluggable database soe3 to container destprim using '/home/oracle/fkobylan/soe3.xml':
The location specified for the manifest file must be directly accessible to both the source primary instance and the destination primary instance you are connecting to. -
5.Once the connection to the destination is established the command will:
- 1.Perform all necessary validations for the migration operation
-
2.Unplug the PDB from the primary
NOTE: For all 12cR1 releases and 12cR2 releases without the fix for bug 25616359 installed, if there is a standby database for the source primary and the PDB is open in that standby database, redo apply will stop when it attempts to apply the unplug operation. To continue redo apply processing, close the PDB and restart redo apply. - 3.Create the PDB in the destination database using the primary's datafiles (NOCOPY clause) and with STANDBYS=NONE.
- 4.Open the PDB in all instances of the destination database only if the destination is the same software version as the source
- 5.Drop the PDB from the source primary database which also drops the PDB in all of the source standby databases.
Once the command completes perform the following tasks.
- 1.Add services for the PDB as desired and start those services to resume connectivity to the PDB
- 2.Backup the PDB in the destination CDB to allow for recovery going forward
- 3.Follow the steps in Note 1916648.1 to enable recovery of the PDB at any standby databases to establish availability and disaster recovery requirements
Migration example:
-
1.Connect to the source database and show the PDBs and the current role. For migration the source database must be the primary
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 5 SOE3 READ WRITE NO 8 SOE4 READ WRITE NO SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY
-
2.Connect to the broker command line on the host shared by instances
of both the source database (fred) and the destination database (wilma)
DGMGRL> connect sys as sysdba Password: Connected to "fred" Connected as SYSDBA.
-
3.Execute the migrate command to migrate the PDB from fred to wilma. In the following command /home/oracle/fred is directly accessible to
both fred and wilma on desthost.
DGMGRL> migrate pluggable database soe3 to container wilma using '/home/oracle/fred/soe3.xml'; Username: sys@"desthost/wilma" Password: Connected to "wilma" Connected as SYSDBA. Beginning migration of pluggable database SOE3. Source multitenant container database is fred. Destination multitenant container database is wilma. Closing pluggable database SOE3 on all instances of multitenant container database fred. Unplugging pluggable database SOE3 from multitenant container database fred. Pluggable database description will be written to /home/oracle/fred/soe3.xml. Dropping pluggable database SOE3 from multitenant container database fred. Creating pluggable database SOE3 on multitenant container database wilma. Opening pluggable database SOE3 on all instances of multitenant container database wilma. Unresolved plug in violations found while migrating pluggable database SOE3 to multitenant container database wilma. Please examine the PDB_PLUG_IN_VIOLATIONS view to see the violations that need to be resolved. Migration of pluggable database SOE3 completed. Succeeded.
Failover Steps
The following pictures show the original and final projected configurations after migrating a failed PDB to the new container.
We have CDB1 with 3 PDBs (PDB1, PDB2, PDB3). CDB1 also has a Data Guard physical standby. On the same environment as the CDB1 standby, we also have CDB2 which is a read write database which will become the new host for one of the targeted PDBs.
Before Image
In this image, CDB1 and all of its PDBs are running normally. CDB1 Standby is not running Active Data Guard. CDB2 has its own PDB, PDB4.
After Image
PDB2 experiences a failure requiring a long recovery period, but the failure does not impact PDB1 and PDB3 and the standby for CDB1 continues to apply redo without error . We will use PDB2’s files at the standby site to plug into CDB2 to restore read/write application access and drop PDB2 from CDB1. This will not be a native unplug operation as that requires a read/write CDB.
Process
Failover is used to move a PDB from a standby database to another container database when the PDB at the primary database has become unusable and all other PDBs at the primary are still operating normally. Failover provides a method of failing over a single PDB without impacting other PDBs in the primary. The standby database must share database file storage with the destination primary and the manifest file created by the DBMS_PDB.DESCRIBE operation at the standby must be directory available to the destination primary either through shared storage (such as an NFS mount) or by connecting to source and destination instances running on the same node.
Failover is an unplanned operation so the goal is to minimize downtime. Therefore the please ensure the following prerequisites are met:
The source and destination databases should be running the same patch levels so additional scripts are not required to be executed. This requirement is enforced by the DGMGRL CLI MIGRATE command.. As a best practice the destination CDB should be running from the same Oracle Home as the standby database.
The destination CDB should minimally have the same database options installed (e.g. Oracle Spatial, Oracle Text, Oracle Multimedia, etc) as the source database to ensure a smooth plug-in operation.
The steps to perform a failover are as follows:
- 1.Although not required, stop all services on both the source primary database and any standby database(s) pertaining to the PDB to be migrated
- 2.If the source configuration has standby databases running in Active Data Guard mode and you do not have Patch 25616359 applied, close the PDB on all instances of those standbys. If they are not closed, redo apply will fail and cannot be successfully restarted until the PDB is closed allowing redo apply to process the PDB drop.
-
3.Start a dgmgrl session connecting to the source configuration
standby database. You must connect to the source database as SYSDBA
using something similar to the following:
$ dgmgrl
DGMGRL> connect sys as sysdba -
4.Execute the migrate command to perform the failover. NOTE: It is the same command as migrate
DGMGRL> migrate pluggable database soe3 to container destprim using '/home/oracle/soe3/soe3.xml' connect as sys@”desthost/destprim”;NOTE: If you leave off the connect clause, you will get prompted for connection information. Be sure to specify the alias or Oracle EZConnect string on the user. When doing failover, use of IMMEDIATE clause will cause a potential data loss failover. Omitting the clause causes broker to validate that the standby files are current with the primary (zero data loss failover)
-or-
DGMGRL> migrate pluggable database soe3 to container destprim using '/home/oracle/soe3/soe3.xml'; # This will prompt for user name and password -
5.Once the connection to the destination is established the command will:
- 1.Perform all necessary validations for the failover operation
- 2.Stop redo apply on the source standby if it is running
- 3.Open the standby instance in read only mode if not already open
- 4.Create the manifest on the standby at the location specified in the command using the DBMS_PDB.DESCRIBE command
- 5.Disable recovery of the PDB at the source standby
- 6.Return the source standby to mount mode if the source standby was not originally in Active Data Guard mode
- 7.Create the PDB in the destination database using the standby's datafiles (NOCOPY clause) and with STANDBYS=NONE.
- 8.Open the PDB in all instances of the destination primary database
- 9.Unplug the PDB from the source primary. If errors occur on unplug messaging is provided to user to perform cleanup manually
- 10.If unplug succeeds, drop the PDB from the source primary with the KEEP DATAFILES clause. This will also drop the PDB in all of the source standby databases.
- 6.Once the command completes, add services for the PDB as desired and start those services to resume connectivity to the PDB
- 7.Backup the PDB in the destination CDB to allow for recovery going forward
- 8.Follow the steps in Note 1916648.1 to enable recovery of the PDB at any standby databases to establish availability and disaster recovery requirements
Failover example:
-
1.Connect to the source database and show the PDBs and the current role. For migration the source database must be the standby
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ ONLY NO 4 PDB2 READ ONLY NO 8 PDB3 READ ONLY NO SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY
-
2.Connect to the broker command line on the host shared by instances
of both the source database (CDB1STBY) and the destination database
(CDB2)
DGMGRL> connect sys as sysdba Password: Connected to "cdb1stby" Connected as SYSDBA.
-
3.Execute the migrate command to failover the PDB from cdb1stby to
cdb2. In the following command /home/oracle/cdb1stby is directly
accessible to both cdb1stby and cdb2 on desthost. Using the IMMEDIATE
command causes a potential data loss failover as this clause skips
checks to confirm that the standby copy of the PDB is consistent with
the primary copy of the PDB.
DGMGRL> migrate pluggable database pdb2 immediate to container cdb2 using '/home/oracle/cdb1stby/pdb2.xml'; Username: sys@"desthost/cdb2" Password: Connected to "cdb2" Connected as SYSDBA. Beginning migration of pluggable database PDB2. Source multitenant container database is cdb1stby. Destination multitenant container database is cdb2. Connected to "cdb1" Closing pluggable database PDB2 on all instances of multitenant container database cdb1. Continuing with migration of pluggable database PDB2 to multitenant container database cdb2. Stopping Redo Apply services on source multitenant container database cdb1stby. Succeeded. Pluggable database description will be written to /home/oracle/cdb1stby/pdb2.xml. Closing pluggable database PDB2 on all instances of multitenant container database cdb1stby. Disabling media recovery for pluggable database PDB2. Restarting redo apply services on source multitenant container database cdb1stby. Succeeded. Creating pluggable database PDB2 on multitenant container database cdb2. Opening pluggable database PDB2 on all instances of multitenant container database CDB2. Unplugging pluggable database PDB2 from multitenant container database cdb1. Pluggable database description will be written to /tmp/ora_tfil0LVBCu.xml. Dropping pluggable database PDB2 from multitenant container database cdb1. Unresolved plug in violations found while migrating pluggable database PDB2 to multitenant container database cdb2. Please examine the PDB_PLUG_IN_VIOLATIONS view to see the violations that need to be resolved. Migration of pluggable database PDB2 completed. Succeeded.
Reference
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dgbkr/oracle-data-guard-broker-commands.html#GUID-03E8CCF2-0ACE-4F54-855D-0BBF839D6695
Data Guard Impact on Oracle Multitenant Environments (Doc ID 2049127.1)
Reusing the Source Standby Database Files When Plugging a non-CDB as a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273304.1)
Reusing the Source Standby Database Files When Plugging a PDB into the Primary Database of a Data Guard Configuration (Doc ID 2273829.1)
PDB Failover in a Data Guard environment: Using Data Guard Broker to Unplug a Single Failed PDB from a Standby Database and Plugging into a New Container or Migrate a Single PDB into a New Container (Doc ID 2088201.1)