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:
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.
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?
Yes its very useful, I often use this whenever I have to create a standby database. I am using 10g.
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.
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
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
Hi,
I think 9i doesn't support ALTER DATABASE BEGIN BACKUP statement. please check it.
Regards,
Mritunjay
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.
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
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
Keyur and Manju,
Which patch set of Oracle9iR2 are u on? Try on 9204 patch set applied db.
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!
I want to know which location oracle will create backup files for online backup?
Post a Comment