数据库exp&imp迁移的整个过程，及注意事项

本例用由9i迁移到11G

 

1.Oracle9i数据库

sys@ORCL> alter database open read only;

sys@ORCL> alter database open read write;

sys@ORCL>alter system set job_queue_processes=0;

sys@ORCL>alter system set aq_tm_processes=0;

----正式迁移数据库时，最好停止job及队列监视器，将数据库置于read only以保持数据库一致性

 

1.1 记录所有用户及用户默认表空间

set pagesize 999

col username for a20

col default_tablespace for a20

select username,default_tablespace,temporary_tablespace

from dba_users;

 

1.2 记录用户所使用的表空间

select distinct owner,tablespace_name from dba_extents order by owner;

 

1.3 记录所有表空间使用大小

Select a.Tablespace_Name "ts_name",

       Round(a.Bytes / 1024 / 1024 ) "total(M)",

              Round(b.Bytes / 1024 / 1024 ) "free(M)",

       Round(((a.Bytes - b.Bytes) / a.Bytes) * 100, 2)||'%' "used_percent"

  From (Select Tablespace_Name, Sum(Bytes) Bytes

          From Dba_Data_Files

         Group By Tablespace_Name) a,

       (Select Tablespace_Name, Sum(Bytes) Bytes, Max(Bytes) Largest

          From Dba_Free_Space

         Group By Tablespace_Name) b

 Where a.Tablespace_Name = b.Tablespace_Name

   And Round(((a.Bytes - b.Bytes) / a.Bytes) * 100, 2) >= 0

   Union All

   SELECT a.tablespace_name "ts_name",

          a.BYTES/ 1024 / 1024 "total(M)",

          (a.bytes - nvl(b.bytes, 0))/ 1024 / 1024 "free(M)",

          ROUND(nvl(b.bytes, 0)/a.BYTES*100,2) || '%' "used_percent"

  FROM (SELECT tablespace_name, SUM (bytes) bytes FROM dba_temp_files GROUP BY tablespace_name) a,

       (SELECT tablespace_name, SUM (bytes_cached) bytes FROM v$temp_extent_pool GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name(+)

  And a.tablespace_name like 'TEMP%'

  And ROUND(nvl(b.bytes, 0)/a.BYTES*100,2) >= 0;

 

 

1.4 记录表空间对应的数据文件

col file_name for a50

col tablespace_name for a20

set pagesize 999

select file_name,tablespace_name from dba_data_files;

 

1.5 记录用户表所在表空间

col owner for a10

select owner,table_name,tablespace_name from dba_tables where owner='SCOTT';

 

1.6 生成9i导出脚本

set linesize 999

set pagesize 999

select 'exp \''/ as sysdba\'' buffer=65535 file=/oracle_exp/expdata/'||username||'.dmp log=/oracle_exp/explog/'||username||'.log'  from dba_users where username in ('SCOTT','HR','SH','OE');

 

2.Oracle11g数据库上操作

sys@ORCL> alter database open read only;

sys@ORCL> alter database open read write;

sys@ORCL>alter system set job_queue_processes=0;

sys@ORCL>alter system set aq_tm_processes=0;

----正式迁移数据库时，最好停止job及队列监视器，将数据库置于read only以保持数据库一致性

 

2.1 在9i中生成创建表空间脚本

select 'create tablespace "'||tablespace_name||'" datafile ''/data_01/oradata/qianlong/'||tablespace_name||'01.dbf'' size 20g;' from dba_tablespaces;

 

2.2 在9i中生成创建用户脚本

select 'create user "'||username||'" identified by "'||username||'" default tablespace "'||default_tablespace||'";'

 from dba_users

 order by default_tablespace;

 

2.3 在9i中生成批量授权

select 'grant connect,resource to "'||username||'";'

    from dba_users;

 

2.4 在9i中生成导入脚本

select 'imp \''/ as sysdba\'' buffer=65535 commit=y file=/oracle_exp/expdata/'||username||'.dmp log=/oracle_exp/implog/'||username||'.log fromuser='||username||' touser='||username from dba_users where  username in ('SCOTT','HR','SH','OE');

 

 

3.迁移完成后注意事项

3.1 处理失效对象

3.1.1 检查失效对象

col owner for a10

col object_type for a15

col object_name for a30

col status for a20

 

select owner,object_type,object_name,status

                from dba_objects

                where status<>'VALID'

                order by owner,object_type,object_name;

 

3.1.2 如何编译失效对象

1> 使用Oracle官方建议：

sys@ORCL>@?/rdbms/admin/utlrp.sql

 

2> 使用UTL_RECOMP编译

——schema level

exec utl_recomp.recomp_parallel('4','USERNAME');

 

——database level

exec utl_recomp.recomp_parallel(4);

 

3> 手动完成编译

编译程序包（规范）

SQL> alter package scott.pk_name compile;

编译程序包（主）体

SQL> alter package scott.pk_name compile body;

编译存储过程

SQL> alter procedure scott.p_name compile;

编译函数

SQL> alter function scott.f_name compile;

编译触发器

SQL> alter trigger scott.t_name compile;

编译视图

SQL> alter view scott.v_name compile;

 

3.1.3 对比9i和11g不一致的失效对象

----如果失效对象过多，可以通过脚本对比失效对象

----9i

create table no_object as

select owner,object_type,object_name,status

  from dba_objects

  where status<>'VALID';

 

SQL> select count(*) from no_object;

exp \'/ as sysdba\' tables=no_object file=no_object.dmp

SQL> drop table no_object;

 

----11g

imp \'/ as sysdba\' tables=no_object file=no_object.dmp fromuser=sys touser=sys

SQL> select count(*) from no_object;

 

create table g_object as

select owner,object_type,object_name,status

  from dba_objects

  where status<>'VALID';

 

SQL> select count(*) from g_object;

 

----对比

--11g中失效而9i中没有失效的

select * from g_object

where owner not in ('SYS','APEX_030200','QS','QS_ADM','QS_CBADM','QS_ES','QS_OS','QS_WS') minus

select * from no_object

where owner not in ('SYS', 'WKSYS','CTXSYS','APEX_030200','QS','QS_ADM','QS_CBADM','QS_ES','QS_OS','QS_WS');

 

--9i中失效而11g中没有失效的

select * from no_object

where owner not in ('SYS', 'WKSYS','CTXSYS','APEX_030200','QS','QS_ADM','QS_CBADM','QS_ES','QS_OS','QS_WS') minus

select * from g_object

where owner not in ('SYS','APEX_030200','QS','QS_ADM','QS_CBADM','QS_ES','QS_OS','QS_WS');

 

3.1.4 对处理失效对象建议

    有时导入新库后，失效对象无法完成编译，应该采用手动编译方式，然后show err查看报错。另外需要考虑下该用户权限是否和旧库一致。

 

----检查9i、11g用户权限

select * from dba_sys_privs where grantee='SCOTT';

select * from dba_role_privs where grantee='SCOTT';

 

----是否需要授予UTL_SMTP权限

----授予权限(直接授权public是不安全的，建议授权给需要的用户)

SQL> grant execute on utl_file to public;

SQL> grant execute on dbms_random to public;

SQL> grant execute on utl_http to public;

SQL> grant execute on utl_tcp to public;

SQL> grant execute on UTL_SMTP to public;

 

----权限分配完成后，编译一下失效对象

SQL> exec utl_recomp.recomp_parallel(4);

 

3.1.5 一些错误代码处理

----还有失效对象就采用手工编译，show err 查看报错，然后打开该对象，定位到报错行

 

1> ORA-01031

----PL/SQL: ORA-01031: insufficient privileges（权限不足）

grant update,insert,delete on shall.Emp_Scott to scott;

 

2> PLS-00904 ORA-02225

----PLS-00904: insufficient privilege to access object SHALL.PK_Emp（权限不足）

grant select,insert,delete,update on SHALL.PK_Emp to scott;

ERROR at line 1:

ORA-02225: only EXECUTE and DEBUG privileges are valid for procedures

grant execute,debug on SHALL.PK_Emp to hct;

 

3> ORA-00918

----PL/SQL: ORA-00918: column ambiguously defined（一般是多表连接时列指定不明确）

修改该对象：仔细检查报错行，是否是某列没有跟表名

如：

select  id,d.name  from emp e dept d where e.dept_id=d.id;

===>select  e.id,d.name  from emp e dept d where e.dept_id=d.id;

 

4> PLS-00201 ORA-00942

----PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared

grant execute on SYS.DBMS_SYSTEM to scott;    

 

SQL> alter package scott.p_name compile body;

1014/7   PL/SQL: SQL Statement ignored

1016/14  PL/SQL: ORA-00942: table or view does not exist(定位到对象该行)

grant select on sys.v_$parameter to scott;

grant select on sys.v_$shared_pool_reserved to scott;

 

PLS-00201: identifier 'SYS.SEND_MAIL' must be declared（定位到对象该行）

grant execute on sys.utl_smtp to scott;

grant execute on sys.utl_encode to scott;

grant execute on sys.utl_raw to scott;

 

3.2 处理Job属组问题

    因为oracle数据库用户很多,统一用sys用户进行导入/导出后，会导致普通用户的job作业停止工作

select * from dba_jobs;

    所有普通用户job的LOG_USER和PRIV_USER字段都会变成sys,而SCHEMA_USER还是原来的用户的schema名字。这是由于imp导入用户与job的属主用户不同造成的。

    解决方法之一用job属主用户进行导入，但是已经导入数据了，删除重新导入比较麻烦，所以使用第二种方法，以sysdba角色登录，修改两个字段LOG_USER和PRIV_USER的值为SCHEMA_USER字段的值

 

3.1 查看JOB属组

col log_user for a20

 col priv_user for a20

 set pagesize 999

 select log_user,priv_user,schema_user from dba_jobs;

 

3.2 修改属组

select 'update dba_jobs set log_user='''||log_user||''',priv_user='''||priv_user||''' where schema_user='''||schema_user||''';'

 from dba_jobs;

----一条一条的执行，执行完成确认无误后，记得提交 commit;

 

3.3 修改broken 'N'，并手动执行一次Job

select * from dba_jobs where broken='Y';

 

----方法一：

----生成执行脚本：

select 'exec dbms_job.broken('''||job||''',false);' from dba_jobs

    where broken='Y' and schema_user='SCOTT';

select 'exec dbms_job.run('''||job||''');' from dba_jobs

    where broken='Y' and schema_user='SCOTT'; 

 

----用户连接后执行生成脚本

conn scott/scott

 

----方法二：

----使用Job用户登录

conn scott/scott

 

begin

  for i in (select job from user_jobs where broken = 'Y') loop

    dbms_job.broken(i.job, false);

    dbms_job.run(i.job, true);

  end loop;

end;

/

 

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2121871/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2121871/