Pro Oracle Database 11g Administration (90 page)
'}'
from dual;
--
spo off;
After running the script, here are the contents of the newname.sql script that was generated: run{
set newname for datafile 1 to '/u02/oracle/oradata/E64202/system01.dbf'; set newname for datafile 2 to '/u02/oracle/oradata/E64202/sysaux01.dbf'; set newname for datafile 3 to '/u02/oracle/oradata/E64202/undotbs01.dbf'; set newname for datafile 4 to '/u02/oracle/oradata/E64202/users01.dbf'; restore database;
switch datafile all;
}
Now, modify the contents of the newname.sql script to reflect the directories on the destination database server. Here is what the final newname.sql script looks like for this example: run{
set newname for datafile 1 to '/ora02/dbfile/O11DEV/system01.dbf';
set newname for datafile 2 to '/ora02/dbfile/O11DEV/sysaux01.dbf';
set newname for datafile 3 to '/ora02/dbfile/O11DEV/undotbs01.dbf';
set newname for datafile 4 to '/ora02/dbfile/O11DEV/users01.dbf';
restore database;
switch datafile all;
}
Now, connect to RMAN and run the prior script to restore the datafiles to the new locations: 539
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
$ rman target /
RMAN> @newname.sql
Here is a snippet of the output for this example:
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 24-SEP-10
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=730527824 file name=/ora02/dbfile/O11DEV/system01.dbf datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=730527824 file name=/ora02/dbfile/O11DEV/sysaux01.dbf datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=730527824 file name=/ora02/dbfile/O11DEV/undotbs01.dbf datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=730527824 file name=/ora02/dbfile/O11DEV/users01.dbf RMAN> **end-of-file**
All of the datafiles have been restored to the new database server. You can use the RMAN REPORT
SCHEMA command to verify that the files have been restored and are in the correct locations: RMAN> report schema;
Here is some sample output:
RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name E64202
List of Permanent Datafiles
===========================
---- -------- -------------------- ------- ------------------------
1 670 SYSTEM *** /ora02/dbfile/O11DEV/system01.dbf 2 470 SYSAUX *** /ora02/dbfile/O11DEV/sysaux01.dbf 3 30 UNDOTBS1 *** /ora02/dbfile/O11DEV/undotbs01.dbf 4 5 USERS *** /ora02/dbfile/O11DEV/users01.dbf List of Temporary Files
=======================
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 /u02/oracle/oradata/E64202/temp01.dbf From the prior output, the database name and temporary tablespace datafile still don't reflect the destination database. Those will be modified in subsequent steps.
Step 12: Recover the Database
Next, you need to apply any archive redo files that were generated during the backup. These should be included in the backup because the ARCHIVELOG ALL clause was used to take the backup. Initiate the application of redo via the RECOVER DATABASE command:
RMAN> recover database;
RMAN will restore and apply as many archive redo logs as it has in the backup pieces and then will throw an error when it reaches an archive redo log which doesn't exist: channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log
540
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /ora02/rman/O11DEV/rman1_05lol0j5_1_1.bk channel ORA_DISK_1: piece handle=/ora02/rman/O11DEV/rman1_05lol0j5_1_1.bk tag=TAG20100923T200037
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/ora02/oraarch/O11DEV/1_4_729167134.arc thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/24/2010 04:35:09
RMAN-06054: media recovery requesting unknown archived log
for thread 1 with sequence 5 and starting SCN of 977214
Now is a good time to verify that your datafiles are online and not in a fuzzy state. Run the following query:
select
file#
,status
,fuzzy
,error
,checkpoint_change#,
to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss') as checkpoint_time
from v$datafile_header;
Step 13: Set the New Location for the Online Redo Logs
Set the names for the online redo logs to reflect the new directory structures on the destination database server. I sometimes use a SQL script that generates SQL to assist with this step: set head off lines 132 pages 0 trimspoo on
spo renlog.sql
select
'alter database rename file ' || chr(10)
|| '''' || member || '''' || ' to ' || chr(10) || '''' || member || '''' ||';'
from v$logfile;
spo off;
For this example, here are the contents of the renlog.sql file that was generated: alter database rename file
'/u02/oracle/oradata/E64202/redo03.log' to
'/u02/oracle/oradata/E64202/redo03.log';
alter database rename file
'/u02/oracle/oradata/E64202/redo02.log' to
'/u02/oracle/oradata/E64202/redo02.log';
alter database rename file
'/u02/oracle/oradata/E64202/redo01.log' to
'/u02/oracle/oradata/E64202/redo01.log';
541
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
The contents of renlog.sql need to be modified to reflect the directory structure on the destination server. Here is what renlog.sql looks like after being edited for this example: alter database rename file
'/u02/oracle/oradata/E64202/redo03.log' to
'/ora02/oraredo/O11DEV/redo03.log';
alter database rename file
'/u02/oracle/oradata/E64202/redo02.log' to
'/ora02/oraredo/O11DEV/redo02.log';
alter database rename file
'/u02/oracle/oradata/E64202/redo01.log' to
'/ora02/oraredo/O11DEV/redo01.log';
Update the control file by running the prior script:
SQL> @renlog.sql
You can select from V$LOGFILE to verify that the online redo log names are correct: SQL> select member from v$logfile;
Here is the output for this example:
/ora02/oraredo/O11DEV/redo03.log
/ora02/oraredo/O11DEV/redo02.log
/ora02/oraredo/O11DEV/redo01.log
Make sure the directories exist on the new server that will contain the online redo logs. For this example, here's the mkdir command:
$ mkdir -p /ora02/oraredo/O11DEV
Step 14: Open the Database
You must open the database with the OPEN RESETLOGS command (because there are no redo logs and they must be recreated at this point):
SQL> alter database open resetlogs;
If successful, you should see this message:
Database altered.
Step 15: Add tempfile
When you start your database, Oracle will automatically try to add any missing tempfiles to the database.
Oracle won't be able to do this if the directory structure on the destination server is different from the source server. In this scenario you will have to manually add any missing tempfile(s). To do this, first take offline the temporary tablespace tempfile. The file definition from the originating database is taken offline like so:
SQL> alter database tempfile '/u02/oracle/oradata/E64202/temp01.dbf' offline; 542
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
Next, add a temporary tablespace file to the TEMP tablespace that matches the directory structure of the destination database server:
SQL> alter tablespace temp add tempfile '/ora02/dbfile/O11DEV/temp01.dbf' size 100m; Step 16: Rename the Database
If you need to rename the database to reflect the name for a development or test database, create a trace file that contains the CREATE CONTROLFILE statement and use it to rename your database. These steps are covered in detail in Chapter 4. The basic steps involved are:
1. Generate a trace file.
SQL> oradebug setmypid
SQL> alter database backup controlfile to trace resetlogs;
SQL> oradebug tracefile_name
2. Modify the trace file to include the SET DATABASE.
CREATE CONTROLFILE SET DATABASE "O11DEV" RESETLOGS...
3. Create an init.ora file that matches the new name.
4. ORACLE_HOME/dbs/init
5. Modify the DB_NAME variable within the new init.ora file (in this example, it’s set to O11DEV).
6. Set ORACLE_SID to reflect the new SID name (in this example, it’s set to O11DEV), 7. SQL> startup nomount;
8. Run the trace file to recreate the control file (trace file from Step 2).
9. SQL> alter database open resetlogs;
If successful, you should have a database that is a copy of the original database. All of the datafiles, control files, archive redo logs, and online redo logs are in the new locations, and the database has a new name.
■
Tip
You can also use the NID utility to change the database name and DBID. For additional information, see My Oracle Support note 863800.1 for more details.
Summary
RMAN is an acronym short for Recovery Manager. It's worth noting that Oracle did not name this tool Backup Manager. The Oracle team recognized that while backups are important, the real value of a B&R
tool is its ability to restore and recover the database. Being able to managing the recovery process is the critical skill. When a database is damaged and needs to be restored, everybody looks to the DBA to perform a smooth and speedy recovery of the database. Oracle DBAs should use RMAN to protect, secure, and ensure the availability of the company's data assets.
543
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
Restore and recovery are analogous to the healing process when you break a bone. Restoring is similar to the process of setting the bone back to its original position. This is like restoring datafiles from a backup and placing them in their original directories. Recovering a datafile is similar to the healing process of a broken bone—returning the bone back to its state before it was broken. When you recover datafiles, you apply transactions (obtained from archive redo and online redo) to transform the restored datafiles back to the state they were in before the media failure occurred.
RMAN can be used for any type of restore and recovery scenario. Depending on the situation, RMAN can be used to restore the entire database, specific datafiles, control files, server parameter files, archive redo logs, or just specific data blocks. You can instruct RMAN to perform a complete recovery or incomplete.
The last section in this chapter details how to use RMAN to restore and recover a database to a remote server. I recommend that you periodically attempt to test this type of recover. This will fully exercise your backup and recovery strategy. You will gain much confidence and fully understand backup and recovery internals once you can successfully restore a database to a different server from the original.
544
CHAPTER 20 ■ ORACLE SECURE BACKUP
•
•
•
•
•
•
•
•
If you are a small shop with just one production database server, then Oracle Secure Backup Express may satisfy your business requirements, whereas the full fledged Oracle Secure Backup edition is more suitable for environments with multiple distributed hosts that require advanced data protection requirements.
■
Note
OSB is available on most Linux and Unix platforms as well as Windows. See My Oracle Support's certification matrix for a current list (https://support.oracle.com).
OSB Terminology
Take some time to familiarize yourself with the architectural terms used with OSB. This section gives a brief description of the major OSB parts, starting with an administrative domain.