网站空间期限查询,做视频网站了几百万,做网站找外包公司要要搞清楚什么,广州注册公司多少钱☘️博主介绍☘️#xff1a; ✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux#xff0c;也在扩展大数据方向的知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章#xff0c;并且也会默默的点赞收藏加关注❣️❣️… ☘️博主介绍☘️ ✨又是一天没白过我是奈斯DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux也在扩展大数据方向的知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注❣️❣️❣️ 今天作为expdp/impdp数据泵的最后一篇文章这篇文章所讲的技能也是非常重要的。在Oracle数据库中新建实例往往伴随着一系列系统用户的自动生成如sys、system、scott等。然而在进行 全库fully导入 时若这些系统用户也被 一并导入 往往会引发大量的ORA-错误这样通过日志排查问题时就会有许多干扰。因此在进行全库迁移时我们必须排除这些系统用户的导入。接下来就让我们深入今天的主题探讨如何通过expdp/impdp实现高效且精准的全库迁移。 关于逻辑迁移工具全部的篇幅介绍四篇的内容分别如下
第一篇一文搞清exp/imp逻辑迁移工具的用法第二篇一文搞清expdp/impdp逻辑迁移工具的用法第三篇expdp/impdp轻松完成某个生产用户从GBK到UTF8编码的迁移第四篇expdp/impdp高效完成全部生产用户的全库迁移当前篇 目录
expdp全库导出实例orcl单机环境部分
impdp全库导入实例test单机环境部分
验证数据部分 expdp/impdp全库导入的应用场景 1、迁移字符集。比如将全库用户从GBK字符集迁移到UTF8编码。 2、大版本升级。可以通过全库导入完成大版本升级。 3、测试环境恢复。通过全量备份完成测试环境的数据导入。 expdp/impdp全库导入注意事项 1通过impdp导入时指定sqlfile参数可以显示执行的DDL语句。加上这个参数后就不会进行数据导入了只会根据导入的dmp文件将dmp文件涉及到的DDL语句输出到sqlfile指定的文件中然后筛选出来业务表空间进行手动创建。 2排除导入的系统用户因为系统用户是按照实例默认创建的所以导入时需要排除。在每个大版本中系统用户都不一样所以需要根据具体的大版本而排除。 Oracle11g和19c环境下的默认系统用户 Oracle 11g单机环境下的系统用户新建了一个11g的实例可以看到默认11g的实例有30个系统用户所以在导入时就要排除它们。 SQL select username,to_char(CREATED,yyyy-mm-dd hh24:mi:ss) from dba_users order by CREATED desc;USERNAME TO_CHAR(CREATED,YYYY-MM-DDHH24:MI:SS)
------------------------------- -------------------------------------------------------------
SCOTT 2013-08-24 12:04:21
OWBSYS_AUDIT 2013-08-24 12:03:41
OWBSYS 2013-08-24 12:03:40
APEX_PUBLIC_USER 2013-08-24 11:58:27
FLOWS_FILES 2013-08-24 11:58:27
APEX_030200 2013-08-24 11:58:27
MGMT_VIEW 2013-08-24 11:57:53
SYSMAN 2013-08-24 11:56:19
SPATIAL_CSW_ADMIN_USR 2013-08-24 11:56:11
SPATIAL_WFS_ADMIN_USR 2013-08-24 11:56:08
MDDATA 2013-08-24 11:53:28
OLAPSYS 2013-08-24 11:52:40
ORDDATA 2013-08-24 11:49:40
ORDSYS 2013-08-24 11:49:40
MDSYS 2013-08-24 11:49:40
ORDPLUGINS 2013-08-24 11:49:40
SI_INFORMTN_SCHEMA 2013-08-24 11:49:40
XS$NULL 2013-08-24 11:49:29
ANONYMOUS 2013-08-24 11:47:55
XDB 2013-08-24 11:47:55
CTXSYS 2013-08-24 11:47:37
EXFSYS 2013-08-24 11:47:27
WMSYS 2013-08-24 11:43:26
APPQOSSYS 2013-08-24 11:43:00
DBSNMP 2013-08-24 11:42:59
ORACLE_OCM 2013-08-24 11:39:34
DIP 2013-08-24 11:38:58
OUTLN 2013-08-24 11:37:43
SYS 2013-08-24 11:37:40
SYSTEM 2013-08-24 11:37:4030 rows selected. Oracle 19c单机环境下的系统用户新建了一个19c的实例可以看到默认19c的实例有36个系统用户所以在导入时就要排除它们。 SQL select username,to_char(CREATED,yyyy-mm-dd hh24:mi:ss) from dba_users order by CREATED desc;USERNAME TO_CHAR(CREATED,YY
--------------------------------- ----------------------------------------------------------------------------------
DVF 2019-04-17 02:02:47
DVSYS 2019-04-17 02:02:47
LBACSYS 2019-04-17 02:02:15
MDDATA 2019-04-17 01:53:39
OLAPSYS 2019-04-17 01:52:56
SI_INFORMTN_SCHEMA 2019-04-17 01:48:37
ORDDATA 2019-04-17 01:48:37
ORDPLUGINS 2019-04-17 01:48:37
MDSYS 2019-04-17 01:48:37
ORDSYS 2019-04-17 01:48:36
CTXSYS 2019-04-17 01:46:56
OJVMSYS 2019-04-17 01:42:57
WMSYS 2019-04-17 01:40:04
ANONYMOUS 2019-04-17 01:16:53
XDB 2019-04-17 01:16:53
GGSYS 2019-04-17 01:15:01
GSMCATUSER 2019-04-17 01:14:57
APPQOSSYS 2019-04-17 01:14:37
DBSNMP 2019-04-17 01:14:35
SYS$UMF 2019-04-17 01:10:05
ORACLE_OCM 2019-04-17 01:04:04
DBSFWUSER 2019-04-17 01:02:55
REMOTE_SCHEDULER_AGENT 2019-04-17 01:02:54
XS$NULL 2019-04-17 01:02:44
DIP 2019-04-17 01:02:17
GSMROOTUSER 2019-04-17 01:02:03
GSMADMIN_INTERNAL 2019-04-17 01:02:02
GSMUSER 2019-04-17 01:02:02
OUTLN 2019-04-17 00:56:39
SYSKM 2019-04-17 00:56:33
AUDSYS 2019-04-17 00:56:33
SYSRAC 2019-04-17 00:56:33
SYSBACKUP 2019-04-17 00:56:33
SYSTEM 2019-04-17 00:56:33
SYSDG 2019-04-17 00:56:33
SYS 2019-04-17 00:56:3236 rows selected.ps注意单机环境和rac环境是不同的所以rac的实例系统用户和单机的实例系统用户是有可能不一样的这里我就不演示了。rac环境搭建后之后通过上面的SQL去查看默认的系统用户有哪些那么就要在导入的时候排除。 expdp全库导出实例orcl单机环境部分
1、创建数据泵的dmp文件存放目录
[root11g ~]# mkdir /liu
[root11g ~]# chown oracle:oinstall /liu
###文件liu路径/liu在/dev/sdb3下挂载将文件的所属用户和目录改为oracleoinstall SYSorcl create directory BACKUP20200328 as /liu;
SYSorcl grant all on directory BACKUP20200328 to system ;
###创建数据泵的转储路径在使用expdp时指定到liu目录时数据文件就会生成在/liu路径下。赋予给所有用户目录liu的所有执行权限为了以后普通用户使用expdp时有权限将dmp数据文件导入到/liu下。
2、全库fully导出数据
[oracle11g ~]# expdp \/ as sysdba\ directoryBACKUP20200328 dumpfileexpdp_orcl_full_%U.dmp logfileexpdp_full.log fully parallel2 impdp全库导入实例test单机环境部分
1、全库用户导入前需要注意的事情 1关注归档目录定时进行删除避免空间耗尽。可以考虑先关闭归档 2全部数据量有500G以上导入过程中undo和temp占用很多适当扩容。 Undo表空间给7个总大小210G并且减少undo_retention为300秒 Temp表空间给3个总大小90G 3数据文件看情况增加 4expdp/impdp导入导出优化。【Oracle篇】一文搞清expdp/impdp逻辑迁移工具的用法第二篇总共四篇-CSDN博客 2、在服务器上创建一个新实例test 实例静默安装 [oracle11g ~]$ vi /home/db/oracle/product/12.2/db_1/response/dbca.rspsid实例名
gdbName全局数据库名
sysPassword密码
systemPassword密码
dbsnmpPassword密码
datafileDestination/home/db/oracle/oradata ---数据库文件路径
recoveryAreaDestination/home/db/oracle/fast_recovery_area
characterSet AL32UTF8
totalMemoryAMM内存大小 ---单位M
automaticMemoryManagementTRUE ---是否启用自动内存管理
templateNameGeneral_Purpose.dbc ---数据库模板必填
storageTypeFS ---指定要创建的数据库的存储类型有FS和ASM默认FS文件系统[oraclerac1 ~]$ dbca -silent -createDatabase -responseFile /home/db/oracle/product/12.2/db_1/response/dbca.rsp 调整新实例的数据文件、undo、temp、redo初始大小 数据库文件和undo
SQL set linesize 500 set pagesize 99col file_name for a70 col file_id for 9999999 col status for a10 col ts_name for a25 col cur_mb for 99999 col max_mb for 99999
SQL select status, file_id, file_name, tablespace_name ts_name,blocks/128 tolal_mb, maxblocks/128 max_mb,AUTOEXTENSIBLE from dba_data_files order by file_name;SQL alter database datafile 2 resize 20g;
SQL alter database datafile 1 resize 20g;
SQL alter database datafile 3 resize 20g;
SQL alter database datafile 5 resize 20g;
SQL alter database datafile 4 resize 10g;temp临时表空间:
SQL select username,temporary_tablespace from dba_users;
SQL set linesize 230col file_name for a65
SQL select FILE_ID,FILE_NAME,TABLESPACE_NAME,bytes/1024/1024 tolal_mb,status,AUTOEXTENSIBLE,MAXBYTES/1024/1024 max_mb from dba_temp_files;SQL alter database tempfile 1 resize 20g;redo重做日志:生产建议3到6组每组大小为1G左右
SQL set linesize 230col member for a50
SQL select * from v$logfile;
SQL select * from v$log;SQL alter database add logfile thread 1 group 10 (ARCHDG) size 1024m;
SQL alter database add logfile thread 1 group 11 (ARCHDG) size 1024m;
SQL alter database add logfile thread 1 group 12 (ARCHDG) size 1024m;
SQL alter database add logfile thread 1 group 13 (ARCHDG) size 1024m;
SQL alter database add logfile thread 2 group 14 (ARCHDG) size 1024m;
SQL alter database add logfile thread 2 group 15 (ARCHDG) size 1024m;
SQL alter database add logfile thread 2 group 16 (ARCHDG) size 1024m;SQL alter system switch logfile;
SQL alter system checkpoint;SQL alter database drop logfile group 1;
SQL alter database drop logfile group 2;
SQL alter database drop logfile group 3;
SQL alter database drop logfile group 4;关闭密码过期默认180天过期: SQL select * from dba_profiles where profileDEFAULT;SQL alter profile default limit PASSWORD_LIFE_TIME unlimited;
SQL alter profile default limit PASSWORD_LOCK_TIME unlimited;
SQL alter profile default limit PASSWORD_GRACE_TIME unlimited; 关闭审计长时间开启审计审计占用空间较大 SQL alter system set audit_trailnone scopespfile; 设置归档路径 SQL shutdown immediate
SQL startup mount SQL alter database archivelog;
SQL alter system set log_archive_dest_1locationASM磁盘;SQL alter database open;
SQL archive log list;
SQL show parameter recover; db实例参数优化 SQL
alter system set sga_max_size物理内存x0.5 scopespfile sid*;
alter system set sga_target物理内存x0.5 scopespfile sid*;
alter system set pga_aggregate_target物理内存x0.2 scopespfile sid*; ---设置为ASMM
alter system set _cursor_obsolete_threshold400 scopespfile sid*;
alter system set _rollback_segment_count 2000 scopespfile sid*;
alter system set _ktb_debug_flags8 sid*;
alter system set _datafile_write_errors_crash_instancefalse scopespfile sid*;
alter system set events 43822 trace name context forever,level 1;
alter system set enable_ddl_loggingtrue sid* scopespfile;
alter system set fast_start_parallel_rollbacklow sid*;
alter system set recovery_parallelism10 scopespfile sid*;
alter system set _cleanup_rollback_entries10000 scopespfile sid*;
alter system set _gc_bypass_readers false sid * scopespfile;
alter system set _gc_override_force_cr false sid * scopespfile;
alter system set _gc_read_mostly_locking false sid * scopespfile;
alter system set _serial_direct_read false sid * ;
alter system set _high_priority_processes LGWR|LMS*|VKTM sid* scopespfile;
alter system set _use_adaptive_log_file_syncfalse sid* scopespfile;
alter system set _px_use_large_pooltrue sid * scopespfile;
alter system set event28401 trace name context forever,level 1,10949 trace name context forever,level 1,60025 trace name context forever,level 1, 10503 trace name context forever,level 128 sid* scopespfile;
alter system set _optimizer_use_feedbackfalse sid * scopespfile;
alter system set _optimizer_adaptive_cursor_sharingfalse sid* scopespfile;
alter system set _optimizer_extended_cursor_sharingnone sid* scopespfile;
alter system set _optimizer_extended_cursor_sharing_relnone sid* scopespfile;
alter system set _gc_defer_time3 scopespfile sid*;
alter system set result_cache_max_size0 scopespfile sid*;
alter system set _gc_policy_time0 scopespfile sid*;
alter system set _gc_undo_affinityfalse scopespfile sid*;
alter system set control_file_record_keep_time31 scopespfile sid*;
alter system set parallel_max_servers128 scopespfile sid*;
alter system set parallel_adaptive_multi_userfalse sid*;
alter system set _undo_autotunefalse scopespfile sid*;
alter system set undo_retention10800 scopespfile sid*;
alter system set _partition_large_extentsfalse scopespfile sid*;
alter system set parallel_force_localtrue scopespfile sid*;
alter system set processes2000 scopespfile sid*; ---进程数。设置完成后session和transactions参数会自动调整session1.5*processes22 transactions1.1*sessions
alter system set open_cursors2000 scopespfile sid*; ---打开游标数。一个sql语句就会对应到一个游标简单理解游标就是一个sql语句
alter system set session_cached_cursors300 scopespfile sid*; ---会话缓存游标数sql语句。一个会话缓存多个游标sql语句一个sql对应一个游标缓存游标是为了避免软解析相同的sql再次执行就不需要打开游标了执行sql就会打开游标避免打开游标就是避免软解析绑定变量解决硬解析。
alter system set db_files8192 scopespfile sid*; ---允许创建的数据文件总数 3、确认字符集调整undo保留时间
[oracle11g ~]# export ORACLE_SIDtest
[oracle11g ~]# echo $ORACLE_SIDSQL alter system set undo_retention300 scopeboth sid*; --导入完成之后修改回来
4、创建存放DMP文件夹的引用地址
SQL create or replace directory baj_dir as /data3/data4/backup/db160;
SQL grant all on directory baj_dir to system;
5、通过备份的dmp文件生成表空间创建的DDL语句 sqlfile导入时可以显示执行的DDL语句。加上这个参数后就不会进行数据导入了只会根据导入的dmp文件将dmp文件涉及到的DDL语句输出到sqlfile指定的文件中然后筛选出来业务表空间进行手动创建。 [oracle11g ~]# impdp \/ as sysdba\ directorybaj_dir dumpfileexpdp_orcl_full_01.dmp,expdp_orcl_full_02.dmp logfileimp.txt sqlfileddl_dump.txt[oracle11g ~]# more ddl_dump.txt
注将ddl_dump.txt文件中创建表空间的DDL语句整理到其他文本中然后在sqlplus中执行排查掉SYSAUX、SYSTEM、TEMP、UNDOTBS1、USERS这些系统表空间的创建。 6、执行创建表空间语句 总共涉及到43个表空间的创建其中42个业务表空间还包括了一个临时表空间CARRECOVER_TEMP的创建将表空间的原路径DATA/core/datafile修改为/data3/oradata/recover/recover/路径
SQL
CREATE TABLESPACE TBLSPACE_PRP DATAFILE /data3/oradata/recover/recover/prp.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_VISA DATAFILE /data3/oradata/recover/recover/visa.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_CLAIM DATAFILE /data3/oradata/recover/recover/claim.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_PRPIDX DATAFILE /data3/oradata/recover/recover/prpidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_PAYMENTIDX DATAFILE /data3/oradata/recover/recover/paymentidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_CLAIMIDX DATAFILE /data3/oradata/recover/recover/claimidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE WLREPORT_DATA DATAFILE /data3/oradata/recover/recover/data.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_DEFAULT DATAFILE /data3/oradata/recover/recover/default.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_ALLIDX DATAFILE /data3/oradata/recover/recover/allidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_CTUSER DATAFILE /data3/oradata/recover/recover/ctuser.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_VISAIDX DATAFILE /data3/oradata/recover/recover/visaidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE WLREPORT_IDX DATAFILE /data3/oradata/recover/recover/idx.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 32767MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_MGR DATAFILE /data3/oradata/recover/recover/mgr.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE JY DATAFILE /data3/oradata/recover/recover/jy.dbf SIZE 2G,/data3/oradata/recover/recover/jy_01.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE INTFTBS DATAFILE /data3/oradata/recover/recover/intftbs.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_REINS DATAFILE /data3/oradata/recover/recover/reins.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_CW DATAFILE /data3/oradata/recover/recover/cw.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE OPENCMS DATAFILE /data3/oradata/recover/recover/opencms.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_JY DATAFILE /data3/oradata/recover/recover/jy2.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE UMTBS DATAFILE /data3/oradata/recover/recover/umtbs.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE INTRAWEB DATAFILE /data3/oradata/recover/recover/intraweb.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE OPRTBS DATAFILE /data3/oradata/recover/recover/oprtbs.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_CIRC DATAFILE /data3/oradata/recover/recover/circ.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_OTHIDX DATAFILE /data3/oradata/recover/recover/othidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_OTH DATAFILE /data1/ora_pmon_recover_oradata/oth.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth_01.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth_02.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth3.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth4.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth5.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth6.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth7.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth8.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth14.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth9.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth10.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth11.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth12.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth13.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth15.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth16.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth17.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth18.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth19.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth20.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth21.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth22.dbf SIZE 1M autoextend on,/data1/ora_pmon_recover_oradata/oth23.dbf SIZE 1M autoextend on;
CREATE TABLESPACE TBLSPACE_RESERVE DATAFILE /data3/oradata/recover/recover/reserve.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE ZCDATA DATAFILE /data3/oradata/recover/recover/zcdata.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_REINSIDX DATAFILE /data3/oradata/recover/recover/reinsidx.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_PAYMENT DATAFILE /data3/oradata/recover/recover/payment.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_OTH02 DATAFILE /data3/oradata/recover/recover/TBLSPACE_OTH02.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TABLESPACE_GB DATAFILE /data3/oradata/recover/recover/gb01.dbf SIZE 2GLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE CEDET_BAOXIAN DATAFILE /data3/oradata/recover/recover/cedet_baoxian.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE FXQ DATAFILE /data3/oradata/recover/recover/fxq.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE HZ_UCIA DATAFILE /data3/oradata/recover/recover/ucia.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE OGG DATAFILE /data3/oradata/recover/recover/ogg.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE HZ_SJZL DATAFILE /data3/oradata/recover/recover/sjzl.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE CEDET_BAOXIAN_NEW DATAFILE /data3/oradata/recover/recover/cedet_baoxian_new.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE TABLESPACE_HZHIC DATAFILE /data3/oradata/recover/recover/hzhic.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE HZ_LITIGATION DATAFILE /data3/oradata/recover/recover/litigation.dbf SIZE 2GAUTOEXTEND ON NEXT 8192 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBS_DBBAK DATAFILE /data3/oradata/recover/recover/TBS_DBBAK.dbf SIZE 2GAUTOEXTEND ON NEXT 104857600 MAXSIZE 32767MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE GGATE DATAFILE /data3/oradata/recover/recover/GGATE.dbf SIZE 2GAUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TBLSPACE_CARRECOVER DATAFILE /data3/oradata/recover/recover/TBLSPACE_CARRECOVER.dbf SIZE 2GAUTOEXTEND ON NEXT 104857600 MAXSIZE 32767MLOGGING ONLINE PERMANENT BLOCKSIZE 8192EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE CARRECOVER_TEMP TEMPFILE /data3/oradata/recover/recover/CARRECOVER_TEMP.dbf SIZE 2GAUTOEXTEND ON NEXT 104857600 MAXSIZE 32767MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
7、执行impdp导入命令 排除导入的系统用户因为系统用户是按照实例默认创建的所以导入时需要排除。在每个大版本中系统用户都不一样所以需要根据具体的大版本而排除。
排除导入的系统用户11g的系统用户涉及到30个将所有的用户编辑到一个文件中在导入时指定这个文件那么就是排除这些用户的导入了
[oracle11g ~]$ vi /data3/data4/backup/db160/exclude.par
EXCLUDESCHEMA:SYS
EXCLUDESCHEMA:SYSTEM
EXCLUDESCHEMA:OUTLN
EXCLUDESCHEMA:MGMT_VIEW
EXCLUDESCHEMA:FLOWS_FILES
EXCLUDESCHEMA:MDSYS
EXCLUDESCHEMA:ORDSYS
EXCLUDESCHEMA:EXFSYS
EXCLUDESCHEMA:DBSNMP
EXCLUDESCHEMA:WMSYS
EXCLUDESCHEMA:APPQOSSYS
EXCLUDESCHEMA:APEX_030200
EXCLUDESCHEMA:OWBSYS_AUDIT
EXCLUDESCHEMA:ORDDATA
EXCLUDESCHEMA:CTXSYS
EXCLUDESCHEMA:ANONYMOUS
EXCLUDESCHEMA:SYSMAN
EXCLUDESCHEMA:XDB
EXCLUDESCHEMA:ORDPLUGINS
EXCLUDESCHEMA:OWBSYS
EXCLUDESCHEMA:SI_INFORMTN_SCHEMA
EXCLUDESCHEMA:OLAPSYS
EXCLUDESCHEMA:SCOTT
EXCLUDESCHEMA:ORACLE_OCM
EXCLUDESCHEMA:XS$NULL
EXCLUDESCHEMA:MDDATA
EXCLUDESCHEMA:DIP
EXCLUDESCHEMA:APEX_PUBLIC_USER
EXCLUDESCHEMA:SPATIAL_CSW_ADMIN_USR
EXCLUDESCHEMA:SPATIAL_WFS_ADMIN_USR进行impdp导入
[oracle11g ~]$ impdp \/ as sysdba\ directorybaj_dir dumpfileexpdp_orcl_full_01.dmp,expdp_orcl_full_02.dmp logfileimpdp_full.log fully parallel4 table_exists_actionappend PARFILEexclude.par
####impdp导入之前需要在目标数据库上创建相应表空间对象即可而对于imp导入时需要在目标数据库上创建相应的用户、权限、表空间等对象。 验证数据部分
第一步验证数据和用户
SQL select sum(bytes) / 1024 / 1024 / 1024 || G sum from dba_segments;
SQL select username,created from dba_users order by created desc;
注总共123个用户除去系统的30个用户总共导入了93个用户 第二步验证有无失效的对象目标数据库上执行
SQL select * from dba_objects where status!VALID and owner in(用户);
SQL ?/rdbms/admin/utlrp.sql ---有无效对象的话进行无效对象的编译最大可能自动修复无效对象。其他重新编译失效对象的方法参考“PLSQL工具使用详解1、对象重定义部分”文档
第三步收集统计信息目标数据库上执行
[oraclerac1 ~]# vi status.sql begin
dbms_stats.gather_database_stats;
end;
/ [oraclerac1 ~]# nohup sqlplus / as sysdba status.sql ---因为收集统计信息时间长所以写个sh后台运行
第四步查看哪些表的统计信息被锁定stattype_locked字段为ALL的表示锁定了表的统计信息默认stattype_locked字段为空表示可以收集统计信息
SQL select * from dba_ind_statistics where stattype_lockedALL AND OWNER用户;
SQL select * from dba_tab_statistics where stattype_lockedALL AND OWNER用户;SQL begindbms_stats.unlock_schema_stats(ownname 用户);end;/
第五步验证对象目标数据库上执行
SQL select object_type t_object_type, count(*) t_countfrom dba_objectswhere owner in(用户)group by object_type
###注oracle的对象类型可以分的很详细表、表分区、表子分区是不同的类型。 第六步对比导入和导出日志 第七步将undo时间修改回最佳值
SQL alter system set undo_retention10800 scopeboth sid*; 关于Oracle逻辑迁移工具的用户和案例今天就全部讲完了4篇文章从构思、排版、整理花了2星期的时间创作不易坚持亦难希望各个小伙伴 点赞、收藏、加关注 。希望这4篇文章能够在大家以后的工作和学习中带来帮助。下篇文章将带来MySQL的内容尽请关注。
