原因：由于机器使用阿里云服务器，初期内存较大，所以选择中途降配，由8G减少到4G，降配完成后，数据库无法正常启动，报以下错误
ORA-00845: MEMORY_TARGET not supported on this system
经过查资料了解其原理后
发现：
原因可能：ORACLE MEMORY_TARGET参数设置超过了oracle服务器本身内存、或超过了现在服务器空闲内存（就是oracle启动时无法获取设置的内存），或者 超过了/dev/shm的大小/. 这些都有可能造成以上错误、
问题确认：
确认为oracle MEMORY_TARGET参数设置SGA大于操作系统的空闲内存，超过了/dev/shm的大小
解决：
修改MEMORY_TARGET的值为合适的范围之内（如果是正式环境切oracle需要设置MEMORY_TARGET的内存，需要增加服务器内存，本初不讨论）
1.备份现有spfile文件
|
1
|
cp
init.ora.715201510237 init.ora.715201510237_bak
|
2.使用spfile创建pfile（spfile为二进制文本，不可直接修改），修改pfile后，重新生成spfile
|
1
2
|
SQL>
create
pfile
from
spfile;
File created.
|
修改pfile参数*.memory_target=104857600(此处的值小于 操作系统的空闲内存，小于/dev/shm的大小)
|
1
2
3
|
SQL>
create
spfile
from
pfile;
File created.
确认后，启动oracle
|
3，如果以上完成后，还无法正常startup,
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
SQL> startup pfile=/app/oracle/admin/orcl/pfile/init.ora.103201271612
SQL>
alter
system
set
memory_max_target=1258M scope=both;
alter
system
set
memory_max_target=1258200M scope=both
*
ERROR
at
line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
alter
system
set
memory_max_target=1258M scope=spfile;
alter
system
set
memory_max_target=1258M scope=spfile
*
ERROR
at
line 1:
ORA-32001: write
to
SPFILE requested but
no
SPFILE
is
in
use//spfile文件可能不存在
SQL> shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE instance shut down
SQL> startup nomount
ORA-01078: failure
in
processing system parameters
LRM-00109: could
not
open
parameter file
'/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora'
#
------------------------------spfile不存在
#
-----------------------------spfile位置：$ORACLE_HOME/dbs/init[ORACLE_SID].ora
#
-----------------------------名字格式为:initORACLE_SID.ora
[oracle@h1 dbhome_2]$ cd /app/oracle/admin/orcl/pfile/
[oracle@h1 pfile]$ ls
init.ora.103201271612
[oracle@h1 pfile]$echo $ORACLE_SID
orcl
#
-------------------查看环境变量$ORACLE_SID
[oracle@h1 pfile]$ cp init.ora.103201271612 /app/oracle/product/11.2.0/dbhome_2/dbs
[oracle@h1 pfile]$ cd /app/oracle/product/11.2.0/dbhome_2/dbs
[oracle@h1 dbs]$ mv init.ora.103201271612 initorcl.ora
#
------------------------------更改名字格式为:initORACLE_SID.ora
|
4.mount数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
[oracle@h1 dbs]$ sqlplus
"/as SYSDBA"
SQL*Plus: Release 11.2.0.1.0 Production
on
Sun Nov 4 16:59:09 2012
Copyright (c) 1982, 2009, Oracle.
All
rights reserved.
Connected
to
an idle instance.
SQL> startup mount
ORACLE instance started.
Total System
Global
Area 1219260416 bytes
Fixed
Size
2212856 bytes
Variable
Size
738200584 bytes
Database
Buffers 469762048 bytes
Redo Buffers 9084928 bytes
Database
mounted.
SQL>
SQL> show parameter memory_target;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big
integer
1168M
SQL> show parameter memory_max_target;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big
integer
1168M
SQL>
SQL>
alter
system
set
memory_max_target=1G scope=spfile;
alter
system
set
memory_max_target=1G scope=spfile
*
ERROR
at
line 1:
ORA-32001: write
to
SPFILE requested but
no
SPFILE
is
in
use
SQL> show parameter spfile;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
#
------------------重建spfile
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 1219260416 bytes
Fixed
Size
2212856 bytes
Variable
Size
738200584 bytes
Database
Buffers 469762048 bytes
Redo Buffers 9084928 bytes
Database
mounted.
Database
opened.
SQL> show parameter spfile;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /app/oracle/product/11.2.0/dbh
ome_2/dbs/spfileorcl.ora
SQL>
alter
system
set
memory_max_target=1G scope=spfile;
System altered.
SQL>
alter
system
set
memory_target=1G scope=both;
System altered.
SQL>
alter
system
set
memory_max_target=1332M scope=both;
alter
system
set
memory_max_target=1332M scope=both
*
ERROR
at
line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL>
alter
system
set
memory_max_target=1332M scope=spfile;
System altered.
-------------------------------
SQL> shutdown immediate
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System
Global
Area 1402982400 bytes
Fixed
Size
2213296 bytes
Variable
Size
922749520 bytes
Database
Buffers 469762048 bytes
Redo Buffers 8257536 bytes
Database
mounted.
Database
opened.
SQL> show parameter memory_max_target;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big
integer
1344M
SQL> show parameter memory_target;
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big
integer
1G
SQL>
-----------------------finish
|
本文转自crazy_charles 51CTO博客，原文链接：http://blog.51cto.com/douya/1753362
，如需转载请自行联系原作者
