在异构平台配置Oracle11gR2 Streams同时再配置相同平台的Oracle11gR2 Dataguard

最新推荐文章于 2024-09-14 19:55:29 发布

css48419

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量204

点赞数

文章标签：数据库网络运维

描述：配置北京机房东方时尚项目集群RAC的数据库与东方时尚项目成都数据库的Streams数据传输，
同时配置东方时尚项目成都两台数据库之间的Dataguard，
还需要在东方时尚项目成都的每个数据库中进行一次自动备份。

集团东方时尚项目集群RAC数据库的两台HP UNIX系统Oracle11gR2数据库分别为：10.1.1.1,10.1.1.2 端口为1568 数据库名为bjoms 需要配置Streams传输的用户为omsuser
成都两台Linux enterprise 5系统Oracle11gR2东方时尚项目数据库ip：10.2.2.1，10.2.2.2。端口为1521 数据库名为cdoms 需要配置Streams传输的用户为omsuser
集团东方时尚项目集群数据库（10.1.1.1,10.1.1.2）与 10.2.2.1 数据库进行Streams双向传输
同时需要把东方时尚项目AIX系统的数据库Oracle10gR2（10.3.3.20:1521:dfoms 用户为omsuser）的数据导入到集团东方时尚项目集群数据库和10.2.2.1 数据库中
10.2.2.1 数据库与 10.2.2.2 数据库配置Dataguard

一、配置两端的数据库Streams数据传输
1、在10.2.2.1操作，验证磁盘空间
[root@HYXXDBS01 ~]# df -lh

2、验证集体RAC数据库是否正常
可以查看数据库日志，系统日志等方式。

在10.1.1.1
3、本地tnsnames.ora
BJOMS =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1568))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.2)(PORT = 1568))
    (LOAD_BALANCE = yes)
    (FAILOVER = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bjoms)
      (FAILOVER_MODE =
        (TYPE = Select)
        (METHOD = BASIC)
      )
    )
)

CDOMS_10.2.2.1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = cdoms)
      (SERVER = DEDICATED)
    )
)

4、在10.3.3.20 查看有哪些表空间
select distinct tablespace_name from user_tables

5、在北京集群数据库建表空间、用户
Select * from dba_tablespaces;
--1
create tablespace MHH_YOU_SPACE
datafile '+DATADG/bjoms/datafile/MHH_YOU_SPACE01.dbf'
size 5M autoextend on

--2
create tablespace GPE_YOU_SPACE
datafile '+DATADG/bjoms/datafile/GPE_YOU_SPACE01.dbf'
size 5M autoextend on

--3
create tablespace HH_YOU_SPACE
datafile '+DATADG/bjoms/datafile/HH_YOU_SPACE01.dbf'
size 5M autoextend on

--4
create tablespace DH_YOU_SPACE
datafile '+DATADG/bjoms/datafile/DH_YOU_SPACE01.dbf'
size 5M autoextend on

--5
create tablespace DRI_YOU_SPACE
datafile '+DATADG/bjoms/datafile/DRI_YOU_SPACE01.dbf'
size 5M autoextend on

--6
create tablespace PUB_YOU_SPACE
datafile '+DATADG/bjoms/datafile/PUB_YOU_SPACE01.dbf'
size 5M autoextend on

--7
create tablespace MHH_LOB_SPACE
datafile '+DATADG/bjoms/datafile/MHH_LOB_SPACE01.dbf'
size 5M autoextend on

--8
create tablespace GPE_LOB_SPACE
datafile '+DATADG/bjoms/datafile/GPE_LOB_SPACE01.dbf'
size 5M autoextend on

--9
create tablespace HH_LOB_SPACE
datafile '+DATADG/bjoms/datafile/HH_LOB_SPACE01.dbf'
size 5M autoextend on

--10
create tablespace DH_LOB_SPACE
datafile '+DATADG/bjoms/datafile/DH_LOB_SPACE01.dbf'
size 5M autoextend on

--11
create tablespace DRI_LOB_SPACE
datafile '+DATADG/bjoms/datafile/DRI_LOB_SPACE01.dbf'
size 5M autoextend on

--12
create tablespace PUB_LOB_SPACE
datafile '+DATADG/bjoms/datafile/PUB_LOB_SPACE01.dbf'
size 5M autoextend on

建用户：
--1
create user omsuser
identified by omspassword
default tablespace pub_norm_space

grant dba to omsuser
--2
create user cdomsmd
identified by cdomsmd
default tablespace pub_norm_space

grant connect to cdomsmd

--3
create user hydt
identified by hydt
default tablespace pub_norm_space

grant connect to hydt

--4
create user cdomsread
identified by cdomsread
default tablespace pub_norm_space

