شیوه بروزرسانی از راه دور 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)