今天，遇到一个需求：
需要在一个RAC环境中的多个实例中修改它们的初始化参数“Local_listener”。
[oracle@gg01 ~]$ ps -ef | grep ora_ | grep -v grep | grep -v ASM | grep pmon oracle 9459 1 0 Oct08 ? 00:01:59 ora_pmon_crmls1 oracle 9756 1 0 Oct08 ? 00:01:58 ora_pmon_gppls1 oracle 10039 1 0 Oct08 ? 00:01:56 ora_pmon_hdqtls1 oracle 10384 1 0 Oct08 ? 00:01:57 ora_pmon_mkt1 oracle 10657 1 0 Oct08 ? 00:01:57 ora_pmon_mtl1 oracle 10983 1 0 Oct08 ? 00:01:57 ora_pmon_pcmls1 oracle 11305 1 0 Oct08 ? 00:01:58 ora_pmon_pos1 [oracle@gg01 ~]$
由于每次进入实例后的操作都是一样的，所以该事务可以脚本化。
这里，我是通过Bash Shell写的脚本：
[oracle@gg01 ~]$ cat show_local-listener.sh for i in `ps -ef | grep ora_ | grep -v grep | grep -v ASM | grep pmon | cut -d'_' -f3` do echo "===================================" echo "Instance is: $i" ip_string=`/sbin/ifconfig eth0 | grep "inet addr" | cut -d':' -f2 | cut -d' ' -f1` echo "IP address is: $ip_string" echo "===================================" export ORACLE_SID=$i env | grep --color SID echo "----------------------------" sqlplus / as sysdba<
它执行时的效果如下：
[oracle@gg01 ~]$ sh show_local-listener.sh
===================================
Instance is: crmls1
IP address is: 169.0.2.27
===================================
ORACLE_SID=crmls1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:24 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='crmls1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
===================================
Instance is: gppls1
IP address is: 169.0.2.27
===================================
ORACLE_SID=gppls1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:24 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='gppls1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
===================================
Instance is: hdqtls1
IP address is: 169.0.2.27
===================================
ORACLE_SID=hdqtls1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:24 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='hdqtls1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
===================================
Instance is: mkt1
IP address is: 169.0.2.27
===================================
ORACLE_SID=mkt1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:25 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='mkt1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
===================================
Instance is: mtl1
IP address is: 169.0.2.27
===================================
ORACLE_SID=mtl1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:25 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='mtl1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
===================================
Instance is: pcmls1
IP address is: 169.0.2.27
===================================
ORACLE_SID=pcmls1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:25 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='pcmls1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
===================================
Instance is: pos1
IP address is: 169.0.2.27
===================================
ORACLE_SID=pos1
----------------------------
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Oct 14 16:17:25 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=)(PORT=1521)))
SQL> @@@@@@@@@@@@@@
SQL> Command is:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.0.2.27)(PORT=1521)))' scope=both sid='pos1';
SQL>
System altered.
SQL> @@@@@@@@@@@@@@
SQL>
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS=(PROTOCO
L=tcp)(HOST=169.0.2.27)(PORT=1
521)))
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@gg01 ~]$
[oracle@gg01 ~]$——————————————————————
Done。