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

東北胖子發表於2014-02-24

資料遷移要求:

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

   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

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

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中其他資料庫物件狀態資訊----------------------------------

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

 

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1085889/，如需轉載，請註明出處，否則將追究法律責任。

相關文章