Home -> Usenet -> comp.databases.oracle -> "Dan's Oracle7 Guide" - fullExpImp.sh

"Dan's Oracle7 Guide" - fullExpImp.sh

From: Daniel B. Bikle <dbikle_at_alumni.cco.caltech.edu>
Date: 07 Sep 1993 07:55:10 GMT
Message-ID: <DBIKLE.93Sep7005510_at_alumni.cco.caltech.edu> 







The following discussion is an excerpt from "Dan's Oracle7 Guide".



While the guide could not be described as a great literary work, it
might prove useful to those DBA's and Application Developers who
work with Oracle7.



The guide will be finished sometime in October.



If you want a copy, I'll put you on my mailing list.



The public domain, email, beta version is free.



If you have an extra $20, please send it to my favorite charity:



Amnesty International 322 8th ave. ny NY usa 10001.



All of the trademarks mentioned in this excerpt are owned by their
respective owners.



Of course, everything I create has no warranty.  If my software or
ideas cause you problems, feel free to send me hate mail.




-Dan




Daniel B. Bikle


dbikle_at_alumni.caltech.edu


415/854-9542


P.O. BOX 'D'



MENLO PARK CA 94026









#! /bin/sh



# fullExpImp.sh




echo '#######################################################################'


echo ' '



echo This is a demo of exporting a V6 dbs and then importing the V6 data
echo into a Oracle7 database.



echo Please remember to set the environment variables ORACLE_HOME,
echo ORACLE_SID, and TWO_TASK to values appropriate for your environment.

echo ' '
echo '#######################################################################'
echo 'Press Return  to continue or ^C to quit.'


read Answer


echo ' '; sleep 2



# We display another message.





cat << end_control_flow	####### embedded text #######



The control flow of this script is described below:


-Setup V6 environment
-Obtain definitions of the V6 redo logs and System tablespace
-Run a full dbs export of V6
-Create a shell script (rm_V6_files.sh) which removes the V6 dbs files, 


 redologs, and control files


-Create a shell script (bakV6.cpio.sh) which backs up the V6 dbs files, 
 redologs, and control files using cpio


-SHUTDOWN V6 so we can back it up


-Backup datafiles, redologs, control files, and export file with
 bakV6.cpio.sh

-Remove datafiles, redologs, and control files by running rm_V6_files.sh
-Setup Oracle7 environment
-Create a CREATE DATABASE script
-Create $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
-Run the CREATE DATABASE script
-Reference the rb segment RBTEMP in $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
-SHUTDOWN, STARTUP the new Oracle7 dbs
-Run an import into the new Oracle7 dbs





end_control_flow


####### embedded text #######


echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer




# We display another message.




cat << end_assumptions  ######### embedded text #########



This script assumes the conditions listed below:



-A V6 dbs is installed and running


-The following UNIX utilities are installed:


        cat, grep, awk, sed, and cpio


-Oracle7 software has been read from the distribution medium and manipulated
 by the Oracle installation script



end_assumptions


######### embedded text #########



echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer




echo setting up V6 environment


echo ' '; sleep 2



ORACLE_HOME=/q/o6


ORACLE_SID=x6


TWO_TASK=P:x6   # this specifies use of the pipe driver against the

                # x6 database; explicitly set here to avoid getting it
		# wrong from the parent shell





export ORACLE_SID ORACLE_HOME TWO_TASK



echo V6 environment variables:


echo ORACLE_HOME is $ORACLE_HOME


echo ORACLE_SID is $ORACLE_SID


echo TWO_TASK is $TWO_TASK

echo ' '; sleep 2
echo ' '
echo 'Press Return  to continue or ^C to quit.'


read Answer



echo Obtaining definitions of the V6 redo logs and System tablespace
echo since the export utility exports neither redo logs nor the
echo System tablespace definition.



echo So, we make a note of them now by placing the results of a query
echo into spool files named v6redo.lst and v6system.lst.



echo ' '; sleep 2



$ORACLE_HOME/bin/sqlplus / << EndOfsql


SET ECHO ON


SPOOL v6redo


SELECT



NAME FROM V\$LOGFILE



/


SPOOL OFF


SPOOL v6system


SELECT



FILE_NAME



,BLOCKS



FROM DBA_DATA_FILES



WHERE TABLESPACE_NAME = 'SYSTEM'



/


SPOOL OFF


EndOfsql



