Pro Oracle Database 11g Administration (89 page)

Performing Change/SCN-Based Recovery

SCN-based incomplete database recovery works in situations where you know the SCN value up to where you want to end the restore and recovery session. RMAN will recover up to, but not including, the specified SCN. RMAN automatically terminates the restore process when it reaches the specified SCN.

You can view your database SCN information in several ways:

• By using LogMiner to determine an SCN associated with a DDL or DML

statement.

• By looking in the alert.log file.

• By looking in your trace files.

• By querying the FIRST_CHANGE# column of V$LOG, V$LOG_HISTORY, and

V$ARCHIVED_LOG.

After establishing the SCN to which you want to restore, use the UNTIL SCN clause to restore up to, but not including, the SCN specified. The following example restores all transactions that have an SCN

that is less than 950:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore database until scn 95019865425;

RMAN> recover database until scn 95019865425;

RMAN> alter database open resetlogs;

If everything went well, you should now see output similar to this:

Database altered

Restoring to a Restore Point

There are two types of restore points: normal and guaranteed. Guaranteed restore points require that you have the flashback database feature enabled. You can create a normal restore point using SQL*Plus as follows:

SQL> create restore point MY_RP;

This command creates a restore point named MY_RP that is associated with the SCN of the database at the time the command was issued. You can view the current SCN of your database as shown here: SQL> select current_scn from v$database;

You can view restore point information in the V$RESTORE_POINT view like so: SQL> select name, scn from v$restore_point;

533

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

The restore point acts like a synonym for the particular SCN. It allows you to restore and recover to an SCN without having to specify a number. RMAN will restore and recover up to, but not including, the SCN associated with the restore point.

This example restores and recovers to the MY_RP restore point:

RMAN> connect target /

RMAN> startup mount;

RMAN> restore database until restore point MY_RP;

RMAN> recover database until restore point MY_RP;

RMAN> alter database open resetlogs;

Restoring and Recovering to Different Server

When you think about backups, you must as the same time think about restore and recovery. Your backups are only as good as the last time you tested a restore and recovery. A backup strategy can be rendered worthless without a good restore and recovery strategy. The last thing you want to happen is to have a media failure, go to restore your database, and then find out you're missing critical pieces, don't have enough space to restore, learn that something is corrupt, and so on.

One of the best ways to test an RMAN backup is to restore and recover it to a different database server. This will exercise all of your backup, restore, and recovery DBA skills. If you can restore and recover an RMAN backup on a different server, it will give you confidence when a real disaster hits. You can think of all of the prior material in this book as the building blocks for performing technically challenging tasks. Moving a database from one server to another using an RMAN backup requires an expert level understanding of the Oracle architecture and how backup and recovery works.

■
Note
RMAN does have a DUPLICATE DATABASE command which works well for copying a database from one server to another. If you're going to be performing this type of task often, I would recommend that you use RMAN's duplicate database functionality. However, you may still have to manually copy a backup of a database from one server to another, especially when the security is such that you can't directly connect a production server to a development environment. I work with many production databases where there is no direct access to a production server, so the only way to duplicate a database is by manually copying the RMAN backups from production to a test environment.

In this example, the originating server and destination server have completely different mount points and disk layouts. Listed next are the high-level steps required to take an RMAN backup and use it to recreate a database on a separate server:

1. Create an RMAN backup on the originating database.

2. Copy RMAN backup to the destination server. All steps after this step are performed on the destination database server.

3. Ensure that Oracle is installed.

4. Source the required OS variables.

5. Create an init.ora file for the database to be restored.

534

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

6. Create any required directories for datafiles, control files, and dump/trace files.

7. Startup the database in NOMOUNT mode.

8. Restore a control file from the RMAN backup.

9. Startup the database in MOUNT mode.

10. Make the control file aware of the location of the RMAN backups.

11. Rename and restore the datafiles to reflect new directory locations.

12. Recover the database.

13. Set the new location for the online redo logs.

14. Open the database.

15. Add tempfile.

16. Rename the database.

Each of the prior steps is covered in detail in the next several sections. Steps 1 and 2 occur on the source database server. All other steps are performed on the destination server. For this example, the source database is named E64202, and the destination database will be named O11DEV.

On the source database, the location of the datafiles, control files, and online redo logs are all in this directory:

/u02/oracle/oradata/E64202

The source database archive redo log file location is here:

/u02/oracle/product/11.2.0/dbhome_1/dbs/arch

The RMAN backup location is determined by the following configurations: CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/rman/%F.bk'; CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/u02/rman/rman1_%U.bk'; On the destination database, the datafiles and control files will be renamed and restored to this directory:

/ora02/dbfile/O11DEV

The destination database online redo logs will be recreated in this directory:

/ora02/oraredo/O11DEV

The destination database archive redo log file location will be set as follows:

/ora02/oraarch/O11DEV

Step 1: Create an RMAN Backup on the Originating Database

When backing up a database, make sure you have the autobackup control file feature turned on. Also include the archive redo logs as part of the backup, like so:

RMAN> backup database plus archivelog;

535

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

I usually configure a channel and have both the backup pieces and the control file autobackup go to the same directory. For example, this is what the backup pieces look like for the source database: rman1_03lol0i0_1_1.bk

rman1_04lol0i1_1_1.bk

rman1_05lol0j5_1_1.bk

c-1984315547-20100923-00.bk

Step 2: Copy RMAN Backup to Destination Server

For this step, I usually use a utility such as rsync or scp to copy the backup pieces from one server to another. This example uses the scp command:

$ scp *.bk oracle@ora03:/ora02/rman/O11DEV

