使用DGBROKER配置dataguard测试

1、使用DGBROKER配置dataguard测试环境准备：准备两台电脑安装好linux X64，配置相同的环境变量，安装好oracle数据库软件，做为主端的数据库上建立好一个数据库实例，做为备端的数库库仅装好软件即可。主、备端都建好监听。测试：1、 主库开启强制日志oracleADGTEST1 $ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 15:02:17 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected t

2、o:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database force logging;Database altered.SQL> 2、 主库开归档日志SQL> shutdown immediate;Database closed.Database dismounted.ORACLE ins

3、tance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 1620115456 bytesFixed Size 2253704 bytesVariable Size 1073745016 bytesDatabase Buffers 536870912 bytesRedo Buffers 7245824 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter dat

4、abase open;Database altered.SQL> 3、 主库修改相关数据库参数a) 修改db_unique_nameSQL> alter system set db_unique_name='porcl' scope=spfile;System altered.SQL> b) 修改standby_file_managementSQL> alter system set standby_file_management='AUTO' scope=spfile;System altered.SQL> 4、 主库配置静态网络

5、监听采用图形方式进行配置，运行netmgr。 完成后，保存退出。注：“Oracle 主目录”框中的路径最后结尾的那个“/”需要删除。完成后的内容：oracleADGTEST1 $ cd $ORACLE_HOME/network/adminoracleADGTEST1 admin$ cat listener.ora # listener.ora Network Configuration File: /mnt_array/OracleDB/DB/oracle/network/admin/listener.ora# Generated by Oracle configuration tools.S

6、ID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /mnt_array/OracleDB/DB/oracle) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = porcl) (ORACLE_HOME = /mnt_array/OracleDB/DB/oracle) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = porcl_DGMGRL) (ORACLE_HOME = /mnt_arr

7、ay/OracleDB/DB/oracle) (SID_NAME = orcl) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ADGTEST1)(PORT = 1521) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) ) )ADR_BASE_LISTENER = /mnt_array/OracleDB/DBoracleADGTEST1 admin$ 5、 主库重启网络监听oracleADGT

8、EST1 admin$ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 15:22:16Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ADGTEST1)(PORT=1521)The command completed successfullyoracleADGTEST1 admin$ lsnrctl startLSN

9、RCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 15:22:20Copyright (c) 1991, 2013, Oracle. All rights reserved.Starting /mnt_array/OracleDB/DB/oracle/bin/tnslsnr: please wait.TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionSystem parameter file is /mnt_array/OracleDB/DB/oracle/networ

10、k/admin/listener.oraLog messages written to /mnt_array/OracleDB/DB/diag/tnslsnr/ADGTEST1/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADGTEST1)(PORT=1521)Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=

11、TCP)(HOST=ADGTEST1)(PORT=1521)STATUS of the LISTENER-Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 16-JAN-2017 15:22:20Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /mnt_array/OracleDB/DB/oracle

12、/network/admin/listener.oraListener Log File /mnt_array/OracleDB/DB/diag/tnslsnr/ADGTEST1/listener/alert/log.xmlListening Endpoints Summary. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADGTEST1)(PORT=1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)Services Summary.Service "orcl" h

13、as 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service.Service "porcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service.Service "porcl_DGMGRL" has 1 instance(s). Instance "orcl", stat

14、us UNKNOWN, has 1 handler(s) for this service.The command completed successfullyoracleADGTEST1 admin$ 可以看到刚才建好的三个静态网络监听。6、 备份数据库oracleADGTEST1 admin$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 16 15:29:21 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All

15、rights reserved.connected to target database: ORCL (DBID=1461199451)RMAN> backup database;Starting backup at 16-JAN-17using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=14 device type=DISKchannel ORA_DISK_1: starting full datafile ba

16、ckup setFinished backup at 16-JAN-17RMAN> exitRecovery Manager complete.oracleADGTEST1 admin$ 7、 建立pfile文件oracleADGTEST1 admin$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 15:31:08 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database

17、11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create pfile from spfile;File created.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Pa

18、rtitioning, OLAP, Data Mining and Real Application Testing optionsoracleADGTEST1 admin$ 8、 拷贝参数文件到备库端oracleADGTEST1 admin$ cd $ORACLE_HOME/dbsoracleADGTEST1 dbs$ lshc_orcl.dat init.ora initorcl.ora lkORCL orapworcl snapcf_orcl.f spfileorcl.oraoracleADGTEST1 dbs$ pwd/mnt_array/OracleDB/DB/oracle/dbso

19、racleADGTEST1 dbs$ scp initorcl.ora orapworcl 192.168.0.115:/mnt_array/OracleDB/DB/oracle/dbsAddress 192.168.0.115 maps to bogon, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!oracle192.168.0.115's password: initorcl.ora 100% 1087 1.1KB/s 00:00 orapworcl 100% 1536 1.5KB/s

20、 00:00 oracleADGTEST1 dbs$ 9、 备库配置静态网络监听采用图形方式进行配置，运行netmgr。 完成后，保存退出。注：“Oracle 主目录”框中的路径最后结尾的那个“/”需要删除。10、 备库重启网络监听oracleTestLinux $ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 15:49:06Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION

21、=(ADDRESS=(PROTOCOL=TCP)(HOST=TestLinux)(PORT=1521)The command completed successfullyoracleTestLinux $ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 15:49:14Copyright (c) 1991, 2013, Oracle. All rights reserved.Starting /mnt_array/OracleDB/DB/oracle/bin/tnslsnr: plea

22、se wait.TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionSystem parameter file is /mnt_array/OracleDB/DB/oracle/network/admin/listener.oraLog messages written to /mnt_array/OracleDB/DB/diag/tnslsnr/TestLinux/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestLinux)(PO

23、RT=1521)Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestLinux)(PORT=1521)STATUS of the LISTENER-Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 16-JAN-2017 15:49:14Uptime 0 days 0 hr. 0

