Oracle archive log files

[Fedele Giuseppe]

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?

[Ivan Ferreira]

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 "_"

[Fedele Giuseppe]

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

[spex]

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

[Yogeeraj_1]

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

[Fedele Giuseppe]

Hi,

I know how to get thread and sequence IDs:

select thread#, sequence# from v$log

but how to get "resetlog identifier" ?

[spex · Accepted solution]

SELECT resetlogs_id
FROM v$archived_log;

[Yogeeraj_1]

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

[Fedele Giuseppe]

Ok,

this is the information I was looking for.

Many thanks

Giuseppe

[Fedele Giuseppe]

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