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.