martes, 29 de septiembre de 2009

Installing the NetBackup Agent for Oracle

1. Make sure you have all the software for the installation:
•NetBackup license (you can’t install without one)
•NetBackup server software for your platform (the current version is 6.0)
•NetBackup agent for Oracle software

2. Prepare the necessary hardware for the installation:
•Master server
•Media server
•Tape library

3. Install the NetBackup software on the master server, the media servers, and the database
(client) server. Test the communication from the master server to the database
server by issuing the following command:

$ cd /usr/openv/netbackup/bin
$ ./bpclntcmd -pn
expecting response from server hounbupbs01-nbu
houcrspdb02-nbu.cce.star houcrspdb02-nbu 172.22.1.121 42546
$

The output shows that the master server (hounbupbs01-nbu) can be reached from the
client (houcrspdb02-nbu) where the database is running.


4. Check the master server and other parameters to make sure everything is correct:
$ cd /usr/openv/netbackup
$ ls
bin bp.conf client dbext ext help logs nblog.conf nblog.conf.template
$ cat bp.conf
SERVER = hounbupbs01-nbu
SERVER = hounbupbs01
MEDIA_SERVER = hounbupms01-nbu
SERVER = houjmp1w-nbu
SERVER = hounbupbs01-nbu.cce.starwoodhotels.com
MEDIA_SERVER = hounbupms02
CLIENT_NAME = houcrspdb02-nbu

5. Verify that the NetBackup daemons are running:
$ netstat -a | grep bpcd
tcp 0 0 *:bpcd *:* LISTEN
$ netstat -a | grep vnetd
tcp 0 0 *:vnetd *:* LISTEN


6. Verify that the licenses are all installed. Run the following command Grafic

$ /usr/openv/netbackup/bin/jnbSA

7. Insert the installation CD for NetBackup for Oracle, and mount the CD-ROM.
8. Log in as root on the database server.
9. Run the script ./install.
10. From the menu, choose the option NetBackup Database Agent.
11. When prompted, choose Y for local installation.
12. From the menu, choose NetBackup for Oracle.
13. Check the log files for any installation-related errors.
14. Now link Oracle to the NetBackup client. Log in to the database server box as the
Oracle software owner.
15. Shut down all the Oracle Databases running on the server under the Oracle Home.
16. Relink Oracle by running the script oracle_link:
$ cd /usr/openv/netbackup/bin
$ ./oracle_link
17. This creates an output in the file /tmp/make_trace.. Check the file for errors.

RMAN Troubleshooting

//VERITAS

The sbt function pointers are loaded from libobk.so library

$ORACLE_HOME/bin/sbttest /tmp/x.lst

Moving and/or Resizing the FRA

The following SQL statement uses the alter system command to move the flash recovery area to /orabackup02/FRA:

SQL> alter system set db_recovery_file_dest='/oraback02/FRA';

If disk space is available, you can increase the size of the flash recovery area to an appropriate value. This example changes the flash recovery area size to 100GB:

SQL> alter system set db_recovery_file_dest_size=100g;

Changing Retention Policy and Deleting Old Backups

RMAN> report obsolete;

This command will show the retention policy and which backups and archived redo log
files are obsolete. Here is what the output might look like:

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 6
no obsolete backups found

In this example, no obsolete backups were reported. We’ll use the configure command to
change the retention policy from a redundancy policy of six down to two:

RMAN> configure retention policy to redundancy 2;
Now the report obsolete command shows that there are several obsolete files:

RMAN> report obsolete;

Dealing with the RMAN-06059 Error

RMAN> backup database plus archivelog;

Your backup process doesn’t get very far when RMAN throws this error:

RMAN-03002: failure of backup command ...
RMAN-06059: expected archived log not found, loss of archived log compromises

Solution
You must update RMAN’s repository to reflect that archived redo log files either have been physically deleted or have been moved to another location on disk. Use the crosscheck command

RMAN> crosscheck archivelog all;


If the archived redo log files have been physically moved to a different location on disk, then use the catalog command to update the RMAN repository with the new location of the files:

RMAN> catalog start with '/oradump01/oldarchive';


RMAN to back up any
archived redo log files that have an AVAILABLE status in the V$ARCHIVED_LOG view. You can query the STATUS column of V$ARCHIVED_LOG as follows:

SQL> select sequence#,
2 decode(status,'A','available','D','deleted','U','unavailable','X','expired')
3 from v$archived_log;

Using SQL to Terminate an RMAN Channel

Use the alter system kill session SQL statement to terminate a hung RMAN job. To do this, you need to first identify the serial ID and serial number:

SQL> SELECT
2 s.sid
3 ,s.serial#
4 ,p.spid
5 ,s.client_info
6 FROM v$process p,
7 v$session s
8 WHERE p.addr = s.paddr
9 AND client_info LIKE '%rman%';
SID SERIAL# SPID CLIENT_INFO
----- ---------- ------------ -------------------------
157 18030 7344 rman channel=ORA_DISK_1

SQL> alter system kill session '157,18030';

Diagnosing NLS Character Set Issues

Problem

You’re trying to connect to RMAN, and you get an NLS error similar to the following:
ORA-12705: Cannot access NLS data files or invalid environment specified

Solution

SQL> select value from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

$ setenv NLS_LANG american_america.we8iso8859p1

In a Unix Korn shell environment, use the OS export command as follows:

$ export NLS_LANG=american_america.we8iso8859p1

V$NLS_VALID_VALUES Lists all valid values for NLS settings.
NLS_SESSION_PARAMETERS Contains NLS values for the current session.
V$NLS_PARAMETERS Contains current values of NLS parameters.
NLS_INSTANCE_PARAMETERS Contains NLS values set at the instance level. NLS_DATABASE_PARAMETERS Contains NLS values defined when your database was created.
These can be overridden by the instance, client OS, or client
session.

Logging RMAN Output


rman TARGET SYS/pwd debug trace=rman.trc log=rman.log


$ rman target / log=rman_output.log

From the RMAN Command Line
You can also spool the output to a log file from the RMAN command line, as shown here:

RMAN> spool log to rman_output.log
RMAN> set echo on
RMAN> backup database;

To turn off logging, use the log off parameter, as shown here:

RMAN> spool log off;

$ rman target / log=rman_output.log append

RMAN> spool log to rman_output.log append

RMAN> spool log to rman_output.log
RMAN> set echo on
RMAN> run{ allocate channel d1 type disk;
2> backup database;
3> release channel d1;
4> }


Viewing RMAN Command History

Solution
Use V$RMAN_OUTPUT to view the text messages that RMAN produces when performing
tasks. Run this query to view the historical RMAN command messages:
SQL> select
2 sid,
3 recid,
4 output
5 from v$rman_output
6 order by recid
7 /

Enabling RMAN’s Debug Output

From the OS Prompt
This first example enables all debugging and captures the output in a log file:
$ rman target / debug=all log=rman_output.log

The following example enables debugging just for I/O activities:
$ rman target / debug=io

When Configuring a Channel
This example configures a channel to debug and trace at level 5:

RMAN> configure channel device type disk debug=5 trace=5;


RMAN> spool log to rman_output.log
RMAN> debug on
RMAN> set echo on
RMAN> backup database;
RMAN> debug off
RMAN> spool log off

Enabling Granular Time Reporting

