记一次oracle 11g数据导入
1.oracle数据库数据导入到测试库环境
172.15.1.51 root kic@test
172.15.1.52 root
一般先将数据导入52的环境(配置比较低),再将数据导入51的环境(本文档使用的环境)
[root@nc-test ~]# df -h filesystem size used avail use% mounted on /dev/mapper/vg_nctest-lv_root 196g 142g 45g 76% / tmpfs 16g 8.5g 7.2g 55% /dev/shm /dev/sda1 485m 39m 421m 9% /boot /dev/mapper/vg_nctest-lv_home 51g 34g 14g 72% /home //172.16.1.101/backup 8.1t 3.9t 4.3t 48% /mnt/win [root@nc-test ~]# cd /home/nc_data_backup/ [root@nc-test nc_data_backup]# ll total 19623748 -rw-r-----. 1 oracle oinstall 444667 sep 19 2017 export.log -rwxr-x--x. 1 oracle oinstall 450379 nov 9 10:45 import.log -rwxr-xr-x. 1 oracle oinstall 20093812736 nov 9 10:00 nc63demo_v633_80_version_11_2_0_4_0_20181108.dmp [root@nc-test nc_data_backup]# rm -rf nc63demo_v633_80_version_11_2_0_4_0_20181108.dmp # 将上次导入的".dmp"文件删除 [root@nc-test nc_data_backup]# cp /mnt/win/database/nc63demo_v633_80_version_11_2_0_4_0_20190411.* /home/nc_data_backup/ # 将备份文件从远程服务器上(oracle数据文件保存在windows server 2008上)拉取到本地目录 [root@nc-test nc_data_backup]# ll total 22829376 -rw-r-----. 1 oracle oinstall 444667 sep 19 2017 export.log -rwxr-x--x. 1 oracle oinstall 450379 nov 9 10:45 import.log -rwxr-xr-x. 1 root root 23375937536 apr 15 12:18 nc63demo_v633_80_version_11_2_0_4_0_20190411.dmp -rwxr-xr-x. 1 root root 435563 apr 15 12:18 nc63demo_v633_80_version_11_2_0_4_0_20190411.log
2. 测试环境建立oracle数据库用户,并且赋予nc表空间权限
[root@nc-test nc_data_backup]# su - oracle # 切换到oracle用户 [oracle@nc-test ~]$ sqlplus / as sysdba # 登录数据库以最高权限 sql> select username, default_tablespace, temporary_tablespace from dba_users; # 查看用户名,默认表空间,临时表空间, sql> set linesize 200; sql> / username default_tablespace temporary_tablespace ------------------------------ ------------------------------ ------------------------------ sys system temp dbsnmp sysaux temp sysman sysaux temp nc63_b2b users temp test users temp nc633kic102 nnc_data01 temp nc633kic006 nnc_data01 temp nc633kic202 nnc_data01 temp nc633kic001 nnc_data01 temp system system temp outln system temp sql> create user nc0412 identified by nc0412 default tablespace nnc_data01 temporary tablespace temp; # 创建用户nc0412,表空间为nnc_data01,临时表空间一般都是temp
user created. sql> select username, default_tablespace, temporary_tablespace from dba_users; username default_tablespace temporary_tablespace ------------------------------ ------------------------------ ------------------------------ sys system temp dbsnmp sysaux temp sysman sysaux temp nc63_b2b users temp test users temp nc0412 nnc_data01 temp # 用户创建完成 system system temp sql> grant connect,dba to nc0412; # 给用户连接及dba权限 grant succeeded. sql> select * from dba_role_privs where grantee='nc0412'; grantee granted_role adm def ------------------------------ ------------------------------ --- --- nc0412 dba no yes nc0412 connect no yes
3.使用数据泵impdp导入数据库备份文件(expdp是导出命令)
sql> select total.tablespace_name,
round(total.mb, 2) as total_mb,
round(total.mb - free.mb, 2) as used_mb,
round(( 1 - free.mb / total.mb ) * 100, 2)
|| '%' as used_pct
from (select tablespace_name,
sum(bytes) / 1024 / 1024 as mb
from dba_free_space
group by tablespace_name) free,
(select tablespace_name,
sum(bytes) / 1024 / 1024 as mb
from dba_data_files
group by tablespace_name) total
where free.tablespace_name = total.tablespace_name; # 查看表空间使用率
2 3 4 5 6 7 8 9 10 11 12 13 14
tablespace_name total_mb used_mb used_pct
------------------------------ ---------- ---------- -----------------------------------------
sysaux 4990 4745.94 95.11%
undotbs1 32767.98 31693.05 96.72%
users 7.5 6 80%
system 1800 1793.5 99.64%
nnc_index01 10108 9517.31 94.16%
nnc_data01 52474 39853.13 75.95%
sql> quit
disconnected from oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
[oracle@nc-test ~]$ impdp nc0412/nc0412(用户/密码) dumpfile=nc63demo_v633_80_version_11_2_0_4_0_20190411.dmp(导入文件名称) directory=dump_dir remap_schema=nc63demo:nc0412
4.重新打开一个窗口(或者将程序放在后台运行),查看导入日志
注:一般导入日志时显示的内容会比较慢,以命令行显示的为准
[root@nc-test ~]# tail -f /home/nc_data_backup/import.log
import: release 11.2.0.4.0 - production on mon apr 15 13:11:47 2019 # 备份开始时间
copyright (c) 1982, 2011, oracle and/or its affiliates. all rights reserved.
connected to: oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
master table "nc0412"."sys_import_full_01" successfully loaded/unloaded
starting "nc0412"."sys_import_full_01": nc0412/******** dumpfile=nc63demo_v633_80_version_11_2_0_4_0_20190411.dmp directory=dump_dir remap_schema=nc63demo:nc0412
processing object type schema_export/user
ora-31684: object type user:"nc0412" already exists # 因为提前创建了用户,所以会有个报错,可以忽略
processing object type schema_export/system_grant
processing object type schema_export/role_grant
processing object type schema_export/default_role
processing object type schema_export/pre_schema/procact_schema
processing object type schema_export/table/table # 日志中这个位置会停留一段时间,正在加载表,属于正常现象。
processing object type schema_export/table/table_data # 之后会在这个位置停留一段时间
. . imported "nc0412"."sm_filestoreview" 11.88 gb 17912 rows
. . imported "nc0412"."iufo_task_data_instance" 2.417 gb 20093 rows
. . imported "nc0412"."sm_busilog_default" 741.5 mb 272905 rows
. . imported "nc0412"."pub_workingtasklog" 132.7 mb 28595 rows
. . imported "nc0412"."ia_detailledger" 256.0 mb 214877 rows
. . imported "nc0412"."cof_planexecdoc" 234.9 mb 597579 rows #后面会出现很多类似的显示,中间省略
. . . . . .
. . . . . .
. . . . . .
ora-01653: unable to extend table nc0412.ia_i5bill_b by 128 in tablespace nnc_data01 #ora-01653报错,发现表空间已经满了,但是无法进行扩容
# 解决办法
sql> alter tablespace nnc_data01 add datafile '/opt/oracle/oradata/ncerpd/nnc_data03.dbf' size 500m autoextend on; # 表空间扩容命令 tablespace altered. . . imported "nc0412"."xx_exsystemdoc" 0 kb 0 rows . . imported "nc0412"."xx_xsysregister" 0 kb 0 rows processing object type schema_export/table/comment processing object type schema_export/function/function processing object type schema_export/function/alter_function processing object type schema_export/table/index/index # 因为此位置要加载索引,会停留较久的时间,耐心等候 processing object type schema_export/table/constraint/constraint processing object type schema_export/table/index/statistics/index_statistics # 可以df -h 看下磁盘空间在不断减少,或者使用top命令和iostat,查看信息 processing object type schema_export/view/view processing object type schema_export/view/grant/owner_grant/object_grant ora-39082: object type view:"nc0412"."kic_tt_2" created with compilation warnings processing object type schema_export/table/constraint/ref_constraint processing object type schema_export/table/trigger processing object type schema_export/table/statistics/table_statistics # 至此还需要加载很长时间,不过已经接近尾声,此时我们可以着手配置erp数据了 job "nc0412"."sys_import_full_01" completed with 20 error(s) at mon apr 15 16:05:50 2019 elapsed 0 02:53:46
# 至此数据导入完成,历时02:53:46,主要是中间解决错误花费的时间,以往记录是在30-40分钟左右
5. 使用可视化程序配置sysconfig.sh文件,启动用友程序
xmanager可视化程序安装:https://jingyan.baidu.com/article/39810a239e70feb636fda6c6.html
[root@nc-test desktop]# vim /etc/profile # 查看末尾处java的环境变量 [root@nc-test desktop]# ulimit -n # 设置文件最大打开数 1024 [root@nc-test desktop]# ulimit -n 65535 [root@nc-test desktop]# ulimit -n 65535 [root@nc-test desktop]# cd /nc633/ [root@nc-test nc633]# vim ./startup.sh [root@nc-test nc633]# vim ./stop.sh [root@nc-test nc633]# vim bin/sysconfig.sh # 查看以上三个脚本文件中java定义的环境变量 [root@nc-test nc633]# cd bin/ [root@nc-test bin]# ./sysconfig.sh # 启动用友软件的脚本,此处必须在可视化界面操作 java_home environment variable is undefined.please set it. example: export java_home=/opt/jdk1608 export path=$java_home/bin:$path # 报错,没有加载环境变量 [root@nc-test bin]# source /etc/profile # 重新加载环境变量 [root@nc-test bin]# ./sysconfig.sh # 启动脚本 platform=linux x86_64 . java_home=/data/javaibm/java . nc_home=/nc633 . was_home= is_cluster_install=false was_profile= was_profile_path=/profiles/ buildfile: /nc633/bin/buildmisc.xml sysconfig: [java] log4j:warn no appenders could be found for logger (org.java.plugin.boot.defaultapplicationinitializer). [java] log4j:warn please initialize the log4j system properly.
上一篇: 请开始你的脑补!