錯誤初始化引數導致無法啟動的解決辦法

在使用spfile系統引數檔案時，有時會碰到修改初始化引數後系統無法啟動的問題，下面給出幾個解決方案,建議使用方案二。

0，無法啟動場景
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfileemrep.ora
SQL> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150
SQL> alter system set processes=50000 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-04031: unable to allocate 1480 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","procs: ksunfy")

 

1,方法一：利用引數檔案中引數可以設定多次,以最後一個為準的特性:
[oracle@test dbs]$ cat initemrep_t.ora
spfile=/u01/app/oracle/product/10.2.0/db_1/dbs/spfileemrep.ora
processes=150

SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initemrep_t.ora
ORACLE instance started.

Total System Global Area  562036736 bytes
Fixed Size                  1262936 bytes
Variable Size             314575528 bytes
Database Buffers          239075328 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfileemrep.ora
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance

--這時可以使用create pfile from spfile生成pfile,但processes=50000

--修改
SQL> alter system set processes=150 scope=spfile;

System altered.

--重新啟動
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  562036736 bytes
Fixed Size                  1262936 bytes
Variable Size             318769832 bytes
Database Buffers          234881024 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfileemrep.ora

 

2,方法二：使用spfile重新生成pfile，修改後再生成spfile。

--雖然沒有啟動，但可以生成pfile
SQL> create pfile from spfile;

File created.

--使用vi修改新生成pfile中的processes=150

--重新啟動
SQL> startup pfile=?/dbs/initemrep.ora
ORACLE instance started.

Total System Global Area  562036736 bytes
Fixed Size                  1262936 bytes
Variable Size             310381224 bytes
Database Buffers          243269632 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150
SQL> create spfile from pfile;

File created.

3，方法三：用strings命令將spfile中的引數匯出生成pfile，修改引數，並使用pfile啟動在生成spfile。
strings spfileemrep.ora >initemrep.ora

--修改processes=150

SQL> startup pfile=?/dbs/initemrep.ora
ORACLE instance started.

Total System Global Area  562036736 bytes
Fixed Size                  1262936 bytes
Variable Size             322964136 bytes
Database Buffers          230686720 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  562036736 bytes
Fixed Size                  1262936 bytes
Variable Size             322964136 bytes
Database Buffers          230686720 bytes
Redo Buffers                7122944 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfileemrep.ora
SQL> show parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
job_queue_processes                  integer     10
log_archive_max_processes            integer     2
processes                            integer     150