Checking the Flashback Status of a Database
Problem You want to check whether your database is flashback enabled.
Solution
The data dictionary view V$DATABASE contains information about the flashback status of the
database. Check the column FLASHBACK_ON on that view to ascertain the flashback
status:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
If the result of the query is different, as in the example shown here, then the database is
not running in archivelog mode:
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
4. To enable archivelog mode, follow these steps:
a. Shut down the database by issuing the following SQL statement:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
b. Start the database in mount mode by issuing the following SQL statement:
SQL> startup mount
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
c. Enable archivelog mode by issuing the following command:
SQL> alter database archivelog;
Database altered.
d. At this point, you can open the database for business, but since your objective is to
enable flashback, go to the next step.
5. Make sure the database is in mounted state by issuing the following SQL statement:
SQL> select OPEN_MODE from v$database;
OPEN_MODE
----------
MOUNTED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
The final line confirms that the database is now mounted.
7. Enable flashback for the database by issuing the following SQL statement:
SQL> alter database flashback on;
Database altered.
8. Open the database:
SQL> alter database open;
Database altered.
Disabling Flashback on a Database
SQL> alter database flashback off;
Database altered
Solution 1: Flashing Back to a Specific SCN
In this example, you will see how to flash back a database to a specific SCN, which is the most precise flashback procedure possible. Here are the steps to follow:
1. First, check the SCN of the database now. Connecting as sys or any other DBA account,issue the following SQL statement:
SQL> select current_scn
2 from v$database;
CURRENT_SCN
-----------
1137633
The output shows the current SCN is 1,137,633. You can flash back to an SCN prior to
this number only.
2. Execute the “common presteps” 1 through 4.
3. Flash the database back to your desired SCN. For instance, to flash back to SCN
1,050,951, issue the following RMAN command:
RMAN> flashback database to scn 1050951;
Solution 2: Flashing Back to a Specific Time
You want to flash the database to a specific time, not an SCN. Here are the steps to follow:
1. Follow common steps 1 through 4.
2. Use the following command to flash back to a time just two minutes ago:
RMAN> flashback database to time 'sysdate-2/60/24';
RMAN> flashback database to time "to_date('01/23/07 13:00:00','mm/dd/yyhh24:mi:ss')";
Solution 3: Flashing Back to a Restore Point
In this solution, you will learn how to flash back the database to a restore point. You can learn about creating restore points in recipe 13-9 and recipe 13-10. Here are the steps to follow to flash back to a restore point:
1. Follow “common presteps” 1 through 4.
2. To flash back to a restore point named rp6, issue the following SQL:
RMAN> flashback database to restore point rp6;
Flashing Back to Before the Last resetlogs Operation
RMAN> flashback database to before resetlogs;
Solution 1: Flashing Back to a Time
You have a specific time—such as January 21, 2007, at 10 p.m.—that you want to flash back to. This time must be in the past. Here are the steps to follow:
1. Perform the “common presteps” 1 through 3.
2. Flash the database to your desired time stamp by issuing the following SQL statement:
SQL> flashback database to timestamp
2> to_date('1/22/2007 00:00:00','mm/dd/yyyy hh24:mi:ss');
Finding Out How Far Back into the Past You Can Flash Back
SQL> select * from v$flashback_database_log;
Estimating the Amount of Flashback Logs Generated at Various Times
Problem You want to find out how much space the flashback logs are expected to consume in the database at various points of time.
Solution
The solution is rather simple. The Oracle database already has a view that shows the estimated database changes and flashback changes in a one-hour period. This view is
V$FLASHBACK_DATABASE_STAT. Here is a sample of how to use the view to identify how
much flashback and database changes are generated in hour-long intervals:
SQL> alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss';
Session altered.
SQL> select * from v$flashback_database_stat
2 order by begin_time
3 /
BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA➥
ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ----------➥
------------------------
01/25/07 21:53:08 01/25/07 22:59:40 27860992 33284096 21613056➥
194224128
01/25/07 22:59:40 01/25/07 23:16:56 2138112 2285568 749056➥
0
... and so on ...
The data of interest is the column ESTIMATED_FLASHBACK_SIZE, which shows the expected
flashback log generated in the time period shown by the columns BEGIN_TIME and END_TIME.
Using this view, you can see an hour-by-hour progress of the flashback data generation. Issue
the following query to find out the estimated total size of the flashback logs at the end of each
period:
SQL> select end_time, estimated_flashback_size
2 from v$flashback_database_stat
3 order by 1
4 /
Here is the output:
END_TIME ESTIMATED_FLASHBACK_SIZE
----------------- ------------------------
01/25/07 19:58:00 73786720
01/25/07 20:53:10 164890123
01/25/07 21:57:37 287563456
01/25/07 22:59:40 194224128
Creating Guaranteed Restore Points
Problem
You want to create guaranteed restore points to ensure that you can flash back to them as needed. You want to require the database to retain any needed logs to support those points.
Solution
Add the guarantee keyword to your create restore point command. For example:
SQL> create restore point rp2 guarantee flashback database;
Restore point created.
Listing Restore Points
Problem
You want to list the various restore points in the database and the information about them. Solution Query the view V$RESTORE_POINT. For example:
SQL> col time format a32
SQL> col name format a10
SQL> select * from v$restore_point
2 order by 2,1;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
---------- --------------------- --- ------------ ------------------------- ----
1047095 2 NO 0 22-JAN-07 01.57.14.00 PM RP1
1049764 2 YES 4096000 22-JAN-07 03.40.55.00 PM RP2
1051267 2 YES 0 22-JAN-07 04.32.55.00 PM RP3
1051276 2 NO 0 22-JAN-07 04.33.05.00 PM RP4
1047289 3 NO 0 22-JAN-07 05.13.55.00 PM RP5
1047301 3 YES 3981312 22-JAN-07 05.14.17.00 PM RP6
Dropping Restore Points
Problem
You want to drop a specific restore point.
Solution
To drop a restore point named rp2, whether normal or guaranteed, simply execute the following
SQL statement:
SQL> drop restore point rp2;
Recovering a Dropped Table
SQL> show recyclebin
SQL> flashback table accounts to before drop;
Tip If you want to delete a table permanently, without sending it to the recycle bin, then use the purge clause in the drop statement. For example:
SQL> drop table accounts purge;
Table dropped.
The table is now completely dropped; similar to the pre-10g behavior, it does not go to the recycle bin.
Renaming the Reinstated Table
The alternative approach is safer because you do not need to drop anything. When you flash back a table to undrop it, you can optionally rename it. In this case, when you flash back the table ACCOUNTS, you want to reinstate it as NEW_ACCOUNTS.
SQL> flashback table accounts to before drop rename to new_accounts;
Solution
To reinstate a specific dropped table, follow the steps:
1. First find out the presence of these objects in the recycle bin:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJEC DROP TIME
------------- ------------------------------ ------ -------------------
ACCOUNTS BIN$VKGbC+r/Qjqg3avhlpBpqw==$0 TABLE 2007-01-23:00:46:50
ACCOUNTS BIN$bQ8QU1bWSD2Rc9uHevUkTw==$0 TABLE 2007-01-23:00:23:39
SQL> flashback table "BIN$bQ8QU1bWSD2Rc9uHevUkTw==$0" to before drop;
SQL> select * from user_recyclebin;
SQL> select * from dba_recyclebin;
Problem
You want to recover all the subordinate objects such as the indexes, constraints, and so on, of a table that has been undropped
SQL> select original_name, object_name, type, can_undrop
2 from user_recyclebin;
SQL> select index_name
2 from user_indexes
3 where table_name = 'ACCOUNTS';
INDEX_NAME
------------------------------
BIN$9POlL6gfQK6RBoOK4klc3Q==$0
BIN$PookVi5nRpmhmPaVOThGQQ==$0
BIN$fzY77+GmTzqz/3u4dqac9g==$0
6. Note the names, and compare them to the names you got in step 1. It’s not easy, but
you can make a clear connection. Using the output from step 1, rename the indexes:
SQL> alter index "BIN$9POlL6gfQK6RBoOK4klc3Q==$0" rename to IN_ACC_01;
Index altered.
SQL> alter index "BIN$PookVi5nRpmhmPaVOThGQQ==$0" rename to SYS_C005457;
Index altered.
SQL> alter index "BIN$fzY77+GmTzqz/3u4dqac9g==$0" rename to in_acc_02;
ORIGINAL_NAME OBJECT_NAME TYPE CAN
--------------- ------------------------------ ------- ---
IN_ACC_03 BIN$mc3jkDZRR42mswWBR0XPyA==$0 INDEX NO
IN_ACC_02 BIN$B8BgSIvWTweFyZv/y57GHg==$0 INDEX NO
IN_ACC_01 BIN$t8poR1f4SIKTOTpH2vYKSQ==$0 INDEX NO
TR_ACC_01 BIN$dt6tBSIWSn+F5epvjybKmw==$0 TRIGGER NO
ACCOUNTS BIN$laonDjIDS6macycHgAjP1Q==$0 TABLE YES
SQL> select trigger_name
2 from user_triggers;
TRIGGER_NAME
------------------------------
BIN$dt6tBSIWSn+F5epvjybKmw==$0
9. Rename triggers to their original names:
SQL> alter trigger "BIN$dt6tBSIWSn+F5epvjybKmw==$0" rename to tr_acc_01;
Trigger altered.
SQL> alter session set recyclebin = off;
Session altered.
After setting recyclebin to off, if you drop a table, the table is completely dropped:
SQL> drop table accounts;
Table dropped.
Now, if you check the recycle bin:
SQL> show recyclebin
the command returns no output, indicating that the recycle bin is empty.
You can turn off the recycle bin for the entire database by executing this:
SQL> alter system set recyclebin = off;
System altered.
Clearing the Recycle Bin
Problem
You want to remove all dropped objects from the recycle bin.
Solution
You can clean up the recycle bin using the purge statement, which clears the recycle bin of the
currently logged on user. For example:
SQL> purge recyclebin;
Recyclebin purged.
SQL> begin
2 purge recyclebin;
3 end;
4 /
purge recyclebin;
Flashing Back a Specific Table
SQL> select row_movement
2 from user_tables
3 where table_name = 'ACCOUNTS';
ROW_MOVE
--------
ENABLED
SQL> alter table accounts enable row movement;
Table altered.
This prepares the table for flashback.
SQL> flashback table accounts to timestamp to_date ('23-JAN-07 18.23.00','dd-MON-YY hh24.mi.ss');
viernes, 25 de septiembre de 2009
RMAN Performing Incomplete
Incomplete database recovery consists of two steps: restore and recovery. The restore step will
re-create datafiles, and the recover step will apply redo up to the specified point in time. The
restore process can be initiated from RMAN in several ways:
• restore database until
• restore tablespace until
• flashback database
recover database using backup controlfile;
restore database command instructs RMAN to retrieve datafiles from a point in the
past based on one of the following methods:
• Time
• Change (sometimes called system change number [SCN])
• Log sequence number
• Restore point (Oracle Database 10g Release 2 and newer versions)
RMAN will determine how to extract the datafiles from
any of the following:
• Full database backup
• Incremental level 0 backup
• Image copy backup generated by the backup as copy command
can view the datafile header SCNs and the status of each datafile via this SQL query:
SQL> select file#, status, checkpoint_change#,
2 to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss')
3 from v$datafile_header;
Performing Time-Based Recovery
Problem
You want to restore your database to a previous date and time.
Solution
You can restore your database to a previous time in one of two ways:
• Specify the time as part of the restore and recover commands.
• Use the set until time command, and then issue unqualified restore and recover
commands.
The following example specifies a time when issuing the restore and recover commands:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
2> "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;
You can also specify the time by using the set until time command. This command and
the subsequent restore and recover must be executed from within a run{} block:
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
RMAN> set until time "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> restore database;
RMAN> recover database;
RMAN> }
RMAN> alter database open resetlogs;
Performing Log Sequence–Based Recovery
Problem
You want to use RMAN to restore up to, but not including, a certain archived redo log file.
Solution
RMAN allows you to apply redo up to (but not including) a specific archived redo log file by specifying its sequence number when restoring and recovering. You can do this in one of two ways:
• Specify until sequence as part of the restore and recover commands.
• Use the set until sequence command.
The following example restores and recovers the target database up to, but not including, log sequence number 50:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until sequence 50;
RMAN> recover database until sequence 50;
RMAN> alter database open resetlogs;
You can also use the set until command from within a run{} block to perform a log
sequence–based recovery. The following examples restores and recovers up to but not including log sequence number 125 of thread 1:
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set until sequence 125 thread 1;
3> restore database;
4> recover database;
5> }
RMAN> alter database open resetlogs;
You can query sequence number information from V$LOG_HISTORY, as shown here:
SQL> select sequence#, first_change#, first_time
2 from v$log_history
3 order by first_time;
And here’s the corresponding query for V$ARCHIVED_LOG:
SQL> select sequence#, first_change#, first_time
2 from v$archived_log
3 order by first_time;
select sequence#, first_change#, first_time
from v$log_history
where first_time > '23/09/2009'
order by first_time
/
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set until scn 950;
3> restore database;
4> recover database;
5> }
RMAN> alter database open resetlogs;
Performing Cancel-Based Recovery
Problem
You desire to perform a cancel-based incomplete database recovery first using RMAN to
restore the datafiles and then using SQL*Plus to recover the datafiles. A cancel-based recovery is one that proceeds until you manually stop it.
Solution
The following example restores from the latest RMAN backup in preparation for a SQL*Plus cancel-based recovery:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database; # restore database from last backup
Once your database is restored, you can start a SQL*Plus session and initiate a cancelbased recovery, as shown here:
SQL> connect / as sysdba
SQL> recover database until cancel;
SQL> alter database open resetlogs;
Using LogMiner to Find an SCN
Problem
A user accidentally dropped a table. You want to find the SCN associated with that drop statement so that you can restore the database to the SCN just prior to the accidental drop.
Solution
Here are the steps for instructing LogMiner to analyze a specific set of archived redo log files for an SCN associated with a SQL statement:
1. Specify a set of archived redo log files for LogMiner to analyze.
2. Start LogMiner, and specify a data dictionary.
3. Perform analysis.
4. Stop the LogMiner session.
First you need to tell LogMiner which online redo log files or archived redo log files you want to analyze. In this scenario, we know the SQL statement that we’re looking for is in the
archived redo log file with a sequence number of 7.
SQL> connect sys/foo as sysdba
SQL> exec dbms_logmnr.add_logfile(-
logfilename=>'/ora01/BRDSTN/arc00007_0605867201.001', -
options=>dbms_logmnr.addfile);
If you want to mine multiple online redo log files, you can add more using
DBMS_LOGMNR.ADD_LOGFILE as follows:
SQL> exec dbms_logmnr.add_logfile(-
logfilename=>'/ora01/BRDSTN/arc00008_0605867201.001', -
options=>dbms_logmnr.addfile);
■Note You can view which log files will be analyzed by the current LogMiner session by querying the V$LOGMNR_LOGS view.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> select operation, scn
2 from v$logmnr_contents
3 where table_name='PAYROLL'
4 and operation='DROP';
OPERATION SCN
--------- -------
DROP 1047474
SQL> exec dbms_logmnr.end_logmnr();
How It Works
You can use LogMiner to find SCNs associated with DML and DDL statements. LogMiner
requires supplemental logging to be enabled to be able to display information about
DML
statements. Enable supplemental logging by issuing the following SQL:
SQL> connect sys/foo as sysdba
SQL> alter database add supplemental log data;
Performing Change/SCN-Based Recovery
Problem
You want to perform an incomplete database recovery to a particular database SCN.
Solution
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 950;
RMAN> recover database until scn 950;
RMAN> alter database open resetlogs;
Recovering to a Restore Point
ProblemYou want to restore and recover to a restore point.
Solution
Before you can restore to a restore point, you must have previously created a restore point via the create restore point command. Once you’ve done this, you can use the until restore point clause of the restore command.
CREATING A NORMAL RESTORE POINT
There are two types of restore points: normal and guaranteed. Guaranteed restore points require that you have the flashback database feature enabled. See Chapter 13 for more details on how to use flashback database
with guaranteed restore points.
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 also view the SCN assigned to your restore point(s):
SQL> select name, scn from v$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;
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
RMAN> set until restore point MY_RP;
RMAN> restore database;
RMAN> recover database;
RMAN> }
RMAN> alter database open resetlogs;
Restoring a Noarchivelog Mode Database
Problem
You used RMAN to back up a database in noarchivelog mode. You now need to restore this database from an RMAN backup.
Solution
When you restore a noarchivelog database, you can choose to use a backup control file or the current control file. You can run the following query to verify the type of control file you used
to mount your database:
SQL> select open_mode, controlfile_type from v$database;
OPEN_MODE CONTROL
---------- -------
MOUNTED CURRENT
Using Backup Control File
Our recommended approach is to first restore the control file that was backed up at the same time your noarchivelog mode database was backed up. This way the control file has an SCN that is consistent with the datafile SCNs. After you restore the control file, you can then restore the datafiles and open your database with the open resetlogs command. For example:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> alter database open resetlogs;
Using Current Control File
If you don’t restore your control file, you will have to perform a few extra steps. This example does not restore the control file and uses SQL*Plus to cancel out of the recovery session:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
Recovering to a Previous Incarnation
ENABLING AUTOBACKUP OF CONTROL FILE
Enabling the autobackup of the control file is fairly straightforward. To enable this feature, use the configure command as follows:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database mount;
RMAN> list incarnation of database;
RMAN> reset database to incarnation 1;
RMAN> restore database until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open reset logs;
RMAN> configure controlfile autobackup on;
Performing Tablespace Point-in-Time Recovery
Problem
A rogue developer thought they were in a test environment and issued commands to delete data from several tables in one tablespace. It turns out they were in the production environment.
You want to use tablespace point-in-time recovery (TSPITR) to restore your tablespace
to the point in time just before the erroneous DML was issued.
Solution
This recipe shows how to perform fully automated RMAN tablespace point-in-time recovery. Here are the steps:
1. Determine and resolve any dependencies to objects in tablespaces not included in the
TSPITR.
2. Determine whether there are objects that will not be recovered.
3. Create a destination on disk to temporarily hold the auxiliary database.
4. Run the recover tablespace until command.
5. Back up the restored tablespace and alter it online.
Step 1: Determine and Resolve Dependencies
SQL> SELECT *
2 FROM sys.ts_pitr_check
3 WHERE(ts1_name = 'USERS' AND ts2_name != 'USERS')
4 OR (ts1_name != 'USERS' AND ts2_name = 'USERS');
Step 2: Determine Which Objects Will Not Be Recovered
You can query the TS_PITR_OBJECTS_TO_BE_DROPPED view to help identify objects
that need to be preserved. This query identifies objects created after the time to which the TSPITR will be performed:
SQL> SELECT owner, name, tablespace_name
2 FROM ts_pitr_objects_to_be_dropped
3 WHERE tablespace_name ='USERS'
4 AND creation_time > to_date('12-nov-2006 16:00:00','dd-mon-rrrr hh24:mi:ss');
Step 3: Create an Auxiliary Destination
First ensure that you have an area on disk that will serve as a temporary container for an auxiliary database. This area will need enough space for a system, undo, and temporary tablespace.
We recommend you have at least 1GB of space in your auxiliary destination.
C:\> mkdir c:\auxx
Step 4: Run the recover Command
You can now perform a fully automated TSPITR. You can restore until a time, SCN, or
sequence. Notice that your database is open during the TSPITR. In this example, we restore the users tablespace up to, but not including, the time specified:
Caution Make sure you use the recover command (and not restore)! You can recover to an SCN, log
sequence number, or time.
RMAN> connect target /
RMAN> recover tablespace users until time
2> "to_date('12-nov-2006 16:00:00','dd-mon-rrrr hh24:mi:ss')"
3> auxiliary destination 'c:\auxx';
Step 5: Back Up the Tablespace and Alter It Online
Once the TSPITR completes, you must back up the recovered tablespace and bring it online:
RMAN> backup tablespace users;
RMAN> sql 'alter tablespace users online';
For example, say you have two tablespaces, p_dt and p_idx, and all of the objects in those two tablespaces are owned by prod_own. If there were undesirable DML statements that were issued against tables owned by prod_own, then you could use TSPITR to restore and recover to just prior to when the bad SQL was run. In this example, we restore the two tablespaces back to just before SCN 1432:
C:\> mkdir c:\auxx
RMAN> connect target /
RMAN> recover tablespace p_dt, p_idx until SCN 1432 auxiliary destination 'c:\auxx';
You should now see quite a number of RMAN messages displaying the status of each
operation. Once complete, back up the recovered tablespaces and bring them online:
RMAN> backup tablespace p_dt, p_idx;
RMAN> sql 'alter tablespace p_dt online';
RMAN> sql 'alter tablespace p_idx online';
Recovering a Subset of Datafiles
RMAN> connect target /
RMAN> startup mount;
Use the RMAN report schema command to identify which datafiles you do not want to
restore and recover. You can also query V$DATAFILE for the datafile details. In this
example,
the datafiles 7, 8, and 9 are taken offline and are not restored and recovered:
RMAN> sql 'alter database datafile 7, 8, 9 offline for drop';
RMAN> restore database until SCN 314159;
RMAN> recover database until SCN 314159;
RMAN> alter database open resetlogs;
Troubleshooting Incomplete Recovery
Problem
You’re attempting to perform an incomplete recovery, and RMAN is returning the following error:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
You wonder how to go about determining what is wrong.
Solution
In many situations, problems with incomplete recovery are caused by omitting one of the required steps. Here is the correct sequence of steps for most incomplete recovery scenarios:
1. restore database until;
2. recover database until;
3. alter database open resetlogs;
re-create datafiles, and the recover step will apply redo up to the specified point in time. The
restore process can be initiated from RMAN in several ways:
• restore database until
• restore tablespace until
• flashback database
recover database using backup controlfile;
restore database command instructs RMAN to retrieve datafiles from a point in the
past based on one of the following methods:
• Time
• Change (sometimes called system change number [SCN])
• Log sequence number
• Restore point (Oracle Database 10g Release 2 and newer versions)
RMAN will determine how to extract the datafiles from
any of the following:
• Full database backup
• Incremental level 0 backup
• Image copy backup generated by the backup as copy command
can view the datafile header SCNs and the status of each datafile via this SQL query:
SQL> select file#, status, checkpoint_change#,
2 to_char(checkpoint_time,'dd-mon-rrrr hh24:mi:ss')
3 from v$datafile_header;
Performing Time-Based Recovery
Problem
You want to restore your database to a previous date and time.
Solution
You can restore your database to a previous time in one of two ways:
• Specify the time as part of the restore and recover commands.
• Use the set until time command, and then issue unqualified restore and recover
commands.
The following example specifies a time when issuing the restore and recover commands:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
2> "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open resetlogs;
You can also specify the time by using the set until time command. This command and
the subsequent restore and recover must be executed from within a run{} block:
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
RMAN> set until time "to_date('05-oct-2006 14:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> restore database;
RMAN> recover database;
RMAN> }
RMAN> alter database open resetlogs;
Performing Log Sequence–Based Recovery
Problem
You want to use RMAN to restore up to, but not including, a certain archived redo log file.
Solution
RMAN allows you to apply redo up to (but not including) a specific archived redo log file by specifying its sequence number when restoring and recovering. You can do this in one of two ways:
• Specify until sequence as part of the restore and recover commands.
• Use the set until sequence command.
The following example restores and recovers the target database up to, but not including, log sequence number 50:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database until sequence 50;
RMAN> recover database until sequence 50;
RMAN> alter database open resetlogs;
You can also use the set until command from within a run{} block to perform a log
sequence–based recovery. The following examples restores and recovers up to but not including log sequence number 125 of thread 1:
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set until sequence 125 thread 1;
3> restore database;
4> recover database;
5> }
RMAN> alter database open resetlogs;
You can query sequence number information from V$LOG_HISTORY, as shown here:
SQL> select sequence#, first_change#, first_time
2 from v$log_history
3 order by first_time;
And here’s the corresponding query for V$ARCHIVED_LOG:
SQL> select sequence#, first_change#, first_time
2 from v$archived_log
3 order by first_time;
select sequence#, first_change#, first_time
from v$log_history
where first_time > '23/09/2009'
order by first_time
/
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set until scn 950;
3> restore database;
4> recover database;
5> }
RMAN> alter database open resetlogs;
Performing Cancel-Based Recovery
Problem
You desire to perform a cancel-based incomplete database recovery first using RMAN to
restore the datafiles and then using SQL*Plus to recover the datafiles. A cancel-based recovery is one that proceeds until you manually stop it.
Solution
The following example restores from the latest RMAN backup in preparation for a SQL*Plus cancel-based recovery:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database; # restore database from last backup
Once your database is restored, you can start a SQL*Plus session and initiate a cancelbased recovery, as shown here:
SQL> connect / as sysdba
SQL> recover database until cancel;
SQL> alter database open resetlogs;
Using LogMiner to Find an SCN
Problem
A user accidentally dropped a table. You want to find the SCN associated with that drop statement so that you can restore the database to the SCN just prior to the accidental drop.
Solution
Here are the steps for instructing LogMiner to analyze a specific set of archived redo log files for an SCN associated with a SQL statement:
1. Specify a set of archived redo log files for LogMiner to analyze.
2. Start LogMiner, and specify a data dictionary.
3. Perform analysis.
4. Stop the LogMiner session.
First you need to tell LogMiner which online redo log files or archived redo log files you want to analyze. In this scenario, we know the SQL statement that we’re looking for is in the
archived redo log file with a sequence number of 7.
SQL> connect sys/foo as sysdba
SQL> exec dbms_logmnr.add_logfile(-
logfilename=>'/ora01/BRDSTN/arc00007_0605867201.001', -
options=>dbms_logmnr.addfile);
If you want to mine multiple online redo log files, you can add more using
DBMS_LOGMNR.ADD_LOGFILE as follows:
SQL> exec dbms_logmnr.add_logfile(-
logfilename=>'/ora01/BRDSTN/arc00008_0605867201.001', -
options=>dbms_logmnr.addfile);
■Note You can view which log files will be analyzed by the current LogMiner session by querying the V$LOGMNR_LOGS view.
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> select operation, scn
2 from v$logmnr_contents
3 where table_name='PAYROLL'
4 and operation='DROP';
OPERATION SCN
--------- -------
DROP 1047474
SQL> exec dbms_logmnr.end_logmnr();
How It Works
You can use LogMiner to find SCNs associated with DML and DDL statements. LogMiner
requires supplemental logging to be enabled to be able to display information about
DML
statements. Enable supplemental logging by issuing the following SQL:
SQL> connect sys/foo as sysdba
SQL> alter database add supplemental log data;
Performing Change/SCN-Based Recovery
Problem
You want to perform an incomplete database recovery to a particular database SCN.
Solution
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 950;
RMAN> recover database until scn 950;
RMAN> alter database open resetlogs;
Recovering to a Restore Point
ProblemYou want to restore and recover to a restore point.
Solution
Before you can restore to a restore point, you must have previously created a restore point via the create restore point command. Once you’ve done this, you can use the until restore point clause of the restore command.
CREATING A NORMAL RESTORE POINT
There are two types of restore points: normal and guaranteed. Guaranteed restore points require that you have the flashback database feature enabled. See Chapter 13 for more details on how to use flashback database
with guaranteed restore points.
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 also view the SCN assigned to your restore point(s):
SQL> select name, scn from v$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;
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
RMAN> set until restore point MY_RP;
RMAN> restore database;
RMAN> recover database;
RMAN> }
RMAN> alter database open resetlogs;
Restoring a Noarchivelog Mode Database
Problem
You used RMAN to back up a database in noarchivelog mode. You now need to restore this database from an RMAN backup.
Solution
When you restore a noarchivelog database, you can choose to use a backup control file or the current control file. You can run the following query to verify the type of control file you used
to mount your database:
SQL> select open_mode, controlfile_type from v$database;
OPEN_MODE CONTROL
---------- -------
MOUNTED CURRENT
Using Backup Control File
Our recommended approach is to first restore the control file that was backed up at the same time your noarchivelog mode database was backed up. This way the control file has an SCN that is consistent with the datafile SCNs. After you restore the control file, you can then restore the datafiles and open your database with the open resetlogs command. For example:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> alter database open resetlogs;
Using Current Control File
If you don’t restore your control file, you will have to perform a few extra steps. This example does not restore the control file and uses SQL*Plus to cancel out of the recovery session:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
Recovering to a Previous Incarnation
ENABLING AUTOBACKUP OF CONTROL FILE
Enabling the autobackup of the control file is fairly straightforward. To enable this feature, use the configure command as follows:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database mount;
RMAN> list incarnation of database;
RMAN> reset database to incarnation 1;
RMAN> restore database until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database until time
2> "to_date('03-sep-2006 00:00:00', 'dd-mon-rrrr hh24:mi:ss')";
RMAN> alter database open reset logs;
RMAN> configure controlfile autobackup on;
Performing Tablespace Point-in-Time Recovery
Problem
A rogue developer thought they were in a test environment and issued commands to delete data from several tables in one tablespace. It turns out they were in the production environment.
You want to use tablespace point-in-time recovery (TSPITR) to restore your tablespace
to the point in time just before the erroneous DML was issued.
Solution
This recipe shows how to perform fully automated RMAN tablespace point-in-time recovery. Here are the steps:
1. Determine and resolve any dependencies to objects in tablespaces not included in the
TSPITR.
2. Determine whether there are objects that will not be recovered.
3. Create a destination on disk to temporarily hold the auxiliary database.
4. Run the recover tablespace until command.
5. Back up the restored tablespace and alter it online.
Step 1: Determine and Resolve Dependencies
SQL> SELECT *
2 FROM sys.ts_pitr_check
3 WHERE(ts1_name = 'USERS' AND ts2_name != 'USERS')
4 OR (ts1_name != 'USERS' AND ts2_name = 'USERS');
Step 2: Determine Which Objects Will Not Be Recovered
You can query the TS_PITR_OBJECTS_TO_BE_DROPPED view to help identify objects
that need to be preserved. This query identifies objects created after the time to which the TSPITR will be performed:
SQL> SELECT owner, name, tablespace_name
2 FROM ts_pitr_objects_to_be_dropped
3 WHERE tablespace_name ='USERS'
4 AND creation_time > to_date('12-nov-2006 16:00:00','dd-mon-rrrr hh24:mi:ss');
Step 3: Create an Auxiliary Destination
First ensure that you have an area on disk that will serve as a temporary container for an auxiliary database. This area will need enough space for a system, undo, and temporary tablespace.
We recommend you have at least 1GB of space in your auxiliary destination.
C:\> mkdir c:\auxx
Step 4: Run the recover Command
You can now perform a fully automated TSPITR. You can restore until a time, SCN, or
sequence. Notice that your database is open during the TSPITR. In this example, we restore the users tablespace up to, but not including, the time specified:
Caution Make sure you use the recover command (and not restore)! You can recover to an SCN, log
sequence number, or time.
RMAN> connect target /
RMAN> recover tablespace users until time
2> "to_date('12-nov-2006 16:00:00','dd-mon-rrrr hh24:mi:ss')"
3> auxiliary destination 'c:\auxx';
Step 5: Back Up the Tablespace and Alter It Online
Once the TSPITR completes, you must back up the recovered tablespace and bring it online:
RMAN> backup tablespace users;
RMAN> sql 'alter tablespace users online';
For example, say you have two tablespaces, p_dt and p_idx, and all of the objects in those two tablespaces are owned by prod_own. If there were undesirable DML statements that were issued against tables owned by prod_own, then you could use TSPITR to restore and recover to just prior to when the bad SQL was run. In this example, we restore the two tablespaces back to just before SCN 1432:
C:\> mkdir c:\auxx
RMAN> connect target /
RMAN> recover tablespace p_dt, p_idx until SCN 1432 auxiliary destination 'c:\auxx';
You should now see quite a number of RMAN messages displaying the status of each
operation. Once complete, back up the recovered tablespaces and bring them online:
RMAN> backup tablespace p_dt, p_idx;
RMAN> sql 'alter tablespace p_dt online';
RMAN> sql 'alter tablespace p_idx online';
Recovering a Subset of Datafiles
RMAN> connect target /
RMAN> startup mount;
Use the RMAN report schema command to identify which datafiles you do not want to
restore and recover. You can also query V$DATAFILE for the datafile details. In this
example,
the datafiles 7, 8, and 9 are taken offline and are not restored and recovered:
RMAN> sql 'alter database datafile 7, 8, 9 offline for drop';
RMAN> restore database until SCN 314159;
RMAN> recover database until SCN 314159;
RMAN> alter database open resetlogs;
Troubleshooting Incomplete Recovery
Problem
You’re attempting to perform an incomplete recovery, and RMAN is returning the following error:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
You wonder how to go about determining what is wrong.
Solution
In many situations, problems with incomplete recovery are caused by omitting one of the required steps. Here is the correct sequence of steps for most incomplete recovery scenarios:
1. restore database until
2. recover database until
3. alter database open resetlogs;
RMAN CREATING AND FIXING BLOCK CORRUPTION
CREATING AND FIXING BLOCK CORRUPTION
The purpose of this sidebar is to show you how to corrupt a block so that you can test recovering at the block
level. Do not perform this test exercise in a production environment.
In a Unix environment, you can corrupt a specific block in a datafile using the dd command. For example, the following dd command populates the 20th block of the tools01.dbf datafile with zeros:
$ dd if=/dev/zero of=tools01.dbf bs=8k conv=notrunc seek=20 count=1
Now if we attempt to back up the tools tablespace using RMAN, we receive an error indicating there is a
corrupt block:
RMAN> backup tablespace tools;
RMAN-03009: failure of backup command on ORA_DISK_1 channel
ORA-19566: exceeded limit of 0 corrupt blocks for file /ora01/BRDSTN/tools01.dbf
We additionally use the dbverify utility to validate that the tools01.dbf datafile has a corrupt block:
$ dbv file=/ora01/BRDSTN/tools01.dbf blocksize=8192
Here is the partial output of the dbverify command:
DBVERIFY - Verification starting : FILE = tools01.dbf
Page 20 is marked corrupt
Corrupt block relative dba: 0x01400014 (file 5, block 20)
The dbverify utility indicates that block 20 in file 5 is corrupt.We can corroborate this by viewing the
contents of V$DATABASE_BLOCK_CORRUPTION, as shown here:
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 20 1 0 ALL ZERO
We can now use the RMAN recover command to restore block 20 in datafile 5, as shown here (if
you’re using Oracle Database 10g or Oracle9i, then use the RMAN blockrecover command):
RMAN> recover datafile 5 block 20;
The purpose of this sidebar is to show you how to corrupt a block so that you can test recovering at the block
level. Do not perform this test exercise in a production environment.
In a Unix environment, you can corrupt a specific block in a datafile using the dd command. For example, the following dd command populates the 20th block of the tools01.dbf datafile with zeros:
$ dd if=/dev/zero of=tools01.dbf bs=8k conv=notrunc seek=20 count=1
Now if we attempt to back up the tools tablespace using RMAN, we receive an error indicating there is a
corrupt block:
RMAN> backup tablespace tools;
RMAN-03009: failure of backup command on ORA_DISK_1 channel
ORA-19566: exceeded limit of 0 corrupt blocks for file /ora01/BRDSTN/tools01.dbf
We additionally use the dbverify utility to validate that the tools01.dbf datafile has a corrupt block:
$ dbv file=/ora01/BRDSTN/tools01.dbf blocksize=8192
Here is the partial output of the dbverify command:
DBVERIFY - Verification starting : FILE = tools01.dbf
Page 20 is marked corrupt
Corrupt block relative dba: 0x01400014 (file 5, block 20)
The dbverify utility indicates that block 20 in file 5 is corrupt.We can corroborate this by viewing the
contents of V$DATABASE_BLOCK_CORRUPTION, as shown here:
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 20 1 0 ALL ZERO
We can now use the RMAN recover command to restore block 20 in datafile 5, as shown here (if
you’re using Oracle Database 10g or Oracle9i, then use the RMAN blockrecover command):
RMAN> recover datafile 5 block 20;
RMAN Performing Complete Recovery
VERIFICAR EL ESTADO DE LA INSTANCIA
Oracle would determine whether crash recovery is required:
SELECT
a.thread#, b.open_mode, a.status,
CASE
WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'
WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'
WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'
ELSE 'huh?'
END STATUS
FROM v$thread a,
v$database b,
v$instance c
WHERE a.thread# = c.thread#
/
THREAD# OPEN_MODE STATUS STATUS
---------- ---------- ------ --------------------------------
1 READ WRITE OPEN Inst. already open
The following SQL query demonstrates the internal checks that Oracle performs to determine
whether media recovery is required:
SELECT
a.name,
a.checkpoint_change#,
b.checkpoint_change#,
CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END STATUS
FROM v$datafile a, -- control file SCN for datafile
v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#;
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database;
RMAN> alter database open;
RMAN> recover database;
RMAN> alter database open;
Determining How to Restore and Recover
RMAN> connect target /
RMAN> startup;
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\BRDSTN\USERS01.DBF'
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_5416.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\BRDSTN\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
■
Validar the datafile headers and reports in the ERROR
SQL> select file#, status, error,recover from v$datafile_header;
The V$RECOVER_FILE reads from the control file and displays information about files
needing media recovery:
SQL> select file#, error from v$recover_file;
FILE# ERROR
---------- -------------------------
4 FILE NOT FOUND
Previewing Backups Needed for Restore
Problem
Before you perform a restore and recovery, you would like to view which backups will be required for the restore operation.
Solution
Use the restore ... preview command to query the RMAN repository for the most recent
backup sets and corresponding files that will be used for a restore operation. Three restore
... preview modes are available:
• Normal
• Summarized
• Recall (MML only)
Normal Mode
In normal mode, you’ll get a full listing of the information contained in the repository. The following
example shows how to preview the restore of the system tablespace:
RMAN> restore tablespace system preview;
The following examples show how to use the preview command with a variety of restore
operations:
RMAN> restore database preview;
RMAN> restore database from tag TAG20060927T183743 preview;
RMAN> restore datafile 1, 2, 3, 4 preview;
RMAN> restore archivelog all preview;
RMAN> restore archivelog from time 'sysdate - 1' preview;
RMAN> restore archivelog from scn 3243256 preview;
RMAN> restore archivelog from sequence 29 preview;
RESTORE DATABASE UNTIL SEQUENCE 13243;
EJEMPLO RESTAURACION DESDE VERITAS TAPE PARA SECUENCIAS
connect catalog rman/rman@cat9i;
set dbid=4007731659;
connect target;
run {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=sg-fa-ccol04-bck,NB_ORA_POLICY=Arc_NETCOOL_sg-fa-ccol04,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
restore archivelog from logseq=613 until logseq=614;
RELEASE CHANNEL ch00;
}
Summarized Mode
You can use the preview summary command to summarize the lengthy output. This next example shows summarized information about the backup set(s) RMAN will use to restore your entire database:
RMAN> restore database preview summary;
Recall Mode
If you use a media manager that supports vaulted backups, then you can use preview recall to recall media from remote storage. This next example will request that any media needed to restore the database be recalled from remote storage.
RMAN> restore database preview recall;
Verifying Integrity of Backups
RMAN> restore database validate;
RMAN> restore database from tag MON_BCK validate;
RMAN> restore datafile 1 validate;
RMAN> restore archivelog all validate;
RMAN> restore controlfile validate;
RMAN> restore tablespace users validate;
By default, RMAN checks only for physical corruption when validating. You can also
instruct RMAN to check for logical corruption with the check logical clause:
RMAN> restore database validate check logical;
RMAN> validate backupset 193;
RMAN> validate backupset 193 check logical;
RMAN> validate recovery area;
You can instruct RMAN to check for logical corruption with the check logical clause of the restore command. When RMAN detects logical corruption, it will write relevant error messages to your target database’s alert.log file and also reflect this information in the V$DATABASE_BLOCK_CORRUPTION view.
Testing Media Recovery
Problem
You need to perform a database recovery, but you suspect one of your archived redo log files is bad. You want to perform a test to see whether all of the redo is available and can be applied.
Solution
The recover ... test command instructs Oracle to apply the redo necessary to perform
recovery but does not make the changes permanent in the datafiles. When you recover in test mode, Oracle applies the required redo but rolls back the changes at the end of the process.
This example starts up the database in mount mode, restores the entire database, and
then does a test recovery:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database test;
■Note Before performing a test recovery, ensure that the datafiles being recovered are offline. Oracle will
throw an ORA-01124 error for any one line datafiles being recovered in test mode.
RMAN> recover tablespace users, tools test;
RMAN> recover datafile 1 test;
RMAN> recover database until time 'sysdate – 1/48' test;
RMAN> recover database until scn 2328888 test;
RMAN> recover database until sequence 343 test;
In Oracle Database 10g and lower, the syntax recover ... test allow n corruption does not work from within RMAN. If you want to run the test command with the allow n corruption clause, then you must issue that command from inside SQL*Plus, as shown here:
SQL> connect sys/muft as sysdba
SQL> recover tablespace system test allow 5 corruption;
Performing Database-Level Recovery
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Recover While Database Not Open
This solution works for any tablespace in your database. In this example, we restore the user_data and user_index tablespaces:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace user_data, user_index;
RMAN> recover tablespace user_data, user_index;
RMAN> alter database open;
Use Backup Control File
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
Recover While Database Is Open
You can take a tablespace offline, restore, and recover it while your database is open. This works for any tablespace except the system and undo tablespaces. This example takes data_ts offline and then restores and recovers before bringing it back online:
RMAN> connect target /
RMAN> sql 'alter tablespace data_ts offline immediate';
RMAN> restore tablespace data_ts;
RMAN> recover tablespace data_ts;
RMAN> sql 'alter tablespace data_ts online';
Performing Datafile-Level Recovery
RMAN> connect target /
RMAN> startup mount;
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> alter database open;
Recover While Database Open
For nonsystem and non-undo datafiles, you have the option of keeping the database open while performing the recovery. When your database is open, you’re required to take offline any datafiles you’re attempting to restore and recover.
RMAN> connect target /
RMAN> sql 'alter database datafile 3, 4 offline';
RMAN> restore datafile 3, 4;
RMAN> recover datafile 3, 4;
RMAN> sql 'alter database datafile 3, 4 online';
Restoring Datafiles to Nondefault Locations
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
3> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
4> restore tablespace data_ts;
5> switch datafile all; # Updates repository with new datafile location.
6> recover tablespace data_ts;
7> alter database open;
8> }
If the database is open, you can place the datafiles offline and then set their new names
for restore and recovery:
RMAN> run{
2> sql 'alter database datafile 4, 5 offline';
3> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
4> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
5> restore datafile 4, 5;
5> switch datafile all; # Updates repository with new datafile location.
6> recover datafile 4, 5;
7> sql 'alter database datafile 4, 5 online';
You can also use datafile names instead of numbers. However, you have to be careful about which name you use and where it comes in the script. This is because the control file doesn’t consider the new location to be the current location until you issue the switch command.
RMAN> run{
2> sql "alter database datafile ''/ora02/BRDSTN/data_ts01.dbf'' offline';
3> set newname for datafile '/ora02/BRDSTN/data_ts01.dbf'
4> to '/ora01/BRDSTN/data_ts01.dbf';
5> restore datafile '/ora02/BRDSTN/data_ts01.dbf';
6> switch datafile all; # Updates repository with new datafile location.
7> recover datafile '/ora01/BRDSTN/data_ts01.dbf';
8> sql "alter database datafile ''/ora01/BRDSTN/data_ts01.dbf'' online";
9> }
■
Performing Block-Level Recovery
RMAN> recover corruption list;
RMAN will automatically detect corruption in blocks whenever a backup or backup
validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view
The other way to recover blocks is to specify particular datafiles and blocks. Here are several
examples:
RMAN> recover datafile 5 block 24;
RMAN> recover datafile 7 block 22 datafile 8 block 43;
RMAN> recover datafile 5 block 24 from tag=tues_backup;
RMAN> recover datafile 6 block 89 restore until sequence 546;
RMAN> recover datafile 5 block 32 restore until 'sysdate-1';
RMAN> recover datafile 5 block 65 restore until scn 23453;
Recovering Read-Only Tablespaces
Problem
You issued a restore database command and notice that the datafiles associated with readonly
tablespaces were not restored.
Solution
Use the check readonly command to instruct RMAN to restore datafiles associated with readonly
tablespaces.
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database check readonly;
RMAN> recover database;
RMAN> alter database open;
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> restore tablespace MAR05DATA, JUN05DATA;
RMAN> recover database;
RMAN> alter database open;
Restoring Temporary Tablespaces
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE
2 '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
SQL> alter tablespace temp
2 add tempfile '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE;
Forcing RMAN to Restore a File
Problem
As part of a test exercise, you attempt to restore a datafile twice and receive this RMAN message: restore not done; all files readonly, offline, or already restored
Solution
Use the force command to restore datafiles and archived redo log files even if they already exist in a location. This command forces RMAN to restore files, even if RMAN determines that they don’t need to be restored. This first example uses the force to restore the obiwan01.dbf
datafile:
RMAN> restore datafile '/ora01/yoda/obiwan01.dbf' force;
You should see a message similar to this at the bottom of your RMAN messages stack:
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 09-FEB-07
Or if you know the particular datafile number, you can use the force command this way:
RMAN> restore datafile 42 force;
Similarly, you can use the force command on a tablespace. Here we use the force command to restore all datafiles associated with the star_wars tablespace:
RMAN> restore tablespace star_wars force;
RMAN> restore database force;
RMAN> restore archivelog from sequence 343 force;
Restoring from an Older Backup
Problem
You want to specifically instruct RMAN to restore from a backup set that is older than the last backup that was taken.
Specify a Tag Name
RMAN> startup mount;
RMAN> restore database from tag MON_BACK;
RMAN> recover database;
RMAN> alter database open;
RMAN> restore tablespace users from tag INCUPDATE;
RMAN> restore datafile 2, 3 from tag AUG_FULL;
Until
RMAN> startup mount;
RMAN> restore database until SCN 1254174;
Or if you know the log sequence number that you want to restore up to, the syntax is as follows:
RMAN> startup mount;
RMAN> restore database until sequence 17;
RMAN> recover database;
RMAN> alter database open;
If you’ve created restore points, then you can also use the restore point name as follows:
RMAN> startup mount;
RMAN> restore database until restore point FRI_RS;
RMAN> recover database;
RMAN> alter database open;
You can also specify a point in time from which you want RMAN to restore an older
backup. This example instructs RMAN to retrieve the first backup it finds that is more than 10 days old:
RMAN> startup mount;
RMAN> restore database until time 'sysdate – 10';
RMAN> recover database;
RMAN> alter database open;
Here we’re specifically instructing RMAN to restore from a date and time. Since we don’t
instruct RMAN to recover to a point in time, this example will perform a complete recovery:
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database;
RMAN> alter database open;
RMAN> recover database;
RMAN> alter database open;
Recovering Through Resetlogs
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
Restoring the Spfile
Using a Recovery Catalog
If you’re using a recovery catalog, then restoring the spfile is fairly straightforward. This example
connects to the recovery catalog and then restores the spfile:
RMAN> connect target /
RMAN> connect catalog rmancat/rmancat@rcat
RMAN> startup nomount;
starting Oracle instance without parameter file for retrieval of spfile
RMAN> restore spfile;
RMAN> startup force; # startup using restored spfile
Not Using a Recovery Catalog, RMAN Autobackup in Default Location
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> set dbid 260150593;
RMAN> restore spfile from autobackup;
RMAN> startup force; # startup using restored spfile
Not Using a Recovery Catalog, RMAN Autobackup Not in Default Location
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> set dbid 260150593;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> restore spfile from '/ora02/FRA/BRDSTN/autobackup/2006_10_02/o1_mf_s_62.bkp';
RMAN> startup force; # startup using restored spfile
Restoring Archived Redo Log Files
RMAN> restore archivelog all;
RMAN> restore archivelog from sequence 50;
If you want to restore a range of archived redo log files, use the from sequence and until sequence clauses or the sequence between clause, as shown here. These commands restore archived redo log files from sequence 5170 through 5178 using thread 1.
RMAN> restore archivelog from sequence 5170 until sequence 5178 thread 1;
RMAN> restore archivelog sequence between 5170 and 5178 thread 1;
By default, RMAN won’t restore an archived redo log file if it is already on disk. You can override this behavior via the force option:
RMAN> restore archivelog from sequence 1 force;
Restoring to Nondefault Location
Use the set archivelog destination clause if you want to restore archived redo log files to a different location than the default. The following example restores to the nondefault location
of /ora01/archrest. The set command must be run from within the RMAN run{} block.
RMAN> run{
2> set archivelog destination to '/ora01/archrest';
3> restore archivelog from sequence 5200;
4> }
Recovering Datafiles Not Backed Up
RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace user_idx;
Using a Backup Control File
This scenario is applicable anytime you use a backup control file to restore and recover a
datafile that has not yet been backed up. First, we restore a control file from an older backup:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from '/orafra/BRDSTN/autobackup/2006_10_11/01_mfn_.bkp';
Deleting Archived Redo Log Files During Recovery
RMAN> recover database delete archivelog;
RMAN> recover database delete archivelog maxsize 500m;
Restoring from Uncataloged Backup Pieces in Oracle
Database 10g and Newer
Problem
You had to re-create your control file and you are not using a recovery catalog. Afterward, you
attempted to restore datafiles using RMAN but received the following errors:
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
Solution
Use the catalog command to add RMAN metadata directly to your control file about backup pieces. Using a Flash Recovery Area
You can have RMAN repopulate the control file with all file information in the flash recovery. The following command will catalog all backup sets, datafile copies, and archived redo log files located in the flash recovery area:
RMAN> catalog recovery area;
Using a Directory
You can also instruct RMAN to catalog all the backup pieces and image copies located under a starting directory path. This example instructs RMAN to record metadata in the repository for any backup pieces and image copies located under the /oradump01/FRA directory:
RMAN> catalog start with '/oradump01/FRA';
Restoring from Uncataloged Backup Pieces in Oracle9i Database and Older
Problem
You had to re-create your control file, and you are not using a recovery catalog. You want to restore control files, datafiles, and archived redo logs from RMAN backup pieces, but your control file now contains no information whatsoever about previously taken backups.
Solution
Use the DBMS_BACKUP_RESTORE package to restore files from backup pieces. This recipe
has several examples:
• Restoring a control file
• Restoring datafiles contained in a single backup piece
• Restoring datafiles contained in several backup pieces
• Applying incremental backups to datafiles
Restoring a Control File
You can use the PL/SQL package DBMS_BACKUP_RESTORE to restore a control file. You need
to know the name of the backup piece that contains the backup of the control file before you
begin. Modify the following anonymous block of PL/SQL to use your backup piece name and
control file name:
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate a channel, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');
--
dbms_backup_restore.restoreSetDatafile;
dbms_backup_restore.restoreControlFileTo(
cfname=>'C:\oracle\product\10.2.0\oradata\ORCL\CONTROL01.CTL');
--
dbms_backup_restore.restoreBackupPiece(
'C:\oracle\product\10.2.0\flash_recovery_area\ORCL\AUTOBACKUP\
2006_12_06\O1_MF_N_608466281_2QFZ6TNJ_.BKP', finished);
--
if finished then
dbms_output.put_line('Control file restored.');
else
dbms_output.put_line('Problem');
end if;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
If the previous code was stored in a file named rc.sql, then you would execute it as follows:
SQL> connect / as sysdba
SQL> startup nomount;
SQL> @rc.sql
Restoring Datafiles in a Single Backup Piece
If you have output logs from your backups, then you can visually inspect those and determine the names of the datafiles within a backup piece. If you don’t have any output logs, then you’ll have to figure out through trial and error which datafiles are in which backup piece.
In this example, we know from our RMAN backup output logs that there are four datafiles contained within this backup piece. You’ll need to modify this anonymous block of PL/SQL
code to specify the files in your environment:
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of restore operation (does not restore anything yet).
dbms_backup_restore.restoreSetDatafile;
--
-- Define datafiles and their locations for datafiles in first backup piece.
dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'C:\ORCL\SYSTEM01.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'C:\ORCL\SYSAUX01.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'C:\ORCL\USERS08.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>9,toname=>'C:\ORCL\ORA02.DBF');
--
-- Restore the datafiles in this backup piece.
dbms_backup_restore.restoreBackupPiece(done => finished,
handle=>'C:\FRA\ORCL\BACKUPSET\2006_12_26\
O1_MF_NNNDF_TAG20061226T174632_2S3JM9NJ_.BKP', params=>null);
--
IF finished THEN
dbms_output.put_line('Datafiles restored');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
• Restoring archived redo log files
If you put the prior code into a file named dbr.sql, then you would run it as follows:
SQL> connect / as sysdba
SQL> startup mount;
SQL> @dbr.sql
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
TYPE v_filestable IS TABLE OF varchar2(500) INDEX BY BINARY_INTEGER;
v_filename V_FILESTABLE;
v_num_pieces NUMBER;
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of restore operation (does not restore anything yet).
dbms_backup_restore.restoreSetDatafile;
--
-- Define backup pieces in backup set.
v_filename(1) :=
'C:\FRA\ORCL\BACKUPSET\2006_12_29\O1_MF_NNNDF_TAG20061229T175720_2SCGCR59_.BKP';
v_filename(2) :=
'C:\FRA\ORCL\BACKUPSET\2006_12_29\O1_MF_NNNDF_TAG20061229T175720_2SCGG2J0_.BKP';
v_filename(3) :=
'C:\FRA\ORCL\BACKUPSET\2006_12_29\O1_MF_NNNDF_TAG20061229T175720_2SCGHSC4_.BKP';
-- There are 3 backup pieces in this backup set.
v_num_pieces := 3;
-- Define datafiles and locations.
dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'C:\ORCL\SYSTEM01.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'C:\ORCL\DS_TS01.DBF');
-- Restore the datafiles in this backup set.
FOR i IN 1..v_num_pieces LOOP
dbms_backup_restore.restoreBackupPiece(done => finished, handle=> v_filename(i),
params=>null);
END LOOP;
--
IF finished THEN
dbms_output.put_line('Datafiles restored');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
If you put the prior code into a file named dbr.sql, then you would run it as follows:
SQL> connect / as sysdba
SQL> startup mount;
SQL> @dbr.sql
Applying Incremental Backups
Here’s an example that shows how to apply an incremental backup. This example assumes
that the datafile has already been restored and is now ready to have an incremental backup
applied to it. You’ll need to modify this anonymous block of PL/SQL to specify the filenames
in your environment.
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of apply operation (does not restore anything yet).
dbms_backup_restore.applySetDatafile;
--
-- Define file to apply incremental to.
dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'C:\ORCL\DATA_TS.DBF');
--
-- Apply incremental backup to datafile.
dbms_backup_restore.applyBackupPiece(done=>finished,
handle=>'C:\FRA\ORCL\BACKUPSET\2006_12_31\O1_MF_NNND1_TAG20061231T130613_2SJ61T4S_.B
KP');
--
IF finished THEN
dbms_output.put_line('Incremental applied.');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
If you put the prior code into a file named dbr.sql, then you would run it as follows:
SQL> connect / as sysdba
SQL> startup mount;
SQL> @dbr.sql
Restoring Archived Redo Log Files
Here is an anonymous block of PL/SQL that shows how to restore archived redo log files using DBMS_BACKUP_RESTORE. Before using this, you’ll have to change this code to match your environment. This example restores two archived redo log files that are stored in one backup piece.
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of restore operation (does not restore anything yet).
dbms_backup_restore.restoreSetArchivedlog;
--
-- Define archived redo log files to be restored.
dbms_backup_restore.restoreArchivedlog(thread=>1, sequence=> 354);
dbms_backup_restore.restoreArchivedlog(thread=>1, sequence=> 355);
--
dbms_backup_restore.restoreBackupPiece(done=>finished, handle=>
'C:\FRA\ORCL\BACKUPSET\2006_12_30\O1_MF_ANNNN_TAG20061230T100354_2SF7055R_.BKP',
params=>null);
--
IF finished THEN
dbms_output.put_line('Archived redo log files restored');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
SQL> connect / as sysdba
SQL> @dbr.sql
Oracle would determine whether crash recovery is required:
SELECT
a.thread#, b.open_mode, a.status,
CASE
WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'
WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'
WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'
ELSE 'huh?'
END STATUS
FROM v$thread a,
v$database b,
v$instance c
WHERE a.thread# = c.thread#
/
THREAD# OPEN_MODE STATUS STATUS
---------- ---------- ------ --------------------------------
1 READ WRITE OPEN Inst. already open
The following SQL query demonstrates the internal checks that Oracle performs to determine
whether media recovery is required:
SELECT
a.name,
a.checkpoint_change#,
b.checkpoint_change#,
CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END STATUS
FROM v$datafile a, -- control file SCN for datafile
v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#;
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database;
RMAN> alter database open;
RMAN> recover database;
RMAN> alter database open;
Determining How to Restore and Recover
RMAN> connect target /
RMAN> startup;
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\BRDSTN\USERS01.DBF'
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_dbw0_5416.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'C:\ORACLE\BRDSTN\USERS01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
■
Validar the datafile headers and reports in the ERROR
SQL> select file#, status, error,recover from v$datafile_header;
The V$RECOVER_FILE reads from the control file and displays information about files
needing media recovery:
SQL> select file#, error from v$recover_file;
FILE# ERROR
---------- -------------------------
4 FILE NOT FOUND
Previewing Backups Needed for Restore
Problem
Before you perform a restore and recovery, you would like to view which backups will be required for the restore operation.
Solution
Use the restore ... preview command to query the RMAN repository for the most recent
backup sets and corresponding files that will be used for a restore operation. Three restore
... preview modes are available:
• Normal
• Summarized
• Recall (MML only)
Normal Mode
In normal mode, you’ll get a full listing of the information contained in the repository. The following
example shows how to preview the restore of the system tablespace:
RMAN> restore tablespace system preview;
The following examples show how to use the preview command with a variety of restore
operations:
RMAN> restore database preview;
RMAN> restore database from tag TAG20060927T183743 preview;
RMAN> restore datafile 1, 2, 3, 4 preview;
RMAN> restore archivelog all preview;
RMAN> restore archivelog from time 'sysdate - 1' preview;
RMAN> restore archivelog from scn 3243256 preview;
RMAN> restore archivelog from sequence 29 preview;
RESTORE DATABASE UNTIL SEQUENCE 13243;
EJEMPLO RESTAURACION DESDE VERITAS TAPE PARA SECUENCIAS
connect catalog rman/rman@cat9i;
set dbid=4007731659;
connect target;
run {
ALLOCATE CHANNEL ch00
TYPE 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=sg-fa-ccol04-bck,NB_ORA_POLICY=Arc_NETCOOL_sg-fa-ccol04,NB_ORA_SERV=bksrv01,NB_ORA_SCHED=Default-Application-Backup)';
restore archivelog from logseq=613 until logseq=614;
RELEASE CHANNEL ch00;
}
Summarized Mode
You can use the preview summary command to summarize the lengthy output. This next example shows summarized information about the backup set(s) RMAN will use to restore your entire database:
RMAN> restore database preview summary;
Recall Mode
If you use a media manager that supports vaulted backups, then you can use preview recall to recall media from remote storage. This next example will request that any media needed to restore the database be recalled from remote storage.
RMAN> restore database preview recall;
Verifying Integrity of Backups
RMAN> restore database validate;
RMAN> restore database from tag MON_BCK validate;
RMAN> restore datafile 1 validate;
RMAN> restore archivelog all validate;
RMAN> restore controlfile validate;
RMAN> restore tablespace users validate;
By default, RMAN checks only for physical corruption when validating. You can also
instruct RMAN to check for logical corruption with the check logical clause:
RMAN> restore database validate check logical;
RMAN> validate backupset 193;
RMAN> validate backupset 193 check logical;
RMAN> validate recovery area;
You can instruct RMAN to check for logical corruption with the check logical clause of the restore command. When RMAN detects logical corruption, it will write relevant error messages to your target database’s alert.log file and also reflect this information in the V$DATABASE_BLOCK_CORRUPTION view.
Testing Media Recovery
Problem
You need to perform a database recovery, but you suspect one of your archived redo log files is bad. You want to perform a test to see whether all of the redo is available and can be applied.
Solution
The recover ... test command instructs Oracle to apply the redo necessary to perform
recovery but does not make the changes permanent in the datafiles. When you recover in test mode, Oracle applies the required redo but rolls back the changes at the end of the process.
This example starts up the database in mount mode, restores the entire database, and
then does a test recovery:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database test;
■Note Before performing a test recovery, ensure that the datafiles being recovered are offline. Oracle will
throw an ORA-01124 error for any one line datafiles being recovered in test mode.
RMAN> recover tablespace users, tools test;
RMAN> recover datafile 1 test;
RMAN> recover database until time 'sysdate – 1/48' test;
RMAN> recover database until scn 2328888 test;
RMAN> recover database until sequence 343 test;
In Oracle Database 10g and lower, the syntax recover ... test allow n corruption does not work from within RMAN. If you want to run the test command with the allow n corruption clause, then you must issue that command from inside SQL*Plus, as shown here:
SQL> connect sys/muft as sysdba
SQL> recover tablespace system test allow 5 corruption;
Performing Database-Level Recovery
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Recover While Database Not Open
This solution works for any tablespace in your database. In this example, we restore the user_data and user_index tablespaces:
RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace user_data, user_index;
RMAN> recover tablespace user_data, user_index;
RMAN> alter database open;
Use Backup Control File
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
Recover While Database Is Open
You can take a tablespace offline, restore, and recover it while your database is open. This works for any tablespace except the system and undo tablespaces. This example takes data_ts offline and then restores and recovers before bringing it back online:
RMAN> connect target /
RMAN> sql 'alter tablespace data_ts offline immediate';
RMAN> restore tablespace data_ts;
RMAN> recover tablespace data_ts;
RMAN> sql 'alter tablespace data_ts online';
Performing Datafile-Level Recovery
RMAN> connect target /
RMAN> startup mount;
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> alter database open;
Recover While Database Open
For nonsystem and non-undo datafiles, you have the option of keeping the database open while performing the recovery. When your database is open, you’re required to take offline any datafiles you’re attempting to restore and recover.
RMAN> connect target /
RMAN> sql 'alter database datafile 3, 4 offline';
RMAN> restore datafile 3, 4;
RMAN> recover datafile 3, 4;
RMAN> sql 'alter database datafile 3, 4 online';
Restoring Datafiles to Nondefault Locations
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
3> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
4> restore tablespace data_ts;
5> switch datafile all; # Updates repository with new datafile location.
6> recover tablespace data_ts;
7> alter database open;
8> }
If the database is open, you can place the datafiles offline and then set their new names
for restore and recovery:
RMAN> run{
2> sql 'alter database datafile 4, 5 offline';
3> set newname for datafile 4 to '/ora01/BRDSTN/data_ts01.dbf';
4> set newname for datafile 5 to '/ora01/BRDSTN/data_ts02.dbf';
5> restore datafile 4, 5;
5> switch datafile all; # Updates repository with new datafile location.
6> recover datafile 4, 5;
7> sql 'alter database datafile 4, 5 online';
You can also use datafile names instead of numbers. However, you have to be careful about which name you use and where it comes in the script. This is because the control file doesn’t consider the new location to be the current location until you issue the switch command.
RMAN> run{
2> sql "alter database datafile ''/ora02/BRDSTN/data_ts01.dbf'' offline';
3> set newname for datafile '/ora02/BRDSTN/data_ts01.dbf'
4> to '/ora01/BRDSTN/data_ts01.dbf';
5> restore datafile '/ora02/BRDSTN/data_ts01.dbf';
6> switch datafile all; # Updates repository with new datafile location.
7> recover datafile '/ora01/BRDSTN/data_ts01.dbf';
8> sql "alter database datafile ''/ora01/BRDSTN/data_ts01.dbf'' online";
9> }
■
Performing Block-Level Recovery
RMAN> recover corruption list;
RMAN will automatically detect corruption in blocks whenever a backup or backup
validate command is issued. These blocks are reported as corrupt in the alert.log file and the V$DATABASE_BLOCK_CORRUPTION view
The other way to recover blocks is to specify particular datafiles and blocks. Here are several
examples:
RMAN> recover datafile 5 block 24;
RMAN> recover datafile 7 block 22 datafile 8 block 43;
RMAN> recover datafile 5 block 24 from tag=tues_backup;
RMAN> recover datafile 6 block 89 restore until sequence 546;
RMAN> recover datafile 5 block 32 restore until 'sysdate-1';
RMAN> recover datafile 5 block 65 restore until scn 23453;
Recovering Read-Only Tablespaces
Problem
You issued a restore database command and notice that the datafiles associated with readonly
tablespaces were not restored.
Solution
Use the check readonly command to instruct RMAN to restore datafiles associated with readonly
tablespaces.
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database check readonly;
RMAN> recover database;
RMAN> alter database open;
RMAN> connect target /
RMAN> startup mount;
RMAN> restore database;
RMAN> restore tablespace MAR05DATA, JUN05DATA;
RMAN> recover database;
RMAN> alter database open;
Restoring Temporary Tablespaces
SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE
2 '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
SQL> alter tablespace temp
2 add tempfile '/ora03/oradata/BRDSTN/temp01.dbf' SIZE 5000M REUSE;
Forcing RMAN to Restore a File
Problem
As part of a test exercise, you attempt to restore a datafile twice and receive this RMAN message: restore not done; all files readonly, offline, or already restored
Solution
Use the force command to restore datafiles and archived redo log files even if they already exist in a location. This command forces RMAN to restore files, even if RMAN determines that they don’t need to be restored. This first example uses the force to restore the obiwan01.dbf
datafile:
RMAN> restore datafile '/ora01/yoda/obiwan01.dbf' force;
You should see a message similar to this at the bottom of your RMAN messages stack:
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 09-FEB-07
Or if you know the particular datafile number, you can use the force command this way:
RMAN> restore datafile 42 force;
Similarly, you can use the force command on a tablespace. Here we use the force command to restore all datafiles associated with the star_wars tablespace:
RMAN> restore tablespace star_wars force;
RMAN> restore database force;
RMAN> restore archivelog from sequence 343 force;
Restoring from an Older Backup
Problem
You want to specifically instruct RMAN to restore from a backup set that is older than the last backup that was taken.
Specify a Tag Name
RMAN> startup mount;
RMAN> restore database from tag MON_BACK;
RMAN> recover database;
RMAN> alter database open;
RMAN> restore tablespace users from tag INCUPDATE;
RMAN> restore datafile 2, 3 from tag AUG_FULL;
Until
RMAN> startup mount;
RMAN> restore database until SCN 1254174;
Or if you know the log sequence number that you want to restore up to, the syntax is as follows:
RMAN> startup mount;
RMAN> restore database until sequence 17;
RMAN> recover database;
RMAN> alter database open;
If you’ve created restore points, then you can also use the restore point name as follows:
RMAN> startup mount;
RMAN> restore database until restore point FRI_RS;
RMAN> recover database;
RMAN> alter database open;
You can also specify a point in time from which you want RMAN to restore an older
backup. This example instructs RMAN to retrieve the first backup it finds that is more than 10 days old:
RMAN> startup mount;
RMAN> restore database until time 'sysdate – 10';
RMAN> recover database;
RMAN> alter database open;
Here we’re specifically instructing RMAN to restore from a date and time. Since we don’t
instruct RMAN to recover to a point in time, this example will perform a complete recovery:
RMAN> startup mount;
RMAN> restore database until time
2> "to_date('05-oct-2006 14:00:00', dd-mon-rrrr hh24:mi:ss')";
RMAN> recover database;
RMAN> alter database open;
RMAN> recover database;
RMAN> alter database open;
Recovering Through Resetlogs
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
Restoring the Spfile
Using a Recovery Catalog
If you’re using a recovery catalog, then restoring the spfile is fairly straightforward. This example
connects to the recovery catalog and then restores the spfile:
RMAN> connect target /
RMAN> connect catalog rmancat/rmancat@rcat
RMAN> startup nomount;
starting Oracle instance without parameter file for retrieval of spfile
RMAN> restore spfile;
RMAN> startup force; # startup using restored spfile
Not Using a Recovery Catalog, RMAN Autobackup in Default Location
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> set dbid 260150593;
RMAN> restore spfile from autobackup;
RMAN> startup force; # startup using restored spfile
Not Using a Recovery Catalog, RMAN Autobackup Not in Default Location
RMAN> connect target /
RMAN> shutdown immediate;
RMAN> set dbid 260150593;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> restore spfile from '/ora02/FRA/BRDSTN/autobackup/2006_10_02/o1_mf_s_62.bkp';
RMAN> startup force; # startup using restored spfile
Restoring Archived Redo Log Files
RMAN> restore archivelog all;
RMAN> restore archivelog from sequence 50;
If you want to restore a range of archived redo log files, use the from sequence and until sequence clauses or the sequence between clause, as shown here. These commands restore archived redo log files from sequence 5170 through 5178 using thread 1.
RMAN> restore archivelog from sequence 5170 until sequence 5178 thread 1;
RMAN> restore archivelog sequence between 5170 and 5178 thread 1;
By default, RMAN won’t restore an archived redo log file if it is already on disk. You can override this behavior via the force option:
RMAN> restore archivelog from sequence 1 force;
Restoring to Nondefault Location
Use the set archivelog destination clause if you want to restore archived redo log files to a different location than the default. The following example restores to the nondefault location
of /ora01/archrest. The set command must be run from within the RMAN run{} block.
RMAN> run{
2> set archivelog destination to '/ora01/archrest';
3> restore archivelog from sequence 5200;
4> }
Recovering Datafiles Not Backed Up
RMAN> connect target /
RMAN> startup mount;
RMAN> restore tablespace user_idx;
Using a Backup Control File
This scenario is applicable anytime you use a backup control file to restore and recover a
datafile that has not yet been backed up. First, we restore a control file from an older backup:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from '/orafra/BRDSTN/autobackup/2006_10_11/01_mfn_.bkp';
Deleting Archived Redo Log Files During Recovery
RMAN> recover database delete archivelog;
RMAN> recover database delete archivelog maxsize 500m;
Restoring from Uncataloged Backup Pieces in Oracle
Database 10g and Newer
Problem
You had to re-create your control file and you are not using a recovery catalog. Afterward, you
attempted to restore datafiles using RMAN but received the following errors:
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
Solution
Use the catalog command to add RMAN metadata directly to your control file about backup pieces. Using a Flash Recovery Area
You can have RMAN repopulate the control file with all file information in the flash recovery. The following command will catalog all backup sets, datafile copies, and archived redo log files located in the flash recovery area:
RMAN> catalog recovery area;
Using a Directory
You can also instruct RMAN to catalog all the backup pieces and image copies located under a starting directory path. This example instructs RMAN to record metadata in the repository for any backup pieces and image copies located under the /oradump01/FRA directory:
RMAN> catalog start with '/oradump01/FRA';
Restoring from Uncataloged Backup Pieces in Oracle9i Database and Older
Problem
You had to re-create your control file, and you are not using a recovery catalog. You want to restore control files, datafiles, and archived redo logs from RMAN backup pieces, but your control file now contains no information whatsoever about previously taken backups.
Solution
Use the DBMS_BACKUP_RESTORE package to restore files from backup pieces. This recipe
has several examples:
• Restoring a control file
• Restoring datafiles contained in a single backup piece
• Restoring datafiles contained in several backup pieces
• Applying incremental backups to datafiles
Restoring a Control File
You can use the PL/SQL package DBMS_BACKUP_RESTORE to restore a control file. You need
to know the name of the backup piece that contains the backup of the control file before you
begin. Modify the following anonymous block of PL/SQL to use your backup piece name and
control file name:
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate a channel, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=>'d1');
--
dbms_backup_restore.restoreSetDatafile;
dbms_backup_restore.restoreControlFileTo(
cfname=>'C:\oracle\product\10.2.0\oradata\ORCL\CONTROL01.CTL');
--
dbms_backup_restore.restoreBackupPiece(
'C:\oracle\product\10.2.0\flash_recovery_area\ORCL\AUTOBACKUP\
2006_12_06\O1_MF_N_608466281_2QFZ6TNJ_.BKP', finished);
--
if finished then
dbms_output.put_line('Control file restored.');
else
dbms_output.put_line('Problem');
end if;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
If the previous code was stored in a file named rc.sql, then you would execute it as follows:
SQL> connect / as sysdba
SQL> startup nomount;
SQL> @rc.sql
Restoring Datafiles in a Single Backup Piece
If you have output logs from your backups, then you can visually inspect those and determine the names of the datafiles within a backup piece. If you don’t have any output logs, then you’ll have to figure out through trial and error which datafiles are in which backup piece.
In this example, we know from our RMAN backup output logs that there are four datafiles contained within this backup piece. You’ll need to modify this anonymous block of PL/SQL
code to specify the files in your environment:
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of restore operation (does not restore anything yet).
dbms_backup_restore.restoreSetDatafile;
--
-- Define datafiles and their locations for datafiles in first backup piece.
dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'C:\ORCL\SYSTEM01.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'C:\ORCL\SYSAUX01.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'C:\ORCL\USERS08.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>9,toname=>'C:\ORCL\ORA02.DBF');
--
-- Restore the datafiles in this backup piece.
dbms_backup_restore.restoreBackupPiece(done => finished,
handle=>'C:\FRA\ORCL\BACKUPSET\2006_12_26\
O1_MF_NNNDF_TAG20061226T174632_2S3JM9NJ_.BKP', params=>null);
--
IF finished THEN
dbms_output.put_line('Datafiles restored');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
• Restoring archived redo log files
If you put the prior code into a file named dbr.sql, then you would run it as follows:
SQL> connect / as sysdba
SQL> startup mount;
SQL> @dbr.sql
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
TYPE v_filestable IS TABLE OF varchar2(500) INDEX BY BINARY_INTEGER;
v_filename V_FILESTABLE;
v_num_pieces NUMBER;
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of restore operation (does not restore anything yet).
dbms_backup_restore.restoreSetDatafile;
--
-- Define backup pieces in backup set.
v_filename(1) :=
'C:\FRA\ORCL\BACKUPSET\2006_12_29\O1_MF_NNNDF_TAG20061229T175720_2SCGCR59_.BKP';
v_filename(2) :=
'C:\FRA\ORCL\BACKUPSET\2006_12_29\O1_MF_NNNDF_TAG20061229T175720_2SCGG2J0_.BKP';
v_filename(3) :=
'C:\FRA\ORCL\BACKUPSET\2006_12_29\O1_MF_NNNDF_TAG20061229T175720_2SCGHSC4_.BKP';
-- There are 3 backup pieces in this backup set.
v_num_pieces := 3;
-- Define datafiles and locations.
dbms_backup_restore.restoreDatafileTo(dfnumber=>1,toname=>'C:\ORCL\SYSTEM01.DBF');
dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'C:\ORCL\DS_TS01.DBF');
-- Restore the datafiles in this backup set.
FOR i IN 1..v_num_pieces LOOP
dbms_backup_restore.restoreBackupPiece(done => finished, handle=> v_filename(i),
params=>null);
END LOOP;
--
IF finished THEN
dbms_output.put_line('Datafiles restored');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
If you put the prior code into a file named dbr.sql, then you would run it as follows:
SQL> connect / as sysdba
SQL> startup mount;
SQL> @dbr.sql
Applying Incremental Backups
Here’s an example that shows how to apply an incremental backup. This example assumes
that the datafile has already been restored and is now ready to have an incremental backup
applied to it. You’ll need to modify this anonymous block of PL/SQL to specify the filenames
in your environment.
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of apply operation (does not restore anything yet).
dbms_backup_restore.applySetDatafile;
--
-- Define file to apply incremental to.
dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'C:\ORCL\DATA_TS.DBF');
--
-- Apply incremental backup to datafile.
dbms_backup_restore.applyBackupPiece(done=>finished,
handle=>'C:\FRA\ORCL\BACKUPSET\2006_12_31\O1_MF_NNND1_TAG20061231T130613_2SJ61T4S_.B
KP');
--
IF finished THEN
dbms_output.put_line('Incremental applied.');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
If you put the prior code into a file named dbr.sql, then you would run it as follows:
SQL> connect / as sysdba
SQL> startup mount;
SQL> @dbr.sql
Restoring Archived Redo Log Files
Here is an anonymous block of PL/SQL that shows how to restore archived redo log files using DBMS_BACKUP_RESTORE. Before using this, you’ll have to change this code to match your environment. This example restores two archived redo log files that are stored in one backup piece.
SET SERVEROUTPUT ON
DECLARE
finished BOOLEAN;
v_dev_name VARCHAR2(75);
BEGIN
-- Allocate channels, when disk then type = null, if tape then type = sbt_tape.
v_dev_name := dbms_backup_restore.deviceAllocate(type=>null, ident=> 'd1');
--
-- Set beginning of restore operation (does not restore anything yet).
dbms_backup_restore.restoreSetArchivedlog;
--
-- Define archived redo log files to be restored.
dbms_backup_restore.restoreArchivedlog(thread=>1, sequence=> 354);
dbms_backup_restore.restoreArchivedlog(thread=>1, sequence=> 355);
--
dbms_backup_restore.restoreBackupPiece(done=>finished, handle=>
'C:\FRA\ORCL\BACKUPSET\2006_12_30\O1_MF_ANNNN_TAG20061230T100354_2SF7055R_.BKP',
params=>null);
--
IF finished THEN
dbms_output.put_line('Archived redo log files restored');
ELSE
dbms_output.put_line('Problem');
END IF;
--
dbms_backup_restore.deviceDeallocate('d1');
END;
/
SQL> connect / as sysdba
SQL> @dbr.sql
RMAN Restoring the Control File
Using the Autobackup of the Control File
When you enable the autobackup of your control file and are using a flash recovery area, then
restoring your control file is fairly simple. First connect to your target database, then issue
a startup nomount command, and lastly issue the restore controlfile from autobackup
command:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
Not Using the Autobackup of the Control File
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from
2> 'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';
Restoring Control File Using Recovery Catalog
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> restore controlfile;
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> list backup of controlfile;
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4922 Full 90.64M DISK 00:00:03 20-SEP-09
BP Key: 4996 Status: AVAILABLE Compressed: NO Tag: TAG20090920T002927
Piece Name: /u02/backup/letodb/c-3814852239-20090920-01
Control File Included: Ckp SCN: 723968640 Ckp time: 20-SEP-09
Writing the DBID to the Alert.log File
Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code
execute as part of your backup job:
COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));
VERIFYING USE OF A FRA
You can verify whether you are using a flash recovery area (FRA) by issuing the following SQL statement:
SQL> show parameter db_recovery_file_dest;
If you are not using a flash recovery area, then the value of the db_recovery_file_dest
initialization parameter will be null. If you are using a flash recovery area, then there will be a directory
shown in the VALUE column. For example, here’s the FRA for one of our test databases:
NAME TYPE VALUE
---------------------- ----------- ------------------------------
db_recovery_file_dest string /oraback/FRA
you can connect to your target database, issue startup nomount, set the DBID
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506593;
RMAN> restore controlfile from autobackup;
By default, RMAN will attempt to retrieve from autobackups created within the last seven days only. If you want to modify the default behavior, use the maxdays parameter. This example
instructs RMAN to look for a control file backup created in the last 20 days:
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> restore controlfile from autobackup maxdays 20;
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> set controlfile autobackup format for device type disk to 'C:\ODUMP\%F';
RMAN> restore controlfile from autobackup;
Unix
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from '/ora01/app/oracle/product/10.2.0/dbs/1hhu0gn1_1_1';
Windows
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from 'C:\ORACLE\PROD\10.2.0\DB_1\DATABASE\5IHU0G6H_1_1';
RMAN> restore controlfile from autobackup maxseq 10;
This example shows the syntax when restoring to a nondefault location and using an autobackup of the control file:
RMAN> connect target /
RMAN> restore controlfile to 'C:\ctl.bk' from autobackup;
RMAN> connect target /
RMAN> connect rmancat/rmancat@rcat
RMAN> restore controlfile to 'C:\ctl.bk';
Restoring Lost Copy of Multiplexed Control File
Problem
You attempted to start your database and receive the following error:
ORA-00205: error identifying controlfile, check alert log for more info
You check your target database alert.log file and verify that Oracle can’t obtain the status of one of your database control files. You wonder whether you can use a good copy of an existing
control file to resolve this issue.
Solution
Modifying the Initialization File
SQL> startup nomount;
SQL> alter system
2 set control_files='/ora01/oradata/BRDSTN/control01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
Copying a Good Control File
RMAN> shutdown immediate;
or using this:
SQL> shutdown immediate;
$ cp /ora01/oradata/BRDSTN/control01.ctl /ora02/oradata/BRDSTN/control02.ctl
Re-creating the Control File
NAMING A TRACE FILE
If you want to specify a text string to be used as part of the trace filename, then use the tracefile_
SQL> alter session set tracefile_identifier='MYTRACE';
Problem
One of the following situations applies:
• You’ve experienced a failure and lost all of your control files, and you belatedly realize that you don’t have a good binary backup of the control file.
• You want to change a database setting that can be modified only by re-creating the control
file.
• You are relocating a large number of datafiles, and you find it easier to re-create the control file with the new names and locations (instead of manually renaming the datafiles).
Solution
You can use the output of the following command to re-create your control file:
SQL> alter database backup controlfile to trace;
The previous command generates a trace file that is placed in your user dump directory.
You can display the location of your user_dump_dest from SQL as follows:
SQL> show parameter user_dump_dest
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 50M,
GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 50M
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;
When you enable the autobackup of your control file and are using a flash recovery area, then
restoring your control file is fairly simple. First connect to your target database, then issue
a startup nomount command, and lastly issue the restore controlfile from autobackup
command:
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
Not Using the Autobackup of the Control File
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from
2> 'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';
Restoring Control File Using Recovery Catalog
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> restore controlfile;
RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> list backup of controlfile;
RMAN> list backup of controlfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4922 Full 90.64M DISK 00:00:03 20-SEP-09
BP Key: 4996 Status: AVAILABLE Compressed: NO Tag: TAG20090920T002927
Piece Name: /u02/backup/letodb/c-3814852239-20090920-01
Control File Included: Ckp SCN: 723968640 Ckp time: 20-SEP-09
Writing the DBID to the Alert.log File
Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code
execute as part of your backup job:
COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));
VERIFYING USE OF A FRA
You can verify whether you are using a flash recovery area (FRA) by issuing the following SQL statement:
SQL> show parameter db_recovery_file_dest;
If you are not using a flash recovery area, then the value of the db_recovery_file_dest
initialization parameter will be null. If you are using a flash recovery area, then there will be a directory
shown in the VALUE column. For example, here’s the FRA for one of our test databases:
NAME TYPE VALUE
---------------------- ----------- ------------------------------
db_recovery_file_dest string /oraback/FRA
you can connect to your target database, issue startup nomount, set the DBID
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506593;
RMAN> restore controlfile from autobackup;
By default, RMAN will attempt to retrieve from autobackups created within the last seven days only. If you want to modify the default behavior, use the maxdays parameter. This example
instructs RMAN to look for a control file backup created in the last 20 days:
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> restore controlfile from autobackup maxdays 20;
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> set controlfile autobackup format for device type disk to 'C:\ODUMP\%F';
RMAN> restore controlfile from autobackup;
Unix
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from '/ora01/app/oracle/product/10.2.0/dbs/1hhu0gn1_1_1';
Windows
RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from 'C:\ORACLE\PROD\10.2.0\DB_1\DATABASE\5IHU0G6H_1_1';
RMAN> restore controlfile from autobackup maxseq 10;
This example shows the syntax when restoring to a nondefault location and using an autobackup of the control file:
RMAN> connect target /
RMAN> restore controlfile to 'C:\ctl.bk' from autobackup;
RMAN> connect target /
RMAN> connect rmancat/rmancat@rcat
RMAN> restore controlfile to 'C:\ctl.bk';
Restoring Lost Copy of Multiplexed Control File
Problem
You attempted to start your database and receive the following error:
ORA-00205: error identifying controlfile, check alert log for more info
You check your target database alert.log file and verify that Oracle can’t obtain the status of one of your database control files. You wonder whether you can use a good copy of an existing
control file to resolve this issue.
Solution
Modifying the Initialization File
SQL> startup nomount;
SQL> alter system
2 set control_files='/ora01/oradata/BRDSTN/control01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
Copying a Good Control File
RMAN> shutdown immediate;
or using this:
SQL> shutdown immediate;
$ cp /ora01/oradata/BRDSTN/control01.ctl /ora02/oradata/BRDSTN/control02.ctl
Re-creating the Control File
NAMING A TRACE FILE
If you want to specify a text string to be used as part of the trace filename, then use the tracefile_
SQL> alter session set tracefile_identifier='MYTRACE';
Problem
One of the following situations applies:
• You’ve experienced a failure and lost all of your control files, and you belatedly realize that you don’t have a good binary backup of the control file.
• You want to change a database setting that can be modified only by re-creating the control
file.
• You are relocating a large number of datafiles, and you find it easier to re-create the control file with the new names and locations (instead of manually renaming the datafiles).
Solution
You can use the output of the following command to re-create your control file:
SQL> alter database backup controlfile to trace;
The previous command generates a trace file that is placed in your user dump directory.
You can display the location of your user_dump_dest from SQL as follows:
SQL> show parameter user_dump_dest
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 50M,
GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 50M
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;
RMAN Creacion Scripts
CREATE GLOBAL SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}
CREATE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';
RUN { EXECUTE GLOBAL SCRIPT full_backup; }
PRINT GLOBAL SCRIPT full_backup;
LIST GLOBAL SCRIPT NAMES;
LIST ALL SCRIPT NAMES; # Global and local scripts.
REPLACE GLOBAL SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}
REPLACE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';
DELETE GLOBAL SCRIPT 'full_backup';
Creating Local-Stored Scripts
Script Windows
RMAN> create script full_disk_db
2> {
3> allocate channel c1 type disk
4> format 'c:\oracle\flash\loc1\rman_%U.rman';
5> backup
6> database
7> include current controlfile;
8> release channel c1;
9> }
Creating a Global-Stored Script
RMAN> create global script gs_arc_disk_bkup
2> comment 'Global Script to Backup Arc Logs Delete Input'
3> {
4> allocate channel c1 type disk
5> format 'C:\oraback\%U.rman';
6> backup
7> archivelog
8> all
9> delete input;
10> release channel c1;
11> }
Updating Stored Scripts
RMAN> replace script full_disk_db
2> {
3> allocate channel c1 type disk
4> format 'c:\backup\rman_%U.rman';
5> backup
6> database
7> include current controlfile;
8> release channel c1;
9> }
Commenting on Stored Scripts
RMAN> create script full_disk_db
2> comment 'Full Backup as Backupset to Disk'
3> {
4> allocate channel c1 type disk
5> format 'c:\backup\rman_%U.rman';
6> backup
7> database
8> include current controlfile;
9> release channel c1;
10> }
Displaying Stored Scripts
RMAN> print script full_disk_db;
The output, in this case, comes back as follows:
printing stored script: full_disk_db
{allocate channel c1 type disk
format 'c:\backup\rman_%U.rman';
backup
database
include current controlfile;
release channel c1;
}
RMAN> print global script full_disk_db;
RMAN> list script names;
EXAMPLE
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Sep 25 10:00:02 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: LETODB (DBID=3814852239)
RMAN> connect catalog rman/rman@cat10g;
connected to recovery catalog database
RMAN> list script names;
List of Stored Scripts in Recovery Catalog
No scripts in recovery catalog
RMAN> list global script names;
Dropping Stored Scripts
RMAN> delete script delete_arc_logs;
You have two scripts of the same name—delete_arc_logs—one local and one global. You
want to execute the global script, not the local one.
Solution
To execute the global script, you call that script with the clause global before it, as shown in
the following RMAN command:
RMAN> run { execute global script delete_arc_logs; }
Converting Stored Scripts to Files
RMAN> print script delete_arc_logs to file 'c:\tools\delete_arc_logs.rman';
Creating or Replacing a Stored Script from a File
RMAN> replace script delete_arc_logs from file 'c:\tools\delete_arc_logs.rman';
Passing Parameters to Stored Scripts
RMAN> replace script delete_archive_log { delete noprompt archivelog sequence &1 ; }
RMAN> replace script delete_archive_log { delete &2 archivelog sequence &1 ; }
Creating a Parameterized Command File Script
Problem
You want to create an RMAN command file script that can accept a parameter.
Solution
In Oracle Database 11g, to create a RMAN command file in Unix (or Windows) that can accept
a parameter, you simply create a file like this:
{ delete noprompt archivelog sequence &1 ; }
C:\tools>rman target=/ @c:\tools\del_arc_logs_pattern.rman using '2007_05_11'
Recovery Manager: Release 11.1.0.4.0 - Beta on Fri May 11 14:55:36 2007
Copyright (c) 1982, 2006, Oracle. All rights reserved.
connected to target database: MOBDB11 (DBID=406156306)
RMAN> { delete archivelog like '%2007_05_11%' ; }
2>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_38_➥
348WK5OZ_.ARC RECID=4 STAMP=622287589
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_39_➥
348WKBQX_.ARC RECID=5 STAMP=622287595
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_40__➥
348WKCB8_.ARC RECID=6 STAMP=622287595
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_41__➥
348WKJ6C_.ARC RECID=7 STAMP=622287600
Deleted 4 objects
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}
CREATE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';
RUN { EXECUTE GLOBAL SCRIPT full_backup; }
PRINT GLOBAL SCRIPT full_backup;
LIST GLOBAL SCRIPT NAMES;
LIST ALL SCRIPT NAMES; # Global and local scripts.
REPLACE GLOBAL SCRIPT full_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
DELETE FORCE NOPROMPT OBSOLETE;
}
REPLACE GLOBAL SCRIPT full_backup FROM FILE 'full_backup.txt';
DELETE GLOBAL SCRIPT 'full_backup';
Creating Local-Stored Scripts
Script Windows
RMAN> create script full_disk_db
2> {
3> allocate channel c1 type disk
4> format 'c:\oracle\flash\loc1\rman_%U.rman';
5> backup
6> database
7> include current controlfile;
8> release channel c1;
9> }
Creating a Global-Stored Script
RMAN> create global script gs_arc_disk_bkup
2> comment 'Global Script to Backup Arc Logs Delete Input'
3> {
4> allocate channel c1 type disk
5> format 'C:\oraback\%U.rman';
6> backup
7> archivelog
8> all
9> delete input;
10> release channel c1;
11> }
Updating Stored Scripts
RMAN> replace script full_disk_db
2> {
3> allocate channel c1 type disk
4> format 'c:\backup\rman_%U.rman';
5> backup
6> database
7> include current controlfile;
8> release channel c1;
9> }
Commenting on Stored Scripts
RMAN> create script full_disk_db
2> comment 'Full Backup as Backupset to Disk'
3> {
4> allocate channel c1 type disk
5> format 'c:\backup\rman_%U.rman';
6> backup
7> database
8> include current controlfile;
9> release channel c1;
10> }
Displaying Stored Scripts
RMAN> print script full_disk_db;
The output, in this case, comes back as follows:
printing stored script: full_disk_db
{allocate channel c1 type disk
format 'c:\backup\rman_%U.rman';
backup
database
include current controlfile;
release channel c1;
}
RMAN> print global script full_disk_db;
RMAN> list script names;
EXAMPLE
Recovery Manager: Release 10.2.0.4.0 - Production on Fri Sep 25 10:00:02 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: LETODB (DBID=3814852239)
RMAN> connect catalog rman/rman@cat10g;
connected to recovery catalog database
RMAN> list script names;
List of Stored Scripts in Recovery Catalog
No scripts in recovery catalog
RMAN> list global script names;
Dropping Stored Scripts
RMAN> delete script delete_arc_logs;
You have two scripts of the same name—delete_arc_logs—one local and one global. You
want to execute the global script, not the local one.
Solution
To execute the global script, you call that script with the clause global before it, as shown in
the following RMAN command:
RMAN> run { execute global script delete_arc_logs; }
Converting Stored Scripts to Files
RMAN> print script delete_arc_logs to file 'c:\tools\delete_arc_logs.rman';
Creating or Replacing a Stored Script from a File
RMAN> replace script delete_arc_logs from file 'c:\tools\delete_arc_logs.rman';
Passing Parameters to Stored Scripts
RMAN> replace script delete_archive_log { delete noprompt archivelog sequence &1 ; }
RMAN> replace script delete_archive_log { delete &2 archivelog sequence &1 ; }
Creating a Parameterized Command File Script
Problem
You want to create an RMAN command file script that can accept a parameter.
Solution
In Oracle Database 11g, to create a RMAN command file in Unix (or Windows) that can accept
a parameter, you simply create a file like this:
{ delete noprompt archivelog sequence &1 ; }
C:\tools>rman target=/ @c:\tools\del_arc_logs_pattern.rman using '2007_05_11'
Recovery Manager: Release 11.1.0.4.0 - Beta on Fri May 11 14:55:36 2007
Copyright (c) 1982, 2006, Oracle. All rights reserved.
connected to target database: MOBDB11 (DBID=406156306)
RMAN> { delete archivelog like '%2007_05_11%' ; }
2>
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_38_➥
348WK5OZ_.ARC RECID=4 STAMP=622287589
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_39_➥
348WKBQX_.ARC RECID=5 STAMP=622287595
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_40__➥
348WKCB8_.ARC RECID=6 STAMP=622287595
deleted archived log
archived log file name=C:\ORACLE\FLASH\MOBDB11\ARCHIVELOG\2007_05_11\O1_MF_1_41__➥
348WKJ6C_.ARC RECID=7 STAMP=622287600
Deleted 4 objects
RMAN Scripting
$ rman target=/ catalog=u/p@catalog cmdfile cmd.rman
You can also use the cmdfile option with an equal sign:
$ rman target=/ catalog=u/p@catalog cmdfile=cmd.rman
You can use the SQL*Plus-like notation to call a script by placing an @ before the name.
For example:
$ rman target=/ catalog=u/p@catalog @cmd.rman
SCRIPT UNIX
1. # Beginning of Script
2. # Start of Configurable Section
3. export ORACLE_HOME=/opt/oracle/10.2/db_1
4. export ORACLE_SID=PRODB1
5. export TOOLHOME=/opt/oracle/tools
6. export BACKUP_MEDIA=DISK
7. export BACKUP_TYPE=FULL_DB_BKUP
8. export MAXPIECESIZE=16G
9. # End of Configurable Section
10. # Start of site specific parameters
11. export BACKUP_MOUNTPOINT=/oraback
12. export DBAEMAIL="dbas@proligence.com"
13. export DBAPAGER="dba.ops@proligence.com"
14. export LOG_SERVER=prolin2
15. export LOG_USER=oracle
16. export LOG_DIR=/dbalogs
17. export CATALOG_CONN=${ORACLE_SID}/${ORACLE_SID}@catalog
18. # End of site specific parameters
19. export LOC_PREFIX=$BACKUP_MOUNTPOINT/loc
20. export TMPDIR=/tmp
21. export NLS_DATE_FORMAT="MM/DD/YY HH24:MI:SS"
22. export TIMESTAMP=`date +%T-%m-%d-%Y`
23. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
24. export LIBPATH=$ORACLE_HOME/lib:/usr/lib:/lib
25. export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
26. export LOG=${TOOLHOME}/log
27. LOG=${LOG}/log/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}_${TIMESTAMP}.log
28. export TMPLOG=$TOOLHOME/log/tmplog.$$
29. echo `date` "Starting $BACKUP_TYPE Backup of $ORACLE_SID \
30. to $BACKUP_MEDIA" > $LOG
31. export LOCKFILE=$TOOLHOME/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}.lock
32. if [ -f $LOCKFILE ]; then
33. echo `date` "Script running. Exiting ..." >> $LOG
34. else
35. echo "Do NOT delete this file. Used for RMAN locking" > $LOCKFILE
36. $ORACLE_HOME/bin/rman log=$TMPLOG <
38. connect catalog $CATALOG_CONN
39. CONFIGURE SNAPSHOT CONTROLFILE NAME TO
40. '${ORACLE_HOME}/dbs/SNAPSHOT_${ORACLE_SID}_${TIMESTAMP}_CTL';
41. run
42. {
43. allocate channel c1 type disk
44. format '${LOC_PREFIX}1/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
45. maxpiecesize ${MAXPIECESIZE};
46. allocate channel c2 type disk
47. format '${LOC_PREFIX}2/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
48. maxpiecesize ${MAXPIECESIZE};
49. allocate channel c3 type disk
50. format '${LOC_PREFIX}3/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
51. maxpiecesize ${MAXPIECESIZE};
52. allocate channel c4 type disk
53. format '${LOC_PREFIX}4/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
54. maxpiecesize ${MAXPIECESIZE};
55. allocate channel c5 type disk
56. format '${LOC_PREFIX}5/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
57. maxpiecesize ${MAXPIECESIZE};
58. allocate channel c6 type disk
59. format '${LOC_PREFIX}6/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
60. maxpiecesize ${MAXPIECESIZE};
61. allocate channel c7 type disk
62. format '${LOC_PREFIX}7/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
63. maxpiecesize ${MAXPIECESIZE};
64. allocate channel c8 type disk
65. format '${LOC_PREFIX}8/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
66. maxpiecesize ${MAXPIECESIZE};
67. backup
68. incremental level 0
69. tag = 'LVL0_DB_BKP'
70. database
71. include current controlfile;
72. release channel c1;
73. release channel c2;
74. release channel c3;
75. release channel c4;
76. release channel c5;
77. release channel c6;
78. release channel c7;
79. release channel c8;
80. allocate channel d2 type disk format
81. '${LOC_PREFIX}8/CTLBKP_${ORACLE_SID}_${TIMESTAMP}.CTL';
82. backup current controlfile;
83. release channel d2;
84. }
85. exit
86. EOF
87. RC=$?
88. cat $TMPLOG >> $LOG
89. rm $LOCKFILE
90. echo `date` "Script lock file removed" >> $LOG
91. if [ $RC -ne "0" ]; then
92. mailx -s "RMAN $BACKUP_TYPE $ORACLE_SID $BACKUP_MEDIA Failed" \
93. $DBAEMAIL,$DBAPAGER < $LOG
94. else
95. cp $LOG ${LOC_PREFIX}1
96. mailx -s "RMAN $BACKUP_TYPE $ORACLE_SID $BACKUP_MEDIA Successful" \
97. $DBAEMAIL < $LOG
98. fi
99. scp $LOG \
100. ${LOG_USER}@${LOG_SERVER}:${LOG_DIR}/${ORACLE_SID}/.
101. rm $TMPLOG
102. fi
3 The Oracle Home for that database. Change for another database.
4 The SID of the database being backed up.
5 The location on the server where this script is executed.
6 The media where the backup is stored, such as tape or disk. This parameter is only
for naming the log file, not for directing the target of the backup.
7 The type of backup, such as full or incremental. This is only for naming the log file.
This parameter does not actually cause the backup to be full or otherwise.
8 The MAXPIECESIZE parameter for RMAN. This parameter in RMAN creates the
backup pieces to be limited to a certain size, which is a limitation on some
operating systems. The limit should be based on the database size as well. If your
database is fairly small and you want to remove any limit, just specify a very high
number. In this example, we have assumed a 16GB limit.
11 The backups will be made to /oraback/loc1 through /oraback/loc8.
12 The email that says where the successful notification should be sent.
13 The email that says where the failure email should be sent, usually a pager.
14 The server where the log files of each run are stored.
15 The user ID of the log server.
16 The directory where the logs are kept on the central log server.
17 The connection string for the catalog connection. Here we assume that your catalog
database connect string is catalog and you have defined a separate catalog owner
for each database, where the owner’s name is the same as the SID of the database
being backed up and the password is the same as the owner name. This is not
absolutely necessary; you can have a common owner for catalogs of all databases.
Whatever your decision is, update this parameter to reflect that.
19 The mount points where the backups will be taken have a common format, such as
/oraback/loc, where varies from 1 to 8. The format is mentioned here.
20 The directory where the temporary file log file of the script is generated. Later this
temp file and the RMAN log file are merged and sent out as the log file.
21 The date format that the time stamps in the RMAN log files are shown as.
22 The time stamp; the log files are generated in this name.
23–25 Various path variables that need to be there. Remember, this script is called from a
cron job, so the user’s profile is not executed, and no variables are set.
26 The log file name is constructed.
27 The temporary log file is created in this name. The parameter $$ indicates the PID in
the shell script. Since the PID of each process is different, a different log file will be
created each time.
31 Since we want to prevent the script from starting if it is running currently, we’re
using a lock file. At the beginning of each run, the script checks the lock file. If it is
present, it indicates the script is running now, and the current run is aborted. At the
end of the run, the script deletes the lock file.
32 We check whether the lock file exists. If it does, then the script is running, so we
abort this run.
35 If the lock file does not exist, we create one. The contents of the file do not matter,
but we put the lines “Do NOT delete this file. Used for RMAN locking” in the file, just
in case someone gets curious and opens this file. The message should be crystal clear.
36 We start the RMAN command. The << EOF clause at the end of the line indicates that
the RMAN executable should accept all the lines until the string EOF is encountered.
37 We connect to the target database.
38 We connect to the catalog.
39 When RMAN starts backing up the database, it must get an exclusive lock on the
control file. Since that creates the disruption of the database, RMAN takes a
snapshot of the control file and uses that. Here, in this line, we decide the snapshot
control file location.
43–45 We allocate the first channel, specifying the format string so that the backups go
there. We also specify MAXPIECESIZE, which determines how big each piece should
be. Note the format string:
${LOC_PREFIX}1/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman
The location of the file will be constructed as /oraback/loc1, a mount point.
46–66 We do the same for channels 2 through 8. They go to the mount points /oraback/
loc2 through /oraback/loc8.
67–71 The actual backup command comes here. You can specify any RMAN command you
want here.
72–79 The channels are explicitly released, a best practice.
80–83 We take an explicit backup of the current control file. Note that in line 69 we have
included the control file as part of the backup, but the control file gets embedded in
the backup pieces. If you have the catalog, it is simple to get the control file from the
backup pieces. But imagine the worst-case scenario where the catalog is lost and so
is the control file. It will be hard to locate the control file from the many backup
piece files. Therefore, as a good practice, we take an explicit backup of the control
file, which has a clearly identified name.
87 After we exit the RMAN command line, we capture the return code, $?.
88 We merge the RMAN log with the script log file.
89 We remove the lock file created earlier to indicate that the script has completed its
run and a new script run may be started.
87 We check the status of the RMAN execution. 0 indicates successful execution.
91 If the script fails because of any reason, the return code will not be 0. The exact
return code is immaterial; the cause of the error will be captured in the RMAN log
file. The error is notified to the DBA’s pager. The log file is sent to the pager and the
DBA’s email.
95 If the RMAN execution was successful, we copy the log file to one of the locations
where the backups are generated. The tape backup software will pick it up from that
location.
99–100 The log file is also copied to the central log server.
101 The temporary log file is removed.
CRONTAB
10 0 * * 0,1,3,5 /u02/backup/backup_database.sh
1. Issue the following Unix command:
$ crontab –e
This opens your crontab file in the vi editor. If you don’t have any entry yet in crontab,
you will see an empty file. Place whatever line you want in the file. Be sure to adhere to
the format described in Table 9-2 later in this chapter.
2. Save the file and exit. The line is now scheduled in crontab.
3. Check cron for all scheduled programs:
$ crontab –l
SCRIPT WINDOWS
1. @ECHO OFF
2. :: Beginning of Script
3. :: Start of Configurable Section
4. set ORACLE_HOME=C:\oracle\product\10.2\db_1
5. set ORACLE_SID=MOBDB10
6. set TOOLHOME=C:\TOOLS
7. set BACKUP_MEDIA=DISK
8. set BACKUP_TYPE=FULL_DB_BKUP
9. set MAXPIECESIZE=16G
10. set BACKUP_MOUNTPOINT=c:\oracle\flash
11. set DBAEMAIL="dbas@proligence.com"
12. set DBAPAGER="dba.ops@proligence.com"
13. set CATALOG_CONN=%ORACLE_SID%/%ORACLE_SID%@catalog
14. set MS=mail.proligence.com
15. ::
16. :: end of Configurable Section
17. ::
18. set BACKUP_LOC_PREFIX=%BACKUP_MOUNTPOINT%\loc
19. set TMPDIR=C:\temp
20. set NLS_DATE_FORMAT="MM/DD/YY HH24:MI:SS"
21. realdate /d /s="set curdate=" > %TOOLHOME%\tmp_dt.bat
22. realdate /t /s="set curtime=" > %TOOLHOME%\tmp_tm.bat
23. call %TOOLHOME%\tmp_dt.bat
24. call %TOOLHOME%\tmp_tm.bat
25. ::
26. ::
27. set LOG=%TOOLHOME%\%ORACLE_SID%_%BACKUP_TYPE%_%BACKUP_MEDIA% ➥_
%CURDATE%_%CURTIME%.log
28. set TMPLOG=%TOOLHOME%\tmplog.$$
29. ::
30. :: Build the Command File
31. set FORMATSTRING=%BACKUP_LOC_PREFIX%1\%ORACLE_SID%_%%u_%%p.rman
32. set CMDFILE=%TOOLHOME%\%ORACLE_SID%.rman
33. echo run { > %CMDFILE%
34. echo allocate channel c1 type disk >> %CMDFILE%
35. echo format '%FORMATSTRING%' >> %CMDFILE%
36. echo maxpiecesize %MAXPIECESIZE%; >> %CMDFILE%
37. echo backup >> %CMDFILE%
38. echo tablespace users; >> %CMDFILE%
39. echo release channel c1; >> %CMDFILE%
40. echo } >> %CMDFILE%
41. :: End of Command File Generation
42. ::
43. echo Starting the script > %LOG%
44. %ORACLE_HOME%\bin\rman target=/ catalog=%CATALOG_CONN% @%CMDFILE% ➥
msglog=%TMPLOG%
45. ::
46. :: Merge the Logfiles
47. type %TMPLOG% >> %LOG%
48. :: Check for errors
49. ::
50. echo THE OUTPUT WAS %ERRORLEVEL% >> %LOG%
51. findstr /i "error" %LOG%
52. if errorlevel 0 if not errorlevel 1 bmail -s %MS% -t %DBAPAGER% ➥
-f "Database" -m %LOG%
53. @echo on
1 This line instructs the batch program executer to stop displaying the commands in the
file; just execute them.
4 We set the Oracle Home.
5 We set the Oracle SID.
6 We set the location of this batch file.
7 We specify the type of the backup, such as disk, tape, and so on. Please note that
specifying a type here merely places the type in the name of the log file; it does not
impact the type of the backup created by this batch file. The RMAN backup commands
in the batch file determine the nature of the backup created.
8 We specify the type of backup, such as full or incremental, so that it becomes part of
the name of the log file.
9 The MAXPIECESIZE for the backup is specified here.
10 The variables that hold the location of the backup.
11–12 The email addresses where an email will be sent.
13 The catalog connection string. In this script, we have assumed that the rman repository
username is the ORACLE_SID and the password is the same as the username.
14 The mail server name. You can ask your email administrator for this. In many small and
medium organizations, this may be mail.organization.com.
21 We want to create a log file whose name should have the current date and time. The
standard Windows date command does not easily yield a usable form of the date to be
used in the log file, as is the case with the time component. Here we have used a special
program called realdate. More information about realdate is provided following the
table.
In this line, we have extracted the current date and issued the command to set a
variable curdate to hold the current date. For instance, if this program is executed on
February 1, 2007, the command realdate /d /s="set curdate=" returns set
curdate=20070201. This line is placed in the file tmp_dt.bat.
22 We again use realdate to extract the current time. For instance, if the program is
executed at 11:15:53 p.m., the command realdate /t /s="set curtime=" yields
set curtime=231553. This line places that string in the file tmp_tm.bat.
23–24 We execute the batch files we generated in the previous two lines. These set the
variables curdate and curtime.
27 We set the name of the log file.
28 We create a temporary log file to hold the output of the RMAN commands.
31 We create a variable called FORMATSTRING for the name of the backup piece.
32 We create a variable called CMDFILE to hold the name of the command file that will be
passed to RMAN.
33–40 We put all the RMAN commands to be executed later in the command file.
44 We call the RMAN to execute the command file created dynamically in lines 33–40. The
output goes to the log file named in line 28.
47 Now that we have the output of the RMAN output, we place the contents of that RMAN
log file to the main log file we have been using.
50 We place the result of the RMAN run, as captured in the variable ERRORLEVEL. If the
RMAN run was successful, this variable will be 0. The result will be in the log file.
51 If there is any error, the log file will contain that error. This line shows how to use the
findstr command to find out whether the log file contains the word error in either
uppercase or lowercase.
52 If the error was found, the errorlevel variable will be nonzero, and we want to email
the log file to the email address specified in the variable DBAPAGER. To send the email,
we have used a program called bmail, which is described next.
Script Windows
RMAN> run {
2> allocate channel c1 type disk
3> format 'c:\oracle\flash\loc1\MOBDB10_%u_%p.rman'
4> maxpiecesize 16G;
5> backup
6> tablespace users;
7> release channel c1;
8> }
You can also use the cmdfile option with an equal sign:
$ rman target=/ catalog=u/p@catalog cmdfile=cmd.rman
You can use the SQL*Plus-like notation to call a script by placing an @ before the name.
For example:
$ rman target=/ catalog=u/p@catalog @cmd.rman
SCRIPT UNIX
1. # Beginning of Script
2. # Start of Configurable Section
3. export ORACLE_HOME=/opt/oracle/10.2/db_1
4. export ORACLE_SID=PRODB1
5. export TOOLHOME=/opt/oracle/tools
6. export BACKUP_MEDIA=DISK
7. export BACKUP_TYPE=FULL_DB_BKUP
8. export MAXPIECESIZE=16G
9. # End of Configurable Section
10. # Start of site specific parameters
11. export BACKUP_MOUNTPOINT=/oraback
12. export DBAEMAIL="dbas@proligence.com"
13. export DBAPAGER="dba.ops@proligence.com"
14. export LOG_SERVER=prolin2
15. export LOG_USER=oracle
16. export LOG_DIR=/dbalogs
17. export CATALOG_CONN=${ORACLE_SID}/${ORACLE_SID}@catalog
18. # End of site specific parameters
19. export LOC_PREFIX=$BACKUP_MOUNTPOINT/loc
20. export TMPDIR=/tmp
21. export NLS_DATE_FORMAT="MM/DD/YY HH24:MI:SS"
22. export TIMESTAMP=`date +%T-%m-%d-%Y`
23. export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
24. export LIBPATH=$ORACLE_HOME/lib:/usr/lib:/lib
25. export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib:/lib
26. export LOG=${TOOLHOME}/log
27. LOG=${LOG}/log/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}_${TIMESTAMP}.log
28. export TMPLOG=$TOOLHOME/log/tmplog.$$
29. echo `date` "Starting $BACKUP_TYPE Backup of $ORACLE_SID \
30. to $BACKUP_MEDIA" > $LOG
31. export LOCKFILE=$TOOLHOME/${ORACLE_SID}_${BACKUP_TYPE}_${BACKUP_MEDIA}.lock
32. if [ -f $LOCKFILE ]; then
33. echo `date` "Script running. Exiting ..." >> $LOG
34. else
35. echo "Do NOT delete this file. Used for RMAN locking" > $LOCKFILE
36. $ORACLE_HOME/bin/rman log=$TMPLOG <
38. connect catalog $CATALOG_CONN
39. CONFIGURE SNAPSHOT CONTROLFILE NAME TO
40. '${ORACLE_HOME}/dbs/SNAPSHOT_${ORACLE_SID}_${TIMESTAMP}_CTL';
41. run
42. {
43. allocate channel c1 type disk
44. format '${LOC_PREFIX}1/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
45. maxpiecesize ${MAXPIECESIZE};
46. allocate channel c2 type disk
47. format '${LOC_PREFIX}2/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
48. maxpiecesize ${MAXPIECESIZE};
49. allocate channel c3 type disk
50. format '${LOC_PREFIX}3/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
51. maxpiecesize ${MAXPIECESIZE};
52. allocate channel c4 type disk
53. format '${LOC_PREFIX}4/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
54. maxpiecesize ${MAXPIECESIZE};
55. allocate channel c5 type disk
56. format '${LOC_PREFIX}5/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
57. maxpiecesize ${MAXPIECESIZE};
58. allocate channel c6 type disk
59. format '${LOC_PREFIX}6/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
60. maxpiecesize ${MAXPIECESIZE};
61. allocate channel c7 type disk
62. format '${LOC_PREFIX}7/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
63. maxpiecesize ${MAXPIECESIZE};
64. allocate channel c8 type disk
65. format '${LOC_PREFIX}8/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman'
66. maxpiecesize ${MAXPIECESIZE};
67. backup
68. incremental level 0
69. tag = 'LVL0_DB_BKP'
70. database
71. include current controlfile;
72. release channel c1;
73. release channel c2;
74. release channel c3;
75. release channel c4;
76. release channel c5;
77. release channel c6;
78. release channel c7;
79. release channel c8;
80. allocate channel d2 type disk format
81. '${LOC_PREFIX}8/CTLBKP_${ORACLE_SID}_${TIMESTAMP}.CTL';
82. backup current controlfile;
83. release channel d2;
84. }
85. exit
86. EOF
87. RC=$?
88. cat $TMPLOG >> $LOG
89. rm $LOCKFILE
90. echo `date` "Script lock file removed" >> $LOG
91. if [ $RC -ne "0" ]; then
92. mailx -s "RMAN $BACKUP_TYPE $ORACLE_SID $BACKUP_MEDIA Failed" \
93. $DBAEMAIL,$DBAPAGER < $LOG
94. else
95. cp $LOG ${LOC_PREFIX}1
96. mailx -s "RMAN $BACKUP_TYPE $ORACLE_SID $BACKUP_MEDIA Successful" \
97. $DBAEMAIL < $LOG
98. fi
99. scp $LOG \
100. ${LOG_USER}@${LOG_SERVER}:${LOG_DIR}/${ORACLE_SID}/.
101. rm $TMPLOG
102. fi
3 The Oracle Home for that database. Change for another database.
4 The SID of the database being backed up.
5 The location on the server where this script is executed.
6 The media where the backup is stored, such as tape or disk. This parameter is only
for naming the log file, not for directing the target of the backup.
7 The type of backup, such as full or incremental. This is only for naming the log file.
This parameter does not actually cause the backup to be full or otherwise.
8 The MAXPIECESIZE parameter for RMAN. This parameter in RMAN creates the
backup pieces to be limited to a certain size, which is a limitation on some
operating systems. The limit should be based on the database size as well. If your
database is fairly small and you want to remove any limit, just specify a very high
number. In this example, we have assumed a 16GB limit.
11 The backups will be made to /oraback/loc1 through /oraback/loc8.
12 The email that says where the successful notification should be sent.
13 The email that says where the failure email should be sent, usually a pager.
14 The server where the log files of each run are stored.
15 The user ID of the log server.
16 The directory where the logs are kept on the central log server.
17 The connection string for the catalog connection. Here we assume that your catalog
database connect string is catalog and you have defined a separate catalog owner
for each database, where the owner’s name is the same as the SID of the database
being backed up and the password is the same as the owner name. This is not
absolutely necessary; you can have a common owner for catalogs of all databases.
Whatever your decision is, update this parameter to reflect that.
19 The mount points where the backups will be taken have a common format, such as
/oraback/loc
20 The directory where the temporary file log file of the script is generated. Later this
temp file and the RMAN log file are merged and sent out as the log file.
21 The date format that the time stamps in the RMAN log files are shown as.
22 The time stamp; the log files are generated in this name.
23–25 Various path variables that need to be there. Remember, this script is called from a
cron job, so the user’s profile is not executed, and no variables are set.
26 The log file name is constructed.
27 The temporary log file is created in this name. The parameter $$ indicates the PID in
the shell script. Since the PID of each process is different, a different log file will be
created each time.
31 Since we want to prevent the script from starting if it is running currently, we’re
using a lock file. At the beginning of each run, the script checks the lock file. If it is
present, it indicates the script is running now, and the current run is aborted. At the
end of the run, the script deletes the lock file.
32 We check whether the lock file exists. If it does, then the script is running, so we
abort this run.
35 If the lock file does not exist, we create one. The contents of the file do not matter,
but we put the lines “Do NOT delete this file. Used for RMAN locking” in the file, just
in case someone gets curious and opens this file. The message should be crystal clear.
36 We start the RMAN command. The << EOF clause at the end of the line indicates that
the RMAN executable should accept all the lines until the string EOF is encountered.
37 We connect to the target database.
38 We connect to the catalog.
39 When RMAN starts backing up the database, it must get an exclusive lock on the
control file. Since that creates the disruption of the database, RMAN takes a
snapshot of the control file and uses that. Here, in this line, we decide the snapshot
control file location.
43–45 We allocate the first channel, specifying the format string so that the backups go
there. We also specify MAXPIECESIZE, which determines how big each piece should
be. Note the format string:
${LOC_PREFIX}1/${ORACLE_SID}_${BACKUP_TYPE}_${TIMESTAMP}_%p_%s.rman
The location of the file will be constructed as /oraback/loc1, a mount point.
46–66 We do the same for channels 2 through 8. They go to the mount points /oraback/
loc2 through /oraback/loc8.
67–71 The actual backup command comes here. You can specify any RMAN command you
want here.
72–79 The channels are explicitly released, a best practice.
80–83 We take an explicit backup of the current control file. Note that in line 69 we have
included the control file as part of the backup, but the control file gets embedded in
the backup pieces. If you have the catalog, it is simple to get the control file from the
backup pieces. But imagine the worst-case scenario where the catalog is lost and so
is the control file. It will be hard to locate the control file from the many backup
piece files. Therefore, as a good practice, we take an explicit backup of the control
file, which has a clearly identified name.
87 After we exit the RMAN command line, we capture the return code, $?.
88 We merge the RMAN log with the script log file.
89 We remove the lock file created earlier to indicate that the script has completed its
run and a new script run may be started.
87 We check the status of the RMAN execution. 0 indicates successful execution.
91 If the script fails because of any reason, the return code will not be 0. The exact
return code is immaterial; the cause of the error will be captured in the RMAN log
file. The error is notified to the DBA’s pager. The log file is sent to the pager and the
DBA’s email.
95 If the RMAN execution was successful, we copy the log file to one of the locations
where the backups are generated. The tape backup software will pick it up from that
location.
99–100 The log file is also copied to the central log server.
101 The temporary log file is removed.
CRONTAB
10 0 * * 0,1,3,5 /u02/backup/backup_database.sh
1. Issue the following Unix command:
$ crontab –e
This opens your crontab file in the vi editor. If you don’t have any entry yet in crontab,
you will see an empty file. Place whatever line you want in the file. Be sure to adhere to
the format described in Table 9-2 later in this chapter.
2. Save the file and exit. The line is now scheduled in crontab.
3. Check cron for all scheduled programs:
$ crontab –l
SCRIPT WINDOWS
1. @ECHO OFF
2. :: Beginning of Script
3. :: Start of Configurable Section
4. set ORACLE_HOME=C:\oracle\product\10.2\db_1
5. set ORACLE_SID=MOBDB10
6. set TOOLHOME=C:\TOOLS
7. set BACKUP_MEDIA=DISK
8. set BACKUP_TYPE=FULL_DB_BKUP
9. set MAXPIECESIZE=16G
10. set BACKUP_MOUNTPOINT=c:\oracle\flash
11. set DBAEMAIL="dbas@proligence.com"
12. set DBAPAGER="dba.ops@proligence.com"
13. set CATALOG_CONN=%ORACLE_SID%/%ORACLE_SID%@catalog
14. set MS=mail.proligence.com
15. ::
16. :: end of Configurable Section
17. ::
18. set BACKUP_LOC_PREFIX=%BACKUP_MOUNTPOINT%\loc
19. set TMPDIR=C:\temp
20. set NLS_DATE_FORMAT="MM/DD/YY HH24:MI:SS"
21. realdate /d /s="set curdate=" > %TOOLHOME%\tmp_dt.bat
22. realdate /t /s="set curtime=" > %TOOLHOME%\tmp_tm.bat
23. call %TOOLHOME%\tmp_dt.bat
24. call %TOOLHOME%\tmp_tm.bat
25. ::
26. ::
27. set LOG=%TOOLHOME%\%ORACLE_SID%_%BACKUP_TYPE%_%BACKUP_MEDIA% ➥_
%CURDATE%_%CURTIME%.log
28. set TMPLOG=%TOOLHOME%\tmplog.$$
29. ::
30. :: Build the Command File
31. set FORMATSTRING=%BACKUP_LOC_PREFIX%1\%ORACLE_SID%_%%u_%%p.rman
32. set CMDFILE=%TOOLHOME%\%ORACLE_SID%.rman
33. echo run { > %CMDFILE%
34. echo allocate channel c1 type disk >> %CMDFILE%
35. echo format '%FORMATSTRING%' >> %CMDFILE%
36. echo maxpiecesize %MAXPIECESIZE%; >> %CMDFILE%
37. echo backup >> %CMDFILE%
38. echo tablespace users; >> %CMDFILE%
39. echo release channel c1; >> %CMDFILE%
40. echo } >> %CMDFILE%
41. :: End of Command File Generation
42. ::
43. echo Starting the script > %LOG%
44. %ORACLE_HOME%\bin\rman target=/ catalog=%CATALOG_CONN% @%CMDFILE% ➥
msglog=%TMPLOG%
45. ::
46. :: Merge the Logfiles
47. type %TMPLOG% >> %LOG%
48. :: Check for errors
49. ::
50. echo THE OUTPUT WAS %ERRORLEVEL% >> %LOG%
51. findstr /i "error" %LOG%
52. if errorlevel 0 if not errorlevel 1 bmail -s %MS% -t %DBAPAGER% ➥
-f "Database" -m %LOG%
53. @echo on
1 This line instructs the batch program executer to stop displaying the commands in the
file; just execute them.
4 We set the Oracle Home.
5 We set the Oracle SID.
6 We set the location of this batch file.
7 We specify the type of the backup, such as disk, tape, and so on. Please note that
specifying a type here merely places the type in the name of the log file; it does not
impact the type of the backup created by this batch file. The RMAN backup commands
in the batch file determine the nature of the backup created.
8 We specify the type of backup, such as full or incremental, so that it becomes part of
the name of the log file.
9 The MAXPIECESIZE for the backup is specified here.
10 The variables that hold the location of the backup.
11–12 The email addresses where an email will be sent.
13 The catalog connection string. In this script, we have assumed that the rman repository
username is the ORACLE_SID and the password is the same as the username.
14 The mail server name. You can ask your email administrator for this. In many small and
medium organizations, this may be mail.organization.com.
21 We want to create a log file whose name should have the current date and time. The
standard Windows date command does not easily yield a usable form of the date to be
used in the log file, as is the case with the time component. Here we have used a special
program called realdate. More information about realdate is provided following the
table.
In this line, we have extracted the current date and issued the command to set a
variable curdate to hold the current date. For instance, if this program is executed on
February 1, 2007, the command realdate /d /s="set curdate=" returns set
curdate=20070201. This line is placed in the file tmp_dt.bat.
22 We again use realdate to extract the current time. For instance, if the program is
executed at 11:15:53 p.m., the command realdate /t /s="set curtime=" yields
set curtime=231553. This line places that string in the file tmp_tm.bat.
23–24 We execute the batch files we generated in the previous two lines. These set the
variables curdate and curtime.
27 We set the name of the log file.
28 We create a temporary log file to hold the output of the RMAN commands.
31 We create a variable called FORMATSTRING for the name of the backup piece.
32 We create a variable called CMDFILE to hold the name of the command file that will be
passed to RMAN.
33–40 We put all the RMAN commands to be executed later in the command file.
44 We call the RMAN to execute the command file created dynamically in lines 33–40. The
output goes to the log file named in line 28.
47 Now that we have the output of the RMAN output, we place the contents of that RMAN
log file to the main log file we have been using.
50 We place the result of the RMAN run, as captured in the variable ERRORLEVEL. If the
RMAN run was successful, this variable will be 0. The result will be in the log file.
51 If there is any error, the log file will contain that error. This line shows how to use the
findstr command to find out whether the log file contains the word error in either
uppercase or lowercase.
52 If the error was found, the errorlevel variable will be nonzero, and we want to email
the log file to the email address specified in the variable DBAPAGER. To send the email,
we have used a program called bmail, which is described next.
Script Windows
RMAN> run {
2> allocate channel c1 type disk
3> format 'c:\oracle\flash\loc1\MOBDB10_%u_%p.rman'
4> maxpiecesize 16G;
5> backup
6> tablespace users;
7> release channel c1;
8> }
martes, 22 de septiembre de 2009
RMAN Maintaining RMAN Backups Repository
Adding User-Made Backups to the Repository
Problem
You’ve made some datafile copies on disk, which you want to add to the RMAN repository.
Solution
You can add any user-managed copies, such as a datafile copy (that you made with an operating
system utility), to the RMAN repository using the catalog command. Here’s a basic
example:
RMAN> catalog datafilecopy '/u01/app/oracl/example1.bkp';
The preceding catalog command catalogs the datafile copy you made of the example01.dbf
datafile as an RMAN-recognized backup. You can, if you want, catalog the datafile copy as an
incremental level 0 backup by issuing the following command:
RMAN> catalog datafilecopy '/u01/app/oracle/example01.bkp' level 0;
RMAN> catalog device type sbt backuppiece 'ilif2lo4_1_1';
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=38 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: WARNING: Oracle Test Disk API
cataloged backuppiece
backup piece handle=ilif2lo4_1_1 recid=3878 stamp=619796430
RMAN>
You can check that the backup piece has been cataloged successfully by issuing the list
command again, as shown here:
RMAN> list backuppiece 'ilif2lo4_1_1';
If you have to catalog multiple files that you had backed up to a directory, use the catalog
start with command, as shown in the following example:
RMAN> catalog start with '/u01/app/oracle/backup' noprompt;
RMAN> catalog recovery area;
Finding Datafiles and Archivelogs That Need a Backup
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File bkps Name
---- ------ ---------------------------------------------------
1 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSTEM01.DBF
2 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\UNDOTBS01.DBF
3 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSAUX01.DBF
4 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\USERS01.DBF
5 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\EXAMPLE01.DBF
This command shows objects that require a backup to conform to a redundancy-based
retention policy:
RMAN> report need backup redundancy n;
This command shows objects that require a backup to conform to a window-based retention
policy:
RMAN> report need backup recovery window of n days
This command shows datafiles that require more than n days worth of archived redo logs
for a recovery:
RMAN> report need backup days=n;
This command shows only the required backups on disk:
RMAN> report need backup device type disk;
This command shows only required backups on tape:
RMAN> report need backup device type sbt;
Finding Datafiles Affected by Unrecoverable Operations
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
----- --------------- ----------------------------------------
1 full /u01/app/oracle/data/prod1/example01.dbf
Identifying Obsolete Backups
Solution
The report obsolete command reports on any obsolete backups. Always run the crosscheck
command first in order to update the status of the backups in the RMAN repository to that on
disk and tape. In the following example, the report obsolete command shows no
obsolete
backups:
RMAN> crosscheck backup;
RMAN> report obsolete;
When using the report obsolete command, you can also specify the redundancy and
recover window options, as shown here:
RMAN> report obsolete recovery window of 5 days;
RMAN> report obsolete redundancy 2;
RMAN> report obsolete recovery window of 5 days device type disk;
Displaying Information About Database Files
RMAN> report schema;
RMAN> report schema at time 'sysdate-1';
Listing RMAN Backups
RMAN> list backup;
RMAN> list backup by file;
RMAN> list backup summary; # lists backup sets, proxy copies, and image copies
RMAN> list expired backup summary; # lists expired backups in summary form
This command lists only backup sets and proxy copies but not image copies:
RMAN> list backupset;
This command lists only datafile, archived redo log, and control file copies:
RMAN> list copy;
This command lists a particular datafile copy:
RMAN> list datafilecopy '/a01/app/oracle/users01.dbf';
This command lists backups by tag:
RMAN> list backupset tag 'weekly_full_db_backup';
This command lists backups according to when the backup was made:
RMAN> list copy of datafile 1 completed between '01-JAN-2007' AND '15-JAN-2007';
This command lists the backup by the number of times they were backed up to tape:
RMAN> list archivelog all backed up 2 times to device type sbt;
This command lists the backups of all datafiles and archivelogs of the target database:
RMAN> list backup of database;
list incarnation: Lists all incarnations of a database
list restore point: Lists all restore points
list script names:
list failure:
Listing Expired Backups
RMAN> list expired backup;
RMAN> list expired archivelog all;
Listing Only Recoverable Backups and Copies
RMAN> list recoverable backup;
Listing Restore Points
RMAN> list restore point all;
Listing Database Incarnations
RMAN> list incarnation;
Updating the RMAN Repository After Manually Deleting Backups
RMAN> change datafilecopy '/u01/app/oracle/users01.dbf' uncatalog;
Here’s another example showing how to uncatalog a specific backup piece:
RMAN> change backuppiece 'ilif2lo4_1_1' uncatalog;
uncataloged backuppiece
backup piece handle=ilif2lo4_1_1 recid=3876 stamp=619796229
Uncataloged 1 objects
Synchronizing the Repository with the Actual Backups
RMAN> delete backup;
RMAN> crosscheck backup;
RMAN> crosscheck backup;
RMAN> delete expired backup;
RMAN> crosscheck backupset of tablespace users
device type sbt completed before 'sysdate-14';
RMAN> delete expired backupset of tablespace users
device type sbt completed before 'sysdate-14';
# cross-checking just backup sets.
RMAN> crosscheck backupset;
# cross-checking a copy of a database
RMAN> crosscheck copy of database;
# cross-checking specific backupsets;
RMAN> crosscheck backupset 1001, 1002;
# cross-checking using a backup tag
RMAN> crosscheck backuppiece tag = 'weekly_backup';
# cross-checking a control file copy;
RMAN> crosscheck controlfilecopy '/tmp/control01.ctl';
# cross-checking backups completed after a specific time
RMAN> crosscheck backup of datafile "/u01/app/oracle/prod1/system01.dbf" completed
after 'sysdate-14';
# cross-checking of all archivelogs and the spfile;
RMAN> crosscheck backup of archivelog all spfile;
# cross-checking a proxy copy
RMAN> crosscheck proxy 999;
Use the completed after clause to restrict the crosscheck command to check only those
backups that were created after a specific point in time. The following command will check
only for backups of a datafile made in the last week:
RMAN> crosscheck backup of datafile 2
completed after 'sysdate -7';
Deleting Backups
RMAN> delete backup;
RMAN> delete backuppiece 999;
RMAN> delete copy of controlfile like '/u01/%';
RMAN> delete backup tag='old_production';
RMAN> delete backup of tablespace sysaux device type sbt;
RMAN> crosscheck backup;
RMAN> crosscheck copy;
RMAN> delete backup;
RMAN> delete copy;
Deleting Archived Redo Logs
RMAN> delete archivelog all;
The delete archivelog all command deletes all archived redo logs on disk that aren’t
necessary to meet the configured archived redo log deletion policy. It’s more likely that you’d want to use the following delete command, which deletes archived redo logs from disk based on whether they have been first backed up to tape a certain number of times:
RMAN> delete archivelog all
backed up 3 times to sbt;
RMAN> delete archivelog until sequence = 999;
RMAN> backup device type sbt archivelog all delete all input;
RMAN> backup archivelog like '/arch%' delete input;
RMAN> configure archivelog deletion policy to backed up 2 times to device type sbt;
Deleting Obsolete RMAN Backups
RMAN> delete obsolete;
RMAN> delete obsolete redundancy = 2;
The command shown here deletes backups that exceed the redundancy requirement of 2:
RMAN> delete obsolete recovery window of 14 days;
Changing the Status of an RMAN Backup Record
RMAN> change backupset 10 unavailable;
Changing the Status of Archival Backups
RMAN> change backup tag 'consistent_db_bkup'
keep forever;
Since this is a consistent backup, it wonít need any recovery, and as such, you wonít
need any archived redo log backups
RMAN> change backup tag 'consistent_db_backup' nokeep;
RMAN> change backupset 111 keep until time 'sysdate+180';
Testing the Integrity of an RMAN Backup
RMAN> backup validate database archivelog all;
RMAN> backup validate check logical database archivelog all;
Validating Datafiles,Backup Sets,and Data Blocks
You can validate datafiles, backup sets, or even individual data blocks by using the validate command. The following example shows how to validate a single backup set with the validate
command:
RMAN> validate backupset 7;
You can also use the validatecommand to check all datafiles at once, as shown here:
RMAN> validate database;
The validate command always skips all the data blocks that were never used, in each of the datafile it validates. The larger the value of the section sizeclause you set, the faster the validation process completes. You can use the validatecommandwith thefollowing options,
among others:
validate recovery area
validate recovery files
validate spfile
validate tablespace
validate controlfilecopy
validate backupset
Problem
You’ve made some datafile copies on disk, which you want to add to the RMAN repository.
Solution
You can add any user-managed copies, such as a datafile copy (that you made with an operating
system utility), to the RMAN repository using the catalog command. Here’s a basic
example:
RMAN> catalog datafilecopy '/u01/app/oracl/example1.bkp';
The preceding catalog command catalogs the datafile copy you made of the example01.dbf
datafile as an RMAN-recognized backup. You can, if you want, catalog the datafile copy as an
incremental level 0 backup by issuing the following command:
RMAN> catalog datafilecopy '/u01/app/oracle/example01.bkp' level 0;
RMAN> catalog device type sbt backuppiece 'ilif2lo4_1_1';
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=38 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: WARNING: Oracle Test Disk API
cataloged backuppiece
backup piece handle=ilif2lo4_1_1 recid=3878 stamp=619796430
RMAN>
You can check that the backup piece has been cataloged successfully by issuing the list
command again, as shown here:
RMAN> list backuppiece 'ilif2lo4_1_1';
If you have to catalog multiple files that you had backed up to a directory, use the catalog
start with command, as shown in the following example:
RMAN> catalog start with '/u01/app/oracle/backup' noprompt;
RMAN> catalog recovery area;
Finding Datafiles and Archivelogs That Need a Backup
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File bkps Name
---- ------ ---------------------------------------------------
1 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSTEM01.DBF
2 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\UNDOTBS01.DBF
3 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\SYSAUX01.DBF
4 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\USERS01.DBF
5 0 C:\ORACLE\PRODUCT\11.1.0\ORADATA\NICK\EXAMPLE01.DBF
This command shows objects that require a backup to conform to a redundancy-based
retention policy:
RMAN> report need backup redundancy n;
This command shows objects that require a backup to conform to a window-based retention
policy:
RMAN> report need backup recovery window of n days
This command shows datafiles that require more than n days worth of archived redo logs
for a recovery:
RMAN> report need backup days=n;
This command shows only the required backups on disk:
RMAN> report need backup device type disk;
This command shows only required backups on tape:
RMAN> report need backup device type sbt;
Finding Datafiles Affected by Unrecoverable Operations
RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
----- --------------- ----------------------------------------
1 full /u01/app/oracle/data/prod1/example01.dbf
Identifying Obsolete Backups
Solution
The report obsolete command reports on any obsolete backups. Always run the crosscheck
command first in order to update the status of the backups in the RMAN repository to that on
disk and tape. In the following example, the report obsolete command shows no
obsolete
backups:
RMAN> crosscheck backup;
RMAN> report obsolete;
When using the report obsolete command, you can also specify the redundancy and
recover window options, as shown here:
RMAN> report obsolete recovery window of 5 days;
RMAN> report obsolete redundancy 2;
RMAN> report obsolete recovery window of 5 days device type disk;
Displaying Information About Database Files
RMAN> report schema;
RMAN> report schema at time 'sysdate-1';
Listing RMAN Backups
RMAN> list backup;
RMAN> list backup by file;
RMAN> list backup summary; # lists backup sets, proxy copies, and image copies
RMAN> list expired backup summary; # lists expired backups in summary form
This command lists only backup sets and proxy copies but not image copies:
RMAN> list backupset;
This command lists only datafile, archived redo log, and control file copies:
RMAN> list copy;
This command lists a particular datafile copy:
RMAN> list datafilecopy '/a01/app/oracle/users01.dbf';
This command lists backups by tag:
RMAN> list backupset tag 'weekly_full_db_backup';
This command lists backups according to when the backup was made:
RMAN> list copy of datafile 1 completed between '01-JAN-2007' AND '15-JAN-2007';
This command lists the backup by the number of times they were backed up to tape:
RMAN> list archivelog all backed up 2 times to device type sbt;
This command lists the backups of all datafiles and archivelogs of the target database:
RMAN> list backup of database;
list incarnation: Lists all incarnations of a database
list restore point: Lists all restore points
list script names:
list failure:
Listing Expired Backups
RMAN> list expired backup;
RMAN> list expired archivelog all;
Listing Only Recoverable Backups and Copies
RMAN> list recoverable backup;
Listing Restore Points
RMAN> list restore point all;
Listing Database Incarnations
RMAN> list incarnation;
Updating the RMAN Repository After Manually Deleting Backups
RMAN> change datafilecopy '/u01/app/oracle/users01.dbf' uncatalog;
Here’s another example showing how to uncatalog a specific backup piece:
RMAN> change backuppiece 'ilif2lo4_1_1' uncatalog;
uncataloged backuppiece
backup piece handle=ilif2lo4_1_1 recid=3876 stamp=619796229
Uncataloged 1 objects
Synchronizing the Repository with the Actual Backups
RMAN> delete backup;
RMAN> crosscheck backup;
RMAN> crosscheck backup;
RMAN> delete expired backup;
RMAN> crosscheck backupset of tablespace users
device type sbt completed before 'sysdate-14';
RMAN> delete expired backupset of tablespace users
device type sbt completed before 'sysdate-14';
# cross-checking just backup sets.
RMAN> crosscheck backupset;
# cross-checking a copy of a database
RMAN> crosscheck copy of database;
# cross-checking specific backupsets;
RMAN> crosscheck backupset 1001, 1002;
# cross-checking using a backup tag
RMAN> crosscheck backuppiece tag = 'weekly_backup';
# cross-checking a control file copy;
RMAN> crosscheck controlfilecopy '/tmp/control01.ctl';
# cross-checking backups completed after a specific time
RMAN> crosscheck backup of datafile "/u01/app/oracle/prod1/system01.dbf" completed
after 'sysdate-14';
# cross-checking of all archivelogs and the spfile;
RMAN> crosscheck backup of archivelog all spfile;
# cross-checking a proxy copy
RMAN> crosscheck proxy 999;
Use the completed after clause to restrict the crosscheck command to check only those
backups that were created after a specific point in time. The following command will check
only for backups of a datafile made in the last week:
RMAN> crosscheck backup of datafile 2
completed after 'sysdate -7';
Deleting Backups
RMAN> delete backup;
RMAN> delete backuppiece 999;
RMAN> delete copy of controlfile like '/u01/%';
RMAN> delete backup tag='old_production';
RMAN> delete backup of tablespace sysaux device type sbt;
RMAN> crosscheck backup;
RMAN> crosscheck copy;
RMAN> delete backup;
RMAN> delete copy;
Deleting Archived Redo Logs
RMAN> delete archivelog all;
The delete archivelog all command deletes all archived redo logs on disk that aren’t
necessary to meet the configured archived redo log deletion policy. It’s more likely that you’d want to use the following delete command, which deletes archived redo logs from disk based on whether they have been first backed up to tape a certain number of times:
RMAN> delete archivelog all
backed up 3 times to sbt;
RMAN> delete archivelog until sequence = 999;
RMAN> backup device type sbt archivelog all delete all input;
RMAN> backup archivelog like '/arch%' delete input;
RMAN> configure archivelog deletion policy to backed up 2 times to device type sbt;
Deleting Obsolete RMAN Backups
RMAN> delete obsolete;
RMAN> delete obsolete redundancy = 2;
The command shown here deletes backups that exceed the redundancy requirement of 2:
RMAN> delete obsolete recovery window of 14 days;
Changing the Status of an RMAN Backup Record
RMAN> change backupset 10 unavailable;
Changing the Status of Archival Backups
RMAN> change backup tag 'consistent_db_bkup'
keep forever;
Since this is a consistent backup, it wonít need any recovery, and as such, you wonít
need any archived redo log backups
RMAN> change backup tag 'consistent_db_backup' nokeep;
RMAN> change backupset 111 keep until time 'sysdate+180';
Testing the Integrity of an RMAN Backup
RMAN> backup validate database archivelog all;
RMAN> backup validate check logical database archivelog all;
Validating Datafiles,Backup Sets,and Data Blocks
You can validate datafiles, backup sets, or even individual data blocks by using the validate command. The following example shows how to validate a single backup set with the validate
command:
RMAN> validate backupset 7;
You can also use the validatecommand to check all datafiles at once, as shown here:
RMAN> validate database;
The validate command always skips all the data blocks that were never used, in each of the datafile it validates. The larger the value of the section sizeclause you set, the faster the validation process completes. You can use the validatecommandwith thefollowing options,
among others:
validate recovery area
validate recovery files
validate spfile
validate tablespace
validate controlfilecopy
validate backupset
Solaris SUN Servicios Cluster
CLUSTER SPE
scstat| more
scswitch -z -g oracle-rg -h CT1BOSUNBD-SPE2
scswitch -z -g oracle-rg -h CT1BOSUNBD-SPE1
Disable monitors:
scswitch -n -M -j oracle-listener
scswitch -n -M -j oracle-server
Shutdwon resources:
scswitch -n -j oracle-listener
scswitch -n -j oracle-server
Validate:
cluster status
Start resources:
scswitch -e -j oracle-server
scswitch -e -j oracle-listener
Enable monitores:
scswitch -e -M -j oracle-listener
scswitch -e -M -j oracle-server
Activar Cluster
ere are the steps to solve this problem:
For example, the Sun Cluster contains two nodes: node0 and node1, the resource group name is test_group, the resource name of Oracle is test_oracle_resource
cd /u01/sc_log/sc.log
1. node0# scstat
Use scstat to check the cluster states, confirm the cluster
status is OK.
2. node0# scswitch -n -M -j test_oracle_resource
Use scswitch to stop the fault monitor of Oracle server temporary
3. node0# scrgadm -c -j test_oracle_resource -x
Connect_string=newuser/newpassword
This command will use new Oracle user and password to act as fault
monitor user.
4. node0# scswitch -e -M -j test_oracle_resource
Use scswitch enable the database fault monitor
5. node0# scrgadm -pvv | grep Connect_string
To confirm the modification become effective.
The above steps do not need shutdown Oracle instance.
scstat| more
scswitch -z -g oracle-rg -h CT1BOSUNBD-SPE2
scswitch -z -g oracle-rg -h CT1BOSUNBD-SPE1
Disable monitors:
scswitch -n -M -j oracle-listener
scswitch -n -M -j oracle-server
Shutdwon resources:
scswitch -n -j oracle-listener
scswitch -n -j oracle-server
Validate:
cluster status
Start resources:
scswitch -e -j oracle-server
scswitch -e -j oracle-listener
Enable monitores:
scswitch -e -M -j oracle-listener
scswitch -e -M -j oracle-server
Activar Cluster
ere are the steps to solve this problem:
For example, the Sun Cluster contains two nodes: node0 and node1, the resource group name is test_group, the resource name of Oracle is test_oracle_resource
cd /u01/sc_log/sc.log
1. node0# scstat
Use scstat to check the cluster states, confirm the cluster
status is OK.
2. node0# scswitch -n -M -j test_oracle_resource
Use scswitch to stop the fault monitor of Oracle server temporary
3. node0# scrgadm -c -j test_oracle_resource -x
Connect_string=newuser/newpassword
This command will use new Oracle user and password to act as fault
monitor user.
4. node0# scswitch -e -M -j test_oracle_resource
Use scswitch enable the database fault monitor
5. node0# scrgadm -pvv | grep Connect_string
To confirm the modification become effective.
The above steps do not need shutdown Oracle instance.
Suscribirse a:
Entradas (Atom)
