topic Re: script help oracle in Operating System - HP-UX

script help oracle

file system — Wed, 07 Feb 2007 00:32:25 GMT

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.

Re: script help oracle

Yogeeraj_1 — Wed, 07 Feb 2007 02:44:07 GMT

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

Re: script help oracle

Yogeeraj_1 — Wed, 07 Feb 2007 02:50:10 GMT

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

Re: script help oracle

Peter Godron — Wed, 07 Feb 2007 03:32:04 GMT

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.

Re: script help oracle

Arturo Galbiati — Thu, 08 Feb 2007 05:14:47 GMT

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

Re: script help oracle

Hein van den Heuvel — Thu, 08 Feb 2007 09:11:51 GMT

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

Re: script help oracle

file system — Thu, 08 Feb 2007 20:57:39 GMT

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

Re: script help oracle

Sean Dale — Mon, 12 Feb 2007 19:33:54 GMT

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

Re: script help oracle

Volker Borowski — Wed, 21 Feb 2007 12:47:58 GMT

Hi,

with 10g you simply can do

alter database begin backup;

Very charming and very speedy compared to 9i.

Volker