1

Recently I discovered in a legacy Oracle 8.1.7.3.0 database that the tablespace holding de AUD$ table has been in backup mode for six months. Dunno who issued the begin backup command on it six moths ago.

  • The scheduled backups are cold backups that will not issue a end backup command on any tablespace. The database can be shutdown and started up with no problemo.
  • The datafiles belonging to the AUD$ tables have a modification date of just right now.
  • When you query the AUD$ table you can see that it's being updated almost every minute. This table is inserted data continously.
  • I understand that REDO information is generated when a datafile is in active backup mode but that tablespace has been un backup mode for six months for Pete's sake.

My questions:

  • Can this tablespace cotinue in active backup mode forever with no consequences?
  • Is there a risk of losing data?
  • Is this a disaster waiting to happen?
  • What exactly is happening here?
1

When we put the tablespace in backup mode Oracle copies whole changed data blocks into the redo stream in order to make the backup safe from fractured block. Copying whole data block instead of only the change may degrade the performance. Oracle strongly recommends to put the tablespace in backup mode only when required.

What Happens When A Tablespace/Database Is Kept In Begin Backup Mode (Doc ID 469950.1) (MOS Account needed)

The following is the excerpt from the Oracle Documentation.

Caution:

If you fail to take the tablespace out of backup mode, then Oracle Database continues to write copies of data blocks in this tablespace to the online redo logs, causing performance problems. Also, you receive an ORA-01149 error if you try to shut down the database with the tablespaces still in backup mode.

Making User-Managed Backups of Online Tablespaces and Data Files

  • 1
    So given what you said, the OP should have dozens of "ORA-01149" errors in his alert log and should probably fine tune his her DB monitoring procedures to catch these types of errors so that this could be detected earlier if it happens again. He/she should also upgrade to 11g or 12c as soon as possible, since 8i has been out of support for nearly 10 years. – Gandolf989 Apr 12 '16 at 13:30
  • If I issue the end backup command after six months, will it happily tell me tablespace altered or will it need to apply old archived logs (media recovery)? – Tulains Córdova Apr 12 '16 at 14:36
  • @user1598390 We need to apply archivelogs starting from the moment the tablespace was brought in backup mode because the data file headers of the data files of that tablespace were not check pointed since then. – JSapkota Apr 12 '16 at 14:47
  • 2
    Issuing the 'end backup' will not affect the database itself. You only "apply archived logs" when doing a recovery, not on the running database. Just go ahead and issue the 'end backup'. I'd then take a backup according to your normal procedures. I'd also second that 8.1 is looooong past due for an upgrade. After which you can start using rman for your backups and leave all this putting tablespaces "in backup modde" behind. – EdStevens Apr 12 '16 at 23:11
  • @EdStevens Man you are right. I did the end backup and nothing happened. Please write up an answer so I can accept it. Please answer the four parts of my question. – Tulains Córdova Apr 13 '16 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.