24、min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /mnt_array/OracleDB/DB/oracle/network/admin/listener.oraListener Log File /mnt_array/OracleDB/DB/diag/tnslsnr/TestLinux/listener/alert/log.xmlListening Endpoints Summary. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)

25、(HOST=TestLinux)(PORT=1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)Services Summary.Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service.Service "sorcl" has 1 instance(s). Instance "sorcl", status U

26、NKNOWN, has 1 handler(s) for this service.Service "sorcl_DGMGRL" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service.The command completed successfullyoracleTestLinux $ 11、 备库修改initorcl.ora文件oracleTestLinux $ cd $ORACLE_HOME/dbsoracleTestLinux db

27、s$ lsinit.ora initorcl.ora orapworcloracleTestLinux dbs$ vi initorcl.ora修改内容：（因为主库和备库环境一样，所有的路径也一样，所以只需要修改db_unique_name参数）*.db_unique_name='porcl' -> *.db_unique_name='sorcl'12、 备库如果有spfileorcl.ora文件，删除。oracleTestLinux dbs$ lsinit.ora initorcl.ora orapworcloracleTestLinux dbs$ 因为

28、没有建立数据库，所以这里现在没有该文件。13、 备库跟据initorcl.ora内容建立相关文件夹oracleTestLinux dbs$ grep "/DB/" initorcl.ora*.audit_file_dest='/mnt_array/OracleDB/DB/admin/orcl/adump'*.control_files='/mnt_array/OracleDB/DB/oradata/orcl/control01.ctl','/mnt_array/OracleDB/DB/fast_recovery_area/orcl/c

29、ontrol02.ctl'*.db_recovery_file_dest='/mnt_array/OracleDB/DB/fast_recovery_area'oracleTestLinux dbs$ mkdir /mnt_array/OracleDB/DB/admin/orcl/adump -poracleTestLinux dbs$ mkdir /mnt_array/OracleDB/DB/oradata/orcl -poracleTestLinux dbs$ mkdir /mnt_array/OracleDB/DB/fast_recovery_area/orcl

30、-p14、 备库启动数据库到nomount状态oracleTestLinux dbs$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 16:00:24 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> startup nomount;ORACLE instance started.Total System Global Area 1620115456 by

31、tesFixed Size 2253704 bytesVariable Size 1073745016 bytesDatabase Buffers 536870912 bytesRedo Buffers 7245824 bytesSQL> 15、 备库将pfile转为spfile，再重启数据库到nomount状态SQL> create spfile from pfile;File created.SQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> st

32、artup nomount;ORACLE instance started.Total System Global Area 1620115456 bytesFixed Size 2253704 bytesVariable Size 1073745016 bytesDatabase Buffers 536870912 bytesRedo Buffers 7245824 bytesSQL> 16、 修改主库本地命名oracleADGTEST1 dbs$ cd $ORACLE_HOME/network/adminoracleADGTEST1 admin$ lslistener.ora sam

33、ples shrept.lst tnsnames.oraoracleADGTEST1 admin$ vi tnsnames.ora修改后的内容：# tnsnames.ora Network Configuration File: /mnt_array/OracleDB/DB/oracle/network/admin/tnsnames.ora# Generated by Oracle configuration tools.ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.73)(PORT = 1521) (CO

34、NNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )PORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.73)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )SORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.115)(PORT = 1521) (CONNECT

35、_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )17、 修改备库本地命名备端没有建立数据库实例，没有此文件，从主库端拷贝过来。主库端：oracleADGTEST1 admin$ pwd/mnt_array/OracleDB/DB/oracle/network/adminoracleADGTEST1 admin$ scp tnsnames.ora oracle192.168.0.115:/mnt_array/OracleDB/DB/oracle/network/adminAddress 192.168.0.115 maps to bog

36、on, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!oracle192.168.0.115's password: tnsnames.ora 100% 678 0.7KB/s 00:00 oracleADGTEST1 admin$ 备库端：oracleTestLinux admin$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /mnt_array/OracleDB/DB/oracle/network/admin/t

37、nsnames.ora# Generated by Oracle configuration tools.ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.115)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )PORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.73)(PORT = 1521) (CONNECT_DATA =

38、 (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )SORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.115)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )oracleTestLinux admin$ 18、 在主库端增加备库日志，数量为（进程数1）X2，单服务器进程数为1，即要增加4个日志。oracleADGTEST1 admin$ sqlplus / as s

39、ysdbaSQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 16:40:07 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>

40、alter database add standby logfile group 10;Database altered.SQL> alter database add standby logfile group 11;Database altered.SQL> alter database add standby logfile group 12;Database altered.SQL> alter database add standby logfile group 13;Database altered.SQL> exitDisconnected from Or

41、acle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoracleADGTEST1 admin$ 19、备库端用RMAN将主库克隆过来oracleTestLinux admin$ rman target sys/oracleporcl auxiliary sys/oraclesorclRecovery Manager: Release 11.2.0.

42、4.0 - Production on Mon Jan 16 16:49:12 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: ORCL (DBID=1461199451)connected to auxiliary database: ORCL (not mounted)RMAN> duplicate target database for standby nofilenamecheck from active da

43、tabase;Starting Duplicate Db at 16-JAN-17using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=359 device type=DISKdatafile 3 switched to datafile copyinput datafile copy RECID=3 STAMP=933439987 file name=/mnt_array/OracleDB/DB/oradata/orcl/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=4 STAMP=933439988 fi