how can you protect read-only indexes? 2005-03-03 - By Reidy, Ron
Carmen,
What if you change your shell script to test for the tablespace(s) being =
in READ ONLY mode before you start:
$ORACLE_HOME/bin/sqlplus -L /nolog <<eof
connect / as sysdba
whenever sqlerror exit fatal
set head off feed off
spool $TEMP/tablespace_status.txt
SELECT DISTINCT tablespace_name || ':' || status
FROM dba_tablespaces
WHERE tablespace_name IN (<your list>);
exit
eof
if [[ $? -ne 0 ]]; then
echo "SQL*Plus failure" # or your own message and actions
fi
if [[ `/bin/grep -c "READ ONLY" $TEMP/tablespace_status.txt` -ne 0 ]]; =
then
# you have READ ONLY tablespaces
else
#load em up
fi
Note: The above is not tested.
-- ---- ---- ----
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of Carmen Rusu
Sent: Thursday, March 03, 2005 10:35 AM
To: Waleed.Khedr@(protected); oracle-l@(protected)
Subject: RE: how can you protect read-only indexes?
This is about a datawarehouse ETL job.
It's scheduled via crontab and runs once a month.
Before a good run, I move the affected tablespaces in read-write by
running a script manually, not via crontab like the rest of the ETL.
When the load finishes I move them back to read-only mode, same way as
at the beginning.
Last week the ETL just run second time due to a crontab schedule
mistake. Obviously, in this case the tablespace alter to read-write
didnt happen, saving the most time consuming part of the ETL, the table
loads.
The sql*loader part is protected by a sentinel file.
The truncate table doesnt happen on a read-only tablespace.
So I am left with the indexes - how do I protect them?
Rebuilding the indexes is easy and takes about 4h.=20
The problem is the data warehouse slows to unusable until I do it so I
want to foolproof it.
Thanks,
-Carmen Rusu
>>> "Khedr, Waleed" <Waleed.Khedr@(protected)> 3/3/2005 11:03:17 AM >>>
You need to configure accounts and security differently.
Don't give owner id to developers.
Waleed
-- --Original Message-- --
From: Carmen Rusu [mailto:carmen.rusu@(protected)]=3D20=20
Sent: Thursday, March 03, 2005 11:03 AM
To: oracle-l@(protected)=20
Cc: Carmen Rusu
Subject: how can you protect read-only indexes?
Oracle EE 9.2.0.4 64 bit
SunOS 5.8 64bit
data warehouse db ~100gb right now, growing every month
no partitioning yet
Just verified that you can drop an index when its underlying
tablespace
is in read-only mode.
It happened when an ETL job ran second time, by mistake. The
corresponding tables, also on read-only tablespaces, survived ok the
truncate op.
So, what can I do to foolproof my ETL, so that the indexes are not
dropped by mistake next time?=3D20
Thanks,
-Carmen Rusu
Oracle DBA
--
http://www.freelists.org/webpage/oracle-l=20
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.
--
http://www.freelists.org/webpage/oracle-l
|
|
