今天完成了一个负载较高的中央数据库的分库操作, 并实现了oracle的滚动升级(10.2.0.1->10.2.0.4), 业务中断仅15分钟. 平台: RHEL AS 4 + Oracle 10.2.0.1
分库目的:
1) 迁出BUSINESS/BUSINESS_APP两业务系统用户数据, 减轻中央库的业务压力和负载, 调整数据库架构, 以应对后续新业务系统上线造成对中央库的冲击;
2) 新库升级Oracle10.2.0.1到10.2.0.4, 数据库本身修复了很多bug, 增强了数据库的稳定性.
3) 调整定时任务, 把原先的定时任务由crontab/job方式改为oracle scheduler.
大概操作步骤如下:
--1) 提前安装Oracle10.2.0.4, 并部署Streams复制(schema复制)
BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS (
schema_names => 'BUSINESS,BUSINESS_APP',
source_directory_object => 'DIR_SOURCE',
destination_directory_object => 'DIR_DEST',
source_database => 'SOUR.NET',
destination_database => 'DEST.LK',
perform_actions => TRUE,
script_name => NULL,
script_directory_object => NULL,
capture_name => 'cap_erating',
capture_queue_table => 'tab_cap_que_erating',
capture_queue_name => 'cap_que_erating',
capture_queue_user => NULL,
propagation_name => 'prop_erating',
apply_name => 'app_erating',
apply_queue_table => 'tab_app_que_erating',
apply_queue_name => 'app_que_erating',
apply_queue_user => NULL,
dump_file_name => 'SOUR.dmp',
log_file => 'SOUR.log',
bi_directional => FALSE,
include_ddl => TRUE,
instantiation => DBMS_STREAMS_ADM.instantiation_schema
);
END;
/
--2) 检查目标库Streams复制是否正常
SELECT 'capture' process_type,
capture_name process_name,
status,
error_message
FROM DBA_CAPTURE
UNION ALL
SELECT 'propagation' process_type,
propagation_name process_name,
status,
error_message
FROM dba_propagation
UNION ALL
SELECT 'apply' process_type,
apply_name process_name,
status,
error_message
FROM dba_apply;
SELECT apply_name,
local_transaction_id,
source_commit_scn,
error_creation_time,
error_message
FROM dba_apply_error
ORDER BY source_commit_scn DESC;
--3) 停止全部业务系统, 要求只能中断15分钟
--4) 取消源库定时任务(包括crontab/job)
--5) 核查源库是否还有连接
SELECT username, status, COUNT ( * )
FROM v$session
WHERE username IN
('BUSINESS', 'BUSINESS_APP')
GROUP BY username, status;
--6) 核查目标库是否还有Streams复制事务
SELECT streams_name,
streams_type,
cumulative_message_count,
first_message_time,
XIDUSN,
XIDSLT,
XIDSQN,
last_message_time,
total_message_count
FROM v$streams_transaction
ORDER BY 3 DESC;
--7) 从源库获得重建序列语句, Streams复制这点特别注意, 因为Streams本身不会去同步序列值
set pages 0
SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';'
stmt
FROM dba_sequences
WHERE sequence_owner IN
('BUSINESS', 'BUSINESS_APP')
UNION ALL
SELECT 'CREATE SEQUENCE '
|| sequence_owner
|| '.'
|| sequence_name
|| ' start with '
|| last_number
|| ' MAXVALUE '
|| max_value
|| ' MINVALUE '
|| min_value
|| DECODE (cycle_flag, 'N', ' NOCYCLE ', ' CYCLE ')
|| DECODE (cache_size, 0, ' NOCACHE ', ' CACHE '||cache_size)
|| DECODE (ORDER_FLAG, 'N', ' NOORDER ', ' ORDER ')
|| ';' stmt
FROM dba_sequences
WHERE sequence_owner IN
('BUSINESS', 'BUSINESS_APP');
--8) 目标库重建序列
--9) 源库和目标库刷新同义词, 把同义词指向新的dblink, 同义词脚本提前准备好
--源库
@E:\用户迁移\sour_synonyms.sql
--目标库
@E:\用户迁移\dest_synonyms.sql
--10) 源库和目标库重新编译失效对象
@?/rdbms/admin/utlrp.sql
exec uts.get_invalid;
--11) 各业务系统更改数据库连接指向, 并启动各业务系统
--12) 测试业务系统启动情况
SELECT username, status, COUNT ( * )
FROM v$session
WHERE username IN
('BUSINESS', 'BUSINESS_APP')
GROUP BY username, status;
--12) 增加定时任务, 为方便管理统一改为scheduler
--13) 删除流配置
exec dbms_streams_adm.remove_streams_configuration;
--14) 其它收尾工作, 如各开发人员查询用户的授权等等.
--End--
