Re: exp/imp and NLS

From: Terminator <kramakrishnabest_at_gmail.com>
Date: 28 Mar 2005 21:34:28 -0800
Message-ID: <1112074468.572621.116500@z14g2000cwz.googlegroups.com>

Filename :- expstuff.ksh ->

#!/usr/bin/ksh
#
# expstuff.ksh <DBNAME>
#

export DB=$1
export COMPRESS=/usr/bin/compress
export ORACLE_HOME=/opt/oracle/product/8.1.7 export MAILER=/usr/bin/mailx
export MAILEE="rkonda_at_deloitte.com"
export ORAHOME=/opt/oracle2/product/8.1.7

echo $DATE
export RUNDT=`date "+%Y%m%d"`
echo $RUNDT

if [[ ${DB} = INTTSTAM ]] then

   #export NLS_LANG=_.WE8ISO8859P15
    export NLS_LANG=_.WE8MSWIN1252
   echo $NLS_LANG
elif [[ ${DB} = CERT ]] then
export NLS_LANG=_.WE8ISO8859P15
echo $NLS_LANG
elif [[ ${DB} = PSBE ]] then

   #export NLS_LANG=_.WE8ISO8859P15
    export NLS_LANG=_.WE8MSWIN1252
   echo $NLS_LANG
elif [[ ${DB} = DEV_DEV ]] then

#export NLS_LANG=_.WE8ISO8859P15
export NLS_LANG=_.WE8MSWIN1252
elif [[ ${DB} = INTTSTJP ]] then

export NLS_LANG=_.JA16SJIS
echo $NLS_LANG
elif [[ ${DB} = INTTSTKO ]] then

export NLS_LANG=_.KO16KSC5601
echo $NLS_LANG
elif [[ ${DB} = INTTSTSC ]] then

export NLS_LANG=_.ZHS16GBK
echo $NLS_LANG
elif [[ ${DB} = PSBS ]] then

export NLS_LANG=_.ZHS16GBK
echo $NLS_LANG
elif [[ ${DB} = PSBT ]] then

export NLS_LANG=_.ZHT16BIG5
echo $NLS_LANG
elif [[ ${DB} = INTTSTTC ]] then

   #export NLS_LANG=_.ZHT16BIG5
   export NLS_LANG=_.ZHT16BIG5
   echo $NLS_LANG
else

echo "UNKNOWN DB"
exit 2
fi

export ORACLE_SID=${DB}
export ORASID=${DB}
export PIPEFILE=mypipe${ORACLE_SID}
export EXPFILE=exp${ORACLE_SID}_${RUNDT}.dmp export LOGFILE=exp${ORACLE_SID}_${RUNDT}.log export OSLOGFILE=exp${ORACLE_SID}_${RUNDT}.tmlog

rm $PIPEFILE

/etc/mknod $PIPEFILE p

${COMPRESS} < ${PIPEFILE} > ${EXPFILE}.Z &

echo "**------------- STARTING ${SCRIPT} ON ${rundate} AT `date '+%R` -------------**" |tee ${OSLOGFILE}
echo $ORACLE_HOME
${ORACLE_HOME}/bin/exp parfile=exp${ORACLE_SID}.par file=${PIPEFILE}
log=${LOGFILE}

rm ${PIPEFILE}

${MAILER} -s "`hostname`: ${ORACLE_SID} export completed" ${MAILEE} <
${LOGFILE}

Filename :- impstuff.ksh -->

#!/usr/bin/ksh
#
# impstuff.ksh
#

export DB=$1
export UNCOMPRESS=/usr/bin/uncompress
export ORACLE_HOME=/opt/oracle/product/8.1.7 export MAILER=/usr/bin/mailx
export MAILEE="rkonda_at_deloitte.com"

echo $DATE
export RUNDT=`date "+%Y%m%d"`
echo $RUNDT

if [[ ${DB} = DEV_DEV ]] then

export NLS_LANG=_.WE8MSWIN1252
echo $NLS_LANG
elif [[ ${DB} = SYSTST ]] then

export NLS_LANG=_.WE8MSWIN1252
echo $NLS_LANG
elif [[ ${DB} = INTTSTAM ]] then

export NLS_LANG=_.WE8MSWIN1252
echo $NLS_LANG
elif [[ ${DB} = INTTSTJP ]] then

export NLS_LANG=_.JA16SJIS
echo $NLS_LANG
elif [[ ${DB} = INTTSTTC ]] then

