Fixing ORA-30012: Unable to Convert Physical Standby to Snapshot Standby

 

Introduction 

I recently encountered the frustrating "ORA-30012: undo tablespace 'UNDOTBS1' does not exist or is of wrong type" while attempting to convert my Data Guard Physical Standby into a writable Snapshot Standby for an important testing cycle. In my setup, the Primary database was a RAC running on Exadata Database Service on Dedicated Infrastructure, while the Standby was a single-instance deployment on DBCS. This mixed environment introduced subtle configuration differences, and one of those differences caused the ORA-30012 failure during the snapshot conversion. In this post, I’ll walk through how I investigated the issue, identified the undo-related mismatch on the standby, and applied the correct fix to enable the Snapshot Standby conversion successfully.

Review the Alert Log on the Standby

After I executed the CONVERT DATABASE TO SNAPSHOT STANDBY command in DGMGRL, the operation failed. 

I checked the alert log and found that ORA-30012 was the root cause of the problem.

Root cause 

I reviewed the undo tablespaces on the primary and compared them with those on the standby.

On the primary:

SQL> set line 300
col INST_ID format 99
col NAME format a30
col TYPE format 99
col VALUE format a10

select INST_ID,NAME,TYPE,VALUE from gv$parameter where name like '%undo_tablespace%';SQL> SQL> SQL> SQL> SQL> SQL>

INST_ID NAME                           TYPE VALUE
------- ------------------------------ ---- ----------
      1 undo_tablespace                   2 UNDOTBS2
      2 undo_tablespace                   2 UNDOTBS11

    
SELECT
  DT.TABLESPACE_NAME,
  DT.contents,
  DT.EXTENT_MANAGEMENT
FROM
  DBA_TABLESPACES DT,
  DBA_DATA_FILES DT2
WHERE
  DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME
  and DT.contents='UNDO';SQL>   2    3    4    5    6    7    8    9   10

TABLESPACE_NAME                CONTENTS              EXTENT_MAN
------------------------------ --------------------- ----------
UNDOTBS2                       UNDO                  LOCAL
UNDOTBS11                      UNDO                  LOCAL
UNDOTBS2                       UNDO                  LOCAL
UNDOTBS2                       UNDO                  LOCAL

On the standby:

SQL> set line 300
col INST_ID format 99
col NAME format a30
col TYPE format 99
col VALUE format a10

select INST_ID,NAME,TYPE,VALUE from gv$parameter where name like '%undo_tablespace%';SQL> SQL> SQL> SQL> SQL> SQL>

INST_ID NAME                           TYPE VALUE
------- ------------------------------ ---- ----------
      1 undo_tablespace                   2 UNDOTBS1


SELECT
  DT.TABLESPACE_NAME,
  DT.contents,
  DT.EXTENT_MANAGEMENT
FROM
  DBA_TABLESPACES DT,
  DBA_DATA_FILES DT2
WHERE
  DT.TABLESPACE_NAME= DT2.TABLESPACE_NAME
  and DT.contents='UNDO';SQL>   2    3    4    5    6    7    8    9   10

TABLESPACE_NAME                CONTENTS              EXTENT_MAN
------------------------------ --------------------- ----------
UNDOTBS2                       UNDO                  LOCAL
UNDOTBS11                      UNDO                  LOCAL
UNDOTBS2                       UNDO                  LOCAL
UNDOTBS2                       UNDO                  LOCAL

As shown, the primary uses UNDOTBS11, whereas the standby is configured with UNDOTBS1 as its default undo tablespace.

Solution

The solution is to configure UNDOTBS11 as the default undo tablespace on the standby system, ensuring it matches the primary’s configuration. To apply this change, I created a pfile from the existing spfile, removed all undo-related parameters, added the three required undo parameters, and then started the database using the modified pfile, as shown below.

[oracle@stby ~]$ sqlplus / as sysdba
SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

[oracle@stby ~]$ vi pfile.ora
--add this parameter to the end of pfile.ora file and delete other undo_management,undo_tablespace, and undo_retention lines in pfile.ora
undo_management = "AUTO"
undo_tablespace = "UNDOTBS11"
undo_retention = 10800

--startup the database using modified pfile
[oracle@stby ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 10 21:55:35 2025
Version 19.24.0.0.0

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

Connected to an idle instance.

SQL> startup pfile='/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area 1.5569E+10 bytes
Fixed Size                  8957848 bytes
Variable Size            2113929216 bytes
Database Buffers         1.3288E+10 bytes
Redo Buffers              158814208 bytes
Database mounted.
Database opened.

--Now we can see that the snapthost database is opened in read write mode
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Now, we can convert the database to a physical standby and sync it with the primary.

The database is now in physical standby mode, and I enabled MRP on the standby

The standby is getting synced with the primary

Conclusion

In summary, resolving the ORA-30012 error in a hybrid environment—where a RAC Primary on Exadata Database Service on Dedicated Infrastructure replicates to a DBCS Standby—ultimately depends on ensuring the standby’s undo configuration satisfies the requirements for snapshot activation. Once the undo issue was corrected, the standby transitioned smoothly into Snapshot Standby mode, allowing testing to proceed without impacting the production Primary. This incident highlights the importance of validating undo and snapshot prerequisites when working across Exadata Dedicated and DBCS platforms.