TRUNCATE and ARCHIVELOG mode

TRUNCATE and ARCHIVELOG mode

Post by Alexei Fo » Tue, 17 Feb 1998 04:00:00



Hi,
It has recently been brought to my attention that on some DBMS (Sybase,
others) the operations like TRUNCATE or direct SQL*Loader load will
invalidate the archives generated after this operation. The reason being
that such operations are not logged, and therefore cannot be reproduced
later in case of media failure recovery. I did not find any warning in
Oracle manuals, and, luckily didn't have a chance to check it in real
life.
Does anyone have any comments on how Oracle handles the issue?
Thanks,
Alexei Fox

 
 
 

TRUNCATE and ARCHIVELOG mode

Post by John P. Higgin » Tue, 17 Feb 1998 04:00:00


From the Oracle Server Utilities Manual:

Specifying RECOVERABLE and UNRECOVERABLE
The following options apply to direct path loads:
RECOVERABLE
Loaded data is logged in the redo log. This option is
the default for direct path loads. All conventional
loads are recoverable.
UNRECOVERABLE
This option can be specified for a direct path load
only. Loaded data is not logged, which improves
load performance. (Other changes to the database
are logged.) For details, see page 8-9. This option
cannot be specified with a conventional load.

Specifying UNRECOVERABLE
Use UNRECOVERABLE to save time and space in the redo log file. An
UNRECOVERABLE load does not record loaded data in the redo log file,
instead, it generates invalidation redo. Note that UNRECOVERABLE applies
to all objects loaded during the load session (both data and index
segments.)
Therefore, media recovery is disabled for the loaded table, although
database changes by other users may continue to be logged.
Note: Because the data load is not logged, you may want to make a
backup of the data after loading.
If media recovery becomes necessary on data that was loaded with the
UNRECOVERABLE phrase, the data blocks that were loaded are marked as
logically corrupted.
To recover the data, drop and re-create the data.


> Sorry to hop on to your post, but I have a similar question for the
> oposite reason:

> I would also like to know if there is a way to do a SQL*Load which does
> not create ARCHIVE logs.


 
 
 

TRUNCATE and ARCHIVELOG mode

Post by Keith E. Moo » Wed, 18 Feb 1998 04:00:00


Sorry to hop on to your post, but I have a similar question for the
oposite reason:

I would also like to know if there is a way to do a SQL*Load which does
not create ARCHIVE logs.  We have one table which is truncated and fully
reloaded on a regular basis, but we do not need to recover it in case of
failure (becuase we can recreate it by other means).  This table is
large, and therefore creates HUGE archives, which we don't need.
Currently we keep this table in a seperate instance with ARCHIVELOG
disabled, but running two instances wastes memory because of the
dedicated memory for the SGA.


>Hi,
>It has recently been brought to my attention that on some DBMS (Sybase,
>others) the operations like TRUNCATE or direct SQL*Loader load will
>invalidate the archives generated after this operation. The reason being
>that such operations are not logged, and therefore cannot be reproduced
>later in case of media failure recovery. I did not find any warning in
>Oracle manuals, and, luckily didn't have a chance to check it in real
>life.
>Does anyone have any comments on how Oracle handles the issue?
>Thanks,
>Alexei Fox

--
-- Keith Moore
   President
   KMA Computer Solutions, Inc.

--
/*----C/C++--Java--VB--Pro*C--SQL--OCI--Java--Delphi--ODBC--COBOL-----*
 *        When the project must be saved at all costs:                *
 * KMA Computer Solutions, Inc.   Project Troubleshooting/Recovery    *
 *---------Linux---AIX---HPUX---SYSV---Novell---NT---OS/2---'95-------*/

 
 
 

TRUNCATE and ARCHIVELOG mode

Post by Ingo Farche » Wed, 18 Feb 1998 04:00:00


I think

ALTER TABLE .. NOLOGGING

should do.

Ingo


> Sorry to hop on to your post, but I have a similar question for the
> oposite reason:

> I would also like to know if there is a way to do a SQL*Load which does
> not create ARCHIVE logs.  We have one table which is truncated and fully
> reloaded on a regular basis, but we do not need to recover it in case of
> failure (becuase we can recreate it by other means).  This table is
> large, and therefore creates HUGE archives, which we don't need.
> Currently we keep this table in a seperate instance with ARCHIVELOG
> disabled, but running two instances wastes memory because of the
> dedicated memory for the SGA.


> >Hi,
> >It has recently been brought to my attention that on some DBMS (Sybase,
> >others) the operations like TRUNCATE or direct SQL*Loader load will
> >invalidate the archives generated after this operation. The reason being
> >that such operations are not logged, and therefore cannot be reproduced
> >later in case of media failure recovery. I did not find any warning in
> >Oracle manuals, and, luckily didn't have a chance to check it in real
> >life.
> >Does anyone have any comments on how Oracle handles the issue?
> >Thanks,
> >Alexei Fox

> --
> -- Keith Moore
>    President
>    KMA Computer Solutions, Inc.