$ setenv NLS_DATE_FORMAT 'dd-mon-yyyy hh24:mi:ss'
In a Unix Korn shell environment, use the OS export command as follows:

$ export NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'
In a Windows environment, use the set command as shown here:

c:\> set NLS_DATE_FORMAT=dd-mon-yyyy hh24:mi:ss

For example, if you wanted to just debug I/O-related operations when backing up your
users tablespace, then you would enable I/O debugging as shown here:

RMAN> spool log to rman_output.log
RMAN> set echo on
RMAN> debug io
RMAN> backup tablespace users;
RMAN> debug off
RMAN> spool log off


RMAN Compatibility Matrix

Target DB Auxiliary DB RMAN Executable Catalog DB Catalog Schema
8.1.7.4 8.1.7.4 8.1.7.4 >= 8.1.7 8.1.7.4 or >= 9.0.1.4
9.0.1 9.0.1 9.0.1 >= 8.1.7 >= RMAN executable
9.2.0 9.2.0 >= 9.0.1.3 and <= Target DB >= 8.1.7 >= RMAN executable
10.1.0 10.1.0 >= 9.0.1.3 and <= Target DB >= 9.0.1 >= RMAN executable
10.2.0 10.2.0 >= 9.0.1.3 and <= Target DB >= 9.0.1 >= RMAN executable
11.1.0 11.1.0 TBD TBD >= RMAN executable


Managing Files in an ASM Environment

C:\> asmcmd -p
You should now see the ASMCMD prompt:
ASMCMD [+]>
The -p option will set your prompt to display the current working directory as part of the
prompt. For example, the ASMCMD prompt changes as we use the cd command to change
the current directory:
ASMCMD [+] > cd +data/prmy/datafile
ASMCMD [+data/prmy/datafile] >

Command Description
cd Changes the current directory to the specified directory
du Displays the total disk space occupied by ASM files in the specified ASM
directory and all its subdirectories, recursively
exit Exits ASMCMD
find Lists the paths of all occurrences of the specified name (with wildcards) under
the specified directory help Displays the syntax and description of ASMCMD commands
ls Lists the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups
lsct Lists information about current ASM clients
lsdg Lists all disk groups and their attributes
mkalias Creates an alias for a system-generated filename
mkdir Creates ASM directories
pwd Displays the path of the current ASM directory
rm Deletes the specified ASM files or directories
rmalias Deletes the specified alias, retaining the file to which the alias points
md_backup Creates a metadata backup script of mounted disk groups (Oracle Database
11g only)
md_restore Restores a disk group backup (Oracle Database 11g only)
lsdsk Lists the ASM disks (Oracle Database 11g only)
repair Repairs range of physical blocks on the ASM disk (Oracle Database 11g only)


with SQL*Plus, the following query is useful for viewing files within your ASM environment:


SQL> SELECT concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path FROM
2 (SELECT g.name gname, a.parent_index pindex, a.name aname,
3 a.reference_index rindex FROM v$asm_alias a, v$asm_diskgroup g
4 WHERE a.group_number = g.group_number)
5 START WITH (mod(pindex, power(2, 24))) = 0
6 CONNECT BY PRIOR rindex = pindex;

Backup Validation with RMAN
You can run the BACKUP ... VALIDATE command to check datafiles for physical and logical corruption, or to confirm that all database files exist in the correct locations. No backup is taken, but all specified files are scanned to verify that they can be backed up. All corruptions are recorded in theV$DATABASE_BLOCK_CORRUPTION view.

The following example shows how to check your entire database and archived redo log files for physical and logical corruption:

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
You cannot use the MAXCORRUPT or PROXY parameters with the VALIDATE option.

# mark backup as unavailable in the repository so that RMAN does not attempt to
# restore it unless explicitly specified on the RESTORE command
RMAN> CHANGE BACKUP TAG 'db_archive_1' UNAVAILABLE;
RMAN> SQL 'ALTER DATABASE OPEN';

lunes, 28 de septiembre de 2009

RMAN Tuning

Views for RMAN Performance Tuning

V$RMAN_BACKUP_JOB_DETAILS Reports information about backup jobs

V$BACKUP_ASYNC_IO Displays RMAN asynchronous I/O performance information for currently running and recently completed backup and restore operations

V$BACKUP_SYNC_IO Displays RMAN synchronous I/O performance information for currently running and recently completed backup and restore operations

V$PROCESS Lists currently active processes

V$SESSION Displays session information for current sessions

V$SESSION_LONGOPS Shows progress of RMAN backup, restore, and
recovery operations

V$RECOVERY_PROGRESS Shows progress of RMAN or user-managed recovery operations

V$SESSION_WAIT Displays events or resources for which sessions are
currently waiting


Identifying RMAN Processes

SQL> SELECT b.sid, b.serial#, a.spid, b.client_info
2 FROM v$process a, v$session b
3 WHERE a.addr = b.paddr
4 AND b.client_info LIKE '%rman%';

This output shows that there is currently one RMAN channel allocated:
SID SERIAL# SPID CLIENT_INFO
---------- ---------- ------------ -------------------------
146 29 4376 rman channel=ORA_DISK_1


Measuring Backup Performance

SQL> SELECT session_recid, input_bytes_per_sec_display,
2 output_bytes_per_sec_display,
3 time_taken_display, end_time
4 FROM v$rman_backup_job_details
5 ORDER BY end_time;
Y
ou should see output similar to the following:
SESSION_RECID INPUT_BYTES_PER OUTPUT_BYTES_PE TIME_TAKEN_DISPLAY END_TIME
------------- --------------- --------------- -------------------- ---------
1096 8.60M 7.69M 00:14:25 20-DEC-06
1101 1.88M 1.78M 00:42:03 21-DEC-06
1110 9.59M 8.56M 00:14:56 22-DEC-06
1114 9.75M 8.71M 00:14:52 23-DEC-06
1116 10.73M 9.58M 00:14:31 24-DEC-06

View Name Recovery Catalog View

V$RMAN_BACKUP_SUBJOB_DETAILS RC_RMAN_BACKUP_SUBJOB_DETAILS
V$BACKUP_SET RC_BACKUP_SET
V$BACKUP_SET_DETAILS RC_BACKUP_SET_DETAILS
V$BACKUP_SET_SUMMARY RC_BACKUP_SET_SUMMARY
V$BACKUP_PIECE RC_BACKUP_PIECE
V$BACKUP_PIECE_DETAILS RC_BACKUP_PIECE_DETAILS
V$BACKUP_DATAFILE RC_BACKUP_DATAFILE
V$BACKUP_DATAFILE_DETAILS RC_BACKUP_DATAFILE_DETAILS
V$BACKUP_DATAFILE_SUMMARY RC_BACKUP_DATAFILE_SUMMARY
V$BACKUP_FILES RC_BACKUP_FILES
V$BACKUP_COPY_DETAILS RC_BACKUP_COPY_DETAILS
V$BACKUP_COPY_SUMMARY RC_BACKUP_COPY_SUMMARY
V$BACKUP_REDOLOG RC_BACKUP_REDOLOG
V$BACKUP_ARCHIVELOG_DETAILS RC_BACKUP_ARCHIVELOG_DETAILS
V$BACKUP_ARCHIVELOG_SUMMARY RC_BACKUP_ARCHIVELOG_SUMMARY
V$BACKUP_CONTROLFILE_DETAILS RC_BACKUP_CONTROLFILE_DETAILS
V$BACKUP_CONTROLFILE_SUMMARY RC_BACKUP_CONTROLFILE_SUMMARY
V$BACKUP_SPFILE RC_BACKUP_SPFILE
V$BACKUP_SPFILE_DETAILS RC_BACKUP_SPFILE_DETAILS
V$BACKUP_SPFILE_SUMMARY RC_BACKUP_SPFILE_SUMMARY

