How to run sql files automatically on aix box 2

[zircon06]

I have sqlfiles like createtable.sql Altertable.sql
createprocedure.sql
Alterprocedure.sql

Can anyone help me how to run these scripts automatically on a oracle database in aix 5.2 environment without manual intervention.

 

[p5wizard]

Read this FAQ in the UNIX scripting forum: faq822-2218


HTH,

p5wizard

 

[khalidaaa]

View this link

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b15658/ldr_demo.htm

and if you want to schedule these scripts to run then you have to use crontab on aix

http://www.hmug.org/man/5/crontab.php

If it is still not clear, let me know
Regards,
Khalid

 

[zircon06]

Thanks for the link but I'm stuck running upgrade.exe using sqlplus on Aix5.2 version. I'm looking for solution to connect sqlplus directly and run upgrade.exe file on database.

Thanks in advance

 

[KenCunningham]

upgrade.exe on AIX? Are you sure? Do you have the Oracle client installed on your PC? You should be able to connect via sqlplus from there.

I want to be good, is that not enough?

 

[zircon06]

Hi Ken

Not on Aix but using sqlplus. But I'm looking for code solution to connect sqlplus directly on Aix and run upgrade.exe without any manual intervention.

Thanks

 

[khalidaaa]

What is the upgrade.exe? is it a script? i can see that it is an executable! Could you explain its usage?

You can run sqlplus from anywhere as long as you have the SID and the host of the server you want to connect to! (tnsnames.ora)

by mentioning "without any manual intervention", all you need to is to schedule it (either with windows scheduler or by using aix crontab)

The links above describe the process of connecting using sqlplus! they are a good source in your case!

Regards,
Khalid

 

[zircon06]

Hi khalid,

Thanks for your note. Upgrade.exe file contain all the sqlfiles to upgrade oracle instance from one build version to another. I need to run this file from windows environment connect to aix unix box where oracle instance is running. Any suggested to connect and run this file from windows env?

 

[TSch]

Good morning,

I'm not sure whether this will help you with the upgrade.exe Problem, but we're using the following mechanism to run sql files automatically:

(Maybe you can adapt this to suit your needs).

1) We have a file - let's call it "statement.sql" containg the sql statement we want to run automatically

statement.sql
-------------

spool /scripts/statement
set underline off
set heading off
set linesize 300
set pagesize 5000
set feedback off

SELECT
tournr,
weight,
TO_CHAR(tourend_datum,'DD.MM.YYYY') DD_MM_YYYY,
tourend_time,
FROM
db.tour tour,
spool off


2) We have a file - let's call it "statement" that's actually executing the file "statement.sql". This file not only executes the statement, but also puts the output into a txt file.


statement
---------
ORACLE_HOME=/usr/oracle/8; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:/usr/lbin; export PATH
ORACLE_SID=<sid>; export ORACLE_SID
ORACLE_OWNER=<owner>; export ORACLE_OWNER
sqlplus << abgleich
user/password <-- here you have to enter your user and password in clear text !
@/scripts/statement.sql
quit
abgleich
tail +2 /scripts/statement1.lst > /statement2.txt
cat /statement2.txt|grep -v ^$ > /statement3.txt
rm /statement2.txt
chmod 777 /statement3.txt


3) We run the "statement" file periodically via crontab.


00 12 * * * /scripts/statement > /dev/null

Regards
Thomas

 

[khalidaaa]

I was trying to look into the internet on that but with no luck! I'm pretty sure that oracle is so organized that they have a document on that! Do you have access to meta link? you might find something useful!

Just wondering why do you need to automate this? If i understand correctly you want to upgrade the oracle instance! so that's one time job! isn't it?

what happends when you run this file from windows manually?

Regards,
Khalid

 

[zircon06]

Hi khalid,

Right now I' running it manually without any issues but trying to automate the process that is why I'm undergoing pain in automating the process

 

[khalidaaa]

Ok then if you are running it with no problems in windows then all you need to do is to schedule this!

To do that you have to go to start --> All Programs --> accessories --> System Tools --> Scheduled Tasks --> Add Scheduled Task

You can easily schedule that to run at your specified timing.

Regards,
Khalid

 

[zircon06]

Hi khalid,

The script should run on Aix unix box not on windows. Our oracle running on aix unix box so that I need to run upgraded oracle instance automatically instead of manual connection to sqlplus and running it.

Thanks

 

[khalidaaa]

Ok! I think it is not that clear to me!

Could you explain exactly what you are doing in step-by-step way so i can understand your question

You mentioned above that you can do this manually. What do you do right now that you want it to be scheduled?

Regards,
Khalid

 

[zircon06]

Hi khalid,

we have script upgrade.sql in order to do oracle database upgrade process. Currently I'm running it through sqlplus. What I'm looking for was solution : To run this script

To connect Aix unix box using Ip address, oracleinstance and sqlplus and run upgrade.sql file automatically by form of sh script without me connecting to sqlplus and running the script manually. Hope you understand my problem. I'm not unix savvy that is the problem

Thanks in advance

 

[khalidaaa]

Then follow TSch answer above!
On AIX you create a file (call it any thing)

# vi anything

Then type the following:

ORACLE_HOME=/usr/oracle/8; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:/usr/lbin; export PATH
ORACLE_SID=<sid>; export ORACLE_SID
ORACLE_OWNER=<owner>; export ORACLE_OWNER
sqlplus << END
user/password
@upgrade.sql
quit
END

Then issue the following:

# chmod u+x anything
# crontab -e

In the crontab you can then schedule this script to run whenever you want!

for example if you want to run this every day at 4 AM you do this as the last (or wherever) line in crontab:

0 4 * * * /path_of_file_anything/anything > /dev/null

I hope it is clear for now! If not please feel free to ask

Regards,
Khalid

 

[KenCunningham]

upgrade.sql eh? So much for upgrade.exe!!

I want to be good, is that not enough?

 

[zircon06]

Hi Khalid TSch or anyone

Now my confusion was cleared regard running upgrade.sql on aix unix box. But struggling to know how to run multiple files in order For example upgrade.sql,table.sql,view.sql files in order with error handling in case some script didnot run properly. Any thoughs on this ?

Thanks in advance

 

[khalidaaa]

You can use the same script but put more sql files to be run in order

ORACLE_HOME=/usr/oracle/8; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin:/usr/lbin; export PATH
ORACLE_SID=<sid>; export ORACLE_SID
ORACLE_OWNER=<owner>; export ORACLE_OWNER
sqlplus << END
user/password
@upgrade.sql
@table.sql
@view.sql
quit
END

You can catch the errors inside the scheduled job:

# crontab -e

0 4 * * * /path_of_file_anything/anything > /dev/null 2>/path_to_logs/errors.log

Now you can view the errors.log file later on (daily for example) to see the errors in case there are some (hope not

Regards,
Khalid