grant connect to cdomsread
grant select any table to cdomsread

在10.2.2.1
6、
Select * from dba_tablespaces;
--1
create tablespace MHH_YOU_SPACE
datafile '/u01/app/oracle/oradata/cdoms/MHH_YOU_SPACE01.dbf'
size 5M autoextend on

--2
create tablespace GPE_YOU_SPACE
datafile '/u01/app/oracle/oradata/cdoms/GPE_YOU_SPACE01.dbf'
size 5M autoextend on

--3
create tablespace HH_YOU_SPACE
datafile '/u01/app/oracle/oradata/cdoms/HH_YOU_SPACE01.dbf'
size 5M autoextend on

--4
create tablespace DH_YOU_SPACE
datafile '/u01/app/oracle/oradata/cdoms/DH_YOU_SPACE01.dbf'
size 5M autoextend on

--5
create tablespace DRI_YOU_SPACE
datafile '/u01/app/oracle/oradata/cdoms/DRI_YOU_SPACE01.dbf'
size 5M autoextend on

--6
create tablespace PUB_YOU_SPACE
datafile '/u01/app/oracle/oradata/cdoms/PUB_YOU_SPACE01.dbf'
size 5M autoextend on

--7
create tablespace MHH_LOB_SPACE
datafile '/u01/app/oracle/oradata/cdoms/MHH_LOB_SPACE01.dbf'
size 5M autoextend on

--8
create tablespace GPE_LOB_SPACE
datafile '/u01/app/oracle/oradata/cdoms/GPE_LOB_SPACE01.dbf'
size 5M autoextend on

--9
create tablespace HH_LOB_SPACE
datafile '/u01/app/oracle/oradata/cdoms/HH_LOB_SPACE01.dbf'
size 5M autoextend on

--10
create tablespace DH_LOB_SPACE
datafile '/u01/app/oracle/oradata/cdoms/DH_LOB_SPACE01.dbf'
size 5M autoextend on

--11
create tablespace DRI_LOB_SPACE
datafile '/u01/app/oracle/oradata/cdoms/DRI_LOB_SPACE01.dbf'
size 5M autoextend on

--12
create tablespace PUB_LOB_SPACE
datafile '/u01/app/oracle/oradata/cdoms/PUB_LOB_SPACE01.dbf'
size 5M autoextend on

--13
select * from dba_tablespaces;
--1
create user omsuser
identified by omspassword
default tablespace pub_norm_space

grant dba to omsuser
--2
create user cdomsmd
identified by cdomsmd
default tablespace pub_norm_space

grant connect to cdomsmd

--3
create user hydt
identified by hydt
default tablespace pub_norm_space

grant connect to hydt

--4
create user cdomsread
identified by cdomsread
default tablespace pub_norm_space

grant connect to cdomsread
grant select any table to cdomsread

7、配置北京集群rac传输到10.2.2.1的streams
在北京
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +ARCHDG
Oldest online log sequence     59
Next log sequence to archive   60
Current log sequence           60

在223
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     54
Current log sequence           56

SQL> startup mount
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size                  2213976 bytes
Variable Size            4697622440 bytes
Database Buffers         2013265920 bytes
Redo Buffers               34623488 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

二、下面正式配置Streams的单项传输
源数据库：北京集群数据库
目标数据库：10.2.2.1

1、设置源、目标数据库的相关参数
源：
SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL>
SQL> alter system set global_names=true scope=both;

System altered.

SQL> show parameter aq_tm_processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0

SQL> alter system set aq_tm_processes=2 scope=both;

System altered.

2、
目标：
SQL> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SQL>
SQL> alter system set global_names=true scope=both;

System altered.

SQL> show parameter aq_tm_processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0

SQL> alter system set aq_tm_processes=2 scope=both;

System altered.

3、在源数据库启用追加日志
启用辅助日志
SQL> alter database add supplemental log data;

Database altered.

4、在源、目标数据库创建表空间、用户、授权
源：
查看数据文件位置：
select file_name from dba_data_files where rownum<2；
创建表空间：
create tablespace streams_space
datafile '+DATADG/bjoms/datafile/streams_space.dbf'
size 5M autoextend on
创建用户：
create user strmadmin
identified by strmadmin
default tablespace streams_space
授予dba角色：
grant dba to strmadmin
授予流管理权限：
exec dbms_streams_auth.grant_admin_privilege('strmadmin') ----使用sys 或 system在命令行执行

目标：
查看数据文件位置：
select file_name from dba_data_files where rownum<2；
创建表空间：
create tablespace streams_space
datafile '/u01/app/oracle/oradata/cdoms/streams_space.dbf'
size 5M autoextend on
创建用户：
create user strmadmin
identified by strmadmin
default tablespace streams_space
授予dba角色：
grant dba to strmadmin
授予流管理权限：
exec dbms_streams_auth.grant_admin_privilege('strmadmin') ----使用sys 或 system在命令行执行

