Archive

Archive for April, 2013

RAC Data Guard setup and management with Oracle 11gR2 (11.2.0.2)

April 9, 2013 Leave a comment

Dataguard setup for 11gr2

Guenadi N Jilevski's Oracle BLOG

RAC Data Guard setup and management with Oracle 11gR2 (11.2.0.2)

In the article we will look at Data Guard creation and management using both Oracle 11gR2 RAC as a primary and standby database. In the article OEL 5.4 and Oracle 11.2 Grid Infrastructure and RDBMS are used and ASM is used as storage. Oracle 11gR2 GI and RDBMS installation or update is a prerequisite for setup a DR primary and standby sites using RAC. The primary database is a RAC database created as described here.. The article will emphasize on creating and managing standby RAC database using sqlplus, RMAN and Data Guard Broker (dgmgrl) without OEM, whereas information related to OEM Grid Control 11g and Data Guard can be obtained here. The general approach for creating a DR standby database includes the following steps:

View original post 5,626 more words

Categories: Uncategorized

ORA-19809: limit exceeded for recovery files during rman duplicate 11g r2

April 9, 2013 1 comment

RMAN  DUPLICATE command fails during media recovery with the following error


RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 13466
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-10035: exception raised in RPC:
ORA-19583: conversation terminated due to error
ORA-19870: error while restoring backup piece /rmanprod/oradata/ukprod/ora_df812148649_s75_s1
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 2488320 bytes disk space from 53687091200 limit
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 2338
RMAN-10031: RPC Error: ORA-19583  occurred during call to DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/09/2013 09:03:04
RMAN-05501: aborting duplication of target database

RMAN fails while it is trying to restore archivelogs that will be used for media recovery. It tells you that there is not
enough space in the FRA (of the auxiliary database) to restore the archivelogs, even when the FRA for the auxiliary database is totally empty.
The problem is due to a bug which makes RMAN to take the FRA usage (ignoring the value of reclaimable space) of the target database and
compare that to FRA size of the auxiliary database.

Workaround

If you cannot have the same FRA size for the auxiliary database as the target database, then the workaround we use to solve this problem with cloning databases, is to just increase the size of the FRA (parameter db_recovery_file_dest_size) on the auxiliary database to be bigger than the used space (including reclaimable space!) of the FRA for the target database.
If you don’t have enough physical disk space available for the FRA for the auxiliary database then you can  just temporarily
increase the db_recovery_file_dest_size parameter to a value that’s bigger than the physical available space.
It is just to make RMAN happy when it checks the FRA usage and it will proceed with the media recovery. After you have finished the
clone you can resize the db_recovery_file_dest_size parameter again to its original value.

Patch

This bug is registered by Oracle support as Bug 13741583 – RMAN duplication erroneously fails with ORA-19804 using fast recovery area [ID 13741583.8].

Categories: Oracle

How to flush one Cursor out of the Shared Pool

April 5, 2013 Leave a comment

Written by Carlos Sierra

REM Flushes one cursor out of the shared pool. Works on 11g+
REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.
REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.
REM These scripts are not run by as part of standard database creation.
SPO flush_cursor_&&sql_id..txt;
PRO *** before flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/
PRO *** after flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
UNDEF sql_id;
SPO OFF;
Categories: Oracle