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!