5、配置源、目标数据库的tnsnames.ora
源数据库节点1：
CBDBS01-> cd $ORACLE_HOME
CBDBS01-> pwd
/oracle/db/product/11.2.0/db_1
CBDBS01-> cd network/admin
CBDBS01-> vi tnsnames.ora
在文件中末尾增加：

CDOMS_10.2.2.1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = cdoms)
      (SERVER = DEDICATED)
    )
)

测试连接到目标数据库
HDDBS01-> sqlplus omsuser/omspassword@CDOMS_10.2.2.1

配置源数据库节点2：
CBDBS02-> cd $ORACLE_HOME
CBDBS02-> pwd
/oracle/db/product/11.2.0/db_1
CBDBS02-> cd network/admin
CBDBS02-> vi tnsnames.ora
在文件中末尾增加：

CDOMS_10.2.2.1 =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = cdoms)
      (SERVER = DEDICATED)
    )
)

测试连接到目标数据库
HDDBS02-> sqlplus omsuser/omspassword@CDOMS_10.2.2.1

目标数据库：
[oraoms@BZXXDBS01 ~]$ cd $ORACLE_HOME
[oraoms@BZXXDBS01 dbhome_1]$ cd network
[oraoms@BZXXDBS01 network]$ cd admin
[oraoms@BZXXDBS01 admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

在这目录下没有tnsnames.ora文件，新建一个tnsnames.ora文件，然后增加下面的内容：
[oraoms@BZXXDBS01 admin]$ vi tnsnames.ora

BJOMS =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 1568))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.2)(PORT = 1568))
    (LOAD_BALANCE = yes)
    (FAILOVER = ON)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bjoms)
      (FAILOVER_MODE =
        (TYPE = Select)
        (METHOD = BASIC)
      )
    )
)

测试是否连接到源数据库。
[oraoms@HYXXDBS01 admin]$ sqlplus omsuser/omspassword@BJOMS

6、在源、目标数据库创建到目标数据库的db_link
源：
SQL> conn strmadmin/strmadmin

SQL>create database link CDOMS connect to strmadmin identified by strmadmin using 'CDOMS_10.2.2.1';

测试：
select * from global_name@CDOMS

目标：
SQL> conn strmadmin/strmadmin

SQL>create database link BJOMS connect to strmadmin identified by strmadmin using 'BJOMS';

测试：
select * from global_name@BJOMS

7、需要传输bhomswas用户的数据，在源数据库创建source队列

CBDBS01-> sqlplus strmadmin/strmadmin

SQL>
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table=>'SOURCE_QUEUE_TABLE',
queue_name=>'SOURCE_QUEUE',
queue_user=>'strmadmin');
END;
/

PL/SQL procedure successfully completed.

或者:

SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

PL/SQL procedure successfully completed.
该命令会创建一个队列缺省名：streams_queue，队列表缺省是：STREAMS_QUEUE_TABLE

队列存储的object类型是anaydata

移除队列：
exec dbms_streams_adm.remove_queue(
queue_name => 'streams_queue',
cascade => true,
drop_unused_queue_table => true);

可以用查询dba_queues,dba_queue_tables来检查：

SQL> select owner,queue_table,name from dba_queues where wner='STRMADMIN';

OWNER QUEUE_TABLE NAME

----------------- ------------------------- ------------------

STRMADMIN SOURCES_QUEUE_TABLE SOURCES_QUEUE

STRMADMIN SOURCES_QUEUE_TABLE AQ$_SOURCES_QUEUE_TABLE_E

SQL>select owner,queue_table,object_type from dba_queue_tables where wner='STRMADMIN';

OWNER QUEUE_TABLE OBJECT_TYPE

-------------- --------------------- ------------------

STRMADMIN SOURCES_QUEUE_TABLE SYS.ANYDATA

8、在目标数据库创建接收队列
[oraoms@BZXXDBS01 admin]$ sqlplus strmadmin/strmadmin

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 9 16:16:20 2010

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table=>'TARGET_QUEUE_TABLE',
queue_name=>'TARGET_QUEUE',
queue_user=>'strmadmin');
END;
/

PL/SQL procedure successfully completed.

9、在源数据库创建capture进程
CBDBS01-> sqlplus strmadmin/strmadmin@CBOMS

BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name=>'omsuser',
streams_type=>'capture',
streams_name=>'capture_stream',
queue_name=>'strmadmin.SOURCE_QUEUE',
include_dml=>true,
include_ddl=>true,
SOURCE_DATABASE=>'BJOMS',
include_tagged_lcr=>false,
inclusion_rule=>true);
END;
/

