物化檢視實現資料庫倉庫主從複製(1)

文章版權所有 Jusin Hao（luckyfriends） ，支援原創，轉載請註明。

1. 實施背景及環境

本方案是利用Oracle物化檢視實現主從同步部分表。

1.1. 涉及主機：

10.168.2.11：BI主庫，SID:bidw，監聽埠：1356）

10.168.2.12：BI從庫（複製庫），SID:bidwstb，監聽埠：1521）

1.2. 涉及的表

‘CHECK_DM', …..’

1.3. 獲取含有主鍵的表的列表

select ''''|| a.table_name || ''','

from dba_constraints a

where a.table_name in

('CHECK_DM',…..) and a.constraint_type='P';

2. 建庫

2.1. 建立相應檔案

[oracle@standby12 response]$ cd /home/oracle/bieeDownload/database/response

[oracle@standby12 response]$ ls

dbca.rsp db_install.rsp netca.rsp

[oracle@standby12 response]$ cp dbca.rsp /home/oracle/lf/db_create.rsp

[oracle@standby12 response]$

或

[oracle@standby12 lf]$ pwd

/home/oracle/lf

[oracle@standby12 lf]$ vi db_create.rsp

#--------------------------------------------------------------------

#以下引數不要更改

[GENERAL]

RESPONSEFILE_VERSION = "11.2.0"

OPERATION_TYPE = "createDatabase"

#以下引數必須設定

[CREATEDATABASE]

GDBNAME = "bidwstb"

TEMPLATENAME = "General_Purpose.dbc"

#以下引數不設定則使用預設值,建議設定

CHARACTERSET = "ZHS16GBK"

TOTALMEMORY = "2048"

#--------------------------------------------------------------------

2.2. 環境變數

[oracle@standby12 ~]$ more .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

# Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=standby12.localdomain; export ORACLE_HOSTNAME

#ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=orcl; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

#HADOOP_DLL=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

#export HADOOP_DLL

#LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib/:/opt/cloudera/impalaodbc/lib/64/

#LD_LIBRARY_PATH=/usr/local/lib/:/opt/cloudera/impalaodbc/lib/64/

#LD_LIBRARY_PATH=/lib:/usr/lib:/usr/local/lib/:/opt/cloudera/impalaodbc/lib/64/:$ORACLE_HOME

LD_LIBRARY_PATH=/usr/local/unixodbc/lib/:/opt/cloudera/impalaodbc/lib/64:$ORACLE_HOME

#:$ORACLE_HOME

export LD_LIBRARY_PATH

#CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

#add cloudera env

export ODBCINI=~/odbc.ini

export ODBCSYSINI=~/odbcinst.ini

export FMWHOME=/u01/wls/

#export NLS_LANG=AL32UTF8

export USE_ZEND_ALLOC=0

[oracle@standby12 ~]$

2.3. 開始靜默建庫

[oracle@standby12 lf]$ $ORACLE_HOME/bin/dbca -silent -responseFile /home/oracle/lf/db_create.rsp

Enter SYS user password:

Enter SYSTEM user password:

Copying database files

1% complete

3% complete

11% complete

18% complete

26% complete

37% complete

Creating and starting Oracle instance

40% complete

45% complete

50% complete

55% complete

56% complete

60% complete

62% complete

Completing Database Creation

66% complete

70% complete

73% complete

85% complete

96% complete

100% complete

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/bidwstb/bidwstb.log" for further details.

2.4. 檢視安裝日誌

[oracle@standby12 bidwstb]$ cd /u01/app/oracle/cfgtoollogs/dbca/bidwstb

[oracle@standby12 bidwstb]$ ls -alt

total 60

-rw-r----- 1 oracle oinstall 41206 May 20 10:36 trace.log

drwxr-x--- 2 oracle oinstall 4096 May 20 10:35 .

-rw-r----- 1 oracle oinstall 197 May 20 10:35 CloneRmanRestore.log

-rw-r----- 1 oracle oinstall 1376 May 20 10:35 rmanRestoreDatafiles.sql

drwxr-x--- 4 oracle oinstall 4096 May 20 10:35 ..

[oracle@standby12 bidwstb]$ tail -f trace.log

[Thread-11] [ 2014-05-20 10:38:29.450 CST ] [PostDBCreationStep.executeImpl:501] before recomp_serial

[Thread-11] [ 2014-05-20 10:38:35.218 CST ] [PostDBCreationStep.executeImpl:507] after recomp_serial

[Thread-11] [ 2014-05-20 10:38:35.219 CST ] [PostDBCreationStep.executeImpl:512] cleaning up AWR data

[Thread-11] [ 2014-05-20 10:38:36.390 CST ] [PostDBCreationStep.executeImpl:515] AWR cleanup is done

