PDB switchover در اوراکل 18c

شیوه بروزرسانی از راه دور pdb در اوراکل 12c قبلا مورد بررسی قرار گرفت(ایجاد و بروزرسانی pdb به صورت از راه دور) در اوراکل 18c قابلیت جدیدی در این زمینه ارائه شد که امکان تغییر نقش pdb مبدا و مقصد و به عبارت دیگر، switchover بین این pdbها را هموار می کند.

در ادامه ضمن ایجاد یک pdb به صورت از راه دور، طریقه انجام switchover بین دو pdb مبدا و مقصد را نمایش خواهیم داد.

1.ایجاد کاربر، pdb و database link در مبدا و مقصد:

–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(clone)

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.ایجاد pdb در cdb2 به صورت از راه دور:

 

–cdb2(clone)

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.جهت تست، کاربر و جدولی را در pdbsource ایجاد می کنیم:

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

اطلاعات این جدول در pdbclone هم قابل مشاهده می باشد(با بروز رسانی در هر دقیقه):

–cdb2(clone)

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.حال قصد داریم نقش بین pdbsource و pdbclone را تغییر دهیم(switchover) طوری که pdbclone قابلیت ثبت اطلاعات را داشته باشد(read write mode) و pdbsource در صورت لزوم برای گزارش گیری استفاده شود. برای انجام این کار، مراحل زیر را طی می کنیم:

–cdb2(clone)

SQL> alter pluggable database pdbclone open read only;

Pluggable database altered.

–cdb1(source)

SQL> alter session set container=PDBSOURCE;

Session altered.

فرم کلی دستور switchover:

ALTER PLUGGABLE DATABASE refresh_mode FROM clonepdb@dblink SWITCHOVER;

اجرای دستور switchover در این محیط:

SQL> ALTER PLUGGABLE DATABASE REFRESH MODE EVERY 1 MINUTES FROM pdbclone@linkclone SWITCHOVER;

Pluggable database altered.

با این دستورات، عملیات switchover با موفقیت به انجام رسید. دستورات زیر هم این نکته را به خوبی نشان می دهند:

–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

همچنین در زمان انجام عملیات switchover، پیامهای زیر در 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

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com

Comments (2)

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *