cancel
Showing results for 
Search instead for 
Did you mean: 

script help oracle

SOLVED
Go to solution
file system
Frequent Advisor

script help oracle

$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




6 REPLIES
Stefan Schulz
Honored Contributor

Re: script help oracle

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
No Mouse found. System halted. Press Mousebutton to continue.
file system
Frequent Advisor

Re: script help oracle

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
Peter Godron
Honored Contributor
Solution

Re: script help oracle

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

file system
Frequent Advisor

Re: script help oracle

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
Peter Godron
Honored Contributor

Re: script help oracle

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
Yogeeraj_1
Honored Contributor

Re: script help oracle

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)