[Thread-11] [ 2014-05-20 10:38:49.843 CST ] [PostDBCreationStep.executeImpl:635] PostDBCreationStep: bShared=false

[Thread-11] [ 2014-05-20 10:38:49.878 CST ] [Host.startupDBWithApprInitFile:7257] startDBWithApp: startup clause=

[Thread-11] [ 2014-05-20 10:38:57.601 CST ] [SQLEngine.spoolOff:2008] Setting spool off = /u01/app/oracle/cfgtoollogs/dbca/bidwstb/postDBCreation.log

[Thread-11] [ 2014-05-20 10:38:57.602 CST ] [BasicStep.configureSettings:304] messageHandler being set=oracle.sysman.assistants.util.SilentMessageHandler@3e018c74

[main] [ 2014-05-20 10:38:57.603 CST ] [SQLEngine.done:2148] Done called

[main] [ 2014-05-20 10:38:57.603 CST ] [Host.cleanup:3407] Dbca exit status is: 0

[main] [ 2014-05-20 10:38:57.604 CST ] [Host.cleanup:3410] check point context oracle.sysman.assistants.util.CheckpointContext@4a48edb5

[main] [ 2014-05-20 10:38:57.609 CST ] [InventoryUtil.getOUIInvSession:347] setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS

[main] [ 2014-05-20 10:38:57.609 CST ] [InventoryUtil.getHomeName:111] homeName = OraDb11g_home1

[main] [ 2014-05-20 10:38:57.610 CST ] [Host.cleanup:3427] check point oracle.sysman.oic.oics.OicsCheckPoint@3ad3c6a3

[main] [ 2014-05-20 10:38:57.610 CST ] [Host.cleanup:3439] adding checkpoint to session

[main] [ 2014-05-20 10:38:57.705 CST ] [OsUtilsBase.copyFile:1413] OsUtilsBase.copyFile:

[main] [ 2014-05-20 10:38:57.706 CST ] [OsUtilsBase.copyFile:1461] **write of file at destination complete...

[main] [ 2014-05-20 10:38:57.706 CST ] [OsUtilsBase.copyFile:1496] **file copy status:= true

[main] [ 2014-05-20 10:38:57.707 CST ] [OsUtilsBase.deleteFile:1711] OsUtilsBase.deleteFile: /u01/app/oracle/cfgtoollogs/dbca/silent.log

2.5. 監聽

[oracle@standby12 lf]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 20-MAY-2014 10:42:20

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.168.2.12)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 17-DEC-2013 11:29:00

Uptime 153 days 23 hr. 13 min. 20 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/standby12/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.168.2.12)(PORT=1521)))

Services Summary...

Service "dwstb" has 1 instance(s).

Instance "bidwstb", status READY, has 1 handler(s) for this service...

Service "bidwstbXDB" has 1 instance(s).

Instance "bidwstb", status READY, has 1 handler(s) for this service...

[oracle@standby12 lf]$

3. 建立表空間、使用者

3.1. 複製庫建立表空間及使用者

---建立存放資料的表空間bi_user_data

create tablespace bi_user_data datafile '/u01/app/oracle/oradata/bidwstb/bi_user_data01.dbf' size 20G autoextend on maxsize 34359721984;

alter tablespace bi_user_data add datafile '/u01/app/oracle/oradata/bidwstb/bi_user_data02.dbf' size 5G autoextend on maxsize 34359721984;

alter tablespace bi_user_data add datafile '/u01/app/oracle/oradata/bidwstb/bi_user_data03.dbf' size 5G autoextend on maxsize 34359721984;

alter tablespace bi_user_data add datafile '/u01/app/oracle/oradata/bidwstb/bi_user_data04.dbf' size 5G autoextend on maxsize 34359721984;

alter tablespace bi_user_data add datafile '/u01/app/oracle/oradata/bidwstb/bi_user_data05.dbf' size 5G autoextend on maxsize 34359721984;

-----建立存放索引 bi_user_index

create tablespace bi_user_index datafile '/u01/app/oracle/oradata/bidwstb/bi_user_index01.dbf' size 20G autoextend on maxsize 34359721984;

alter tablespace temp add tempfile '/u01/app/oracle/oradata/bidwstb/temp02.dbf' size 5G autoextend on maxsize 34359721984;

---建立使用者BI_USER12

create user BI_USER12 identified by oracle default tablespace bi_user_data temporary tablespace temp;

grant connect,dba to BI_USER12;

3.2. 生產庫建立表空間

create tablespace BI_MATERIALIZED datafile '/u02/oracle/oradata/BIDW/BI_MATERIALIZED_01.dbf' size 5G autoextend on maxsize 34359721984;