Operating System - HP-UX
1748074 Members
5311 Online
108758 Solutions
New Discussion юеВ

Re: ORACLE redo logs identifying

 
Enrico Venturi
Super Advisor

ORACLE redo logs identifying

Hello colleagues,
I need to know exactly the name / identifier of the "next" redo log archive the standby database is waiting for.
Which are the VIEW name and variables to get such information?

Thanks
Enrico
14 REPLIES 14
Hein van den Heuvel
Honored Contributor

Re: ORACLE redo logs identifying

V$ARCHIVED_LOG, V$LOG_HISTORY ...

The table I use to see the active logs are V$LOG and V$LOGFILE. But in this case you may want to look back further and use V$LOG_HISTORY to see which sequence covered what times, which change numbers.

Did you check the Oracle Doc?
Which version are you using?
I foudn some interesting reading in "Oracle├В┬о Data Guard Concepts and Administration
10g Release 1 (10.1)
Part Number B10823-01
"

It has a section: "6.3 Applying Redo Data to Physical Standby Databases"

Is mentions for example: V$MANAGED_STANDBY

And:

"The V$ARCHIVED_LOG fixed view on the physical standby database shows all the archived redo log files received from the primary database. This view is only useful after the standby site starts receiving redo data, because before that time the view is populated by old archived redo log records generated from the primary control file."

and

"To quickly determine the level of synchronization for the standby database, issue the following query on the physical standby database:

SQL> SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
2> FROM V$ARCHIVE_DEST_STATUS;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
1 947 1 945"


Good luck,
Hein.
HvdH Performance Consulting.
Yogeeraj_1
Honored Contributor

Re: ORACLE redo logs identifying

hi Enrico,


You did not mention version etc.

Anyway, depending on the mode in which your standby database is configured, you will have to query either at the source or the destination.


If it is on automatic mode, you can try:
sys@MTDB.MU> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/admin/mydb/arch
Oldest online log sequence 15082
Next log sequence to archive 15084
Current log sequence 15084
sys@MYDB.MU>

The alert log should also give you information on the last archived log that has been shipped.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: ORACLE redo logs identifying

I'm not using the standby database concept in that I remote copy and I apply by myself the archived redo logs.
The redo logs are produces Master side, then are remotely copied on the stanby machine; the user application takes care of applying the archivals on the standby instance database; unfortunately some archive can be lost therefore we'd like to get from the database the identifier of the next redo logs archival it's waiting for.
Hoa can I know the identifier of this archive?

thanks
Enrico

P.S.
ORACLE 8.1.7
Enrico Venturi
Super Advisor

Re: ORACLE redo logs identifying

I'm not using the standby database concept in that I remote copy and I apply by myself the archived redo logs.
The redo logs are produces Master side, then are remotely copied on the standby machine; the user application takes care of applying the archivals on the standby instance database; unfortunately some archive can be lost therefore we'd like to get from the database the identifier of the next redo logs archival it's waiting for.
Hoa can I know the identifier of this archive?

thanks
Enrico

P.S.
ORACLE 8.1.7
Sean Dale
Trusted Contributor

Re: ORACLE redo logs identifying

select sequence#, applied
from v$archived_log
where applied = 'NO'
;

This will tell you the next archive log in the sequence that it wants to apply.
Live life everyday
Yogeeraj_1
Honored Contributor

Re: ORACLE redo logs identifying

hi,

The following data dictionary view can help:

V$ARCHIVED_LOG
This view displays archived log information from the controlfile including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared). If the log is archived twice, there will be two archived log records with the same THREAD#, SEQUENCE#, and FIRST_CHANGE#, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy.
COLUMN DESCRIPTION
RECID Archived log record ID
STAMP Archived log record stamp
NAME Archived log file name
THREAD# Redo thread number
SEQUENCE# Redo log sequence number
RESETLOGS_CHANGE# Resetlogs change# of database when written
RESETLOGS_TIME Resetlogs time of database when written
FIRST_CHANGE# First change# in the archived log
FIRST_TIME Timestamp of the first change
NEXT_CHANGE# First change in the next log
NEXT_TIME Timestamp of the next change
BLOCKS Size of the archived log in blocks
BLOCK_SIZE Redo log block size
COMPLETION_TIME Time when the archiving completed
DELETED YES/NO


Note that my preferred way of doing the same would be using RMAN.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: ORACLE redo logs identifying

Sorry but I wasn't clear enough.
I have a database "OFF".
I don't have an usual "standby database".

I copy the archives from the RUNNING database to the OFF database, then I start the "OFF" database with the "recovery" option.
When the database is started in automatic recovery option it processes the archives it finds in the directory we specified in the command string. It looks for some specific archive number (since there's a sequence in the archives) and it processes it, then looks for the next one and so on.
My question is: can I get (read) the expected archive number from (in) the database itself?

thanks
Enrico
Yogeeraj_1
Honored Contributor

Re: ORACLE redo logs identifying

hi Enrico,

I do understand what you trying to do. I had procedures for the same but using RMAN.

unfortunately, i no longer have a similar environment for testing purposes.

can you check metalink note: 241512.1

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Enrico Venturi
Super Advisor

Re: ORACLE redo logs identifying

I have no access to ORACLE metalink.

We don't use RMAN, even if it's a profitable tool.
We use the following command:
$ORACLE_HOME/bin/sqldba
connect internal
startup mount exclusive pfile=${ORACLE_PFILE};
set autorecovery on;
alter database recover automatic from '${ARCHIVE_LOG_DEST}' database until cancel using backup controlfile;
alter database recover cancel;
alter database open resetlogs;

$ARCHIVE_LOG_DEST contains the archives; if the database is waiting from the archive #100 but it isn't the the
$ARCHIVE_LOG_DEST then the recovery fails.
How (where) can I check in advance the number of the archiver the database is waiting for?

sorry if I'm not clear

Enrico