Recovering Very Large Databases in the Least Amount of Time

Sometimes you come across beautiful things in your life, you wish you had known before, because they were there all the time, waiting for that moment to be discovered – by you.

Recently I discovered one of these things. Shamefully I confess, this brilliant feature of RMAN is around for some time – since 10g already – but I have never been lucky enough to explore it, until recently.
I am working for a customer now, who has a number of very large databases, all of them around the size of 12 to 25 Terabytes. These databases are all very heavily used by all kinds of activities; each of them is producing over 150 Gigabytes of redo per day.
Making a full online backup (to disk) of these databases takes more than a week – per database…

Can you imagine what it takes to restore and recover this kind of databases? The customer estimates around 2 weeks. This is not an option, because these databases hold data that is very frequently consulted and processed.

So, what do you do in such a case? Well, RMAN has a nice feature called backup as copy. This makes an image copy of your database and puts it into another location, defaulting to the Flash Recovery Area (assuming you have set up your parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE). I knew about this feature because I have migrated lots of databases from file system to ASM, for example.

What I didn’t know is that you can use incremental backups to recover this copy of the database. A jewel in the RMAN feature portfolio!

To make a full copy backup of your database:

allocate channel for maintenance type disk;configure controlfile autobackup on; configure default device type to disk;

run { BACKUP AS COPY DATABASE TAG ‘FULL_COPY’;}The above is not mandatory, the following script will do this for you as well.

allocate channel for maintenance type disk;configure controlfile autobackup on; configure default device type to disk;