Monitoring RMAN Job Progress

SQL>gt; select sid, serial#, sofar, totalwork, opname,
2 round(sofar/totalwork*100,2) "% Complete"
3 from v$session_longops
4 where opname LIKE 'RMAN%'
5 and opname NOT LIKE '%aggregate%'
6 and totalwork != 0
7 and sofar <> totalwork;

SID SERIAL# SOFAR TOTALWORK OPNAME % Complete
---------- ---------- ---------- ---------- ---------------------------------------------------------------- ----------
2838 43105 902382 902956 RMAN: archived log backup 99,94


Oracle also provides a view, V$RECOVERY_PROGRESS, that reports on just the recovery
operations (either RMAN or user-managed). This view is a subview of the V$SESSION_
LONGOPS view. To report on the progress of a recover command, you can run a SQL
query as shown here:

SQL> select type, item, units, sofar, total from v$recovery_progress;


TYPE ITEM UNITS SOFAR TOTAL
--------------- ------------------------- ---------- ---------- ----------
Media Recovery Log Files Files 3 229
Media Recovery Active Apply Rate KB/sec 3425 0
Media Recovery Average Apply Rate KB/sec 3861 0
Media Recovery Redo Applied Megabytes 449 0
Media Recovery Last Applied Redo SCN+Time 1415101 0
Media Recovery Active Time Seconds 81 0
Media Recovery Apply Time per Log Seconds 0 0
Media Recovery Checkpoint Time per Log Seconds 0 0
Media Recovery Elapsed Time Seconds 119 0

The V$SESSION_LONGOPS view contains information about long-running jobs (SQL statements,RMAN operations, and so on) in your database. You can use this view to monitor the progress of RMAN backup, restore, and recovery operations.


select s.client_info,
sl.opname,
sl.message,
sl.sid, sl.serial#, p.spid,
sl.sofar, sl.totalwork,
round(sl.sofar/sl.totalwork*100,2) "% Complete"
from v$session_longops sl, v$session s, v$process p
where p.addr = s.paddr
and sl.sid=s.sid
and sl.serial#=s.serial#
and opname LIKE 'RMAN%'
and opname NOT LIKE '%aggregate%'
and totalwork != 0
and sofar <> totalwork;

If your backup or restore command is interacting with several backup sets, then you may want to report at an aggregate level. To report at the aggregate level, run a query similar to the one shown next:


SQL> select sid, serial#, sofar, totalwork,opname,
2 round(sofar/totalwork*100,2) "% Complete"
3 from v$session_longops
4 where opname LIKE 'RMAN%aggregate%'
5 and totalwork != 0
6 and sofar <> totalwork;

Identifying I/O Bottlenecks

SQL> SELECT sid, serial, filename, type, elapsed_time, effective_bytes_per_second
2 FROM v$backup_async_io
3 WHERE close_time > sysdate – 7;

If you have identified your SID and SERIAL number (see recipe 16-1), you can specifically query for records associated with your current session:

SQL> SELECT filename, sid, serial, close_time, long_waits/io_count as ratio
2 FROM v$backup_async_io
3 WHERE type != 'AGGREGATE'
4 AND SID = &SID
5 AND SERIAL = &SERIAL
6 ORDER BY ratio desc;


Improving Tape I/O Performance

We have two recommendations for improving RMAN’s I/O performance with tape devices:

• Use an incremental backup strategy with block change tracking.
• Adjust multiplexing of backup sets.


The block change tracking feature enables RMAN to quickly identify which blocks have
changed since the last incremental backup. This feature can significantly improve the performance of incremental backups.

Also, consider altering the default multiplexing behavior of RMAN. Setting filesperset high and maxopenfiles low may increase the efficiency of writing to your tape device. You can also use the diskratio parameter to instruct RMAN to balance the load if datafiles are distributed across several different disks. See recipe 16-6 for details on how to adjust the multiplexing of backup sets.


Maximizing Throughput to Backup Device

Solution
You can tune the throughput to backup devices by adjusting RMAN’s level of multiplexing. RMAN multiplexing is controlled by three parameters:

• filesperset
• maxopenfiles
• diskratio

Using filesperset Use the fileperset clause of the backup command to limit the number of datafiles in each backup set. For example, if you wanted to limit the number of files being written to a backup set to only two files, you would use filesperset, as shown here:

RMAN> backup database filesperset 2;

Using maxopenfiles Use the maxopenfiles clause of the configure channel command or the allocate channel command to limit the number of files that can be simultaneously open for reads during a backup. If you want to limit the number of files being read by a channel to two files, use maxopenfiles as follows:

RMAN> configure channel 1 device type disk maxopenfiles 2;
To reset the channel maxopenfiles back to the default setting, use the clear parameter as shown here:

RMAN> configure channel 1 device type disk clear;

Using diskratio The diskratio parameter of the backup command instructs RMAN to read datafiles from a specified number of disks. For example, if you wanted RMAN to include datafiles located on at least four different disks into one backup set, then use diskratio as follows:

RMAN> backup database diskratio 4;
If you specify filesperset, and not diskratio, then diskratio will default to the value of filesperset. The diskratio parameter works only on operating systems that can provide RMAN with information such as disk contention and node affinity.

The value of filesperset specifies the maximum number of files in each backup
set. The default value of filesperset is as follows:

