What happens when the database is in Begin backup mode?

What happens when the database is in Begin backup mode?

When a hot backup runs in the oracle, first we will make the database in begin backup mode and then we copy the database files to tape or disk. The question that most of us will have is: what happens to the datafiles when the database is in begin backup mode?

Offline? Or frozen? Answer is No.

“Some of the Oracle books contains that the database files are not writable during the backup mode and the changes are stored in the SGA, redo logs and Rollback segments. And these changes will be the written into the data files when the database is taken out of begin backup mode. “

But the above predictions are opposed. It does not stop writing to the data files. The database almost works in normal way when the database in the backup mode. It can be summarized in as follows:

·         DBWn process writes  out all the dirty blocks of a particular SCN, i.e. when a check point process runs, the DBWn process writes all the blocks related to that SCN generated.

·         CKPT process stops updating the checkpoint SCN field in the data files headers and   begins updating the Hot backup checkpoint SCN field in the data files headers.

·         LGWR process begins logging of the changed blocks.

By freezing the checkpoint SCN in the data file headers, any subsequent recovery on that particular hot backup will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the archivelog containing that SCN, and applies recovery starting there.

Here we need to know that in hot backup mode the checkpoints to data files are not suppressed. The main checkpoint flag is frozen, but the hot backup Checkpoint SCN will be updated in the file header.

SQL> create table student(name varchar(20));

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                                       TABLE

EMP                                        TABLE

BONUS                                  TABLE

SALGRADE                      TABLE

T1                                            TABLE

STUDENT                        TABLE

RAMA                                    TABLE

7 rows selected.

Insert some values into the student table:

SQL> insert into student values('venkat');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

Check the block number and the datafile in which the data is stored.

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num,dbms_rowid.rowid_block_number(rowid) block_num, name from student;   

  FILE_NUM  BLOCK_NUM NAME

----------           ----------          --------------------

         4            68                    venkat

Check the data in the block number 68 with the dd command:

[oracle@dg2 ~]$ dd bs=8k skip=68 count=1 if=/u02/oradata/DBACLASS/users01.dbf |strings

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 6.9861e-05 seconds, 117 MB/s

Insert another row, commit the transaction and check the block and file information:

SQL> insert into student values('ram');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_rowid.rowid_relative_fno(rowid) "file num", dbms_rowid.rowid_block_number(rowid) "block num",name from student;

  file num  block num NAME

----------     ---------- --------------------

         4         68         venkat

         4         68         ram

Make the database in Backup mode:

SQL> alter tablespace users begin backup;

Tablespace altered.

Update the record in the student table:

SQL> update student set name='rama krishna' where name='ram';

1 row updated.

[oracle@dg2 ~]$ dd bs=8k skip=68 count=1 if=/u02/oradata/DBACLASS/users01.dbf |strings

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 6.5554e-05 seconds, 125 MB/s

ram,

venkat

Run the checkpoint process which creates an SCN and invoke the DBWn process to write the changed blocks to the datafiles:

SQL> alter system checkpoint;

System altered.

Now extract the information in the 68 block of that particular data file. We can observe that the changes made to the table (database) are written into the data file during the backup mode.

[oracle@dg2 ~]$ dd bs=8k skip=68 count=1 if=/u02/oradata/DBACLASS/users01.dbf |strings

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 7.0099e-05 seconds, 117 MB/s

rama krishna,

ram,

venkat

So, it is clear that the data files can be writable during the hot backup mode.

End the tablespace backup mode.

SQL> alter tablespace users end backup;

Tablespace altered.