Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, razvan.

Asked: June 21, 2000 - 9:12 am UTC

Last updated: May 26, 2005 - 12:58 pm UTC

Version: 7.3.2

Viewed 1000+ times

You Asked

hello tom
please can you asked me the following questions?

1.if i put a database in archivelog mode but i don't enable ARCH process or i don't ever manual archive redo logs - can affect in some way the database?
2. there is posibility that when i tried to reused a full log file the ARCH don't finish the work over it(that can happen when i have an intense activity who make redo log full in a short time which is faster then archiving activity) and so the database hangs up.How can i prevent it beside i use more redo log files(5 instead 3 for example) .
3.when the ARCH tried to archive, the destination files what permission must have (in unix parlance)? It must have oracle/dba ownweships and rw-rw---- or another permissions?
4.All the files worked by LGWR and DBWR what permissions might have?
5. all the operations over archiving can be done by another user other than dba? and how?

with respect
razvan
thanks a lot

and Tom said...

1) yes, the database will eventually halt. In archive log mode we will refuse to reuse an online redo log until it has been archived - either automatically or manually. If the file is not archived, we will eventually get to the point where we need to reuse it but cannot and we will stop until it is archived.


2) yes, if arch has not finished copying a redo log file, we will not be able to reuse it, we will HALT. The solution is to speed up arch (removing any contention). You may also consider tuning with the init.ora parameter: log_archive_buffer_size

adding more online redo logs will in all probability help the problem as well.

3) the destination files will typically be assigned "-rw-r-----" perms (owner = rw, group = r, other= nothing)

4) they are typically -rw-r----- as well.

5) archiving is performed by the database either automatically or manually via a SQL command. It will be done by the owner of the database software, it can be initiated by anyone with the appropriate "alter system" privs.





Rating

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What does archive log mode command do?

Martin Astill, July 22, 2003 - 8:11 pm UTC

Tom,

what does running the alter db archive log mode do?
(besides the obvious). I mean does it update
header files, oracle dictionary etc etc

my reason for asking is this, I know of someone who
took a cold backup of a Db in non archive log mode
when they restarted they switched to archive log mode.
Can any of the files backedup in non archive log mode
be used in a recovery scenario and apply archived redo logs
that were generated since the DB was placed in archive log mode?

Regards

Martin



Tom Kyte
July 22, 2003 - 8:27 pm UTC

you cannot use a NOARCHIVELOG mode database backup for anything other then a full restore AS OF the point in time of the backup (eg: you cannot roll forward and recover the work performed during the time between the backup and the recovery)

Martin Astill, July 22, 2003 - 9:14 pm UTC

Hey! aren't you supposed to be on vacation

Thanks very much for taking the time to answer my
question.

Regards
Martin


One Question

Riaz Shahid, July 23, 2003 - 4:13 am UTC

How can we delete archive logs ??? I've delted them but they are still shown as 'not deleted'.

Regards

Tom Kyte
July 23, 2003 - 7:55 am UTC

what is showing them as "not deleted"

Information

Riaz Shahid, July 23, 2003 - 8:49 am UTC

The deleted column in V_$ARCHIVED_LOG has value "NO".

Riaz

Tom Kyte
July 23, 2003 - 9:58 am UTC

so? did you use our tools to manage the logs or are you just "erasing them"

as far as we know, they are NOT deleted -- they are on tape, they are moved, they are shipped to another system -- they could exist. so what if you erased them?

the definition of the deleted column, as per the documentation (the Oracle Reference Guide) is simply:

Specifies (YES | NO) whether an RMAN DELETE command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog.



So, unless you are using RMAN, this column isn't meaningful.

More question on Archived log files

David Jiang, October 02, 2003 - 6:29 pm UTC

Tom,

Would you please tell me in detail:
1. if I can delete the archived log files after each backup? when can I delete it if not?
2. How can I delete the archived log files mannually? What tool do you have to manage/delete the archived log?
3. How much or how long back should I keep the archived log file after backup on each weekend?
4. What is the best recommendation you have for me to manage the archived log file so that the files would not pile up and take a lot of spaces?

Thanks a lot.

Tom Kyte
October 02, 2003 - 6:56 pm UTC


I would recommend you use RMAN -- it'll be your "book-keeper" and won't delete things you actually need.

For every backup you have -- you need every archive log generated since the backup BEGAN.

So, if you keep 3 backups -- as you must -- and the oldest started at midnight on sunday, you need ALL of the archives generated since then.

You need to get the archives off of your system -- onto something else (eggs, single basket pops into mind otherwise). Once you get them off - it is common practice to keep as many on disk as possible -- faster then getting them back onto the system when you need them. You can get rid of whatever ones you want though (on disk) at that point in time -- once they are safely copied 2 or 3 other places.

Use rman.

FGRD Vs ARCH

Sami, December 12, 2003 - 11:08 am UTC

Dear Tom,
What is the difference FGRD and ARCH in v$archived_log.creator column.
I understand FGRD is foreground process. Exactly in what scenario the value will be populated as FGRD?

I see something like below in my db.
select recid,creator from v$archived_log
RECID CREATOR
----- -------
1 FGRD
2 ARCH
3 ARCH
4 ARCH
5 ARCH
6 ARCH
7 ARCH
8 FGRD
8 rows selected


Tom Kyte
December 13, 2003 - 11:00 am UTC 

Ok, so, i set log_archive_start so I could manually archive then:

ops$tkyte@ORA920> @a
 
     RECID CREATOR
