oracle Database 10g 高可用性实现方案 源码

create tablespace ws_app_data datafile 'u01/product/oracle/oradata/orcl/ws_app_data01.dbf' size 100m;

create tablespace ws_app_idx datafile 'u01/product/oracle/oradata/orcl/ws_app_idx01.dbf' size 100m;

create user ws_app identified by ws_app default tablespace ws_app_data temporary tablespace temp;

grant connect,resource to ws_app;

connect ws_app/wsapp;

create table woodscrew(
scr_id number not null,
manufactr_id varchar2(20) not null,
scr_type varchar2(20),
thread_cnt number,
length number,
head_config varchar2(20),
constraint pa_woodscrew primary key (scr_id,manufactr_id)
using index tablespace ws_app_idx);

create index woodscrew_identity on
woodscrew(scr_type,thread_cnt,length,head_config) tablespace ws_app_idx;

create table woodscrew_inventory(
scr_id number not null,
manufactr_id varchar2(20) not null,
warehouse_id number not null,
region varchar2(20),
count number,
log_price number);

create table woodscrew_orders(
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_wdscr_orders primary key (ord_id,ord_date)
using index tablespace ws_app_idx);

-- now add rows to the tables
-- now add woodscrew tables
insert into woodscrew values(
1000,'Tommy Hardware','Finish',30,1.5,'Phillips');
insert into woodscrew values(
1000,'Balaji Parts,Inc.','Finish',30,1.5,'Phillips');
insert into woodscrew values(
1001,'Tommy Hardware','Finish',30,1,'Phillips');
insert into woodscrew values(
1001,'Balaji Parts,Inc.','Finish',30,1,'Phillips');
insert into woodscrew values(
1002,'Tommy Hardware','Finish',30,1.5,'Phillips');
insert into woodscrew values(
1002,'Balaji Parts,Inc.','Finish',30,1.5,'Phillips');
insert into woodscrew values(
1003,'Tommy Hardware','Finish',30,1,'Phillips');
insert into woodscrew values(
1003,'Balaji Parts,Inc.','Finish',30,1,'Phillips');
insert into woodscrew values(
1004,'Tommy Hardware','Finish',30,2,'Phillips');
insert into woodscrew values(
1004,'Balaji Parts,Inc.','Finish',30,2,'Phillips');
insert into woodscrew values(
1005,'Tommy Hardware','Finish',30,2,'Phillips');
insert into woodscrew values(
1005,'Balaji Parts,Inc.','Finish',30,2,'Phillips');
insert into woodscrew values(
1006,'Tommy Hardware','Finish',30,1,'Phillips');
insert into woodscrew values(
1006,'Balaji Parts,Inc.','Finish',30,1,'Phillips');

-- now add woodscrew_inventory tables
insert into woodscrew_inventory values(
1000,'Tommy Hardware',200,'NORTHEAST',3000000,.01);
insert into woodscrew_inventory values(
1000,'Tommy Hardware',350,'SOUTHWEST',1000000,.01);
insert into woodscrew_inventory values(
1000,'Balaji Parts,inc.',450,'NORTHEAST',1500000,.015);
insert into woodscrew_inventory values(
1005,'Balaji Parts,inc.',450,'NORTHEAST',1700000,.017);

