- 检查资料库是否存在GSM用户(必备条件)
#12c后新增了几个用户，下面这些用户用于GDS
[oracle@XAG138 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
SQL> select username,account_status from dba_users where username like '%GSM%';
USERNAME ACCOUNT_STATUS
GSMCATUSER EXPIRED & LOCKED
GSMUSER EXPIRED & LOCKED
GSMADMIN_INTERNAL EXPIRED & LOCKED
- Catalog DB(资料库)信息
| 项目名称 | 项目值 | 配置文件 |
|---|---|---|
| 主机名 | XAG138 | hosts |
| ORACLE_SID(资料库) | ORACLE_SID=XAG12C ，PORT=1521 | /home/oracle/.bash_profile |
| ORACLE_HOME | ORACLE_BASE=/u01/app/oracle | 同上 |
| ORACLE_HOME | ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1 | 同上 |
2.1 GSM 信息
| 项目名称 | 项目值 | 配置文件/主机 |
|---|---|---|
| 节点 1 GSM 名称 | GSM196 | DB196 |
| 节点 2 GSM 名称 | GSM197 | DB197 |
| 节点 1 GSM Port | 1571 | DB196 |
| 节点 2 GSM Port | 1572 | DB197 |
| ORACLE_BASE | /u05/app/oracle | /home/oracle/.gsm |
| ORACLE_HOME | $ORACLE_BASE/product/12.2.0/gsm_1 | 同上 |
- Active DataGuard信息
| 项目名称 | Primary | Standby |
|---|---|---|
| 主机名 | DB196 | DB197 |
| DB Unique Name | MPCDB196 | MPCDB197 |
| Instance Name | MPCDB | MPCDB |
| Port | 1521 | 1521 |
| ORACLE_BASE | /u01/app/oracle | - |
| ORACLE_HOME | $ORACLE_BASE/product/12.2.0/db_1 | - |
- 安装 GDS环境
# 查看hosts配置(两台GSM服务器DB196、DB197 增加 资料库XAG138 配置)
[root@DB196 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.40.196 DB196 DB196.COM
192.168.40.197 DB197 DB197.COM
192.168.40.138 XAG138 XAG138.COM
#创建 gds 用户（每台gsm服务器）
[root@DB196 ~]# useradd -g oinstall -G dba,oper gds
[root@DB196 ~]# echo "123456" | passwd --stdin gds
#创建安装目录
mkdir -p /u05/app/oracle/
chown -R gds:oinstall /u05/app/
chmod -R 775 /u05/app/
mkdir -p /u05/tmp/
chown -R gds:oinstall /u05/tmp/
chmod -R 775 /u05/tmp/
# 查看环境变量&安装GSM(两台GSM服务器DB196、DB197 都要有此配置且都安装)
[oracle@DB196 ~]$ su - gds
[gds@DB196 ~]$ vim /home/gds/.bash_profile
[gds@DB196 ~]$ source /home/gds/.bash_profile
[gds@DB196 ~]$ cat /home/gds/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export DISPLAY=192.168.0.4:0.0
ORACLE_BASE=/u05/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.2.0/gsm_1; export ORACLE_HOME
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
export PATH=${PATH}:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export TEMP=/u05/tmp
export TMPDIR=/u05/tmp
umask 022
[oracle@DB196 ~]$ unzip linuxx64_12201_gsm.zip
[oracle@DB196 ~]$ cd gsm
[oracle@DB196 gsm]$ ./runInstaller
# 安装很简单，一直点 next
- 配置 GSM Home’s tnsnames.ora (两台 GSM 服务器都要配置)
#无需配置
- Setup GDS Administrator Accounts & Privileges
6.1 主库上解锁 gsmuser 用户（备库会自动同步解锁）
#在主库上unlock数据库用户gsmuser
[oracle@DB196 ~]$ sql system/123456
SQL> SET SQLFORMAT ansiconsole
SQL> select username,account_status from dba_users where username like '%GSM%';
USERNAME ACCOUNT_STATUS
GSMCATUSER EXPIRED & LOCKED
GSMUSER EXPIRED & LOCKED
GSMADMIN_INTERNAL EXPIRED & LOCKED
SQL> alter user gsmuser identified by gsmuser account unlock;
SQL> select username,account_status from dba_users where username like '%GSM%';
USERNAME ACCOUNT_STATUS
GSMCATUSER EXPIRED & LOCKED
GSMUSER OPEN
GSMADMIN_INTERNAL EXPIRED & LOCKED
SQL> select instance_name,instance_role from gv$instance;
INSTANCE_NAME INSTANCE_ROLE
MPCDB PRIMARY_INSTANCE
SQL> select open_mode from gv$database;
OPEN_MODE
READ WRITE
#在备库上查询
[oracle@DB197 ~]$ sql system/123456@SL
SQL> SET SQLFORMAT ansiconsole
SQL> select instance_name,instance_role from gv$instance;
INSTANCE_NAME INSTANCE_ROLE
MPCDB PRIMARY_INSTANCE
SQL> select username,account_status from dba_users where username like '%GSM%';
USERNAME ACCOUNT_STATUS
GSMCATUSER EXPIRED & LOCKED
GSMUSER OPEN
GSMADMIN_INTERNAL EXPIRED & LOCKED
SQL> select open_mode from gv$database;
OPEN_MODE
READ ONLY WITH APPLY
6.2 解锁 gsmcatuser & Grant GSMADMIN_ROLE to an existing user or to a newly created user
#登录资料库（GDS catalog）
[gds@DB196 admin]$ sqlplus system/123456@XAG138:1521/XAG12C
SQL> show parameter db_unique
NAME TYPE VALUE
---------------------- ---------------------------------------------------------
db_unique_name string XAG12C
SQL> alter user gsmcatuser account unlock;
SQL> alter user gsmcatuser identified by gsmcatuser;
SQL> create user c##mygdsadmin identified by mygdsadmin;
SQL> grant gsmadmin_role to c##mygdsadmin;
- Configure GDS
7.1 创建GDS Catalog(资料库)
# gsm196环境下create catalog
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
or
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521:XAG12C
#如已存在 catalog 则先删除
GDSCTL>delete catalog -connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>create catalog -database XAG138:1521/XAG12C -user c##mygdsadmin/mygdsadmin
or
GDSCTL>create catalog -database XAG138:1521:XAG12C -user c##mygdsadmin/mygdsadmin
7.2 为GDS Catalog增加一个gsm196
GDSCTL>add gsm -gsm gsm196 -listener 1571 -catalog XAG138:1521/XAG12C -pwd gsmcatuser
GDSCTL>start gsm -gsm gsm196
GDSCTL>config gsm -gsm gsm196
Name: gsm196
Endpoint 1: (ADDRESS=(HOST=DB196)(PORT=1571)(PROTOCOL=tcp))
Local ONS port: 6123
Remote ONS port: 6234
ORACLE_HOME path: /u01/app/oracle/product/12.2.0/gsmhome_1
GSM Host name: DB196
Region: regionora
Buddy
------------------------
GDSCTL>status gsm -gsm gsm196
Alias GSM196
Version 12.2.0.1.0
Start Date 14-APR-2019 19:59:10
Trace Level off
Listener Log File /u01/app/oracle/diag/gsm/DB196/gsm196/alert/log.xml
Listener Trace File /u01/app/oracle/diag/gsm/DB196/gsm196/trace/ora_39557_139886588080512.trc
Endpoint summary (ADDRESS=(HOST=DB196)(PORT=1571)(PROTOCOL=tcp))
GSMOCI Version 2.2.1
Mastership Y
Connected to GDS catalog Y
Process Id 39560
Number of reconnections 0
Pending tasks. Total 0
Tasks in process. Total 0
Regional Mastership TRUE
Total messages published 0
Time Zone +08:00
Orphaned Buddy Regions:
None
GDS region regionora
GDSCTL>exit
7.3 为GDS Catalog增加一个gsm197 （第2个）
[gds@DB197 ~]$ gdsctl
GDSCTL>add gsm -gsm gsm197 -listener 1572 -catalog XAG138:1521/XAG12C -pwd gsmcatuser
GDSCTL>start gsm -gsm gsm197
GDSCTL>config gsm -gsm gsm197
GDSCTL>status gsm -gsm gsm197
7.4 Create the GDS regions
[gds@DB196 ~]$ gdsctl
GDSCTL>add region -region region196,region197
#Assign a region to each GSM:
GDSCTL>modify gsm -gsm gsm196 -region region196
[oracle@DB197 ~]$ gdsctl
GDSCTL>modify gsm -gsm gsm197 -region region197
#先连接到 GDS Catalog (后续 gdspool、region、service 等操作都需要先连接上)
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>modify gsm -gsm gsm196 -region region196
#先连接到 GDS Catalog (后续 gdspool、region、service 等操作都需要先连接上)
[gds@DB197 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>modify gsm -gsm gsm197 -region region197
................................
#Create the GDS pools
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>add gdspool -gdspool poolsales
- 增加database/brokerconfig到gdspool
#注意：如果配置了DG Broker，可以通过add brokerconfig添加数据库，如果没有，可以通过add database添加
#测试
GDSCTL>connect gsmuser/gsmuser@DB196:1521/MPCDB196
or
GDSCTL>connect gsmuser/gsmuser@DB196:1521:MPCDB
GDSCTL>exit
[oracle@DB197 admin]$ dgmgrl sysdg/123456
Connected to "MPCDB197"
Connected as SYSDG.
DGMGRL> show configuration
----------------------------------------------------------------------
Configuration - ADGbroker
Protection Mode: MaxAvailability
Members:
MPCDB196 - Primary database
MPCDB197 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 47 seconds ago)
----------------------------------------------------------------------
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL> add brokerconfig -connect DB196:1521/MPCDB196 -region region196 -gdspool poolsales -pwd gsmuser
or
GDSCTL> add brokerconfig -connect DB196:1521:MPCDB -region region196 -gdspool poolsales -pwd gsmuser
DB Unique Name: mpcdb196
The operation completed successfully
GDSCTL>config database
Name Pool Status State Region Availability
---- ---- ------ ----- ------ ------------
mpcdb196 poolsales Ok none region196 -
mpcdb197 poolsales Ok none -
GDSCTL>status database
Database: "mpcdb196" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region196
Registered instances:
poolsales%1
Database: "mpcdb197" Registered: N State: Ok ONS: N. Role: N/A Instances: 0 Region: N/A
#DB197上
[gds@DB197 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>modify database -database MPCDB197 -region region197 -gdspool poolsales
GDSCTL>config database
Name Pool Status State Region Availability
---- ---- ------ ----- ------ ------------
mpcdb196 poolsales Ok none region196 -
mpcdb197 poolsales Ok none region197 -
GDSCTL>status database
Database: "mpcdb196" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region196
Registered instances:
poolsales%1
Database: "mpcdb197" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: region197
Registered instances:
poolsales%11
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>config region
Name Buddy
---- -----
region196
region197
regionora
GDSCTL>modify region -region region196 -buddy region197
GDSCTL>modify region -region region197 -buddy region196
GDSCTL>config region
Name Buddy
---- -----
region196 region197
region197 region196
regionora
[gds@DB197 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>config region
Name Buddy
---- -----
region196 region197
region197 region196
regionora
- Create and Start Global Service in a pool
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>add service -service sales_report_srvc -gdspool poolsales -preferred_all -role PHYSICAL_STANDBY -failover_primary
GDSCTL>start service -service sales_report_srvc -gdspool poolsales
GDSCTL>status service -service sales_report_srvc
Service "sales_report_srvc.poolsales.oradbcloud" has 1 instance(s). Affinity: ANYWHERE
Instance "poolsales%11", name: "MPCDB", db: "MPCDB197", region: "region197", status: ready.
#GDSCTL>start service -gdspool poolsales （此方法启动所有服务）
#GSM Warnings:
#GSM-40134: Service is already running on all possible databases
GDSCTL>config service -service sales_report_srvc
Name: sales_report_srvc
Network name: sales_report_srvc.poolsales.oradbcloud
Pool: poolsales
Started: Yes
Preferred all: Yes
Locality: ANYWHERE
Region Failover: No
Role: PHYSICAL_STANDBY
Primary Failover: Yes
Lag: ANY
Runtime Balance: SERVICE_TIME
Connection Balance: LONG
Notification: Yes
TAF Policy: NONE
Policy: AUTOMATIC
DTP: No
Failover Method: NONE
Failover Type: NONE
Failover Retries:
Failover Delay:
Edition:
PDB:
Commit Outcome:
Retention Timeout:
Replay Initiation Timeout:
Session State Consistency:
SQL Translation Profile:
Stop option: NONE
Drain timeout:
Databases
------------------------
Database Preferred Status
-------- --------- ------
mpcdb196 Yes Enabled
mpcdb197 Yes Enabled
GDSCTL>config
Regions
------------------------
region196
region197
regionora
GSMs
------------------------
gsm196
gsm197
GDS pools
------------------------
dbpoolora
poolsales
Databases
------------------------
mpcdb196
mpcdb197
Services
------------------------
sales_report_srvc
GDSCTL pending requests
------------------------
Command Object Status
------- ------ ------
Global properties
------------------------
Name: oradbcloud
Master GSM: gsm196
DDL sequence #: 0
- 检查
[oracle@DB196 ~]$ sql / as sysdba
SQL> SET SQLFORMAT ansiconsole
SQL> select name, network_name, global_service from dba_services;
NAME NETWORK_NAME GLOBAL_SERVICE
SYS$BACKGROUND NO
SYS$USERS NO
MPCDB196 MPCDB196 NO
MPCDB197 MPCDB197 NO
MPCDBXDB MPCDBXDB NO
MPCDB MPCDB NO
MPCDB_CFG MPCDB_CFG NO
MPCDB196_DGB MPCDB196_DGB NO
MPCDB197_DGB MPCDB197_DGB NO
sales_report_srvc sales_report_srvc.poolsales.oradbcloud YES
SQL> select name, network_name, global from v$active_services;
NAME NETWORK_NAME GLOBAL
MPCDB196 MPCDB196 NO
MPCDB MPCDB NO
SYS$BACKGROUND NO
MPCDB_CFG MPCDB_CFG NO
SYS$USERS NO
MPCDBXDB MPCDBXDB NO
MPCDB196_DGB MPCDB196_DGB NO
[oracle@DB197 ~]$ sql system/123456@SL
SQL> SET SQLFORMAT ansiconsole
SQL> select name, network_name, global_service from dba_services;
NAME NETWORK_NAME GLOBAL_SERVICE
SYS$BACKGROUND NO
SYS$USERS NO
MPCDB196 MPCDB196 NO
MPCDB197 MPCDB197 NO
MPCDBXDB MPCDBXDB NO
MPCDB MPCDB NO
MPCDB_CFG MPCDB_CFG NO
MPCDB196_DGB MPCDB196_DGB NO
MPCDB197_DGB MPCDB197_DGB NO
sales_report_srvc sales_report_srvc.poolsales.oradbcloud YES
SQL> select name, network_name, global from v$active_services;
NAME NETWORK_NAME GLOBAL
MPCDB197 MPCDB197 NO
MPCDB MPCDB NO
SYS$BACKGROUND NO
sales_report_srvc sales_report_srvc.poolsales.oradbcloud YES
MPCDB_CFG MPCDB_CFG NO
SYS$USERS NO
MPCDBXDB MPCDBXDB NO
MPCDB197_DGB MPCDB197_DGB NO
- 客户端 client’s tnsnames.ora
#说明hosts 文件增加主机名映射，否则1571、1572端口无法访问（增加如下2行）
192.168.40.196 DB196 DB196.COM
192.168.40.197 DB197 DB197.COM
sales_report_srvc =
(DESCRIPTION =
(FAILOVER=ON)
(ADDRESS_LIST =
(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL = TCP)(HOST=192.168.40.196)(PORT=1571))
)
(ADDRESS_LIST=
(LOAD_BALANCE=ON)
(ADDRESS=(PROTOCOL = TCP)(HOST=192.168.40.197)(PORT=1572))
)
(CONNECT_DATA =
(SERVICE_NAME=sales_report_srvc.poolsales.oradbcloud)(REGION=REGION196)
)
)
[gds@DB196 admin]$ lsnrctl status
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB196)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "MPCDB" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB196" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB196_DGB" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB196_DGMGRL" has 1 instance(s).
Instance "MPCDB", status UNKNOWN, has 1 handler(s) for this service...
Service "MPCDBXDB" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB_CFG" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[gds@DB197 ~]$ lsnrctl status
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB197)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "MPCDB" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB197" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB197_DGB" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB197_DGMGRL" has 1 instance(s).
Instance "MPCDB", status UNKNOWN, has 1 handler(s) for this service...
Service "MPCDBXDB" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "MPCDB_CFG" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "sales_report_srvc.poolsales.oradbcloud" has 1 instance(s).
Instance "MPCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
- test
客户端连接测试
[oracle@XAG138 ~]$ sql system/123456@//DB197:1521/sales_report_srvc.poolsales.oradbcloud
SQL> SET SQLFORMAT ansiconsole
Bad Format specified
SQL> select instance_name,host_name,sys_context('USERENV', 'INSTANCE_NAME') as instance_name2 from v$instance;
INSTANCE_NAME HOST_NAME INSTANCE_NAME2
---------------- ---------------------------------------------------------------
MPCDB DB197.COM poolsales%11
[oracle@XAG138 ~]$ sql system/123456@//192.168.40.196:1571/sales_report_srvc.poolsales.oradbcloud
SQL> SET SQLFORMAT ansiconsole
SQL> select instance_name,host_name,sys_context('USERENV', 'INSTANCE_NAME') as instance_name2 from v$instance;
INSTANCE_NAME HOST_NAME INSTANCE_NAME2
MPCDB DB197.COM poolsales%11
[oracle@XAG138 ~]$ sql system/123456@//192.168.40.197:1572/sales_report_srvc.poolsales.oradbcloud
SQL> SET SQLFORMAT ansiconsole
SQL> select instance_name,host_name,sys_context('USERENV', 'INSTANCE_NAME') as instance_name2 from v$instance;
INSTANCE_NAME HOST_NAME INSTANCE_NAME2
MPCDB DB197.COM poolsales%11
- 管理（先启动gsm，后启动 service）
[gds@DB196 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL> start service -service sales_report_srvc -gdspool poolsales
GDSCTL> stop service -service sales_report_srvc -gdspool poolsales
GDSCTL> status service -service sales_report_srvc
GDSCTL>status gsm -gsm gsm196
GDSCTL>start gsm -gsm gsm196
GDSCTL>stop gsm -gsm gsm196
[gds@DB197 ~]$ gdsctl
GDSCTL>connect c##mygdsadmin/mygdsadmin@XAG138:1521/XAG12C
GDSCTL>status gsm -gsm gsm197
GDSCTL>start gsm -gsm gsm197
GDSCTL>stop gsm -gsm gsm197
