oracle數(shù)據(jù)庫(kù)起不來(lái)，內(nèi)容如下，求解！

counter1219

SQL> alter system set undo_tablespace=undotbs9 scope=both;
alter system set undo_tablespace=undotbs9 scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 18970
Session ID: 885 Serial number: 5


SQL>  startup upgrade;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL>


oracle@linux:/oracle/app/product/11g/db> oerr ora 01092
01092, 00000, "ORACLE instance terminated. Disconnection forced"
// *Cause:  The instance this process was connected to was terminated
//          abnormally, probably via a shutdown abort. This process
//          was forced to disconnect from the instance.
// *Action: Examine the alert log for more details. When the instance has been
//          restarted, retry action.
oracle@linux:/oracle/app/product/11g/db>


alert日志如下：Thread 1 advanced to log sequence 11334 (thread open)
Thread 1 opened at log sequence 11334
  Current log# 3 seq# 11334 mem# 0: /oracle/app/oradata/ora11g/redo03.log
Successful open of redo thread 1
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC0: Becoming the heartbeat ARCH
SMON: enabling cache recovery
Errors in file /oracle/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_17155.trc:
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Error 30012 happened during db open, shutting down database
USER (ospid: 17155): terminating the instance due to error 30012
Instance terminated by USER, pid = 17155
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORA-1092 : opiodr aborting process unknown ospid (17155_140602872239872)
Fri Apr 26 19:48:12 2013
ORA-1092 : opitsk aborting process
Fri Apr 26 20:06:23 2013
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =97
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side spfile /oracle/app/product/11g/db/dbs/spfileora11g.ora
System parameters with non-default values:
  processes                = 800
  sga_max_size             = 8G
  sga_target               = 8G
  memory_target            = 12G
  memory_max_target        = 12G
  control_files            = "/oracle/app/oradata/ora11g/control01.ctl"
  control_files            = "/oracle/app/oradata/ora11g/control02.ctl"
  control_files            = "/oracle/app/oradata/ora11g/control03.ctl"
  db_block_size            = 8192
  compatible               = "11.1.0.0.0"
  log_archive_dest_1       = "location=/oracle/app/arch/dzh"
  log_buffer               = 3145728
  log_checkpoint_interval  = 0
"alert_ora11g.log" 1712721L, 83931597C                                                                            1712579,1     99%
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Fri Apr 26 20:07:25 2013
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 7 processes
Started redo scan
Completed redo scan
1 redo blocks read, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 11334, block 2, scn 93344370
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11334 Reading mem 0
  Mem# 0: /oracle/app/oradata/ora11g/redo03.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 11334, block 3, scn 93364372
0 data blocks read, 0 data blocks written, 1 redo blocks read
Fri Apr 26 20:07:27 2013
LGWR: STARTING ARCH PROCESSES
Fri Apr 26 20:07:27 2013
ARC0 started with pid=27, OS id=19039
Fri Apr 26 20:07:27 2013
ARC1 started with pid=28, OS id=19041
Fri Apr 26 20:07:27 2013
ARC2 started with pid=29, OS id=19043
ARC0: Archival started
Fri Apr 26 20:07:27 2013
ARC3 started with pid=30, OS id=19045
ARC1: Archival started
ARC2: Archival started
ARC3: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC3: Becoming the 'no FAL' ARCH
ARC3: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Archived Log entry 85 added for thread 1 sequence 11332 ID 0xf94b721e dest 1:
Archived Log entry 86 added for thread 1 sequence 11333 ID 0xf94b721e dest 1:
Thread 1 advanced to log sequence 11335 (thread open)
Thread 1 opened at log sequence 11335
  Current log# 1 seq# 11335 mem# 0: /oracle/app/oradata/ora11g/redo01.log
Successful open of redo thread 1
Fri Apr 26 20:07:29 2013
SMON: enabling cache recovery
Errors in file /oracle/app/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_18970.trc:
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Error 30012 happened during db open, shutting down database
USER (ospid: 18970): terminating the instance due to error 30012
Instance terminated by USER, pid = 18970
ORA-1092 signalled during: alter database open...
ORA-1092 : opiodr aborting process unknown ospid (18970_140278976595712)
Fri Apr 26 20:07:31 2013
ORA-1092 : opitsk aborting process
                                                                     

counter1219

其中，'UNDOTBS1'  表空間我剛剛已經(jīng)drop成功了，我現(xiàn)在重建了undotbs9  表空間，
SQL> alter system set undo_tablespace=undotbs9 scope=both;
alter system set undo_tablespace=undotbs9 scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01219: database not open: queries allowed on fixed tables/views only

counter1219

startup force也啟動(dòng)不了，要怎么啟動(dòng)，才能把默認(rèn)的undo表空間，修改為undotbs9 這個(gè)表空間？
SQL> startup force;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2176808 bytes
Variable Size            2382366936 bytes
Database Buffers         6157238272 bytes
Redo Buffers                9793536 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 20077
Session ID: 885 Serial number: 5


SQL>

vcdog

1.先查看一下，你新創(chuàng)建的undo表空間的名字：
SQL> select name from v$tablespace where name like 'UNDO%';

2.修改 $ORACLE_HOME/dbs/init$ORACLE_SID.ora

設(shè)置*.undo_tablespace='第1步查詢出來(lái)的undo表空間的名字'

3.重新啟動(dòng)

SQL> shutdown;
SQL> startup mount;
SQL> alter database open;
SQL> show parameter undo;

4.問(wèn)題解決

祝你好運(yùn)。

counter1219

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

NAME
------------------------------
UNDOTBS9
修改了spfile<sid>.ora文件之后，重啟，如下：查看表空間還是使用了UNDOTBS1這個(gè)表空間啊，
SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 1.6769E+10 bytes
Fixed Size                  2176400 bytes
Variable Size            9294580336 bytes
Database Buffers         7449083904 bytes
Redo Buffers               23277568 bytes
Database mounted.
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      UNDOTBS1
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 4461
Session ID: 885 Serial number: 5


SQL>

counter1219

問(wèn)題解決了，我自己說(shuō)下解決方法吧：
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_tablespace                      string      UNDOTBS1
上面undo表空間還是undotbs1的原因是，startup啟動(dòng)的還是使用原理的spfile文件，現(xiàn)在需要，先用修改過(guò)的pfile文件啟動(dòng)，具體步驟如下：
1，create pfile='$path/ora.ora' from spfile；
vi pfile
*.undo_tablespace='UNDOTBS9'

2，shutdown ;/shutdown immediate;/shutdown abort;
3,startup pfile='$path/ora.ora';
4,create spfile from pfile=$path/file.name ;
5,shutdown;
6,startup;
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     100
undo_tablespace                      string      UNDOTBS9
SQL> alter system set undo_management='AUTO' scope=spfile;

System altered.
SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL     修改之后需要重啟才能生效。
undo_retention                       integer     100
undo_tablespace                      string      UNDOTBS9
SQL> shutdown immediate;
Database closed.
Database dismounted.

SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size                  2176808 bytes
Variable Size            2382366936 bytes
Database Buffers         6157238272 bytes
Redo Buffers                9793536 bytes
Database mounted.
Database opened.

counter1219

忘了說(shuō)修改方式了：
alter system set undo_management='AUTO' scope=spfile;