topic script help oracle in Operating System - HP-UX

script help oracle

file system — Fri, 19 Jan 2007 02:04:58 GMT

$ORACLE_HOME/bin/sqlplus /nolog < DB_INFO
connect / as sysdba;
set linesize 200;
select db_unique_name, dbid from v\$database;
select name ||':' || block_size from v\$controlfile;
select file_name ||':'||tablespace_name||':'|| status from dba_data_files
order by tablespace_name;
exit
EOF

DB_NAME=`/usr/bin/awk '$2 == "2859985154" { print $1}' DB_INFO`
CONTROLFILE=`/usr/bin/awk -F: '$2 == "16384" { print "\n" $1 }' DB_INFO`
DATE=`/usr/bin/date +%y%m%d`
CONTROLFILE_BACK=${ORACLE_HOME}/dbs/control.${DATE}
FILE_NAME=`/usr/bin/awk -F: '$3 == "AVAILABLE" { print $1 }' DB_INFO`
TABLESPACE_NAME=`/usr/bin/awk -F: '$3 == "AVAILABLE" { print $2 }' DB_INFO`

echo $DB_NAME
echo $CONTROLFILE
echo $DATE
echo $CONTROLFILE_BACK
echo $FILE_NAME
echo $TABLESPACE_NAME

DB_INFO is below
SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jan 19 15:52:47 2007

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

SQL> Connected.
SQL> SQL>
DB_UNIQUE_NAME DBID
------------------------------ ----------
IAEIS 2859985154

SQL>
NAME||':'||BLOCK_SIZE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/IAEIS/control01.ctl:16384
/oradata/IAEIS/control02.ctl:16384
/oradata/IAEIS/control03.ctl:16384

SQL> 2
FILE_NAME||':'||TABLESPACE_NAME||':'||STATUS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oradata/IAEIS_DATAFILE/agriedu01.dbf:AGRIEDU:AVAILABLE
/oradata/IAEIS/sysaux01.dbf:SYSAUX:AVAILABLE
/oradata/IAEIS/system01.dbf:SYSTEM:AVAILABLE
/oradata/IAEIS/undotbs01.dbf:UNDOTBS1:AVAILABLE
/oradata/IAEIS/users01.dbf:USERS:AVAILABLE

SQL> Disconnected from Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

the output is...

IAEIS
/oradata/IAEIS/control01.ctl /oradata/IAEIS/control02.ctl /oradata/IAEIS/control03.ctl
/oracle/product/dbs/control.070119
/oradata/IAEIS_DATAFILE/agriedu01.dbf /oradata/IAEIS/sysaux01.dbf /oradata/IAEIS/system01.dbf /oradata/IAEIS/undotbs01.dbf /oradata/IAEIS/users01.dbf
AGRIEDU SYSAUX SYSTEM UNDOTBS1 USERS

How can I get the output like.

IAEIS
/oradata/IAEIS/control01.ctl
/oradata/IAEIS/control02.ctl
/oradata/IAEIS/control03.ctl
070119
/oradata/IAEIS_DATAFILE/agriedu01.dbf
/oradata/IAEIS/sysaux01.dbf
/oradata/IAEIS/system01.dbf
/oradata/IAEIS/undotbs01.dbf
/oradata/IAEIS/users01.dbf
AGRIEDU
SYSAUX
SYSTEM
UNDOTBS1
USERS

Re: script help oracle

Stefan Schulz — Fri, 19 Jan 2007 02:15:13 GMT

Hi,

one solution would be to pipe the output through tr to replace every space character with a newline.

Something like

$ORACLE_HOME/bin/sqlplus /nolog < DB_INFO

should work.

Hope this helps

Regards Stefan

Re: script help oracle

file system — Fri, 19 Jan 2007 02:47:00 GMT

it dose not work
outputs are same.

can you give me more information?

and One more question..
after backup command like
alter tablespace tablespace_name begin backup;

how can I get the database file status 'ACTIVE' relatively tablespace_name

Re: script help oracle

Peter Godron — Fri, 19 Jan 2007 03:22:19 GMT

Hi,
can I suggest you review your other threads and see whether you can reward the efforts there and feedback on the status of the problem.

For example:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1090793

As to your currect problem:

Remove the assignation of ONTROLFILE:
CONTROLFILE=`/usr/bin/awk -F: '$2 == "16384" { print "\n" $1 }' DB_INFO`
echo $CONTROLFILE

becomes
/usr/bin/awk -F: '$2 == "16384" { print $1 }' DB_INFO

Re: script help oracle

file system — Fri, 19 Jan 2007 03:48:33 GMT

thank you Peter Godron..
but the problem is not resolved clearly.
my question is on going.

ButI'm going to reward you and other's effors too.

and did you see the question last reply;
I want to get the datafile 'ACTIVE' status
relative tablespace_name.
when I run the plsql command like
'alter tablespace tablespace_name begin backup;

ACTIVE STATUS

Re: script help oracle

Peter Godron — Fri, 19 Jan 2007 04:29:45 GMT

Hi,
thanks for reviewing the earlier threads.

Do you mean the status column on dba_data_files ?

Found more details,which may help you with your backup strategy/coding:
http://www.ordba.net/Articles/Backup.htm

Re: script help oracle

Yogeeraj_1 — Sat, 20 Jan 2007 02:38:32 GMT

Hi,

Once again, unless your system is now running 24x7 and you are running this backup script during off-peak hours, i would not recommend this type of backup.

RMAN provides a neater way of doing backup. It also simplifies the recovery methods.

The problem with the strategy if you are using are:
1. it impacts directly on your database performance. (alter tablespace tablespace_name begin backup;)
2. If you add a tablespace later, you will have to come review the script again.

when you use RMAN, you don't even "alter tablespace begin backup", you just backup.
RMAN doesn't need the extra "begin backup" processing.

Also note that, v$backup tells you file by file (since files can be in backup mode or not as well...)

hope this helps!

kind regards
yogeeraj