PDB Switchover

By -

 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.

1. Create a User, PDB, and Database Link on the Source and Destination

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.
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

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

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.
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

Write on Medium
SQL> alter pluggable database pdbclone open read only;

Pluggable database altered.
SQL> alter session set container=PDBSOURCE;

Session altered.
ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER;
SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@linkclone SWITCHOVER;

Pluggable database altered.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         7 PDBCLONE                       READ WRITE NO
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         4 PDBSOURCE                      MOUNTED
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
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

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — error_message_details Parameter for Displaying Error Details

By -
  error_message_details   is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to   ON , we can see the exact value that caused errors such as   ORA-00001: unique constraint violated   within the error message itself. For example, in the following scenario, enabling the  error_message_details  parameter helps identify the value that triggered the error: SQL > create table tbl1(id number primary key); Table created. SQL > variable B number; SQL > exec :B: = 2547 ; PL / SQL procedure successfully completed. SQL > insert into tbl1 values (:B); 1 row created. SQL > insert into tbl1 values (:B); ERROR at line 1 : 'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)' 'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists' Help: https: / / docs.oracle.com ...
Read more

Buffer Busy Wait and Read by Other Session in Oracle

By -
Image
Assume a block is accessed and pinned in the buffer cache by Session 1 (each buffer can be pinned by only one session at a time). While this block is pinned, if another session attempts to access the same block, it must wait. This type of wait is called  Buffer Busy Wait . What distinguishes Buffer Busy Wait from some other wait events is that the contention occurs at the  block level . Even if the two sessions intend to access different rows within the same block, they are still competing at the block level. As you know, Oracle blocks can contain different types of data. For example, one block may hold user data, while another may store control information. Therefore, contention for blocks can be divided into several categories: Data Block Data Segment Header Undo Header Undo Block Let’s briefly review the role of each block type in this wait event. 1) Data Block This refers to the blocks that store object data. Buffer Busy Waits at this level can have several causes: Poorly ...
Read more