PL/SQL procedure successfully completed.

可以通过dba_capture查看：

SQL> select CAPTURE_NAME,QUEUE_NAME,START_SCN,STATUS,CAPTURE_TYPE from dba_capture;

10、在源数据库继续创建传播进程
CBDBS01-> sqlplus strmadmin/strmadmin

SQL>
BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name=>'omsuser',
streams_name=>'source_to_target',
source_queue_name=>'strmadmin.SOURCE_QUEUE',
destination_queue_name=>'strmadmin.TARGET_QUEUE@CDOMS',
include_dml=>true,
include_ddl=>true,
source_database=>'BJOMS',
inclusion_rule=>true,
queue_to_queue=>true);
END;
/

PL/SQL procedure successfully completed.

可以通过dba_propagations查看propagation是否启动

启动
SQL>exec dbms_propagation_adm.start_propagation('source_to_target'); （关闭为exec dbms_propagation_adm.stop_propagation('source_to_target');）

11、在目标数据库创建apply进程
[oraoms@BZXXDBS01 admin]$ sqlplus strmadmin/strmadmin

SQL>
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name=>'omsuser',
streams_type=>'apply',
streams_name=>'target_apply_stream',
queue_name=>'strmadmin.TARGET_QUEUE',
include_dml=>true,
include_ddl=>true,
include_tagged_lcr=>false,
source_database=>'BJOMS',
inclusion_rule=>true);
END;
/

PL/SQL procedure successfully completed.

12、直接设置SCN的方式进行实例化
在源数据库：
获取源库互置用户的SCN
sqlplus strmadmin/strmadmin
SQL> set serveroutput on
SQL>
DECLARE
iscn NUMBER;
BEGIN
iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is :'||iscn);
END;
/
Instantiation SCN is :7278344

PL/SQL procedure successfully completed.

13、在目标数据库
设置为目标库互置用户的SCN
sqlplus strmadmin/strmadmin
SQL>
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=>'omsuser',
source_database_name=>'BJOMS',
instantiation_scn=>&iscn);
END;
/
Enter value for iscn: 7278344
old 5: instantiation_scn=>&iscn);
new 5: instantiation_scn=>7278344);

PL/SQL procedure successfully completed.

14、在目标数据库启动Apply进程
SQL>
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name=>'target_apply_stream');
END;
/

PL/SQL procedure successfully completed.

#停止Apply进程

SQL>begin
dbms_apply_adm.stop_apply(
apply_name => 'target_apply_stream');
end;
/

查看状态

SQL> select apply_name,queue_name,status from dba_apply;

15、在源数据库上启动capture
SQL>
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name=>'capture_stream');
END;
/

PL/SQL procedure successfully completed.

#停止Capture进程

begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_stream');
end;
/

查看状态：

注意：如果出错，可以执行清除配置，这样重新再来配置：
SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
查看目标数据库实例化的对象：
select * from dba_apply_instantiated_objects;

三、配置Streams的反向传输
在Oracle11g Streams单向传输的基础上，配置Streams双向传输

描述：
原来在两个数据库服务器直接配置了Streams的单向传输，
原来的源数据库为两台集群64位HP-UNIX的Oracle11gR2数据库，
原来的目标数据库为1台64位Linux的Oracle11gR2的数据库，
现在上面的基础上，把1台64位Linux的Oracle11gR2的当作源数据库，把两台集群64位HP-UNIX的Oracle11gR2数据库当作目标数据库，
安装Streams的单向传输步骤进行配置，这样就变成了两个数据库服务器之间的Steams的双向传输。
。
10.2.2.1:为源数据库 10.1.1.1及10.1.1.2 集群数据库为目标数据库

1、源数据库归档模式
源：
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     420
Next log sequence to archive   422
Current log sequence           422

2、在源数据库启用追加日志
启用辅助日志
SQL> alter database add supplemental log data;

Database altered.

3、需要传输testUser 用户的数据，在源数据库创建source队列

> sqlplus strmadmin/strmadmin

SQL>
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table=>'SOURCE_QUEUE_TABLE',
queue_name=>'SOURCE_QUEUE',
queue_user=>'strmadmin');
END;
/
PL/SQL procedure successfully completed.

4、在目标数据库创建接收队列
$ sqlplus strmadmin/strmadmin
SQL>
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table=>'TARGET_QUEUE_TABLE',
queue_name=>'TARGET_QUEUE',
queue_user=>'strmadmin');
END;
/

PL/SQL procedure successfully completed.

5、在源数据库创建capture进程
CBDBS01-> sqlplus strmadmin/strmadmin@CBOMS

