Oracle data import notes

see liunx edition

uname -rm
# see liunx
cat /proc/version
# Check the disk size 
df -h

rpm Check software requirements

 The following packages must be installed 
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 Install the software

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 defect

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

Set up user groups

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

# Set the password 
passwd oracle
 Unified settings softroad

 View user groups 
id oracle
 Show =uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)

Set the value of kernel parameters

Create or edit /etc/sysctl.conf file

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

 Input  /sbin/sysctl -p

Add downlink to /etc/security/limits.conf

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

establish oracle file

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

Set environment parameters

/etc/profile Finally add

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

Unzip the file

cd /oracle/Installation_package

gunzip filename.cpio.gz
unzip p4547817_10202_LINUX.zip

Use Xstart Report errors

Linux install Oracle newspaper Checking operating system version must be redhat-3, SuSE-9, redhat-4, UnitedLin

Will file /etc/redhat-release The content of

Red Hat Enterprise Linux Server release 5 (Tikanga)
 It is amended as follows 
Red Hat Enterprise Linux Server release 4 (Tikanga)

./runInstaller -ignoreSysPreReqs

yum Installation mode

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

rpm Installation mode

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

0. Commonly used instructions

# Query users and tablespaces 
select username,default_tablespace from dba_users;
# Query environment 
select *  from nls_database_parameters;
# Query all users 
select * from all_users;

1. Delete user

drop user  user name  cascade;

# Unable to delete user 
#------ Check whether to log in to this user -----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';
# Execute the query to find all 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  user name  cascade;
#------ Check whether to log in to this user -----end

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

2. Delete tablespace

drop tablespace  Table space name  including contents and datafiles;
# Due to low version oracle, You need to manually delete the source file in the tablespace Directory 
# Cannot delete , After executing the order , Then delete 
shutdown immediate;
startup;

# Accidentally delete the tablespace source file , Start database error :ORA-01219
# The solution is as follows ：  The wrong solution 
1.  Run input ：sqlplus / as sysdba
2.  Execute first ”alter database open”, The following error will occur 
 The first  1  Line error :
ORA-01157:  Unable to identify / Lock the data file  6 -  see also  DBWR  Tracking files 
ORA-01110:  Data files  6: D:\oracle\product\10.2.0\oradata\XXXX( Instance name )\XXXXXXXXX.DBF

3.  perform alter database create datafile 'D:\oracle\product\10.2.0\oradata\XXXX( Instance name )\XXXXXXXXX.DBF';
4.  perform alter database datafile 'D:\oracle\product\10.2.0\oradata\XXXX( Instance name )\XXXXXXXXX.DBF' offline drop
5.  perform alter database open

3. Create tablespace

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 Error resolution -----start
#---- View the standard block size of the database ----
show parameter db_block_size;

#---- Set up 16kb Non standard block data buffer size ----
alter system set db_16k_cache_size=16m;

# Block size   Maximum value of physical file  M
#=============================================== 
2048               8191M
4096               16383M
8192               32767M
16384              65535M
#ORA-29339 Error resolution -----end

4. Create user

create user XDH
identified by XDH;

create user XDH
identified by XDH
default tablespace XXXX( Specify the name of the table space );

5. Authorized user

grant dba to XXX( user name );

grant select on v_$mystat to XXX( user name );

grant  select any table to XXX( user name );

grant  delete any table to XXX( user name );

grant  update any table to XXX( user name );

grant  insert any table to XXX( user name );

6. Tablespace authorization

alter user XXX( user ) quota unlimited on XXXX( Specify the name of the table space );

7. Import dum

#window Use impdp Must be in lowercase 
impdp SYSTEM_GRANT/SYSTEM_GRANT remap_schema=SYSTEM_GRANT:SYSTEM_GRANT dumpfile=amdux070_expdp_full.dmp logfile=amdux070_expdp_full_3.log directory=dump_dir exclude=user table_exists_action=replace parallel=4;

IMP SYSMAN/SYSMAN@d372sv BUFFER=64000 FILE=D:\DBCreat\dmp\d372sv\d372sv_exp_full.dmp log=D:\DBCreat\dmp\d372sv\d372sv_exp_full_5.log ignore=y full=y;

7.1. Use impdp, establish dum Folder

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

8. Create synonyms

create synonym WWW( Table name ) for XXX.WWW( Table name );

# See Synonyms 
SELECT * FROM V$TABLESPACE;
 
# Delete tablespace 
drop tablespace WWW( Table name );

8. Execute all in batch sql

#1. stay sql File root directory , newly build sqlList.bat file 
dir /s/b *.sql >ddl_fileList.sql

#2. Generate ddl_fileList.sql, Change to the following format ,  View's sql Put the file on the last side to execute 
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. Copy to command window 
@D:\DBCreat\01_DDL\ Generate ddl_fileList.sql

9. Import data (ctl and csv)

#1. stay ctl File root directory , newly build ctl_fileList.bat file 
dir /s/b *.sql >ctl_fileList.txt


#2. newly build loadctlandcsv.bat file , take log The file path is created manually 
@echo off & setlocal EnableDelayedExpansion
set USERNAME="XXXX"
set PASSWORD="XXXX"
set SID="WWWWW"
for /f "delims=""" %%i in (D:\DBCreat\CTL writing \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 File format

OPTIONS (skip=1) 
load data
infile "C:\DBCreate\db_TableAndData\XXXX.csv"
append into table XXXX Table name 
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 '"'
)

There is an error

1. call oracle Graphical interface error report

Oracle Universal Installerを Starting です...

インストーラ important document の Confirming ...

オペレーティング・システムのバージョンを Confirming :  Must 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
                                       The problem is なし


すべてのインストーラ important document を Machine たしています.

Oracle Universal Installerの Starting を In preparation  /tmp/OraInstall2022-04-15_04-39-40AM. お stay ちください...[oracle@localhost Disk1]$ Oracle Universal Installer, バージョン 10.2.0.5.0  Product version 
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の Spring ladle を Clean and sprinkle noodles  /tmp/OraInstall2022-04-19_05-40-42PM. お A little ちください...[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. Error command resolution

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

------ Check whether to log in to this user -----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;
------ Check whether to log in to this user -----end

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

ORA-29339 Error resolution -----start
---- View the standard block size of the database ----
show parameter db_block_size;

---- Set up 16kb Non standard block data buffer size ----
alter system set db_16k_cache_size=16m;

 Block size          Maximum value of physical file  M
===============================================               
2048               8191M
4096               16383M
8192               32767M
16384              65535M
ORA-29339 Error resolution -----end



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

3.ORA-01219 The wrong solution

 The solution is as follows ：

1.  Run input ：sqlplus /nolog

2.  With sysdba Role login ：connect sys/ password  as sysdba

3.  Execute first ”alter database open”, The following error will occur 

 The first  1  Line error :
ORA-01157:  Unable to identify / Lock the data file  6 -  see also  DBWR  Tracking files 
ORA-01110:  Data files  6: D:\DATA\PROD\PRODDATA\CUX_INDEX_X_1.DBF

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

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

6.  perform alter database open

5.ORA-39083 ORA-01917 Problem solving

` have access to impdp Medium transform=oid:n  Solve the problem .`

ORA-39083:  object type  OBJECT_GRANT  Create failure ,  There is an error :
ORA-01917:  Users or roles  ‘C##DCPS’  non-existent 

 Export the user's schema And import the user's schema atypism , Cause the user or role does not exist 

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