1.ORACLE資料庫數據導入到測試庫環境 172.15.1.51 root kic@test 172.15.1.52 root 一般先將數據導入52的環境（配置比較低），再將數據導入51的環境（本文檔使用的環境） 2. 測試環境建立oracle資料庫用戶，並且賦予nc表空間許可權 3.使用數據泵im ...
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.