echo ' '


echo About to run a full dbs export.


echo We are assuming we have disk space for the export file.
echo 'Press Return  to continue or ^C to quit.'
read Answer


echo ' ';sleep 2



$ORACLE_HOME/bin/exp 'USERID=/ FULL=Y BUFFER=10485660 FILE=full6.dmp ROWS=y'



echo Creating a shell script which removes the V6 dbs files, redologs, and
echo control files



$ORACLE_HOME/bin/sqlplus / << EndOfsql


rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
set heading off


spool rm_V6_files.tmp1



rem selecting remove commands for the 


rem dbs & redolog files is pretty simple
SELECT



'rm ' || FILE_NAME


FROM DBA_DATA_FILES



/



SELECT



'rm ' || NAME


FROM V\$LOGFILE



/



spool off


spool ctl.tmp1



rem Selecting remove commands for the control files is more of a challenge
rem since the names are in a single record. The record is only 50 char
rem long which means some of the control files might get left behind, but
rem they are not big disk pigs so it might not be a big deal.  If it is a
rem big deal, you will need to remove the control files manually after
rem obtaining their names from the init.ora.



rem Oh, one more detail, the file names are separated by ",".



SELECT 



'rm '||VALUE


FROM V\$PARAMETER



WHERE NAME='control_files'


/


spool off



rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
EndOfsql



# Now, we text process the spool files into a shell script.


# First, we put in some comments.




cat > rm_V6_files.sh << end_rm_V6_files

############################################




# This script removes V6 files.





# Remove dbs and redolog files 1st.



############################################


end_rm_V6_files



# Then, we grep out lines where the 1st token is a left adjusted rm.


cat rm_V6_files.tmp1 | egrep '^rm ' > rm_V6_files.tmp2



# Next, we remove all trailing spaces from each line


cat rm_V6_files.tmp2 | sed '1,$s/ *$//' >>rm_V6_files.sh



# Then, we put in some comments




cat >> rm_V6_files.sh << end_rm_V6_files2

############################################################################




# Remove control files 2nd.

# Some of the rm commands below might attempt to rm a bogus path.

# No need to worry though, rm has error handling, this script does not.


# Do, however, feel free to put error handling in this script.



############################################################################


end_rm_V6_files2



# We process the record from V$PARAMETER into a set of commands which

# remove control files.

# We face 2 challenges:

#  -the record might contain the default value for the control file

#  -the record might contain a bogus value for a control file since

#  the last token in the record might be truncated due to the record's

#  short 50 char length


#  -the file names are separated by ","





# First we grep out lines where the 1st token is a left adjusted rm.


cat ctl.tmp1 | egrep '^rm ' > ctl.tmp2



# Next, we rip out the commas




cat ctl.tmp2 | sed 's/,//g' > ctl.tmp3



# Next, we transform the default value into one the shell can understand


# We replace ? with $ORACLE_HOME




cat ctl.tmp3 | sed 's/?/$ORACLE_HOME/g' > ctl.tmp4



# Next, We replace _at_ with ${ORACLE_SID}


cat ctl.tmp4 | sed 's/_at_/${ORACLE_SID}/' > ctl.tmp5



# complete the shell script by removing trailing spaces


cat ctl.tmp5 | sed 's/ *$//' >> rm_V6_files.sh


echo ' ' >> rm_V6_files.sh
echo '# End of rm_V6_files.sh ' >> rm_V6_files.sh
echo ' ' >> rm_V6_files.sh





echo The shell script has been built and is displayed below:
echo ' '



cat rm_V6_files.sh



# We turn of its execute bit, it's a powerful script.  To run it


# we will just type in sh rm_V6_files.sh.





chmod 644 rm_V6_files.sh



echo Next, we create a script which creates a backup script, we need to
echo backup datafiles, redologs, control files,  and export file.
echo If the path to the 1st control file is longer than 50 characters,
echo it will not get backed up by this script.  We need to back it up
echo manually.


echo 'Press Return  to continue or ^C to quit.'
read Answer


echo ' ';sleep 2



echo All the files we need are listed in rm_V6_files.sh so we will get
echo them from that file.


echo ' ';sleep 2



# First we grep out lines where the 1st token is a left adjusted rm.


cat rm_V6_files.sh | egrep '^rm ' > bakV6.tmp1



# Next, we remove the rm and expand $ORACLE_HOME if it's in the path


