客户给了7个dmp文件共15G左右,需要导入到测试环境,结果导入是成功了,但中文都是乱码。也只怪自己没注意刚开始的提示信息:
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
系统有提示编码对不上,查了半天只能重来了,幸好数据库是新安装的,20G的数据量还是比较大,于是先把表空间和用户都删除了干脆一干二净重来。
drop tablespace XXXX INCLUDING CONTENTS;
drop user XXXX cascade;
然后到之前存放数据文件的目录把20个数据文件也删除了。
附监听命令:
启动监听:lsnrctl start
查看监听:lsnrctl status
停止监听:lsnrctl stop
因安装oracle以及新建实例时未注意字符集,修改数据库字符集相对简单也方便就改数据库字符集了。按照网上:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set internal_use ZHS16GBK;
SQL> shutdown immediate;
SQL> startup;
结果关闭没问题,但不能启动了,报错信息:
SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_3/dbs/initorcl.ora'
把数据库实例对应目录下的复制过去也不行,终于找到解决方法,在运行上面命令前,先把系统环境变量做修改
[oracle@localhost table_space]$ export ORACLE_SID=xxxx xxxx表示你的实例名即sid
[oracle@localhost table_space]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 14 12:29:48 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220796 bytes
Variable Size 113250116 bytes
Database Buffers 482344960 bytes
Redo Buffers 7163904 bytes
Database mounted.
SQL>
接下来继续剩余 的操作,因为系统是32位的,创建个大表空间还是有些麻烦:
create tablespace xxx datafile '/home/oracle/table_space/icms1.dat' SIZE 3G,
'/home/oracle/table_space/icms2.dat' SIZE 3G,'/home/oracle/table_space/icms3.dat' SIZE 3G,
'/home/oracle/table_space/icms4.dat' SIZE 3G,'/home/oracle/table_space/icms5.dat' SIZE 3G,
'/home/oracle/table_space/icms6.dat' SIZE 3G,'/home/oracle/table_space/icms7.dat' SIZE 3G,
'/home/oracle/table_space/icms8.dat' SIZE 3G,'/home/oracle/table_space/icms9.dat' SIZE 3G,
'/home/oracle/table_space/icms10.dat' SIZE 3G,'/home/oracle/table_space/icms11.dat' SIZE 3G,
'/home/oracle/table_space/icms12.dat' SIZE 3G,'/home/oracle/table_space/icms13.dat' SIZE 3G,
'/home/oracle/table_space/icms14.dat' SIZE 3G,'/home/oracle/table_space/icms15.dat' SIZE 3G,
'/home/oracle/table_space/icms16.dat' SIZE 3G,'/home/oracle/table_space/icms17.dat' SIZE 3G,
'/home/oracle/table_space/icms18.dat' SIZE 3G,'/home/oracle/table_space/icms19.dat' SIZE 3G,
'/home/oracle/table_space/icms20.dat' size 3g autoextend on next 1g maxsize unlimited;
一次性做了60G的表空间,注意上面表空间的名称最好与dmp文件中涉及到的表空间一致,我之前就有类似提示警告,导致有些脚本不能正常执行。接下来都完成后就是导入数据了:
imp system/manage@sid file=(/home/20111213DB_DUMP/ic_001.dump,....) log=import.log fromuser=xxxx touser=xxx 最后指定日志文件以观察导入进度和结果。