جلوگیری از Blocking Session در زمان اجرای دستور Update(قابلیت Lock-free reservation)

نویسنده مطلب: Vahid Yousefzadeh 2 Comments

شرایطی را در نظر بگیرید که قرار است فیلدی به عنوان شمارنده، دائما توسط تعداد زیادی session بروزرسانی شود(با همزمانی بالا) به این شیوه که با هر بار انجام «اقدامی مشخص»، دستور updateای اجرا شده و یک عدد به این شمارنده اضافه کند.

در این حالت با توجه به زیاد بودن تعداد sessionهای همزمان، احتمال رخ دادن Blocking session هم بسیار افزایش می یابد چرا که در دیتابیس اوراکل اگر دو کاربر قصد ویرایش یک رکورد را داشته باشند و با فاصله زمانی کمی دستور Update را اجرا کنند، کاربری که دیرتر دستور update را صادر کرده Block خواهد شد و تا زمانی که کاربر اول(کاربری که زودتر رکورد را در اختیار گرفته) به تراکنش خاتمه ندهد، کاربر دوم در حالت Block باقی خواهد ماند.

--session 1:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
      2190
SQL> update tbl_counter set counttt=counttt+1;
1 row updated

--session 2:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
       944

SQL> update tbl_counter set counttt=counttt+1;
Executing…

بلاک شدن session دوم را می توانیم از طریق دستور زیر ببینیم:

SQL>  select SID,ID1,ID2,LMODE,block,request from v$lock where type='TX';
       SID        ID1        ID2      LMODE      BLOCK    REQUEST
---------- ---------- ---------- ---------- ---------- ----------
       944     458766       2511          0          0          6
      2190     458766       2511          6          1          0

بنابرین زمانی که کاربران زیادی قصد کار بر روی یک رکورد را دارند، مکانیزم locking امکان ویرایش رکورد را در هر لحظه به یک نفر از آنها خواهد داد و بقیه sessionها Block می شوند. پیدا کردن ترفندی برای جلوگیری از Block شدن sessionها می تواند در بعضی از این شرایط نظیر «شرایط ذکر شده در ابتدای متن» بسیار راهگشا باشد.

(بیشتر…)

اوراکل 23ai- تنظیم اولویت برای PDB

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

سوال: اگر CDBای بیش از یک PDB داشته باشد، چگونه می توان در مورد ترتیب open شدن این PDBها اعمال نظر کرد؟

تا قبل از اوراکل 23c راهکاری در این زمینه وجود نداشت و با اجرای دستور startup، تضمینی در مورد ترتیب باز شدن این PDBها وجود نداشت(معمولا بر اساس شماره container این PDBها باز می شدند). اما در نسخه 23c قابلیت جدیدی ارائه شد و بر اساس آن می توان برای هر PDB یک PRIORITY تنظیم کرد تا از طریق آن، ترتیب انجام عملیاتهایی نظیر open کردن PDBء، upgrade و یا restoration را کنترل کرد.

ساختار کلی دستور را در قسمت زیر می بینید:

ALTER PLUGGABLE DATABASE <PDB name> PRIORITY <value>

در این دستور، نام PDB الزامی است و PRIORITY می تواند مقداری بین 1 تا 4096 بگیرد که هر چه این عدد کمتر باشد، PDB اولویت بیشتری دارد به طور مثال، PDB با اولویت 1 نسبت به PDB با اولویت 2 زودتر open خواهد شد.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEHRANPDB                      READ WRITE NO
         4 BABOLPDB                       READ WRITE NO
         5 VARAMINPDB                     READ WRITE NO
         6 ABADANPDB                      READ WRITE NO
SQL> ALTER PLUGGABLE DATABASE babolpdb PRIORITY 1;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE ABADANPDB PRIORITY 2;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE VARAMINPDB PRIORITY 3;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE TEHRANPDB PRIORITY 4;
Pluggable database altered.

با restart کردن دیتابیس خواهیم دید که دیتابیسها با ترتیب اعلام شده باز شده اند:

