Pro Oracle Database 11g Administration (73 page)
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
If you attempt to stop the recovery process before all redo between the begin and end markers has been applied to the datafile, Oracle throws this error:
ORA-01195: online backup of file 1 needs more recovery to be consistent All redo generated during the hot backup of a tablespace must be applied to the datafiles before they can be opened. Oracle at minimum needs to apply everything between the begin-backup SCN marker and the end-backup marker, to account for every block modified while the tablespace was in backup mode. This redo is in archive-redo log files; or, if the failure happened right after the backup ended, some of the redo may not have been archived and is in the online-redo logs. Therefore, you have to instruct Oracle to apply what’s in the online-redo logs.
Understanding that Datafiles
Are
Updated
Notice in Figure 16–2 and Figure 16–3 that the behavior of the database writer is for the most part unchanged throughout the backup procedure. The database writer continues to write blocks to datafiles regardless of the backup mode of the database. It doesn’t care if a hot backup is taking place; its job is to write blocks from the buffer cache to the datafiles.
Every once in while, you run into a DBA who states that the database writer doesn’t write to datafiles during user-managed hot backups. This is a widespread misconception. Use some common sense: if the database writer isn’t writing to the datafiles during a hot backup, then where are the changes being written? If the transactions are being written to somewhere other than the datafiles, how would those datafiles be resynchronized after the backup? It doesn’t make any sense.
Some DBAs say, “The datafile header is frozen, which means no changes to the datafile.” Oracle does freeze the SCN to indicate the start of the hot backup in the datafile header and doesn’t update that SCN until the tablespace is taken out of backup mode. This “frozen SCN” doesn’t mean blocks aren’t being written to datafiles during the backup. You can easily demonstrate that a datafile is written to during backup mode by doing this:
1. Put a tablespace in backup mode:
SQL> alter tablespace users begin backup;
2. Create a table that has a character field:
SQL> create table cc(cc varchar2(20)) tablespace users;
3. Insert a string into that table:
SQL> insert into cc values('DBWR does write');
4. Switch the online-redo log files to force a checkpoint:
SQL> alter system switch logfile;
5. From the OS, use the strings and grep commands to search for the string in the datafile:
$ strings /ora02/dbfile/O11R2/users01.dbf | grep "DBWR does write"
6. Here’s the output proving that the database writer did write the data to disk: DBWR does write
7. Don’t forget to take the tablespace out of backup mode:
SQL> alter tablespace users end backup;
440
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
Performing a Complete Recovery of an Archivelog-Mode
Database
Complete recovery
means you can recover all transactions that were committed before a failure occurred.
Complete recovery doesn’t mean you completely restore and recover the entire database. For example, if only one datafile has experienced media failure, you only need to restore and recover the damaged datafile to perform a complete recovery.
■
Tip
If you have access to a test or development database, take the time to walk through each step in each example in this section. Going through these steps can teach you more about B&R than any documentation.
The steps in this section apply to any database backed up while in archivelog mode. It doesn’t matter if you made a cold backup or hot backup. The steps to restore and recover datafiles are the same, as long as the database was in archivelog mode during the backup. For a complete recovery, you need
• To be able to restore the datafiles that have experienced media failure
• Access to all archive-redo logs generated since the last backup was started
• Have intact online-redo logs.
Here’s the basic procedure for a complete recovery:
1. Any datafiles being restored need to be offline before the restoration can take place; the easiest way to achieve this is to place your database in mount mode.
2. Restore the damaged datafiles with an OS copy utility.
3. Issue the appropriate SQL*Plus RECOVER command to apply any information required in the archive-redo logs and online-redo logs.
4. Alter the database open.
The next several sections demonstrate some common complete restore and recovery scenarios. You should be able to apply these basic scenarios to diagnose and recover from any complex situation you find yourself in.
Restoring and Recovering with the Database Offline
This section details a simple restore-and-recovery scenario. Described next are the steps to simulate a failure and then perform a complete restore and recovery. Try this scenario in a development database.
Ensure that you have a good backup and that you aren’t trying this experiment in a database that contains critical business data.
Before you start this example, create a table and insert some data. This table and data are selected from at the end of the complete recovery process to demonstrate a successful recovery: SQL> create table foo(foo number) tablespace users;
SQL> insert into foo values(1);
SQL> commit;
441
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
Now, switch the online logs several times. Doing so ensures that you have to apply archive-redo logs as part of the recovery:
SQL> alter system switch logfile;
The forward slash (/) reruns the most recently executed SQL statement: SQL> /
SQL> /
SQL> /
Next, simulate a media failure by renaming the datafile associated with the USERS tablespace. You can identify the name of the datafile associated with the USERS tablespace with this query: SQL> select file_name from dba_data_files where tablespace_name='USERS'; FILE_NAME
----------------------------------
/ora02/dbfile/O11R2/users01.dbf
From the OS, rename the file:
$ mv /ora02/dbfile/O11R2/users01.dbf /ora02/dbfile/O11R2/users01.dbf.old And attempt to stop your database:
$ sqlplus / as sysdba
SQL> shutdown immediate;
You should see an error similar to the following:
ORA-01110: data file 4: '/ora02/dbfile/O11R2/users01.dbf'
ORA-27041: unable to open file
If this were a real disaster, it would be prudent to navigate to the datafile directory, list the files, and see if the file in question was in its correct location. You should also inspect the alert.log file to see if any relevant information is logged there by Oracle.
Now that you’ve simulated a media failure, the next several steps walk you through a restore and complete recovery.
Step 1: Place Your Database in Mount Mode
Before you place your database in mount mode, you may need to first shut it down using ABORT: $ sqlplus / as sysdba
SQL> shutdown abort;
SQL> startup mount;
Step 2: Restore the Datafile from the Backup
The next step is to copy from the backup the datafile that corresponds to the one that has had a failure: $ cp /oradump/hbackup/O11R2/users01.dbf /ora02/dbfile/O11R2/users01.dbf At this point, it’s instructional to ponder what Oracle would do if you attempted to start your database. When you issue the ALTER DATABASE OPEN statement, Oracle inspects the SCN in the control file for each datafile. You can inspect this SCN by querying V$DATAFILE:
442
k
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
SQL> select checkpoint_change# from v$datafile where file#=4;
CHECKPOINT_CHANGE#
------------------
17788196
Oracle compares the SCN in the control file with the SCN in the datafile header. You can inspect the SCN in the datafile header by querying V$DATAFILE_HEADER. For example: SQL> select file#, checkpoint_change# from v$datafile_header where file#=4; FILE# CHECKPOINT_CHANGE#
---------- ------------------
4 17779631
Notice that the SCN recorded in V$DATAFILE_HEADER is less than the SCN in V$DATAFILE for the same datafile. If you attempt to open your database, Oracle throws an error stating that media recovery is required (meaning you need to apply redo) to synchronize the SCN in the datafile with the SCN in the control file. Here’s what happens when you attempt to open the database at this point: SQL> alter database open;
alter database open;
alter database open
*
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/ora02/dbfile/O11R2/users01.dbf'
Oracle doesn’t let you open the database until the SCN in all datafile headers matches the corresponding SCN in the control file.
Step 3: Issue the Appropriate RESTORE Statement
The archive-redo logs and online-redo logs have the information required to catch the datafile SCN up to the control-file SCN. You can apply redo to the datafile that needs media recovery by issuing one of the following SQL*Plus statements:
• RECOVER DATAFILE
• RECOVER TABLESPACE
• RECOVER DATABASE
Because only one datafile in this example needs to be recovered, the RECOVER DATAFILE statement is appropriate. However, keep in mind that you can run any of the previously listed RECOVER statements, and Oracle will figure out what needs to be recovered. In this particular scenario, you may find it easier to remember the name of the tablespace that contains the restored datafile(s) than to remember the datafile name(s). Next, any datafiles that need recovery in the USERS tablespace are recovered: SQL> recover tablespace users;
At this point, Oracle uses the SCN in the datafile header to determine which archive-redo log or online-redo log to use to begin applying redo. If all of the redo required is in the online-redo logs, Oracle applies that redo and displays this message:
443
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
Media recovery complete.
If Oracle needs to apply redo that is only contained in archived redo logs (meaning that the online-redo log that contained the appropriate redo has already been overwritten), you’re prompted with a recommendation from Oracle as to which archive-redo log to apply first: ORA-00279: change 17779631 generated at 08/21/2010 04:51:22 needed for thread 1
ORA-00289: suggestion : /ora02/oraarch/O11R2/1_33_726314508.arc
ORA-00280: change 17779631 for thread 1 is in sequence #33
Specify log: {
You can press Enter or Return (
In this example, specify AUTO. Oracle applies all redo in all archive-redo log files and online-redo log files to perform a complete recovery:
AUTO
The last message displayed after all required archive redo and online redo has been applied is this: Log applied.
Media recovery complete.
Step 4: Alter Your Database Open
After the media recovery is successful, you can open your database:
SQL> alter database open;
You can now verify that the transaction you committed just prior to the media failure was restored and recovered:
SQL> select * from foo;
FOO
----------
1
Restoring and Recovering with a Database Online
If you lose a datafile associated with a tablespace other than with SYSTEM and UNDO, you can restore and recover the damaged datafile while leaving the database online. For this to work, any datafiles being restored and recovered must be taken offline first. You may be alerted to an issue with one datafile when a user is attempting to update a table and sees an error such as this: SQL> insert into foo values(2);
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/ora02/dbfile/O11R2/users01.dbf'
ORA-27041: unable to open file
You navigate to the OS directory that contains the datafile, and determine that the datafile has been erroneously removed by a system administrator.
444
CHAPTER 16 ■ USER-MANAGED BACKUP AND RECOVERY
In this example, the datafile associated with the USERS tablespace is taken offline and subsequently restored and recovered while the rest of the database remains online. First, place take the datafile offline: SQL> alter database datafile '/ora02/dbfile/O11R2/users01.dbf' offline; Now, restore the appropriate datafile from the backup location:
$ cp /oradump/hbackup/O11R2/users01.dbf /ora02/dbfile/O11R2/users01.dbf In this situation, you can’t use RECOVER DATABASE. The RECOVER DATABASE statement attempts to recover all datafiles in the database, of which the SYSTEM tablespace is part. The SYSTEM tablespace can’t be recovered while the database is online. If you use the RECOVER TABLESPACE, all datafiles associated with the tablespace must be offline. In this case, it’s more appropriate to recover at the datafile level of granularity: