script help oracle

[file system]

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.

[Yogeeraj_1]

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

[Yogeeraj_1]

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

[Peter Godron]

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]

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]

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]

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]

It probably wouldn't hurt to set ${ORACLE_SID} as well.

[Volker Borowski]

Hi,

with 10g you simply can do

alter database begin backup;

Very charming and very speedy compared to 9i.

Volker