topic Re: Oracle archive log files in Operating System - HP-UX

Oracle archive log files

Fedele Giuseppe — Thu, 14 Jun 2007 03:45:00 GMT

Hello,

I'm working with an ORACLE 10g 10.2.0 database.

The archive log files are produced with the following name (example):

arch_1_134_625048520.arc

My question is: what does the third number (625048520) means and how can I get it?

Re: Oracle archive log files

Ivan Ferreira — Thu, 14 Jun 2007 05:34:16 GMT

LOG_ARCHIVE_FORMAT
----------------------------
%s log sequence number
%t thread number
e.g.
LOG_ARCHIVE_FORMAT = "LOG%s_%t.ARC"

You can get it by using:

ls arch_1_134_625048520.arc| cut -f 1 -d "." | cut -f 4 -d "_"

Re: Oracle archive log files

Fedele Giuseppe — Thu, 14 Jun 2007 05:43:00 GMT

Ok, but in my case I have a third number in the file name and I need to know its meaning.

Moreover I need to know if it is possible to get it by quering the database.

Thanks

Re: Oracle archive log files

spex — Thu, 14 Jun 2007 07:21:59 GMT

Hello,

arch_1_134_625048520.arc
implies
LOG_ARCHIVE_FORMAT = "arch_%t_%s_%r.arc"
where
%t=thread number
%s=log sequence number
%r=resetlogs identifier

http://download-east.oracle.com/docs/cd/B19306_01/rac.102/b14197/rmanops.htm

PCS

Re: Oracle archive log files

Yogeeraj_1 — Thu, 14 Jun 2007 09:23:34 GMT

hi,

you can also get an indication of the number by querying:

SQL> archive log list;

e.g.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/oracle/admin/mydb/arch
Oldest online log sequence 39388
Next log sequence to archive 39390
Current log sequence 39390
SQL>

hope this helps too!
kind regards
yogeeraj

Re: Oracle archive log files

Fedele Giuseppe — Thu, 14 Jun 2007 09:37:24 GMT

Hi,

I know how to get thread and sequence IDs:

select thread#, sequence# from v$log

but how to get "resetlog identifier" ?

Re: Oracle archive log files

spex — Thu, 14 Jun 2007 09:43:25 GMT

SELECT resetlogs_id
FROM v$archived_log;

Re: Oracle archive log files

Yogeeraj_1 — Thu, 14 Jun 2007 23:43:49 GMT

hi Fedele,

The v$archived_log does give this information.

SQL>desc v$archived_log
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
NAME VARCHAR2(513)
DEST_ID NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
RESETLOGS_ID NUMBER
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER
NEXT_TIME DATE
BLOCKS NUMBER
BLOCK_SIZE NUMBER
CREATOR VARCHAR2(7)
REGISTRAR VARCHAR2(7)
STANDBY_DEST VARCHAR2(3)
ARCHIVED VARCHAR2(3)
APPLIED VARCHAR2(3)
DELETED VARCHAR2(3)
STATUS VARCHAR2(1)
COMPLETION_TIME DATE
DICTIONARY_BEGIN VARCHAR2(3)
DICTIONARY_END VARCHAR2(3)
END_OF_REDO VARCHAR2(3)
BACKUP_COUNT NUMBER
ARCHIVAL_THREAD# NUMBER
ACTIVATION# NUMBER
IS_RECOVERY_DEST_FILE VARCHAR2(3)
COMPRESSED VARCHAR2(3)
FAL VARCHAR2(3)
END_OF_REDO_TYPE VARCHAR2(10)

SQL>

What are you trying to achieve?

kind regards
yogeeraj

Re: Oracle archive log files

Fedele Giuseppe — Fri, 15 Jun 2007 01:18:04 GMT

Ok,

this is the information I was looking for.

Many thanks

Giuseppe

Re: Oracle archive log files

Fedele Giuseppe — Wed, 04 Feb 2009 10:28:44 GMT

I have used the following query to get the needed info:
SELECT resetlogs_id
FROM v$archived_log;