SQL> startup force;
SQL> select OPEN_TIME,NAME,PRIORITY from v$pdbs order by 1;
OPEN_TIME                                NAME         PRIORITY
---------------------------------------- ---------- ----------
10-MAY-23 05.52.43.190 PM +04:30         PDB$SEED            1
10-MAY-23 05.52.43.812 PM +04:30         BABOLPDB            1
10-MAY-23 05.52.44.889 PM +04:30         ABADANPDB           2
10-MAY-23 05.52.45.929 PM +04:30         VARAMINPDB          3
10-MAY-23 05.52.47.102 PM +04:30         TEHRANPDB           4

ترتیب باز شدن PDB در ALERT LOG هم ثبت شده است:

PDB$SEED(2):Opening pdb with Resource Manager plan: DEFAULT_PLAN
BABOLPDB(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
2023-05-10T17:52:44.739491+04:30
ABADANPDB(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
2023-05-10T17:52:45.784421+04:30
VARAMINPDB(5):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
2023-05-10T17:52:46.828984+04:30
TEHRANPDB(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18

*همراه با اوراکل 12cR2، اولویت PDBها در زمینه upgrade از طریق دستور زیر قابل تنظیم است:

SQL*Plus: Release 12.0.0.0.0 - Production on Wed May 10 16:56:59 2023
SQL> ALTER PLUGGABLE DATABASE babolpdb UPGRADE PRIORITY 5;
Pluggable database altered.

 

Schema Privilege در اوراکل 23ai

نویسنده مطلب: Vahid Yousefzadeh 1 Comment

تا قبل از اوراکل23aiء، privilegeها به سه نوع object، system و administrative قابل تقسیم بودند و برای آنکه کاربری صرفا به اشیاء یک اسکیما دسترسی داشته باشد به ناچار می بایست از object privilegeها استفاده کرد که این محدودیت را قبلا در مستندی شرح داده ایم.

رجوع شود به : اهدای مجوز در سطح اسکیما در اوراکل و پستگرس

در نسخه 23ai قابلیت جدیدی در این زمینه ایجاد شد و اوراکل نوع دیگری از privilege به نام schema privilege را ارائه کرد که بر اساس آن می توان دسترسی به تمامی اشیاء{موجود و آینده} یک اسکیما را صرفا با اجرای یک دستور به یک کاربر(و یا role) اهدا کرد و یا به کاربر این امکان را داد تا برای اسکیما شی جدیدی بسازد.

schema privilege با کمک system privilegeها کار می کند مثلا می توانیم دسترسی select any table که یک system Privilege محسوب می شود را بر روی یک اسکیما به یک کاربر اهدا کنیم. بدیهی است که schema privilege به نسبت system privilege از دادن دسترسی های اضافه جلوگیری خواهد شد.

(بیشتر…)

اهدای مجوز به فانکشن، پکیج و پروسیجر

نویسنده مطلب: Vahid Yousefzadeh 1 Comment

زمانی که یک program unit(فانکشن، پکیج، پروسیجر) به صورت invoker right ایجاد می شود، کاربر صدا زننده برنامه، با مجوز خودش این برنامه را اجرا خواهد کرد بنابرین اگر جدولی در برنامه موجود باشد که این کاربر به آن دسترسی نداشته باشد، کارش با خطا متوقف خواهد شد(بررسی Invoker’s Rights و Definer’s Rights). برای مثال، تابع زیر را در نظر بگیرید:

SQL> CREATE FUNCTION sys.Tabe(name_malek in varchar2,name_jadval in varchar2)
   RETURN VARCHAR2
   AUTHID CURRENT_USER
AS
V_bytes varchar2(1000);
BEGIN
  select bytes into V_bytes from dba_segments where owner=name_malek and segment_name=name_jadval;
   RETURN V_bytes;
END;
/
Function created.

این تابع که با یوزر sys ایجاد شده، قرار است نام segment را دریافت و حجم آن را بر اساس بایت برگرداند. عبارت AUTHID CURRENT_USER بیانگر invoker right است.

کاربری با نام vahid را با حداقل دسترسی ایجاد کرده و بررسی می کنیم که آیا این کاربر می تواند تابع فوق را اجرا کند؟

SQL> create user vahid  identified by a;
User created.
SQL> grant create session to vahid; 
Grant succeeded.

(بیشتر…)

ردیابی اهدای مجوزها در اوراکل از طریق ویوی ALL_TAB_PRIVS_MADE و USER_TAB_PRIVS_RECD

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

اگر کاربری مجوزی را به کاربر دیگر اهدا کند، این عملیات از طریق ویوی ALL_TAB_PRIVS_MADE قابل ردیابی است(البته با رعایت شرایطی!) و این ویو در کنار ویوی USER_TAB_PRIVS_RECD مشخص می کند که مجوز توسط کدام کاربر به کاربر دیگر اهدا شده است.

برای مثال در قسمت زیر، کاربر usef مجوز select on ali.tbl1 را به کاربر vahid اهدا می کند:

SQL> create user usef identified by a;
User created.
SQL> create user vahid identified by a;
User created.
SQL> grant create session to vahid,usef;
Grant succeeded.

SQL> grant select on ali.tbl1 to usef with grant option;
Grant succeeded.
SQL> conn usef/a
Connected.
SQL> show user
User is "USEF"
SQL> grant select on ali.tbl1 to vahid;
Grant succeeded.
SQL> conn ali/a
Connected.
SQL> select grantee, grantor,privilege, table_name from user_tab_privs_made where GRANTEE='VAHID';
GRANTEE     GRANTOR    PRIVILEGE  TABLE_NAME
----------- ---------- ---------- ----------
VAHID       USEF       SELECT     TBL1

همانطور که می بینید، با اجرای پرس و جوی فوق توسط کاربر ali، خواهیم دید که مجوز select on ali.tbl1 توسط کاربر usef به کاربر VAHID اختصاص داده شده است. البته با اجرای ویوی USER_TAB_PRIVS_RECD هم به این نتیجه خواهیم رسید:

SQL> conn vahid/a
Connected.
SQL> select owner,table_name,grantor,privilege,type from USER_TAB_PRIVS_RECD;
OWNER      TABLE_NAME GRANTOR    PRIVILEGE  TYPE
---------- ---------- ---------- ---------- ------------
ALI        TBL1       USEF       SELECT     TABLE

در این شرایط با حذف کاربر usef، دسترسی داده شده به کاربر vahid از بین خواهد رفت و به تبع آن، این دو ویو هم اطلاعاتی را در این زمینه بر نمی گردانند:

SQL> drop user usef;
User dropped.
SQL> conn ali/a
Connected.
SQL> select owner,table_name,grantor,privilege,type from USER_TAB_PRIVS_RECD;
no rows selected
SQL> select * from ALI.TBL1;
ORA-00942: table or view does not exist

با اندکی تغییر در سناریوی قبلی، نقش dba را به کاربر usef اختصاص می دهیم و بعد از ان کاربر usef، دسترسی select on ali.tbl1 را به کاربر vahid اهدا می کند، با این تغییر، دو ویوی فوق، کاربر ALI را به عنوان grantor معرفی خواهند کرد:

SQL> create user usef identified by a;
User created.
SQL> grant dba to usef;
Grant succeeded.
SQL>  conn usef/a
Connected.
SQL> grant select on ali.tbl1 to vahid;
Grant succeeded.
SQL> conn vahid/a
Connected.
SQL> select owner,table_name,grantor,privilege,type from USER_TAB_PRIVS_RECD;
OWNER      TABLE_NAME GRANTOR    PRIVILEGE  TYPE
---------- ---------- ---------- ---------- ------------------------
ALI        TBL1       ALI        SELECT     TABLE
SQL> conn ali/a
Connected.
SQL> select grantee, grantor, table_name from user_tab_privs_made where GRANTEE='VAHID';
GRANTEE     GRANTOR    TABLE_NAME
----------- ---------- ----------
VAHID       ALI        TBL1

 

نکته ای در مورد Role و Privilege در اوراکل

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

زمانی که مجوزی از نوع object privilege و یا system privilege به کاربری داده می شود(و یا از کاربر گرفته(revoke) می شود)، بلافاصله sessionهای ان user که به دیتابیس متصل هستند، از این مجوزها بهره مند خواهند شد:

–session 1:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 16 19:48:16 2021
Version 19.11.0.0.0
SQL> show user
USER is "ALI"
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION

–session 2:

SQL> show user
USER is "USEF"
SQL> grant select any table to ALI;
Grant succeeded.

–session 1:

SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
SELECT ANY TABLE

اما این مسئله برای role صادق نیست و در صورت grant یا revoke کردن یک roleء، sessionهای جاری متاثر نخواهند شد مگر آنکه از دستور SET ROLE استفاده کنند:

(بیشتر…)

اوراکل 21.7 – راه اندازی دیتاگارد در سطح PDB(قابلیت DGPDB)

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

DGPDB(یا همان Data Guard per Pluggable Database) قابلیت جدیدی است که در نسخه 21.7 اوراکل ارائه شده و قرار است با کمک این قابلیت، در سطح PDB، دیتاگارد راه اندازی کنیم.

 در معماری قدیمی(قبل از ارائه DGPDB)، یکی از CDBها(به انضمام همه PDBها) در نقش primary قرار داشت و CDB دیگر نقش standby را ایفا می کرد و راه اندازی دیتاگارد برای یک PDB منوط به راه اندازی دیتاگارد برای CDB$ROOT بود و دیتاگارد هم نمی توانست container اضافه ای را داشته باشد به عبارتی دیگر، Guard عینا مشابه primary و یا در صورت مستثنا کردن بعضی از PDBها(enabled_PDBs_on_standby)، دیتاگارد زیر مجموعه ای از primary بود.

اما DGPDB این محدودیتها را برداشت معماری این قابلیت که شباهتهایی هم با قابلیت PDB switchover دارد(قابلیت switchover  در نسخه 18c ارائه شده بود)، به این صورت است که:

1.هر دو CDB در نقش primary و در حالت read write قرار دارند و برخلاف معماری سنتی، CDB$ROOT بین این دو دیتابیس یکسان نیست و هر CDBء PDBهای مختص به خود را دارند.

2.نقش standby و primary در سطح PDB قابل تنظیم است مثلا برای PDB حاضر در CDB1 می توانیم در CDB2 گارد ایجاد کنیم و به همین شکل می توانیم در CDB1 برای PDBهای حاضر در CDB2 دیتاگارد راه اندازی کنیم.

3.تغییر چندانی در پروسه ارسال و دریافت redoها در طرفین ایجاد نشده و پروسه TTnn در معماری DGPDB نقش کلیدی را ایفا می کند.

(بیشتر…)

برگرداندن بکاپ PDB در یک CDB جدید!

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

مطابق با داکیومنتهای اوراکل در زمینه Backup/Recovery، برای برگرداندن یک PDB ابتدا باید بکاپ CDB مربوط به آن PDB را برگرداند در غیر این صورت امکان برگرداندن PDB وجود ندارد! اما در این متن با روش جدیدی آشنا خواهیم شد که امکان برگرداندن بکاپ PDB را در یک CDB جدید فراهم می کند. البته این روش همیشه جواب نمی دهد و بهتر است که در سناریو بکاپ و ریکاوری این موضوع را در نظر داشته باشیم.

در سناریوی پیش رو قرار است از PDBای به نام RmanPDB به صورت مستقل بکاپ گرفته و با انتقال آن به سرور جدید، سعی داریم این PDB را در یک CDB جدید برگردانیم.

بکاپ از RmanPDB:

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         5 PDB1                           READ WRITE NO
         4 RMANPDB                        READ WRITE NO
RMAN> backup pluggable database RmanPDB format '/oracle21c/bkp_pdb/%U' current controlfile  format '/oracle21c/bkp_pdb/control0000.bkp';
Starting backup at 16-OCT-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=266 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00068 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_sysaux_jpo62lhz_.dbf
input datafile file number=00067 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_system_jpo62lhv_.dbf
input datafile file number=00069 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_undotbs1_jpo62lj1_.dbf
input datafile file number=00070 name=/oracle21c/base/oradata/DB21C/CE75C2880CCD2A3FE0530488200A29C8/datafile/o1_mf_tbs01_jpo66vjx_.dbf
channel ORA_DISK_1: starting piece 1 at 16-OCT-21
channel ORA_DISK_1: finished piece 1 at 16-OCT-21
piece handle=/oracle21c/bkp_pdb/4p0bo4mu_153_1_1 tag=TAG20211016T052342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 16-OCT-21
channel ORA_DISK_1: finished piece 1 at 16-OCT-21
piece handle=/oracle21c/bkp_pdb/control0000.bkp tag=TAG20211016T052342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-OCT-21

(بیشتر…)

تابع checksum در اوراکل 21c

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

در نسخه 12c، اوراکل با ارائه تابع STANDARD_HASH، امکان محاسبه hash value را برای یک فیلد و یا عبارت فراهم کرده است:

SQL> select id,salary,substr(STANDARD_HASH(id||salary),1,20) hash_id_sal from tbl1;
        ID     SALARY HASH_ID_SAL
---------- ---------- ----------------------
         1         10 5E796E48332AF4142B10
         2         12 E2154FEA5DA2DD0D1732
         3         17 F44A286F486D11990238
         4         18 93AC1946CB917ABC4735

با این روش می توانیم از تغییر مقدار سطرهای جدول باخبر شویم. البته در نسخه های قبل از 12c هم می توانستیم از توابع دیگری نظیر ora_hash بدین منظور استفاده کنیم:

SQL> select id,salary,ora_hash(id||salary)  hash_id_sal from tbl1;

        ID     SALARY HASH_ID_SAL
---------- ---------- -----------
         1         10  3316966336
         2         12  1402677848
         3         17  3795753203
         4         18   936769390

در نسخه 21c هم قابلیت جدیدی در این زمینه ارائه شد و اوراکل با معرفی تابع checksum، امکان شناسایی تغییر دیتا را در سطح ستون فراهم کرده است.

(بیشتر…)

اوراکل 21c – پشتیبانی از شرط نامساوی در Automatic Indexing

نویسنده مطلب: Vahid Yousefzadeh Leave a comment

قابلیت auto indexing در اوراکل نسخه 19c شرط عدم تساوی را پشتیبانی نمی کند که قبلا در این مورد مطلبی را ارائه کرده ایم. نسخه 21c این قابلیت را فراهم کرده است که در ادامه این مسئله را می بینید.

برای نمایش این محدودیت، جدولی را همراه با حجم قابل توجهی از اطلاعات ایجاد می کنیم:

SQL> create table tb(id number,name varchar2(100),date_time date,c1 varchar2(4000),c2 varchar2(4000));
Table created
SQL> insert into tb select rownum,'test'||’’||rownum,sysdate - rownum,rpad('test',400,'c1'),rpad('test',400,'c2') from dual connect by level <=666444;
666444 rows inserted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => 'TB');
PL/SQL procedure successfully completed

بعد از ایجاد جدول، پرس و جوی زیر را که شرط عدم تساوی در آن استفاده شده است را در دیتابیس اجرا می کنیم:

Select count(*) from tb where id between 1 and 10;

همچنین با اجرای بلاک plsql زیر، سعی در مجاب کردن اوراکل برای بررسی این پرس و جو داریم:

declare
  temp number;
begin
  for a in 1 .. 1000 loop
    select count(*) into temp from tb where id between 1 and 10;
  end loop;
end;

پس از گذشت interval پانزده دقیقه ای، گزارشی از آخرین اجرا را می بینیم:

select DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSTIMESTAMP-1,SYSTIMESTAMP,'HTML','ALL','ALL') from dual;

با اجرای دستور زیر خواهیم دید که بر روی ستون id ایندکسی ایجاد شده است:

select owner,index_name,table_name,auto from dba_indexes where AUTO='YES';