# to the control file. Also, only one control file is enough.


cat bakV6.tmp1 | sed '1,$s/^rm /echo /' |awk '{print $1 " " $2}'> bakV6.tmp2



# The next command expands $ORACLE_HOME, and $ORACLE_SID


sh bakV6.tmp2 > bakV6.cpio.tmp



# We build a cpio script being careful of the $ in the sed command.





cat > bakV6.cpio.sh << end_bakV6cpiosh

######################################################################




# This is a simple cpio script which backs up relative path names.





# To make the path names relative, we remove the first /.


 


# We now put the path to the export file in the cpio file list.

# We need to ensure the path corresponds to the path listed above


# when we run the export.





echo `pwd`/full6.dmp >> bakV6.cpio.tmp



# Now we remove the first / in each file name


cat bakV6.cpio.tmp | grep '/' | sed '1,\$s:^/::' > /tmp/bakV6.cpio.lst



# Running cpio can be a bit subtle. We use it here by feeding it a list of


# file names which are contained in bakV6.cpio.lst.





# For now we will hard code the cpio destination.





# We want all path names to be relative to root.


cd /



cat /tmp/bakV6.cpio.lst | cpio -ov | compress > /q/bakV6.cpio.Z



# Use the following command to verify the contents of the backup:


# 		zcat /q/bakV6.cpio.Z | cpio -itv





# Use the following command to restore the contents of the backup:


# 		cd /; zcat /q/bakV6.cpio.Z | cpio -idv




######################################################################


end_bakV6cpiosh



echo ' '


echo The backup script is displayed below:
echo ' '


cat bakV6.cpio.sh


echo ' '


echo ' '



# Now, we shut down V6 so we can back it up.


echo Now, we shut down V6 so we can back it up.
echo 'Press Return  to continue or ^C to quit.'
read Answer


echo ' ';sleep 2



$ORACLE_HOME/bin/sqldba << EndSqldba



SHUTDOWN IMMEDIATE


EndSqldba



# Now, we backup datafiles, redologs, control files, and export file


# with backup script.





echo ' '


echo ' '


echo Now, we backup datafiles, redologs, control files, and export file
echo with backup script.


echo 'Press Return  to continue or ^C to quit.'
read Answer


echo ' ';sleep 2



sh bakV6.cpio.sh



echo The backup of the datafiles, redologs, control files, and export
echo file is complete.


echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer



# We remove the V6 dbs files now if we want to.  The main reason we

# might want to remove them is to reclaim disk space.  If we have

# abundant disk space we may want to leave them on disk, but in a


# different location since the Import will overwrite some of them.





echo We remove the V6 dbs files now if we want to.  The main reason we
echo might want to remove them is to reclaim disk space.  If we have
echo abundant disk space we may want to leave them on disk, but in a
echo different location since the Import will overwrite some of them.
echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer



sh rm_V6_files.sh



echo This script is at a major turning point.  We have just exported
echo the data from the V6 dbs and now we want to turn our attention towards
echo the Oracle7 dbs.



echo setting up Oracle7 environment variables



# setup Oracle7 environment




ORACLE_HOME=/q/o7


ORACLE_SID=x72

TWO_TASK=''     # This specifies use of the pipe driver against the
                # x72 database; explicitly set here to avoid getting it
		# wrong from the parent shell.
		# It would be nice if we knew how to un-set an env variable
		# in the Bourne shell.




export ORACLE_SID ORACLE_HOME TWO_TASK



echo Oracle7 environment variables:


echo ORACLE_HOME is $ORACLE_HOME


echo ORACLE_SID is $ORACLE_SID


echo TWO_TASK is $TWO_TASK


echo ' ';sleep 2



# create a CREATE DATABASE script

# I wish we could grep this out of the export file, but we can't

# since it's not exported.

# One good place to rummage around for the syntax is the "alert log"

# which is sometimes here: $ORACLE_HOME/rdbms/log/_${ORACLE_SID}.log

# Also, the Oracle7 install will sometimes leave a create dbs script 


# in the following location: $ORACLE_HOME/dbs.





echo Creating a CREATE DATABASE script


echo ' ';sleep 2


cat > cr_x72_dbs.sql << EndOfcr_x72_dbs


rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
rem  cr_x72_dbs.sql


rem
  


rem  This is a create dbs script to be run from Oracle7 sqldba utility.



CONNECT INTERNAL



