描述：配置北京機房東方時尚專案叢集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

配置源資料庫節點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

目標資料庫：
[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

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

目標：
SQL> conn strmadmin/strmadmin

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

測試：
select * from

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

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

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
);
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

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;

在異構平臺配置Oracle11gR2 Streams同時再配置相同平臺的Oracle11gR2 Dataguard

相關文章