資料遷移要求:

表結構要同步，儲存過程、函式、同義詞是、檢視要、序列更要同步

1 同步資料結構

2 預先匯入大表資料

3 匯入其他資料（注意序列、觸發器）

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

遷移過程:

1. 在本地庫建立用dawn 與值對應的表空間;

2. 用dawn登陸，建立相關的表，主外來鍵約束，索引，儲存過程，函式，同義詞，

檢視，序列，觸發器;

3. 將dawn使用者的資料結構和資料分別匯出;

4. 將dump檔案匯入到RAC 例項;

5. 事後處理[主要包括索引,約束,觸發器的啟用--無效的procedure,function,package

的重新編譯]

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

DB script:

建立使用者和表空間:

SQL> select file_name from dba_data_files;

FILE_NAME

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

E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF

E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF
E:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF

SQL> create tablespace dawn datafile

2 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\dawn.dbf' size 50m;

Tablespace created

SQL> create user dawn identified by dawn default tablespace dawn temporary tablespace temp;

SQL> grant dba to dawn;

SQL> grant connect to dawn;

SQL> grant resource to dawn;

使用dawn登入建立相關資料結構:

SQL> conn dawn/dawn;

--學生表

create table T_student

(

stu_id number not null,

stu_name VARCHAR2(9) not null,

subject_id NUMBER not null

)

alter table t_student add constraint stu_pk primary key(stu_id);

--建立唯一索引

create unique index ind_stu_name on T_student(stu_name); --唯一索引不能插入相同的資料

--課程表

create table t_subject(

subject_id number not null,

subject_name varchar2(50) not null,

subject_teacher varchar2(50)

)

alter table t_subject add constraint sub_pk primary key(subject_id);

--建立序列

create sequence stu_seq increment by 1 start with 1 MAXVALUE 999999 NOCYCLE NOCACHE;

--外來鍵約束,級聯刪除

alter table T_student add constraint stu_fk foreign key (subject_id) references t_subject(subject_id) ON DELETE CASCADE;

--建立試圖--學生課程檢視

create view v_stu_sub as select st.stu_id,st.stu_name,sj.subject_name,sj.subject_teacher from T_student st,t_subject sj

where st.subject_id=sj.subject_id;

--建立函式

CREATE OR REPLACE

FUNCTION F_add (num1 IN NUMBER, num2 IN NUMBER)

RETURN NUMBER

AS

num3 number;

BEGIN

num3 := num1 + num2;

RETURN num3;

--建立重建索引procedure

create or replace procedure BATCH_REBUILD_INDEX is

S_SQL VARCHAR2(500);

ACCOUNT_normal NUMBER := 0;

--normal index rebuild

begin

FOR LINE2 IN (select t4.index_name,t4.tablespace_name from user_indexes t4 where t4.status='UNUSABLE')LOOP

S_SQL := 'alter index ' || LINE2.INDEX_NAME || ' rebuild tablespace '||LINE2.TABLESPACE_NAME||' pararllel 4 nologging';

DBMS_OUTPUT.PUT_LINE(S_SQL);

ACCOUNT_normal := ACCOUNT_normal + 1;

EXECUTE IMMEDIATE S_SQL;

END LOOP;

DBMS_OUTPUT.PUT_LINE('normal index rebuild numbers:'||ACCOUNT_normal);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

end BATCH_REBUILD_INDEX;

--建立同義詞--學生表

create synonym st for dawn.T_student;

--建立觸發器--學生表插入資料前先給stu_id賦值stu_seq.NEXTVAL

create or replace trigger tg_stu_insert

before insert on T_student

for each row

declare

-- local variables here

begin

SELECT stu_seq.NEXTVAL INTO:NEW.stu_id FROM DUAL;

end tg_stu_insert;

--插入資料

insert into t_subject values(11,'English','wanglin');

insert into t_subject values(12,'Chinese','wangBing');

insert into t_subject values(13,'Physics','wangHuan');

insert into t_student(stu_name,subject_id)values('Lucy',11);

insert into t_student(stu_name,subject_id)values('Lily',12);

insert into t_student(stu_name,subject_id)values('Tom',13);

commit;

--查詢

SQL> select * from t_subject;

SUBJECT_ID SUBJECT_NAME SUBJECT_TEACHER

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

11 English wanglin

12 Chinese wangBing

13 Physics wangHuan

SQL> select * from t_student;

STU_ID STU_NAME SUBJECT_ID

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

1 Lucy 11

2 Lily 12

3 Tom 13