SHUTDOWN ABORT



STARTUP NOMOUNT


CREATE DATABASE "x72"


CONTROLFILE REUSE



DATAFILE



'/q/x72/systx72.dbf'	SIZE   5M REUSE


LOGFILE


'/q/x72/log1x72.dbf'	SIZE 500K REUSE,
'/q/x72/log2x72.dbf'	SIZE 500K REUSE




/



rem  The previous statement will trigger scripts specified by an init.ora
rem  parameter named init_sql_files.  We have no way of predicting what
rem  init_sql_files will be set to so we will assume it's set to this:

rem  	init_sql_files                 = (?/dbs/sql.bsq)





rem  Now, run catalog.sql. If it was listed in init_sql_files, no big deal
rem  it doesn't hurt to run it twice.


_at_?/rdbms/admin/catalog.sql



rem We may put more scripts below if we want, many are found 
rem in $ORACLE_HOME/rdbms/admin.



rem create a rollback segment so we can make more



CREATE ROLLBACK SEGMENT RBTEMP



/



rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
EndOfcr_x72_dbs




echo ' '


echo The Create Database script is now written and displayed below.
echo ' ';sleep 2


echo ' '


cat cr_x72_dbs.sql


echo ' '


echo ' ';sleep 2



echo We need to create the following file:
echo	$ORACLE_HOME/dbs/init${ORACLE_SID}.ora



echo We will create it now.


cat > $ORACLE_HOME/dbs/init${ORACLE_SID}.ora  << endInitOra

############################################################################




# This is the init.ora a special file needed by Oracle7.

# Sometimes this file is referred to as the initialization file.


# This file is read when the dbs is started up.




db_name		= $ORACLE_SID
init_sql_files	= (?/dbs/sql.bsq)



# We wish to allow ops$ accounts to login over network without a password.


REMOTE_OS_AUTHENT	= TRUE





# We could specify more parameters if we want.



##############################################################################


endInitOra



echo We now display $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
echo ' '


cat $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer



echo We now create an Oracle7 dbs.


echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer



echo ' '


echo ' ';sleep 2



$ORACLE_HOME/bin/sqldba _at_cr_x72_dbs.sql



echo ' '


echo We now reference the rb segment RBTEMP in the init.ora.
echo 'rollback_segments	= (RBTEMP)' >> $ORACLE_HOME/dbs/init${ORACLE_SID}.ora



echo To make the rb segment active, we need to bounce the dbs.
echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer



$ORACLE_HOME/bin/sqldba << endSqldba


CONNECT INTERNAL



SHUTDOWN



STARTUP



endSqldba



echo ' '



echo The Oracle7 dbs is now ready for full dbs import.
echo ' '


echo 'Press Return  to continue or ^C to quit.'
read Answer



echo ' ';sleep 2


$ORACLE_HOME/bin/imp \


'USERID=system/manager BUFFER=12345 FILE=full6.dmp COMMIT=Y FULL=Y'


echo ' '
echo ' '
echo ' '
echo ' '
echo ' '
echo ' '
echo ' '





echo If the new dbs looks okay, we need to reference the new rollback
echo segments in the init.ora, bounce the dbs, and then drop rollback
echo segment RBTEMP.


echo ' '


echo ' '


echo done with fullExpImp.sh


echo ' '



# end of fullExpImp.sh







Output from the above script is displayed below:

////////////////////////////////////////////////







This file was captured by an emacs shell buffer.





ava2-dan-% fullExpImp.sh

#######################################################################
 




This is a demo of exporting a V6 dbs and then importing the V6 data
into a Oracle7 database.


Please remember to set the environment variables ORACLE_HOME,
ORACLE_SID, and TWO_TASK to values appropriate for your environment.
 

#######################################################################


Press Return  to continue or ^C to quit.

 



The control flow of this script is described below:


-Setup V6 environment
-Obtain definitions of the V6 redo logs and System tablespace
-Run a full dbs export of V6
-Create a shell script (rm_V6_files.sh) which removes the V6 dbs files, 


 redologs, and control files


-Create a shell script (bakV6.cpio.sh) which backs up the V6 dbs files, 
 redologs, and control files using cpio


-SHUTDOWN V6 so we can back it up


-Backup datafiles, redologs, control files, and export file with
 bakV6.cpio.sh