MIN(64, # of files to be backed up divided by the numbers of channels allocated)

The default value of maxopenfiles is as follows:
MIN(8, # files being backed up)

Setting Large Pool Memory Size

Problem
You’re using I/O slaves, and you’re aware that RMAN can take advantage of memory in the large pool when using I/O slaves. You want to ensure that the large pool is sized correctly.

Solution

Use the Automatic Shared Memory Management (ASMM) feature to have Oracle automatically manage the allocation of memory pools. To enable ASMM, do the following:

1. Ensure that the initialization parameter statistics_level is set to TYPICAL (the
default) or ALL.

2. Set sga_target to an appropriate nonzero value for your database not exceeding the
sga_max_size.

3. Set the following initialization parameters to zero:

• shared_pool_size
• large_pool_size
• java_pool_size
• db_cache_size
• streams_pool_size

Once you enable ASMM, Oracle will automatically size and adjust those memory pools
for you. You can set any of these values manually to enforce a minimum amount of memory used for the specified pool.

If you want to manually set the large pool size, here is Oracle’s recommended formula for sizing it:

large_pool_size = num_of_allocated_channels * (16 MB + (4 * size_of_tape_buffer ))

Tuning Media Recovery


Problem
You want to manually adjust the degree of parallelism that Oracle uses for media recovery to match the number of CPUs on your database server.

Solution

Starting with Oracle Database 10g, when you issue an RMAN recover command from either
within RMAN or within SQL*Plus, Oracle’s default behavior is to automatically perform media recovery operations in parallel. However, if you want to override this default behavior, you can use the recover parallel or recover noparallel command. This next line of code instructs
Oracle to spawn four parallel processes to apply redo:

RMAN> recover database parallel 4;

If you don’t want Oracle to recover in parallel, then specify the noparallel clause as shown here:

RMAN> recover database noparallel;

Tuning Crash Recovery


Problem

You want to ensure that your database comes up as efficiently as possible after you issue a shutdown abort command or experience a hard crash. You want to specify a target duration time for any crash recovery that is needed as a result of an instance crash or a shutdown abort command.

Solution

The fast_start_mttr_target initialization parameter allows you to specify a target value in seconds that denotes the amount of time that you want Oracle to take to perform crash recovery.

To determine an appropriate value for this parameter, follow this procedure:

1. Disable the initialization parameters that interfere with fast_start_mttr_target.
2. Determine the lower bound for fast_start_mttr_target.
3. Determine the upper bound for fast_start_mttr_target.
4. Select a value within the upper and lower bounds.
5. Monitor and adjust.

Note Although you can specify a target duration using fast_start_mttr_target, be aware that the database software treats that target as a “best-effort” target. Depending upon the circumstances, the actual time to perform crash recovery might be more or less than the target.

Step 1:Disable Parameters

Ensure that fast_start_io_target, log_checkpoint_interval, and log_checkpoint_timeout are all set to 0. These parameters will interfere with fast_start_mttr_target.

Step 2: Determine the Lower Bound Set fast_start_mttr_target to a value of 1, and then stop and start your database. This example
assumes you are using an spfile; if you aren’t, then you will have to manually modify your init.ora file.

SQL> alter system set fast_start_mttr_target=1;
SQL> shutdown immediate;
SQL> startup;

Immediately query the TARGET_MTTR value from V$INSTANCE_RECOVERY, as shown
here:

SQL> select target_mttr from v$instance_recovery;
TARGET_MTTR
-----------
52

Step 3: Determine the Upper Bound

Set fast_start_mttr_target to a value of 3600, and then stop and start your database. This example assumes you are using an spfile; if you aren’t, then you will have to manually modify your init.ora file.

SQL> alter system set fast_start_mttr_target=3600;
SQL> shutdown immediate;
SQL> startup;

Immediately query the TARGET_MTTR value from V$INSTANCE_RECOVERY, as shown
here:

SQL> select target_mttr from v$instance_recovery;


After your database has experienced a normal amount of activity, you can query

V$MTTR_TARGET_ADVICE as follows:

SQL> SELECT
2 mttr_target_for_estimate, advice_status, estd_cache_writes, estd_total_ios
3 from v$mttr_target_advice
4 order by 1;

The following output shows values of writes and I/O for each estimated value of
fast_start_mttr_target:

MTTR_TARGET_FOR_ESTIMATE ADVIC ESTD_CACHE_WRITES ESTD_TOTAL_IOS
------------------------ ----- ----------------- --------------
52 ON 1811 11030
130 ON 1575 10794
209 ON 1575 10794
288 ON 1575 10794
377 ON 1575 10794


Problem

You’ve noticed that your application performance degrades when the RMAN backups are running. You want to reduce RMAN’s I/O rate so that it spreads out its impact on the system over a period of time.

Solution

Use one of the following to control RMAN’s I/O rate:
• The backup duration ... minimize load command
• The rate clause of the allocate channel or configure channel command
Using backup duration ... minimize load
Use the backup duration ... minimize load command to evenly distribute RMAN I/O
over a period of time. This example shows how to spread the I/O of an RMAN backup over a
45-minute period:

RMAN> backup duration 00:45 minimize load database;

RMAN will report the time taken for the backup operation in the output, as shown in this
snippet:
channel ORA_DISK_1: throttle time: 0:44:43
Finished backup at 10-MAR-07

# Backup completo con impacto mínimo en la BD y una duración máxima de 30 minutos
BACKUP AS BACKUPSET DURATION 00:30 MINIMIZE LOAD PARTIAL DATABASE;
# Backup completo minimizando el tiempo de ejecucion y una duración máximo de 15 minutos
# Hemos eliminado PARTIAL para que en caso de que no se complete, se considere FALLIDO
BACKUP AS BACKUPSET DURATION 00:15 MINIMIZE TIME DATABASE;

Using rate
You can also use the rate clause of the allocate channel command or the configure channel command to control RMAN’s I/O rate. This example configures channel 1 to have a maximum read rate of 5MB per second:

RMAN> configure channel 1 device type disk rate=5M;

RMAN {
2> backup duration 1:00 partial minimize load database;
3> backup archivelog all;
3> backup current controlfile;

If you want to force RMAN to include only one datafile per backup set, then you can use the filesperset parameter as shown here

RMAN> backup duration 01:00 minimize load database filesperset 1;

Improving Performance Through Parallelism

Solution
Use the parallel clause of the configure command to instruct RMAN to allocate multiple channels for backup and restore operations. The following command instructs RMAN to automatically allocate four channels for the default disk device:

RMAN> configure device type disk parallelism 4;

RMAN> show device type;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET PARALLELISM 2;


Managing Backup Performance with MINIMIZE TIME and MINIMIZE LOAD

When using DURATION you can run the backup with the maximum possible performance, or run as slowly as possible while still finishing within the allotted time, to minimize the performance impact of backup tasks. To maximize performance, use the MINIMIZE TIME option with DURATION, as shown in this example:

BACKUP DURATION 4:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
To extend the backup to use the full time available, use the MINIMIZE LOAD option, as in this example:

BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
RMAN monitors the progress of the running backup, and periodically estimates how long the backup will take to complete at its present rate. If RMAN estimates that the backup will finish before the end of the backup window, it slows down the rate of backup so that the full available duration will be used. This reduces the overhead on the database associated with the backup.


Configuring Backup Optimization
Run the CONFIGURE command to enable and disable backup optimization. Backup optimization skips the backup of files in certain circumstances if the identical file or an identical version of the file has already been backed up. Full details on the backup optimization algorithm are provided in "Backup Optimization".

Note that backup optimization applies only to the following commands:

•BACKUP DATABASE

•BACKUP ARCHIVELOG with ALL or LIKE options

•BACKUP BACKUPSET ALL

You can override optimization at any time by specifying the FORCE option on the BACKUP command. For example, you can run:

BACKUP DATABASE FORCE;
BACKUP ARCHIVELOG ALL FORCE;
By default, backup optimization is configured to OFF. To enable backup optimization, run the following command:

CONFIGURE BACKUP OPTIMIZATION ON;
To disable backup optimization, run the following command:

CONFIGURE BACKUP OPTIMIZATION OFF;
To clear the current backup optimization setting, that is, return backup optimization to its default setting of OFF, run this command:

CONFIGURE BACKUP OPTIMIZATION CLEAR;


Configuring Tablespaces for Exclusion from Whole Database Backups
You can run CONFIGURE EXCLUDE FOR TABLESPACE to exempt the specified tablespace from the BACKUP DATABASE command. The exclusion condition applies to any datafiles that you add to this tablespace in the future.

This tablespace exclusion feature is useful when you do not want to make a specified tablespace part of the regular backup schedule, as in these cases:

•A tablespace is easy to rebuild, so it is more cost-effective to rebuild it than back it up every day.

•A tablespace contains temporary or test data that you do not need to back up.

•A tablespace does not change often and therefore should be backed up on a different schedule from other backups.

For example, you can exclude testing tablespaces cwmlite and example from whole database backups as follows:

CONFIGURE EXCLUDE FOR TABLESPACE cwmlite;
CONFIGURE EXCLUDE FOR TABLESPACE example;
If you run the following command, then RMAN backs up all tablespaces in the database except cwmlite and example:

BACKUP DATABASE;
You can still back up the configured tablespaces by explicitly specifying them in a BACKUP command or by specifying the NOEXCLUDE option on a BACKUP DATABASE command. For example, you can enter one of the following commands:

# backs up the whole database, including cwmlite and example
BACKUP DATABASE NOEXCLUDE;
BACKUP TABLESPACE cwmlite, example; # backs up only cwmlite and example
You can disable the exclusion feature for cwmlite and example as follows:

CONFIGURE EXCLUDE FOR TABLESPACE cwmlite CLEAR;
CONFIGURE EXCLUDE FOR TABLESPACE example CLEAR;
RMAN includes these tablespaces in future whole database backups.

Showing the Tablespaces Excluded from Backups
SHOW EXCLUDE shows how you have used the CONFIGURE EXCLUDE command to exclude tablespaces from whole database backups.

After connecting to the target database and recovery catalog (if you use one), run the SHOW EXCLUDE command. For example, enter:

RMAN> SHOW EXCLUDE; # shows the CONFIGURE EXCLUDE setting
Sample output for SHOW EXCLUDE follows:

RMAN configuration parameters are:
CONFIGURE EXCLUDE FOR TABLESPACE 'OLD_ACCOUNTS';

Transporting Tablespaces on the Same OS Platform

In the following example, the transportable
tablespace set consists of two tablespaces—test1 and test2. Use the transport_set_check procedure of the DBMS_TTS package to verify whether the two tablespaces are selfcontained, as shown here:

SQL> execute sys.dbms_tts.transport_set_check('test1, test2',TRUE);
PL/SQL procedure successfully completed.

RMAN> transport tablespace test1,test2
2> tablespace destination '/u05/app/oracle/transportdest'
3> auxiliary destination '/u05/app/oracle/auxdest';

You can also use the transport tablespace command to perform a tablespace transport
to a past point in time. Simply add the unitl scn clause to the transport tablespace command, as shown here:

RMAN> transport tablespace test1,test2
2> tablespace destination '/u05/app/oracle/transportdest'
3> auxiliary destination '/u05/app/oracle/auxdest'
4> until SCN 259386;

Transporting Tablespaces Across Different Operating
System Platforms


To find out the endian formats of the two platforms, use the following query:

SQL> select platform_name, endian_format from
2* v$transportable_platform;


1. Place the tablespaces you want to transport in read-only mode:

SQL> alter tablespace myspace read only;
Tablespace altered.

SQL>

2. Use the convert tablespace command to convert the source (HP-UX in this case)
datafiles in the transportable tablespace set to the target (Linux in this case) platform:

RMAN> convert tablespace myspace
2> to platform 'Linux IA (32-bit)'
3> format='/tmp/dba/%U';

3. Verify that you have the two datafiles that are part of the myspace tablespace in the
/tmp/dba directory:

$ ls –altr /tmp/dba
data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs
data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200


4. Use the Data Pump Export utility to create the export dump file with the metadata for the myspace tablespace:

$ expdp pasowner/orbiter1 directory=direct1 transport_tablespaces=myspace

5. Move both the converted datafiles from step 2 and the export dump file
(myspace.dmp) from step 3 to the target host, as shown here:
$ rcp data_D-PASU_I-877170026_TS-MYSPACE_FNO-64_02i6g1vs prod1:/tmp/dba
$ rcp data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200 prod1:/tmp/dba
$ rcp myspace.dmp prod1:/tmp/dba


6. Import the tablespace metadata for the transported tablespaces into the target database,
as shown here:
$ impdp system/sammyy1 directory=data_dump_dir2
dumpfile='myspace2.dmp'
transport_datafiles='/u05/app/oracle/data_D-PASU_I-877170026_TS-YSPACE_FNO-➥
64_02i6g1v,
'/u05/app/oracle/ data_D-PASU_I-877170026_TS-MYSPACE_FNO-66_03i6g200'

RMAN> connect target /
RMAN> convert datafile=
'/u01/transport_solaris/sales/sales01.dbf',
'/u01/transport_solaris/sales/sales02.dbf'
from platform 'Solaris[tm] OE (32-bit)'
db_file_name_convert
'/u01/transport_solaris/sales','/u05/newdb/sales';

Transporting an Entire Database to a Different Platform

Problem

You want to transport an entire Oracle database to another host that is using a different operating system platform.

Solution

Use the convert database command to move an Oracle database from one platform to
another. The only requirement is that the two platforms share an identical endian format. You can perform the convert database operation on the source platform or the destination platform.

The following are the steps to transport an Oracle database from a Windows XP platform
to the Linux platform:

1. Make sure the source database is eligible for transporting to the destination operating system platform by executing the dbms_tdb.check_db procedure, as shown here. The source database is running on a Windows XP platform, and the target database is running on a Linux 32-bit platform.


SQL> connect sys/sammyy1 as sysdba
Connected.
SQL> set serveroutput on
SQL> declare
2> db_ready boolean;
3> begin
4> db_ready := dbms_tdb.check_db('Linux IA(32-bit)',
dbms_tdb.skip_readonly);
5* end;
SQL> /

PL/SQL procedure successfully completed.
SQL>

If you see the message “PL/SQL procedure successfully completed,” as is the case in
this example, it means you can migrate from the specified operating system platform
to the destination platform.

2. Since external tables aren’t automatically transported to the target platform, you must invoke the dbms_tdb.check_db procedure again to get a list of the external tables, which you can then use to re-create those tables on the destination platform after the database transportation.

SQL> declare
2> external boolean;
3> begin
4> external :=dbms_tdb.check_external;
5* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>

3. Put the target database in the read-only mode after shutting it down first and restarting it in mount state:

SQL> connect sys/sammyy1 as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 180357996 bytes
Database Buffers 427819008 bytes
Redo Buffers 2940928 bytes
Database mounted.

SQL> alter database open read only;
Database altered.
SQL>

RMAN> convert database new database 'mydb'
2> transport script 'c:\temp\mydb_script'
3> to platform 'Linux IA (32-bit)'
4> db_file_name_convert 'c:\oracle\product\11.1.0\oradata\nick\'
'c:\temp\';

5. On the target platform, first set the ORACLE_SID environment variable pointing to the
new database:

$ export ORACLE_SID=newdb
The ORACLE_HOME environment variable must point to the same version of Oracle
software as on the source platform.
Execute the mydb_script file after logging in to SQL*Plus as the user sys:

$ sqlplus /nolog
SQL> connect sys/sammyy1 as sysdgba
Connected to an idle instance.
SQL> @mydb
ORACLE instance started.
Total System Global Area 243269632 bytes
Fixed Size 1218748 bytes
Variable Size 79693636 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Control file created.
Database altered.
Tablespace altered.

6. Check to make sure there are no invalidated objects left in the database:
SQL> select count(*) from dba_objects where
status='INVALID';

Transporting a Database by Converting Datafiles on the
Target Platform


1. Make sure the source database is eligible for transporting to the destination operating system platform by executing the dbms_tdb.check_db procedure, as shown here. The source database is running on a Windows XP platform, and the target database is running on a Linux 32-bit platform.

SQL> connect sys/sammyy1 as sysdba
Connected.

SQL> set serveroutput on

SQL> declare
2> db_ready boolean;
3> begin
4> db_ready := dbms_tdb.check_db('Linux IA(32-bit)',
5> dbms_tdb.skip_readonly);
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>


2. Since external tables aren’t automatically transported to the target platform, you must invoke the dbms_tdb.check_external procedure again to get a list of the external tables that you can then use to re-create those tables on the destination platform after the database transportation.

SQL> declare
2> external boolean;
3> begin
4> external :=dbms_tdb.check_external;
5> end;
6> /
PL/SQL procedure successfully completed.


3. Put the target database in read-only mode after shutting it down first and restarting it in the mount state:

SQL> connect sys/sammyy1 as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250452 bytes
Variable Size 180357996 bytes
Database Buffers 427819008 bytes
Redo Buffers 2940928 bytes
Database mounted.

SQL> alter database open read only;
Database altered.
SQL>

4. Execute the convert database command on the source database platform with the on
target platform clause, as shown here:

RMAN> convert database on target platform
2> convert script 'c:\temp\convert\convertscript.rman'
3> transport script 'c:\temp\convert\transportscript.sql'
4> new database 'youdb'
5> format 'c:\temp\convert\%U'
6> ;

5. Copy the datafiles of the source database while that database is still in the read-only mode and move them to the target database platform.

6. Run the convertscript.rman script on the target database after first transporting all the source database files to the target platform. Once you copy the necessary files, you can put the source database in a read-write mode again.

7. You use a separate convert datafile command for each datafile you need to convert
from the source to the target platform. The format parameter of the convert datafile
command specifies the location of each file on the target platform.

$ rman target / cmdfile=CONVERTSCRIPT.RMAN


8. Make the necessary changes in the pfile and the transportscript.sql script produced
by the convert database script. Execute the transportscript.sql script from SQL*Plus
to produce a new database instance, and create the new database (named youdb in
the example):

$ sqlplus /nolog
SQL> @transportscript

RMAN Duplicating Databases and Transporting Data

Renaming Files in a Duplicate Database

db_file_name_convert and the log_file_name_convert

Once you specify the spfile parameter during the database duplication, you can provide your list of initialization parameters and the values for them through the parameter_value_convert clause and the set clause. It’s easiest to use the parameter_value_convert clause in the duplicate database command to specify all parameters that specify a directory path. You can specify all such directory-related parameter values through the parameter_value_convert parameter, except for the db_file_name_convert and log_file_name_convert parameters.

Here’s an example showing how to use the parameter_value_convert clause and the set clause to specify various initialization parameter values for the duplicate instance right when you issue the duplicate database command:


duplicate target database
to dupdb
from active database spfile
parameter_value_convert '/a01', '/a20'
set sga_max_size = 800m
set sga_target = 700m
set log_file_name_convert = '/a01','/a20',
db_file_name_convert '/a01','/a20';


RMAN> run
{
set newname for datafile 1 to '/u01/app/oracle/testdata/file1.dbf';
set newname for datafile 2 to '/u01/app/oracle/testdata/file2.dbf';
...
duplicate target database to newdb nofilenamecheck;
}

Creating a Duplicate Database on the Same Host


1. Back up the target database as follows:

RMAN> connect target /
RMAN> backup database plus archivelog;

You’ll use these backups as the source for the database duplication later. Of course, if you already have made backups of the source database, you can skip this step.

2. Use a dedicated listener configuration for RMAN by making the following additions to your listener.ora file:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =prod1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =test1)
)
)
3. Add the following information to the tnsnames.ora file, located in the
$ORACLE_HOME/network/admin directory:
test1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))

)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)

