Erlo

记一次oracle 11g数据导入

2019-05-20 18:00:20 发布   327 浏览  
页面报错/反馈
收藏 点赞

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.

 

 

 

 

登录查看全部

参与评论

评论留言

还没有评论留言,赶紧来抢楼吧~~

手机查看

返回顶部

给这篇文章打个标签吧~

棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认