使用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 instance shut d

3、own.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 database open;Database alte

4、red.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、 主库配置静态网络监听采用图形方式进行配置，运行netmgr。 完成后，保存退出。注：“Oracle 主目录”框中的路径最后结尾的那个“/”需要删

5、除。完成后的内容：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.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl

6、) (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_array/OracleDB/DB/oracle) (SID_NAME = orcl) ) )LISTENER = (DESCRIPT

7、ION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ADGTEST1)(PORT = 1521) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) ) )ADR_BASE_LISTENER = /mnt_array/OracleDB/DBoracleADGTEST1 admin$ 5、 主库重启网络监听oracleADGTEST1 admin$ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.4.0 -

8、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 startLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 1

9、5: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/network/admin/listener.oraLog messages written to /mnt_array/OracleDB/

10、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=TCP)(HOST=ADGTEST1)(PORT=1521)STATUS of the LISTENER-Alias LISTE

11、NERVersion 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/network/admin/listener.oraListener Log File /mnt_array/OracleDB

12、/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 has 1 instance(s). Instance orcl, status UNKNOWN, has 1 handler(s) for this s

13、ervice.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, status UNKNOWN, has 1 handler(s) for this service.The command completed successfullyoracleADGTEST1 admin$ 可以看到刚才建好的三个静态网络监听。6、 备份数据库oracleAD

14、GTEST1 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 rights reserved.connected to target database: ORCL (DBID=1461199451)RMAN backup database;Starting backup at 16-JAN-17using target databa

15、se control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=14 device type=DISKchannel ORA_DISK_1: starting full datafile backup setFinished backup at 16-JAN-17RMAN exitRecovery Manager complete.oracleADGTEST1 admin$ 7、 建立pfile文件oracleADGTEST1 admin$ sqlplus / as

16、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 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL cre

17、ate pfile from spfile;File created.SQL exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsoracleADGTEST1 admin$ 8、 拷贝参数文件到备库端oracleADGTEST1 admin$ cd $ORACLE_HOME/dbsoracleADGT

18、EST1 dbs$ lshc_orcl.dat init.ora initorcl.ora lkORCL orapworcl snapcf_orcl.f spfileorcl.oraoracleADGTEST1 dbs$ pwd/mnt_array/OracleDB/DB/oracle/dbsoracleADGTEST1 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

19、back to the address - POSSIBLE BREAK-IN ATTEMPT!oracle192.168.0.115s password: initorcl.ora 100% 1087 1.1KB/s 00:00 orapworcl 100% 1536 1.5KB/s 00:00 oracleADGTEST1 dbs$ 9、 备库配置静态网络监听采用图形方式进行配置，运行netmgr。 完成后，保存退出。注：“Oracle 主目录”框中的路径最后结尾的那个“/”需要删除。10、 备库重启网络监听oracleTestLinux $ lsnrctl stopLSNRCTL for

20、 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=(ADDRESS=(PROTOCOL=TCP)(HOST=TestLinux)(PORT=1521)The command completed successfullyoracleTestLinux $ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0 - P

21、roduction on 16-JAN-2017 15:49:14Copyright (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/network/admin/listener.oraLog messages writt

22、en to /mnt_array/OracleDB/DB/diag/tnslsnr/TestLinux/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestLinux)(PORT=1521)Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestLinux)(PORT=1521)STATU

23、S 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 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /mnt_array/OracleDB/DB/oracle/network/admin/listener.oraListener

24、 Log File /mnt_array/OracleDB/DB/diag/tnslsnr/TestLinux/listener/alert/log.xmlListening Endpoints Summary. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestLinux)(PORT=1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)Services Summary.Service orcl has 1 instance(s). Instance orcl, status UNKNO

25、WN, has 1 handler(s) for this service.Service sorcl has 1 instance(s). Instance sorcl, status UNKNOWN, 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、 备库

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

27、a initorcl.ora orapworcloracleTestLinux dbs$ 因为没有建立数据库，所以这里现在没有该文件。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_a

28、rea/orcl/control02.ctl*.db_recovery_file_dest=/mnt_array/OracleDB/DB/fast_recovery_areaoracleTestLinux 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 -p14、

29、 备库启动数据库到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 bytesFixed

30、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 startup nomount;ORACLE inst

31、ance 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 samples shrept.lst tnsnames.orao

32、racleADGTEST1 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) (CONNECT_DATA = (SERVER = DEDICA

33、TED) (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_DATA = (SERVER = DEDICATED)

34、(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 bogon, but this does not map bac

35、k to the address - POSSIBLE BREAK-IN ATTEMPT!oracle192.168.0.115s 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/tnsnames.ora# Generated by Oracle c

36、onfiguration 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 = (SERVER = DEDICATED) (SERVICE_NAM

37、E = 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 sysdbaSQL*Plus: Release 11.2.0.4.0

38、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 alter database add standby logfile gro

39、up 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 Oracle Database 11g Enterprise Edition Release 11.2.0.4.

40、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.4.0 - Production on Mon Jan 16 16:49:12 2017Copyright

41、(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 database;Starting Duplicate Db at 16-JAN-17using target data

42、base 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 cop