-----匯出前禁用索引、約束、觸發器

SQL> conn dawn/dawn;

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as dawn

SQL> select tg.table_name,tg.constraint_type,tg.constraint_name,tg.status from user_constraints tg;

TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS

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

T_STUDENT R STU_FK ENABLED

T_SUBJECT P SUB_PK ENABLED

T_STUDENT P STU_PK ENABLED

T_STUDENT C SYS_C009607 ENABLED

T_STUDENT C SYS_C009606 ENABLED

T_STUDENT C SYS_C009605 ENABLED

T_SUBJECT C SYS_C009610 ENABLED

T_SUBJECT C SYS_C009609 ENABLED

SQL> select dd.index_type,dd.table_name,dd.index_name from user_indexes dd;

INDEX_TYPE TABLE_NAME INDEX_NAME

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

NORMAL T_SUBJECT SYS_C009508

NORMAL T_STUDENT SYS_C009505

NORMAL T_STUDENT IND_STU_NAME

---------------指令碼----------------not null約束不用處理

Alter index ind_stu_name UNUSABLE;

ALTER trigger stu_insert disable;

ALTER TABLE T_STUDENT disable constraint STU_FK;

ALTER TABLE T_STUDENT disable constraint SUB_PK;

ALTER TABLE T_SUBJECT disable constraint STU_PK;

----資料型別有：索引，約束(主鍵約束，外來鍵約束，唯一性約束，檢查性約束)，觸發器，序列

------用資料泵匯出Dawn使用者的表空間

SQL> conn sys/sys as sysdba;

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

Connected as SYS

SQL> create directory backup as 'e:/RAC_BACKUP';

Directory created

SQL> grant read,write on directory backup to dawn;

Grant succeeded

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS BACKUP e:/RAC_BACKUP

SYS IDR_DIR e:\app\administrator\diag\rdbms\orcl\orcl\ir

SYS AUDIT_DIR /tmp/

SYS DATA_PUMP_DIR E:\app\Administrator\admin\orcl\dpdump\

SYS ORACLE_OCM_CONFIG_DIR E:\app\Administrator\product\11.1.0\db_1\ccr\state

--資料泵匯出

[dawn@localhost~]$expdp dawn/dawn DIRECTORY=backup DUMPFILE=dawn.dmp SCHEMAS=dawn logfile=exp.log version=10.2.0.1.0

注意:oracle下建立好目錄前授權前得先在硬碟上建立好目錄才行，指令碼結束時也不要帶分號,否則會報錯如下:

   連線到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
   with the Partioning,OLAP and Data Mining options
   ORA-39002:操作無效
   ORA-39070:無法開啟日誌檔案。
   ORA-39087:目錄名EMPDIR;無效

RAC環境建立匯入匯出目錄授權:

--RAC節點狀態正常:

SQL> select file_name from dba_data_files;

FILE_NAME

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

+RAC_DISK/racdb/datafile/system.259.839755843

+RAC_DISK/racdb/datafile/undotbs1.260.839755857

+RAC_DISK/racdb/datafile/sysaux.261.839755861

+RAC_DISK/racdb/datafile/undotbs2.263.839755875

+RAC_DISK/racdb/datafile/users.264.839755881

SQL> create tablespace dawn datafile '+RAC_DISK/racdb/datafile/dawn.dbf' size 50m;

Tablespace created.

SQL> create user dawn identified by dawn default tablespace dawn temporary tablespace temp;

User created.

SQL> grant dba to dawn;

Grant succeeded.

SQL> grant connect to dawn;

Grant succeeded.

SQL> grant resource to dawn;

Grant succeeded.

SQL> create directory backup as '/opt/oracle10g/impdp_dir';

Directory created.

SQL> grant read,write on directory backup to dawn;

Grant succeeded.

SQL> select owner,directory_name,directory_path from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS DATA_PUMP_DIR /opt/oracle10g/product/10.2.0/db_1/rdbms/log/

SYS ADMIN_DIR /opt/oracle10g/product/10.2.0/db_1/md/admin

SYS BACKUP /opt/oracle10g/impdp_dir

SYS WORK_DIR /opt/oracle10g/product/10.2.0/db_1/work

--開始匯入:

[oracle@rac1 ~]$ impdp dawn/dawn DIRECTORY=BACKUP DUMPFILE=dawn.dmp SCHEMAS=dawn logfile=impdp.log TABLE_EXISTS_ACTION=REPLACE version=10.2.0.1.0