-Remove datafiles, redologs, and control files by running rm_V6_files.sh
-Setup Oracle7 environment
-Create a CREATE DATABASE script
-Create /q/o7/dbs/initx7.ora
-Run the CREATE DATABASE script
-Reference the rb segment RBTEMP in /q/o7/dbs/initx7.ora
-SHUTDOWN, STARTUP the new Oracle7 dbs
-Run an import into the new Oracle7 dbs

 




Press Return  to continue or ^C to quit.




This script assumes the conditions listed below:



-A V6 dbs is installed and running


-The following UNIX utilities are installed:


        cat, grep, awk, sed, and cpio


-Oracle7 software has been read from the distribution medium and manipulated
 by the Oracle installation script

 


Press Return  to continue or ^C to quit.



setting up V6 environment
 


V6 environment variables:


ORACLE_HOME is /q/o6


ORACLE_SID is x6


TWO_TASK is P:x6
 
 


Press Return  to continue or ^C to quit.



Obtaining definitions of the V6 redo logs and System tablespace
since the export utility exports neither redo logs nor the
System tablespace definition.


So, we make a note of them now by placing the results of a query
into spool files named v6redo.lst and v6system.lst.
 



SQL*Plus: Version 3.0.9.1.2 - Production on Sun Sep  6 00:17:30 1992



Copyright (c) Oracle Corporation 1979, 1989.  All rights reserved.




Connected to:


ORACLE RDBMS V6.0.33.2.3, transaction processing option - Production
PL/SQL V1.0.33.1.1 - Production



00:17:31 OPS$DAN v6sql> 00:17:31 OPS$DAN v6sql> 00:17:31 OPS$DAN v6sql> 00:17:31 OPS$DAN v6sql> 00:17:31   2  00:17:31   3  
NAME





/q/o6/dbs/log1x6.dbf


/q/o6/dbs/log2x6.dbf



00:17:32 OPS$DAN v6sql> 00:17:32 OPS$DAN v6sql> 00:17:32 OPS$DAN v6sql> 00:17:32 OPS$DAN v6sql> 00:17:32   2  00:17:32   3  00:17:32   4  00:17:32   5  00:17:32   6  
FILE_NAME





    BLOCKS





/q/o6/dbs/dbsx6.dbf


      5120




00:17:32 OPS$DAN v6sql> 00:17:32 OPS$DAN v6sql> 00:17:32 OPS$DAN v6sql> Disconnected from ORACLE RDBMS V6.0.33.2.3, transaction processing option - Production
PL/SQL V1.0.33.1.1 - Production
 


About to run a full dbs export.


We are assuming we have disk space for the export file.
Press Return  to continue or ^C to quit.

 




Export: Version 6.0.33.2.3 - Production on Sun Sep  6 00:17:37 1992



Copyright (c) Oracle Corporation 1979, 1989.  All rights reserved.





Connected to: ORACLE RDBMS V6.0.33.2.3, transaction processing option - Production
PL/SQL V1.0.33.1.1 - Production




Warning: grants on tables/views/sequences not exported
Warning: constraints on tables not exported



About to export the entire database ...

. exporting tablespace definitions
. exporting user definitions
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export PUBLIC's tables ...
. about to export SYSTEM's tables ...
. exporting table                           HELP
     7751 rows exported
. exporting table                PRODUCT_PROFILE        0 rows exported
. exporting table                   USER_PROFILE        0 rows exported
. about to export RICARDO's tables ...
. about to export OPS$DAN's tables ...
. exporting table                        V6PHONE        8 rows exported
. exporting table                    V7DATAFILES        0 rows exported
. about to export OPS$MIKE's tables ...
. exporting table                PRODUCT_PROFILE        0 rows exported
. exporting table                   USER_PROFILE        0 rows exported
. about to export OPS$RICARDO's tables ...
. about to export OPS$ROOT's tables ...
. about to export OPS$O6's tables ...
. about to export OPS$MYA's tables ...
. exporting table                           PROJ        0 rows exported
. exporting table                         SUBSET        0 rows exported
. exporting table                            XYZ        2 rows exported


. exporting synonyms and views


. exporting default and system auditing options
Export terminated successfully.


Creating a shell script which removes the V6 dbs files, redologs, and
control files



SQL*Plus: Version 3.0.9.1.2 - Production on Sun Sep  6 00:17:50 1992



Copyright (c) Oracle Corporation 1979, 1989.  All rights reserved.




Connected to:


