欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  科技

记一次oracle 11g数据导入

程序员文章站 2022-09-08 17:27:27
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.