Re: ALTER DATABASE RENAME FILE errors when executed from inside a shell script

Subrahmanyam Arya wrote:

> Dear oracle gurus,
>
> Bang me question might be silly and simple.
> I am trying to relocate the datafiles from location to other and i am
> trying to do this from inside a shell script named
> rename-datafiles.sh. i paste below this simple script
>
> #!/bin/ksh
>
> ORACLE_SID=plxdb3
> export ORACLE_SID
>
> ORACLE_HOME=/opt/PlexView/Oracle/ora01/app/oracle/product/8.1.7
> export ORACLE_HOME
>
> export ECHO='/usr/ucb/echo'
>
> usage () {
>
> /usr/ucb/echo "
> Wrong number of parameters
>
> Usage: $0 <[SYSTEM][TOOLS][RBS][TEMP][USERS][INDX][TEST]>
> <OldDataFileNameAndPath> <NewDataFileNameAndPath>
>
> - 1st Arg is tablespace name and can be only one of
> - the following SYSTEM/TOOLS/RBS/TEMP/USERS/INDX/TEST
>
> - 2nd Arg is the old data filename and path for the
> tablespace
> - 3rd Arg is the new data filename and path for the
> tablespace"
>
> /usr/ucb/echo ""
> return 1
> }
>
> #######
> # (0.0) Check args.
> #
> if [ $# -ne 3 ]
> then
> usage || return 1
> fi
>
> /opt/PlexView/Oracle/ora01/app/oracle/product/8.1.7/bin/svrmgrl <<
> EOSQL
> connect internal
> startup mount plxdb3;
> alter database rename file $2 TO $3;
> alter database open;
>
> exit
> EOSQL
>
> I get the following error and dont have any clue about the message.
> This is the way i executed the script
>
> ./rename-datafiles.sh TEST
> '/opt/PlexView/Oracle/ora03/oradata/plxdb3/test01.dbf'
> '/opt/PlexView/Oracle/ora02/oradata/plxdb3/test01.dbf'
> Relocating datafiles for TEST tablespace
>
> Oracle Server Manager Release 3.1.7.0.0 - Production
>
> Message 4505 not found; No message file for product=SVRMGR,
> facility=MGR
>
> Error while trying to retrieve text for error ORA-12545
> SVRMGR> Message 701 not found; No message file for product=SVRMGR,
> facility=MGR
> Message 701 not found; No message file for product=SVRMGR,
> facility=MGR
> Error while trying to retrieve text for error ORA-12545
> SVRMGR> MGR-00310: Message 310 not found; No message file for
> product=SVRMGR, facility=MGR
> SVRMGR> alter database rename file
> '/opt/PlexView/Oracle/ora03/oradata/plxdb3/test01.dbf' TO
> '/opt/PlexView/Oracle/ora02/oradata/plxdb3/test01.dbf'
> *
> Error while trying to retrieve text for error ORA-03114
> SVRMGR> alter database open
> *
> Error while trying to retrieve text for error ORA-03114
> SVRMGR> SVRMGR> Message 37 not found; No message file for
> product=SVRMGR, facility=MGR
>
> How to overcme this error and how exactly to pass the command line
> args from shell script to an SQL script or to the SQL commands inside
> th script ???
>
> -thx,
> avsrk

I've no doubt this can be done from a shell script but why?

To perform this action requires the following:

SQL> CONN / AS SYSDBA SQL> SHUTDOWN SQL> STARTUP MOUNT SQL> HOST $ cp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbf

$ EXIT SQL> ALTER DATABASE RENAME FILE '/u01/oradata/tools01.dbf' TO '/u06/oradata/tools01.dbf';

SQL> ALTER DATABASE OPEN; SQL> HOST $ rm /u01/oradata/tools.01.dbf

$ EXIT

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)