oracle数据导入记录笔记

查看liunx版本

uname -rm
#查看liunx
cat /proc/version
#查看磁盘大小
df -h
#重启网卡
service network restart
ifdown ens160
ifup ens160
ifconfig eth0 down / ifconfig eth0 up

网络设置

[root@192 ~]# cd /etc/sysconfig/network-scripts/
[root@192 network-scripts]# cp -p ifcfg-ens160 ifcfg-ens160.bak
[root@192 network-scripts]# vi ifcfg-ens160
#最后面追加
IPADDR=192.168.75.0
PREFIX=24
GATEWAY=192.168.75.2
DNS1=223.5.5.5
DNS2=114.114.114.114
NETMASK=255.255.255.0


参照以下查看ip地址设置

查看ip地址设置

1、点击虚拟机网络连接方式：使用net模式。

2点击编辑->选择虚拟网络编辑器，确定自己的网关地址。

rpm检查软件需求

必须安装以下软件包
rpm -qa | grep binutils
rpm -qa | grep compat
rpm -qa | grep control
rpm -qa | grep gcc
rpm -qa | grep gcc
rpm -qa | grep glibc 
rpm -qa | grep glibc
rpm -qa | grep gnome
rpm -qa | grep libstdc
rpm -qa | grep libstdc
rpm -qa | grep make
rpm -qa | grep pdksh
rpm -qa | grep sysstat
rpm -qa | grep xscreensaver

rpm安装软件

rpm -ivh gnome-libs-1.4.1.2.90-44.1.x86_64.rpm
rpm -ivh pdksh-5.2.14-30.x86_64.rpm
rpm -ivh xscreensaver-4.18-5.rhel4.2.x86_64.rpm

rpm缺失

compat-db 4.0.14-5.1
gcc-c++-3.2.3-47
compat-db-4.0.14-5

设置用户组

groupadd oinstall
grep dba /etc/group
groupadd dba
useradd -g oinstall -G dba,oper oracle

#设置密码
passwd oracle
统一设置softroad

查看用户组
id oracle
显示=uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)

设定内核参数的值

创建或编辑/etc/sysctl.conf文件

kernel.shmall = 2097152 
kernel.shmmax = 2147483648 

kernel.shmmni = 4096 
kernel.sem = 250 32000 100 128 
fs.file-max = 65536 
net.ipv4.ip_local_port_range = 1024 65000 
net.core.rmem_default = 262144 
net.core.rmem_max = 262144 
net.core.wmem_default = 262144 
net.core.wmem_max = 262144

输入 /sbin/sysctl -p

下行添加到/etc/security/limits.conf

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  1024
oracle              hard    nofile  65536

创建oracle文件

mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01/app/oracle

mkdir -p /oracle/Installation_package
chown -R oracle:oinstall /oracle/Installation_package
chmod -R 775 /oracle/Installation_package

设置环境参数

/etc/profile最后追加

export ORACLE_HOME=/u01/app/oracle/product/10.2/db_1
export ORACLE_SID=orcl
export PATH=$PATH:ORACLE_HOME/bin

解压文件

cd /oracle/Installation_package

gunzip filename.cpio.gz
unzip p4547817_10202_LINUX.zip

使用Xstart报错

Linux安装Oracle报Checking operating system version must be redhat-3, SuSE-9, redhat-4, UnitedLin

将文件 /etc/redhat-release的内容

Red Hat Enterprise Linux Server release 5 (Tikanga)
修改为
Red Hat Enterprise Linux Server release 4 (Tikanga)

./runInstaller -ignoreSysPreReqs

yum安装方式

yum install libXt.so.6
yum provides libXt.so.6

rpm安装方式

rpm -ivh *
rpm -Uvh *.rpm
rpm -Uvh *.rpm --nodeps --force

在liunx挂载镜像iso

#1.创建文件夹 mkdir /iso 将镜像放在此目录下
#2.创建文件夹 mkdir /mnt/dcrom
#3.执行以下命令
mount -o loop /iso/CentOS-7-x86_64-DVD-1804.iso /mnt/cdrom
#查看挂载镜像是否成功
df -h

0.常用指令

#查询用户和表空间
select username,default_tablespace from dba_users;
#查询环境
select *  from nls_database_parameters;
#查询所有用户
select * from all_users;
#oracle查询数据库实例名
SELECT instance_name FROM v$instance;
#启动监听器
lsnrctl  start
#停止监听器
lsnrctl stop
#查看监听
lsnrctl status
#查看oracle版本
select * from v$version;

1.删除用户

drop user 用户名 cascade;