4. Create the init.ora file for the new duplicate database, test1. During the database duplication process, RMAN will create the control files, the data files, and the redo log files for the duplicate database with the filename structure you provide through the db_file_name_convert and log_file_name_convert initialization parameters. The database duplication process will create a control file for the new duplicate database in the location specified by the control_files initialization parameter.

db_name = test1
db_block_size = 8192
compatible = 11.1.0.1.0
remote_login_passwordfile = exclusive
control_files = ('/u01/app/oracle/test1/control01.ctl',
'/u01/app/oracle/test1/control02.ctl')
db_file_name_convert = ('/u01/app/oracle/oradata/prod1',
'/u05/app/oracle/oradata/test1')
log_file_name_convert = ('/u01/app/oracle/oradata/prod1',
'/u05/app/oracle/oradata/test1')

5. Start the new auxiliary database (duplicate database) instance. You must start the
new instance in nomount mode since you don’t have a control file for this new
database yet.

$ export ORACLE_SID=test1
$ sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance

SQL> startup nomount pfile=$ORACLE_HOME/dbs/inittest1.ora
Oracle Instance started.
Total System Global Area 113246208 bytes
Fixed Size 1218004 bytes
Variable Size 58722860 bytes


6. Start RMAN, and connect to the target database after making sure you first set the
ORACLE_SID environmental variable to the source database, prod1. Note that the target
database can be mounted or open.