SQL>
BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name=>'omsuser',
streams_type=>'capture',
streams_name=>'capture_stream',
queue_name=>'strmadmin.SOURCE_QUEUE',
include_dml=>true,
include_ddl=>true,
SOURCE_DATABASE=>'cdoms',
include_tagged_lcr=>false,
inclusion_rule=>true);
END;
/

PL/SQL procedure successfully completed.

6、在源数据库继续创建传播进程
CBDBS01-> sqlplus strmadmin/strmadmin
SQL>
BEGIN DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
schema_name=>'omsuser',
streams_name=>'source_to_target',
source_queue_name=>'strmadmin.SOURCE_QUEUE',
destination_queue_name=>'strmadmin.TARGET_QUEUE@BJOMS',
include_dml=>true,
include_ddl=>true,
source_database=>'cdoms',
inclusion_rule=>true,
queue_to_queue=>true);
END;
/

PL/SQL procedure successfully completed.

可以启动
SQL>exec dbms_propagation_adm.start_propagation('source_to_target'); （关闭为exec dbms_propagation_adm.stop_propagation('source_to_target');）

7、在目标数据库创建apply进程
$ sqlplus strmadmin/strmadmin
SQL>
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
schema_name=>'omsuser',
streams_type=>'apply',
streams_name=>'target_apply_stream',
queue_name=>'strmadmin.TARGET_QUEUE',
include_dml=>true,
include_ddl=>true,
include_tagged_lcr=>false,
source_database=>'cdoms',
inclusion_rule=>true);
END;
/

PL/SQL procedure successfully completed.

8、直接设置SCN的方式进行实例化
源：
获取源库互置用户的SCN
sqlplus strmadmin/strmadmin
SQL> set serveroutput on
SQL>
DECLARE
iscn NUMBER;
BEGIN
iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE('Instantiation SCN is :'||iscn);
END;
/
Instantiation SCN is :7295863

PL/SQL procedure successfully completed.

在目标数据库：
设置为目标库互置用户的SCN

sqlplus strmadmin/strmadmin
SQL>
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=>'omsuser',
source_database_name=>'cdoms',
instantiation_scn=>&iscn);
END;
/
Enter value for iscn: 7295863
old 5: instantiation_scn=>&iscn);
new 5: instantiation_scn=>7295863);

PL/SQL procedure successfully completed.

9、在目标数据库启动Apply进程
SQL>
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name=>'target_apply_stream');
END;
/

PL/SQL procedure successfully completed.

附注：
1、#停止Apply进程

SQL>begin
dbms_apply_adm.stop_apply(
apply_name => 'target_apply_stream');
end;
/

2、启动Apply进程
begin
dbms_apply_adm.start_apply(
apply_name => 'target_apply_stream');
end;
/

查看Apply状态

SQL> select apply_name,queue_name,status from dba_apply;

3、启动传播进程
exec dbms_propagation_adm.start_propagation('source_to_target');
关闭：
exec dbms_propagation_adm.stop_propagation('source_to_target');

4、在源数据库上启动capture
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name=>'capture_stream');
END;
/

PL/SQL procedure successfully completed.

5、#停止Capture进程
begin
dbms_capture_adm.stop_capture(
capture_name => 'capture_stream');
end;
/
启动：
begin
dbms_capture_adm.start_capture(
capture_name => 'capture_stream');
end;
/

查看Capture状态：

SQL> select capture_name,status from dba_capture;

如果在目标端（北京集群数据库）出现下面的错误：
select * from dba_apply_error
ORA-26687: no instantiation SCN provided for "BHOMSWAS"."TT" in source database "CDOMS"

解决为：
在目标数据库：
select APPLY_NAME,LOCAL_TRANSACTION_ID,SOURCE_COMMIT_SCN,MESSAGE_NUMBER,ERROR_MESSAGE from dba_apply_error;
得到SOURCE_COMMIT_SCN 11071236
在源数据库执行：
SQL>
begin
DBMS_APPLY_ADM.set_table_instantiation_scn@bjoms(source_object_name=>'omsuser.TT',source_database_name=>'cdoms',instantiation_scn=>11071236);
end;
/

然后在目标数据库：
SQL>
BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR
(local_transaction_id =>'20.16.180786',
execute_as_user=>false,
user_procedure =>null);
end;
/

如果出错，可以执行清除配置，这样重新再来配置：
SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

经过测试，两端数据库可以互相传输数据。

查看目标数据库实例化的对象：
select * from dba_apply_instantiated_objects;

四、用expdp，impdp导入导出数据到北京集群数据库和10.2.2.1数据库

在10.3.3.20服务器操作：
1、
SQL> create directory expdp_dir as '/oracle/oraarch/expdp_dir';
SQL> grant read,write on directory expdp_dir to omsuser；