#无法删除用户
#------查看是否登录该用户-----start
select username,sid,serial#,paddr from v$session where username='SYSMAN';
select PROGRAM from v$process where addr='00007FF8973DF560';
#alter system kill session 'SID,SERIAL';
#执行查询出来所有的SID,SERIAL
alter system kill session '138,5';
select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
drop user 用户名 cascade;
#------查看是否登录该用户-----end

#-----ORA-24005--start------
alter session set events'10851 trace name context forever,level 1'
drop user 用户名 cascade;
#-----ORA-24005--end------

2.删除表空间

drop tablespace 表空间名 including contents and datafiles;
#由于低版本oracle,需要在表空间目录手动删除源文件
#无法删除时,执行一下命令后,然后删除
shutdown immediate;
startup;

#不小心删除表空间源文件,启动数据库报错:ORA-01219
#解决办法如下： 错误的解决办法
1. 运行输入：sqlplus / as sysdba
2. 先执行”alter database open”，会出现如下的错误
第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: D:\oracle\product\10.2.0\oradata\XXXX(实例名)\XXXXXXXXX.DBF

3. 执行alter database create datafile 'D:\oracle\product\10.2.0\oradata\XXXX(实例名)\XXXXXXXXX.DBF';
4. 执行alter database datafile 'D:\oracle\product\10.2.0\oradata\XXXX(实例名)\XXXXXXXXX.DBF' offline drop
5. 执行alter database open

3.创建表空间

CREATE TABLESPACE INDEX03 BLOCKSIZE 8192 
DATAFILE '/opt/oracle/oradata/XXX/XXXXX.dbf' 
SIZE 2048M
AUTOEXTEND ON NEXT 1048576 MAXSIZE 32767M 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE JRS_USRSP BLOCKSIZE 16384 
DATAFILE 'D:\oracle\product\10.2.0\oradata\XXX\XXXX.DBF'
SIZE 2048M
AUTOEXTEND ON NEXT 1048576 MAXSIZE 65535M 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE INDEX01 BLOCKSIZE 8192 
DATAFILE '/opt/oracle/oradata/XXX/XXX.dbf' 
SIZE 2048M 
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M,
'/opt/oracle/oradata/XXX/XXX.dbf' 
SIZE 2048M 
AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT SEGMENT SPACE MANAGEMENT AUTO;

#ORA-29339错误解决办法-----start
#----查看数据库的标准数据块大小----
show parameter db_block_size;

#----设置16kb非标准数据块数据缓冲区大小----
alter system set db_16k_cache_size=16m;

#数据块的大小        物理文件的最大值 M
#===============================================               
2048               8191M
4096               16383M
8192               32767M
16384              65535M
#ORA-29339错误解决办法-----end

4.创建用户

create user XDH
identified by XDH;

create user XDH
identified by XDH
default tablespace XXXX(指定表空间名);

5.授权用户

grant dba to XXX(用户名);

grant select on v_$mystat to XXX(用户名);

grant  select any table to XXX(用户名);

grant  delete any table to XXX(用户名);

grant  update any table to XXX(用户名);

grant  insert any table to XXX(用户名);

6.表空间授权

alter user XXX(用户) quota unlimited on XXXX(指定表空间名);

7.导入dum

#window使用impdp必须小写
impdp SYSTEM_GRANT/SYSTEM_GRANT remap_schema=SYSTEM_GRANT:SYSTEM_GRANT dumpfile=XXXX_expdp_full.dmp logfile=XXXX_expdp_full_3.log directory=dump_dir exclude=user table_exists_action=replace parallel=4;

IMP SYSMAN/SYSMAN@XXXX BUFFER=64000 FILE=D:\DBCreat\dmp\XXXX\XXXX_exp_full.dmp log=D:\DBCreat\dmp\XXXX\XXXX_exp_full_5.log ignore=y full=y;

7.1.使用impdp, 创建dum文件夹

create or replace directory dump_dir as '/oracle/dmp';
grant read,write on directory dump_dir to system;
select * from dba_directories;

8.创建同义词

create synonym WWW(表名) for XXX.WWW(表名);

#查看同义词
SELECT * FROM V$TABLESPACE;
 
#删除表空间
drop tablespace WWW(表名);

8.批量执行所有的sql

#1.在sql文件根目录下,新建sqlList.bat文件
dir /s/b *.sql >ddl_fileList.sql

#2.生成ddl_fileList.sql,修改成以下格式, 视图的sql文件放在最后面执行
spool C:\DBCreate\db_TableAndData\01_DDL\ddlsql_init.log;
set feedback off;
set define off;
prompt
-----start---
@C:\sql\XXXX.sql;
prompt
-----end---
prompt done
spool off;
exit;

