Thứ Ba, 1 tháng 12, 2020

Quản lý database link trong Oracle Database

--1. CHECK
select * from dba_db_links;

--Check dblink
SQL> select * from dual@db1   // db1 là tên DBLink

--2. CREATE
--Public
CREATE PUBLIC DATABASE LINK link1
 CONNECT TO user1
 IDENTIFIED BY pwd1
 USING '(DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.138)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.139)(PORT = 1521))
  
      (LOAD_BALANCE = yes)
      (FAILOVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = srv1)
    )
  )';
  
  --Private
  CREATE PUBLIC DATABASE LINK link1
 CONNECT TO user1
 IDENTIFIED BY pwd1
 USING '(DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.138)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.139)(PORT = 1521))
      (LOAD_BALANCE = yes)
      (FAILOVER = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gold)
    )
  )';

  --3.ALTER
  • ALTER DATABASE LINK private_link 
  CONNECT TO hr IDENTIFIED BY hr_new_password;
• ALTER PUBLIC DATABASE LINK public_link
  CONNECT TO scott IDENTIFIED BY scott_new_password;
  
  --4.Drop
  DROP [PUBLIC] DATABASE LINK dblink_name
  
  CREATE OR REPLACE PROCEDURE user1.cre_dbl
IS
    v_sql   LONG;
BEGIN
  EXECUTE IMMEDIATE 'drop DATABASE LINK link2';
    v_sql :=
        'CREATE DATABASE LINK link2
CONNECT TO user1
IDENTIFIED BY pwd1
USING '
        || ''''
        || '(DESCRIPTION=
    (FAILOVER=yes)
    (LOAD_BALANCE=yes)
    (ADDRESS_LIST=
      (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.1.145) (PORT=1521))
   (ADDRESS= (PROTOCOL=TCP) (HOST=192.168.1.146) (PORT=1521))
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=db2)
    )
  )'
        || '''';

    EXECUTE IMMEDIATE v_sql;
END;
/

--5.KILL LOCK DBLINK
 declare
 CURSOR c1
        IS
            SELECT   local_tran_id, state
              FROM   DBA_2PC_PENDING
              where (retry_time-fail_time)*24*60>1.5; --waiting for longer 4 min
    BEGIN
        /*FOR r1 IN c1
        LOOP

            dbms_output.put_line('1');
            EXECUTE IMMEDIATE 'rollback force '''
                     || r1.local_tran_id
                     || '''';
            commit;
        end loop;*/

        FOR r2 IN c1
        LOOP
            if r2.state in ('committed') then
                EXECUTE IMMEDIATE 'begin
                dbms_transaction.purge_lost_db_entry('''
                         || r2.local_tran_id
                         || ''');
                commit;
                end;';
                commit;
             elsif r2.state='prepared' then
                EXECUTE IMMEDIATE 'rollback force '''
                     || r2.local_tran_id
                     || '''';
                commit;
                EXECUTE IMMEDIATE 'begin
                dbms_transaction.purge_lost_db_entry('''
                         || r2.local_tran_id
                         || ''');
                commit;
                end;';
                commit;
             else
                EXECUTE IMMEDIATE 'begin
                dbms_transaction.purge_lost_db_entry('''
                         || r2.local_tran_id
                         || ''');
                commit;
                end;';
                commit;

             end if;
        end loop;
    END;


KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master