oraclednaThis post is to demonstrate how to overcome ORA-30012 error in pluggable database.
ORA-30012: undo tablespace ‘UNDOTBS1’ does not exist or of wrong type.
Environment=
============
CDB : PROD
PDB: PRODPDB1
Undo tablespace of PDB: UNDOTBS1
DB Version: 19.27
1. Change the UNDO_MANAGEMENT to MANUAL in CDB$ROOT.
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 2365584784 bytes
Fixed Size 9180560 bytes
Variable Size 520093696 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7593984 bytes
Database mounted.
Database opened.
2. Open the PDB DB PRODPDB1.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 MOUNTED
SQL> ALTER PLUGGABLE DATABASE PRODPDB1 OPEN;
Pluggable database altered.
SQL> show parameter undo;
NAME TYPE VALUE
———————————— ———– ——————————
temp_undo_enabled boolean FALSE
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> ALTER SESSION SET CONTAINER = PRODPDB1;
Session altered.
TABLESPACE_NAME AUT MAX_TS_SIZE MAX_TS_PCT_USED CURR_TS_SIZE USED_TS_SIZE TS_PCT_USED FREE_TS_SIZE TS_PCT_FREE
—————————— — ———– ————— ———— ———— ———– ———— ———–
SYSTEM YES 32767.98 1.45 480 474.13 98.78 5.88 1
SYSAUX YES 32767.98 1.36 470 444.06 94.48 25.94 6
TEMP YES 32767.98 .6 201 198 98.51 3 1
NEW_UNDO_TBS NO 500 .45 500 2.25 .45 497.75 100
USERS YES 32767.98 0 5 1 20 4 80
3. Create undo tablespace UNDOTBS1 in PDB DB PRODPDB1.
SQL> create undo tablespace UNDOTBS1 datafile ‘/u01/app/oracle/oradata/PROD/prodpdb1/undotbs1_02.dbf’ size 500m;
Tablespace created.
4. Change the UNDO_MANAGEMENT to AUTO and bounce the CDB$ROOT container.
SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.
SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 2365584784 bytes
Fixed Size 9180560 bytes
Variable Size 520093696 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7593984 bytes
Database mounted.
Database opened.
5. Open the PDB DB.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 MOUNTED
SQL> alter pluggable database PRODPDB1 open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PRODPDB1 READ WRITE NO
SQL> ALTER SESSION SET CONTAINER = PRODPDB1;
Session altered.
SQL> show con_name
CON_NAME
——————————
PRODPDB1
Now, able to open the pluggable database.
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
