關於Oracle資料庫的使用者物件配置以及資料鏡像的總結–轉載於項目組周朝勇筆記

--轉載於項目組周朝勇筆記

主題：
1. 配置資料表空間和各種使用者物件。
2. 建立從一個即時資料庫到一個備份資料庫的資料鏡像。

環境：Sun Solaris Unix 9
      Oracle 9i

條件：1.存在兩個空的資料庫：live database，backup database。
      2.使用者對以上兩個資料庫均有sysdba 許可權。
      2.以上兩個資料庫所在伺服器可以通過TCP/IP協議進行互相訪問。
     

 

1. 配置資料表空間和各種使用者物件。

Oracle 是一個可以讓使用者自由配置各種參數以及空間的資料庫。
- 使用者可以在pfile中配置各種Oracle的初始化參數。
  包括rollback segment的個數，具體的rollback segment，sql log 檔案的輸出設定等等。
- 使用者可以自由的分配各種對象的資料空間。
  包括db file，segment，block，tablespace，table等等。

Oracle的儲存空間分為實體儲存體空間和邏輯儲存空間，類似於Windows作業系統的磁碟管理。

- 資料對象的載體：db file
- 最基本的儲存單位：block
- 資料對象的空間配置基本單位：segment
- 所有使用者物件的載體：tablespace

資料庫概念 存在形式 說明
==========================================================================
datafile physical 可以用多個data file作為tablespace 的儲存空間
segment  physical 必須使用data file上的連續空間
block  physical 一般預設為 8k Byte

tablespace logical  建立在data file上，可以使用多個data file
table  logical  建立在tablespace上。
MV log  logical  建立在tablespace上。
MV  logical  建立在tablespace上。
index  logical  建立在tablespace上。
view  logical  建立在system tablespace上
sequence logical  為建立使用者的對象

strored procedure
  logical  為建立使用者的對象

user    擁有預設的資料表空間，以及暫存資料表空間，
    定義在create user statement中

*MV 為Materialized View

1)建立 tablespace

在建立使用者物件的載體，tablespace的時候，Oracle將建立（如果不存在）並分配data file。
在本文中，我們使用 Segment Space Management Auto 功能。

i.e.:
用sysdba登入到db，運行以下sql:

DROP TABLESPACE ts_xxx
        INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE ts_xxx
   LOGGING
   DATAFILE '/oracle/oradata/dbName/ts_xxx01.dbf' SIZE 512M REUSE
   AUTOEXTEND ON
   NEXT 5120K
   MINIMUM EXTENT 512K
   SEGMENT SPACE MANAGEMENT AUTO
   DEFAULT STORAGE (
                   INITIAL 512K
                   NEXT 512K
                   PCTINCREASE 0
                   MINEXTENTS 8
                   MAXEXTENTS UNLIMITED
                   );

以上指令碼將在/oracle/oradata/dbName/目錄（在建立資料庫時 ，已經建立。）中建立ts_xxx01.dbf。
如果資料庫中已經存在ts_xxx，Oracle 將刪除該ts_xxx，包括所有建立在其上的內容和資料檔案ts_xxx01.dbf。
初始化的data file 大小為512M，可以在其中的tablespace被drop掉之後被重複利用。
Segment 將自動初始化為512k，並根據tablespace配置，做自動成長。下一個Segment 將為5120k+512k。

2)建立 user
在建立table之前，需要建立user。
在Oracle中，所有的使用者物件均屬於某一個scheme，scheme 是某一個使用者的所有使用者物件的集合。
i.e.:
用sysdba登入到db，運行以下sql:

DROP USER op CASCADE;
DROP ROLE opRole;

CREATE ROLE opRole;
GRANT DBA, CREATE SESSION to opRole;

CREATE USER op IDENTIFIED BY opPwd
    DEFAULT TABLESPACE ts_xxx
    TEMPORARY TABLESPACE ts_temp;
GRANT opRole to op;

