- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- script help oracle
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2007 06:04 PM
тАО01-18-2007 06:04 PM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2007 06:15 PM
тАО01-18-2007 06:15 PM
Re: script help oracle
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 <
should work.
Hope this helps
Regards Stefan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2007 06:47 PM
тАО01-18-2007 06:47 PM
Re: script help oracle
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2007 07:22 PM
тАО01-18-2007 07:22 PM
Solutioncan 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2007 07:48 PM
тАО01-18-2007 07:48 PM
Re: script help oracle
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-18-2007 08:29 PM
тАО01-18-2007 08:29 PM
Re: script help oracle
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-19-2007 06:38 PM
тАО01-19-2007 06:38 PM
Re: script help oracle
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