-- now add woodscrew_orders table
insert into woodscrew_orders values(
20202,'2003-09-22 00:02:02',2001,1000,20000,64114,'NORTHEAST');
insert into woodscrew_orders values(
20203,'2003-09-22 00:02:04',2001,1001,10000,64114,'NORTHEAST');
insert into woodscrew_orders values(
20204,'2003-09-22 00:02:06',2002,1002,10000,64114,'NORTHEAST');
insert into woodscrew_orders values(
20205,'2003-09-22 00:02:08',2002,1003,20000,64114,'NORTHEAST');
insert into woodscrew_orders values(
20206,'2003-10-04 00:02:02',2002,1004,10000,80903,'SOUTHWEST');
insert into woodscrew_orders values(
20207,'2003-10-04 00:02:14',2001,1003,20000,80903,'SOUTHWEST');
insert into woodscrew_orders values(
20208,'2003-10-04 00:02:16',2002,1002,30000,64114,'SOUTHWEST');
insert into woodscrew_orders values(
20209,'2003-10-04 00:02:08',2003,1001,40000,90210,'NORTHEAST');
insert into woodscrew_orders values(
20210,'2003-11-04 00:02:16',2005,1000,10000,83401,'SOUTHWEST');
insert into woodscrew_orders values(
20211,'2003-11-04 00:02:16',2002,1005,10000,83401,'SOUTHWEST');
insert into woodscrew_orders values(
20212,'2003-11-04 00:02:08',2001,1004,10000,64114,'NORTHEAST');
insert into woodscrew_orders values(
20213,'2003-11-04 00:02:08',2003,1003,10000,64114,'NORTHEAST');
insert into woodscrew_orders values(
20214,'2003-12-04 00:02:16',2002,1001,20000,64114,'SOUTHWEST');
insert into woodscrew_orders values(
20215,'2003-12-04 00:02:08',2001,1000,10000,80903,'NORTHEAST');
insert into woodscrew_orders values(
20216,'2003-12-04 00:02:16',2005,1001,50000,80903,'SOUTHWEST');
insert into woodscrew_orders values(
20217,'2003-12-04 00:02:15',2003,1003,70000,90210,'SOUTHWEST');

commit;

第二章
2.3.2.2分区表
创建范围分区
create table woodscrew_orders(
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_woodscrew_orders primary key(ord_id,ord_date)
using index tablespace ws_app_idx)
partition by range (ord_date)
(partition values less than(TO_DATE('1-OCT-2003','DD-MON-YYYY'))
tablespace wdscrord_sep_2003,
partition values less than(TO_DATE('1-NOV-2003','DD-MON-YYYY'))
tablespace wdscrord_oct_2003,
partition values less than(TO_DATE('1-DEC-2003','DD-MON-YYYY'))
tablespace wdscrord_nov_2003,
partition values less than(TO-DATE('1-JAN-2004','DD-MON-YYYY'))
tablespace wdscrord_dec_2003)
enable row movement;

创建散列分区
create table woodscrew(
scr_id number not null,
manufactr_id varchar2(20) not null,
scr_type varchar2(20),
thread_cnt number,
length number,
head_config varchar2(20),
constraint pk_woodscrew primary key(scr_id,manufactr_id)
using index tablespace ws_app_idx)
partition by hash(scr_id)
partitions 4
store in(wdscr_part1,wdscr_part2);

创建列表分区
create table woodscrew_inventory(
scr_id number not null,
manufactr_id varchar2(20) not null,
warehouse_id number not null,
region varchar2(20),
count number,
lot_price number)
partition by list(manufactr_id)
(partition east_suppliers values9'Tommy Hardware','2Many Parts')
tablespace wdscr_inv_part1,
partition west_suppliers values('Balaji Parts')
tablespace wdscr_inv_part2,
partition other values(DEFAULT)
tablespace ws_app_data)
enable row movement;