#3.复制到命令窗口
@D:\DBCreat\01_DDL\生成ddl_fileList.sql

9.导入数据(ctl和csv)

#1.在ctl文件根目录下,新建ctl_fileList.bat文件
dir /s/b *.sql >ctl_fileList.txt


#2.新建loadctlandcsv.bat文件,将log文件路径手动创建
@echo off & setlocal EnableDelayedExpansion
set USERNAME="XXXX"
set PASSWORD="XXXX"
set SID="WWWWW"
for /f "delims=""" %%i in (D:\DBCreat\CTL文\ctl_fileList.txt) do (
set /a num+=1
set con!num!=%%i
call set filePath=%%con!num!%%
echo !filePath!
sqlldr %USERNAME%/%PASSWORD%@%SID% control=!filePath!  log=C:\log\info!num!.log  errors=10000
)
pause

9.1ctl文件格式

OPTIONS (skip=1) 
load data
infile "C:\DBCreate\db_TableAndData\XXXX.csv"
append into table XXXX表名
fields terminated by ","
trailing nullcols
(
TORIHIKI_C char(10) OPTIONALLY ENCLOSED BY '"',
USING_KAISHA_C char(10) OPTIONALLY ENCLOSED BY '"',
TAIHI_RECORD_KBN char(1) OPTIONALLY ENCLOSED BY '"',
SET_FLG char(1) OPTIONALLY ENCLOSED BY '"',
K_TORIHIKI_C char(10) OPTIONALLY ENCLOSED BY '"',
REF_USING_KAISHA_C char(10) OPTIONALLY ENCLOSED BY '"',
OBJECT_VERSION OPTIONALLY ENCLOSED BY '"',
DELETE_FLG char(1) OPTIONALLY ENCLOSED BY '"',
UPDATE_YMD DATE "YYYY-MM-DD HH24:MI:SS" OPTIONALLY ENCLOSED BY '"',
SYS1 OPTIONALLY ENCLOSED BY '"',
SYS2 OPTIONALLY ENCLOSED BY '"',
SYS3 OPTIONALLY ENCLOSED BY '"',
SYS4 OPTIONALLY ENCLOSED BY '"',
SYS5 OPTIONALLY ENCLOSED BY '"'
)

修改实例名

create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/pfileamdux073.ora' from spfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';

shutdown abort;
create spfile from pfile;
alter system set db_name=amdux073 scpoe =orcl;


show parameter spfile;

出现报错

1.调用oracle图形化界面报错

Oracle Universal Installerを起動中です...

インストーラ要件の確認中...

オペレーティング・システムのバージョンを確認中: 必須redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6, UnitedLinux-1.0, asianux-1, asianux-2, asianux-3, enterprise-4, enterprise-5 or SuSE-11
                                      問題なし


すべてのインストーラ要件を満たしています。

Oracle Universal Installerの起動を準備中 /tmp/OraInstall2022-04-15_04-39-40AM. お待ちください...[oracle@localhost Disk1]$ Oracle Universal Installer, バージョン 10.2.0.5.0 製品版
Copyright (C) 1999, 2010, Oracle. All rights reserved.

Exception java.lang.UnsatisfiedLinkError: /tmp/OraInstall2022-04-15_04-39-40AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory occurred..
java.lang.UnsatisfiedLinkError: /tmp/OraInstall2022-04-15_04-39-40AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
	at java.lang.ClassLoader$NativeLibrary.load(Native Method)
	at java.lang.ClassLoader.loadLibrary0(Unknown Source)
	at java.lang.ClassLoader.loadLibrary(Unknown Source)
	at java.lang.Runtime.loadLibrary0(Unknown Source)
	at java.lang.System.loadLibrary(Unknown Source)
	at sun.security.action.LoadLibraryAction.run(Unknown Source)
	at java.security.AccessController.doPrivileged(Native Method)
	at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
	at sun.awt.DebugHelper.<clinit>(Unknown Source)
	at java.awt.Component.<clinit>(Unknown Source)
	at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.<init>(OiifmGraphicInterfaceManager.java:222)
	at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:194)
	at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:203)
	at oracle.sysman.oii.oiic.OiicInstaller.getInterfaceManager(OiicInstaller.java:463)
	at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:959)
	at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:899)
Exception in thread "main" java.lang.NoClassDefFoundError
	at oracle.sysman.oii.oiif.oiifm.OiifmGraphicInterfaceManager.<init>(OiifmGraphicInterfaceManager.java:222)
	at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.createInterfaceManager(OiicSessionInterfaceManager.java:194)
	at oracle.sysman.oii.oiic.OiicSessionInterfaceManager.getInterfaceManager(OiicSessionInterfaceManager.java:203)
	at oracle.sysman.oii.oiif.oiifm.OiifmAlert.<clinit>(OiifmAlert.java:151)
	at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:1017)
	at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:899)