run { RECOVER COPY OF DATABASE WITH TAG ‘IMG_COPY’ UNTIL TIME ‘SYSDATE – 1’; BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘IMG_COPY’ DATABASE PLUS ARCHIVELOG DELETE INPUT;copy current controlfile to ‘//cp_cntrl.ctl’; } OK, let’s break this down into small pieces:

RECOVER COPY OF DATABASE WITH TAG ‘IMG_COPY’ UNTIL TIME ‘SYSDATE – 1’;This command will not do anything until there is a backupset with the tag FULL_COPY. When there is a backupset, it will take it and recover the copy of the database using this backupset (see next command).

BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘IMG_COPY’ DATABASE PLUS ARCHIVELOG DELETE INPUT;This command will create a level 1 backup if there is a level 0 available. During the first run of this script, there is no level 0, so the level 0 will be created (which is basically equal to a backup as copy database).
During the second run of the script, a level 1 incremental backup will be created, which can be used in effect for the recover command as discussed above.

Basically, you are creating an image backup of your database in the Flash Recovery Area. This image backup can be used for restore and media recovery. Since this will be faster than conventional backups, it could be a solution to look at when you demand quick recovery. Well, nothing spectacular, this solution has been discussed a number of times on the Internet already.

In the situation my customer is in, we’re talking different stuff. The database is so large, taking the initial backup takes more than a week, let alone a restore, and we’re not even talking about recovery yet. So we chose a different approach to the ‘recovery’ of the database in case it becomes damaged.

RMAN has this tiny little command that does it all in this case:

RMAN> SWITCH DATABASE TO COPY;This only works when you have your controlfile available, but hey, that is what we provide for (twice) in the backup script, don’t we? And not for nothing.

copy current controlfile to ‘//cp_cntrl.ctl’;During the script we autobackup the controlfile and after the backup has been taken, an image copy of the controlfile is made to a separate location, eliminating the need to restore it from autobackup with all of its consequences. If somehow we have lost this image backup of the controlfile as well, we can always fall back to the autobackup.

When we want to recover our lost database in the least amount of time, we recover the copy of the database, assure the availability of the controlfile, then switch the database to the copy, which in effect updates the controlfile with the new location of all the datafiles. After this has been completed, we can open the database (with or without resetlogs, depending on incomplete or complete recovery), and off we go.

The database is available now, but you will be left with a database that doesn’t have a valid backup anymore. Remember we’re using the database copy in the FRA, which in essence was our backup. No panic, redefine the FRA (to e.g. the location of the original database) and start your rman scripts to create a new copy.
Optionally, if required, you can relocate your datafiles according to your needs. BTW, you can use RMAN for this as well:

RMAN> backup as copy datafile format '';RMAN> switch datafile to copy; The downside of this solution is that you would need twice the amount of diskspace for your database, because you are holding a copy of it on disk. But compare the cost of that against the cost of 2 weeks inavailability of the database. I think in most cases (with current storage costs) the costs will be far less than the benefits.

understanding the SCN

In order to understand how Oracle performs recovery, it’s first necessary to understand Oracle’s SCN in terms of the various places where it can be stored and how it’s used for instance and media recovery.

The SCN is an internal number maintained by the database management system (DBMS) to log changes made to a database. The SCN increases over time as changes are made to the database by Structured Query Language (SQL). By understanding how the SCN is used, you can understand how Oracle recovery works. Oracle9i enables you to examine the current SCN using the following SQL:

SQL> select dbms_flashback.get_system_change_number from dual;

Whenever an application commits a transaction, the log writer process (LGWR) writes records from the redo log buffers in the System Global Area (SGA) to the online redo logs on disk. LGWR also writes the transaction’s SCN to the online redo log file. The success of this atomic write event determines whether your transaction succeeds, and it requires a synchronous (wait-until-completed) write to disk.


Note: The need for a synchronous write upon commit is one of the reasons why the online redo log can become a bottleneck for applications and why you should commit as infrequently as is practical. In general, Oracle writes asynchronously to the database datafiles for performance reasons, but commits require a synchronous write because they must be guaranteed at the time they occur.

SCN and Checkpoints:

A checkpoint occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. The checkpoint process (CKPT) updates all datafiles and control files with the SCN at the time of the checkpoint and signals DBWn to write out the blocks. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during recovery. Checkpoints occur automatically as follows:

Whenever a redo log switch takes place
Whenever the time set by the LOG_CHECKPOINT_TIMEOUT initialization parameter is reached
Whenever the amount of redo written reaches the number of bytes associated with the LOG_CHECKPOINT_INTERVAL
Typically, LOG_CHECKPOINT_INTERVAL is chosen so that checkpoints only occur on log switches. Oracle stores the SCN associated with the checkpoint in four places: three of them in the control file and one in the datafile header for each datafile.

The System Checkpoint SCN:

After a checkpoint completes, Oracle stores the system checkpoint SCN in the control file. You can access the checkpoint SCN using the following SQL:

SQL> select checkpoint_change# from v$database;


CHECKPOINT_CHANGE#
--------------------
292767

The Datafile Checkpoint SCN:

After a checkpoint completes, Oracle stores the SCN individually in the control file for each datafile. The following SQL shows the datafile checkpoint SCN for a single datafile in the control file:

SQL> select name,checkpoint_change# from v$datafile where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 292767

The Start SCN:

Oracle stores the checkpoint SCN value in the header of each datafile. This is referred to as the start SCN because it is used at instance startup time to check if recovery is required. The following SQL shows the checkpoint SCN in the datafile header for a single datafile:

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 292767


The Stop SCN:

The stop SCN is held in the control file for each datafile. The following SQL shows the stop SCN for a single datafile when the database is open for normal use:

SQL> select name,last_change# from v$datafile where name like '%users01%';

NAME LAST_CHANGE#
----------------------------------- ------------
/u02/oradata/OMFD1/users01.dbf

During normal database operation, the stop SCN is NULL for all datafiles that are online in read-write mode. SCN Values while the Database Is Up Following a checkpoint while the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match. The stop SCN for each datafile in the control file is NULL. SCN after a Clean Shutdown After a clean database shutdown resulting from a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL of the database, followed by STARTUP MOUNT, the previous queries on v$database and v$datafile return the following:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293184

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%user%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------------------- -------------------- --------------
/u02/oradata/OMFD1/users01.dbf 293184 293184


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 293184

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.


SCN after an Instance Crash

The previous example showed the behavior of the SCN after a clean shutdown. To demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:
create table x(x number) tablespace users;

insert into x values(100);

If the instance is crashed by using SHUTDOWN ABORT, the previous queries on v$database and v$datafile return the following after the database is started up in mount mode:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
--------------------
293185

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%users01%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE#
----------------------------------- -------------------- --------------
/u02/oradata/OMFD1/users01.dbf 293185


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME CHECKPOINT_CHANGE#
----------------------------------- --------------------
/u02/oradata/OMFD1/users01.dbf 293185

In this case, the stop SCN is not set, which is indicated by the NULL value in the LAST_CHANGE# column. This information enables Oracle, at the time of the next startup, to determine that the instance crashed because the checkpoint on shutdown was not performed. If it had been performed, the LAST_CHANGE# and CHECKPOINT_CHANGE# values would match for each datafile as they did during a clean shutdown. If an instance crashes at shutdown, then instance crash recovery is required the next time the instance starts up.

Recovery from an Instance Crash

Upon the next instance startup that takes place after SHUTDOWN ABORT or a DBMS crash, the Oracle DBMS detects that the stop SCN for datafiles is not set in the control file during startup. Oracle then performs crash recovery. During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back. In our example, the following transaction was active but not committed at the time of the SHUTDOWN ABORT, so it needs to be rolled back:

SQL> insert into x values(100);

After instance startup, the X table exists, but remains empty. Instance recovery happens automatically at database startup without database administrator (DBA) intervention. It may take a while because of the need to apply large amounts of outstanding redo changes to data blocks for transactions that completed and those that didn’t complete and require roll back.

Recovery from a Media Failure

Up until this point, the checkpoint start SCN in the datafile header has always matched the datafile checkpoint SCN number held in the control file. This is reasonable because during a checkpoint, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are both updated, along with the system checkpoint SCN. The following SQL shows the start SCN from the datafile header and datafile checkpoint SCN from the control file for the same file:

SQL> select 'controlfile' "SCN location",name,checkpoint_change# from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change# from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
-------------- ----------------------------------- --------------------
controlfile /u02/oradata/OMFD1/users01.dbf 293188
file header /u02/oradata/OMFD1/users01.dbf 293188

Unlike the v$datafile view, there is no stop SCN column in the v$datafile_header view because v$datafile_header is not used at instance startup time to indicate that an instance crash occurred. However, the v$datafile_header does provide the Oracle DBMS with the information it requires to perform media recovery. At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is
required.

For example, media recovery is required if a media failure has occurred and the original datafile has been replaced with a backup copy. In this case, the start SCN in the backup copy is less than the checkpoint SCN value in the control file, and Oracle requests archived redo logs—generated at the time of previous log switches—in order to reapply the changes required to bring the datafile up to the current point in time.

In order to recover the database from a media failure, you must run the database in ARCHIVELOG mode to ensure that all database changes from the online redo logs are stored permanently in archived redo log files. In order to enable ARCHIVELOG mode, you must run the command ALTERDATABASE ARCHIVELOG when the database is in a mounted state.

You can identify files that need recovery after you have replaced a datafile with an older version by starting the instance in mount mode and running the following SQL:

SQL> select file#,change# from v$recover_file;

FILE# CHANGE#
---------- ----------
4 313401

In this example, file 4 is the datafile in the USERS tablespace. By reexecuting the previous SQL to display the datafile checkpoint SCN in the control file and the start SCN in the datafile header, you can see that the start SCN is older due to the restore of the backup datafile that has taken place:

SQL> select 'controlfile' "SCN location",name,checkpoint_change#
from v$datafile where name like '%users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
-------------- ----------------------------------- --------------------
controlfile /u02/oradata/OMFD1/users01.dbf 313551
file header /u02/oradata/OMFD1/users01.dbf 313401
If you were to attempt to open the database, you would receive errors like the following:
ORA-01113: file 4 needs media recovery
ORA-01110: datafile 4: '/u02/oradata/OMFD1/users01.dbf'
You can recover the database by issuing RECOVER DATABASE from SQL*Plus while the database is in a mounted state. If the changes needed to recover the database to the point in time before the crash are in an archived redo log, then you will be prompted to accept the suggested name:
ORA-00279: change 313401 generated at 11/10/2001 18:50:23 needed for thread
ORA-00289: suggestion : /u02/oradata/OMFD1/arch/T0001S0000000072.ARC
ORA-00280: change 313401 for thread 1 is in sequence #72

Specify log: {=suggested | filename | AUTO | CANCEL}

If you respond to the prompt using AUTO, Oracle applies any archived redo logs it needs, followed by any necessary changes in the online redo logs, to bring the database right up to the last committed transaction before the media failure that caused the requirement for the restore.

So far, we’ve considered recovery scenarios where the goal is to recover the database to the most recent transaction. This is known as complete recovery. The RECOVER DATABASE command has several other options that enable you to recover from a backup to a point in time before the most recent transaction by rolling forward and then stopping the application of the redo log changes at a specified point. This is known as incomplete recovery. You can specify a time or an SCN as the recovery point. For example,

recover database until time '2001-11-10:18:52:00';
recover database until change 313459;

Before you perform incomplete recovery, it’s recommended that you restore a complete database backup first. After incomplete recovery, you must open the mounted database with ALTER DATABASE OPEN RESETLOGS. This creates a new incarnation of the database and clears the contents of the existing redo logs to make sure they can’t be applied.

Recovery from a Media Failure Using a Backup Control File

In the previous example, we had access to a current control file at the time of the media failure. This means that none of the start SCN values in the datafile headers exceeded the system checkpoint SCN number in the control file. To recap, the system checkpoint number is given by the following:

SQL> select checkpoint_change# from v$database;

You might be wondering why Oracle needs to maintain the last system checkpoint value in the control file as well as checkpoint SCNs in the control file for each datafile (as used in the previous example). There are two reasons for this. The first is that you might have read-only tablespaces in your database. In this case, the database checkpoint SCN increases, and the checkpoint SCN for the datafiles in the read-only tablespace remains frozen in the control file.

The following SQL report output shows a database with a read-write tablespace (USERS) and read-only tablespace (TEST). The start SCN in the file header and the checkpoint SCN in the control file for TEST are less than the system checkpoint value. Once a tablespace is read only, checkpoints have no effect on the files in it. The other read-write tablespace has checkpoint values that match the system checkpoint:

SCN location NAME CHECKPOINT_CHANGE#
-------------------- ---------------------------------- ----------------
controlfile SYSTEM checkpoint 355390
file header /u02/oradata/OD2/users01.dbf 355390
file in controlfile /u02/oradata/OD2/users01.dbf 355390
file header /u02/oradata/OD2/test01.dbf 355383
file in controlfile /u02/oradata/OD2/test01.dbf 355383

The second reason for the maintenance of multiple checkpoint SCNs in the control file is that you might not have a current control file available at recovery time. In this case, you need to restore an earlier control file before you can perform a recovery. The system checkpoint in the control file may indicate an earlier change than the start SCN in the datafile headers.

The following SQL shows an example where the system checkpoint SCN and datafile checkpoint SCN indicate an earlier change than the start SCN in the datafile header:

SQL> select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change#
from v$database
union
select 'file in controlfile',name,checkpoint_change#
from v$datafile where name like 'users01%'
union
select 'file header',name,checkpoint_change#
from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE#
------------------- ------------------------------ ------------------
controlfile SYSTEM checkpoint 333765
file header /u02/oradata/OD2/users01.dbf 355253
file in controlfile /u02/oradata/OD2/users01.dbf 333765

If try you to recover a database in the usual way in this situation, Oracle detects that the control file is older than some of the datafiles, as indicated by the checkpoint SCN values in the datafile headers, and reports the following message:

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

If you want to proceed with recovery in this situation, you need to indicate to Oracle that a noncurrent control file—possibly containing mismatches in the SCN values identified by the previous error messages—is about to be specified for recovery by using the following command:
recover database using BACKUP CONTROLFILE;

Oracle tablespace hot backup mode revisited

Oracle’s pre-RMAN hot backup mode is the subject of one of the most pervasive and persistent misconceptions about Oracle.
During an Oracle tablespace hot backup, your (or your script) puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode. These steps are widely understood by most DBAs.
However, there is a popular misconception that datafiles are “quiesced,” “frozen,” “offlined” or “locked” during backup mode. So many people think it is true, that it appears in some books on Oracle and on numerous websites. Some have even reported that they learned this from DBA class instructors.
The myth has a couple permutations. One is that while the datafiles are allegedly not writable, changes are stored somewhere in the SGA, the redologs, the rollback segments or some combination thereof, then written back into the datafile when the tablespace is taken out of backup mode. There is a passage in the SAMS title Oracle Unleashed describing this supposed mechanism.
When you place a tablespace in backup mode, the Oracle instance notes that a backup is being performed and internally compensates for it. As you know, it is impossible to make an authentic copy of a database file that is being written to. On receipt of the command to begin the backup, however, Oracle ceases to make direct changes to the database file. It uses a complex combination of rollback segments, buffers, redo logs, and archive logs to store the data until the end backup command is received and the database files are brought back in sync. Simplifying a hot backup in this way is tantamount to classifying the USS Nimitz as a boat. The complexity of the actions taken by the Oracle RDBMS under a hot backup could consume an entire chapter and is beyond the scope of this book. What you should understand is the trade-off for taking a hot backup is increased use of rollback segments, redo logs, archive logs, and internal buffer areas within the SGA.

No No No! Stop making stuff up! Oracle’s tablespace hot backup does not work this way at all. It is actually a simple, elegant and failure-resistant mechanism. It absolutely does not stop writing to the datafiles. It actually allows continued operation of the database almost exactly as during normal operation. Contrary to the characterization as “complex” in SAMS Oracle Unleashed, it can be summarized in a few steps:
DBWn checkpoints the tablespace (writes out all dirty blocks as of a given SCN)
CKPT stops updating the Checkpoint SCN field in the datafile headers and begins updating the Hot Backup Checkpoint SCN field instead
LGWR begins logging full images of changed blocks the first time a block is changed after being written by DBWn
Those three actions are all that is required to guarantee consistency once the file is restored and recovery is applied. By freezing the Checkpoint SCN, any subsequent recovery on that backup copy of the file 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 apply recovery starting there.
Note that during hot backup mode, checkpoints to datafiles are not suppressed. Only the main Checkpoint SCN flag is frozen, but CKPT continues to update a Hot Backup Checkpoint SCN in the file header.
There is a confusing side effect of having the Checkpoint SCN frozen at an SCN earlier than the true checkpointed SCN of the database. In the event of a system crash or a shutdown abort during hot backup of a tablespace, the automatic crash recovery routine during startup will look at the file headers, think that the files for that tablespace are out of date, and will suggest that you need to apply old archived redologs in order to bring them back into sync with the rest of the database. Fortunately, no media recovery is necessary. With the database started up in mount mode:
SQL> alter database end backup;
This action will bring the Checkpoint SCN in the file headers in sync with the Hot Backup Checkpoint SCN (which is a true representation of the last SCN to which the datafile is checkpointed). Once you do this, normal crash recovery can proceed during ‘alter database open;’.
By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be present in the archivelogs in case they are ever used for a recovery. Most of the Oracle user community knows that Oracle generates a greater volume of redo during hot backup mode. This is the result of Oracle logging of full images of changed blocks in these tablespaces. Normally, Oracle writes a change vector to the redologs for every change, but it does not write the whole image of the database block. Full block image logging during backup eliminates the possibility that the backup will contain unresolvable split blocks. To understand this reasoning, you must first understand what a split block is.
Typically, Oracle database blocks are a multiple of O/S blocks. For example, most Unix filesystems have a default block size of 512 bytes, while Oracle’s default block size is 8k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 8k chunks or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, your process is reading in O/S-block-sized increments. If DBWn happens to be writing a DB block into the datafile at the same moment that your script is reading that block’s constituent O/S blocks, your copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block. By logging the full block image of the changed block to the redologs, Oracle guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the archivelogs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved by overlaying them with the block images from the archivelogs.
All of these mechanisms exist for the benefit of the backup copy of the files and any future recovery. They have very little effect on the current datafiles and the database being backed up. Throughout the backup, server processes read datafiles DBWn writes them, just as when a backup is not taking place. The only difference in the open database files is the frozen Checkpoint SCN, and the active Hot Backup Checkopint SCN. To demonstrate the principle, we can formulate a simple proof:
Create a table and insert a row:
SQL> create table fruit (kind varchar2(32)) tablespace users;
Table created.

SQL> insert into fruit values ('orange');
1 row created.

SQL> commit;
Commit complete.
Force a checkpoint, to flush dirty blocks to the datafiles.
SQL> alter system checkpoint;
System altered.
Get the file name and block number where the row resides:
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num,
dbms_rowid.rowid_block_number(rowid) block_num,
kind
from fruit;
FILE_NUM BLOCK_NUM KIND
-------- --------- ------
4 183 orange

SQL> select name from v$datafile where file# = 4;
NAME
-----------------------------
/u01/oradata/uw01/users01.dbf
Use the dd utility to skip to block 183 and extract the DB block containing the row:
unixhost% dd bs=8k skip=183 count=1 if=/u01/oradata/uw01/users01.dbf | strings
1+0 records in
16+0 records out
orange
Now we put the tablespace into hot backup mode:
SQL> alter tablespace users begin backup;
Tablespace altered.
Update the row, commit, and force a checkpoint on the database.
SQL> update fruit set kind = 'plum';
1 row updated

SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.
Extract the same block. It shows that the DB block has been written to disk during backup mode:
unixhost% dd bs=8k skip=183 count=1 if=/u01/oradata/uw01/users01.dbf | strings
1+0 records in
16+0 records out
plum
orange
Don’t forget to take the tablespace out of backup mode!
SQL> alter tablespace administrator end backup;
Tablespace altered.
It is quite clear from this demonstration that datafiles receive writes even during hot backup mode!

DBA Scripts

Hot Backup
Finding a desired value in a table
Finding out connected users