ORACLE RDBMS V6.0.33.2.3, transaction processing option - Production
PL/SQL V1.0.33.1.1 - Production



00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51   2  00:17:51   3  00:17:51   4  
rm /q/o6/dbs/dbsx6.dbf


rm /usr/local/o6/scratch.dbf



00:17:51 OPS$DAN v6sql> 00:17:51 OPS$DAN v6sql> 00:17:51   2  00:17:51   3  00:17:51   4  
rm /q/o6/dbs/log1x6.dbf


rm /q/o6/dbs/log2x6.dbf



00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52   2  00:17:52   3  00:17:52   4  00:17:52   5  
rm ?/dbs/cntrl_at_.dbf



00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> 00:17:52 OPS$DAN v6sql> Disconnected from ORACLE RDBMS V6.0.33.2.3, transaction processing option - Production
PL/SQL V1.0.33.1.1 - Production


The shell script has been built and is displayed below:
 

############################################




# This script removes V6 files.





# Remove dbs and redolog files 1st.



############################################
rm /q/o6/dbs/dbsx6.dbf
rm /usr/local/o6/scratch.dbf




rm /q/o6/dbs/log1x6.dbf


rm /q/o6/dbs/log2x6.dbf

############################################################################




# Remove control files 2nd.

# Some of the rm commands below might attempt to rm a bogus path.

# No need to worry though, rm has error handling, this script does not.


# Do, however, feel free to put error handling in this script.



############################################################################


rm $ORACLE_HOME/dbs/cntrl${ORACLE_SID}.dbf
 


# End of rm_V6_files.sh 


 


Next, we create a script which creates a backup script, we need to
backup datafiles, redologs, control files, and export file.
If the path to the 1st control file is longer than 50 characters,
it will not get backed up by this script. We need to back it up
manually.


Press Return  to continue or ^C to quit.
 


All the files we need are listed in rm_V6_files.sh so we will get
them from that file.
 
 


The backup script is displayed below:
 

######################################################################




# This is a simple cpio script which backs up relative path names.





# To make the path names relative, we remove the first /.


 


# We now put the path to the export file in the cpio file list.

# We need to ensure the path corresponds to the path listed above


# when we run the export.





echo /q/dan/o7book/v6to_o7/full6.dmp >> bakV6.cpio.tmp



# Now we remove the first / in each file name


cat bakV6.cpio.tmp | grep '/' | sed '1,$s:^/::' > /tmp/bakV6.cpio.lst



# Running cpio can be a bit subtle. We use it here by feeding it a list of


# file names which are contained in bakV6.cpio.lst.





# For now we will hard code the cpio destination.





# We want all path names to be relative to root.


cd /



cat /tmp/bakV6.cpio.lst | cpio -ov | compress > /q/bakV6.cpio.Z



echo Use the following command to verify the contents of the backup:
echo 		zcat /q/bakV6.cpio.Z | cpio -itv



echo Use the following command to restore the contents of the backup:
echo 		cd /; zcat /q/bakV6.cpio.Z | cpio -idv


######################################################################
 
 




Now, we shut down V6 so we can back it up.
Press Return  to continue or ^C to quit.

 



SQL*DBA: Version 6.0.33.2.3 - Production on Sun Sep  6 00:19:42 1992



Copyright (c) Oracle Corporation 1979, 1989.  All rights reserved.



ORACLE RDBMS V6.0.33.2.3, transaction processing option - Production
PL/SQL V1.0.33.1.1 - Production



SQLDBA> SQLDBA> Database closed.


Database dismounted.


ORACLE instance shut down.


SQLDBA> SQLDBA> 



SQL*DBA complete.
 
 


Now, we backup datafiles, redologs, control files, and export file
with backup script.


Press Return  to continue or ^C to quit.

 


q/o6/dbs/dbsx6.dbf


usr/local/o6/scratch.dbf

q/o6/dbs/log1x6.dbf
q/o6/dbs/log2x6.dbf
q/o6/dbs/cntrlx6.dbf
q/dan/o7book/v6to_o7/full6.dmp




43890 blocks



The backup of the datafiles, redologs, control files, and export
file is complete.
 


Press Return  to continue or ^C to quit.



We remove the V6 dbs files now if we want to. The main reason we
might want to remove them is to reclaim disk space. If we have
abundant disk space we may want to leave them on disk, but in a
different location since the Import will overwrite some of them.
 