$ rman

Recovery Manager: Release 11.1.0.1.0 - Beta on Sat Jun 9 14:03:42 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target
connected to target database: prod1 (DBID=2561840016)

7. Connect to the duplicate database using the keyword auxiliary through a SQL*Net
connection:

RMAN> connect auxiliary sys/@test1
connected to auxiliary database: test1 (not mounted)

RMAN>
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes

SQL> exit

8. Issue the duplicate target database command to start the database duplication
process:

RMAN> duplicate target database to test1;

SQL> select parameter, value from nls_database_parameters
where parameter in
('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET'
,'NLS_NCHAR_CHARACTERSET');


Duplicating a Database Without Any RMAN Backups

1. Use a dedicated listener configuration for RMAN by making the following additions to
your listener.ora file:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prod1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =prod1)
)
(SID_DESC =
(GLOBAL_DBNAME = test1)
(ORACLE_HOME = /u01/app/oracle/product/11g/)
(SID_NAME =test1)
)
)

2. Add the following information to the tnsnames.ora file, located in the
$ORACLE_HOME/network/admin directory:
test1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1)
)
)

3. Create a password file for connecting remotely to the duplicate database with the
sysdba privilege. This is because when you perform active database duplication, the
target database instance must connect directly to the auxiliary database instance. Note
that the password file connection requires the same sysdba password as that of the
source database. Create the password file manually with the orapwd utility, as shown
here, with just a single password to start the auxiliary instance:

$ orapwd password= file=orapwtest1 entries=20

4. Start the auxiliary database (duplicate database) instance. You must start the new
instance in nomount mode since you don’t have a control file for this new database yet.
$ export ORACLE_SID=test1
$ sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance

SQL> startup nomount
Oracle Instance started.
Total System Global Area 113246208 bytes
Fixed Size 218004 bytes
Variable Size 58722860 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
SQL> exit


$rman target sys/sammyy1@eleven

RMAN> connect auxiliary sys/sammyy1@auxdb
connected to auxiliary database: AUXDB (not mounted)

