工作簿有很多工作表以产品名字命名如何创建带超链的目录(超链接工作簿中的一个工作表)
1552
2022-08-13
企业级灾备方案Oracle Stream搭建过程(灾备行业企业)
生产环境和灾备放在不同的机房,中间使用20M的专线相连,为了实现两个机房之间数据库的灾难备份,两个机房的操作系统不一样,没有办法使用dataguard,于是免费的Stream成为我们的首选,复制时只复制其中两个用户,数据库总大小为1.5T左右,每天生成归档量为20-40G左右,运行stream期间除遇到大的事务延迟外,没有发现有大的性能问题。
除灾备外还有一个最重要的目的,这种架构可以实现数据库大版本升级(跨平台)0风险,只需停机半小时就能完成升级切换, 切换时注意同步sequence
防止泄露信息,下面实例名和IP以及要同步的用户都替换成了无意义的名字
ora71
TopicDescriptionOS VersionLinuxIP192.168.0.71Oracle version10.2.0.2SIDora71Global nameora71
ora72
TopicDescriptionOS VersionAIX 6.1IP192.168.1.72Oracle version11.2.0.2SIDora72Global nameora72
Stream
如果源库已经存在,请先在源库和目标库上面清除原来stream配置,一般有两种方式,更推荐使用方法2
方法1
下面操作在stream backup库上面一定是在impdp之前,因为这个操作会把表的SCN号清掉
connect SYS/password as SYSDBA
如果.REMOVE_STREAMS_CONFIGURATION报错也可以先执行删除用户操作,再执行清除操作
drop user stream_admin cascade;
下面操作可以在impdp之前操作,也可以在之后操作,不涉及到清SCN号
也可以使用下面方式
删除captrue
PL/SQL procedure successfully completed
2.删除传播进程
SQL>
exec dbms_streams_adm.remove_rule(RULE_NAME=> 'EYMIT116',STREAMS_TYPE=>'CAPTURE',STREAMS_NAME=>'PROP_ORA71_TO_ORA72',DROP_UNUSED_RULE=>TRUE);
--删除消息队列
OWNER NAME QUEUE_TABLE QUEUE_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
STREAM_ADMIN ORA71_QUEUE ORA71_QUEUE_TABLE NORMAL_QUEUE
STREAM_ADMIN AQ$_ORA71_QUEUE_TABLE_E ORA71_QUEUE_TABLE EXCEPTION_QUEUE
exec dbms_streams_adm.REMOVE_QUEUE(QUEUE_NAME=>'ORA71_QUEUE', CASCADE=>TRUE, DROP_UNUSED_QUEUE_TABLE=>true);
删除Apply配置
apply之前需要把所有报错信息删除
exec dbms_apply_adm.DROP_APPLY(APPLY_NAME=>'APPLY_ORA71_TO_ORA72',DROP_UNUSED_RULE_SETS=>TRUE);
--删除消息队列
SQL> select owner,name,QUEUE_TABLE,QUEUE_TYPE from dba_queues where wner='STREAM_ADMIN';
OWNER NAME QUEUE_TABLE QUEUE_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
STREAM_ADMIN Q_ORA71_TO_ORA72 Q_ORA71_TO_ORA72_TABLE NORMAL_QUEUE
STREAM_ADMIN AQ$_Q_ORA71_TO_ORA72_TABLE_E Q_ORA71_TO_ORA72_TABLE EXCEPTION_QUEUE
SQL>
SQL>
PL/SQL procedure successfully completed
oracle 参数
COMPATIBLE默认值: 10.0.0是否必须修改: 不Oracle 10g R2必须设置为10.2.0或更高.GLOBAL_NAMES默认: false是否必须修改: 是两边都必须设置为trueJOB_QUEUE_PROCESSES默认: 10是否必须修改: 否最低为2LOG_ARCHIVE_CONFIG默认: 'SEND, RECEIVE, NODG_CONFIG'是否必须修改: 是如果是下游stream必须要设置LOG_ARCHIVE_CONFIG LOG_ARCHIVE_DEST_n 默认无是否必须修改: 是需要指定归档日志路径(开启归档)LOG_ARCHIVE_DEST_STATE_n默认: enable OPEN_LINKS 默认: 4是否必须修改: 否Stream环境确保最小为4PARALLEL_MAX_SERVERS默认:自动分配是否必须修改: 是设置一个合适的值PROCESSES默认:40是否必须修改: 是设置合适的值,我们设置为1000SGA_MAX_SIZE默认:SGA SGA_TARGET默认:0是否必须修改: 是设置为动态分配SHARED_POOL_SIZE默认:0是否必须修改: 是大于84M,如果SGA_TARGET和STREAMS_POOL_SIZE没有设置,stream将从shared pool中分配10%STREAMS_POOL_SIZE默认:0是否必须修改: 是如果设置为0 stream将不会跑,每个captrue进程最少10 MB ,每个队列最少10M,stream库上面每个apply进程最少1M TIMED_STATISTICS 默认: TYPICAL.是否必须修改: 是必须为TYPICAL或者 ALLUNDO_RETENTION默认: 900是否必须修改: 是Stream要求最少3600
Login as SYSDBA
Connect "/ as sysdba"
3.2.1 Global_Names
SQL>
System altered.
如果某个数据库的GLOBAL_NAMES参数设置成了TRUE,那么要求该数据库上DB Link名字与连接的对方的数据库的Golbal Name相同。
alter system set streams_pool_size=100M scope=both sid='*';
alter system set undo_retention=9000 scope=both sid='*';
alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile sid='*';
alter system set aq_tm_processes=2 scope=both sid='*';
alter system set parallel_max_servers=20 scope=both sid='*';
查看哪些对象不支持
select * from DBA_STREAMS_UNSUPPORTED where wner='EYMIT'
在传输过程中确保这个不支持的对象是只读的
stream的用户
3.3.1 Create tablespace for stream
create tablespace stream_tbs
datafile '+DATAVG' size 100m autoextend on next 500m maxsize 20g segment space management auto;
3.3.2 Create stream_admin user
create user stream_admin identified by stream_xxxxx default tablespace stream_tbs temporary tablespace temp;
3.3.3 Grant privilege to stream_admin
grant connect,resource,dba,aq_administrator_role to stream_admin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'stream_admin',
grant_privileges => true);
end;
/
3.3.4 Change the default tablespace of Logminer
#将logminer的数据字典从system表空间转移到新建的表空间,防止撑满system表空间
SQL> execute dbms_logmnr_d.set_tablespace('stream_tbs');
stream的用户
#以sysdba身份登录
connect / as sysdba
3.4.1 Create tablespace for stream
create tablespace stream_tbs
datafile '/oradata/ora72/stream_tbs01.dbf' size 100m autoextend on next 500m maxsize 20g segment space management auto;
3.4.2 Create stream_admin user
create user stream_admin identified by stream_xxxxx
default tablespace stream_tbs temporary tablespace temp;
3.4.3 Grant privilege to stream_admin
grant connect,resource,dba,aq_administrator_role to stream_admin ;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'stream_admin ',
grant_privileges => true);
end;
/
3.4.4 Change the default tablespace of Logminer
execute dbms_logmnr_d.set_tablespace('stream_tbs');
tnsnames.ora
3.5.1 Config tnsnames.ora of Master DB
主库(tnsnames.ora)中添加Backup Database配置。
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.72)(PORT = 1522))
)
(CONNECT_DATA =
(SID = ora72)
(SERVER = DEDICATED)
)
)
3.5.2 Config tnsnames.ora of Backup DB
Backup Database(tnsnames.ora)中添加主库配置。
ora71 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.71)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora71)
(SERVER = DEDICATED)
)
)
supplemental log
使所有操作都记录在redo log中,防止部分手动设置nolog的操作不能记录到redo log中,从而导致数据不同步
ALTER DATABASE FORCE LOGGING;
启用追加日志(Supplemental Log),可以基于Database级别或Table级别
sysdba登录master数据库
#检查是否启用了追加日志:
SELECT SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_ALL FROM V$DATABASE;
#启用Database Supplemental Log
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE, FOREIGN KEY,ALL) COLUMNS;
如果需要删除SUPPLEMENTAL
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE, FOREIGN KEY,ALL) COLUMNS;
Database Link
3.7.1 Create 主库 Link
#以stream_admin 身份,登录主库。
connect stream_admin /stream_xxxxx@ora71
create database link ora72 connect to stream_admin identified by stream_xxxxx using 'ora72';
3.7.2 Create 灾备库Link
#以stream_admin 身份,登录Backup Database。
connect stream_admin/stream_xxxxx
create database link ora71 connect to stream_admin identified by stream_xxxxx using 'ora71';
3.7.3 Test Database Link
测试两个DBLINK的可用性
3.8.1 create Tablespace and User on Backup DB
# Tablespace
CREATE SMALLFILE TABLESPACE "EYMIT"
DATAFILE '/oradata/ora72/EYMIT_01.dbf' SIZE 2G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
# Users
create user EYMIT
identified by EYMITpwd
default tablespace EYMIT_TBS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke object privileges
-- Grant/Revoke role privileges
grant connect to EYMIT;
grant resource to EYMIT;
-- Grant/Revoke system privileges
grant create any synonym to EYMIT;
grant create any table to EYMIT;
grant unlimited tablespace to EYMIT;
-- Create the user
create user EYMIT2
identified by EYMIT2pwd
default tablespace EYMIT2_TBS
temporary tablespace TEMP
profile DEFAULT;
-- Grant/Revoke role privileges
grant connect to EYMIT2;
grant resource to EYMIT2;
-- Grant/Revoke system privileges
grant create any synonym to EYMIT2;
grant create session to EYMIT2;
grant create synonym to EYMIT2;
grant debug connect session to EYMIT2;
grant select any dictionary to EYMIT2;
grant unlimited tablespace to EYMIT2;
3.8.2 Export shema
在导入前确保STREAM目标数据库中没有JOB在更改目标库中的数据
使用expdp的方式
在源用户中初始化SCN号
sqlplus stream_admin/stream_xxxxx
DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(schema_name => 'EYMIT');END;/
BEGINDBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(schema_name => 'EYMIT2');END;/
导出前一定要检查undo_retention的时间比导出时间长.
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_retention integer 56000
SQL>
把SCN号信息写到redo log中,不执行下面语句取不到符合条件的SCN号.
exec DBMS_CAPTURE_ADM.BUILD();
取得当前SCN号,确保这个arch logfile是有效且存在在磁盘中的.
从源库导出,注意需要加入flashback_scn,加入scn后导出的表不会都是这个SCN号,但是都会比这个SCN号大,一般相关联的表会是同一个SCN号
如:
expdp system/xxxxxx directory=expdp_dir dumpfile=expdp_ora71_20120824_%U.dmp schemas=EYMIT2,EYMIT exclude=TABLE:\"IN \(\'T_A
TTACHMENT\'\)\",STATISTICS flashback_scn=1110227790 PARALLEL=5
导入到目标库
impdp userid/password dumpfile= directory=
如:
impdp system/xxxx directory=expdp_dir dumpfile= expdp_ora71_20120824_%U.dmp logfile=impdp_ora71_20110916.log EXCLUDE=grant
PARALLEL=3 REMAP_TABLESPACE=EYMIT_TBS:EYMIT_tbs,CIRCBJ_ACC_TBS:EYMIT2_tbs
stream消息队列
3.8.1 在主库上面创建队列
#以stream_admin 身份,登录主库。
connect stream_admin /stream_admin@ora71
begin
dbms_streams_adm.set_up_queue(
queue_table => 'ora71_queue_table',
queue_name => 'ora71_queue');
end;
/
#同时,也创建了queue table。一个queue table可以有多个queue。
3.8.2 在灾备库上面创建队列
#以stream_admin 身份,登录Backup Database。
connect stream_admin /stream_admin@ora72
dbms_streams_adm.set_up_queue(
queue_table => 'q_table_ora71_to_ora72',
queue_name => 'q_ora71_to_ora72');
end;
/
Capture捕获进程
#以stream_admin 身份,登录主库。
connect stream_admin /stream_admin@ora71
execute DBMS_CAPTURE_ADM.CREATE_CAPTURE(queue_name => 'ora71_queue', capture_name => 'capture_ora71', use_database_link=>TRUE,start_scn=>1741155485, first_scn=>1741155485);
注意这里的start_scn and first_scn是从上面视图v$archived_log中取得.
注意如果同步两个用户,需要更改变shema_name后再执行一次
begin
schema_name => 'EYMIT',
streams_type => 'capture',
streams_name => 'capture_ora71',
queue_name => 'ora71_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/
begin
dbms_streams_adm.add_schema_rules(
schema_name => ,
streams_type => 'capture',
streams_name => 'capture_ora71',
queue_name => 'ora71_queue',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => null,
inclusion_rule => true);
end;
/
propagation传播进程
#以stream_admin 身份,登录主库。
注意如果同步两个用户,需要更改变shema_name后再执行一次
connect stream_admin/stream_admin
begin
schema_name => 'EYMIT',
streams_name => 'prop_ora71_to_ora72',
source_queue_name => 'stream_admin.ora71_queue',
destination_queue_name => 'stream_admin.q_ora71_to_ora72@ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ora71',
,
inclusion_rule => true);
end;
/
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name => 'EYMIT2',
streams_name => 'prop_ora71_to_ora72',
source_queue_name => 'stream_admin.ora71_queue',
destination_queue_name => 'stream_admin.q_ora71_to_ora72@ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
queue_to_queue =>true,
source_database => 'ora71',
inclusion_rule => true);
end;
/
#修改propagation休眠时间为0,实时传播LCR。
begin
dbms_aqadm.alter_propagation_schedule(
queue_name => 'ORA71_QUEUE',
destination => '"STREAM_ADMIN"."Q_ORA71_TO_ORA72"@ORA72',
latency => 0);
end;
/
在Primary库上面设置capture的SGA为100M,避免出现性能问题
-- set the SGA size of capture.
EXEC dbms_capture_adm.set_parameter('CAPTURE_ORA71','_SGA_SIZE','100');
Apply应用进程
#以stream_admin 身份,登录Backup Database。
connect stream_admin /stream_admin
如果同步2个用户需要建两个rules
dbms_streams_adm.add_schema_rules(
schema_name => 'EYMIT',
streams_type => 'apply',
streams_name => 'apply_ora71_to_ora72',
queue_name => 'stream_admin.q_ora71_to_ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ora71',
inclusion_rule => true);
end;
/
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'EYMIT2',
streams_type => 'apply',
streams_name => 'apply_ora71_to_ora72',
queue_name => 'stream_admin.q_ora71_to_ora72',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
source_database => 'ora71',
inclusion_rule => true);
end;
/
如果要应用的用户和源用户不一样,需要进行改用户操作,注意只能改DML,不能改DDL操作,也就是说当执行DDL操作时会报错
from_schema_name => 'EYMIT',
to_schema_name => 'PUBEYMIT',
operation => 'ADD');
在Backup DB库中设置apply的进程为1,11g默认为4,貌似是Bug(ID 345119.1),不修改没有主键的表同步会报错
-- 设置apply进程的并行数量
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'APPLY_ORA71_TO_ORA72',
parameter => 'PARALLELISM',
value => 1 );
END;
/
查询初始化的SCN是否存在或正确
已执行到这一步
#以stream_admin 身份,登录主库。
创建排除RULE , T_ATTACHMENT为附件表,里面有不支持的列,MV_FC_VEHICLE为物化视图,STREAM不支持
connect stream_admin/stream_admin
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES
(
table_name => 'EYMIT2.EXCLUE_TABLE1',
streams_type => 'capture',
streams_name => 'capture_ORA71',
queue_name => 'ORA71_queue',
include_dml => true,
include_ddl => true,
source_database => null,
inclusion_rule => false
);
END;
/
在backup库禁用不同步表的外键约束
FROM DBA_CONSTRAINTS a
WHERE A.owner IN ('EYMIT2','EYMIT')
AND R_CONSTRAINT_NAME IN
(SELECT CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = 'T_ATTACHMENT'
AND CONSTRAINT_TYPE IN ('P','U'));
STREAM
#以stream_admin 身份,登录Backup Database。
connect stream_admin /stream_admin
#启动Apply进程
dbms_apply_adm.start_apply(
apply_name => 'apply_ora71_to_ora72');
end;
/
#以stream_admin 身份,登录主库。
connect stream_admin /stream_admin
#启动Capture进程
dbms_capture_adm.start_capture(
capture_name =>'capture_ora71');
end;
/
发表评论
暂时没有评论,来抢沙发吧~