topic Re: ORACLE redo logs identifying in Operating System - HP-UX

ORACLE redo logs identifying

Enrico Venturi — Mon, 02 Oct 2006 05:29:30 GMT

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

Re: ORACLE redo logs identifying

Hein van den Heuvel — Mon, 02 Oct 2006 07:51:22 GMT

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.

Re: ORACLE redo logs identifying

Yogeeraj_1 — Mon, 02 Oct 2006 09:01:47 GMT

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

Re: ORACLE redo logs identifying

Enrico Venturi — Mon, 02 Oct 2006 09:16:11 GMT

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

Re: ORACLE redo logs identifying

Enrico Venturi — Mon, 02 Oct 2006 09:16:18 GMT

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

Re: ORACLE redo logs identifying

Sean Dale — Mon, 02 Oct 2006 15:53:32 GMT

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.

Re: ORACLE redo logs identifying

Yogeeraj_1 — Mon, 02 Oct 2006 23:16:27 GMT

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

Re: ORACLE redo logs identifying

Enrico Venturi — Tue, 03 Oct 2006 02:59:37 GMT

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

Re: ORACLE redo logs identifying

Yogeeraj_1 — Tue, 03 Oct 2006 05:17:11 GMT

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

Re: ORACLE redo logs identifying

Enrico Venturi — Tue, 03 Oct 2006 05:37:28 GMT

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

Re: ORACLE redo logs identifying

Yogeeraj_1 — Tue, 03 Oct 2006 08:59:09 GMT

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

Re: ORACLE redo logs identifying

Enrico Venturi — Tue, 03 Oct 2006 10:34:41 GMT

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>

Re: ORACLE redo logs identifying

Yogeeraj_1 — Wed, 04 Oct 2006 04:38:02 GMT

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

Re: ORACLE redo logs identifying

Enrico Venturi — Wed, 04 Oct 2006 05:06:42 GMT

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

Re: ORACLE redo logs identifying

Yogeeraj_1 — Wed, 04 Oct 2006 23:40:04 GMT

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