2 、创建系统目录/oracle/oraarch/expdp_dir

3、在DOS命令窗口导出：
expdp omsuser/omspassword DIRECTORY=expdp_dir dumpfile=omsuser1227.dmp logfile=omsuser1227expdp.log

在10.1.1.1服务器中操作：
4、SQL> create directory impdp_dir as '/oracle/db/impdp_dir';
SQL> grant read,write on directory impdp_dir to omsuser;

在系统中需要有/home/oracle/impdp_dir目录，在impdp_dir目录下必须有读写权限
（chmod 777 impdp_dir）

5、用ftp上传数据到北京的数据库服务器10.1.1.1的/oracle/db/impdp_dir
(因为是集群，最好是共享的路径，不然数据库找不到相应的数据文件)

6、在10.1.1.1服务器命令导入：
impdp omsuser/omspassword DIRECTORY=impdp_dir dumpfile=omsuser1227.dmp logfile=omsuser1227impdp.log
（这里注意大小写，如果omsuser1227.dmp.dmp在linux中为大写，必须更改为大写。Linux区分大小写）

7、在10.2.2.1服务器
SQL> create directory impdp_dir as '/home/oraoms/impdp_dir';

Directory created.

SQL> grant read,write on directory impdp_dir to omsuser;

8、在10.3.3.20服务器上把刚导出来的数据文件上传到10.2.2.1服务器的/home/oraoms/impdp_dir目录中
导入：
impdp omsuser/omspassword DIRECTORY=impdp_dir dumpfile=omsuser1227.dmp logfile=omsuser1227impdp.log

五、配置10.2.2.1与10.2.2.2两台数据库之间的dataguard
主库：10.2.2.1
备库：10.2.2.2
1、在10.2.2.2查看磁盘空间大小
[root@HYXXDBS02 ~]# df -lh

2、数据库是否归档
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     64
Current log sequence           66
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size                  2213976 bytes
Variable Size            4496295848 bytes
Database Buffers         2214592512 bytes
Redo Buffers               34623488 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

3、在主库和备库更改归档日志的空间大小
SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 5G
recovery_parallelism                 integer     0

SQL> alter system set db_recovery_file_dest_size=50G scope=both;

4、在主库设置：
SQL>ALTER DATABASE FORCE HHGING;
查看下面参数：
如：SQL> show parameter HH_ARCHIVE_DEST_1
主库
DB_NAME=cdoms
DB_UNIQUE_NAME=cdoms01 （如果是spfile文件，alter system set db_unique_name='cdoms01' scope=spfile; 统一修改参数后，可以重启数据库）
HH_ARCHIVE_CONFIG='DG_CONFIG=(cdoms01,cdoms02)' （alter system set log_archive_config='dg_config=(cdoms01,cdoms02)';）
HH_ARCHIVE_DEST_1='location=/u01/app/oracle/flash_recovery_area/CDOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cdoms01'
(alter system set log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/CDOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cdoms01';)
HH_ARCHIVE_DEST_2='service=cdoms02 async valid_for=(online_logfiles,primary_role) db_unique_name=cdoms02'
(alter system set log_archive_dest_2='service=cdoms02 async valid_for=(online_logfiles,primary_role) db_unique_name=cdoms02';)
HH_ARCHIVE_DEST_STATE_1=ENABLE (alter system set HH_ARCHIVE_DEST_STATE_1=ENABLE;)
HH_ARCHIVE_DEST_STATE_2=ENABLE (alter system set HH_ARCHIVE_DEST_STATE_2=ENABLE;)
FAL_SERVER=cdoms02 (alter system set fal_server=cdoms02;)
FAL_CLIENT=cdoms01 (alter system set fal_client=cdoms01;)
DB_FILE_NAME_CONVERT='cdoms02','cdoms01' (alter system set DB_FILE_NAME_CONVERT='cdoms02','cdoms01' scope=spfile;)
HH_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdoms/','/u01/app/oracle/oradata/cdoms','/u01/app/oracle/flash_recovery_area/CDOMS02/onlinelog','/u01/app/oracle/flash_recovery_area/CDOMS01/onlinelog'
(alter system set log_file_name_convert='/u01/app/oracle/oradata/cdoms/','/u01/app/oracle/oradata/cdoms','/u01/app/oracle/flash_recovery_area/CDOMS02/onlinelog','/u01/app/oracle/flash_recovery_area/CDOMS01/onlinelog' scope=spfile;)
STANDBY_FILE_MANAGEMENT=AUTO (alter system set STANDBY_FILE_MANAGEMENT=AUTO;)

------------------------------
错误：
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/CDOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cdoms01';
alter system set log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/CDOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cdoms01'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16053: DB_UNIQUE_NAME cdoms01 is not in the Data Guard Configuration

这是因为刚执行的alter system set db_unique_name='cdoms01' scope=spfile;还没有数据库识别到
需要重新启动数据库可以解决。
SQL> select * from V$DATAGUARD_CONFIG ;

DB_UNIQUE_NAME
------------------------------
cdoms
cdoms01
cdoms02
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

SQL> select * from V$DATAGUARD_CONFIG ;

DB_UNIQUE_NAME
------------------------------
cdoms01
cdoms02
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/CDOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cdoms01';
System altered.

5、关闭数据库，启动，让刚设置的参数有效
SQL> shutdown immediate;

启动：
SQL> startup

查看：
SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
cdoms01
cdoms02

6、在主库备份
[oraoms@BZXXDBS01 ~]$ rman target/
RMAN> backup database;

7、在备库启动ftp服务，这样把主库备份的数据、控制文件、密码文件、初始化文件上传到备库：
[root@BZXXDBS02 ~]#

编写：
vi /etc/xinetd.d/gssftp 把disable=yes改成no，然后打开ftp服务

把server_args = -l -a 改成 server_args = -l 把 -a 去掉，这样系统的用户才有权限用ftp登陆

[root@BZXXDBS02 ~]#
[root@BZXXDBS02 ~]# service xinetd restart
Stopping xinetd: [ OK ]
Starting xinetd: [ OK ]

查看21端口：
[root@BZXXDBS02 etc]# netstat -tnl

8、在主库：
把备份的数据文件ftp上传到备库/home/oraoms/backup上
ftp 10.2.2.2
输入用户、密码
cd /home/oraoms/backup
bin
put 文件名
bye

9、在主库上创建备库的控制文件
SQL> alter database create standby controlfile as '/home/oraoms/cdoms02.ctl';

Database altered.

10、在主库上创建备份需要的pfile文件
SQL> create pfile='/home/oraoms/initcdoms.ora' from spfile;

File created.

11、把上面的控制文件、pfile文件、密码文件上传到备库
（密码文件位于：$ORACLE_HOME/dbs/orapwcdoms）

12、在备库上对文件授权
[oraoms@BZXXDBS02 backup]$ pwd
/home/oraoms/backup
[oraoms@BZXXDBS02 backup]$ chmod 777 *

13、假设备库上已经安装好数据库名为cdoms的数据库
配置备库tnsnames.ora($ORACLE_HOME/network/admin/tnsnames.ora)
cdoms01 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdoms01)
    )
)