GRANT UNLIMITED TABLESPACE TO op;

以上指令碼將建立一個使用者op（密碼為opPwd），以及角色opRole。
若使用者在該指令碼運行之前已經存在，其對應的theme中的所有使用者物件先被刪除，該使用者也被刪除。
然後建立新的使用者。該使用者的預設資料表空間為ts_xxx，暫存資料表空間為ts_temp（在建立資料表空間時，自訂建立）。
DBA以及Create Session的許可權將被賦予給opRole，DBA中的unlimited tablespace 無法賦予給
一個角色，所以必須在將該角色賦予給使用者op之後，獨立賦予unlimited tablespace 給使用者op。

3)建立table
用使用者op登入到db，運行以下sql:

ALTER TABLE tb_xxx DROP PRIMARY KEY CASCADE;
DROP TABLE tb_xxx CASCADE CONSTRAINTS;

CREATE TABLE tb_xxx
(
  xxx_NO  VARCHAR2(9 BYTE)    NOT NULL,
  xxx_ID  VARCHAR2(3 BYTE)    NOT NULL,
  xxx     NUMBER(13,2),
  xxx_DT  DATE                DEFAULT sysdate
)
TABLESPACE ts_xxx
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCACHE
NOPARALLEL;

以上指令碼用於建立table: tb_xxx。若該table已經存在，則先刪除該存在的表。若該表有主鍵，則先刪除主鍵。
以上table建立在名為tb_xxx的tablespace上。

ALTER TABLE tb_xxx ADD (
  CONSTRAINT idx_tb_xxx PRIMARY KEY (col_a, col_b)
    USING INDEX
    TABLESPACE ts_index
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          3M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));
以上指令碼用於建立table的主鍵：col_a, col_b，該主鍵建立在idx_tb_xxx上，而該index idx_tb_xxx建立在tablespace ts_index上。
index idx_tb_xxx在運行以上指令碼時自動產生。tablespace ts_index應該在產生tablespace時候建立。

ALTER TABLE tb_xxx ADD (
  CONSTRAINT IDX_tb_xxx_UNQ UNIQUE (col_a, col_b)
    USING INDEX
    TABLESPACE ts_index
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
               ));
類似於建立主鍵，以上指令碼用於建立table的唯一鍵：col_a, col_b，該主鍵建立在index idx_tb_xxx_UNQ上，
而該index idx_tb_xxx_UNQ建立在tablespace ts_index上。
index idx_tb_xxx_UNQ在運行以上指令碼時自動產生。tablespace ts_index應該在產生tablespace時候建立。

 

4)建立index
用使用者op登入到db，運行以下sql:

CREATE INDEX IDX_TB_xxx ON TB_xxx
(col_a, col_b)
LOGGING
TABLESPACE TS_index
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
以上指令碼用於建立關於col_a 和col_b 的索引 IDX_TB_xxx，該索引建立在tablespace ts_index上。

 

5)建立MV Log
Oracle 記錄所有對對應表的操作。這些記錄將被記錄在Log表中。資料鏡像就是尋找記錄在
Log表中的記錄，將資料操作鏡像到備份資料庫中。
i.e.:
用使用者op登入到db，運行以下sql:

DROP MATERIALIZED VIEW LOG ON tb_xxx;

CREATE MATERIALIZED VIEW LOG ON tb_xxx
   PCTUSED 40
   PCTFREE 10
   INITRANS 1
   MAXTRANS 255
   STORAGE (
            INITIAL          20M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            )
   TABLESPACE ts_xxx
   LOGGING
   NOCACHE
   WITH PRIMARY KEY;
以上指令碼將在tb_xxx上建立Log，若該Log已經存在，則先刪除。該Log建立在 tablespace ts_xxx上。

 

 

6)建立sequence
用使用者op登入到db，運行以下sql:

DROP SEQUENCE SQ_xxx;

create sequence SQ_xxx
minvalue 1
maxvalue 99999999
start with 1
increment by 1
cache 20
cycle;