---成功匯入，不過這裡需要注意下:在高版本匯出的時候指定匯出的版本，比如匯入版本為102010，那麼在11.1上匯出時透過VERSION=10.2.0.1，確保匯出的資料和結構和低版本相容,否則會報錯如下:

還有關於這個錯:

ORA-31684: Object type USER:"DAWN" already exists

我匯入前明確drop user dawn cascade.並且purge recyclebin了。還沒弄明白為何報這樣的錯誤。不過不影響結果

[oracle@rac1 ~]$ impdp dawn/dawn DIRECTORY=BACKUP DUMPFILE=dawn.dmp SCHEMAS=dawn logfile=impdp.log

Import: Release 10.2.0.1.0 - Production on Friday, 21 February, 2014 0:20:17

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, Real Application Clusters, OLAP and Data Mining options

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39142: incompatible version number 2.1 in dump file "/opt/oracle10g/impdp_dir/dawn.dmp"

----------------------------------------RAC雙節點impdp後資料庫資訊圖---------------------------------------
rac1--------&gt

rac2------------&gt

---------------------------------------對觸發器、約束、索引解禁------------------------------------------------

SQL> select t.table_name,t.index_type,t.index_name,t.status from user_indexes t;

TABLE_NAME INDEX_TYPE INDEX_NAME STATUS

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

T_STUDENT NORMAL IND_STU_NAME UNUSABLE

SQL> select ct.table_name,ct.constraint_type,ct.constraint_name,ct.status from user_constraints ct;

TABLE_NAME C CONSTRAINT_NAME STATUS

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

T_SUBJECT C SYS_C004501 DISABLED

T_SUBJECT C SYS_C004502 DISABLED

T_STUDENT C SYS_C004498 DISABLED

T_STUDENT C SYS_C004499 DISABLED

T_STUDENT C SYS_C004500 DISABLED

SQL> select tg.table_name,tg.trigger_type, tg.trigger_name,tg.status from user_triggers tg;

TABLE_NAME TRIGGER_TYPE TRIGGER_NAME STATUS

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

T_STUDENT BEFORE EACH ROW STU_INSERT DISABLED

----索引未enable前插入失敗-----

SQL> select * from t_student;

STU_ID STU_NAME SUBJECT_ID

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

1 Lucy 11

2 Lily 12

3 Tom 13

SQL> insert into t_student(stu_name,subject_id) values('ss',11);

insert into t_student(stu_name,subject_id) values('ss',11)

*

ERROR at line 1:

ORA-01502: index 'DAWN.IND_STU_NAME' or partition of such index is in unusable

State

------對觸發器、約束、索引解禁------

Alter index ind_stu_name rebuild;

alter table t_student enable all triggers;

禁用所有外來鍵約束

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type in ('P','R','C');

ALTER TABLE T_STUDENT enable constraint STU_FK;

啟用所有主鍵約束

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='P';

'ALTERTABLE'||TABLE_NAME||'ENA

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

alter table T_STUDENT enable constraint STU_PK;

alter table T_SUBJECT enable constraint SUB_PK;

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

啟用所有自定義約束

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='C';

'ALTERTABLE'||TABLE_NAME||'ENA

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

這個約束不用處理.

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

啟用所有外來鍵約束

select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

'ALTERTABLE'||TABLE_NAME||'ENA

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

alter table T_STUDENT enable constraint STU_FK;

Type Code	Type Description	Acts On Level
C	Check on a table	Column
O	Read Only on a view	Object
P	Primary Key	Object
R	Referential AKA Foreign Key	Column
U	Unique Key	Column
V	Check Option on a view	Object

---需要注意的是：對於約束的恢復順序為:檢查性約束，唯一性約束，主鍵約束，外來鍵約束

SQL> select tg.table_name,tg.constraint_type,tg.constraint_name,tg.status from user_constraints tg;

TABLE_NAME C CONSTRAINT_NAME STATUS

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

T_SUBJECT C SYS_C004577 ENABLED

T_SUBJECT C SYS_C004578 ENABLED

T_STUDENT C SYS_C004574 ENABLED

T_STUDENT C SYS_C004575 ENABLED

T_STUDENT C SYS_C004576 ENABLED

T_STUDENT R STU_FK ENABLED

T_STUDENT P STU_PK ENABLED

T_SUBJECT P SUB_PK ENABLED

8 rows selected.

-----------------------RAC中其他資料庫物件狀態資訊----------------------------------

------------------------------------------結束-------------------------------------------

單機遷移資料到RAC完整案例

相關文章