ORACLE redo logs identifying

[Enrico Venturi]

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

[Hein van den Heuvel]

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]

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

[Enrico Venturi]

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]

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]

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.

[Yogeeraj_1]

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

[Enrico Venturi]

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]

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

[Enrico Venturi]

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

[Yogeeraj_1]

hi Enrico,

to determine this, the following should help:
sys@MYDB.MU> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oracle/admin/mydb/arch
Oldest online log sequence 15155
Next log sequence to archive 15157
Current log sequence 15157
sys@MYDB.MU>

Using SQL, the same thing can be queried as follows:
sys@MYDB.MU> select thread#, sequence#
2 from v$log
3 where status = 'CURRENT';

THREAD# SEQUENCE#
__________ __________
1 15157

Elapsed: 00:00:00.03
sys@MYDB.MU>

Which implies that the next required archived log file would have sequence: 15158

hope this helps!

kind regards
yogeeraj

[Enrico Venturi]

Unfortunately it doesn't work .........
see the following traces:

SVRMGR> set autorecovery on;
Autorecovery ON

SVRMGR> alter database recover automatic from '/usr/osres/WarmArchive/1354RM/' database until cancel using backup controlfile;
alter database recover automatic from '/usr/osres/WarmArchive/1354RM/' database until cancel using backup controlfile
*
ORA-00279: change 53464 generated at 10/03/2006 07:58:13 needed for thread 1
ORA-00289: suggestion : /usr/osres/WarmArchive/1354RM/arch_1_7.arc
ORA-00280: change 53464 for thread 1 is in sequence #7
ORA-00278: log file '/usr/osres/WarmArchive/1354RM/arch_1_7.arc' no longer needed for this recovery
ORA-00308: cannot open archived log '/usr/osres/WarmArchive/1354RM/arch_1_7.arc'
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

SVRMGR> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /usr/osres/WarmArchive/1354RM/
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1

SVRMGR> select THREAD#, SEQUENCE# from v$log where status='CURRENT';
THREAD# SEQUENCE#
---------- ----------
1 1
1 row selected.
SVRMGR>

[Yogeeraj_1]

hi,

now it is much clearer.

You are issueing a "ALTER DATABASE OPEN RESETLOGS;"!!


If you do this your logfiles are no longer in sync! You will never be able to apply your archivelog files.

I am not sure that noresetlogs will work in this case..

If you are using the OFF database only for query, you may issue:
sql> alter database open read only


with this you will be able to later recover your database by applying the new archived logs!


hope this helps!

kind regards
yogeeraj

[Enrico Venturi]

No chance to find a solution.
No answers to my question: just a show of the know how of each one.

[Yogeeraj_1]

hi Enrico,

Sorry, I think i was not *clear* enough.

1. Since you are doing a resetlog, you will not be able to apply the next archived logfile.

2. The SQL statement do give you the information you require.
select THREAD#, SEQUENCE# from v$log where status='CURRENT';

The next archived log file that you will need is sequence#+1

If you need any further clarification, please do let us know.

kind regards
yogeeraj