Press Return  to continue or ^C to quit.


rm: override protection 640 for /q/o6/dbs/dbsx6.dbf? y
rm: override protection 644 for /usr/local/o6/scratch.dbf? y
rm: override protection 640 for /q/o6/dbs/log1x6.dbf? y
rm: override protection 640 for /q/o6/dbs/log2x6.dbf? y
rm: override protection 640 for /q/o6/dbs/cntrlx6.dbf? y





This script is at a major turning point. We have just exported
the data from the V6 dbs and now we want to turn our attention towards
the Oracle7 dbs.


setting up Oracle7 environment variables
Oracle7 environment variables:


ORACLE_HOME is /q/o7


ORACLE_SID is x72


TWO_TASK is
 


Creating a CREATE DATABASE script
 
 


The Create Database script is now written and displayed below.
 
 


rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
rem  cr_x72_dbs.sql


rem
  


rem  This is a create dbs script to be run from Oracle7 sqldba utility.



CONNECT INTERNAL



SHUTDOWN ABORT



STARTUP NOMOUNT


CREATE DATABASE "x72"


CONTROLFILE REUSE



DATAFILE



'/q/x72/systx72.dbf'	SIZE   5M REUSE


LOGFILE


'/q/x72/log1x72.dbf'	SIZE 500K REUSE,
'/q/x72/log2x72.dbf'	SIZE 500K REUSE




/



rem  The previous statement will trigger scripts specified by an init.ora
rem  parameter named init_sql_files.  We have no way of predicting what
rem  init_sql_files will be set to so we will assume it's set to this:

rem  	init_sql_files                 = (?/dbs/sql.bsq)





rem  Now, run catalog.sql. If it was listed in init_sql_files, no big deal
rem  it doesn't hurt to run it twice.


_at_?/rdbms/admin/catalog.sql



rem We may put more scripts below if we want, many are found 
rem in /q/o7/rdbms/admin.



rem create a rollback segment so we can make more



CREATE ROLLBACK SEGMENT RBTEMP



/



rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
 
 


We need to create the following file:


/q/o7/dbs/initx72.ora


We will create it now.


We now display /q/o7/dbs/initx72.ora
 

############################################################################




# This is the init.ora a special file needed by Oracle7.

# Sometimes this file is referred to as the initialization file.


# This file is read when the dbs is started up.




db_name		= x72
init_sql_files	= (?/dbs/sql.bsq)



# We wish to allow ops$ accounts to login over network without a password.


REMOTE_OS_AUTHENT	= TRUE





# We could specify more parameters if we want.



##############################################################################
 




Press Return  to continue or ^C to quit.



We now create an Oracle7 dbs.
 


Press Return  to continue or ^C to quit.

 
 



SQL*DBA: Release 7.0.12.1.0 - Production on Sun Sep  6 00:25:46 1992



Copyright (c) Oracle Corporation 1979, 1992.  All rights reserved.



ORACLE7 Server Release 7.0.12.1.0 - Production
With the procedural and distributed options
PL/SQL Release 2.0.14.0.1 - Production



Connected.


ORACLE instance shut down.


ORACLE instance started.


Statement processed.


Statement processed.



lots deleted


SQLDBA> 
SQLDBA> CREATE ROLLBACK SEGMENT RBTEMP
     2> /




Statement processed.

SQLDBA> 
SQLDBA> rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem rem 
SQLDBA> 




SQL*DBA complete.
 


We now reference the rb segment RBTEMP in the init.ora.
To make the rb segment active, we need to bounce the dbs.
 


Press Return  to continue or ^C to quit.




SQL*DBA: Release 7.0.12.1.0 - Production on Sun Sep  6 00:36:44 1992



Copyright (c) Oracle Corporation 1979, 1992.  All rights reserved.



ORACLE7 Server Release 7.0.12.1.0 - Production
With the procedural and distributed options
PL/SQL Release 2.0.14.0.1 - Production



SQLDBA> Connected.


SQLDBA> Database closed.


Database dismounted.


ORACLE instance shut down.


SQLDBA> ORACLE instance started.


Database mounted.


Database opened.

Total System Global Area       3967100 bytes
              Fixed Size         38260 bytes
           Variable Size       3830536 bytes
        Database Buffers         65536 bytes
            Redo Buffers         32768 bytes


SQLDBA> 



SQL*DBA complete.
 


