Сервисы в Oracle Restart

Создаем сервисы в БД testdb:

exec dbms_service.create_service(service_name => 'testdb_app', network_name=> 'testdb_app');
exec dbms_service.start_service('testdb_app');

удалять так:
exec dbms_service.stop_service('testdb_app');
exec dbms_service.delete_service('testdb_app');


Добавляем базу testdb в конфигурацию oracle restart:

srvctl add database -d testdb_p -n testdb -i testdb -o /u01/app/oradb/product/11.2.0.3/dbhome_1 -p /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/spfiletestdb.ora  -s OPEN  -r PRIMARY  -t IMMEDIATE -y AUTOMATIC -a "DATA,RECO,REDO";

srvctl add database -d testdb_s -n testdb -i testdb -o /u01/app/oradb/product/11.2.0.3/dbhome_1 -p /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/spfiletestdb.ora  -s MOUNT -r PHYSICAL_STANDBY -t IMMEDIATE -y AUTOMATIC -a "DATA,RECO,REDO";


Тестовую базу можно добавить в режиме (manual)
после перезагрузки сервера базу нужно будет запускать руками

srvctl add database -d testdb -n testdb -i testdb -o /u01/app/oradb/product/11.2.0.3/dbhome_1 -p /u01/app/oradb/product/11.2.0.3/dbhome_1/dbs/spfiletestdb.ora  -s OPEN -r PRIMARY -t IMMEDIATE -y MANUAL -a "DATA,RECO,REDO,TEST";

Добавляем сервисы в конфигурацию oracle restart:

$ srvctl add service -d testdb_p -s testdb_app -l primary -e SELECT -m BASIC
$ srvctl modify service -d testdb_p -s testdb_app -z 180 -w 1
$ srvctl config service -d testdb_p


standby:

$ srvctl add service -d testdb_s -s testdb_app -l primary -e SELECT -m BASIC
$ srvctl modify service -d testdb_s -s testdb_app -z 180 -w 1
$ srvctl config service -d testdb_s

Регистрация сервисов БД в случае БЕЗ использования GI

Названия сервисов: SIDAPP для Primary и  SIDREP для Standby

Без поддержки TAF    
  
-- Для БД с ролью PRIMARY

begin
  DBMS_SERVICE.CREATE_SERVICE (
            service_name => 'TESTAPP',
            network_name => 'TESTAPP');
end;
/


-- Для БД с ролью STANDBY + READ ONLY

begin
  DBMS_SERVICE.CREATE_SERVICE (
            service_name => 'TESTREP',
            network_name => 'TESTREP');
end;
/

С поддержкой TAF

-- Для БД с ролью PRIMARY

begin
  DBMS_SERVICE.CREATE_SERVICE (
            service_name => 'TESTAPP',
            network_name => 'TESTAPP',
            failover_method  => 'BASIC',
            failover_type    => 'SELECT',
            failover_retries => 20,
            failover_delay   => 5);
end;
/

-- Для БД с ролью STANDBY + READ ONLY

begin
  DBMS_SERVICE.CREATE_SERVICE (
            service_name => 'TESTREP',
            network_name => 'TESTREP',
            failover_method  => 'BASIC',
            failover_type    => 'SELECT',
            failover_retries => 20,
            failover_delay   => 5);
end;
/


Процедура автозапуска сервисов в БД в зависимости от её РОЛИ

CREATE OR REPLACE TRIGGER SYS.startDGservices after startup on database
DECLARE
  db_role VARCHAR(30);
  db_open_mode VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;
  IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('TESTAPP'); END IF;
  IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('TESTREP'); END IF;
END;
/


Процедуры ручного запуска сервисов в БД в зависимости от её РОЛИ

CREATE OR REPLACE procedure SYS.startAPPservice as
DECLARE
  db_role VARCHAR(30);
  db_open_mode VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;
  IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('TESTAPP'); END IF;
END;
/

CREATE OR REPLACE procedure SYS.startREPservice as
DECLARE
  db_role VARCHAR(30);
  db_open_mode VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;
  IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('TESTREP'); END IF;
