Monday, December 15, 2014

How to solve "ORA-06502: PL/SQL: numeric or value error" when cloning a database

While attempting to clone an Oracle 11gR2 production database, Oracle returned "ORA-06502: PL/SQL: numeric or value error".

The clone proceeded nicely up until RMAN attempted to set the db_unique_name. After this point, the clone failed, see error message output below:
Oracle instance started

Total System Global Area    8551575552 bytes

Fixed Size                     2263488 bytes
Variable Size               1107297856 bytes
Database Buffers            7415529472 bytes
Redo Buffers                  26484736 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
   ''testdb01'' comment=
   ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}

executing Memory Script

sql statement: alter system set  db_name =  ''testdb01'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down


RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/11/2014 13:20:45
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error
The trace file also adds the following line to the previous lines:
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5567

The culprit seems to be that my production database and the auxiliary database had at some point in the past been part of a Data Guard setup.
By looking at the registered databases in the recovery catalog, this could indeed be verified:

rman target / catalog uid/pwd@rmancat 

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 15 11:33:55 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: proddb01 (DBID=3046916437)
connected to recovery catalog database

RMAN> set echo on;
2> list db_unique_name of database;
4> exit
echo set on
List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
133048143 proddb01     3046916437       PRIMARY          proddb01
133048143 proddb01     3046916437       STANDBY          testdb01

Solution:
In this case, this old association to an obsolete standby database could be removed altogether.
I therefore unregistered the unique database from the recovery catalog:
RMAN> unregister db_unique_name 'testdb01';

database db_unique_name is "testdb01", db_name is "proddb01" and DBID is 3046916437

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

After this was done, I restarted the clone, and the error was no longer thrown and the script continued as expected.

If you are in a slightly different position, and have several primary databases with the same unique name, you need a different approach. Consider the following example:
RMAN> list db_unique_name of database;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
376254740 PDB01    3895530761       PRIMARY          PDB01
380374509 PDB01    3902464629       PRIMARY          PDB01
383100641 PDB01    3906391689       PRIMARY          PDB01

RMAN> SET DBID 3895530761;

executing command: SET DBID
database name is "PDB01" and DBID is 3895530761

RMAN> UNREGISTER DB_UNIQUE_NAME PDB01;

database db_unique_name is "PDB01", db_name is "PDB01" and DBID is 3895530761

Want to unregister the database with target db_unique_name (enter YES or NO)? yes

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20244: can not change currently connected database db_unique_name
RMAN> exit

Solution is to still explicitly set DBID, but then execute "unregister database" instead, like this:

RMAN> unregister database;

database name is "PDB01" and DBID is 3895530761

Do you really want to unregister the database (enter YES or NO)? yes

database unregistered from the recovery catalog

RMAN> SET DBID 3902464629;
executing command: SET DBID
database name is "PDB01" and DBID is 3902464629 

RMAN> unregister database;
database name is "PDB01" and DBID is 3902464629

Do you really want to unregister the database (enter YES or NO)? YES

RMAN> list db_unique_name of database;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name

------- ------- ----------------- ---------------  ------------------
383100641 PDB01    3906391689       PRIMARY          PDB01

We are left with only one unique db name, which is what we want.

4 comments:

  1. Thanks vegard, today I faced similar issue and it fixed by your solution. Thanks a lot.

    ReplyDelete
  2. No problem, glad it helped you. I actually added a few lines to this post, as I today faced a similar issue, where the db_unique_name and the database Role were identical. In that case, you need a slightly different syntax.
    Cheers
    V

    ReplyDelete
  3. Simple and precise, Appreciate your time and effort.
    Thanks, Syed

    ReplyDelete