The Oracle7 dbs is now ready for full dbs import.
 


Press Return  to continue or ^C to quit.

 



Import: Release 7.0.12.1.0 - Production on Sun Sep  6 00:37:14 1992



Copyright (c) Oracle Corporation 1979, 1992.  All rights reserved.




Connected to: ORACLE7 Server Release 7.0.12.1.0 - Production
With the procedural and distributed options
PL/SQL Release 2.0.14.0.1 - Production



Export file created by EXPORT:V06.00.33



Warning: the objects were exported by OPS$DAN, not by you


. importing OPS$DAN's objects into OPS$DAN
. . importing table "HELP"                                  7751 rows imported
. . importing table "PRODUCT_PROFILE"                          0 rows imported
. . importing table "USER_PROFILE"                             0 rows imported
. importing OPS$DAN's objects into OPS$DAN
. . importing table "V6PHONE"                                  8 rows imported
. . importing table "V7DATAFILES"                              0 rows imported
. importing OPS$MIKE's objects into OPS$MIKE
. . importing table "PRODUCT_PROFILE"                          0 rows imported
. . importing table "USER_PROFILE"                             0 rows imported
. importing OPS$MYA's objects into OPS$MYA
. . importing table "PROJ"                                     0 rows imported
. . importing table "SUBSET"                                   0 rows imported
. . importing table "XYZ"                                      2 rows imported
. importing SYSTEM's objects into SYSTEM
. importing OPS$MIKE's objects into OPS$MIKE
. importing SYSTEM's objects into SYSTEM


IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "TAB" FOR "SYS"."TAB""


. importing OPS$O6's objects into OPS$O6
. importing OPS$DAN's objects into OPS$DAN
. importing SYSTEM's objects into SYSTEM


IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "AUDIT_ACTIONS" FOR "SYS"."AUDIT_ACTIONS""

IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "USER_AUDIT_TRAIL" FOR "SYS"."USER_AUDIT_TRAIL""

IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "DUAL" FOR "SYS"."DUAL""

IMP-00015: following statement failed because the object already exists:
 "CREATE PUBLIC SYNONYM "CAT" FOR "SYS"."USER_CATALOG""

 "CREATE SYNONYM "TAB" FOR "SYS"."TAB""



LOTS DELETED




 "CREATE SYNONYM "COL" FOR "SYS"."COL""



IMP-00015: following statement failed because the object already exists:
 "CREATE SYNONYM "TABQUOTAS" FOR "SYS"."TABQUOTAS""

IMP-00015: following statement failed because the object already exists:
 "CREATE SYNONYM "SYSFILES" FOR "SYS"."SYSFILES""

IMP-00015: following statement failed because the object already exists:
 "CREATE SYNONYM "PUBLICSYN" FOR "SYS"."PUBLICSYN""

. importing OPS$MIKE's objects into OPS$MIKE
. importing OPS$O6's objects into OPS$O6
IMP-00041: Warning: object created with compilation warnings

 "CREATE FORCE VIEW "OPS$O6"."PRODUCT_PRIVS"                          ("PRODU"
 "CT", "USERID", "ATTRIBUTE", "SCOPE", "NUMERIC_VALUE", "CHAR_VALUE", "DATE_V"
 "ALUE", "LONG_VALUE") as "
 "select product, userid, attribute, scope,"
 "         numeric_value, char_value, date_value, long_value"


 "  from product_profile"


 "  where userid = 'PUBLIC' or user like userid"
IMP-00041: Warning: object created with compilation warnings

 "CREATE FORCE VIEW "OPS$O6"."USER_PRIVS"                          ("PRODUCT""
 ", "USERID", "PROFILE", "ATTRIBUTE", "NUMERIC_VALUE", "CHAR_VALUE", "DATE_VA"
 "LUE", "LONG_VALUE") as "
 "select product, userid, profile, attribute,"
 "         numeric_value, char_value, date_value, long_value"
 "  from user_profile"




 "  where userid = user"


 "  with check option"


Import terminated successfully with warnings.
 
 
 
 
 
 
 


If the new dbs looks okay, we need to reference the new rollback
segments in the init.ora, bounce the dbs, and then drop rollback
segment RBTEMP.
 
 


done with fullExpImp.sh
 

ava2-dan-% 
ava2-dan-% 
ava2-dan-% 


Received on Tue Sep 07 1993 - 09:55:10 CEST












Original text of this message