---------- -------
        61 ARCH
        62 ARCH
        63 ARCH
        64 ARCH
        65 ARCH
        66 ARCH
        67 ARCH
        68 ARCH
        69 ARCH
        70 ARCH
 
10 rows selected.

<b>all things had been done in the past by arch -- now:</b>
 
ops$tkyte@ORA920> alter system switch logfile;
 
System altered.
 
ops$tkyte@ORA920> alter system archive log all;
 
System altered.
 
ops$tkyte@ORA920> @a
 
     RECID CREATOR
---------- -------
        63 ARCH
        64 ARCH
        65 ARCH
        66 ARCH
        67 ARCH
        68 ARCH
        69 ARCH
        70 ARCH
        71 FGRD
        72 FGRD
 
10 rows selected.
 

<b>some of them are done by the foreground (me, my process) not arch.  This just means someone manually archived at some point</b>

Not able to reproduce Win/2000 Oracle 9.2.0.4

Saminathan Seerangan, December 14, 2003 - 10:56 am UTC 

Dear Tom

I am not able reproduce the same using both SYS and NON-SYS users.

SQL> connect sys/change_on_install@sami as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\ora92\RDBMS
Oldest online log sequence     1241
Next log sequence to archive   1243
Current log sequence           1243
SQL> connect test/test@sami
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.


SQL> select sequence#,creator,registrar from v$archived_log where sequence# >=12

 SEQUENCE# CREATOR               REGISTRAR
---------- --------------------- ---------------------
      1234 ARCH                  ARCH
      1235 ARCH                  ARCH
      1236 ARCH                  ARCH
      1237 ARCH                  ARCH
      1238 ARCH                  ARCH
      1239 ARCH                  ARCH
      1240 ARCH                  ARCH
      1241 ARCH                  ARCH
      1242 ARCH                  ARCH
      1243 ARCH                  ARCH
      1244 ARCH                  ARCH

 SEQUENCE# CREATOR               REGISTRAR
---------- --------------------- ---------------------
      1245 ARCH                  ARCH

12 rows selected.

SQL>

Ignore my last posting

A reader, December 14, 2003 - 11:02 am UTC

I should use "alter system archive log all"

archiving files too fast

jasdeep, March 22, 2004 - 8:41 pm UTC

hi tom

in our database sometimes archive files occurs very fast like 35 files in one hour. at other times it 1 file in 4 hours as load of work is not in much differnce. how is this occurring.
we have ibm - aix oracle 9.0.2 environment



Tom Kyte
March 22, 2004 - 9:28 pm UTC

wellll, i would say "the workload is very much different"

it has to be, else you would not observe this.


you can use logminer to see "whats in the log".

review

sven, July 29, 2004 - 6:42 pm UTC

Hi Tom,

If I have a database in noarchivelog mode and execute:
alter system switch logfile

the previous log group become a status 'ACTIVE' and current status 'CURRENT' in V$LOG view.

If I execute:
alter system checkpoint

the previous log group become a status 'INACTIVE' and current status 'CURRENT' in V$LOG view.

"ACTIVE - The log is active but is not the current log. It is needed for
crash recovery. It may be in use for block recovery. It might or might
not be archived."

I can not drop archive log group (or a member of a group) if a group is in a 'ACTIVE' status
but command "alter system switch logfile" cause a checkpoint to occur and according to this the status of previous group should be 'INACTIVE'.
Could you please give me some hint about this.

Thanks,

Sven

Tom Kyte
July 30, 2004 - 7:15 am UTC

the switch INITIATES a checkpoint, lazy, in the background, so as to not flood the system.


alter system checkpoint says "do it, do it now, do it fast and don't return control to my session until it is complete"


if you switch the logfile, wait an appropriate amount of time (whilst dbwr trickles the data out) and then check, you would see the same effect.

A reader, January 24, 2005 - 11:33 am UTC

Tom,

The standby database directory doesn't show some of the archive log files. i.e some of them at standby are either missing, accidentally deleted .. I think this would've brought standby to some inconsistent state. But how to check if all logs are applied on standby?

Tom Kyte
January 24, 2005 - 11:42 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96653/manage_ps.htm#1005611 <code>

A reader, January 24, 2005 - 11:42 am UTC

Tom,

To expand the above question a little bit .. The primary has arch files since Jan 21 9 am. Standby has arc log files since Jan 22 7 am. i.e some of the files from primary have either not flowed or have flowed applied at standby and have been deleted. Is there a way to know what would've happened? I am unable to see anything at standby, because it is currently under recovery mode.

Tom Kyte
January 24, 2005 - 2:00 pm UTC

did you check out that chapter i pointed you to? it shows how to see whats what with regards to standby databases.

archivelog mode - some questions

Alex Leong, May 26, 2005 - 11:32 am UTC

Good morning Tom

Can you please comment on the following ?

select sequence#, to_char(completion_time, 'mm/dd/yyyy hh24:mi:ss') from v$archived_log order by completion_time

26110 05/12/2005 03:37:25
26111 05/12/2005 03:55:17
26113 05/12/2005 04:17:16
26112 05/12/2005 04:23:32
26114 05/12/2005 09:57:03

Sequence 26112 has a later completion time than 26113. (Normally both columns are in the same order)

What has happened here ?

Thanks


Tom Kyte
May 26, 2005 - 12:58 pm UTC

could be that both needed archiving at the same time. you had multiple archivers and the one finished before the first.

for example.

ARCHIVING THREAD

gaspard, July 05, 2005 - 9:33 am UTC

the articel on issues re archiving from prod to standby was very informative, keep it up!

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.