PDB switchover
The procedure for remotely refreshing a PDB in Oracle Database 12c was previously discussed in Creating and Refreshing a PDB Remotely. Oracle Database 18c introduced a new capability in this area that enables the roles o
The procedure for remotely refreshing a PDB in Oracle Database 12c was previously discussed in Creating and Refreshing a PDB Remotely. Oracle Database 18c introduced a new capability in this area that enables the roles of the source and destination PDBs to be switched, in other words, it facilitates a switchover between the two PDBs.
In this article, we first create a PDB remotely and then demonstrate how to perform a switchover between the source and destination PDBs.
1. Create a User, PDB, and Database Link on the Source and Destination
cdb1 (source)
SQL> create pluggable database pdbsource admin user u identified by u;
Pluggable database created.
SQL> alter pluggable database PDBSOURCE open;
Pluggable database altered.
SQL> create user db_usef identified by pass;
User created.
SQL> grant sysoper,dba,sysdba to db_usef container=all;
Grant succeeded.
SQL> create public database link LINKclone connect to DB_USEF identified by pass using ‘cdb18c’;
Database link created.
cdb2 (Destination)
SQL> create user db_usef identified by pass;
User created.
SQL> grant sysdba,dba to db_usef container=all;
Grant succeeded.
SQL> create public database link LINK_SOURCE connect to DB_USEF identified by pass using ‘db18c’;
Database link created.
2. Create the PDB Remotely on cdb2
cdb2 (Destination)
SQL> CREATE PLUGGABLE DATABASE pdbclone FROM PDBSOURCE@LINK_SOURCE CREATE_FILE_DEST=’/u01/18c_oradata/CDB18C’ REFRESH MODE EVERY 1 MINUTES;
Pluggable database created.
3. Create a User and Table in PDBSOURCE for Testing
cdb1 (source)
SQL> alter session set container=PDBSOURCE;
Session altered.
SQL> create user usef identified by a;
User created.
SQL> grant dba to usef;
Grant succeeded.
SQL> create table usef.tbl1(last_name varchar2(9));
Table created.
SQL> insert into usef.tbl1 values(‘USEFZADEH’);
1 row created.
SQL> commit;
Commit complete.
The data in this table is also available in PDBCLONE after the refresh operation, which is performed every minute.
cdb2 (Destination)
SQL> alter pluggable database pdbclone open read only;
Pluggable database altered.
SQL> alter session set container=pdbclone;
Session altered.
SQL> select last_name from usef.tbl1;
LAST_NAME
———
USEFZADEH
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database pdbclone close;
Pluggable database altered.
4. Perform a Switchover Between PDBSOURCE and PDBCLONE
The goal is to switch the roles of PDBSOURCE and PDBCLONE so that PDBCLONE becomes the primary PDB and can accept changes in read/write mode, while PDBSOURCE can be used for reporting purposes when required.
Perform the following steps.
cdb2 (Destination)
SQL> alter pluggable database pdbclone open read only;
Pluggable database altered.
cdb1 (source)
SQL> alter session set container=PDBSOURCE;
Session altered.
The general syntax of the SWITCHOVER operation is:
ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER;
In this environment, execute the following command:
SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@linkclone SWITCHOVER;
Pluggable database altered.
The switchover completes successfully. The following commands demonstrate the resulting roles of the two PDBs.
cdb2 (clone)
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
7 PDBCLONE READ WRITE NO
cdb1 (source)
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
4 PDBSOURCE MOUNTED
During the switchover operation, the following messages can also be observed in the alert log.
cdb1 (source)
2018-04-29T16:46:31.417366+04:30
PDBSOURCE(4):ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@LINKclone SWITCHOVER
2018-04-29T16:46:31.809754+04:30
PDBSOURCE(4):JIT: pid 22466 requesting stop
PDBSOURCE(4):Buffer Cache flush started: 4
PDBSOURCE(4):Buffer Cache flush finished: 4
2018-04-29T16:46:32.170496+04:30
PDBSOURCE(4):While transitioning the pdb 4 to clean state, clearing all its abort bits in the control file.
Pluggable database PDBSOURCE closed
2018-04-29T16:46:35.836492+04:30
Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_temp_fgcfdd7k_.dbf
Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_undotbs1_fgcfdd7j_.dbf
Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_sysaux_fgcfdd7h_.dbf
Deleted Oracle managed file /u01/18c_oradata/DB18C/6AFC495838351172E0530288200A1577/datafile/o1_mf_system_fgcfdd7f_.dbf
2018-04-29T16:46:38.386672+04:30
Opatch validation is skipped for PDB PDBSOURCE (con_id=4)
2018-04-29T16:46:40.648340+04:30
PDBSOURCE(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database PDBSOURCE 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-0x00000000000000fb
****************************************************************
2018-04-29T16:46:41.872741+04:30
Applying media recovery for pdb-4099 from SCN 7824847 to SCN 7825420
Remote log information: count-1
thr-1, seq-52, logfile-/u01/arch/parlog_1_52_6363a705_974028799.arc, los-7791131, nxs-18446744073709551615
PDBSOURCE(4):Media Recovery Start
2018-04-29T16:46:41.875469+04:30
PDBSOURCE(4):Serial Media Recovery started
PDBSOURCE(4):max_pdb is 8
2018-04-29T16:46:41.922623+04:30
PDBSOURCE(4):Media Recovery Log /u01/arch/parlog_1_52_6363a705_974028799.arc
2018-04-29T16:46:42.249189+04:30
PDBSOURCE(4):Incomplete Recovery applied until change 7825420 time 04/29/2018 16:45:01
2018-04-29T16:46:42.250989+04:30
PDBSOURCE(4):Media Recovery Complete (db18c)
PDBSOURCE(4):Completed: ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@LINKclone SWITCHOVER
2018-04-29T16:46:42.310450+04:30
PDBSOURCE(4):alter pluggable database refresh
cdb2 (clone)
2018-04-29T16:44:43.238462+04:30
PDBCLONE(7):alter pluggable database refresh
PDBCLONE(7):Completed: alter pluggable database refresh
2018-04-29T16:44:52.219206+04:30
PDBCLONE(7):Opatch XML is skipped for PDB PDBCLONE (conid=7)
PDBCLONE(7): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated
PDBCLONE(7):JIT: pid 6896 requesting stop
PDBCLONE(7):Buffer Cache flush started: 7
PDBCLONE(7):Buffer Cache flush finished: 7
Pluggable database PDBCLONE closed
2018-04-29T16:44:55.325901+04:30
Applying media recovery for pdb-4099 from SCN 7824084 to SCN 7824621
Remote log information: count-1
thr-1, seq-158, logfile-/u01/arch/parlog_1_158_26eb8840_972296216.arc, los-7815768, nxs-18446744073709551615
PDBCLONE(7):Media Recovery Start
2018-04-29T16:44:55.327301+04:30
PDBCLONE(7):Serial Media Recovery started
PDBCLONE(7):max_pdb is 7
PDBCLONE(7):WARNING: process USER (ospid: 6896) was unable to attach SMR.
2018-04-29T16:44:55.371061+04:30
PDBCLONE(7):ORA-27300: OS system dependent operation:open failed with status: 2
ORA-27301: OS failure message: No such file or directory
ORA-27302: failure occurred at: sskgmsmr_7
2018-04-29T16:44:55.384489+04:30
PDBCLONE(7):Media Recovery Log /u01/arch/parlog_1_158_26eb8840_972296216.arc
2018-04-29T16:44:55.891033+04:30
PDBCLONE(7):Incomplete Recovery applied until change 7824621 time 04/29/2018 16:46:33
2018-04-29T16:44:55.893566+04:30
PDBCLONE(7):Media Recovery Complete (cdb18c)
PDBCLONE(7):Undo initialization recovery: err:0 start: 1567231498 end: 1567231507 diff: 9 ms (0.0 seconds)
PDBCLONE(7):[6896] Successfully onlined Undo Tablespace 2.
PDBCLONE(7):Undo initialization online undo segments: err:0 start: 1567231508 end: 1567231520 diff: 12 ms (0.0 seconds)
PDBCLONE(7):Undo initialization finished serial:0 start:1567231498 end:1567231522 diff:24 ms (0.0 seconds)
PDBCLONE(7):Database Characterset for PDBCLONE is AL32UTF8
PDBCLONE(7):Buffer Cache flush started: 7
PDBCLONE(7):Buffer Cache flush finished: 7
2018-04-29T16:44:56.270556+04:30
PDBCLONE(7):While transitioning the pdb 7 to clean state, clearing all its abort bits in the control file.
2018-04-29T16:44:56.756133+04:30
PDBCLONE(7):Undo initialization recovery: err:0 start: 1567232232 end: 1567232233 diff: 1 ms (0.0 seconds)
PDBCLONE(7):[6896] Successfully onlined Undo Tablespace 2.
PDBCLONE(7):Undo initialization online undo segments: err:0 start: 1567232233 end: 1567232286 diff: 53 ms (0.1 seconds)
PDBCLONE(7):Undo initialization finished serial:0 start:1567232232 end:1567232289 diff:57 ms (0.1 seconds)
Opatch validation is skipped for PDB PDBCLONE (con_id=7)
PDBCLONE(7):Deleting old file#43 from file$
PDBCLONE(7):Deleting old file#44 from file$
PDBCLONE(7):Deleting old file#45 from file$
PDBCLONE(7):Adding new file#28 to file$(old file#43). fopr-1, newblks-32000, oldblks-19200
PDBCLONE(7):Adding new file#29 to file$(old file#44). fopr-1, newblks-44800, oldblks-15360
PDBCLONE(7):Adding new file#30 to file$(old file#45). fopr-1, newblks-12800, oldblks-12800
PDBCLONE(7):Successfully created internal service PDBCLONE at open
****************************************************************
Post plug operations are now complete.
Pluggable database PDBCLONE with pdb id – 7 is now marked as NEW.
****************************************************************
PDBCLONE(7):Database Characterset for PDBCLONE is AL32UTF8
2018-04-29T16:44:57.367254+04:30
PDBCLONE(7):JIT: pid 6896 requesting full stop
2018-04-29T16:44:58.275048+04:30
Violations: Type: 2, Count: 3
PDBCLONE(7):***************************************************************
PDBCLONE(7):WARNING: Pluggable Database PDBCLONE with pdb id – 7 is
PDBCLONE(7): altered with errors or warnings. Please look into
PDBCLONE(7): PDB_PLUG_IN_VIOLATIONS view for more details.
PDBCLONE(7):***************************************************************
2018-04-29T16:44:58.788594+04:30
PDBCLONE(7):Opening pdb with no Resource Manager plan active
Pluggable database PDBCLONE opened read write
2018-04-29T16:44:59.007884+04:30
PDBCLONE(7):Opatch XML is skipped for PDB PDBCLONE (conid=7)
PDBCLONE(7): AUDSYS.AUD$UNIFIED (SQL_TEXT) – CLOB populated
📰 Read the original article on Dev.to AI
Originally published by Dev.to AI. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.