END;
/


Процедуры ручной остановки сервисов в БД

create or replace procedure SYS.stopAPPservice as
begin
  DBMS_SERVICE.STOP_SERVICE('TESTAPP');
end;
/


create or replace procedure SYS.stopREPservice as
begin
  DBMS_SERVICE.STOP_SERVICE('TESTREP');
end;
/

Регистрация сервисов БД в случае использованияGI

Регистрация сервисов в Grid Infrastructure (GI)

Без поддержки TAF

-- Для БД с ролью PRIMARY

. oraenv
$ srvctl add service -d TEST_OMEGA01 \
                     -s TESTAPP         \
                     -l PRIMARY   \
                     -y AUTOMATIC \
                     -P NONE      \
                     -e NONE      \
                     -m NONE      \
                     -w 5         \
                     -z 20         

-- Для БД с ролью STANDBY + READ ONLY

. oraenv
$ srvctl add service -d TEST_OMEGA02 \
                     -s TESTREP         \
                     -l PHYSICAL_STANDBY \
                     -y AUTOMATIC \
                     -P NONE      \
                     -e NONE      \
                     -m NONE      \
                     -w 5         \
                     -z 20         

С поддержкой TAF

-- Для БД с ролью PRIMARY

. oraenv
$ srvctl add service -d TEST_OMEGA01 \
                     -s TESTAPP         \
                     -l PRIMARY   \
                     -y AUTOMATIC \
                     -P BASIC     \
                     -e SELECT    \
                     -m BASIC     \
                     -w 5         \
                     -z 20   

-- Для БД с ролью STANDBY + READ ONLY

. oraenv
$ srvctl add service -d TEST_OMEGA02 \
                     -s TESTREP         \
                     -l PHYSICAL_STANDBY \
                     -y AUTOMATIC \
                     -P BASIC     \
                     -e SELECT    \
                     -m BASIC     \
                     -w 5         \
                     -z 20


Процедура автозапуска сервисов в БД в зависимости от её РОЛИ

Работа сервисов контролируется ПО Grid Infrastructure в АВТО-режиме

Процедуры ручного запуска сервисов в БД в зависимости от её РОЛИ

$ srvctl start  service -d TEST_OMEGA01 -s TESTAPP –o OPEN
$ srvctl start  service -d TEST_OMEGA02 -s TESTREP


Процедуры ручной остановки сервисов в БД

$ srvctl stop  service -d TEST_OMEGA01 -s TESTAPP
$ srvctl stop  service -d TEST_OMEGA02 -s TESTREP


Процедура проверки характеристик зарегистрированного сервиса

$ srvctl config service -d TEST_OMEGA01 -s TESTAPP
$ srvctl config service -d TEST_OMEGA02 -s TESTREP


Проверка клиентский соединения на предмет использования сервисов с поддержкой технологии TAF :

col username format a25
col machine format a30
col module format a15
col service_name format a15
col server format a12
col failover_type format a15
col failover_method format a15
col failed_over format a15

select inst_id,sid,username, machine, substr(module,1,15) as module, service_name,server,
       case when failover_type='NONE'   then NULL else failover_type   end as failover_type,
       case when failover_method='NONE' then NULL else failover_method end as failover_method,
       case when failed_over='NO'       then NULL else failed_over     end as failed_over
from gv$session
order by 1,2;


Проверка работы сервисов

col host_name    format a22
col service_name format a22
col network_name format a22

select (select host_name from v$instance) host_name,
       name service_name,
       network_name
from v$services;

Настройка дескрипторов соединений на клиентской стороне

01 - Primary - должен быть поднят сервис APP
02 - Standby- доджен быть поднят сервис REP в режиме RO

Подключение к базам в БЕЗ Standby конфигурации:

Non-RAC конфигурация

