Hey, all.

I'm having a weird issue with standby under 8.1.7.4. on AIX.

My goal is to create a read-only reporting database, which will be put
into recovery mode a few times a day, to catch up on logs generated
from the master.

My routine at this time is as follows:

**Master Database:
-Normal operation; redo files are copied to secondary location for the
standby db to apply
-Right before applying this redo to the standby, I do an explicit ALTER
SYSTEM ARCHIVE LOG CURRENT on the master, to ensure that the current
redo log is written to archive.

**Standby Database:
Currently in read-only mode; the following is run as a script
shutdown immediate;
startup nomount;
alter database mount standby database;
recover standby database;
auto
alter database open read only;
exit

My testing process is very consistent at this point:
-I update one value in one table in the master db three times, to
generate the same amount of redo. This is the only session in the
primary database.
-After three updates, I do the ARCHIVE LOG CURRENT, then run the
recovery commands on the standby as a script.

However, _sometimes_ the 'first' log file which is required for the
current recovery is not in the secondary location. The remainder are
all there. I am forced to do a manual copy of _one_ file from the
primary archive_dest to the secondary, after which point the recovery
works smoothly.

The only thing I can think of is that something in the process of
shutting down the standby is interrupting the stream of log files.
Makes sense that it would. Considering that it is the 'first' log file
in each recovery process that is missing, that file must have been
generated while the standby was in the process of restarting for
recovery in the previous 'iteration'.

I have read posts which confirm that a restart is required to reset
from read-only mode back to manual-recover mode. If this is the cause
of the interruption in the redo stream to the secondary location, am I
forced to do something at the OS level to ensure that all redo log
files are synchronized??


BD

It sounds like you have set log_archive_dest_2 to the standy service?
Or are you manually (scripted) copying them periodically? By the
'first' logfile, do you mean the one generated by the foreground
switch?

I can't remember if 8.1.7 had a FAL (Fetch Archive Log) process. If
not, you will probably have to read the v$archived_log view on the
priamry and copy any ones you are missing, prior to issuing the
"recover standby database" command.

Regards,

Steve

On 27 Apr 2006 11:19:33 -0700, "BD" <bobby_dread (AT) hotmail (DOT) com> wrote:

Which is not exactly the purpose of a standby database.
If you read the docs carefully you will notice there are so many
restrictions to a readonly standby database,you would better setup two
standby databases. Any attempt to hack yourself out (which is
apparently what you are trying to do) is doomed to fail.

You would be better off using advanced replication
--
Sybrand Bakker, Senior Oracle DBA

Upgrade to 10g and use Data Guard.

-bdbafh

Yes.

I've just scrutinized my process more closely, and I think there may be
an issue with the standby actually getting a complete set of redo each
time I do the recover. When I trigger the recovery and it chews through
the log files, it always fails at the last one: I see it successfully
apply #100 - #104, and then I see the following:

ORA-00308: cannot open archived log
'/home/oracle/admin/REP2/archive/arch/arch1_REP2_105.arc'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

I kind of assumed that this happened because the recovery will keep
looking for files until it can't find any more - but the fact that it's
looking for #105 must mean it _needs_ it! I may be confused as to what
mechanism is driving the recovery - looking for subsequent log files,
or actively looking for a current change number (in which case, the
recovery is not really 'completing' each time).

Anyway. After that recovery, I flip back into read only mode, and
continue normal operation. I run my 'recovery' script again, and it
once again fails to find file #105. I go to the file system, copy that
file from the archive_dest_1 to archive_dest_2, do the recover again,
and it continues. successfully.

Since the required change number is reported as each log file is
processed, I gather that it's getting all it needs. I think the key
here may be the fact that on each recovery it fails when it tries to
find the last file - it's this same file which does not end up being
automatically copied to the second archive_dest.


Yeah - not my first choice; I'd at least want to understand what is
causing this gap.

Obviously that's the ideal. Thanks, but if that was an option I think
I'd have done it long ago. I have to work within the infrastructure
that I'm provided; I don't sign the cheques for software licensing.

I appreciate the suggestion, but asking "how can I make this tool work"
and being told "use a different tool" is not helpful.

I dunno about that. I have not discovered these restrictions yet, but
it's stated right at the top of the Oracle docs that one of the main
purposes of a standby is to act as a reporting database. it also says,
as to the 3 modes (Managed Recovery, Manual Recovery and Read Only) -
"you can switch back and forth between the modes at will."

If my process is simply flawed, then that's fine - I'll fix my process
as I understand more about how it works. But if you're saying that
standby is not really supposed to be _used_ for reporting in the way
I'm trying to make it work - well, that's not consistent with the docs
I'm reading.

I've continued poring through the documentation, and I think that
everything I'm seeing is predictable - bringing down the standby while
the primary is running can result in a gap in logs; the docs describe
the process of fixing a gap sequence as copying the missing file from
the primary and renaming it appropriately.

It seems to come to this: When I go from read-only mode to recover
mode, and there are sessions active, is the appropriate way to
terminate these sessions to shutdown and restart?

Research continues, but the 'workaround' of manually filling the gap
sequence by copying files appears to be explained quite clearly in the
docs - therefore it should be a supportable scenario.

BD

On Thu, 27 Apr 2006 11:19:33 -0700, BD wrote:

Yes, my guess is that the primary database sometimes tries to send an
archived log to your standby while it is down, ie after "shutdown
immediate" and before "alter database mount standby database;".

Since FAL did not exist in this version, the standby recovery fails.

Have a look at the doco for "log_archive_dest_n" in this version and see
whether the options REOPEN and MAX_FAILURE already existed.
Otherwise one solution would be to prevent the primary database
from archiving any log while the standby database is down...

Jerome

I'd have to experiment with that one. Doesn't give me a real warm
fuzzy, although I'm sure it's a viable option.