分类目录归档：数据库

在RAC中lsnrctl和srvctl操作监听区别

发表于 2012 年 3 月 14 日由惜分飞

朋友今天询问了一个问题RAC中使用srvctl 操作监听和lsnrctl 操作监听结果不一样，下面我通过实验说明问题
0.listener.ora文件内容

LISTENER_RAC1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_RAC1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

1.srvctl 启动监听

rac1-> srvctl start listener -n rac1
rac1-> lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:09:34

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_RAC1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-MAR-2012 22:07:21
Uptime                    0 days 0 hr. 2 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1        
ora....db1.srv application    ONLINE    ONLINE    rac2        
ora.devdb.db   application    ONLINE    ONLINE    rac2        
ora....b1.inst application    ONLINE    ONLINE    rac1        
ora....b2.inst application    ONLINE    ONLINE    rac2        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    ONLINE    ONLINE    rac2

srvctl操作监听，自动反馈到crs中

2.使用srvctl关闭监听

rac1-> srvctl stop listener -n rac1
rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1        
ora....db1.srv application    ONLINE    ONLINE    rac2        
ora.devdb.db   application    ONLINE    ONLINE    rac2        
ora....b1.inst application    ONLINE    ONLINE    rac1        
ora....b2.inst application    ONLINE    ONLINE    rac2        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    OFFLINE   OFFLINE               
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    ONLINE    ONLINE    rac2

3.使用lsnrctl查看监听状态

rac1-> lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:15:54

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) <--host为空
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused



rac1-> lsnrctl 

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:16:55

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status listener_rac1
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.11)(PORT=1521)(IP=FIRST)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

这里可以发现问题:
1)如果当前没有监听在运行，使用lsnrctl status的时候，会去检查默认的监听名称为listener的监听，如果该监听不存在不会使用hostname填充到hostname项中(注意下面的启动默认监听过程)
2)lsnrctl查看指定监听为listener_rac1，发现和listener.ora中配置相同

4.lsnrctl 关闭监听

rac1-> srvctl start listener -n rac1
rac1-> lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:43:14

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) <--host为空
The command completed successfully
rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1        
ora....db1.srv application    ONLINE    ONLINE    rac2        
ora.devdb.db   application    ONLINE    ONLINE    rac2        
ora....b1.inst application    ONLINE    ONLINE    rac1        
ora....b2.inst application    ONLINE    ONLINE    rac2        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    OFFLINE   OFFLINE               
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    ONLINE    ONLINE    rac2

这里可以说明问题：
1)lsnrctl stop虽然是要停止掉默认监听，但是也会停止掉非默认监听
2)lsnrctl stop如果默认监听不存在，那么注册host也为空

5.使用lsnrctl启动默认监听

rac1-> lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-MAR-2012 22:17:37

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-MAR-2012 22:17:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521))) <--主机名
The listener supports no services
The command completed successfully

rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1        
ora....db1.srv application    ONLINE    ONLINE    rac2        
ora.devdb.db   application    ONLINE    ONLINE    rac2        
ora....b1.inst application    ONLINE    ONLINE    rac1        
ora....b2.inst application    ONLINE    ONLINE    rac2        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    OFFLINE   OFFLINE               
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    ONLINE    ONLINE    rac2

这里发现问题：
1)监听的ip只有主机名的一个，和srvctl启动的监听不一样
2)虽然监听启动了，crs中依然显示为offline状态

6.使用lsnrctl启动listener_rac1监听

LSNRCTL> start listener_rac1
Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1-vip)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     listener_rac1
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                11-MAR-2012 22:19:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener_rac1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.11)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

rac1-> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.....XFF.cs application    ONLINE    ONLINE    rac1        
ora....db1.srv application    ONLINE    ONLINE    rac2        
ora.devdb.db   application    ONLINE    ONLINE    rac2        
ora....b1.inst application    ONLINE    ONLINE    rac1        
ora....b2.inst application    ONLINE    ONLINE    rac2        
ora....SM1.asm application    ONLINE    ONLINE    rac1        
ora....C1.lsnr application    ONLINE    ONLINE    rac1        
ora.rac1.gsd   application    ONLINE    ONLINE    rac1        
ora.rac1.ons   application    ONLINE    ONLINE    rac1        
ora.rac1.vip   application    ONLINE    ONLINE    rac1        
ora....SM2.asm application    ONLINE    ONLINE    rac2        
ora....C2.lsnr application    ONLINE    ONLINE    rac2        
ora.rac2.gsd   application    ONLINE    ONLINE    rac2        
ora.rac2.ons   application    ONLINE    ONLINE    rac2        
ora.rac2.vip   application    ONLINE    ONLINE    rac2

这里可以说明两个问题:
1)使用lsnrctl启动监听和srvctl启动一样
2)启动listener_rac1后，crs中监听资源变成online

7.问题原因分析

rac1-> srvctl config listener -n rac1
rac1 LISTENER_RAC1

通过这里可以发现，其实srvctl操作的监听就是LISTENER_RAC1，所以当我使用lsnrctl 操作LISTENER_RAC1监听时候crs会自动offline或者online，而lsnrctl 操作默认监听时crs不会online

发表在 Oracle RAC | 2 条评论

误删除dual表恢复

发表于 2012 年 3 月 13 日由惜分飞

1.10G中删除dual表恢复

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select object_type,owner from dba_objects where object_name='DUAL';

OBJECT_TYPE         OWNER
------------------- ------------------------------
TABLE               SYS
SYNONYM             PUBLIC

SQL> drop table sys.dual;

