在異構平臺配置Oracle11gR2 Streams同時再配置相同平臺的Oracle11gR2 Dataguard
描述：配置北京機房東方時尚專案叢集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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12778571/viewspace-682828/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DataGuard對異構平臺的支援
- 跨平臺級聯dataguard配置
- 【DG】Oracle11g異構平臺之Linux To Windows DataGuard安裝配置--duplicateOracleLinuxWindows
- ORACLE11GR2 RAC DATABASE+STANDLONE DATAGUARD配置摘要OracleDatabase
- oracle 11g dataguard 可以互相支援的異構平臺Oracle
- Oracle資料庫同平臺與異構平臺下的表空間傳輸Oracle資料庫
- AIX平臺NFS配置AINFS
- rman之同平臺異機恢復
- 同平臺異機備份恢復
- 配置高可用的Hadoop平臺Hadoop
- Oracle11gR2 Active DataGuardOracle
- Oracle11gR2 Dataguard搭建Oracle
- 工位出租，資源配置平臺
- 在一臺Mac上不同平臺同時使用多個Git賬號MacGit
- C#Winform快速開發平臺與軟體配置平臺C#ORM
- 在多臺Cisco交換機更改相同的配置命令(轉)
- windows平臺下配置cron服務Windows
- mac平臺安裝配置TomCatMacTomcat
- Data Guard 異構平臺支援手冊
- Oracle DataGuard跨平臺支援列表Oracle
- 研發協同平臺架構演進架構
- Oracle Data Guard 支援的異構平臺 說明Oracle
- IoT平臺如何實現業務配置中心
- Zabbix Agent-Windows平臺配置指導Windows
- 使用Nginx配置NodeJs程式(Windows平臺)NginxNodeJSWindows
- 異構平臺Data Guard支援列表 413484.1
- 安全的自媒體多平臺一鍵式傳送程式，同時操控30+平臺
- Java跨平臺？慎用這些有平臺差異性的方法Java
- 自媒體多平臺管理，多個平臺同時管理，矩陣運營更方便矩陣
- Linux平臺上Oracle Rac中的TAF配置LinuxOracle
- 異構OS平臺的不同點 - 位元組順序
- Data Guard只支援同平臺同版本的DB
- 容器雲平臺物理叢集配置實踐
- jenkins配置android自動打包平臺JenkinsAndroid
- 開發nagios修改配置檔案平臺iOS
- Oracle DG（Data Guard）支援異構平臺說明Oracle
- Oracle GoldenGate 異構平臺同步（Mysql到Oracle）OracleGoMySql
- oracle11gR2在RedHat5上前期安裝配置指令碼OracleRedhat指令碼