cancel
Showing results for 
Search instead for 
Did you mean: 

script help oracle

file system
Frequent Advisor

script help oracle

Dear experts.

#!/usr/bin/ksh
${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF > /dev/null
alter tablespace ${TABLESPACE_NAME} begin backup;
exit

this script is supposed to login oracle and
tablespace is altered , when i run the script, it is connected but
dose not know sql command alter..

Beginning backup of tablespace MAITM010_IDX

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 7 14:18:10 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> SP2-0734: unknown command beginning "MAITM010_T..." - rest of line ignored.
SQL> SP2-0042: unknown command "SYSAUX" - rest of line ignored.
SQL> SP2-0042: unknown command "SYSTEM" - rest of line ignored.
SQL> SP2-0042: unknown command "UNDOTBS1" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.

how can I run the script to run.
8 REPLIES
Yogeeraj_1
Honored Contributor

Re: script help oracle

hi,

to be able to troubleshoot this script better, add the following to the commands:
spool /tmp/log.txt
...
...
spool off;

and then upload the log.txt so that we can have a better look.


In the meanwhile, I have tried this simple script here and it works fine:

#!/usr/bin/ksh
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF >> /dev/null
spool /tmp/log.txt
select sysdate from dual;
exit;
EOF


revert.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: script help oracle

hi again,

since you are using Oracle 10g, it is much better and easy to do RMAN backup.

please refer to my post in your previous thread:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1097393


kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Peter Godron
Honored Contributor

Re: script help oracle

Hi,
I assume you have set the value for ${TABLESPACE_NAME} somewehere else, but have assigned the list of tablespaces, rather the
individual value.

If your do a
echo ${TABLESPACE_NAME}
just before your call to sqlplus, you should find the list includes SYSAUX SYSTEM UNDOTBS1.

You have to call the alter statement in a loop, or have multiple alter statement with a different tablespace_name value.

You are also missing a EOF in the line following the exit statement.

Arturo Galbiati
Esteemed Contributor

Re: script help oracle

Hi,
try:

#!/usr/bin/ksh
TABLESPACE_NAME=MAITM010_IDX
${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF > /dev/null
alter tablespace ${TABLESPACE_NAME} begin backup;
exit
EOF

HTH,
Art
Hein van den Heuvel
Honored Contributor

Re: script help oracle

The command loolks ok, but it is best to verify by just giving the result directly to SQLplus. Does it work as expected?

Next, use the SET SPOOL as requested and share the exact commands as given to Oracle and the result.

We see where MAITM010_IDX might come from.
But where does the SYSAUX, SYSTEM,... come from. Understanding that may explain a problem. Maybe the command shown works, but the next command suggests a problem?

Finally... If the MAITM010_IDX is what it's name implies: just indexes, then be sure to carefully weigh the cost of backup + restore all versus just re-indexing and analyzing selected indexes.

Regards,
Hein van den Heuvel
HvdH Performance Consulting
file system
Frequent Advisor

Re: script help oracle

Thank you all experts.
I think that maybe the shell environment is the cause of error

I changed ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF > /dev/null to ${ORACLE_HOME}/bin/sqlplus / as sysdba <
this works fine.
but your helps are available to me.
I really appreciate that.

and I will backup of oracle using RMAN next time to your advice. thank you
Sean Dale
Trusted Contributor

Re: script help oracle

It probably wouldn't hurt to set ${ORACLE_SID} as well.
Live life everyday
Volker Borowski
Honored Contributor

Re: script help oracle

Hi,

with 10g you simply can do

alter database begin backup;

Very charming and very speedy compared to 9i.

Volker