企业级灾备方案Oracle Stream搭建过程

企业级灾备方案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;

/