Oracleリスナーの追加方法メモ

Oracle Listenerを追加する際にいつもわからなくなるのでメモ。 環境はOracle 11.2.0.4。
1.動的リスナー追加
2.動的リスナーを大量に追加
3.静的リスナー追加

1.動的リスナー追加

• LISTENER2とLISTENER3を追加する

  listener.ora編集

vim listener.ora

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
  )

パラメータの変更

SQL> alter system set local_listener='(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523))
  )';

リスナー起動

lsnrctl start LISTENER2
lsnrctl start LISTENER3

2.動的リスナーを大量に追加

• LISTENER2～LISTENER7まで追加

listener.ora編集

vim listener.ora

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
  )
・
・
・
LISTENER7 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1527))
    )
  )

ここで先ほどと同じようにパラメータに追加するとエラーとなる

SQL> alter system set local_listener='(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1524))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1525))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1526))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1527))
  )';

行1でエラーが発生しました。:
ORA-32021: パラメータ値が255文字を超えています

tnsname.oraの編集

動的リスナー情報が255文字を超える場合はtnsname.oraに記述する

vim tnsnames.ora

ALLLISTENERS = (ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1524))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1525))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1526))
  (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1527))
  )

パラメータの変更

SQL> alter system set local_listener='ALLLISTENERS ';
※tnsnames.oraで指定した別名を登録する

リスナー起動

lsnrctl start LISTENER
・
・
・
lsnrctl start LISTENER7

その他

LISTENER8を追加する場合は、 listener.oraとtnsnames.oraに追記後、再度

alter system set local_listener='ALLLISTENERS ';

を実行してあげる必要あり。

3.静的リスナー追加

• パラメータの変更が不要
• LISTENER2とLISTENER3を追加する

listener.ora編集

LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )
SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)
    )
  )
LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
    )
  )
SID_LIST_LISTENER3 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /opt/app/oracle/product/11.2.0/dbhome_1)
    )
  )

リスナー起動

lsnrctl start LISTENER2
lsnrctl start LISTENER3