ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type - Common mistake during oracle database duplicate process
Common mistake during oracle duplicate process:
spfile configuration related to UNDO is not the same between source and target databases
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 11/15/2017 23:25:41
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type
Process ID: 48225
Session ID: 362 Serial number: 3
What is the issue?
- The problem is related to spfile configuration between the source database and target database
- If you access to Oracle Support, please check following Metalink Note:
(Doc ID 1536368.1) RMAN RESTORE / DUPLICATE FAILS AT OPEN (CLONE) ORA-01139 / ORA-16433, IF WRONG UNDO TABLESPACE IS SPECIFIED FOR THE RESTORE DB / or AUXILIARY
How to fix the issue?
- You need to modify spfile parameter undo_tablespace on auxiliary database
- Create backup of source database control file in case of question on this stage follow Metalink note - How to Recreate a Controlfile (Doc ID 735106.1)
- Modify information in the control files,db name of the target, redo file path, datafile path, control file should look like this
Example: - filesystem
CREATE CONTROLFILE REUSE SET DATABASE "TARGET_DB_NAME" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/V11/redo01.log' SIZE 50M,
GROUP 2 '/oradata/V11/redo02.log' SIZE 50M,
GROUP 3 '/oradata/V11/redo03.log' SIZE 50M
DATAFILE
'/oradata/V11/system01.dbf',
'/oradata/V11/sysaux01.dbf',
'/oradata/V11/undotbs01.dbf',
'/oradata/V11/user01.dbf'
CHARACTER SET AL32UTF8
;
Example: - ASM
You should list all datafiles from ASM disk group and add in the below list
CREATE CONTROLFILE REUSE SET DATABASE "TARGET_DB_NAME" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+redo1', '+redo2' ) SIZE 1000M BLOCKSIZE 512,
GROUP 2 ( '+redo1', '+redo2' ) SIZE 1000M BLOCKSIZE 512,
GROUP 3 ( '+redo1', '+redo2' ) SIZE 1000M BLOCKSIZE 512
DATAFILE
'+DATA1/target/datafile/system.2014.955840625',
'+DATA1/target/datafile/sysaux.2166.955838403',
'+DATA1/target/datafile/undotbs2.2414.955838741',
'+DATA1/target/datafile/users.2178.955837049'
CHARACTER SET AL32UTF8
;
- Recover database and open with resetlogs
- Correct undo_tablespace parameter in auxiliary pfile
- After control file recreation database will remain in mount stage
- Recover and open with resetlogs
- recover database using backup controlfile;
You will be prompted that recovery need to Apply archivelog which have been generated during the resetlog process ( this is first archivelog, you can find the name from the recovery destination )
ORA-00279: change 2023230 generated at 11/10/2017 14:27:01 needed for thread 1
ORA-00289: suggestion :
ORA-00280: change 2023230 for thread 1 is in sequence #1
Specify log: {<"RET">=suggested | filename | AUTO | CANCEL}
->>>>> add here archivelog ->>>> "FULL_PATH"/"archivelog_name"ARC
Log applied.
Media recovery complete.
After successful recovery completion you have to trigger open resetlogs
;SQL> alter database open resetlogs;
Database altered.
Have fun, i hope that this is useful
After a lot of work today, i try to get some time to share very short finding which is very useful in order to prevent the max process on ASM instance.
There was an issue with one of the newly created Oracle GI 12c - 12.1.0.2 due to massive migration and ASM instance report error ORA-00020: maximum number of processes. This problem lead to a lot of unnecessary communication for downtime in order to fix ASM instance by adding more processes :)
Today, I am going to show you how to upgrade 11.2.0.4 to 12.2.0.1 in rapid way ( you can apply the same process with 11.2.0.3 )
Maybe as start point you have to check following post where you can find all new features of the release and what is supported.
Before a few days, one of my colleagues performs the creation of a “restore point guarantee flashback database” without considering that flashback database feature is available only in Enterprise edition.
What is really annoying me is that the database accepts to create a “restore point guarantee flashback database” but you are unable to restore/flashback database. :(
Maybe there is a special reason for this or somewhere in the documentation exist cases where GRP (guarantee restore point) is necessary for Standard Edition.
( Please if you find anything related to that case – share with me )