Automate SQL statements


 
Thread Tools Search this Thread
Operating Systems Solaris Automate SQL statements
# 1  
Old 01-03-2008
Automate SQL statements

Hello,
On unix side, I have written below script for taking oracle db backup. But when I tried to execute it - i could not execute the sql statements from unix.
Please suggest
------------------------
$ more bkp.sh
#!/bin/ksh
# make sure database is shutdown cleanly
sqlplus '/ as sysdba' <<eof
{
shutdown immediate;
startup force dba pfile=$ORACLE_HOME/dbs/init.ora;
shutdown immediate;
}
exit;
eof
#Mount the database and start backup
run {
startup mount pfile=$ORACLE_HOME/dbs/init.ora;
}
# Backup datafile, controlfile and archivelogs
rman target=/ << EOF
RUN {
allocate channel ch1 type
disk format '/u00/oradata/backup/%d_DB_%u_%s_%p_%t';
backup database include current controlfile
tag = '1_daily_backup';
release channel ch1;
}
EXIT;
EOF
# Open the database
run {
alter database open;
}
# Archive all logfiles including current
run {
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
}
# Backup outdated archlogs and delete them
rman target=/ << EOF
RUN {
allocate channel ch1 type
disk format '/u00/oradata/backup/%d_DB_%u_%s_%p_%t';
backup archivelog
until time 'Sysdate-2' all
delete input;
release channel ch1;
}
EXIT;
EOF
# Backup remaining archlogs
rman target=/ << EOF
RUN {
allocate channel ch1 type
disk format '/u00/oradata/backup/%d_DB_%u_%s_%p_%t';
backup archivelog all;
release channel ch1;

}
EXIT;
EOF
------------------------

Thank you!
# 2  
Old 01-03-2008
what do you mean could not execute the sql statements. what errors did you receive?

also, please use the code tags(the # icon) around your code.
# 3  
Old 01-03-2008
Ok - I get what was wrong in the script. I was running sql commands within run { } , i have modified and kept all sql statements within sqlplus '/ as sysdba' <<eof
{ ... } and all the rman related commands under rman target=/ << EOF
RUN { ... }

The script looks to be working fine now - Thanks. Except one below error in below piece:
sqlplus '/ as sysdba' <<eof
{
shutdown immediate;
startup mount;
}
exit;
eof

Error is:
SQL> SP2-0042: unknown command "}" - rest of line ignored.
# 4  
Old 01-03-2008
Thanks - The problem is resolved now and I learn that in unix script style - SQL commands dont require parenthesis and will run as below:
sqlplus '/ as sysdba' <<eof
alter database open;
ALTER SYSTEM ARCHIVE LOG CURRENT;
exit;
eof

Thank you for the hints.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Storing multiple sql queries output into variable by running sql command only once

Hi All, I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise. Eg : Select 'Query 1 output' from dual; Select 'Query 2 output' from dual; I want to... (3 Replies)
Discussion started by: Rokkesh
3 Replies

2. Shell Programming and Scripting

Create SQL DML insert statements from file using AWK or similar

Hi all. This is my first post on this forum. I've previously found great help in the huge knowledgebase that is here, but this time I have not been able to find a solution to my problem. I have a large text file that looks like this: typedef struct ABC_struct_nbr1_ { char attr1; /*... (0 Replies)
Discussion started by: Yagi Uda
0 Replies

3. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

4. Shell Programming and Scripting

Looping through a shell script with sql statements

Hello members, I'm working on the Solaris environment and the DB i'm using is Oracle 10g. Skeleton of what I'm attempting; Write a ksh script to perform the following. I have no idea how to include my sql query within a shell script and loop through the statements. Have therefore given a... (4 Replies)
Discussion started by: novice82
4 Replies

5. UNIX for Dummies Questions & Answers

store SQL statements and results in a file

Hello Guys... I want a small help from you guys. Actually in Oracle, we are having a utlity called spool through which can store whatever SQL statements executed and other queries and the output of those queries in a file So, similarly in Unix, if I start a session executing a number of Unix... (2 Replies)
Discussion started by: mraghunandanan
2 Replies

6. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

7. Shell Programming and Scripting

isql input file with multiple sql statements

I've got: isql -U $USERID -S $SERVER -D $DATABASE -i inputfile.sql -o outputfile.txt in inputfile I have: go sql#1 go sql#2 go sql#3 go I also tried without "go" and with";" instead which did not work SQL statements will work if I paste them directly into the script and use EOF ... (0 Replies)
Discussion started by: Cailet
0 Replies

8. Shell Programming and Scripting

put value of multiple sql statements into unix variables

i want to use multple sql count statements and store these count values in unix variable but in one connection only i.e. in only 1 time database should be hit ,which is the main requirement. (1 Reply)
Discussion started by: sw@pnil
1 Replies

9. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

10. Shell Programming and Scripting

Running remote shell script containing sql statements

I have a shell script which resides on three SCO machines containing some simple sqlplus statments. I need to run these scripts remotely. Currently, I am trying to use rsh to do so: rsh hostname myscript args The problem is that the arguments to the sqlplus statements in the remote shell... (4 Replies)
Discussion started by: Madbreaks
4 Replies
Login or Register to Ask a Question