دیتابیس اوراکل

دیتابیس اوراکل
طبقه بندی موضوعی

Improve Bulk Insert with Array Processing

DECLARE
    CURSOR c_cur IS
      SELECT COL1, COL2, COL3, COL4
        FROM R2.TAB2 E
       WHERE E.ID = 5;

    TYPE c_array_t IS TABLE OF R1.TAB1%ROWTYPE; -- define collection for rows selected

    k_rows_max   CONSTANT INTEGER := 500; -- defines the maximum rows per fetch
    l_collect    c_array_t; -- define variable of rows collection
    
BEGIN
    OPEN c_cur;

    LOOP
        FETCH c_cur                  -- fetch up to LIMIT rows from cursor
        BULK COLLECT INTO l_collect LIMIT k_rows_max;

        FORALL i IN 1 .. l_collect.COUNT -- run insert for ALL rows in the collection
            INSERT INTO R1.TAB1
                 VALUES (l_collect (i).COL1,
                         l_collect (i).COL2,
                         l_collect (i).COl3,
                         l_collect (i).COL4);

        EXIT WHEN c_cur%NOTFOUND;                  -- no more rows so exit
        
    END LOOP;

    CLOSE c_cur;

    COMMIT;                                                  -- JUST 1 COMMIT;
    
END;

Improve Bulk Update with Array Processing

DECLARE
    CURSOR c_dim_cur IS
        SELECT /*+ parallel(10) */  
               k.col1, t.ID, t.col4
          FROM r1.tab1 t, r1.tab2 k
         WHERE t.no = k.no
           AND t.insert_sysdate < TRUNC (SYSDATE)
           AND t.col2 = '123456789'
           AND t.no IS NOT NULL;

    TYPE c_dim_array_t_1 IS RECORD
    (
        col1    r1.tab1.col2%TYPE,
        id      r1.tab1.ID%TYPE,
        col4    r1.tab1.col4%TYPE
    );

    TYPE c_dim_array_t IS TABLE OF c_dim_array_t_1; -- define collection for rows selected

    k_dim_rows_max   CONSTANT INTEGER := 5000; -- defines the maximum rows per fetch
    l_dim_collect             c_dim_array_t; -- define variable of rows collection
    
BEGIN
    OPEN c_dim_cur;
    LOOP
        FETCH c_dim_cur                  -- fetch up to LIMIT rows from cursor
        BULK COLLECT INTO l_dim_collect LIMIT k_dim_rows_max;

        FORALL i IN 1 .. l_dim_collect.COUNT -- run update for ALL rows in the collection
            UPDATE /*+ index(tab1 PK_tab1IX) parallel(10) */
                   r1.tab1 f
               SET f.col2 = l_dim_collect (i).col1
             WHERE f.id = l_dim_collect (i).id
               AND f.col4 = l_dim_collect (i).col4;

        EXIT WHEN c_dim_cur%NOTFOUND;                  -- no more rows so exit

        COMMIT;
        
    END LOOP;

    CLOSE c_dim_cur;

    COMMIT;
END;

Cancel SQL

From Oracle 12.2:

 

SQL> select S.SID,S.SERIAL#,s.status

from v$session s

where s.osuser='shakeri'

and s.sql_id='a4ayr73ytbp8p';

 

       SID    SERIAL#
----------     ----------
     10923      64869

 

 

SQL> alter system cancel sql '10923,64869';

 

Get DDL for indexes

SET SERVEROUTPUT ON SIZE 1000000 


DECLARE
   V_DDL CLOB;
BEGIN
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', false);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',false);
   DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
   FOR I IN (SELECT OWNER,INDEX_NAME FROM DBA_INDEXES WHERE VISIBILITY='INVISIBLE') LOOP
     V_DDL := DBMS_METADATA.GET_DDL('INDEX', I.INDEX_NAME, I.OWNER);
     DBMS_OUTPUT.PUT_LINE(V_DDL);
   END LOOP;
END;
 

Invisible Unused Indexes

