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;