问题描述:
虚拟机上的两个实例都连接不上。现象如下。
我尝试将listener.ora、tnsnames.ora备份后,用netca重建两个文件以及sqlnet.ora,但没有任何效果。
现象为:
[oracle@localhost ~]$ set oracle_sid=testdb
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 2 04:39:40 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-00845: MEMORY_TARGET not supported on this system
SQL> exit
Disconnected
[oracle@localhost ~]$ pwd
/u01/oracle
[oracle@localhost ~]$ cd network/admin
[oracle@localhost admin]$ ll
total 88
-rw-r--r-- 1 oracle oinstall 293 Oct 27 11:35 listener12102711AM3507.bak
-rw-r--r-- 1 oracle oinstall 293 Oct 27 05:59 listener1210275AM5907.bak
-rw-r--r-- 1 oracle oinstall 293 Oct 27 09:00 listener1210279AM0036.bak
-rw-r--r-- 1 oracle oinstall 420 Nov 2 03:53 listener1211023AM5358.bak
-rw-r--r-- 1 oracle oinstall 293 Nov 2 04:24 listener1211024AM2452.bak
-rw-r--r-- 1 oracle oinstall 293 Nov 2 04:27 listener.ora
-rw-r--r-- 1 oracle oinstall 489 Oct 27 09:51 listener.ora-tianleibak
drwxr-xr-x 2 oracle oinstall 4096 Oct 26 18:45 samples
-rw-r--r-- 1 oracle oinstall 187 May 9 2007 shrept.lst
-rw-r--r-- 1 oracle oinstall 212 Oct 27 11:35 sqlnet12102711AM3507.bak
-rw-r--r-- 1 oracle oinstall 212 Oct 27 09:52 sqlnet1210279AM5215.bak
-rw-r--r-- 1 oracle oinstall 213 Nov 2 03:53 sqlnet1211023AM5358.bak
-rw-r--r-- 1 oracle oinstall 213 Nov 2 04:24 sqlnet1211024AM2452.bak
-rw-r--r-- 1 oracle oinstall 212 Oct 28 16:33 sqlnet.orabak-tl
-rw-r--r-- 1 oracle oinstall 213 Nov 2 04:27 sqlnet.orabak-tl-2
-rw-r--r-- 1 oracle oinstall 316 Oct 27 11:35 tnsnames12102711AM3507.bak
-rw-r--r-- 1 oracle oinstall 312 Oct 27 05:59 tnsnames1210275AM5907.bak
-rw-r--r-- 1 oracle oinstall 312 Oct 27 09:00 tnsnames1210279AM0036.bak
-rw-r--r-- 1 oracle oinstall 580 Nov 2 03:53 tnsnames1211023AM5358.bak
-rw-r--r-- 1 oracle oinstall 580 Nov 2 04:24 tnsnames1211024AM2452.bak
-rw-r--r-- 1 oracle oinstall 490 Nov 2 04:27 tnsnames.ora
-rw-r----- 1 oracle oinstall 312 Oct 27 09:51 tnsnames.ora-tianleibak
[oracle@localhost admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-NOV-2012 04:41:44
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
[oracle@localhost admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-NOV-2012 04:42:00
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/oracle/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/oracle/network/admin/listener.ora
Log messages written to /u01/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 02-NOV-2012 04:42:01
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/network/admin/listener.ora
Listener Log File /u01/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
The listener supports no services
The command completed successfully
由于报出的问题是连接方面的,因此一直在几个文件上查找原因。但可以确定,并未手工进行文件的修改。
又进行了一遍分析,觉得这个操作比较可疑:
SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system
[oracle@localhost alert]$ oerr ora 00845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was
not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to
be at least the SGA_MAX_SIZE on each Oracle instance running on the system.
[oracle@localhost admin]$ df -m
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
17824 14033 2872 84% /
/dev/sda1 99 12 82 13% /boot
tmpfs 484 232 252 48% /dev/shm
/dev/hdc 2875 2875 0 100% /media/RHEL_5.3 i386 DVD
[oracle@localhost dbs]$ strings spfiletestdb.ora |grep memory
*.memory_target=403701760
/dev/shm中的可用空间时252M,而参数文件中配置的大小是近400M,看来问题出在这里。
因此想到将memory_target参数暂时改小一点,但又不知道如何生效。
网上查询了一下,一篇很有帮助的博客:http://ibmcn.blog.51cto.com/510174/766996
按其方法尝试:
[root@localhost ~]# umount /dev/shm
[root@localhost ~]# mount /dev/shm
[root@localhost ~]# df -m
Filesystem 1M-blocks Used Available Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
17824 14036 2868 84% /
/dev/sda1 99 12 82 13% /boot
tmpfs 1024 0 1024 0% /dev/shm
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ set oracle_sid=testdb
[oracle@localhost ~]$ sqlplus /nolog
conn /as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 2 07:02:03 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL>
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 405929984 bytes
Fixed Size 1336848 bytes
Variable Size 310381040 bytes
Database Buffers 88080384 bytes
Redo Buffers 6131712 bytes
SQL> alter database mount
2 ;
Database altered.
SQL> alter database open;
Database altered.
终于成功了
=========================================
那么刚才想到的修改参数的方法怎么做呢?
我们用该虚拟机上的另一个数据库来实验。(故障现象是一样的,db_name=succ)
[root@localhost dbs]# pwd
/u01/oracle/dbs
[root@localhost dbs]# strings spfilesucc.ora >pfilesucc.ora
[root@localhost dbs]# cat pfilesucc.ora |grep mem
//结果为空
[root@localhost dbs]# su - oracle
[oracle@localhost ~]$ set oracle_sid=succ
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 2 08:04:03 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 405929984 bytes
Fixed Size 1336848 bytes
Variable Size 310381040 bytes
Database Buffers 88080384 bytes
Redo Buffers 6131712 bytes
Database mounted.
Database opened.
很遗憾,另一个数据库中没有参数memory_target。
而我想到的方法:通过spfile生成pfile,再对其修改然后用其启动数据库的方法 无法验证。感觉上可能会有问题,不过也算是一种思路,记录下来吧!
另外查了一下/dev/shm,这是一个类似虚拟磁盘的挂载点,成为tmpfs,可以使用RAM,也可以使用交换分区来存储,系统重启后将释放,处理速度很快。
=======================================
然后说说我对这三个文件的认识,供拍砖。
listener.ora
监听文件,告诉oracle需要在哪台服务器的哪个端口上用什么协议监听,通过netca/listener configuration配置
tnsnames.ora
网络服务名配置文件,将完整的连接名称转化为一个别名,从而方便用户调用,通过netca/Local Net Service Name Configuration配置
sqlnet.ora
11g中可以不存在此物理文件。存储了密码验证方式、数据钱包等信息,主要是安全性方面的配置,通过netca/Naming Method Configuration添加
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-748540/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-748540/