10 апр. 2014 г.

Демонстрация GDS


27 марта в офисе компании Oracle проходил семинар "Новые возможности Oracle Database 12с для обеспечения высокой доступности", на котором я рассказывал о новой технологии  Oracle Database 12с: Global Data Services.

Мою презентацию можно скачать здесь.

Во время презентации я демонстрировал некоторые возможности GDS. Предлагаю и Вам пощупать изучить новую технологию.

Для выполнения описанных ниже шагов необходимо:
 - установить программное обеспечение GDS из дистрибутивного пакета Oracle Database 12c
 - создать базу данных Oracle Database 12c и создать в ней каталог GDS
 - запустить GSM
 - создать primary и standby базы данных Oracle Database 12c, сконфигурировать Data Guard Broker и открыть standby базу данных в режиме Active Data Guard
 - вместо использования в описании ниже скрипта clb.sh, можно просто выполнять соединение с БД с помощью sqlplus (или другого удобного для вас инструмента работы с БД) и определять через v$database имя БД, к которой Вы подключились.

В преднастроенных виртуальных машинах, которые я могу предоставить по Вашему запросу, для подготовки достаточно выполнить:
[oracle@gsm ~]$ ./dbstart.sh
[oracle@gsm ~]$ ./gsm_start.sh
[oracle@msk ~]$ ./dbstart.sh
[oracle@spb ~]$ ./dbstart.sh
И после успешного старта всех экземпляров БД и GSM можно приступать к знакомству с
с новой и очень интересной технологией:

1. Запустите утилиту управления конфигурацией GDSCTL и создайте регионы msk и spb
[oracle@gsm ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/gsmhome_1
[oracle@gsm ~]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@gsm ~]$ gdsctl
GDSCTL: Version 12.1.0.1.0 - Production on Tue Dec 17 18:52:37 EST 2013

Copyright (c) 2011, 2012, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSM_MAIN
GDSCTL>
GDSCTL>add region -region msk,spb
GDSCTL>modify gsm -gsm gsm_main -region msk
GSM modified
GDSCTL>exit
[oracle@gsm ~]$

2. Разблокируйте пользователя  gsmuser в базе данных CDB (primary)

GDSCTL>exit
[oracle@gsm ~]$ sqlplus system/oracle@msk:1521/cdb

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 17 18:57:01 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Dec 17 2013 18:47:38 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user gsmuser account unlock identified by oracle;

User altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@gsm ~]$

3. Добавьте в конфигурацию GDS информацию Data Guard Broker

[oracle@gsm ~]$ gdsctl
GDSCTL: Version 12.1.0.1.0 - Production on Tue Dec 17 18:59:36 EST 2013

Copyright (c) 2011, 2012, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSM_MAIN
GDSCTL>add brokerconfig -connect msk:1521:cdb -region msk
Catalog connection is established
"gsmuser" password:
DB Unique Name: cdb
GDSCTL>databases
Database: "cdbstby" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: N/A
Database: "cdb" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: msk

GDSCTL>modify database -database cdbstby -region spb
GDSCTL>databases
Database: "cdbstby" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: spb
   Registered instances:
     dbpoolora%11
Database: "cdb" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: msk
   Registered instances:
     dbpoolora%1

GDSCTL>

4. Создайте глобальные сервисы gsm_rw (для чтения и записи) и gsm_rpt (для выполнения отчётов).
Обратите внимание, что сервис gsm_rpt определяется для соединения со standby базой данных
и только в случае её неисправности пользователи этого сервиса будут подключаться к primary базе данных

GDSCTL>add service -service gsm_rw -preferred_all -role PRIMARY
GDSCTL>add service -service gsm_rpt -preferred_all -role PHYSICAL_STANDBY -failover_primary
GDSCTL>start service -service gsm_rw
GDSCTL>services
Service "gsm_rw.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%1", name: "cdb", db: "cdb", region: "msk", status: ready.

GDSCTL>start service -gdspool dbpoolora
GSM Warnings:
NET-40134: Service is already running on all possible databases

GDSCTL>services
Service "gsm_rpt.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%11", name: "cdb", db: "cdbstby", region: "spb", status: ready.
Service "gsm_rw.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%1", name: "cdb", db: "cdb", region: "msk", status: ready.

GDSCTL>databases
Database: "cdbstby" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: spb
   Service: "gsm_rpt" Globally started: Y Started: Y
            Scan: Y Enabled: Y Preferred: Y
   Service: "gsm_rw" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     dbpoolora%11
Database: "cdb" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: msk
   Service: "gsm_rpt" Globally started: Y Started: N
            Scan: Y Enabled: Y Preferred: Y
   Service: "gsm_rw" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     dbpoolora%1

GDSCTL>

5. Определите строку адреса для строки соединения с сервисами GDS и настройте файл tnsnames.ora
для подключения к сервисам gsm_rw (для чтения и записи) и gsm_rpt (для выполнения отчётов)