In this example, the /ora02/rman/O11DEV directory must be created on the destination server before copying the backup files.

■
Note
If the RMAN backups are on tape instead on disk, then the same media manager software must be installed/configured on the destination server. Also, that server must have direct access to the RMAN backups on tape.

Step 3: Ensure that Oracle is Installed

Make sure you have the same version of Oracle binaries installed on the destination as you do on the originating database. In this example, Oracle Database 11
g
release 2 is used for both the source and destination databases.

Step 4: Source the Required OS Variables

You need to establish the operating system variables such as ORACLE_SID, ORACLE_HOME, and PATH. I usually set the ORACLE_SID variable to match what it was on the original database. The database name will be changed as part of the last step in this section. Here are the settings for ORACLE_SID and ORACLE_HOME on the destination server:

$ echo $ORACLE_SID

E64202

$ echo $ORACLE_HOME

/oracle/app/oracle/product/11.2.0/db_1

Step 5: Create an init.ora File for the Database to be Restored

Copy the init.ora file from the original server to the destination server and modify it so that it matches the destination box in terms of any directory paths. For example, make sure you modify the 536

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

CONTROL_FILES parameter so that the path names are reflective of where the control files will be placed on the new server.

For now, the name of the init.ora file is ORACLE_HOME/dbs/initE64202.ora. This file will be renamed when the database is renamed to O11DEV in a later step. For now, the name of the database is E64202; this will be renamed in a later step.

Here are the contents of the init.ora file:

db_name='E64202'

control_files='/ora02/dbfile/O11DEV/control01.ctl'

,'/ora02/dbfile/O11DEV/control02.ctl'

diagnostic_dest='/oracle/app/oracle'

log_archive_dest_1='location=/ora02/oraarch/O11DEV'

log_archive_format='%t_%s_%r.arc'

db_block_size=8192

memory_target=408944640

open_cursors=300

processes=100

remote_login_passwordfile='EXCLUSIVE'

sessions=115

undo_tablespace='UNDOTBS1'

Notice that the CONTROL_FILES, DIAGNOSTIC_DEST, and LOG_ARCHIVE_DEST_1 reflect the new path directories on the destination server.

■
Note
If this was an Oracle Database 10g example, you would need to set the parameters of: BACKGROUND_DUMP_DEST, USER_DUMP_DEST, CORE_DUMP_DEST.

Step 6: Create any Required Directories for Datafiles, Control Files, and Dump/Trace Files

For this example, the directories of /ora02/dbfile/O11DEV, /oracle/app/oracle, and

/ora02/oraarch/O11DEV are created:

$ mkdir -p /ora02/dbfile/O11DEV

$ mkdir -p /oracle/app/oracle

$ mkdir -p /ora02/oraarch/O11DEV

Step 7: Startup the Database in NOMOUNT Mode

You should now be able to startup the database in NOMOUNT mode:

$ rman target /

RMAN> startup nomount;

537

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

Step 8: Restore a Control File from the RMAN Backup

Now restore the control file from the backup that was previously copied. In this example, the backup piece that contains the control file backup is c-1984315547-20100923-00.bk: RMAN> restore controlfile from '/ora02/rman/O11DEV/c-1984315547-20100923-00.bk'; The control file will be restored to all locations specified by the CONTROL_FILES initialization parameter. Here is some sample output:

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/ora02/dbfile/O11DEV/control01.ctl

output file name=/ora02/dbfile/O11DEV/control02.ctl

Step 9: Startup Database in Mount Mode

You should be able to startup your database in mount mode now:

RMAN> alter database mount;

Step 10: Make the Control File Aware of the Location of the RMAN

Backups

First, use the CROSSCHECK command to let the control file know that none of the backups or archive redo logs are in the same location that they were on the original server: RMAN> crosscheck backup;

RMAN> crosscheck copy;

RMAN> crosscheck archivelog all;

Now use the CATALOG command to make the control file aware of the location and names of the backup pieces that were copied to the destination server. In this example, any RMAN files that are in the

/ora02/rman/O11DEV directory will be cataloged in the control file:

RMAN> catalog start with '/ora02/rman/O11DEV';

Here is some sample output:

searching for all files that match the pattern /ora02/rman/O11DEV

List of Files Unknown to the Database

=====================================

File Name: /ora02/rman/O11DEV/c-1984315547-20100923-00.bk

File Name: /ora02/rman/O11DEV/rman1_04lol0i1_1_1.bk

File Name: /ora02/rman/O11DEV/rman1_03lol0i0_1_1.bk

Do you really want to catalog the above files (enter YES or NO)?

Now, type in YES (if everything looks okay). You should be able to use the RMAN LIST BACKUP

command now to view the newly cataloged backup pieces:

RMAN> list backup;

538

CHAPTER 19 ■ RMAN RESTORE AND RECOVERY

Step 11: Rename and restore the datafiles to Reflect New Directory

Locations

If your destination server has the exact same directory structure as the original server directories, you can issue the RESTORE command directly:

RMAN> restore database;

However, when restoring datafiles to locations are different from the original directories, you'll have to use the SET NEWNAME command. Create a file that uses an RMAN run{} block that contains the appropriate SET NEWNAME and RESTORE commands. I like to use a SQL script that generates SQL to give me a starting point. Here is a sample script:

set head off feed off verify off pages 0 trimspool on

set lines 132 pagesize 0

spo newname.sql

--

select 'run{' from dual;

--

select

'set newname for datafile ' || file# || ' to ' || '''' || name || '''' || ';'

from v$datafile;

--

select

'restore database;' || chr(10) ||

'switch datafile all;' || chr(10) ||