export NLS_LANG=_.ZHT16BIG5
echo $NLS_LANG
elif [[ ${DB} = INTTSTKO ]] then

export NLS_LANG=_.KO16KSC5601
echo $NLS_LANG
elif [[ ${DB} = TRNEU ]] then

export NLS_LANG=_.WE8ISO8859P15
echo $NLS_LANG
elif [[ ${DB} = TRNAM ]] then

export NLS_LANG=_.WE8ISO8859P15
echo $NLS_LANG
elif [[ ${DB} = CERT ]] then

export NLS_LANG=_.WE8ISO8859P15
echo $NLS_LANG
elif [[ ${DB} = TRNJP ]] then
# export NLS_LANG=_.JA16SJIS

export NLS_LANG=
echo $NLS_LANG
elif [[ ${DB} = INTTSTSC ]] then

export NLS_LANG=_.ZHS16GBK
echo $NLS_LANG
elif [[ ${DB} = TRNTC ]] then
# export NLS_LANG=_.ZHT16BIG5

export NLS_LANG=
echo $NLS_LANG
elif [[ ${DB} = TRNKO ]] then
# export NLS_LANG=_.KO16KSC5601

export NLS_LANG=
echo $NLS_LANG
else

echo "UNKNOWN DB"
exit 2
fi

export ORACLE_SID=${DB}
export PIPEFILE=mypipeimp${ORACLE_SID}
export IMPFILE=$2
export LOGFILE=imp${ORACLE_SID}_${RUNDT}.log

rm $PIPEFILE

/etc/mknod $PIPEFILE p

${UNCOMPRESS} < ${IMPFILE} > ${PIPEFILE} &

${ORACLE_HOME}/bin/imp parfile=imp${ORACLE_SID}.par file=${PIPEFILE}
log=${LOGFILE}

rm ${PIPEFILE}

${MAILER} -s "`hostname`: ${ORACLE_SID} import completed" ${MAILEE} <
${LOGFILE}

File Name :SID.par -->

userid=username/password_at_SID
buffer=2000000
compress=no
consistent=n

SET THE ORACLE SID FOR WHICH YOU WANT THE EXPORT TO BE TAKEN !!! THIS IS ONLY APPLICABLE IF YOU HAVE MANY INSTANCES RUNNING IN THE SYSTEM $ set ORACLE_SID=SID <-- Operating Sytem Environment
LOGIN TO THE OS USER $ sys -u username
CHECK THE ENVIRONEMT $ env <-- check the ORACLE_SID
LIST AND EDIT THE FILE OF THE SID.par FILE !!! PLEASE CHANGE THE USERNAME/PASSWORDS IN THE PAR FILES $ ls *.par <-- check the path where the .par file is $ vi SID.par <-- For the instance we want to export make the changes, Ex:- username,password.
THEN CHECK THE EXPORT SCRIPT that is expstuff.ksh/impstuff.ksh filename $ ls *.ksh
DO THE EXPORT WITH THE BELOW COMMAND IN BACKGROUND <-- This is to avoid if the terminal go's away then still it can run in background. $nohup ./expstuff.ksh SIDNAME & <-- Then press Enter
tail -f nohup.out <-- Then press Enter and then on the screen you will able to see the object's that are being exported.
It even sends the mail to you, so please edit and give yours mail id.

Regards,
Terminator
Deloitte -DBA

astalavista wrote:

> Hi,
>
> I have to transfert a schema
> from a DB 9i  to a new DB 8i,
> unfortunately the DB9 is in AL32UTF8
>
> I use the exp/imp binaries of DB8, I did the exp/imp from the DB8

machine

>
> I tried a lot of combinaisons:
> exp:
> NLS=UTF8
> NLS=US7ASCII
>
> I created the DB8
> UTF8
> WE8ISO88591
> US7ASCII
>
> imp:
> NLS=UTF8
> NLS=US7ASCII
> NLS=WE8ISO8859P1
>
> with no success, with  errors like :
>
> Export file created by EXPORT:V08.01.07 via conventional path
> import done in UTF8 character set and US7ASCII NCHAR character set
> import server uses US7ASCII character set (possible charset

conversion)

> IMP-00067: Could not convert to server character set's handle
> IMP-00000: Import terminated unsuccessfully
>
>
> What is the right combinaison ???
> I have no special characters in DB9
> Can you help me ???
> Thanks in advance ...

Received on Mon Mar 28 2005 - 23:34:28 CST