3.18.2007

ALTER DATABASE BEGIN BACKUP

Today I have learned a new thing about backup and recovery on how to keep the entire database in backup mode, instead of issuing separate BEGIN BACKUP statement for every tablespace.

I know Oracle strongly recommend of using RMAN for backup and recovery . I thought, this would be good for the DBAs who still use the legacy method of backup, i.e. ALTER TABLESPACE BEGIN/END BACKUP and if they are not aware of this new command in 9i and 10g versions.

Starting with version 9i(I dont know the exact release), Oracle gives the facility to put the entire database in backup by simply using the following command:

ALTER DATABASE BEGIN BACKUP;

ALTER DATABASE END BACKUP; -- to exit from the backup mode.

In version 9i, the above statement can be used only when the database is mounted, not opend. In 10g, this behavior changes. The statement can be executed while the database is open.

-- The following has done with Oracle 10gR1

SYS OCM AS SYSDBA>alter database begin backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

-- All the datafiles are in backup mode now

SYS OCM AS SYSDBA>alter database end backup;
Database altered.
SYS OCM AS SYSDBA>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

-- All the datafiles are out of backup mode now.

Happy reading,

13 comments:

Mohammed Aijaz Mohiuddin said...

Venerable Sir, I need your guidance.

We are aware that when we keep database in backup mode, excessive archive logs will be generated. As concern to the performance and generation of archive logs which one of the following method is good ?

[ASSUMPTION] It is hot time or daytime.

1) Keeping the whole database in backup mode and backing up. All the tablespaces are in backup mode. DML's against any tablespace will lead to complete copy of concern dirty block to redo.

2) One after another, keeping tablespace in backup mode and backing up. This will keep that concern tablespace in backup mode. DML's against this particular tablespace will lead to complete copy of dirty block to redo. DML's against any other tablespace will not lead to complete copy of dirty block to redo.


Thanks and Best Regards,

Mohammed Aijaz Mohiuddin.

Don said...

Depending on database activity at the time of your backup, wouldn't this create a LOT more redo than if you were to just go tablespace-by-tablespace?

Fahd Mirza said...

Yes its very useful, I often use this whenever I have to create a standby database. I am using 10g.

The Human Fly said...

Aijaz,

Yes, it is true that when a tablespace kept in backup mode and DML take place on this tablespace, oracle definately generates excessive redo in order to resolve fractured/spilt block.

Oracle strongly recommends to have this kind of backup during off-peak hours.

The single command is just an extension to avoid putting all the tablespace manually in the backup mode one after one.

I dont think there is any change in the internal mechanisim of this type of backup.

Anonymous said...

Jaffar,

This is introduced to support the to take BCV copies of the data files (in the triple mirroring disk array).

In a triple mirroring disk array we just split one mirror by putting the entire database in backup mode for a second and then split the mirrors and take the database out of backup mode. So the database will be in Backup mode only for few seconds.

Once we split the mirror, we mount the mirror to another server (media server) and make backups from that server. In this way, we do not depend on CPU/IO cycles on the database host. Once the backup is done, we just resilver the mirrors and the database is again running with triple mirror till we break the storage again.

The above is a rather simplified note.There are much more than tbe above said..

-Gopal

Alex Gorbachev said...

Gopal,
We also used it for that but fallen back to switching each datafile to backup mode - this helps to smoothen sharp spike of redo generation increase if backup mode enabled for all datafiles at once. We introduced a delay between each datafile and this way redo log generation spike is smoother even though the process takes a bit longer.
Cheers,
Alex

Anonymous said...

Hi,
I think 9i doesn't support ALTER DATABASE BEGIN BACKUP statement. please check it.

Regards,
Mritunjay

The Human Fly said...

Mritunjay,

ALTER DATABASE BEGIN BACKUP command very well exists in 9i, (I guess in R2) with the limitation that the database must be in mount state in order to successfully execute the command, otherwise, the command will fail with the 'command can't be recognized' error.

Anonymous said...

Hi,
I am using 9i R2. But when i issue ALTER DATABASE BEGIN BACKUP in mount state, it give error.

SQL>alter database begin backup;
alter database begin baclup
*
ERROR at line 1:
ORA-00905: missing keyword

Regards,
Mritunjay

Keyur said...

agree with you Mritunjay,
It's not working in 9iR2 ..

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> show release
release 902000100

SQL> alter database begin backup;
alter database begin backup
*
ERROR at line 1:
ORA-00905: missing keyword

The Human Fly said...

Keyur and Manju,

Which patch set of Oracle9iR2 are u on? Try on 9204 patch set applied db.

Anonymous said...

Can I ask a question here: I run a script that executes the vbackup command with the -restore.(I have a versant db)
I want to run this command automatically and bypass answering the question that asks me if I want another restore..How can I do it?

Thanks!

Rajeev said...

I want to know which location oracle will create backup files for online backup?