WITH
    x
    AS
        (SELECT do.owner,
                t.name table_name,
                io.name index_name,
                DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') monitoring,
                DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') used,
                ou.start_monitoring,
                ou.end_monitoring
           FROM sys.obj$          io,
                sys.obj$          t,
                sys.ind$          i,
                sys.object_usage  ou,
                dba_objects       do
          WHERE i.obj# = ou.obj#
            AND io.obj# = ou.obj#
            AND t.obj# = i.bo#
            AND i.obj# = do.object_id)
SELECT 'alter index ' || x.owner || '.' || x.index_name || ' invisible;'
  FROM x, dba_ind_columns um                           
 WHERE x.used = 'NO'
   AND x.owner = um.index_owner
   AND x.owner = um.table_owner
   AND x.index_name = um.index_name
   AND x.table_name = um.table_name
   AND (um.index_owner, um.table_name, um.column_name) NOT IN
        (SELECT f2.owner, f2.table_name, f2.column_name
           FROM dba_cons_columns f2);

 

 

Document:
https://blog.zeddba.com/2018/07/17/index-monitoring-in-oracle-database/

 

پاک کردن یک یا چند ستون از جدول

اگر بخواهیم یک یا چند ستون را از یک جدول حذف کنیم، میتوانیم با دستور alter table drop column این کار را انجام دهیم که به صورت فیزیکالی جدول را حذف میکند ولی اگر سایز جدول زیاد باشد، این کار خیلی طول میکشد و ممکن است روی دیتابیس لود هم بگذارد.

یک راه حل دیگر این است که با دستور زیر ستون را از جدول حذف کنیم:

alter table R.TAB1 set unused column DATE1;

 

با این کار ستون به صورت logical از جدول حذف میشود و برای کاربرها قابل مشاهده نخواهد بود. بعدا اگر فرصت مناسبی پیش آمد، میتوان با دستور زیر ستون را به صورت فیزیکالی هم از جدول حذف کرد:

alter table R.TAB1 drop unused columns checkpoint 500;

 

برای جداول بزرگ برای اینکه حجم undo segmentها را کم کنیم، با استفاده از عبارت CHECKPOINT میتوانیم مشخص کنیم، بعد از این تعداد سطر، یک checkpoint انجام شود.

با استفاده از ویوی زیر میتوانیم به ازای هر جدول تعداد ستونهایی که unused شده اند را مشاهده کنیم:

select * from DBA_UNUSED_COL_TABS;

COUNT

TABLE_NAME

OWNER

2

TAB1

R

1

TAB_LOG

H

 

 

 

 

 

 

 

 

برای آزاد کردن فضا بعد از پاک کردن ستون میتوانیم از دستور زیر استفاده کنیم:

alter table R.TAB1 move online;

 

 

RMAN-03009, ORA-19566: exceeded limit of 0 corrupt blocks for file

Error:

RMAN-03009: failure of backup command on DISK02 channel at 06/15/2022 01:44:04
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/DB1/DATAFILE/tab_index.260.1104075295

 

Solution (Resizing the datafile to Smaller size):

SQL> alter database datafile '+DATA/DB1/DATAFILE/tab_index.260.1104075295' resize 946368M;

 

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured

موقع بکاپ گرفتن از دیتابیس گارد خطای زیر را در RMAN گرفتیم:

ORA-20079: full resync from primary database is not done

 

doing automatic resync from primary

resyncing from database with DB_UNIQUE_NAME PRIMARY

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03015: error occurred in stored script bk_primary

RMAN-03002: failure of sql command at 06/28/2022 20:00:05

RMAN-03014: implicit resync of recovery catalog failed

RMAN-03009: failure of partial resync command on default channel at 06/28/2022 20:00:05

RMAN-06613: Connect identifier for DB_UNIQUE_NAME STANDBY not configured

 

برای حل این خطا در دیتابیس primary باید دستور زیر اجرا شود:

[oracle@PRIMARY ~]$ rman target / catalog user@CATALOG

RMAN> configure db_unique_name 'STANDBY' connect identifier 'STANDBY';

 

starting full resync of recovery catalog

full resync complete

new RMAN configuration parameters:

CONFIGURE DB_UNIQUE_NAME 'STANDBY' CONNECT IDENTIFIER  'STANDBY';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

 

و همچنین در دیتابیس استندبای دستور زیر اجرا شد:

[oracle@STANDBY ~]$ rman target / catalog user@CATALOG

RMAN> resync catalog;

 

starting partial resync of recovery catalog

partial resync complete

 

 

reliable message wait event

در دیتابیسی با ورژن 11.2.0.4 لود از نوع other داشتیم، که با کلیک روی آن مشخص شد wait event مربوط به آن از نوع reliable message است:

در AWR Report هم به صورت زیر مشخص است:

 

در داکیومنت Document 1951729.1 گفته شده است اگر این event را مشاهده کردید، ابتدا کوئری زیر را اجرا کنید:

SELECT CHANNEL, SUM (wait_count) sum_wait_count

  FROM GV$CHANNEL_WAITS

 GROUP BY CHANNEL

 ORDER BY SUM (wait_count) desc;

 

 

همانطور که در نتیجه کوئری بالا مشخص است، بیشترین مقدار کوئری بالا مربوط به Result Cache Channel است، که طبق داکیومنت، این یک باگ است که 3 راه حل دارد:

1. آپگرید به ورژن بالاتر

2. Apply کردن patch 18416368 (در صورتی که برای platform و version شما در دسترس باشد)

3. disable کردن Result Cache به صورت زیر:

SQL> alter system set result_cache_max_size=0;

 

در دیتابیس ما result cache فعال بود ولی استفاده ای از آن نمیشد، اگر میخواستیم با استفاده از روش سوم result cache را disable کنیم، برای اعمال آن نیاز بود که instance ریستارت شود و چون نمیخواستیم این اتفاق بیفتد به صورت زیر result cache را disable کردیم (در RAC این دستور باید در همه نودها اجرا شود):

BEGIN

   DBMS_RESULT_CACHE.BYPASS(TRUE);

   DBMS_RESULT_CACHE.FLUSH;

END;  

 

با استفاده از فانکشن زیر میتوانیم وضعیت result cache را در دیتابیس مشاهده کنیم:

select DBMS_RESULT_CACHE.STATUS from dual;

 

STATUS

BYPASS

 

 

 

 

 

 

 

 

 

PDB Replication in Data Guard

برای کنترل کردن اینکه یک pdb در دیتابیس استندبای سینک شود یا نه، 2 روش وجود دارد:

1- استفاده از عبارت STANDBYS در دستور CREATE PLUGGABLE DATABASE که از ورژن 12.1.0.2 اضافه شده است.

CREATE PLUGGABLE DATABASE PDBTEST

ADMIN USER pdbadmin IDENTIFIED BY Password1

STANDBYS=NONE ;

 

مقادیری که میتوان برای STANDBYS ست کرد به صورت زیر است:

  • STANDBYS=NONE: این pdb با هیچ کدام از استندبای ها سینک نشود.
  • STANDBYS=ALL: این pdb با همه استندبای های سینک شود.
  • STANDBYS=ALL EXCEPT ('cdb1_stby_1','cdb1_stby_2'): این pdb با همه استندبای ها سینک شود بجز استندبای هایی که DB_UNIQUE_NAME آنها cdb1_stby_1 و cdb1_stby_2 است.
  • STANDBYS=('cdb1_stby_1'): این pdb فقط با استندبای cdb1_stby_1 سینک شود.

 

2- استفاده از پارامتر ENABLED_PDBS_ON_STANDBY که از ورژن 12.2 اضافه شده است.

اگر میخواهید یک pdb توسط استندبای سینک نشود، باید قبل از ایجاد pdb ، این پارامتر را به صورت زیر در دیتابیس اصلی یا استندبای ست کنید، ولی این را مد نظر داشته باشید که این پارامتر فقط توسط استندبای استفاده میشود:

ALTER SYSTEM SET enabled_pdbs_on_standby="*", "-PDBTEST";

 

مقادیری که میتوان به پارامتر enabled_pdbs_on_standby پاس داد به صورت زیر است:

  • "*": همه pdbها با استندبای سینک شوند.
  • ""PDB1", "PDB2: فقط PDB1 و PDB2 با استندبای سینک شوند.
  • ""PDB*: فقط pdbهایی که با کلمه "PDB" شروع میشوند با استندبای سینک شوند.
  • ""*", "-PDB*: همه pdbها بجز pdbهایی که با کلمه "PDB" شروع میشوند با استندبای سینک شوند.
  • ""*", "-PDB1: همه pdbها بجز PDB1 با استندبای سینک شوند.

 

بعد از ست کردن این پارامتر به صورت بالا، pdb زیر را ایجاد میکنیم:

CREATE PLUGGABLE DATABASE PDBTEST

ADMIN USER pdbadmin IDENTIFIED BY Password1;

 

در دیتابیس primary:

SQL> SELECT name, open_mode, recovery_status

FROM v$pdbs

WHERE name='PDBTEST';

 

NAME           OPEN_MODE   RECOVERY_STATUS

---------------- ----------------- --------------------------

PDBTEST       READ WRITE   ENABLED

 

در دیتابیس standby:

SQL> SELECT name, open_mode, recovery_status

FROM v$pdbs

WHERE name='PDBTEST';

 

NAME           OPEN_MODE   RECOVERY_STATUS

---------------- ----------------- --------------------------

PDBTEST       READ WRITE   DISABLED

 

 

Document:

https://oracle-base.com/articles/12c/multitenant-controlling-pdb-replication-in-data-guard-environments-12c