http://www.bitscn.com/pdb/oracle/200611/82301.html
在进行代码测试时,出现
ORA-00604 递归SQL 级别 出现错误
SQL> create pfile from spfile
cat /opt/app/oracle/product/11.2.0/dbs/initracdb1.ora
看到:*.open_cursors=300
sqlplus:
select count(0) from v$open_cursor
-------------------------------------
327
说明确实 已超过了设置的值
下面就进行修改 open_cursors=300 参数
SQL> ALTER SYSTEM SET open_cursors = 500 SCOPE=SPFILE SID='*';
System altered.
SQL> ALTER SYSTEM SET processes = 200 SCOPE=SPFILE SID='*';
System altered.
#生成pfile,再查看 pfile 里面的参数是否修改过
SQL> create pfile from spfile;
cat /opt/app/oracle/product/11.2.0/dbs/initracdb1.ora
*.memory_target=7448035328
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=500
*.processes=200
#先关闭RAC2,再只使用RAC1节点使用pfile 进入数据库,再改写spfile
SQL> shutdown immediate;
#再关闭RAC1,重新使用PFILE 打开数据库
SQL> shutdown immediate;
SQL> startup pfile='/opt/app/oracle/product/11.2.0/dbs/initracdb1.ora'
show parameter open_cursors;
SQL> show parameter open_cursors;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
open_cursors integer
500
create spfile from pfile='/opt/app/oracle/product/11.2.0/dbs/initracdb1.ora';
另一节点启动出错:
SQL> startup;
ORA-01102: cannot mount database in EXCLUSIVE mode
[grid@rac2 bin]$ srvctl start database -d racdb
PRCR-1079 : 无法启动资源 ora.racdb.db
ORA-01012: 没有登录
ORA-01102: 无法在 EXCLUSIVE 模式下装载数据库
CRS-2674: 未能启动 'ora.racdb.db' (在 'rac2' 上)
CRS-2632: 没有更多符合资源 'ora.racdb.db' 的布局策略的服务器来尝试放置该资源
[grid@rac2 bin]$ srvctl start database -d racdb
SQL> show parameter cluster;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
cluster_database boolean
FALSE
cluster_database_instances integer
1
cluster_interconnects string
SQL> alter system set cluster_database=TRUE scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 7415820288 bytes
Fixed Size 2214376 bytes
Variable Size 4160751128 bytes
Database Buffers 3221225472 bytes
Redo Buffers 31629312 bytes
Database mounted.
Database opened.
SQL> show parameter cluster;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
cluster_database boolean
TRUE
cluster_database_instances integer
2
cluster_interconnects string
SQL>
#节点1 RAC无法启动了
[root@rac1 ~]# /opt/app/grid/bin/crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.
检查两个节点的VOTEDISK 是否正常
[root@rac1 ~]# /opt/app/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE aeea7277f1164f5fbf1f836e68019682 (/dev/oracleasm/disks/CRS1) [CRS]
Located 1 voting disk(s).
[root@rac2 ~]#
[root@rac2 ~]# /opt/app/grid/bin/crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE aeea7277f1164f5fbf1f836e68019682 (/dev/oracleasm/disks/CRS1) [CRS]
Located 1 voting disk(s).
[root@rac2 ~]#
ocrcheck 检查也正常
[root@rac1 ~]# /opt/app/grid/bin/ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2688
Available space (kbytes) : 259432
ID : 1716307569
Device/File Name : +CRS
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
查看 /opt/app/grid/log/rac1/alertrac1.log 日志看到:
[ohasd(5072)]CRS-2765:资源 'ora.crsd' 已失败 (在服务器 'rac1' 上)。
2013-04-03 10:27:04.860
[crsd(6295)]CRS-1013:ASM 磁盘组中的 OCR 位置不可访问。详细资料见 /opt/app/grid/log/rac1/crsd/crsd.log。
2013-04-03 10:27:05.794
[ohasd(5072)]CRS-2765:资源 'ora.crsd' 已失败 (在服务器 'rac1' 上)。
2013-04-03 10:27:04.858: [ OCRASM][986170080]proprasmo: Error in open/create file in dg [CRS]
[ OCRASM][986170080]SLOS : SLOS: cat=7, opn=kgfoAl06, dep=15077, loc=kgfokge
ORA-15077: 找不到提供所需磁盘组的 ASM 实例
查看 /opt/app/grid/log/rac1/crsd/crsd.log 看到,ASM 无法访问了。
2013-04-03 10:27:04.860: [ OCRASM][986170080]proprasmo: kgfoCheckMount returned [7]
2013-04-03 10:27:04.860: [ OCRASM][986170080]proprasmo: The ASM instance is down
2013-04-03 10:27:04.860: [ OCRRAW][986170080]proprioo: Failed to open [+CRS]. Returned proprasmo() with [26]. Marking loc
ation as UNAVAILABLE.
2013-04-03 10:27:04.860: [ OCRRAW][986170080]proprioo: No OCR/OLR devices are usable
2013-04-03 10:27:04.860: [ OCRASM][986170080]proprasmcl: asmhandle is NULL
2013-04-03 10:27:04.860: [ OCRRAW][986170080]proprinit: Could not open raw device
2013-04-03 10:27:04.860: [ OCRASM][986170080]proprasmcl: asmhandle is NULL
2013-04-03 10:27:04.861: [ OCRAPI][986170080]a_init:16!: Backend init unsuccessful : [26]
2013-04-03 10:27:04.861: [ CRSOCR][986170080] OCR context init failure. Error: PROC-26: 访问物理存储时出错 ASM 错误 [SLO
S: cat=7, opn=kgfoAl06, dep=15077, loc=kgfokge
ORA-15077: 找不到提供所需磁盘组的 ASM 实例
查看 RAC的进程情况
[root@rac1 ~]# /opt/app/grid/bin/crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
1 ONLINE OFFLINE
ora.crsd
1 ONLINE OFFLINE
ora.cssd
1 ONLINE ONLINE rac1
ora.cssdmonitor
1 ONLINE ONLINE rac1
ora.ctssd
1 ONLINE ONLINE rac1 OBSERVER
ora.diskmon
1 ONLINE ONLINE rac1
ora.drivers.acfs
1 ONLINE ONLINE rac1
ora.evmd
1 ONLINE ONLINE rac1
ora.gipcd
1 ONLINE ONLINE rac1
ora.gpnpd
1 ONLINE ONLINE rac1
ora.mdnsd
1 ONLINE ONLINE rac1
[root@rac1 ~]#
[root@rac1 ~]# /opt/app/grid/bin/crsctl start res ora.asm -init
CRS-2672: 尝试启动 'ora.asm' (在 'rac1' 上)
CRS-5011: 检查资源 "+ASM" 失败: 详细资料见 "(:CLSN00006:)" (位于 "/opt/app/grid/log/rac1/agent/ohasd/oraagent_grid/oraagent_grid.log")
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
进程 ID: 0
会话 ID: 0 序列号: 0
ORA-27154: 传递/等待创建失败
CRS-5011: 检查资源 "+ASM" 失败: 详细资料见 "(:CLSN00006:)" (位于 "/opt/app/grid/log/rac1/agent/ohasd/oraagent_grid/oraagent_grid.log")
CRS-2674: 未能启动 'ora.asm' (在 'rac1' 上)
CRS-2679: 尝试清除 'ora.asm' (在 'rac1' 上)
CRS-5011: 检查资源 "+ASM" 失败: 详细资料见 "(:CLSN00006:)" (位于 "/opt/app/grid/log/rac1/agent/ohasd/oraagent_grid/oraagent_grid.log")
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
进程 ID: 0
会话 ID: 0 序列号: 0
CRS-5011: 检查资源 "+ASM" 失败: 详细资料见 "(:CLSN00006:)" (位于 "/opt/app/grid/log/rac1/agent/ohasd/oraagent_grid/oraagent_grid.log")
CRS-2681: 成功清除 'ora.asm' (在 'rac1' 上)
CRS-4000: Command Start failed, or completed with errors.
还是ORA.ASM 无法启动。怎么办,继续。。。。。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
正确方法如下:
1.先生成一个操作系统下面的spfile:(用pfile生成一个类似的spfile)
[oracle@rac3 dbs]$ cat initracdb1.ora
SPFILE='+DATA/racdb/spfileracdb.ora'
cp initracdb1.ora spfileracdb1.ora
2.重启数据库
shutdown abort;
startup;
3.修改参数
alter system set processes=800 scope = spfile;
alter system set open_cursors=2000 scope=spfile;
4.把rac1 spfile 质到rac2
scp spfileracdb1.ora rac4:/opt/app/oracle/product/11.2.0/dbs/spfileracdb2.ora
参考文章:
http://space.itpub.net/22308399/viewspace-753157
查看资料:
http://t.askmaclean.com/thread-2167-1-1.html
http://blog.csdn.net/tianlesoftware/article/details/6019557
http://blog.csdn.net/tianlesoftware/article/details/6013763