RMAN> duplicate target database
2> to auxdb
3> from active database
4> spfile
5> parameter_value_convert =
'/u01/app/oracle/eleven/eleven','/u01/app/oracle/eleven/auxdb'
6> set log_file_name_convert =
'/u05/app/oracle/eleven/eleven','/u05/app/oracle/eleven/auxdb'
7> db_file_name_convert =
'/u05/app/oracle/eleven/eleven','/u05/app/oracle/eleven/auxdb';

RMAN> duplicate database
to newdb
from active database
spfile
nofilenamecheck;

If you’d rather create a standby database instead of a duplicate database, all you have to do is replace the to auxdb part of the duplicate database command with the for standby clause, as shown here:

RMAN> duplicate target database
for standby
from active database
spfile

It’s easy to duplicate a non-ASM file based database to an ASM file system–based database. Here we’ll show you a simple example to demonstrate how to do this. First create an ASM disk group, named +DISK1. Here’s the database duplication command to create an ASM file system–based duplicate database:


RMAN> duplicate target database
to newdb
from active database
spfile
parameter_value_convert
'/u01/app/oracle/oradata/sourcedb/','+DISK1'
set db_create_file_dest = +DISK1;

Creating a Duplicate Database on a Remote Host with the
Same File Structure


Solution
Let’s call our primary database PROD, the duplicate database AUX, and the RMAN catalog
database CATDB (the catalog is purely optional). Here are the steps to duplicate the primary
database on a different server:

1. Back up the primary database. You must take a full backup and include all the archive logs as well as the control file.

[oracle@linux] rman target=/ catalog rman/rman@catdb

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}

2. Ensure that the source database backups are available for the duplication process. You can get a complete list of the necessary backups by running the list backup command, as shown here:

RMAN>list backup;


3. Create the necessary directories for the duplicate database, and then create the initialization
parameter file for the duplicate database, as shown in the following example:
audit_file_dest =/oradata/AUX/adump
background_dump_dest =/oradata/AUX/bdump
core_dump_dest =/oradata/AUX/cdump
user_dump_dest =/oradata/AUX/udump
db_name ="AUX"
instance_name =AUX
control_files =('/oradata/AUX/control01.ctl',
'/oradata/AUX/control02.ctl','/oradata/AUX/control03.ctl')


4. Start up the auxiliary instance in nomount mode:
$ export ORACLE_SID=AUX
$ sqlplus '/as sysdba'

SQL> startup nomount;

5. Check the Oracle Net connections to the primary database and the recovery catalog.
The production database can be open or in a mounted state. The catalog database, if
you’re using it, must be open. Make sure you can connect to the primary database on
server A from the target server. Make sure you can also connect to the RMAN catalog
from the target server..

$ sqlplus 'sys/oracle@PROD as sysdba'
$ rman catalog rman/rman@catdb # not mandatory

$ export ORACLE_SID=AUX
$ rman target sys/sys@PROD catalog rman/rman@catdb auxiliary /

RMAN> duplicate target database to AUX
nofilenamecheck;

SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
900 150 20.34375 1070.34375

Duplicating a Database with Several Directories

Problem
You want to duplicate a source database with data spread out over several directories.

Solution
If the source database files are spread over multiple directories, you must use the set newname parameter instead of the db_file_name_convert parameter to rename the files in the duplicate database.
The following example shows how to create a duplicate database when the target database uses several different directories:

