不使用undo表空间 ORA-30046
当事务开始时，数据库给事务分配一个Undo段。如果数据库没有undo 表空间，那么会用system undo段
测试：
重建控制文件 ，把 undo数据文件去掉
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 314576176 bytes
Database Buffers 96468992 bytes
Redo Buffers 4272128 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "OGG1" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/data/ogg1/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/data/ogg1/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/data/ogg1/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/u01/apppp/oradata/system01.dbf',
14 '/data/ogg1/sysaux01.dbf',
15 '/u01/apppp/oradata/users01.dbf'
16 CHARACTER SET WE8MSWIN1252
17 ;
Control file created.
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30046: Undo tablespace UNDOTBS1 not found in control file.
Process ID: 10459
Session ID: 1 Serial number: 3
使用pfile改参数
SQL> create pfile ='/u01/pfile.ora' from spfile;
File created.
改pfile里的参数
第一次只把undo_tablespace这个参数去掉，
启动时还是报一样的错
第二次加上undo_management=MANUAL
之后 能把数据库启起来
SQL> startup pfile='/u01/pfile.ora'
ORACLE instance started.
Total System Global Area 417546240 bytes
Fixed Size 2228944 bytes
Variable Size 264244528 bytes
Database Buffers 146800640 bytes
Redo Buffers 4272128 bytes
Database mounted.
Database opened.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/apppp/oradata/users01.dbf
USERS
/data/ogg1/sysaux01.dbf
SYSAUX
/u01/apppp/oradata/system01.dbf
SYSTEM
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/apppp/db_1/dbs/MISSING00003 --undo表空间状态是online 并且还指向一个文件
UNDOTBS1
SQL> ! ls -al /u01/apppp/db_1/dbs/MISSING00003 --可是这个文件 不存在
ls: /u01/apppp/db_1/dbs/MISSING00003: No such file or directory
SQL>
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SQL>
by song
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25099483/viewspace-773510/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
