Known RMAN Performance Problems [ID 247611.1]

Anyone who wants to improve the RMAN backup/recovery performance problems I strongly recommend to read the below Metalink ID: This is very useful and simple. "ALL known issues documented at one place"


Known RMAN Performance Problems [ID 247611.1]

gather stats on fixed database objects

Latching on large fixed objects is expensive, so without proper statistics, performance degradation is expected when sub-optimal plans happen.(as in some cases, there are instance wide effects, such as effectively serializing access to the shared pool) This is more prevalent in very large, or very busy systems, as the number of times the latch is requested, and/or the length of time the latch is held, will increase with load and volume in the X$ being queried through the views.

Fixed object statistics will need to be gathered under representative load and you may experience performance degradation while the statistics are gathering.

There are some cases where having no statistics on a fixed object may produce the best plans, but in general, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on. In general, in the case of these volatile fixed tables, better plans are achieved by gathering statistics on these tables than by not gathering statistics.

SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

OWNER TABLE_NAME LAST_ANAL
------------------------------ ------------------------------ ---------
SYS X$KGLDP

SQL> exec dbms_stats.gather_fixed_objects_stats();

PL/SQL procedure successfully completed.


SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KGLDP';

OWNER TABLE_NAME LAST_ANALYZED
------------------------------ ------------------------------ -------------------
SYS X$KGLDP 07/1/2010 11:09:54

Size of the local individual index partitions

If you know the index name then go to PART-2 if not use PART-1 to get the index name using table name

PART-1
First find the index names for the given table and partition names:
select DISTINCT ''''||A.INDEX_NAME||''''||',' FROM dba_ind_partitions A,
BA_PART_INDEXES B
WHERE
A.INDEX_OWNER=B.OWNER
AND A.INDEX_NAME=B.INDEX_NAME
AND A.INDEX_OWNER='ISA' AND
B.TABLE_NAME IN
('ADJUSTMENT_REASON_CODE_PRE')
AND A.PARTITION_NAME IN
('P_CHARLOTTE')

PART-2:

select SEGMENT_NAME,PARTITION_NAME,sum(bytes)/1024/1024 size_in_mb
FROM DBA_SEGMENTS
WHERE owner='USER1'
AND SEGMENT_name in ('INDEX1')
AND SEGMENT_TYPE='INDEX PARTITION'
AND PARTITION_NAME IN
('P_CHARLOTTE')
GROUP BY SEGMENT_NAME,PARTITION_NAME
ORDER BY 1,2

Restore older version db on a newer version binaries

Oracle Says yes:

1) Can I restore or duplicate my previous version database using a later version of Oracle?

For example, is it possible to restore a 9i backup while using the 10g executables?

It is possible to use the 10.2 RMAN executable to restore a 9.2 database even if the restored datafiles will be stored in ASM. RMAN is configured so that a higher release is able to restore a lower release, but it is strongly suggested you use only the same version. In this scenario for a restore (not RMAN duplicate) you will be using the restored 9.2 database against 10.2 binaries. If you intend to run the restored 9.2 database against the 10.2 binary files you need to run the upgrade steps to make the restored 9.2 database a 10.2 database, but understand that the pre-upgrade checklist which you may have submitted against the source 9.2 database does NOT apply to this restored 9.2 database.

Once the restore / recovery is completed you will need to open the database with a combination of the resetlogs option and the upgrade option. For example:

SQL> alter database open resetlogs upgrade;
After the database is opened in this fashion run the upgrade steps for the higher version. If you have any problems with any part of this process please open a service request. If you are restoring using backups created by an earlier release, some time after you have upgraded the database, then media recovery will re-play the changes that were made during the upgrade, and you do not need to run the upgrade scripts again.

Restore database with SPFILE and Controlfile

when SPFILE and controlfile backup location are not available or not sure then do the following to do the restore of controlfile and spfile:
but YOU SHOULD KNOW the DBID

when no init.ora or spfile available
use RMAN
>export ORACLE_SID=ABC
>rman

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jun 30 14:16:48 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/UNIX/u0001/oracle/product/10.2.0.1/db_1/dbs/initABC.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 1977112 bytes
Variable Size 71308520 bytes
Database Buffers 79691776 bytes
Redo Buffers 6406144 bytes

now restoring the controlfile:

'set controlfile autobackup format' is only necessary if the controlfile autobackup is in a NON default location – set accordingly

RMAN> set dbid=123456789012;
run {
set controlfile autobackup format for device type disk to '\%F'; -----if disk else change type to SBT if TAPE
restore spfile from autobackup;
restore controlfile from autobackup;
startup force mount; --startup FORCE mount' is needed because the instance was originally started without an spfile
}

once DB is mounted .. list the backup files and you all set with restore and recover commands
RMAN> list backup summary;