RMAN> run
{set newname for datafile 1 to '/u01/app/oracle/testdata/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/testdata/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/testdata/data01.dbf';
set newname for datafile 4 to '/u01/app/oracle/testdata/index01.dbf';
set newname for datafile 5 to '/u01/app/oracle/testdata/undotbs01.dbf';
duplicate target database to newdb
logfile
group 1 ('/u01/app/oracle/testdata/logs/redo01a.log',
('/u01/app/oracle/testdata/logs/redo01b.log') size 10m reuse,
group 2 ('/u01/app/oracle/testdata/logs/redo02a.log',
('/u01/app/oracle/testdata/logs/redo02b.log') size 10m reuse;
}

Creating a Standby Database on a New Host

1. Perform a full backup of the primary database, including all the archive logs and the current control file.

2. Calculate the disk space necessary for the standby database by summing up the size of all the datafiles in the database.

3. Ensure there is enough space on the target host to accommodate the standby database by using an operating system command such as df –kh.

4. Back up the target database as shown here with the 'backup current controlfile for
standby format' command:

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/PROD/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/PROD/al_t%t_s%s_p%p' archivelog all;
backup current controlfile for standby format '/backups/PROD/sb_t%t_s%s_p%p';
release channel d1;
}

5. Copy the backups from Host A to Host B to make them available to the standby creation process. Make sure you place the backups in identically named directories on
Host B. For example, if your source backup files are in /backups/PROD, those backup
files must be copied to the /backups/PROD directory on Host B. If you decide to place
them in a different directory, as explained at the beginning of this chapter, you must run the catalog command ("catalog start with ") to update the source
database control file with the location of the backups. For the catalog command to
work, make sure you have the different directory structure on the source database
as well. You must also move the archive log backups to the target server. If you’re performing a point-in-time recovery, you may need only some of the archived redo logs:

RMAN> list backup;
List of Backup Sets

6. Create an initialization file (init.ora) for the standby database in the $ORACLE_HOME/dbs directory. Use the primary database’s initialization file as the source for the initialization

parameter settings:
audit_file_dest =/apps/oracle/admin/PROD/adump
background_dump_dest =/apps/oracle/admin/PROD/bdump
core_dump_dest =/apps/oracle/admin/PROD/cdump
user_dump_dest =/apps/oracle/admin/PROD/udump
db_name ="PROD"
instance_name =PROD
# Set the following to the location of the standby clone control file.
control_files=('/u01/PROD/control01.ctl','/u02/PROD/control02.ctl',
'/u03/PROD/control03.ctl')
# Set the following for the from and to location for all data files / redo
# logs to be cloned. This is set if the location differs from Primary.
db_file_name_convert =("/u01/oradata/PROD","/u02/oradata/PROD")
log_file_name_convert =("/u01/oradata/PROD","/u02/oradata/PROD")
#Set the following to the same as the production target
undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 11.1.0.1.0
7. Run the following command on the standby database server to start the auxiliary
instance in nomount state:
$ export ORACLE_SID=AUX
$ sqlplus '/ as sysdba'
SQL> startup nomount;
8. Test the SQL*Net connections to the primary database and the RMAN catalog. The
production database must be open or mounted, and the recovery catalog must be
open. Run the following commands on the standby database server:
$ sqlplus 'sys/oracle@prod1 as sysdba'
$ sqlplus rman/rman@catdb
Test that you’re connecting to the correct SID/service_name and the hostname by executing
the tnsping command for both the prod and rman connections.
9. Connect to the production database (target) and the auxiliary instance, and run the
RMAN duplicate command for the standby database, as shown here (again, you’ll be
running these commands on the standby database server):
$ export ORACLE_SID=PROD
$ rman target sys/sys@PROD auxiliary /
RMAN> run {
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
duplicate target database for standby nofilenamecheck;
}

Duplicating a Database to a Past Point in Time

RMAN> connect target sys/@targdb
RMAN> connect auxiliary sys/RMAN> duplicate target database
to dupdb
spfile
nofilenamecheck
until time 'sysdate-1';

run
{
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk
set until time "to_date('July 16 2007 12:00:00','Mon DD YYYY HH24:MI:SS')";
duplicate target database to aux;
}

Skipping Tablespaces During Database Duplication

run
{
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
duplicate database to aux
skip tablespace users, tools;
}

Duplicating a Database with a Specific Backup Tag

ProblemYou want to specify a particular backup tag during the duplication of a database.

Solution

You can “force” RMAN to use a specific backup during a database duplication process by simply making other backups unavailable. Here are the steps to follow:
1. Use the list backup of database command to find out the primary key of the backup
set you plan to use in the duplication process:

RMAN> list backup of database;
2. Make all the backup sets except the one you choose unavailable to RMAN during the
database duplication process by using the following command for each of the backups
you want to make inaccessible to RMAN:

RMAN> change backupset unavailable;
3. Follow the steps in recipe 15-2 or recipe 15-3 to duplicate the source database.
4. Once the database duplication is finished, make all the backups available to RMAN
again by issuing the following command for each of the backup sets you made unavailable
prior to the database duplication:

RMAN> change backupset available;
All the backup sets are once again “available” for use by RMAN.

RMAN> connect target /
RMAN> connect catalog rman/cat@catdb
RMAN> connect auxiliary sys/sammyy1@dupdb
RMAN> run {
configure auxname for datafile 1 to '/oradata1/system01.dbf';
configure auxname for datafile 2 to '/oradata2/sysaux01.dbf';
configure auxname for datafile 3 to '/oradata3/undotbs01.dbf';
configure auxname for datafile 4 to '/oradata4/drsys01';
configure auxname for datafile 5 to '/oradata5/example01.dbf';
configure auxname for datafile 6 to '/oradata6/indx01.dbf';
configure auxname for datafile 7 to '/oradata7/users01.dbf';
}
Synchronize the duplicate database with the source database by periodically executing
the duplicate target database command to re-create the duplicate database. For example:

RMAN> connect target /
RMAN> connect catalog rman/cat@catdb
RMAN> connect auxiliary sys/sammyy1@dupdb
RMAN> duplicate target database to dupdb
logfile
group 1 ('/duplogs/redo01a.log',
'/duplogs/redo01b.log') size 200k reuse,
group 2 ('/duplogs/redo02a.log',
'/duplogs/redo02b.log') size 200k reuse;

SEARCHING FOR ERRORS IN THE ALERT.LOG FILE

SEARCHING FOR ERRORS IN THE ALERT.LOG FILE

Here’s a simple Unix Korn shell script that greps for “ORA-” and “ERROR” in the alert.log file. Before you run this shell script, you’ll have to change the setup variables at the top to match your environment. The DBS variable stores the Databases on a box. The ADIR variable stores the base directory for the database. The
DIRS variable stores the actual directories that you want to search for the Alert.log file. The MAILLIST variable

stores the email address to where the error findings will be sent.


#!/bin/ksh
export DBS="PATCH50 PATCH60"
export ADIR="/ora01/app/oracle/admin"
export DIRS="bdump udump cdump"
export MAILLIST=" uncleLarry@oracle.com "
export BOX=`uname -a | awk '{print$2}'`
export MAILX="/usr/ucb/Mail"

#-----------------------------------------------------------
for instance in $DBS
do
for directories in $DIRS
do
if [ -r $ADIR/$instance/$directories/alert*.log ]
then
grep -ic error $ADIR/$instance/$directories/alert*.log
if [ $? = 0 ]
then
$MAILX -s "Error in $instance log file" $MAILLIST <<'EOF'
Error in $instance log file on $BOX...
Check $ADIR/$instance/$directories/alert*.log
EOF
fi # $?
grep -ic ORA- $ADIR/$instance/$directories/alert*.log
if [ $? = 0 ]
then
$MAILX -s "ORA- Error in $instance log file" $MAILLIST <<'EOF'
Error in $instance log file on $BOX...
Check $ADIR/$instance/$directories/alert*.log
EOF
fi # $?
fi # -r
done # for directories
done # for instance
exit

viernes, 25 de septiembre de 2009

RMAN Redo Log Failures

Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_lgwr_5800.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01B.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Query V$LOG and V$LOGFILE views to determine the status of your log group and the
member files in each group:

SQL> select
2 a.group#, a.thread#,
3 a.status grp_status,
4 b.member member,
5 b.status mem_status
6 from v$log a,
7 v$logfile b
8 where a.group# = b.group#
9 order by a.group#, b.member


V$LOG Displays the online redo log group information stored in the control file.
V$LOGFILE Displays online redo log file member information.

Status Meaning

CURRENT The log group that is currently being written to by the log writer.
ACTIVE The log group is required for crash recovery and may or may not have
been archived.
CLEARING The log group is being cleared out by an alter database clear
logfile command.
CLEARING_CURRENT The current log group is being cleared of a closed thread.
INACTIVE The log group isn’t needed for crash recovery and may or may not have
been archived.
UNUSED The log group has never been written to; it was recently created.

Table 14-4. Status for Online Redo Log File Members in the V$LOGFILE View
Status Meaning

INVALID The log file member is inaccessible, or it has been recently created.
DELETED The log file member is no longer in use.
STALE The log file member’s contents are not complete.
NULL The log file member is being used by the database.

If the failed member is in the current log group, then use the alter system switch
logfile command to make the next group the current group. Then drop the failed member
as follows:

SQL> alter database drop logfile member '<\directory\member>';

SQL> alter database add logfile member '<\new directory\member>' to group ;


To recover when you’ve lost all members of an inactive redo log group, perform the following steps:

1. Verify that all members of a group have been damaged.
2. Verify that the log group status is INACTIVE.
3. Re-create the log group with the clear logfile command.
4. If the re-created log group has not been archived, then immediately back up your
database.


SQL> connect / as sysdba
SQL> startup mount;

Next, run the following query to verify that the damaged log group is INACTIVE and
determine whether it has been archived:

SQL> select group#, status, archived, thread#, sequence# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE#
------ -------- --- ------- ----------
1 INACTIVE YES 1 44
3 INACTIVE YES 1 45
2 CURRENT NO 1 46


If the status is INACTIVE, then this log group is no longer needed for crash recovery , you can use the clear logfile command to re-create all
members of a log group. The following example re-creates all log members of group 1:

SQL> alter database clear logfile group 1;

If the log group has not been archived, then you will need to use the clear unarchived logfile command as follows:

SQL> alter database clear unarchived logfile group 1;

SQL> select group#, status, archived, thread#, sequence# from v$log;

SQL> alter system checkpoint;

If the status is inactive and the log has been archived, you can use the clear logfile command to re-create the log group, as shown here:

SQL> alter database clear logfile group ;

If the status is inactive and the log group has not been archived, then re-create it with the clear unarchived logfile command, as shown here:

SQL> alter database clear unarchived logfile group ;

Recovering After Loss of All Members of the CURRENT Redo Log Group

Problem


All of the members of a current online redo log group in your database have experienced media failure.

SQL> shutdown immediate;
SQL> startup mount;
SQL> select group#, status, archived, thread#, sequence#, first_change# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
------ -------- --- ------- ---------- -------------
1 INACTIVE YES 1 50 1800550
2 INACTIVE YES 1 49 1800468
3 CURRENT NO 1 51 1800573

RMAN> restore database until scn 1800573;
RMAN> recover database until scn 1800573;
RMAN> alter database open resetlogs;

RMAN Performing Flashback Recovery

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');

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;