Skip to main content

Manual archiving of redo logs gone with 9i – or is it not?

Originally published on Dbvisit blog at http://blog.dbvisit.com/manual-archiving-of-redo-logs-gone-with-9i-or-is-it-not/.

If you memory dates back to the pre-10g times, you will maybe recall the steps to enable archivelog mode on the database at that time. Perhaps the greatest catch was that the online logs did not get archived automatically by default – you had to do it manually.

Or, let Oracle come to rescue, and let the ARCH process(es) do it for you by starting them with log_archive_start = true (or even dynamically with alter system archive log start/stop). You could even start ARCH in noarchivelog mode, so they did completely nothing, just writing error messages to alert.log.

One of the new features of 10g was getting rid of all of this – ARCH starting is governed solely by (no)archivelog mode and the DBA does not need to fiddle with anything else to get it right. Great applause, many thanks, scene over.

Well, all above is just a short transcript of my memory of those great times, and I lived with it happily and well till this week.

As it turns out, the story with 10g is not so straightforward – although it is true that if you just set no/archivelog mode that ARCH works as expected, the manual way of archiving was in fact not removed. But the way to enable it new:

To enable manual archiving mode, use alter database archivelog manual. The rest is the same as with 9i – use alter system archive log commands.

One consequence of this change – the log_mode in v$database can be archivelog, noarchivelog (as in 9i and before) or manual (from 10g on). This also means that if you want to detect whether a database runs in archivelog mode, you have to check for manual log_mode as well.

Comments

Popular posts from this blog

ORA-27048: skgfifi: file header information is invalid

I was asked to analyze a situation, when an attempt to recover a 11g (standby) database resulted in bunch of "ORA-27048: skgfifi: file header information is invalid" errors.

I tried to reproduce the error on my test system, using different versions (EE, SE, 11.1.0.6, 11.1.0.7), but to no avail. Fortunately, I finally got to the failing system:

SQL> recover standby database;
ORA-00279: change 9614132 generated at 11/27/2009 17:59:06 needed for thread 1
ORA-00289: suggestion :
/u01/flash_recovery_area/T1/archivelog/2009_11_27/o1_mf_1_208_%u_.arc
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-27048: skgfifi: file header information is invalid
ORA-00280: change 9614132 for thread 1 is in sequence #208


Interestingly, nothing interesting is written to alert.log n…

Reading data from PGA and SGA

Overview For our investigation of execution plan as it is stored in memory, we need in the first place to be able to read the memory.

We have the options of
x$ksmmem, reading SGA using SQL. Personally I don't like it, it's cumbersome and slow.direct SGA read: obviously reading SGA only; it's fast and easy to doread process memory: can read PGA, process stack - and since the processes do map the SGA, too, you can read it as well. Unfortunately ptrace sends signals to the processes and the process is paused when reading it, but so far all my reads were short and fast and the processes did not notice. Some OS configurations can prevent you from using ptrace (e.g. docker by default), google for CAP_SYS_PTRACE.gdb: using your favorite debugger, you can read memory as well. Useful when investigating. Direct SGA read I always considered direct SGA read of some dark magic, but the fundamentals are actually very easy. It still looks like sorcery when actually reading the Oracle in…

Filter and access predicates

More than just column projections When we look around for further pointers in the tree nodes, we find more pieces resembling the column projections we have seen so far. With some experimenting, we will find out that these are access predicates and filters.

First of all, the location of these pointers is not always the same, it seems that the value at 0x34 is some kind of flag, indicating whether filters and/or access predicates are present, and where. Or whether there is just one, or more of them.  It probably also indicates what other info is present, but I have no idea what info that would be or what each value means.
Resembling, but different The data we see as predicates are not columns; after all, a predicate is a condition, not a single column. But the structure is similar to what we have seen with columns, and if we follow pointers further, we eventually build a tree, and some of the leaves are indeed just column projections.
After some contemplation, we realize it's all t…