以上指令碼用於建立sequence SQ_xxx。若該sequence存在，則先刪除。
該sequence的初始化值為1，最大值為 99999999，步長為1，每次Oracle在執行SQ_xxx.NextVal時，將
在資料庫伺服器記憶體中產生20個sequence的緩衝，在sequence達到最大值後，將回到最初的初始化值。

 

7)建立View
用使用者op登入到db，運行以下sql:

create or replace view vw_xxx as
select * from tbl_xxx;

運行以上指令碼將建立view vw_xxx。

8)建立預存程序：
用使用者op登入到db，運行以下sql:

create or replace package PK_xxx AS
...
/

create or replace package body PK_xxx AS
...
/

以上指令碼中的/表示結束並執行一段sql。運行以上指令碼將建立預存程序。
這裡的預存程序包括package和package body，在package 中建立抽象介面的定義，
在package body中建立了package body 中各個抽象介面的具體定義，也就是實現了package中抽象的介面。

package body 與package中的Function 以及 Procedure必須一一對應。
若在建立package 或者package body 之前，已經存在對應的package 或者package body，則先刪除，再建立。

9)取消使用者的某些許可權
用sysdba登入db，運行以下sql:

REVOKE DBA from opRole;
REVOKE UNLIMITED TABLESPACE from op;

以上指令碼將取消角色bkOP_ROLE的DBA許可權，以及使用者bkOP的unlimited tablespace許可權。

 

 

 

2. 建立從一個即時資料庫到一個備份資料庫的資料鏡像。
建立資料備份有多種途徑，使用Materialized View 進行資料鏡像是比較便利，高效，安全的一種。

使用Materialized View 進行資料鏡像的原理：
讀取即時資料庫的Log記錄，在自訂的時間上將即時資料備份到備份資料庫中。

1)與建立即時資料庫類似，先用使用者sysdba建立資料表空間，用於容納Materialized View。

2)與建立即時資料庫中的使用者類似，建立使用者：
用使用者sysdba登入到db，運行以下sql:

DROP USER bkOP CASCADE;
DROP ROLE bkOP_ROLE;

CREATE ROLE bkOP_ROLE;
GRANT DBA, CREATE SESSION to bkOP_ROLE;

CREATE USER bkOP IDENTIFIED BY bkOPpwd
    DEFAULT TABLESPACE ts_xxxMV
    TEMPORARY TABLESPACE ts_temp;
GRANT bkOP_ROLE to bkOP;

GRANT UNLIMITED TABLESPACE TO bkOP;
以上指令碼將建立一個使用者bkOP（密碼為bkOPpwd），以及角色bkOP_Role。
若使用者在該指令碼運行之前已經存在，其對應的theme中的所有使用者物件先被刪除，該使用者也被刪除。
然後建立新的使用者。該使用者的預設資料表空間為ts_xxxMV，暫存資料表空間為ts_temp（在建立資料表空間時，自訂建立）。
DBA以及Create Session的許可權將被賦予給bkOP_Role，DBA中的unlimited tablespace 無法賦予給
一個角色，所以必須在將該角色賦予給使用者bkOP之後，獨立賦予unlimited tablespace 給使用者bkOP。

3)建立資料庫連接：
用使用者sysdba登入到db，運行以下sql:

DROP PUBLIC DATABASE LINK FromBKtoLive;

CREATE PUBLIC DATABASE LINK FromBKtoLive
       CONNECT TO op IDENTIFIED BY opPwd USING 'liveDatabaseName';

以上指令碼用於建立一個從備份資料庫到即時資料庫的database link。

4)建立Materialized View
用使用者bkOP登入到db，運行以下sql:

DROP MATERIALIZED VIEW tb_xxx;