创建了分区(范围-散列分区)
将woodscrew_orders表按ord_date进行分区,为获得更好的性能并将负载进一步分配到各表空间
以利于恢复.通过散列分区对该表进行子分区
create table woodscrew_orders(
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_woodscrew_orders primary key(ord_id,ord_date)
using index tablespace ws_app_idx)
partition by range(ord_date) subspartition by hash(ord_id)
subspartitions 2
(partition values less than(TO_DATE('1-OCT-2003','DD-MON-YYYY'))
store in (wdscrord_sep_2003_part1,wdscrord_sep_2003_part2),
partition values less than(TO_DATE('1-NOV-2003','DD-MON-YYYY'))
store in (wdscrord_oct_2003_part1,wdscrord_oct_2003_part2),
partition values less than(TO_DATE('1-DEC-2003','DD-MON-YYYY'))
store in (wdscrord_nov_2003_part1,wdscrord_nov_2003_part2),
partition values less than(TO-DATE('1-JAN-2004','DD-MON-YYYY'))
store in (wdscrord_dec_2003_part1,wdscrord_dec_2003_part2)
enable row movement;


范围-列表分区
oracle9i的版本2中引入这一先项,先按范围分区,再按列表进行子分区的能力,通过这种方法,可以非常明确地控制分区的形状,
从而使维护和挖掘操作的粒度等级达到最优.P30
create table woodscrew_orders(
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_woodscrew_orders primary key(ord_id,ord_date)
using index tablespace ws_app_idx0
partition by range(ord_date)
subpartition by list(region)
(partition wdscrord_sep_2003
values less than(TO_DATE('1-OCT-2003','DD-MON-YYYY'))
(subpartition wdscrord_sep_2003_west values('SOUTHWEST','NORTHWEST')
tablespace wdscrord_sep_2003_part1,
subpartition wdscrord_sep_2003_east values ('SOUTHEAST','NORTHEAST')
tablespace wdscrord_sep_2003_part2),
partition wdscrord_oct_2003
values less than(TO_DATE('1-NOV-2003','DD-MOV-YYYY'))
(
subpartition wdscrord_oct_2003_west values('SOUTHWEST','NORTHWEST')
tablespace wdscrord_oct_2003_part1,
subpartition wdscrord_oct_2003_east values('SOUTHWEST','NORTHWEST')
tablespace wdscrord_oct_2003_part2
),
partition wdscrord_nov_2003
values less than(TO_DATE('1-DEC-2003','DD-MON-YYYY'))
(
subpartition wdscrord_nov_2003_west values('SOUTHWEST','NORTHWEST')
tablespace wdscrord_nov_2003_part1,
subpartition wdscrord_nov_2003_east values('SOUTHEAST','NORTHWEST')
tablespace wdscrord_nov_2003_part2
),
partition wdscrord_dec_2003
values less than(TO_DATE('1-JAN-2004','DD-MON-YYYY'))
(
subpartition wdscrord_dec_2003_west values('SOUTHWEST','NORTHWEST')
tablespace wdscrord_dec_2003_part1,
subpartition wdscrord_dec_2003_east values('SOUTHWEST','NORTHEAST')
tablespace wdscrord_dec_2003_part2
))
enable row movement;


创建物化视图 p.35
create materialized view log on ws_app.woodscrew
tablespace ws_app_data;
create meterialized view log on ws_app.woodscrew_orders
tablespace ws_app_data;

create materialized view ws_app.cust_ws_order_mv
pctfree 0 tablespace ws_app_data
storage (initial 16k next 16k pctincrease 0)
parallel
build immediate
refresh on demand
enable query rewrite as
select w.scr_type,w.head_config,wo.cust_id,wo.ord_cnt,wo.scr_id
from ws_app.woodscrew_orders wo,ws_app.woodscrew w
where w.scr_id = wo.scr_id
and wo.cust_id = 2002;

查询重写
可以为一个会话或整个系统开启该功能
alter session set query_rewrite_enabled = true;
alter system set query_rewrite_enabled = true;
还可以在物化视图上,通过使用alter 或 create命令来为特定物化视力开启查询重写功能.

联机重组p 36

如果在已经生成的表和索引上实现分区,索引组织表或物化视图?可以通过联机重组技术:  DBMS_REDEFINITION
联机表管理费用可用于下列情况:
-将表更必为索引组织表(反之亦然)
-将表更必为分区表(反之亦然)
-添加或删除一列
-将表移至一个不同的表空间,或者更改存储参数
带有物化视力的表不能进行联机重组.
使用DBMS_REDEFINITION将表更改为分区的IOT(索引组织表)
1.获得表的行数,有助于确定重组第一阶段的完成时间.
select count(*) from woodscrew_orders;
2.确认该表是可以进行重组的
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sales','woodscrew_orders',
dbms_redefinition.cons_use_pk);
END;
/
3.建立临时表,在我们的情况中,需要为woodscrew_orders表建立一个分区的索引组织结构
create table woodscrew_orders_new(
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_woodscrew_orders primary key (ord_id,ord_date))
organization index
including ord_date pctthreshold 20
overflow tablespace wd_scr_overflow
partition by range (ord_date)
(
   partition values less than (TO_DATE('1_OCT-2003','DD-MON-YYYY'))
   tablespace wdscrord_sep_2003,
   partition values less than (TO_DATE('1-NOV-2003','DD-MON-YYYY'))
   tablespace wdscrord_oct_2003,
   partition values less than (TO_DATE('1-DEC-2003','DD-MON-YYYY'))
   tablespace wdscrord_nov_2003,
   partition values less than (TO_DATE('1-JAN-2004','DD-MON-YYYY'))
   tablespace wdscrord_dec_2003
);
4.开始重组过程,因未重映射任何列,只需指定模式,原始表和临时表
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('ws_app','woodscrew_orders',
'woodscrew_orders_new',dbms_redefinition.cons_use_pk);
END;
/
5.自动重建所有表的依赖关系(索引,触发器等)
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ws_app','woodscrew_orders',
'woodscrew_orders_new',TRUE,TRUE,TRUE,FALSE);
END;
/
6.在建立依赖对象时,根据累积的数据量,可能需要根据来自后台生成的物化视图
日志的数据执行一次刷新
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ws_app','woodscrew_orders',
'woodscrew_orders_new');
END;
/
7.对原始表加锁,结束对临时表进行的最后同步操作,然后交换两表的表名,从而完成重组过程.
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ws_app','woodscrew_orders',
'woodscrew_orders_new');
END;
/
8.完成最后一步后,就可以删除临时表了-也就是示例中的woodscrew_orders_new表
这其实是被重命名了的原始表.

2.4资料管理器和调度器 p39
建立一个简单资源规划,CPU资源的分配
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'woodscrew_plan',
CONSUMER_GROUP1 => 'order_placement',GROUP1_CPU => 70,
CONSUMER_GROUP2 => 'order_review',GROUP2_CPU => 30);
END;
/
创建一个调度
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'nightly_review_schedule',
start_date => '24-DEC-2003 01:00:00AM',
end_date => '01-JAN-2005 01:00:00AM',
repeat_interval => 'FREQ=DAILY;INTERVAL=1',
comments => 'Schedule for Nightly Reviews');
END;
/
repeat_interval使用的是一种新的日历语法

程序
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'alert_review_script',
program_action => '/oracle/ora10/admin/PROD/scripts/alter_rev.sh',
program_type => 'EXECUTABLE',
comments => 'executes and alert review');
END;
/
程序定义了作业运行的方式

作业
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'daily_alert_review',
program_name => 'alert_review_script',
schedule_name => 'nightly_review_schedule');
END;
/
作业描述了想要完成的任务

窗口
BEGIN
dbms_scheduler.create_window(
window_name => 'Year_end_reporting_window',
resource_plan => 'woodscrew_plan',
start_date => '01-JAN-2004 01:00:00AM',
repeat_interval => 'FREQ=YEARLY',
end_date => '31-JAN-2006 01:00:00AM',
duration => interval '30' day,
window_priority => 'HIGH',
comments = 'end of year sales reporting for CEO');
END;
/

LogMiner:事务析取
它挖掘联机重做日志中的数据,归档重做日志来获取数据库中发生变更的记录信息
.例
查看一个归档日志内已经删除的事务,注意:在执行事务删除之前需要修改一个系统参
数以获得完整的事务
1.打开数据库的追加日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
2.切换日志文件,然后执行事务删除
connect /as sysdba
alter system switch logfile;
connect ws_app/ws_app
delete from woodscrew;
commit;

connect /as sysdba
alter system switch logfile;
select name from v$archived_log;
3.将刚生成的新日志文件添加到logminer列表中
"如果看到我的这写法运行的时候有错,去掉["-"]",这里我是照书上的写出来,运行的时候未
必会用,只做参考
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(-
LOGFILENAME =>
'/u02/oradata/flash_recovery_area/ORCL/01_mf_1_161_032xckmg_.arc',-
OPTIONS => DBMS_LOGMNR.NEW);
4.为logminer指定将要使用的联机目录,如果源数据库处于打开且可用的状态,
那么它也可用
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
5.在V$LOGMNR_CONTENTS中查询有头删除事务的信息
select username,sql_redo,sql_undo
from v$logmnr_contents where username='WS_APP'
and operation ='DELETE';

2.6可传输表空间(Transportable Tablespace, TTS)p43
可将某数据库上一个表空间的
一级数据文件复制,并将该表空间插入另外一个数据库中.
传输用时=通过ftp+复制

