关于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 filetable  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_xxxPCTUSED    40PCTFREE    10INITRANS   1MAXTRANS   255STORAGE    (            INITIAL          10M            MINEXTENTS       1            MAXEXTENTS       2147483645            PCTINCREASE      0            BUFFER_POOL      DEFAULT           )LOGGING NOCACHENOPARALLEL;

以上脚本用于创建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)LOGGINGTABLESPACE TS_indexPCTFREE    10INITRANS   2MAXTRANS   255STORAGE    (            INITIAL          10M            MINEXTENTS       1            MAXEXTENTS       2147483645            PCTINCREASE      0            BUFFER_POOL      DEFAULT           )NOPARALLEL;以上脚本用于建立关于col_a 和col_b 的索引 IDX_TB_xxx，该索引建立在tablespace ts_index上。

 

5)创建MV LogOracle 记录所有对对应表的操作。这些记录将被记录在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_xxxminvalue 1maxvalue 99999999start with 1increment by 1cache 20cycle;

以上脚本用于建立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 asselect * 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)LOGGINGTABLESPACE TS_xxxMVPCTFREE    10INITRANS   2MAXTRANS   255STORAGE    (            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

promptprompt Creating Table TB_xxxprompt ===============================prompt...具体的关于建立TB_XXX 的SQL语句...spool off

以上脚本运行后，将产生一个 tb_xxx.log的ASCII文件，内容为Creating Table TB_xxx===============================...运行建表语句的结果，成功或者失败

 

周朝勇联系方式: YORCK_ZHOU@HOTMAIL.COM

俺的联系方式: LIGANG1000@HOTMAIL.COM