虚拟机系统
ORACLE11g R2 expdp impdp 方式 FOR OEL6.5迁移报告
目录
一、产生数据:
使用示例数据。后创建表空间,创建用户,重复插入数据。
1.1检查数据库
首先检查当前数据库的SID 设置。确认需要导入数据库的SID。
确保数据库干净的关闭。
1.1.1 查询当前数据库的版本组件是否有效。
select * from v$version; set lines 90 numwidth 12 pages 10000 long 2000000000 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col version format a12 col comp_id format a8 col schema like version col comp_name format a35 col status format a12 select comp_id,schema,status,version,comp_name from dba_registry order by 1; |
1.1.2 查询当前所有的系统表空间的对象数目。
run queries on dba_objects: connect / as sysdba set lines 80 numwidth 12 pages 10000 long 2000000000 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col version format a12 col comp_id format a8 col schema like version col comp_name format a35 col status format a12 col owner format a25 select owner, count(*) from dba_objects where owner in ('ctxsys', 'olapsys', 'mdsys', 'dmsys', 'wksys', 'lbacsys', 'ordsys', 'xdb', 'exfsys', 'owbsys', 'wmsys', 'sysman') or owner like 'apex%' group by owner order by 1; select owner, object_type, count(*) from dba_objects where object_type like 'java%' group by owner, object_type order by 1,2; |
1.2 检查字符集
源平台和目标平台 set lines 80 numwidth 12 pages 10000 long 2000000000 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; col value format a15 select * from nls_database_parameters where parameter like '%set' order by 1; |
1.3 检查redo 文件
SET lines 140 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL member FORMAT a100 SPOOL redo.out SELECT group#,bytes,blocksize,members,status FROM v$log ORDER BY 1;
SELECT * FROM v$logfile ORDER BY 1,3;
SPOOL off SET lines 140 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL member FORMAT a100
SPOOL redo.out
SELECT group#,bytes,members,status FROM v$log ORDER BY 1;
SELECT * FROM v$logfile ORDER BY 1,3; |
1.4检查表空间信息
CONNECT / as sysdba
SET lines 170 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL file_name FORMAT a100
SPOOL tbsp.out
SELECT tablespace_name, bytes, status, online_status, file_name FROM dba_data_files ORDER BY 1,5;
SET lines 100 COL ddl FORMAT a100 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') "DDL" FROM dual;
-- do this for all tablespaces
SPOOL off
CONNECT / as sysdba
SET lines 170 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL file_name FORMAT a100
SPOOL tbsp.out
SELECT tablespace_name, bytes, status, file_name FROM dba_data_files ORDER BY 1,4;
SET lines 100 COL ddl FORMAT a100 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') "DDL" FROM dual;
-- do this for all tablespaces
SPOOL off |
1.5 检查用户
SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL file_name FORMAT a100
SELECT * FROM v$pwfile_users; |
1.6 数据库支持情况
连接到数据库。查询当前版本是否支持expdp,impdp.否则就改为exp,imp
二、系统调整
2.1.tns连接串
文件到相应对应的目录。
2.2创建表空间。
2.2.1 每个表空间只有一个数据文件
select 'create tablespace '|| tablespace_name|| ' datafile ' || ',' || '+DATA2/oracle/datafile/' || substr(file_name,33,100) || ',' || ' size ' || round(bytes/1024/1024,0) || 'm autoextend on next 1m;' from dba_data_files; |
2.2.2 如果一个表空间下有多个数据文件使用下面的语句进行创建。
select 'create tablespace ' || tablespace_name || ' datafile ' || ' size ' || sum(round(bytes/1024/1024,0)) || 'm autoextend on next 1m;' from dba_data_files group by tablespace_name; |
说明:'+DATA2/oracle/datafile/' || substr(file_name,33,100) 这个根据实际情况来调整。 '+DATA2/oracle/datafile/' 为要导入数据的目录,substr(file_name,33,100) 为源机器截取文件名。然后使用编辑器替换,为';
2.3.删除添加的表空间:
select 'drop tablespace ' || tablespace_name || ';' from dba_data_files where tablespace_name not in ('USERS','UNDOTBS1','SYSAUX','SYSTEM','EXAMPLE'); |
2.4.调整数据文件的大小
select 'alter database datafile ' || ',' || '+data2/oracle/datafile/' || substr(file_name,33,100) || ',' || ' resize '|| round(bytes/1024/1024,0) || 'm;' from dba_data_files; |
注:这个根据实际情况修改。'+data2/oracle/datafile/' || substr(file_name,33,100)
替换,号为单引号。
2.5 打开数据文件的自动扩展
select 'alter database datafile ' || ',' || '+data2/oracle/datafile/' || substr(file_name,33,100) || ',' || ' autoextend on next 1m maxsize 1024m;' from dba_data_files; |
2.6.调整联机日志文件大小和个数
查看联机日志文件的大小和位置。
调整大小需要删除联机日志文件然后再添加。
2.7.如果导入有问题,则可以清除不必要的数据
2.7.1 有没有用户连接上去。
select 'alter system kill session ' || '[' || sid|| ',' || serial# || '[' from ( select saddr,sid,serial#,paddr,username,status from v$session where username is not null and username not in ('SYS')); |
2.7.2 如果有进程连接,运行脚本杀死进程。并关闭监听
2.7.3查找比系统用户晚创建的用户创建的时间,查看系统用户和业务用户
select username,created from dba_users order by created; |
找出非系统的用户。
select username ,created from dba_users where created > to_date('03-sep-13','dd-mon-rr'); |
产生删除用户的语句:
select 'drop user ' || username || ' cascade;' from (select username ,created from dba_users where created > TO_DATE('03-SEP-13','DD-MON-RR')); |
三、创建目标数据库
3.1创建数据库要和源数据库相同字符集。
3.2 对于undo 管理段 管理方式为自动或者手动。
四、导出数据准备
4.1 启停数据库
shutdown immeidate startup |
4.2 停止监听对于源数据库库,避免用户连接上来。
4.3 显示系统对象
CONNECT / as sysdba
SET lines 100 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL status FORMAT a9 COL object_type FORMAT a20; COL owner.object FORMAT a50
SPOOL obj_source.out
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT" FROM dba_objects WHERE status != 'VALID' AND object_name NOT LIKE 'BIN$%' ORDER BY 4,2;
COL owner FORMAT a30 SELECT owner, object_type, status, count(*) FROM dba_objects WHERE object_name NOT LIKE 'BIN$%' GROUP BY owner, object_type, status ORDER BY 1,2,3;
SPOOL off |
4.4 由于sys 用户的对象不能导出,查看当前sys的对象。
CONNECT / as sysdba SET lines 180 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL triggering_event FORMAT a35 SPOOL trig.out SELECT owner, trigger_name, trigger_type, triggering_event, table_owner, base_object_type, status FROM dba_triggers WHERE owner='SYS' ORDER BY 2; SET lines 100 COL ddl FORMAT a100 SELECT dbms_metadata.get_ddl('TRIGGER','LOGMNRGGC_TRIGGER','SYS') "DDL" FROM dual;
-- do this for all manually created triggers in the SYS schema SPOOL off |
4.5 产生脚本赋予某些对象给sys用户权限的。
CONNECT / as sysdba SET lines 80 NUMWIDTH 12 PAGES 10000 LONG 2000000000 ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS'; COL grants FOR a80 SPOOL sysgrants.out -- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx -- Add below the users and/or roles as appropriate for GRANTEE -- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx SELECT 'GRANT ' || privilege || ' ON ' || table_name || ' TO ' || grantee || ';' "GRANTS" FROM dba_tab_privs WHERE owner = 'SYS' AND privilege NOT IN ('READ', 'WRITE') AND grantee IN ('TC') ORDER BY 1;
SPOOL OFF |
五、导出数据
5.1 对于10G 以下的系统
exp system/oracle file=exp_f1.dmp,exp_f2,dmp,exp_f3.dmp log=exp_f.log filesize=10G full=y consistent=y compress=y |
5.2 对于11 G
expdp \" sys\oracle as sysdba\" DIRECTORY=dump_test DUMPFILE=expdp_f%U.dmp LOGFILE=expdp_f.log FILESIZE=10g FULL=y PARALLEL=8 FLASHBACK_TIME=systimestamp |
六、导入数据
6.1 copy dump文件到目标端机。
6.2 10G使用imp 导入数据文件。
imp system/oracle FILE=exp_f1.dmp,exp_f2.dmp,exp_f3.dmp LOG=imp_f.log FILESIZE=10g FULL=y |
6.3 11G使用impdp 导入数据文件。
impdp \"sys/123 as sysdba\" DIRECTORY=dump_test DUMPFILE=expdp_f%U.dmp LOGFILE=impdp_f.log FULL=y PARALLEL=8 |
七、译无效对象
spool invalid_pre.lst select substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type, status from dba_objects where status <> 'VALID'; spool off $ sqlplus '/as sysdba' SQL> @?/rdbms/admin/utlrp.sql |
八、关闭数据库启动数据库。启动监听。
由应用开发人员检查是否数据库是否正确。
附:RAC 的一些命令
一、查看RAC 运行状态。
1. 完整的关闭数据库的顺序
关闭EM emctl stop dbconsole(在所有节点执行)
关闭数据库实例 srvctl stop instance -d oracle -i oracle1 srvctl stop instance -d oracle -i oracle2
关闭节点应用 srvctl stop nodeapps -n oracle1 srvctl stop nodeapps -n oracle2
关闭ASM实例 srvctl stop asm -n rac1 srvctl stop asm -n rac2
关闭crs服务 crsctl stop crs (root 用户,在所有节点执行) |
二、 完整的启动数据库的顺序
启动crs crsctl start crs(root 用户,在所有节点执行)
启动节点应用(比如监听等) srvctl start nodeapps -n oracle1 srvctl start nodeapps -n oracle2
启动数据库 srvctl start instance -d oracle -i oracle1 srvctl start instance -d oracle -i oracle2
或者 srvctl start database -d oracle
启动EM emctl start dbconsole crsctl stat res -t |