cdoms02 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdoms02)
    )
)

测试：
[oraoms@BZXXDBS02 admin]$ tnsping cdoms01
[oraoms@BZXXDBS02 admin]$ tnsping cdoms02

14、同时配置主库的tnsnames.ora($ORACLE_HOME/network/admin/tnsnames.ora)
cdoms01 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdoms01)
    )
)

cdoms02 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.2.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdoms02)
    )
)
测试：
[oraoms@BZXXDBS01 admin]$ tnsping cdoms01
[oraoms@BZXXDBS01 admin]$ tnsping cdoms02

15、关闭数据库，备份数据文件到其他的目录
SQL>shutdown immediate

16、在备库上修改initcdoms.ora参数
*.db_unique_name='cdoms02'
*.db_file_name_convert='cdoms01','cdoms02'
*.fal_client='cdoms02'
*.fal_server='cdoms01'
*.log_file_name_convert='/u01/app/oracle/oradata/cdoms/','/u01/app/oracle/oradata/cdoms','/u01/app/oracle/flash_recovery_area/CDOMS01/onlinelog','/u01/app/oracle/flash_recovery_area/CDOMS02/onlinelog'
*.log_archive_dest_1='location=/u01/app/oracle/flash_recovery_area/CDOMS/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=cdoms02'
*.log_archive_dest_2='service=cdoms01 async valid_for=(online_logfiles,primary_role) db_unique_name=cdoms01'

17、在备库复制初始化文件pfile、密码文件到$ORACLE_HOME/dbs目录下
[oraoms@BZXXDBS02 dbs]$ cp /home/oraoms/backup/initcdoms.ora $ORACLE_HOME/dbs/
[oraoms@BZXXDBS02 dbs]$ cp /home/oraoms/backup/orapwcdoms $ORACLE_HOME/dbs/

18、在备库生成spfile初始化文件
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initcdoms.ora';

File created.

19、启动备库到nomount状态：
SQL> startup nomount
ORACLE instance started.

Total System Global Area 6747725824 bytes
Fixed Size                  2213976 bytes
Variable Size            5033166760 bytes
Database Buffers         1677721600 bytes
Redo Buffers               34623488 bytes

20、恢复备库控制文件
[oraoms@BZXXDBS02 dbs]$ rman target/
RMAN> restore controlfile from '/home/oraoms/backup/cdoms02.ctl';

