因为虚拟机IP 地址冲突,需要修改DG IP 地址,顺便测试一下 DG 修改IP的步骤。
一. IP 及数据库信息
修改之前:
主库:192.168.1.60
备库:192.168.1.61
修改之后:
主库:192.168.1.10
备库:192.168.1.11
数据库版本: 12.2.0.1.0
操作系统版本: Redhat 7.3
二. Shutdown 主备库实例及监听
2.1 shutdown 主库
[oracle@dg1 ~]$ sqlplus /nolog
[
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 16:58:46 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL>conn /as sysdba
Connected.
SQL> select db_unique_name,open_mode from v$database;
DB_UNIQUE_NAME OPEN_MODE
------------------------------ --------------------
pdbcndba_p READ WRITE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2.2 shutdown 备库
[oracle@dg2 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 17:07:58 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select db_unique_name,open_mode from v$database;
DB_UNIQUE_NAME OPEN_MODE
------------------------------ --------------------
pdbcndba_s READ ONLY WITH APPLY
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2.3 停主备库的监听
--主库
[oracle@dg1 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 26-MAY-2017 17:09:42
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
The command completed successfully
--备库
[oracle@dg2 ~]$ lsnrctl stop
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 26-MAY-2017 17:09:45
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))
The command completed successfully
三. 修改服务器IP信息
3.1 主库操作
3.1.1修改IP
[root@dg1 ~]# cat /etc/hosts
127.0.0.1 localhost
192.168.1.10 dg1
192.168.1.11 dg2
[root@dg1 ~]# cd /etc/sysconfig/network-scripts/
[root@dg1 network-scripts]# ls
ifcfg-enp0s3 ifdown-ib ifdown-ppp ifdown-tunnel ifup-ib ifup-plusb ifup-Team network-functions
ifcfg-lo ifdown-ippp ifdown-routes ifup ifup-ippp ifup-post ifup-TeamPort network-functions-ipv6
ifdown ifdown-ipv6 ifdown-sit ifup-aliases ifup-ipv6 ifup-ppp ifup-tunnel
ifdown-bnep ifdown-isdn ifdown-Team ifup-bnep ifup-isdn ifup-routes ifup-wireless
ifdown-eth ifdown-post ifdown-TeamPort ifup-eth ifup-plip ifup-sit init.ipv6-global
[root@dg1 network-scripts]# cat /etc/sysconfig/network-scripts/ifcfg-enp0s3
TYPE="Ethernet"
BOOTPROTO="none"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="enp0s3"
UUID="4d53412d-c2c8-46c6-8d8d-57e4d05007e1"
DEVICE="enp0s3"
ONBOOT="yes"
IPADDR="192.168.1.10"
PREFIX="24"
GATEWAY="192.168.1.1"
DNS1=114.114.114.114
IPV6_PEERDNS="yes"
IPV6_PEERROUTES="yes"
IPV6_PRIVACY="no"
3.1.2 重启network
注意这里是redhat 7.3 重启网络服务命令
linux 6 重启命令 service network restart
[root@dg1 network-scripts]# systemctl restart network.service
[root@dg1 ~]# ifconfig
enp0s3: flags=4163
mtu 1500 inet 192.168.1.10 netmask 255.255.255.0 broadcast 192.168.1.255 inet6 fe80::4c05:b135:3ecf:4423 prefixlen 64 scopeid 0x20
ether 08:00:27:ad:20:fe txqueuelen 1000 (Ethernet) RX packets 1495050 bytes 147897088 (141.0 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 2988290 bytes 7840223919 (7.3 GiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 lo: flags=73
mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10
loop txqueuelen 1 (Local Loopback) RX packets 461408 bytes 122914610 (117.2 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 461408 bytes 122914610 (117.2 MiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 virbr0: flags=4099
mtu 1500 inet 192.168.122.1 netmask 255.255.255.0 broadcast 192.168.122.255 ether 52:54:00:50:ba:a8 txqueuelen 1000 (Ethernet) RX packets 0 bytes 0 (0.0 B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 0 bytes 0 (0.0 B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
3.1.3修改监听信息
[oracle@dg1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cndba)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = cndba)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CNDBA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
pdbcndba_p =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
pdbcndba_s =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
3.2 备库操作
3.2.1修改IP
[root@dg2 ~]# cat /etc/hosts
127.0.0.1 localhost
192.168.1.10 dg1
192.168.1.11 dg2
[root@dg2 ~]# cd /etc/sysconfig/network-scripts/
[root@dg2 network-scripts]# ls
ifcfg-enp0s3 ifdown-ib ifdown-ppp ifdown-tunnel ifup-ib ifup-plusb ifup-Team network-functions
ifcfg-lo ifdown-ippp ifdown-routes ifup ifup-ippp ifup-post ifup-TeamPort network-functions-ipv6
ifdown ifdown-ipv6 ifdown-sit ifup-aliases ifup-ipv6 ifup-ppp ifup-tunnel
ifdown-bnep ifdown-isdn ifdown-Team ifup-bnep ifup-isdn ifup-routes ifup-wireless
ifdown-eth ifdown-post ifdown-TeamPort ifup-eth ifup-plip ifup-sit init.ipv6-global
[root@dg2 ~]# cat /etc/sysconfig/network-scripts/ifcfg-enp0s3
TYPE="Ethernet"
BOOTPROTO="none"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="enp0s3"
UUID="1fa4056e-afbb-44ea-a368-1c36a3242cee"
DEVICE="enp0s3"
ONBOOT="yes"
IPADDR="192.168.1.11"
PREFIX="24"
GATEWAY="192.168.1.1"
IPV6_PEERDNS="yes"
IPV6_PEERROUTES="yes"
IPV6_PRIVACY="no"
3.2.2 重启network
[root@dg2 network-scripts]# systemctl restart network.service
3.2.3 修改监听信息
[root@dg2 ~]# su - oracle
Last login: Fri May 26 17:07:55 CST 2017 on pts/0
[oracle@dg2 ~]$ cd $ORACLE_HOME
[oracle@dg2 db_1]$ cd network/admin
[oracle@dg2 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@dg2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cndba)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = cndba)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CNDBA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
pdbcndba_p =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
pdbcndba_s =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = cndba)
)
)
四. 启动Data Guard并验证同步
4.1 启动备库监听和实例
4.1.1 启动监听
[oracle@dg2 admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 26-MAY-2017 17:40:27
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg2)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 26-MAY-2017 17:40:27
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dg2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg2)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cndba" has 1 instance(s).
Instance "cndba", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4.1.2 启动实例
[oracle@dg2 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 17:42:20 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8793256 bytes
Variable Size 1023411032 bytes
Database Buffers 553648128 bytes
Redo Buffers 7983104 bytes
SQL> startup
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8793256 bytes
Variable Size 1023411032 bytes
Database Buffers 553648128 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
4.2 启动主库监听和实例
4.2.1 启动主库监听
[oracle@dg1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 26-MAY-2017 17:40:06
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 26-MAY-2017 17:40:06
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "cndba" has 1 instance(s).
Instance "cndba", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4.2.2 启动主库实例至open状态
oracle@dg1 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 17:48:08 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1593835520 bytes
Fixed Size 8793256 bytes
Variable Size 989856600 bytes
Database Buffers 587202560 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
4.3 验证主备库同步
--主库操作
SQL> alter session set container=pdbcndba;
Session altered.
SQL> select count(*) from cndba;
select count(*) from cndba
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDBCNDBA MOUNTED
SQL> alter database open;
Database altered.
SQL> SQL> select count(*) from cndba;
COUNT(*)
----------
37
SQL> insert into cndba select * from dba_users;
38 rows created.
SQL> commit;
Commit complete.
SQL> select count(1) from cndba;
COUNT(1)
----------
75
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@dg1 admin]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 17:51:07 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBCNDBA READ WRITE NO
SQL> alter system switch logfile;
System altered.
--备库操作
SQL> alter session set container=pdbcndba;
Session altered.
SQL> select count(1) from cndba;
select count(1) from cndba
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter database open;
Database altered.
SQL> select count(1) from cndba;
COUNT(1)
----------
75