ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

RMAN duplicate db fails with ORA-06502

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/02/2017 10:02:21
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

RMAN> exit

solution

1. remove db_unique_name parameter from init.ora
    and retry the duplicate. you can set the db_unique_name later once the duplicate complets fine. 

2. cleanup the catalog if required.
    remove the dbunique name related to duplicate work

oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> rman target / catalog rman/passs@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 10:01:55 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TestPG (DBID=1734336466)
connected to recovery catalog database
re
RMAN> sync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
5548648 5548649 TestPG  1734336466       CURRENT 1          31-JAN-14
5548648 42491323 TestPG  1734336466       ORPHAN  11928117675339 12-MAY-16

RMAN> exit


Recovery Manager complete.
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> sqlplus rman@Rmancatalog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 10:04:11 2017

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648;

  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14
TestPG_DG1

   5560736    5548648 STANDBY 09/03/15
TestPG_DG2

  42491321    5548648 STANDBY 12/05/16
TestPG_DG3


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  40708303    5548648 STANDBY
TESTSG

  46494327    5548648 STANDBY
TESTSG1

  46265758    5548648 STANDBY
TESTSG_DG1_MDR


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  42469535    5548648 STANDBY
TESTT4

  35485732    5548648 STANDBY
TESTT9


8 rows selected.

SQL> set linesize 200
SQL> 1
  1* select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
SQL> /

  SITE_KEY     DB_KEY DATABAS CF_CREAT DB_UNIQUE_NAME
---------- ---------- ------- -------- ------------------------------------------------------------------------------------------------------------------------
  40708303    5548648 STANDBY          TESTSG
  46494327    5548648 STANDBY          TESTSG1
  35485732    5548648 STANDBY          TESTT9
  46265758    5548648 STANDBY          TESTSG_DG1
  42491321    5548648 STANDBY 12/05/16 TestPG_DG3
   5560736    5548648 STANDBY 09/03/15 TestPG_DG2
  42469535    5548648 STANDBY          TESTT4
   5548650    5548648 PRIMARY 31/01/14 TestPG_DG1

8 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback




Remove the db unique names which is not related to source database. These entries are created whenever we duplicate the database and it got failed.
 
> rman target / catalog rman/passs@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 10:05:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TestPG (DBID=1734336466)
connected to recovery catalog database

RMAN> unregister db_unique_name TESTT4;

database db_unique_name is "TESTT4", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT4 unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTSG;

database db_unique_name is "TESTSG", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTSG unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTSG1
2> ;

database db_unique_name is "TESTSG1", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTSG1 unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTT9;

database db_unique_name is "TESTT9", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT9 unregistered from the recovery catalog

RMAN> exit


Recovery Manager complete.
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> sqlplus rman@Rmancatalog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 10:07:42 2017

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL>  select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
  2  /

  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14
TestPG_DG1_MDR

   5560736    5548648 STANDBY 09/03/15
TestPG_DG2_HTC

  42491321    5548648 STANDBY 12/05/16
TestPG_DG3_MDR


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  46265758    5548648 STANDBY
TESTSG_DG1_MDR


SQL> set linesize 200
SQL> 1
  1*  select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
SQL> /

  SITE_KEY     DB_KEY DATABAS CF_CREAT DB_UNIQUE_NAME
---------- ---------- ------- -------- ------------------------------------------------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14 TestPG_DG1
   5560736    5548648 STANDBY 09/03/15 TestPG_DG2
  42491321    5548648 STANDBY 12/05/16 TestPG_DG3
  46265758    5548648 STANDBY          TESTSG_DG1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
oracle@oravm1[TestPG]:/