Wednesday, July 6, 2011

ORA-32012

You may get error ORA-32012 if you are on 11g, but have the compatible parameter set to pre-11g in the database you are cloning from, and the spfile for the source database is stored in ASM when you do an RMAN duplicate from active database.

To get around this error, I have found two workarounds:

1) Set the value for compatible to at least 11.1
2) Skip transferring of the spfile during the clone process.

Option 1 is a big change for the database (it affects the CBO among other things), but say you need a clone and are going to change this parameter anyway it is the simplest one since you don't have to create the parameterfile manually. Option 2 means you create the spfile before the cloning starts and remove the clause with spfile from the duplicate command.

An example of duplicate for option 1 is:

run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to FOOTEST
from active database
spfile
parameter_value_convert='FOOPROD','FOOTEST'
set compatible='11.1.0.0'
set diagnostic_dest='/u01/app/oracle'
;
}


An example for option 2:



run {
set newname for datafile 1 to NEW ;
set newname for datafile 2 to NEW ;
set newname for datafile 3 to NEW ;
set newname for datafile 4 to NEW ;
set newname for datafile 5 to NEW ;
set newname for datafile 6 to NEW ;
set newname for tempfile 2 to NEW ;
duplicate target database to WINTIDST
from active database;
}


It looks as the transfer of spfile from ASM to normal file system is perceived as a downgrade, though there is no change when compatible is 10.2.3 on both databases. The format of the spfile in ASM seems to be different when stored in ASM as explained in Database Administrator's Guide 11g. The change from HARD-enabled storage to normal is not supported then if you are below 11g. Makes sense when one thinks about it, but I did a few tests back and forth to understand the connection, and it looks as if this new format was introduced in 11g. Exactly what format is used for the spfile in ASM is not clear. The ASM installation in this case is on version 11.2.0.2 and the database is on 11.1.0.7 (with the compatible left on 10.2.0.3 for some unknown reason). Maybe the new format is used when the database is on version 11g even though the compatible parameter is pre-11g, if so it looks like a bug when RMAN duplicate looks only at the compatible parameter and not on version of Oracle.

When RMAN creates the spfile it will be created in $ORACLE_HOME/dbs, I could not find any way of having it created in ASM directly.

Anyway, probably not a frequent error, but decided to write it up since I didn't find much when googling.