Starting restore at 21-DEC-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=601 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/cdoms/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/cdoms/control02.ctl
Finished restore at 21-DEC-10

21、更改数据库到mount状态
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

22、可以查看dataguard配置：
SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME
------------------------------
cdoms02
cdoms01

23、恢复数据文件时，需要在备库上创建和主库一致的放置备份数据的目录
(如：/u01/app/oracle/flash_recovery_area/CDOMS01/backupset/2010_12_28/)
[oraoms@BZXXDBS02 flash_recovery_area]$ pwd
/u01/app/oracle/flash_recovery_area
[oraoms@BZXXDBS02 flash_recovery_area]$ mkdir CDOMS01
[oraoms@BZXXDBS02 flash_recovery_area]$ cd CDOMS01
[oraoms@BZXXDBS02 CDOMS01]$ mkdir backupset
[oraoms@BZXXDBS02 CDOMS01]$ cd backupset/
[oraoms@BZXXDBS02 backupset]$ mkdir 2010_12_21

24、把备份的数据移动到目录中
[oraoms@BZXXDBS02 2010_12_21]$ mv /home/oraoms/backup/o1_mf_ncsnf_TAG20101221T091706_6k001pvl_.bkp /u01/app/oracle/flash_recovery_area/CDOMS01/backupset/2010_12_28
[oraoms@BZXXDBS02 2010_12_21]$ mv /home/oraoms/backup/o1_mf_nnndf_TAG20101221T091706_6k000lpz_.bkp /u01/app/oracle/flash_recovery_area/CDOMS01/backupset/2010_12_28

25、在备份恢复数据
RMAN> restore database;

Starting restore at 21-DEC-10
Starting implicit crosscheck backup at 21-DEC-10
。。。。。
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-DEC-10

26、关闭备库数据库：
SQL>shutdown immediate
SQL>startup nomount
SQL>
SQL> alter database mount standby database;

27、在备库只读打开模式：
SQL> alter database open read only;
让主备库同步：
SQL> alter database recover managed standby database disconnect from session;

Database altered.

28、验证：
在备库查看归档日志
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_HH ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
721 21-DEC-10 21-DEC-10
722 21-DEC-10 21-DEC-10

在主库强制归档日志
SQL>ALTER SYSTEM SWITCH HHFILE;

再次查询备库：
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_HH ORDER BY SEQUENCE#;

备库可以实时同步.

六、在10.2.2.1服务器上实施晚上11:30进行自动备份。
因为在北京集群服务器已经在带库中保持半年以上的备份，
这里因为空间问题，只保留最近一次的备份。

备份10.2.2.1：
1、RMAN> show all;
冗余备份为1份最近的历史备份数据
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
自动优化备份
CONFIGURE BACKUP OPTIMIZATION ON;
自动备份控制文件
CONFIGURE CONTROLFILE AUTOBACKUP ON;

2、创建备份的目录
[oraoms@HYXXDBS01 app]$ pwd
/u01/app
[oraoms@HYXXDBS01 app]$ mkdir rmanBackup

3、因为空间问题，只保留最近一次备份
[oraoms@HYXXDBS01 rmanBackup]$ vi backupFull.sql
内容：
#   script.:bakupFull.sql
#   creater:mengzhaoliang
#   date:2010/12/28
#   desc:backup full database datafile in archive with rman

# connect database
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=cdoms
export PATH=$ORACLE_HOME/bin:$PATH
rman target/ << EOF_RMAN
run{
allocate channel c1 type disk;
backup tag 'full' format '/u01/app/rmanBackup/db0_%d_%T_%s' database include current controlfile;
delete noprompt obsolete;
release channel c1;
}
# end

备注：如果没有加上export 的环境变量，Linux的crontab不能执行找不到命令执行脚本

[oraoms@HYXXDBS01 rmanBackup]$ chmod 777 *.*

4、编写定时器crontab,定制每天23：30调用/u01/app/rmanBackup/backupFull.sql脚本
[oracle@mzl ~]$ crontab -e
30 23 * * 0-6 /u01/app/rmanBackup/backupFull.sql >>/u01/app/rmanBackup/backupFull.log

5、查看备份总体信息：
RMAN> list backup summary;

在10.2.2.2服务器进行备份：
1、RMAN> show all;
冗余备份为1份最近的历史备份数据
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
自动优化备份
CONFIGURE BACKUP OPTIMIZATION ON;
自动备份控制文件
CONFIGURE CONTROLFILE AUTOBACKUP ON;

2、创建备份目录
[oraoms@HYXXDBS01 app]$ pwd
/u01/app
[oraoms@HYXXDBS01 app]$ mkdir rmanBackup