主题：
1. 配置表空间和各种用户对象。
2. 建立从一个实时数据库到一个备份数据库的数据镜像。
环境：
Sun Solaris Unix 9
Oracle 9i
条件：
1.存在两个空的数据库：live database，backup database。
2.用户对以上两个数据库均有sysdba 权限。
3.以上两个数据库所在服务器可以通过TCP/IP协议进行互相访问。
特别鸣谢：Black Snail, Yi Yihui
1. 配置表空间和各种用户对象。
Oracle 是一个可以让用户自由配置各种参数以及空间的数据库。
- 用户可以在pfile中配置各种Oracle的初始化参数。
包括rollback segment的个数，具体的rollback segment，
UTIL_FILE_DIR=* (stored procedure 中log 文件的输出设置)等等。
--应该是UTL_FILE_DIR(yaogao 修改)
在启动的Oracle Instance 中使用的这些配置过的参数的方法:
- 在启动Oracle 时使用以下命令：
startup pfile=/oracle/admin/dbName/pfile/init.ora.xxxxxxxx
- 或者，在用以上命令启动Oracle后，使用sysdba登录db，执行以下命令：
create spfile pfile=/oracle/admin/dbName/pfile/init.ora.xxxxxxxx
shutdown immediate
startup
- 用户可以自由的分配各种对象的数据空间。
包括db file，segment，block，tablespace，table等等。
Oracle的存储空间分为物理存储空间和逻辑存储空间，类似于Windows操作系统的磁盘管理。
- 数据对象的载体：db file
- 最基本的存储单位：block
- 数据对象的空间配置基本单位：extend
- 所有用户对象的载体：tablespace
数据库概念 存在形式 说明
==========================================================================
datafile physical 可以用多个data file作为tablespace 的存储空间
extend physical 必须使用data file上的连续空间，可能会由连续空间不够，产生碎片
block physical 一般默认为 8k Byte（为所在操作系统最小block的整数倍）
segment logical 必须使用data file上的连续空间
tablespace logical 建立在data file上，可以使用多个data file
table logical 建立在tablespace上。
MV log logical 建立在tablespace上。
MV logical 建立在tablespace上。
index logical 建立在tablespace上。
view logical 存储在Oracle 的User_Views(View_name, text)表中
sequence logical 存储在Oracle 的User_Sequences表中
strored procedure
logical 存储在Oracle 的User_Porcedures, User_Source表中
user logical 存储在Oracle 的User_Users表中
*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。
此处的default storage 用于建立在其上的User Objects(table, index, materialized view, mv log, etc.)。
如果这些User Objects 没有指定storage，将使用此处的 default storage。
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
原文地址：http://blog.csdn.net/yorck/archive/2004/07/23/49532.aspx
2762
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