确定传输集合.传输集合指想要移所有表空间的集合.
可以使用DBMS_TTS包来确保表空间集合符合传输条件
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('WDSCRORD_SEP_2003,
WDSCRORD_OCT_2003,WDSCRORD_NOV_2003,WDSCRORD_DEC_2003',TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

TTS的限禁 p44
可传输表空间8I就已经有,10G中有了革命性的升级.可以跨平台传输
查询任何一个10G的数据库来查看其支持的操作系统
COLUMN PLATFORM_NAME FORMAT A30
SELECT * FROM V$TRANSPORTABLE_PLATFORM;
注意endian_format列,平台的ENDIAN决定着在向新平台传输时需要多少步聚
除了endial格式外还有别外的一个新限制,就是在平台间只能传输10G的数据文件.
其他的限制与同平台间的TTS限制相同
HA工作室:将表空间从Solaris传输到Linux
说明:
本例将说明如果将一个woodscrew_orders表的分区表空间从Solaris上的oracle 10G
移植到Linux上运行的oracle 10G,我们注意到如果不传输整个表空间就无法传输表
的一个分区的,为此,需要与一个独立表临时交换该分区,这样分区就变成了它自己的表,
然后,使用TTS过程进行传输
1.
为分区交换生成一个临时表
create tablespace ws_sep_trans datafile
'/u01/product/oracle/oradata/orcl/ws_sep_trans01.dbf'
size 50m;
create table woodscrew_orders_sep(
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_woodscrew_orders_sep primary key (ord_id,ord_date)
using index tablespace ws_app_idx)
tablespace ws_sep_trans;
2.
与表交换分区
alter table woodscrew_orders
exchange partition wdscrew_sep_2003 with table woodscrew_orders_sep;
3.
确认WDSCRORD_SEP_2003表空间中的独立表符合传输条件,注意到,ORACLE实际
上并没有真正交换分区中的数据,它只是简单地交换了数据字典中的信息,因此,
分区现在处于我们的新表空间WS_SEP_TRANS中,但它并不是我们要传输的,
我们真正要传输的表空间是WDSCRORD_SEP_2003的原始表空间(它碰巧与我们原
始分区的名称一样),因为表空间现在包含了我们的独立表
connect /as sysdba
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('WDSCRORD_SEP_2003',TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
注意:当我们在TRANSPORT_SET_VIOLATIONS中进行选择时,由于我们的索引
所处的表空间没有在传输集合中,因此使用我们设置示例的方法会出现一个
约束冲突,我们可以在进行实际导出时通过选择不导出约束来消除它(
如第5步)
4.
设置表空间为只读
alter tablespace wdscrord_sep_2003 read only;
5.
导出元数据,注意,我们没有指定约束,必须在新数据库的表重建主键
exp file=/u01/product/oracle/oradata/orcl/ws_sep_dat.dmp
transport_tablespace = y constraints=n tablespaces=wdscrord_sep_2003
6.
使用RMAN将数据文件转换为Linux移植所需的little endian.
convert tablespace wdscrord_sep_2003
to platform 'Linux IA(32-bit)'
FORMAT = '/u01/product/oracle/oradata/orcl/wscrord_sep_2003_for_LNX.dbf';
7.
将数据文件移植到linux系统上,可以使用任何符合您对速度及易用需求的方法进
行文件传送,我们使用二进制FTP,传送完成之后重命名数据文件(从wscrord_sep_2003_for_LNX
改为wscrord_sep_2003.dbf).
8.
当然,要将表空间设置为读写模式,然后将分区交换回原来的位置,您需要让该表的索引重新生效
connect /as sysdba
alter tablespace wdscrord_sep_2003 read write;
connect ws_app/ws_app
alter table woodscrew_orders
exchange partition wdscrord_sep_2003 with table woodscrew_orders_sep;
9.
在目标linux上,导入数据文件的元数据
imp file=/u01/product/oracle/oradata/orcl/ws_sep_dat.dmp
transport_tablespace = y tablespaces = wdscrord_sep_2003
datafiles ='u01/product/oracle/oradata/orcl/wscrord_sep_2003.dbf'
tts_owners = (ws_app)
10.
设置新表空间为读写模式
connect /as sysdba
alter tablespace wedscrord_sep_2003 read write;


本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/liuya1985liuya/archive/2007/10/23/1838969.aspx