> --
> /*----C/C++--Java--VB--Pro*C--SQL--OCI--Java--Delphi--ODBC--COBOL-----*
>  *        When the project must be saved at all costs:                *
>  * KMA Computer Solutions, Inc.   Project Troubleshooting/Recovery    *
>  *---------Linux---AIX---HPUX---SYSV---Novell---NT---OS/2---'95-------*/

 
 
 

TRUNCATE and ARCHIVELOG mode

Post by Alexei Fo » Wed, 18 Feb 1998 04:00:00


The question remains "What happens to the OTHER database operation (by the
same or other users) logs after the direct load with unrecoverable option is
done? Will they ALL be invalid after that?". Imagine the loaded data was
lated used in UPDATE statements - they probably will not be recovered. How
about others? I doubt DBMS will be smart enough to do partial recovery...

Alexei Fox


> From the Oracle Server Utilities Manual:

> Specifying RECOVERABLE and UNRECOVERABLE
> The following options apply to direct path loads:
> RECOVERABLE
> Loaded data is logged in the redo log. This option is
> the default for direct path loads. All conventional
> loads are recoverable.
> UNRECOVERABLE
> This option can be specified for a direct path load
> only. Loaded data is not logged, which improves
> load performance. (Other changes to the database
> are logged.) For details, see page 8-9. This option
> cannot be specified with a conventional load.

> Specifying UNRECOVERABLE
> Use UNRECOVERABLE to save time and space in the redo log file. An
> UNRECOVERABLE load does not record loaded data in the redo log file,
> instead, it generates invalidation redo. Note that UNRECOVERABLE applies
> to all objects loaded during the load session (both data and index
> segments.)
> Therefore, media recovery is disabled for the loaded table, although
> database changes by other users may continue to be logged.
> Note: Because the data load is not logged, you may want to make a
> backup of the data after loading.
> If media recovery becomes necessary on data that was loaded with the
> UNRECOVERABLE phrase, the data blocks that were loaded are marked as
> logically corrupted.
> To recover the data, drop and re-create the data.


> > Sorry to hop on to your post, but I have a similar question for the
> > oposite reason:

> > I would also like to know if there is a way to do a SQL*Load which does
> > not create ARCHIVE logs.

 
 
 

TRUNCATE and ARCHIVELOG mode

Post by John P. Higgin » Wed, 18 Feb 1998 04:00:00


As I read it, all subsequent uses of the unrecoverable data is also
unrecoverable -- until it is backed up. Accept the risk OR don't allow
updates (to the unrecoverable data) until the backup is done.


> The question remains "What happens to the OTHER database operation (by the
> same or other users) logs after the direct load with unrecoverable option is
> done? Will they ALL be invalid after that?". Imagine the loaded data was
> lated used in UPDATE statements - they probably will not be recovered. How
> about others? I doubt DBMS will be smart enough to do partial recovery...

> Alexei Fox


> > From the Oracle Server Utilities Manual:

> > Specifying RECOVERABLE and UNRECOVERABLE
> > The following options apply to direct path loads:
> > RECOVERABLE
> > Loaded data is logged in the redo log. This option is
> > the default for direct path loads. All conventional
> > loads are recoverable.
> > UNRECOVERABLE
> > This option can be specified for a direct path load
> > only. Loaded data is not logged, which improves
> > load performance. (Other changes to the database
> > are logged.) For details, see page 8-9. This option
> > cannot be specified with a conventional load.

> > Specifying UNRECOVERABLE
> > Use UNRECOVERABLE to save time and space in the redo log file. An
> > UNRECOVERABLE load does not record loaded data in the redo log file,
> > instead, it generates invalidation redo. Note that UNRECOVERABLE applies
> > to all objects loaded during the load session (both data and index
> > segments.)
> > Therefore, media recovery is disabled for the loaded table, although
> > database changes by other users may continue to be logged.
> > Note: Because the data load is not logged, you may want to make a
> > backup of the data after loading.
> > If media recovery becomes necessary on data that was loaded with the
> > UNRECOVERABLE phrase, the data blocks that were loaded are marked as
> > logically corrupted.
> > To recover the data, drop and re-create the data.


> > > Sorry to hop on to your post, but I have a similar question for the
> > > oposite reason:

> > > I would also like to know if there is a way to do a SQL*Load which does
> > > not create ARCHIVE logs.

 
 
 

1. place in ARCHIVELOG mode

I have Oracle8 on WinNT4.0.
How can I put the database in ARCHIVELOG mode?
When it is started, it tells me that I have to stop it.
When it is stopped I can't connect to it.
Please mail to  me.
Thanks in advance.

----------------------------
Vagelis S. Hristidis
Electrical and Computer Engineering
National Technical University of Athens

URL:http://www.cc.ece.ntua.gr/~exrist

2. LANG or LC_COLLATE environment variable invalid

3. Cannot change to ARCHIVELOG mode

4. CRecordSet inside an ISAPI extension?

5. fail to start in ARCHIVELOG mode.

6. ADO 2.5

7. turn on auto archivelog mode

8. Help with Find

9. Known Problems in Archivelog mode

10. DB file not updated in ARCHIVELOG mode?

11. How to Alter NoARCHIVELOG mode to ARCHIVELOG

12. archivelog mode switch logfile statement

13. unable to startup in archivelog-mode