Oracle Universal Installerの弹瓢を洁洒面 /tmp/OraInstall2022-04-19_05-40-42PM. お略ちください...[oracle@localhost database]$ current locale is not supported in X11, locale is set to CX locale modifiers are not supported, using defaultWarning: Cannot convert string "-sony-fixed-medium-r-normal--*-140-*-*-c-*-jisx0201.1976-0" to type FontStruct
Warning: Cannot convert string "-watanabe-mincho-medium-r-normal--*-140-*-*-c-*-jisx0208.1983-0" to type FontStruct
Exception in thread "main" java.lang.InternalError: Current locale is not supported
	at sun.awt.motif.MWindowPeer.pSetTitle(Native Method)
	at sun.awt.motif.MWindowPeer.init(Unknown Source)
	at sun.awt.motif.MFramePeer.<init>(Unknown Source)
	at sun.awt.motif.MToolkit.createFrame(Unknown Source)
	at java.awt.Frame.addNotify(Unknown Source)
	at java.awt.Window.pack(Unknown Source)
	at oracle.sysman.oio.oioc.OiocOneClickInstaller.createSubCenterPanel(OiocOneClickInstaller.java:878)
	at oracle.sysman.oio.oioc.OiocOneClickInstaller.createPanel1(OiocOneClickInstaller.java:768)
	at oracle.sysman.oio.oioc.OiocOneClickInstaller.createCenterPanel(OiocOneClickInstaller.java:719)
	at oracle.sysman.oio.oioc.OiocOneClickInstaller.init(OiocOneClickInstaller.java:430)
	at oracle.sysman.oio.oioc.OiocOneClickInstaller.createFrameElements(OiocOneClickInstaller.java:386)
	at oracle.sysman.oio.oioc.OiocOneClickInstaller.main(OiocOneClickInstaller.java:2135)

2.错误指令解决

select username,default_tablespace from dba_users;
shutdown immediate;
startup;
alter database open;

------查看是否登录该用户-----start
select username,sid,serial#,paddr from v$session where username='SYSMAN';
select PROGRAM from v$process where addr='00007FF8973DF560';
/*alter system kill session 'SID,SERIAL';*/
alter system kill session '138,5';
select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
drop user SYSMAN cascade;
------查看是否登录该用户-----end

-----ORA-24005--start------
alter session set events'10851 trace name context forever,level 1'
drop user SYSMAN cascade;
-----ORA-24005--end------

ORA-29339错误解决办法-----start
----查看数据库的标准数据块大小----
show parameter db_block_size;

----设置16kb非标准数据块数据缓冲区大小----
alter system set db_16k_cache_size=16m;

数据块的大小        物理文件的最大值 M
===============================================               
2048               8191M
4096               16383M
8192               32767M
16384              65535M
ORA-29339错误解决办法-----end



ORA-03113: end-of-file on communication channel
Process ID: 25798
Session ID: 1 Serial number: 5

3.ORA-01219 错误的解决办法

解决办法如下：

1. 运行输入：sqlplus /nolog

2. 以sysdba的角色登录：connect sys/口令 as sysdba

3. 先执行”alter database open”，会出现如下的错误

第 1 行出现错误:
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: D:\DATA\PROD\PRODDATA\CUX_INDEX_X_1.DBF

4. 执行alter database create datafile 'D:\DATA\PROD\PRODDATA\CUX_INDEX_X_1.DBF';

5. 执行alter database datafile 'D:\DATA\PROD\PRODDATA\CUX_INDEX_X_1.DBF' offline drop

6. 执行alter database open

5.ORA-39083 ORA-01917问题解决

`可以使用impdp中的transform=oid:n 解决该问题。`

ORA-39083: 对象类型 OBJECT_GRANT 创建失败, 出现错误:
ORA-01917: 用户或角色 ‘C##DCPS’ 不存在

导出用户的schema和导入用户的schema不一致，导致用户或者角色不存在

6.ORA-39002 ORA-39070 ORA-29283问题解决

With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

文件夹权限不足, chmod -R 777 /oracle/dmp

liunx5.6配置yum

[BaseOS]
name=BaseOS
baseurl=file:///mnt/cdrom/BaseOS
enabled=1
gpgcheck=0
[AppStream]
name=AppStream
baseurl=file:///mnt/cdrom/AppStream
enabled=1
gpgcheck=0