GDSCTL>config gsm
Catalog connection is established
Name                          Region                        ENDPOINT                                            
----                          ------                        --------                                            
gsm_main                      msk                           (ADDRESS=(HOST=gsm.ru.oracle.com)(PORT=1523)(PROTOCOL=tcp))

GDSCTL>
GDSCTL>exit

[oracle@gsm ~] cd clb
[oracle@gsm clb]$ echo "
GSM_RW_MSK= (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
    (CONNECT_DATA=
     (SERVICE_NAME=gsm_rw.dbpoolora.oradbcloud)
     (REGION=msk)))

GSM_RPT_MSK= (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
    (CONNECT_DATA=
     (SERVICE_NAME=gsm_rpt.dbpoolora.oradbcloud)
     (REGION=msk)))
" >> tnsnames.ora

6. Протестируйте работу сервисов в нормальном (не аварийном) режиме

[oracle@gsm clb]$ ./clb.sh GSM_RW_MSK
----------------------------------------------------------
Connection Load Balancing:
----------------------------------------------------------
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
(CONNECT_DATA= (SERVICE_NAME=gsm_rw.dbpoolora.oradbcloud) (REGION=msk)))
----------------------------------------------------------
Number of sessions to msk(primary): 10
Number of sessions to spb(standby): 0
----------------------------------------------------------
[oracle@gsm clb]$


[oracle@gsm clb]$ ./clb.sh GSM_RPT_MSK
----------------------------------------------------------
Connection Load Balancing:
----------------------------------------------------------
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
(CONNECT_DATA= (SERVICE_NAME=gsm_rpt.dbpoolora.oradbcloud) (REGION=msk)))
----------------------------------------------------------
Number of sessions to msk(primary): 0
Number of sessions to spb(standby): 10
----------------------------------------------------------
[oracle@gsm clb]$

7. Завершите работу экземпляра standby базы данных (имитация аварии в резервном датацентре)

[oracle@msk ~]$ ssh spb
oracle@spb's password:
Last login: Tue Dec 17 18:44:03 2013 from msk.ru.oracle.com
[oracle@spb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 17 19:25:38 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT DATABASE_ROLE , OPEN_MODE FROM V$DATABASE;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@spb ~]$

8. Протестируйте работу сервисов в аварийном режиме

[oracle@gsm clb]$ ./clb.sh GSM_RW_MSK
----------------------------------------------------------
Connection Load Balancing:
----------------------------------------------------------
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
(CONNECT_DATA= (SERVICE_NAME=gsm_rw.dbpoolora.oradbcloud) (REGION=msk)))
----------------------------------------------------------
Number of sessions to msk(primary): 10
Number of sessions to spb(standby): 0
----------------------------------------------------------
[oracle@gsm clb]$


[oracle@gsm clb]$ ./clb.sh GSM_RPT_MSK
----------------------------------------------------------
Connection Load Balancing:
----------------------------------------------------------
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
(CONNECT_DATA= (SERVICE_NAME=gsm_rpt.dbpoolora.oradbcloud) (REGION=msk)))
----------------------------------------------------------
Number of sessions to msk(primary): 10
Number of sessions to spb(standby): 0
----------------------------------------------------------
[oracle@gsm clb]$
[oracle@gsm clb]$ gdsctl
GDSCTL: Version 12.1.0.1.0 - Production on Tue Dec 17 19:32:43 EST 2013

Copyright (c) 2011, 2012, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSM_MAIN
GDSCTL>services
Service "gsm_rpt.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%1", name: "cdb", db: "cdb", region: "msk", status: ready.
Service "gsm_rw.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%1", name: "cdb", db: "cdb", region: "msk", status: ready.

GDSCTL>databases
Database: "cdbstby" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: spb
   Service: "gsm_rpt" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Service: "gsm_rw" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
Database: "cdb" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: msk
   Service: "gsm_rpt" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Service: "gsm_rw" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     dbpoolora%1

GDSCTL>

9. Запустите standby базу данных

[oracle@msk ~]$ ssh spb
oracle@spb's password:
Last login: Tue Dec 17 19:25:30 2013 from msk.ru.oracle.com
[oracle@spb ~]$ ./dbstart.sh

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 17-DEC-2013 19:34:34

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

TNS-01106: Listener using listener name LISTENER has already been started

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 17 19:34:34 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2293496 bytes
Variable Size             390070536 bytes
Database Buffers          402653184 bytes
Redo Buffers                6684672 bytes
Database mounted.
Database opened.
SQL> Pluggable Database opened.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@spb ~]$

10. С помощью утилиты dgmgrl убедитесь в нормальном статусе primary и standby баз данных и протестируйте работу сервисов в нормальном (не аварийном) режиме

[oracle@spb ~]$ dgmgrl sys/oracle
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration

Configuration - cdb

  Protection Mode: MaxPerformance
  Databases:
  cdb     - Primary database
    cdbstby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> exit
[oracle@spb ~]$