CREATE MATERIALIZED VIEW tb_xxx
        PCTUSED 40
        PCTFREE 10
        INITRANS 1
        MAXTRANS 255
        STORAGE (
                 INITIAL          256M
                 MINEXTENTS       1
                 MAXEXTENTS       2147483645
                 PCTINCREASE      0
                 BUFFER_POOL      DEFAULT
                )
        TABLESPACE TS_xxxMV
        LOGGING
        NOCACHE
        NOPARALLEL
        REFRESH FAST
        WITH PRIMARY KEY
        USING DEFAULT ROLLBACK SEGMENT
        AS
        SELECT * FROM tb_xxx@FromBKtoLive;
運行以上指令碼後，Oracle將即時資料庫中對應表的表結構，
以及資料直接copy到Materialized View中。建立的表結構包括資料定義，
以及主鍵定義（WITH PRIMARY KEY）。對應的index約束將建立在預設的tablespace ts_xxxMV上。

5)在Materialized View上建立Index：
用使用者bkOP登入到db，運行以下sql:

DROP INDEX IDX_TB_xxx;

CREATE INDEX IDX_TB_xxx ON TB_xxx
(col_a, col_b)
LOGGING
TABLESPACE TS_xxxMV
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
運行以上指令碼後，將在Materialized View 上產生表tb_xxx關於(col_a, col_b)的index，
該index IDX_TB_xxx 產生在 tablespace ts_xxxMV上。

 

6)建立預存程序
用使用者bkOP登入到db，運行以下sql:

create or replace package PK_Mirroring AS
   TYPE BK_MV IS REF CURSOR; --Materialized View表名集合
   PROCEDURE SP_Make(refreshType varchar2);
end PK_Mirroring;
/

create or replace package body PK_Mirroring is
   --主預存程序
   PROCEDURE SP_Make(refreshType varchar2) is
   CURSOR BK_MV IS --Materialized View表資訊
     select Table_Name from User_Tables order by Table_name; 
     V_Mirror BK_MV%ROWTYPE;
   begin
     open BK_MV;
     Loop
         FETCH BK_MV into V_Mirror;
         exit when BK_MV%notfound;
         DBMS_MVIEW.REFRESH(V_Mirror.Table_Name, refreshType);
     End Loop;
   end;
end PK_Mirroring;
/

以上指令碼用於建立預存程序的package 以及package body，該package有
一個procedure SP_Make(refreshType varchar2)，用於遍曆備份資料庫的使用者bkOP 使用者
的所有使用者物件，並一一加以Materialized View重新整理，重新整理形式(refreshType)為 'F'(Fast)
或'C'(Complete)等多種，一般選擇Fast，只是將更新過的即時資料鏡像到備份資料庫中。
Complete是將所有的資料重新從即時資料庫中copy到備份資料庫，將佔用大量的時間和空間。

使用者可以用bkOP身份登入到備份資料庫上，運行 exec PK_Mirroring.SP_Make('F')

或者在Unix的crontabs表中建立任務，在特定的時間點上運行該鏡像處理。

7)取消使用者的某些許可權
用sysdba登入db，運行以下sql:

REVOKE DBA from bkOP_ROLE;
REVOKE UNLIMITED TABLESPACE from bkOP;

以上指令碼將取消角色bkOP_ROLE的DBA許可權，以及使用者bkOP的unlimited tablespace許可權。

 

 

運行SQL指令碼的一個補充：
可以在sql文前後加入spool, prompt命令。
在當前的sql_path運行目錄中 產生log檔案。便於分析sql啟動並執行結果。

 

spool tb_xxx.log

prompt
prompt Creating Table TB_xxx
prompt ===============================
prompt
...具體的關於建立TB_XXX 的SQL語句...
spool off

以上指令碼運行後，將產生一個 tb_xxx.log的ASCII檔案，內容為
Creating Table TB_xxx
===============================
...運行建表語句的結果，成功或者失敗

 

周朝勇連絡方式: YORCK_ZHOU@HOTMAIL.COM

俺的連絡方式: LIGANG1000@HOTMAIL.COM