Thanks
A reader, July 14, 2002 - 3:49 pm UTC
It's amazing what can run for years and years and still be wrong.
Thanks for the help!!!
NULL
Lidia, August 08, 2003 - 7:26 am UTC
Why do you include in script files with NULL name , documentation tells us, that is in case of clearing unarchived log files (very rare)?
And how will the line with name =NULL in your final script /tmp/21513.sh (for gzip) look?
August 10, 2003 - 11:44 am UTC
I added NULL in the event there was nothing in the directory. It is not looking for a log with a name of NULL, it would be looking for an log whose name "is null" which will never happen. the lines in question are:
echo 'where name in(' >> /tmp/$$.sql
ls /arch1/oradata/ora8i/arch/*.log | sed "s/^/'/;s/"'$'"/',/" >> /tmp/$$.sql
echo 'NULL ) and sequence# not in ' >> /tmp/$$.sql
if /arch1/oradata/ora8i/arch/*.log returned nothing and I didn't have NULL ) in there, the predicate would be:
where name in ( )
which would fail. instead the predicate is:
where name in ( NULL )
which successfully will find NOTHING.
A reader, October 30, 2003 - 10:35 am UTC
Tom,
Lets say i performed an incomplete recovery, will v$archived_log return duplicate records?? if yes then Tom can you please comment on this.
October 30, 2003 - 11:28 am UTC
no, it will not.
Great script... and great model for others.
Robert, October 30, 2003 - 4:27 pm UTC
Ver 9204
A reader, August 31, 2004 - 2:01 pm UTC
Tom,
Your script is working great, is it ok if i modify the script for my standby database and include the seq# when the applied column =YES in v$archived_log
instead of v$log.
Thanks
August 31, 2004 - 2:38 pm UTC
you'll need to test and debug it, but sure, anything you want to do with the concept is "ok" really
v$archived_log
Alain, September 22, 2004 - 5:56 am UTC
Hi Tom,
Sometime, a simple query from v$archived_log is quite slow 20 to 30 seconds for the following query on a 9.2 database
select name into l_name
from v$archived_log
where thread# = a_Thread and sequence#=a_sequence;
I'm aware that v$ tables are not real tables (may be no index ...), but do you know some tips to speed up this query (and may be other v$ queries) or at least to prevent it to slow down when archived log number increase ?
September 22, 2004 - 8:18 am UTC
accessing v$ tables in a live, busy system is excessively, extremely expensive.
You have to basically prevent people from modifying these structures whilst you are querying them (latch them). so, if someone else has the structure latched -- you wait.
How to zip arc files when using RMAN for backup
Roman Filipsky, September 22, 2004 - 9:39 am UTC
We are using RMAN to backup database (full on Sunday/arch on every other day). I have a feeling that it is not possible to compress arc (archived redo log) files with any kind of program (compress/gzip/rarr/...) before running RMAN. The reason is that RMAN checks the data dictionary of the target database for all the arc files that are not yet backed up and backs them up then. If the arc files are compressed and renamed then it is not possible for RMAN to find them. If they are not renamed - that is the arc files will retain the original name after compression - then it might be possible for RMAN to backup them. It is not possible however to tell easily if the file is compressed or not (when it is still on the disk) just by looking at it's name.
Could you please clarify if it's possible to compress arc files when the backup is done using RMAN?
Best regards
Roman Filipsky
September 22, 2004 - 10:16 am UTC
correct.
this is for a purely "manual, I'm doing everything myself" operation.
10g does compress archives on disk if you want as a feature of the database itself.
Alain, September 22, 2004 - 9:41 am UTC
So all the previous scripts have to be used with caution.
Thanks for your answer.
September 22, 2004 - 10:17 am UTC
all scripts do?
every half hour/hour/long period of time is perfectly ok here.
Where in the docu?
Michael, September 23, 2004 - 5:43 am UTC
"10g does compress archives on disk if you want as a feature of the database
itself."
I searched in the 10g docu, but i couldn't find information about this feature.
Where should i look?
Thanks!
September 24, 2004 - 8:10 am UTC
hmm, looks like it only applies to rman backupsets (which does true compression in 10g, not just "skip unused blocks"). sorry for the confusion on that.
archivelog compression
adolf_H, April 28, 2005 - 7:15 pm UTC
first let me clarify my understanding...
irrespective of whether a transaction was committed or not its redo will be logged in the redo and archive log files. at the time of recovering database oracle will apply all these transactions [ roll forward ] and then roll back uncommitted transactions.
if above is the case then is it possible to apply only committed transactions. consider i do bulk processing very often and do not necessarily commit my all transactions. if somehow i can enable roll forward for committed transactions only then i may save time to recover my database in critical situation.
if someone can provide a tool which will look into archive log files and clear redo data of roll backed [ i mean completed and uncommitted ] transactions then i will also be able to save disk space.
i am not asking to log only committed transaction to save disk space but optimizing log applying to corrupt datafile to save resources during recovery.
what is your opinion on the same?
April 28, 2005 - 7:28 pm UTC
you have to roll forward even those uncommitted...
why? because a rollback is a logical thing, it does NOT put things back precisely the way they where.
When you insert a row, we put the equivalent of "delete rowid" in the undo.
when you update a row, we put the anti-update
when you delete a row, we put the reinsert it at this rowid in the undo.
try this:
create an empty table.
see how big it is.
now put lots of data in it.
rollback.
now see how big it is.
We have to roll forward those index changes, those table changes (your transaction MIGHT have been the one to pull a block from above the HWM -- if during rolling forward we didn't do that -- the next guy that put stuff on that block and did commit -- we'll he wouldn't have a formatted block. Sort of like going back in time and killing your ancestors :)
It is way more complicated, you cannot clear out "rolled back" stuff -- because rolling back must be done in order to put everything back the way it was.
I would be looking at why I have lots of roll backs, not how to remove rolled back transactions. rolling back is hugely expensive.
Another way
Vinayak, April 29, 2005 - 1:39 am UTC
Another way to check whether ARCH process has finished writing the file or not yet:
(On UNIX) When ARCH is still writing the file, its file permissions are 000, when it is done with writing, its file permission gets changed to 640.
April 29, 2005 - 8:07 am UTC
I would not entirely rely on that. You'll see different behaviors by OS, file system type, database version, OS version.
eg: that is not a documented behavior -- i would hestitate to rely on that in general
(and on my RHAS Linux box, I was not able to observe this -- the perms were constant from creation of the arch to completion.
RE: Another Way
Vinayak, April 29, 2005 - 11:20 am UTC
Since it worked on our platform (Alpha Server), we had tested this and implemented this.
But yes, it is not generic way.
adolf_H, May 02, 2005 - 7:06 am UTC
<quote>
try this:
create an empty table.
see how big it is.
now put lots of data in it.
rollback.
now see how big it is.
We have to roll forward those index changes, those table changes (your
transaction MIGHT have been the one to pull a block from above the HWM -- if
during rolling forward we didn't do that -- the next guy that put stuff on that
block and did commit -- we'll he wouldn't have a formatted block. Sort of like
going back in time and killing your ancestors :)
</quote>
Agreed, my transactions will make changes apart from my changes. but those information [ allocation of extents etc ] is stored any way in the dictionary tables. why do i need to apply [ roll forward ] all the changes which i *know* rolled back?
eg. my DML [ insert / update ] pushes HWM up, this information will be stored in dict irrespective i commit my transaction or not. and saving of those information will not end my transaction.
my question is why do you need to roll forward when you *know* that you need to roll back the same later on?
May 02, 2005 - 8:42 am UTC
because the database is optimized to go forward, not optimized to go backwards.
How much harder would logging be if we optimized to rollback (which is RARE). We are optimized to commit.
rolling forward - get the database the way it was at the point of failure, and then rollback. It is the only thing that makes sense. The problem is, you don't know that you are going to rollback until you've processed all of the redo.
And when you rollback - the redo you generated might have been in 50 archived redo logs -- which may or may not be available to you anymore (nothing to say they are even on the file system anymore). We do not go back to archives and rewrite them (how expensive would that be)
archivelog script review
ayo, April 16, 2009 - 5:33 am UTC
Hi Tom,
According to the script you wrote below, how do you now spool or get the name of the current archive logs in the v$archived_log since it is null?, how can i include the names?
Using the script below i am trying to spool the names from v$archived_log to populate v$logmnr_contents.
Thanks
ayo
April 16, 2009 - 9:54 am UTC
I don't know what you mean. name is not null.
and why would you spool the names out, why wouldn't you just use v$archived_log DIRECTLY in your sqlplus session to populate log miner - why are you scripting and spooling - just write a plsql loop and call the dbms log miner stuff.
archivelog script review
ayo, April 17, 2009 - 8:25 am UTC
Hi Tom,
Thanks for replying, can u please help me with the pl/sql loop
script for populating the V$logmnr_contents with the v$archived_logs.
Just like the one you wrote below:
!/bin/ksh
export DUPLEX_LOC=/arch2/oradata/ora8i/arch
echo set heading off > /tmp/$$.sql
echo set feedback off >> /tmp/$$.sql
echo set linesize 200 >> /tmp/$$.sql
echo set trimspool on >> /tmp/$$.sql
echo column cmd format a70 >> /tmp/$$.sql
echo spool /tmp/$$.sh >> /tmp/$$.sql
echo select "'/opt/gnu/bin/gzip -v ' || name ||' ; cp -p '||name||'.gz
$DUPLEX_LOC ' " >> /tmp/$$.sql
echo 'from sys.v_$archived_log' >> /tmp/$$.sql
echo 'where name in(' >> /tmp/$$.sql
ls /arch1/oradata/ora8i/arch/*.log | sed "s/^/'/;s/"'$'"/',/" >> /tmp/$$.sql
echo 'NULL ) and sequence# not in ' >> /tmp/$$.sql
echo '( select sequence# from v$log where archived = '"'NO'"')' >> /tmp/$$.sql
echo '/' >> /tmp/$$.sql
echo spool off >> /tmp/$$.sql
echo exit >> /tmp/$$.sql
Kindly help out thanks, i really need it for an emergency report.
Best Regards.
April 17, 2009 - 10:47 am UTC
"U" isn't available, "U" is dead as far as I know. Look it up, it is true.
http://en.wikipedia.org/wiki/U_of_Goryeo you are a developer or dba and you cannot write a plsql for loop against v$archive_logs?
for x in (select name from v$archived_log)
loop
... process x.name right here ...
end loop;
archivelog script review
ayo, April 17, 2009 - 11:11 am UTC
thanks a lot Tom, i actually could write the loop but did not know where exactly to place the statements from v$archived_log to populate v$logmnr_contents.
April 17, 2009 - 3:48 pm UTC
inside the loop?
generate script from shell review
Sokrates, April 17, 2009 - 11:31 am UTC
instead of
echo a > /tmp/$$.sql
echo b >> /tmp/$$.sql
...
echo z >> /tmp/$$.sql
it seems much prettier to do
cat << EOF > /tmp/$$.sql
a
b
...
z
EOF
when you can do it in a single call, just do it !
(and don't create a process for each line, since echo doesn't seem to be a shell-built-in
$ file `which echo`
/bin/echo: ELF 32-bit LSB executable, ...
well I am not on ksh and know nothing about this shell, but i conject this one is shell independent
)
Use this script
nipradi, October 24, 2010 - 10:27 am UTC
####Created by nipradi####
export ARCH_LOC=$1
COUNT=`ls -ltr $ARCH_LOC/*.arc | wc -l`
COUNT=`expr $COUNT - 3`
ls -ltr $ARCH_LOC/*.arc | head -$COUNT | awk '{print "/usr/bin/gzip",$9}' > $ARCH_LOC/compress_archive.sh
nohup sh $ARCH_LOC/compress_archive.sh &
This script will gzip the archives by excluding last 3 archives & useful if you will use it in crontab