Table dropped.

SQL> select object_type from dba_objects where object_name='DUAL';

OBJECT_TYPE
-------------------
SYNONYM

SQL> SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual
                    *
ERROR at line 1:
ORA-01775: looping chain of synonyms


SQL> CREATE TABLE XFF AS SELECT * from dba_objects;

Table created.

SQL> drop table xff purge;
drop table xff purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms


设置10046跟踪会话发现，在每次删除表操作的时候发现如下错误
select dummy from dual where  ora_dict_obj_type = 'TABLE'

其实这里错误都很明显，是因为dual表不存在了，表对应的同义词还存在，当查询dual的时候，会去查询同义词，然后该同义词去找表，而表不存在，所以出现上述的ORA-01775: looping chain of synonyms错误

2.解决方法

SQL> CREATE TABLE "SYS"."DUAL" 
  2     (       "DUMMY" VARCHAR2(1)
  3     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  4    STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  5    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  6    TABLESPACE "SYSTEM" ;

Table created.

SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;

Grant succeeded.

SQL> insert into dual values('X');

1 row created.

SQL> COMMIT;

Commit complete.

--编译对象
SQL> @?/rdbms/admin/utlrp.sql

3.测试结果


SQL> select sysdate from dual;

SYSDATE
------------
13-MAR-12

SQL> drop table xff purge;

Table dropped.

发表在 Oracle | 2 条评论

rman 备份出现ORA-00245/RMAN-08132

发表于 2012 年 3 月 13 日由惜分飞

rman备份出现如下错误

RMAN> backup full  tag 'dbfull' format '/jfkdata1/rman_bak/full_%d_%u' database 
 2>   include current controlfile  plus archivelog  format '/jfkdata1/rman_bak/arch_%d_%u'; 


Starting backup at 13-MAR-12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=297 instance=ykcdb2 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=29 RECID=43 STAMP=777808820
input archived log thread=1 sequence=30 RECID=46 STAMP=777816035
input archived log thread=2 sequence=21 RECID=45 STAMP=777816033
channel ORA_DISK_1: starting piece 1 at 13-MAR-12
channel ORA_DISK_1: finished piece 1 at 13-MAR-12
piece handle=/jfkdata1/rman_bak/arch_YKCDB_01n5p1vf tag=DBFULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 13-MAR-12

Starting backup at 13-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/jfkdata1/oradata/ykcdb/sysaux01.dbf
input datafile file number=00001 name=/jfkdata1/oradata/ykcdb/system01.dbf
input datafile file number=00003 name=/jfkdata1/oradata/ykcdb/undotbs01.dbf
input datafile file number=00005 name=/jfkdata1/oradata/ykcdb/undotbs02.dbf
input datafile file number=00004 name=/jfkdata1/oradata/ykcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-MAR-12
channel ORA_DISK_1: finished piece 1 at 13-MAR-12
piece handle=/jfkdata1/rman_bak/full_YKCDB_02n5p209 tag=DBFULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13-MAR-12
channel ORA_DISK_1: finished piece 1 at 13-MAR-12
piece handle=/jfkdata1/rman_bak/full_YKCDB_03n5p213 tag=DBFULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAR-12

Starting backup at 13-MAR-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=22 RECID=48 STAMP=777816102
input archived log thread=1 sequence=31 RECID=47 STAMP=777816101
channel ORA_DISK_1: starting piece 1 at 13-MAR-12
channel ORA_DISK_1: finished piece 1 at 13-MAR-12
piece handle=/jfkdata1/rman_bak/arch_YKCDB_04n5p217 tag=DBFULL comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAR-12
ORA-00245: control file backup operation failed

RMAN-08132: WARNING: cannot update recovery area reclaimable file list

RMAN>

从这里可以看出数据库文件，归档日志，spfile和controlfile都备份完成，后面又出现一个controlfile失败，应该是控制文件快照备份失败。

错误原因

RMAN creates a copy of the control file for read consistency, this is the snapshot controlfile. 
Due to the changes made to the controlfile backup mechanism in 11gR2 any instances 
in the cluster may write to the snapshot controlfile. Therefore, 
the snapshot controlfile file needs to be visible to all instances.

The same happens when a backup of the controlfile is created directly from 
sqlplus any instance in the cluster may write to the backup controfile file.

In 11gR2 onwards, the controlfile backup happens without holding the control file enqueue. 
For non-RAC database, this doesn't change anything. 

But, for RAC database, the snapshot controlfile location 
must be in a shared file system that will be accessible from all the nodes.

The snapshot controlfile MUST be  accessible by all nodes of a RAC database.

大致意思就是rman的snapshot controlfile必须放在共享存储之上。

解决方法

The snapshot controlfile MUST be accessible by all nodes of a RAC database, 
if the snapshot controlfile does not reside in on a shared device this error will raise.

1. Check the snapshot controlfile location:
RMAN> show all;

2. Configure the snapshot controlfile to a shared disk:
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '<shared_disk>/snapcf_<DBNAME>.f';

发表在 rman备份/恢复 | 标签为 ORA-00245 | 2 条评论

17813235971

QQ 咨询

分类目录归档：数据库

在RAC中lsnrctl和srvctl操作监听区别

误删除dual表恢复

rman 备份出现ORA-00245/RMAN-08132

文章分类

最近发表

分类目录归档：数据库

在RAC中lsnrctl和srvctl操作监听区别

误删除dual表恢复

rman 备份出现ORA-00245/RMAN-08132

标签云

文章分类

最近发表