错误初始化参数导致无法启动的解决办法-CSDN博客

在使用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

--这时可以使用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

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

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

File created.

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