单机迁移数据到RAC完整案例

最新推荐文章于 2024-07-26 12:52:45 发布

conglueh193642

最新推荐文章于 2024-07-26 12:52:45 发布

阅读量332

点赞数

文章标签：数据库数据结构与算法

数据迁移要求:

表结构要同步，存储过程、函数、同义词是、视图要、序列更要同步

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

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