[oracle@gsm clb]$ ./clb.sh GSM_RW_MSK
----------------------------------------------------------
Connection Load Balancing:
----------------------------------------------------------
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
(CONNECT_DATA= (SERVICE_NAME=gsm_rw.dbpoolora.oradbcloud) (REGION=msk)))
----------------------------------------------------------
Number of sessions to msk(primary): 10
Number of sessions to spb(standby): 0
----------------------------------------------------------
[oracle@gsm clb]$

[oracle@gsm clb]$ ./clb.sh GSM_RPT_MSK
----------------------------------------------------------
Connection Load Balancing:
----------------------------------------------------------
Attempting to contact (DESCRIPTION= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
(CONNECT_DATA= (SERVICE_NAME=gsm_rpt.dbpoolora.oradbcloud) (REGION=msk)))
----------------------------------------------------------
Number of sessions to msk(primary): 0
Number of sessions to spb(standby): 10
----------------------------------------------------------
[oracle@gsm clb]$

[oracle@gsm clb]$ gdsctl
GDSCTL: Version 12.1.0.1.0 - Production on Tue Dec 17 19:39:51 EST 2013

Copyright (c) 2011, 2012, Oracle.  All rights reserved.

Welcome to GDSCTL, type "help" for information.

Current GSM is set to GSM_MAIN
GDSCTL>services
Service "gsm_rpt.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%11", name: "cdb", db: "cdbstby", region: "spb", status: ready.
Service "gsm_rw.dbpoolora.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
   Instance "dbpoolora%1", name: "cdb", db: "cdb", region: "msk", status: ready.

GDSCTL>databases
Database: "cdbstby" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: spb
   Service: "gsm_rpt" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Service: "gsm_rw" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     dbpoolora%11
Database: "cdb" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: msk
   Service: "gsm_rpt" Globally started: Y Started: N
            Scan: N Enabled: Y Preferred: Y
   Service: "gsm_rw" Globally started: Y Started: Y
            Scan: N Enabled: Y Preferred: Y
   Registered instances:
     dbpoolora%1

GDSCTL>exit
[oracle@gsm clb]$

11. Исследуйте возможности GDS по ограничению доступа пользователей к базам данных других регионов:

[oracle@gsm ~]$ gdsctl
GDSCTL>stop service -service gsm_rpt_local
GDSCTL>remove service -service gsm_rpt_local

GDSCTL>add service -service gsm_rpt_local -preferred_all -locality LOCAL_ONLY
GDSCTL>start service -service gsm_rpt_local
[oracle@gsm ~] echo "
GSM_RPT_MSKONLY= (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
    (CONNECT_DATA=
     (SERVICE_NAME=gsm_rpt_local.dbpoolora.oradbcloud)
     (REGION=msk)))
" >> tnsnames.ora
[oracle@gsm ~] echo "
GSM_RPT_SPBONLY= (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
    (CONNECT_DATA=
     (SERVICE_NAME=gsm_rpt_local.dbpoolora.oradbcloud)
     (REGION=spb)))
" >> tnsnames.ora
[oracle@gsm clb]$ ./clb.sh GSM_RPT_MSKONLY
[oracle@gsm clb]$ ./clb.sh GSM_RPT_SPBONLY

12. Исследуйте возможности GDS по ограничению доступа пользователей к базам данных,
у которых время синхронизации с основной БД больше допустимого:

[oracle@gsm ~]$ gdsctl
GDSCTL>stop service -service gsm_rpt_lag
GDSCTL>remove service -service gsm_rpt_lag

GDSCTL>add service -service gsm_rpt_lag -preferred_all -role PHYSICAL_STANDBY -failover_primary -lag 30
GDSCTL>start service -service gsm_rpt_lag
[oracle@gsm ~] echo "
GSM_RPT_LAG= (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL = TCP)(HOST=gsm.ru.oracle.com)(port=1523)))
    (CONNECT_DATA=
     (SERVICE_NAME=gsm_rpt_lag.dbpoolora.oradbcloud)
     (REGION=spb)))
" >> tnsnames.ora
[oracle@gsm clb]$ ./clb.sh GSM_RPT_LAG
[oracle@spb ~]$ dgmgrl sys/oracle
DGMGRL> show configuration
DGMGRL> edit database cdbstby set state ='APPLY-OFF';

[oracle@gsm clb]$ sqlplus system/oracle@msk:1521/cdb
SQL> alter system switch logfile;

DGMGRL> show database cdbstby
[oracle@gsm clb]$ ./clb.sh GSM_RPT_LAG
DGMGRL> edit database cdbstby set state ='APPLY-ON';
DGMGRL> show database cdbstby
[oracle@gsm clb]$ ./clb.sh GSM_RPT_LAG

Если в процессе выполнения у Вас возникли сложности, сообщите мне пожалуйста, я добавлю в описание более детальную информацию.


Желаю удачи в изучении Oracle Database 12с: Global Data Services!