12c oracle 01109,开启数据库报错 ORA-01092:ORA-30012:

异地恢复数据库后开启数据库报错

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type

SQL> alter database open

2  ;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30012

Process ID: 28764

Session ID: 1705 Serial number: 5

//用pfile文件启动数据库

SQL> startup mount pfile = /u01/oracle/recoverydata/pfile_plusbpdb.ora;

ORACLE instance started.

Total System Global Area  446832640 bytes

Fixed Size            2253984 bytes

Variable Size          385878880 bytes

Database Buffers       50331648 bytes

Redo Buffers            8368128 bytes

Database mounted.

//查看undo表空间信息

SQL> show parameter undo

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

undo_management              string     AUTO

undo_retention                 integer     900

undo_tablespace              string     UNDOTBS

//查看pfile配置文件中的undo表空间

SQL> ho grep undo /u01/oracle/recoverydata/pfile_plusbpdb.ora

plusbpdb.undo_tablespace='UNDOTBS'

#plusbpdb1.undo_tablespace='UNDOTBS1'

//查看数据库中的undo表空间

SQL> select name from v$tablespace where name like '%UNDO%';

NAME

------------------------------

UNDOTBS1

UNDOTBS2

//尝试创建对应缺失的表空间信息undotbs

SQL> create undo tablespace undotbs datafile '/u01/oracle/recoverydata/undotbs_11.DBF'

2  size 500M

3  autoextend on

4  next  100M

5  extent management local;

create undo tablespace undotbs datafile '/u01/oracle/recoverydata/undotbs_11.DBF'

*

ERROR at line 1:

ORA-01109: database not open

//修改pfile配置文件中的undo表空间

SQL> ho vi /u01/oracle/recoverydata/pfile_plusbpdb.ora

plusbpdb.undo_tablespace='UNDOTBS1'

#plusbpdb1.undo_tablespace='UNDOTBS1'

"/u01/oracle/recoverydata/pfile_plusbpdb.ora" 58L, 2034C written

SQL>  ho grep undo /u01/oracle/recoverydata/pfile_plusbpdb.ora

plusbpdb.undo_tablespace='UNDOTBS1'

#plusbpdb1.undo_tablespace='UNDOTBS1'

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup mount pfile = /u01/oracle/recoverydata/pfile_plusbpdb.ora;

ORACLE instance started.

Total System Global Area  446832640 bytes

Fixed Size            2253984 bytes

Variable Size          385878880 bytes

Database Buffers       50331648 bytes

Redo Buffers            8368128 bytes

Database mounted.

SQL> show parameter undo

NAME                     TYPE     VALUE

------------------------------------ ----------- ------------------------------

undo_management              string     AUTO

undo_retention                 integer     900

undo_tablespace              string     UNDOTBS1

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

MOUNTED

SQL> alter database open;

Database altered.

创建undo表空间并修改undo表空间

SQL> create undo tablespace undotbs datafile '/u01/oracle/recoverydata/undotbs_1.DBF'

2  size 500M

3  autoextend on

4  next  100M

5  extent management local;

SQL> alter system set undo_tablspace=undotbs1 scope=both;