Showing results for 
Search instead for 
Did you mean: 

Oracle archivelog mode !!

Go to solution
Chris Fung
Frequent Advisor

Oracle archivelog mode !!

Hi all,

Is it possible to recovery the system from archive logs if we only have:

1. export backup image
2. offline database backup
3. online database backup

Please help evaluate the possiblity and steps (if any) in the above 3 options.

Many thanks,

Brian Crabtree
Honored Contributor

Re: Oracle archivelog mode !!

1. No. The export backup can be recovered to a point in time only with subsequent exports (Cumulative and incremental exports)

2. Yes. Be careful not to open the database after mounting, as you will not be able to recover the database. Once the datafiles have been restored, put the database into a mount state and apply the logs to the database.

3. Yes. You will have to have all of the logs generated to the last tablespace "end backup" command.

Honored Contributor

Re: Oracle archivelog mode !!

Hi, chris

1. No, you can't recover from export backup image to the point of DB crash when it is in archive log mode.

2. It's possible provided if you have backup of log/data/control files backup till the point of DB crash.

3.Yes, If you have log files backup till the point of DB crash
never give up
Honored Contributor

Re: Oracle archivelog mode !!

Hi Chris,

A few comments.

First of all, today, where 24x7 is a requirement, EXP and IMP are not backup and recovery tool at all. They are relatively simple tools that should be used to move a modest amount of data back and forth between instances, or, by using transportable tablespaces, to move massive amounts of data. To use it to backup a 500GB database would be ludicrous.

Offline backup:
Copying of the datafiles, control file and online redo log files must be done by using an operating system copy utility. This is a considered as a complete backup of the database. Any changes made after this backup will be unrecoverable if the database is running in NOARCHIVELOG mode. All transactions are recorded in online redo log files whether archiving or not. When redo logs are archived (ARCHIVELOG mode), ORACLE allows you to apply these transactions after restoring files that were damaged (assuming an active Redo log file was not among the files damaged).

Online backup:
A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

You can use a backup taken while the database is open and in normal system use if you keep an archived log.

At sites where a database must operate 24-hours per day and when it is not feasible to take offline backups, then an alternative is provided by Oracle where physical backups are performed while the database remains available for both reading and updating. For this kind of backup the database must be in ARCHIVELOG mode. Only data files and current control file need to be backed up. Unlike offline backups, the unit of a online backup is a tablespace, and any or all tablespaces can backed up whenever needed. Different datafiles can be backed up at different times.

In general for Media Recovery using archived log, the following applies:

Manual recovery after media failure enables you to control how many redo log files to apply to the database. This can used to undo an inadvertent change to the database by stopping recovery before the change took place. MANUAL option needed for recovery with a control file backup (old copy) and current control file is not available. Database must be MOUNTed but not OPEN. After MOUNTing the database connect internal and issue RECOVER DATABASE MANUAL command. Then you will be prompted beginning with the earliest redo log file recorded in the header of each database file. The recovery process will continue to prompt for redo log files until CANCEL is typed when prompted for the next redo log file. Recovery can be cancelled at any time of any redo log.

Datafiles should have information of log change nbr (SCN), so Oracle knows which archived logs need to be applied to get DB on date. This can be done "usually":
- alter database mount
- recover database until cancel
- alter database open [resetlogs] (if uncomplete recovery)

If you need any further clarifications please let us know.

Hope this helps!
Best Regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Chris Fung
Frequent Advisor

Re: Oracle archivelog mode !!

Hi all,

Thanks for all your inputs especially for "Yogeeraj". Very much help !!

It seems online database backup is the only choice for a 24x7 database......however, what about for "Data warehouse"....during the day time, it has to sever the users and at night it has to perform the data loading processing......!! Turning on archivelog mode will greatly impact the data loading process...!!

We also can't afford not to perform backup on the data warehouse.....any suggestions other than using database export ?
Indira Aramandla
Honored Contributor

Re: Oracle archivelog mode !!

For datawarehouse most of the time the data will be read only. Only the data that is being loaded (current) data is writable. So you can partition the data in the tablespace. So that you need not backup the complete datawarehouse every day. Once the database has been backed up the read-only parth need not be backed up every day.

Other wise you can archive the read-only data i.e the earlier years data and configure the script that runs the hot backup (on-line) backups to backup only the current data that is beeing loaded.
Never give up, Keep Trying
Brian Crabtree
Honored Contributor

Re: Oracle archivelog mode !!

There are solutions out there that can split a backup mirror for a database. This will reduce the amount of time required for a backup. (HP Business Copy for XP Disk arrays for example).

Another option is to use rman. While this will not reduce the I/O, it does not require the database to be put in a "backup state" that generates additional redolog information.


Honored Contributor

Re: Oracle archivelog mode !!

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Steven E. Protter
Exalted Contributor

Re: Oracle archivelog mode !!

My very small points

rman can take pretty nice hot backups, but we have the luxury of taking ours cold.

I'm bookmarking this post and will try and get my dba to comment.

As someone with a few oracle recoveries under my belt, you got great help here. As you've already noted.

The only thing we use export/import for is migrating data between platforms or db versions.

Steven E Protter
Owner of ISN Corporation
Occasional Visitor

Re: Oracle archivelog mode !!

Hi all

When I use rman to restore backuped archivelog , I get such error:

RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 1697 scn 57340 found to restore
RMAN-06025: no backup of log thread 1 seq 1696 scn 57309 found to restore

can you help me?