cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle archive log files

SOLVED
Go to solution
Fedele Giuseppe
Frequent Advisor

Oracle archive log files

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?

9 REPLIES
Ivan Ferreira
Honored Contributor

Re: Oracle archive log files

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 "_"
Por que hacerlo dificil si es posible hacerlo facil? - Why do it the hard way, when you can do it the easy way?
Fedele Giuseppe
Frequent Advisor

Re: Oracle archive log files

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
Honored Contributor

Re: Oracle archive log files

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
Honored Contributor

Re: Oracle archive log files

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Fedele Giuseppe
Frequent Advisor

Re: Oracle archive log files

Hi,

I know how to get thread and sequence IDs:

select thread#, sequence# from v$log

but how to get "resetlog identifier" ?


spex
Honored Contributor
Solution

Re: Oracle archive log files

SELECT resetlogs_id
FROM v$archived_log;
Yogeeraj_1
Honored Contributor

Re: Oracle archive log files

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Fedele Giuseppe
Frequent Advisor

Re: Oracle archive log files

Ok,

this is the information I was looking for.

Many thanks

Giuseppe
Fedele Giuseppe
Frequent Advisor

Re: Oracle archive log files

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