TEST_APP =
  (DESCRIPTION =
    (ENABLE=BROKEN)
    (ADDRESS_LIST=
        (ADDRESS = (PROTOCOL = TCP)(HOST = omega01.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTAPP)
    )
  )


RAC конфигурация

TEST_APP =
  (DESCRIPTION =
     (ENABLE=BROKEN)
     (CONNECT_TIMEOUT=30)
     (TRANSPORT_CONNECT_TIMEOUT=10)
     (RETRY_COUNT=3)
     (ADDRESS_LIST=
       (LOAD_BALANCE=on)
       (ADDRESS = (PROTOCOL = TCP)(HOST = scan_omega01)(PORT = 1521))
     )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTAPP)
    )
  )

Подключение к базам в  Standby конфигурации:

Non-RAC конфигурация

TEST_APP =
  (DESCRIPTION =
    (ENABLE=BROKEN)
    (TRANSPORT_CONNECT_TIMEOUT=10)
    (ADDRESS_LIST=
      (LOAD_BALANCE=OFF)
      (FAILOVER=ON)
        (ADDRESS = (PROTOCOL = TCP)(HOST = omega01.localdomain)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = omega02.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTAPP)
    )
  )

TEST_REP =
  (DESCRIPTION =
    (ENABLE=BROKEN)
    (TRANSPORT_CONNECT_TIMEOUT=10)
    (ADDRESS_LIST=
      (LOAD_BALANCE=OFF)
      (FAILOVER=ON)
        (ADDRESS = (PROTOCOL = TCP)(HOST = omega01.localdomain)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = omega02.localdomain)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTREP)
    )
  )


RAC конфигурация

TEST_APP =
  (DESCRIPTION_LIST=
     (LOAD_BALANCE=OFF)
     (FAILOVER=ON)
       (DESCRIPTION=
          (ENABLE=BROKEN)
          (CONNECT_TIMEOUT=30)
          (TRANSPORT_CONNECT_TIMEOUT=10)
          (RETRY_COUNT=3)
          (ADDRESS_LIST=
            (LOAD_BALANCE=ON)
            (ADDRESS=(PROTOCOL=TCP)(HOST=scan_omega01)(PORT=1521))
          )
            (CONNECT_DATA=
              (SERVER = DEDICATED)
              (SERVICE_NAME=TESTAPP)
            )
       )
       (DESCRIPTION=
          (ENABLE=BROKEN)
          (CONNECT_TIMEOUT=30)
          (TRANSPORT_CONNECT_TIMEOUT=10)
          (RETRY_COUNT=3)
          (ADDRESS_LIST=
            (LOAD_BALANCE=ON)
            (ADDRESS=(PROTOCOL=TCP)(HOST=scan_omega02)(PORT=1521))
          )
            (CONNECT_DATA=
              (SERVER = DEDICATED)
              (SERVICE_NAME=TESTAPP)
            )
       )
  )


TEST_REP =
  (DESCRIPTION_LIST=
     (LOAD_BALANCE=OFF)
     (FAILOVER=ON)
       (DESCRIPTION=
          (ENABLE=BROKEN)
          (CONNECT_TIMEOUT=30)
          (TRANSPORT_CONNECT_TIMEOUT=10)
          (RETRY_COUNT=3)
          (ADDRESS_LIST=
            (LOAD_BALANCE=ON)
            (ADDRESS=(PROTOCOL=TCP)(HOST=scan_omega01)(PORT=1521))
          )
            (CONNECT_DATA=
              (SERVER = DEDICATED)
              (SERVICE_NAME=TESTREP)
            )
       )
       (DESCRIPTION=
          (ENABLE=BROKEN)
          (CONNECT_TIMEOUT=30)
          (TRANSPORT_CONNECT_TIMEOUT=10)
          (RETRY_COUNT=3)
          (ADDRESS_LIST=
            (LOAD_BALANCE=ON)
            (ADDRESS=(PROTOCOL=TCP)(HOST=scan_omega02)(PORT=1521))
          )
            (CONNECT_DATA=
              (SERVER = DEDICATED)
              (SERVICE_NAME=TESTREP)
            )
       )
  )