流复制灾备库实施

搭建基于database级别的流复制灾备库实施步骤：

1.两端数据库分别执行如下参数设置

 alter system set aq_tm_processes=4 scope=both;

 alter system set global_names=true scope=both;

 alter system set job_queue_processes=5 scope=both;

 alter system set parallel_max_servers=20 scope=both;

 alter system set undo_retention=3600 scope=both;

 alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile;

 alter system set streams_pool_size=500M scope=spfile;

 alter system set utl_file_dir='*' scope=spfile;

 alter system set open_links=4 scope=spfile;

 

2.创建流复制管理用户的独立表空间，方便管理：

 create tablespace xxxx datafile '/opt/oracle/oradata/logandb/tbs_stream.dbf' size 100m autoextend on next 10m maxsize unlimited segment space management auto;

 

 execute dbms_logmnr_d.set_tablespace('TBS_STREAMS');

3.创建流复制管理用户：

 create user STRADMIN identified by STRADMIN default tablespace xxxxx temporary tablespace temp;

4.授予stradmin用户所需权限：

 grant connect,resource,dba,aq_administrator_role to STRADMIN;

 

 begin

 dbms_streams_auth.grant_admin_privilege(

 grantee => 'stradmin',

 grant_privileges => true);

 end;

 /

5.开启数据追加日志：

 alter database add supplemental log data;

 

6.修改主、备库的数据库全局名称

 1)sysdba身份登录主库

 alter database rename global_name to MASTER.COM;

 

2)sysdba身份登录备库

 alter database rename global_name to SLAVE.COM;

 

7.配置主、备环境监听

8.创建主、备库的数据链

 1）创建主库到备库的数据链 

 conn stradmin/stradmin

 create database link SLAVE.COM connect to stradmin identified by stradmin using 'SLAVE'; 

 

2）创建备库到主库的数据链

 conn stradmin/stradmin

 create database link MASTER.COM connect to stradmin identified by stradmin using 'MASTER';

9.检查数据链的可用性

 1）登录主库

 conn stradmin/stradmin

 select * from global_name;        /*显示主库的global_name*/

 select * from global_name@SLAVE.COM; /*显示备库的global_name*/

 

2）登录备库

 conn stradmin/stradmin

 select * from global_name;        /*显示备库的global_name*/

 select * from global_name@MASTER.COM; /*显示主库的global_name*/

 

10.创建各自的directory（确保存在所创建路径）

 1）创建主库的directory

 create directory str_source as '/opt/oracle/str_source';

 

 2）创建备库的directory

 create directory str_target as '/opt/oracle/str_target';

11.在主库stradmin用户下，运行如下过程：

conn stradmin/stradmin

 

 begin

 dbms_streams_adm.maintain_global (

 source_directory_object=>'STR_SOURCE',

 destination_directory_object=>'STR_TARGET',

 source_database=>'MASTER.COM',

 destination_database=>'SLAVE.COM',

 perform_actions=>true,

 include_ddl=>true);

 end;

 /