基于RMAN搭建DataGuard，使用Broker管理DataGuard

一、环境准备

1、数据库软件准备

（1）、在主节点，安装单机数据库软件并创建数据库。
（2）、在备库， 安装单机数据库软件， 但是不创建数据库。


2、操作系统配置

在/etc/hosts下面配置主机名。
  1. $ cat /etc/hosts
  2. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
  3. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  4. 192.168.56.31 xiaohe_dg1
  5. 192.168.56.32 xiaohe_dg2

3、数据库部分参数

角色主机名数据库实例db_domain
主库xiaohe_dg1xiaohexiaohe.com
备库xiaohe_dg2xiaohedgxiaohe.com


二、配置数据库

1、主库打开数据库

  1. SQL> startup

2、配置监听

（1）、设置db_domain
  1. SQL> show parameter db_domain;
  3. NAME				     TYPE	 VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_domain			     string
  6. SQL> 
  7. SQL> alter system set db_domain='xiaohe.com' scope=spfile;
  8. SQL> shutdown immediate     
  9. SQL> startup
  10. SQL> show parameter db_domain;
  12. NAME				     TYPE	 VALUE
  13. ------------------------------------ ----------- ------------------------------
  14. db_domain			     string	 xiaohe.com

（2）、在主/备库设置监听

路径：$ORACLE_HOME/network/admin

主库配置：
  1. $ cat listener.ora 
  2. LISTENER=
  3.   (DESCRIPTION=
  4.     (ADDRESS_LIST=
  5.       (ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg1)(PORT=1521))))
  6. SID_LIST_LISTENER=
  7.   (SID_LIST=
  8.     (SID_DESC=
  9.       (GLOBAL_DBNAME=xiaohe_DGMGRL.xiaohe.com)
  10.       (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
  11.       (SID_NAME=xiaohe))
  12. )



备库配置：
  1. $ cat listener.ora 
  2. LISTENER=
  3.   (DESCRIPTION=
  4.     (ADDRESS_LIST=
  5.       (ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg2)(PORT=1521))))
  6. SID_LIST_LISTENER=
  7.   (SID_LIST=
  8.     (SID_DESC=
  9.       (GLOBAL_DBNAME=xiaohedg_DGMGRL.xiaohe.com)
  10.       (ORACLE_HOME=/u01/app/oracle/product/11.2.0)
  11.       (SID_NAME=xiaohedg))
  12. )


说明：GLOBAL_DBNAME为db_unique_name_DGMGRL.db_domain

（3）、启动主备库的监听
下面是备库的监听：
  1. [oracle@xiaohe_dg2 admin]$ lsnrctl start
  3. LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 18-MAR-2017 18:32:48
  5. Copyright (c) 1991, 2011, Oracle.  All rights reserved.
  7. Starting /u01/app/oracle/product/11.2.0/bin/tnslsnr: please wait...
  9. TNSLSNR for Linux: Version 11.2.0.3.0 - Production
  10. System parameter file is /u01/app/oracle/product/11.2.0/network/admin/listener.ora
  11. Log messages written to /u01/app/oracle/diag/tnslsnr/xiaohe_dg2/listener/alert/log.xml
  12. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg2)(PORT=1521)))
  14. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg2)(PORT=1521)))
  15. STATUS of the LISTENER
  16. ------------------------
  17. Alias                     LISTENER
  18. Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
  19. Start Date                18-MAR-2017 18:32:49
  20. Uptime                    0 days 0 hr. 0 min. 1 sec
  21. Trace Level               off
  22. Security                  ON: Local OS Authentication
  23. SNMP                      OFF
  24. Listener Parameter File   /u01/app/oracle/product/11.2.0/network/admin/listener.ora
  25. Listener Log File         /u01/app/oracle/diag/tnslsnr/xiaohe_dg2/listener/alert/log.xml
  26. Listening Endpoints Summary...
  27.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg2)(PORT=1521)))
  28. Services Summary...
  29. Service "xiaohedg_DGMGRL.xiaohe.com" has 1 instance(s).
  30.   Instance "xiaohedg", status UNKNOWN, has 1 handler(s) for this service...
  31. The command completed successfully

监听实例必须是静态的， 因为数据库没有启动， 监听也是能够启动的。


3、配置tnsnames

（1）、监听的内容
  1. $ cat tnsnames.ora 
  2. xiaohe_dg1=
  3.  (DESCRIPTION= 
  4.   (ADDRESS_LIST=
  5.    (ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg1)(PORT=1521)))
  6.   (CONNECT_DATA=
  7.    (SERVICE_NAME=xiaohe_DGMGRL.xiaohe.com))
  8. )
  10. xiaohe_dg2=
  11.  (DESCRIPTION= 
  12.   (ADDRESS_LIST=
  13.    (ADDRESS=(PROTOCOL=tcp)(HOST=xiaohe_dg2)(PORT=1521)))
  14.   (CONNECT_DATA=
  15.    (SERVICE_NAME=xiaohedg_DGMGRL.xiaohe.com))
  16. )

SERVICE_NAME可以通过lsnrctl status来查看。

（2）、在主库验证监听是否配置成功
  1. [oracle@xiaohe_dg1 admin]$ sqlplus sys/oracle123@xiaohe_dg1 as sysdba
  3. SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 18 19:02:30 2017
  5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  12. SQL>

（3）、将主库的tnsnames配置文件传到备库
  1. $ scp tnsnames.ora xiaohe_dg2:/u01/app/oracle/product/11.2.0/network/admin/
  2. oracle@xiaohe_dg2's password: 
  3. tnsnames.ora                                                        100%  333     0.3KB/s   00:00


4、开启主库的归档模式

（1）、新建归档目录
  1. [oracle@xiaohe_dg1 oradata]$ mkdir archive
  2. [oracle@xiaohe_dg1 oradata]$ cd archive/
  3. [oracle@xiaohe_dg1 archive]$ pwd
  4. /oradata/archive

（2）、开启归档
  1. SQL> shutdown immediate
  2.      
  3. SQL> startup mount

  5. SQL> alter database archivelog;  
  6.   
  7. SQL> alter system set log_archive_dest_1='location=/oradata/archive' scope=spfile;

  9. SQL> shutdown immediate;

  11. SQL> startup

  13. SQL> archive log list;
  14. Database log mode	       Archive Mode
  15. Automatic archival	       Enabled
  16. Archive destination	       /oradata/archive
  17. Oldest online log sequence     10
  18. Next log sequence to archive   12
  19. Current log sequence	       12


5、配置主备库数据、日志目录转换

  1. SQL> alter system set db_file_name_convert='/oradata/xiaohedg','/oradata/xiaohe' scope=spfile;
  2. SQL> alter system set log_file_name_convert='/oradata/xiaohedg','/oradata/xiaohe' scope=spfile;
注意格式：alter system set log_file_name_convert='对端库的目录','当前库目录' scope=spfile;


6、设置闪回

设置闪回， 后面测试建立测试库。
（1）、新建闪回目录
  1. [oracle@xiaohe_dg1 oradata]$ mkdir flash
  2. [oracle@xiaohe_dg1 oradata]$ cd flash/
  3. [oracle@xiaohe_dg1 flash]$ pwd
  4. /oradata/flash

（2）、设置闪回参数
  1. SQL> show parameter recover;
  3. NAME				     TYPE	 VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_recovery_file_dest		     string
  6. db_recovery_file_dest_size	     big integer 0
  7. db_unrecoverable_scn_tracking	     boolean	 TRUE
  8. recovery_parallelism		     integer	 0

  10. SQL> alter system set db_recovery_file_dest_size=500M;
  12. SQL> alter system set db_recovery_file_dest='/oradata/flash';

  14. SQL> show parameter recover;
  16. NAME				     TYPE	 VALUE
  17. ------------------------------------ ----------- ------------------------------
  18. db_recovery_file_dest		     string	 /oradata/flash
  19. db_recovery_file_dest_size	     big integer 500M
  20. db_unrecoverable_scn_tracking	     boolean	 TRUE
  21. recovery_parallelism

7、设置standby的文件传输参数

如果设置为手动， 那么主库新建的表空间文件无法传到备库。
  1. SQL> show parameter standby 
  2. NAME				     TYPE	 VALUE
  3. ------------------------------------ ----------- ------------------------------
  4. standby_archive_dest		     string	 ?/dbs/arch
  5. standby_file_management 	     string	 MANUAL

  7. SQL> alter system set standby_file_management=AUTO;
  9. SQL> show parameter standby;
  10. NAME				     TYPE	 VALUE
  11. ------------------------------------ ----------- ------------------------------
  12. standby_archive_dest		     string	 ?/dbs/arch
  13. standby_file_management 	     string	 AUTO

8、开启强制日志

  1. SQL> alter database force logging;

9、开启闪回

  1. SQL> shutdown immediate

  3. SQL> startup mount;

  5. SQL> alter database flashback on;
  7. SQL> alter database open;

10、配置standby log参数

创建4组standby 日志， 比原日志数目多一个。
  1. SQL> alter database add standby logfile group 4 '/oradata/xiaohe/std_redo04.log' size 50M;
  3. SQL> alter database add standby logfile group 5 '/oradata/xiaohe/std_redo05.log' size 50M;
  5. SQL> alter database add standby logfile group 6 '/oradata/xiaohe/std_redo06.log' size 50M;
  7. SQL> alter database add standby logfile group 7 '/oradata/xiaohe/std_redo07.log' size 50M;

11、生成参数文件pfile

（1）、生成pfile文件
  1. SQL> create pfile='/oradata/initxiaohedg.ora' from spfile;
（2）、传送pfile到备库的dbs目录下面
  1. $ scp initxiaohedg.ora xiaohe_dg2:/u01/app/oracle/product/11.2.0/dbs/
  2. oracle@xiaohe_dg2's password: 
  3. initxiaohedg.ora

12、传送密码文件到备库


  1. $ cd $ORACLE_HOME/dbs
  2. [oracle@xiaohe_dg1 dbs]$ scp orapwxiaohe xiaohe_dg2:/u01/app/oracle/product/11.2.0/dbs/orapwxiaohedg
  3. oracle@xiaohe_dg2's password: 
  4. orapwxiaohe
注意：密码文件传过去， 一定要改名字。


13、在备库修改pfile文件

  1. [oracle@xiaohe_dg2 dbs]$ cat initxiaohedg.ora 
  2. xiaohe.__db_cache_size=327155712
  3. xiaohe.__java_pool_size=4194304
  4. xiaohe.__large_pool_size=4194304
  5. xiaohe.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  6. xiaohe.__pga_aggregate_target=339738624
  7. xiaohe.__sga_target=503316480
  8. xiaohe.__shared_io_pool_size=0
  9. xiaohe.__shared_pool_size=155189248
  10. xiaohe.__streams_pool_size=4194304
  11. *.audit_file_dest='/u01/app/oracle/admin/xiaohe/adump'
  12. *.audit_trail='db'
  13. *.compatible='11.2.0.0.0'
  14. *.control_files='/oradata/xiaohedg/control01.ctl','/oradata/xiaohedg/control02.ctl'
  15. *.db_block_size=8192
  16. *.db_domain='xiaohe.com'
  17. *.db_file_name_convert='/oradata/xiaohe','/oradata/xiaohedg'
  18. *.db_name='xiaohe'
  19. *.db_unique_name='xiaohedg'
  20. *.db_recovery_file_dest_size=524288000
  21. *.db_recovery_file_dest='/oradata/flash'
  22. *.diagnostic_dest='/u01/app/oracle'
  23. *.dispatchers='(PROTOCOL=TCP) (SERVICE=xiaoheXDB)'
  24. *.log_archive_dest_1='location=/oradata/archive'
  25. *.log_file_name_convert='/oradata/xiaohe','/oradata/xiaohedg'
  26. *.memory_target=839909376
  27. *.open_cursors=300
  28. *.processes=150
  29. *.remote_login_passwordfile='EXCLUSIVE'
  30. *.standby_file_management='AUTO'
  31. *.undo_tablespace='UNDOTBS1'
修改的地方：
（1）、*.audit_file_dest：目录不存在， 重新创建
（2）、*.control_files：修改控制文件的目录，并创建目录/oradata/xiaohedg
（3）、*.db_file_name_convert 和 *.log_file_name_convert的对端库和当前库的目录位置互换。
（4）、*.log_archive_dest_1目录不存在， 需要创建。
（4）、新增一个条目：*.db_unique_name='xiaohedg'


14、在备库启动实例

生成启动文件spfile， 下次启动时候能自动选择spfile启动。
将数据库启动到nomount。
  1. SQL> startup
  2. ORACLE instance started.
  4. Total System Global Area  839282688 bytes
  5. Fixed Size		    2233000 bytes
  6. Variable Size		  494931288 bytes
  7. Database Buffers	  339738624 bytes
  8. Redo Buffers		    2379776 bytes
  9. ORA-00205: error in identifying control file, check alert log for more info
  12. SQL> show parameter spfile;
  14. NAME				     TYPE	 VALUE
  15. ------------------------------------ ----------- ------------------------------
  16. spfile				     string
  17. SQL> create spfile from pfile;
  19. File created.
  21. SQL> shutdown immediate;
  22. ORA-01507: database not mounted
  25. ORACLE instance shut down.
  26. SQL> startup nomount
  27. ORACLE instance started.
  29. Total System Global Area  839282688 bytes
  30. Fixed Size		    2233000 bytes
  31. Variable Size		  494931288 bytes
  32. Database Buffers	  339738624 bytes
  33. Redo Buffers		    2379776 bytes

在主库多切换几次日志：
  1. SQL> alter system switch logfile;
  3. System altered.
  5. SQL> /
  7. System altered.
  9. SQL> /
  11. System altered.
  13. SQL> /
  15. System altered.
  17. SQL> /

15、检查并重启主库


（1）、重启主库， 查看能否正常启动。

（2）、检查主备库的db_name是否一致。
db_name必须一样， db_unique_name不能一样。

（3）、检查网络：
  1. $ sqlplus sys/oracle123@xiaohe_dg1 as sysdba;
  2. $ sqlplus sys/oracle123@xiaohe_dg2 as sysdba;

（4）、检查目录转换
  1. SQL> show  parameter  convert;


16、使用rman

  1. [oracle@xiaohe_dg1 dbs]$ rman target  sys/oracle123@xiaohe_dg1  auxiliary sys/oracle123@xiaohe_dg2
  3. Recovery Manager: Release 11.2.0.3.0 - Production on Sat Mar 18 20:21:06 2017
  5. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
  7. connected to target database: XIAOHE (DBID=4061739917)
  8. connected to auxiliary database: XIAOHE (not mounted)
  10. RMAN> duplicate target database for standby from active database;
  12. Starting Duplicate Db at 18-MAR-17
  13. using target database control file instead of recovery catalog
  14. allocated channel: ORA_AUX_DISK_1
  15. channel ORA_AUX_DISK_1: SID=20 device type=DISK
  17. contents of Memory Script:
  18. {
  19.    backup as copy reuse
  20.    targetfile  '/u01/app/oracle/product/11.2.0/dbs/orapwxiaohe' auxiliary format 
  21.  '/u01/app/oracle/product/11.2.0/dbs/orapwxiaohedg'   ;
  22. }
  23. executing Memory Script
  25. Starting backup at 18-MAR-17
  26. allocated channel: ORA_DISK_1
  27. channel ORA_DISK_1: SID=38 device type=DISK
  28. Finished backup at 18-MAR-17
  30. contents of Memory Script:
  31. {
  32.    backup as copy current controlfile for standby auxiliary format  '/oradata/xiaohedg/control01.ctl';
  33.    restore clone controlfile to  '/oradata/xiaohedg/control02.ctl' from 
  34.  '/oradata/xiaohedg/control01.ctl';
  35. }
  36. executing Memory Script
  38. Starting backup at 18-MAR-17
  39. using channel ORA_DISK_1
  40. channel ORA_DISK_1: starting datafile copy
  41. copying standby control file
  42. output file name=/u01/app/oracle/product/11.2.0/dbs/snapcf_xiaohe.f tag=TAG20170318T202332 RECID=2 STAMP=938982213
  43. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
  44. Finished backup at 18-MAR-17
  46. Starting restore at 18-MAR-17
  47. using channel ORA_AUX_DISK_1
  49. channel ORA_AUX_DISK_1: copied control file copy
  50. Finished restore at 18-MAR-17
  52. contents of Memory Script:
  53. {
  54.    sql clone 'alter database mount standby database';
  55. }
  56. executing Memory Script
  58. sql statement: alter database mount standby database
  60. contents of Memory Script:
  61. {
  62.    set newname for tempfile  1 to 
  63.  "/oradata/xiaohedg/temp01.dbf";
  64.    switch clone tempfile all;
  65.    set newname for datafile  1 to 
  66.  "/oradata/xiaohedg/system01.dbf";
  67.    set newname for datafile  2 to 
  68.  "/oradata/xiaohedg/sysaux01.dbf";
  69.    set newname for datafile  3 to 
  70.  "/oradata/xiaohedg/undotbs01.dbf";
  71.    set newname for datafile  4 to 
  72.  "/oradata/xiaohedg/users01.dbf";
  73.    set newname for datafile  5 to 
  74.  "/oradata/xiaohedg/example01.dbf";
  75.    backup as copy reuse
  76.    datafile  1 auxiliary format 
  77.  "/oradata/xiaohedg/system01.dbf"   datafile 
  78.  2 auxiliary format 
  79.  "/oradata/xiaohedg/sysaux01.dbf"   datafile 
  80.  3 auxiliary format 
  81.  "/oradata/xiaohedg/undotbs01.dbf"   datafile 
  82.  4 auxiliary format 
  83.  "/oradata/xiaohedg/users01.dbf"   datafile 
  84.  5 auxiliary format 
  85.  "/oradata/xiaohedg/example01.dbf"   ;
  86.    sql 'alter system archive log current';
  87. }
  88. executing Memory Script
  90. executing command: SET NEWNAME
  92. renamed tempfile 1 to /oradata/xiaohedg/temp01.dbf in control file
  94. executing command: SET NEWNAME
  96. executing command: SET NEWNAME
  98. executing command: SET NEWNAME
  100. executing command: SET NEWNAME
  102. executing command: SET NEWNAME
  104. Starting backup at 18-MAR-17
  105. using channel ORA_DISK_1
  106. channel ORA_DISK_1: starting datafile copy
  107. input datafile file number=00001 name=/oradata/xiaohe/system01.dbf
  108. output file name=/oradata/xiaohedg/system01.dbf tag=TAG20170318T202342
  109. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
  110. channel ORA_DISK_1: starting datafile copy
  111. input datafile file number=00002 name=/oradata/xiaohe/sysaux01.dbf
  112. output file name=/oradata/xiaohedg/sysaux01.dbf tag=TAG20170318T202342
  113. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
  114. channel ORA_DISK_1: starting datafile copy
  115. input datafile file number=00005 name=/oradata/xiaohe/example01.dbf
  116. output file name=/oradata/xiaohedg/example01.dbf tag=TAG20170318T202342
  117. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
  118. channel ORA_DISK_1: starting datafile copy
  119. input datafile file number=00003 name=/oradata/xiaohe/undotbs01.dbf
  120. output file name=/oradata/xiaohedg/undotbs01.dbf tag=TAG20170318T202342
  121. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
  122. channel ORA_DISK_1: starting datafile copy
  123. input datafile file number=00004 name=/oradata/xiaohe/users01.dbf
  124. output file name=/oradata/xiaohedg/users01.dbf tag=TAG20170318T202342
  125. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
  126. Finished backup at 18-MAR-17
  128. sql statement: alter system archive log current
  130. contents of Memory Script:
  131. {
  132.    switch clone datafile all;
  133. }
  134. executing Memory Script
  136. datafile 1 switched to datafile copy
  137. input datafile copy RECID=2 STAMP=938982344 file name=/oradata/xiaohedg/system01.dbf
  138. datafile 2 switched to datafile copy
  139. input datafile copy RECID=3 STAMP=938982344 file name=/oradata/xiaohedg/sysaux01.dbf
  140. datafile 3 switched to datafile copy
  141. input datafile copy RECID=4 STAMP=938982344 file name=/oradata/xiaohedg/undotbs01.dbf
  142. datafile 4 switched to datafile copy
  143. input datafile copy RECID=5 STAMP=938982344 file name=/oradata/xiaohedg/users01.dbf
  144. datafile 5 switched to datafile copy
  145. input datafile copy RECID=6 STAMP=938982344 file name=/oradata/xiaohedg/example01.dbf
  146. Finished Duplicate Db at 18-MAR-17


17、在备库应用日志

  1. SQL> select status from v$instance ;    
  3. STATUS
  4. ------------
  5. MOUNTED
  7. SQL> alter database recover managed standby database disconnect from session;
  9. Database altered.


18、在主、备库启用broker

  1. SQL> show parameter broker;
  3. NAME				     TYPE	 VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. dg_broker_config_file1		     string	 /u01/app/oracle/product/11.2.0
  6. 						 /dbs/dr1xiaohe.dat
  7. dg_broker_config_file2		     string	 /u01/app/oracle/product/11.2.0
  8. 						 /dbs/dr2xiaohe.dat
  9. dg_broker_start 		     boolean	 FALSE
  10. SQL> alter system set dg_broker_start=true;
  12. System altered.

19、进入broker， 并创建配置信息

  1. $ dgmgrl sys/oracle123@xiaohe_dg1;

创建配置信息并启用：
  1. DGMGRL> CREATE CONFIGURATION c1 AS
  2.         PRIMARY DATABASE IS xiaohe
  3.         CONNECT IDENTIFIER IS xiaohe_dg1;
  5. DGMGRL> ADD DATABASE xiaohedg
  6.         AS CONNECT IDENTIFIER IS xiaohe_dg2;

  8. DGMGRL> ENABLE CONFIGURATION;

查看配置信息：
  1. DGMGRL> show configuration;
  3. Configuration - c1
  5.   Protection Mode: MaxPerformance
  6.   Databases:
  7.     xiaohe   - Primary database
  8.     xiaohedg - Physical standby database
  10. Fast-Start Failover: DISABLED
  12. Configuration Status:
  13. SUCCESS
查看详细信息：
  1. DGMGRL> show configuration verbose;
  3. Configuration - c1
  5.   Protection Mode: MaxPerformance
  6.   Databases:
  7.     xiaohe   - Primary database
  8.     xiaohedg - Physical standby database
  10.   Properties:
  11.     FastStartFailoverThreshold      = '30'
  12.     OperationTimeout                = '30'
  13.     FastStartFailoverLagLimit       = '30'
  14.     CommunicationTimeout            = '180'
  15.     FastStartFailoverAutoReinstate  = 'TRUE'
  16.     FastStartFailoverPmyShutdown    = 'TRUE'
  17.     BystandersFollowRoleChange      = 'ALL'
  19. Fast-Start Failover: DISABLED
  21. Configuration Status:
  22. SUCCESS
  24. DGMGRL>



帮助信息如下：
  1. DGMGRL> help create;              
  3. Creates a broker configuration
  5. Syntax:
  7.   CREATE CONFIGURATION <configuration name> AS
  8.     PRIMARY DATABASE IS <database name>
  9.     CONNECT IDENTIFIER IS <connect identifier>;
  11. DGMGRL> help add;
  13. Adds a standby database to the broker configuration
  15. Syntax:
  17.   ADD DATABASE <database name>
  18.     [AS CONNECT IDENTIFIER IS <connect identifier>]
  19.     [MAINTAINED AS {PHYSICAL|LOGICAL}];
  21. DGMGRL> help enable;
  23. Enables a configuration, a database, or fast-start failover
  25. Syntax:
  27.   ENABLE CONFIGURATION;
  29.   ENABLE DATABASE <database name>;
  31.   ENABLE FAST_START FAILOVER [CONDITION <condition>];
  33. DGMGRL>


20、打开备库

（1）、关闭备库
（2）、打开备库
  1. shutdown immediate
  2. startup open read only;

查看mode信息：
select open_mode from v$database;

21、打开备库的闪回创建测试库

（1）、将备库重启到mount状态
（2）、开启闪回
  1. SQL> select flashback_on from v$database;
  2. FLASHBACK_ON
  3. ------------------
  4. NO
  6. SQL> shutdown immediate;

  8. SQL> startup mount;

  10. SQL> alter database flashback on;
  12. SQL> select flashback_on from v$database;
  13. FLASHBACK_ON
  14. ------------------
  15. YES

  17. SQL> alter database open;

  19. SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

  21. SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

（3）、在broker下面切换
  1. DGMGRL> help convert;       
  3. Converts a database from one type to another
  5. Syntax:
  7.   CONVERT DATABASE <database name> TO
  8.      {SNAPSHOT STANDBY|PHYSICAL STANDBY};
  10. DGMGRL> CONVERT DATABASE xiaohedg TO SNAPSHOT STANDBY;
  11. Converting database "xiaohedg" to a Snapshot Standby database, please wait...
  12. Database "xiaohedg" converted successfully
  13. DGMGRL> show configuration;
  15. Configuration - c1
  17.   Protection Mode: MaxPerformance
  18.   Databases:
  19.     xiaohe   - Primary database
  20.     xiaohedg - Snapshot standby database
  22. Fast-Start Failover: DISABLED
  24. Configuration Status:
  25. SUCCESS

（4）、在备库当做测试库读写操作
查看备库已经可以读写了：
  1. SQL> select open_mode from v$database;
  3. OPEN_MODE
  4. --------------------
  5. READ WRITE

建表插入数据等操作：
  1. SQL> create table test(id number, name varchar2(20));

  3. SQL> insert into test values(1, 'node');
  5. SQL> commit;
  7. SQL> select * from test;
  9. 	ID NAME
  10. ---------- --------------------
  11. 	 1 node

（5）、将备库从快照切换为物理standby
  1. DGMGRL> CONVERT DATABASE xiaohedg TO PHYSICAL STANDBY;
  2. Converting database "xiaohedg" to a Physical Standby database, please wait...
  3. Operation requires shutdown of instance "xiaohedg" on database "xiaohedg"
  4. Shutting down instance "xiaohedg"...
  5. Database closed.
  6. Database dismounted.
  7. ....

（6）、备库在快照状态下的数据全部丢失


22、主备库切换

  1. DGMGRL> show configuration;
  3. Configuration - c1
  5.   Protection Mode: MaxPerformance
  6.   Databases:
  7.     xiaohe   - Primary database
  8.     xiaohedg - Physical standby database
  10. Fast-Start Failover: DISABLED
  12. Configuration Status:
  13. SUCCESS
  15. DGMGRL> switchover to xiaohedg;
  16. Performing switchover NOW, please wait...
  17. New primary database "xiaohedg" is opening...
  18. Operation requires shutdown of instance "xiaohe" on database "xiaohe"
  19. Shutting down instance "xiaohe"...
  20. ORACLE instance shut down.
  21. Operation requires startup of instance "xiaohe" on database "xiaohe"
  22. Starting instance "xiaohe"...
  23. ORACLE instance started.
  24. Database mounted.
  25. Database opened.
  26. Switchover succeeded, new primary is "xiaohedg"
  27. DGMGRL> 
  28. DGMGRL> show configuration;
  30. Configuration - c1
  32.   Protection Mode: MaxPerformance
  33.   Databases:
  34.     xiaohedg - Primary database
  35.     xiaohe   - Physical standby database
  37. Fast-Start Failover: DISABLED
  39. Configuration Status:
  40. SUCCESS



23、几个有用的视图

（1）、主库
  1. SQL>  select sequence#,applied from v$archived_log order by 1 desc;
  3.  SEQUENCE# APPLIED
  4. ---------- ---------
  5. 	79 NO
  6. 	79 NO
  7. 	78 YES
  8. 	78 NO
  9. 	77 NO
  10. 	77 YES
  11. 	76 YES
做一次日志切换后：
  1. SQL> alter system switch logfile;
  3. System altered.
  5. SQL> select sequence#,applied from v$archived_log order by 1 desc;
  7.  SEQUENCE# APPLIED
  8. ---------- ---------
  9. 	80 NO
  10. 	80 NO
  11. 	79 YES
  12. 	79 NO
  13. 	78 YES
  14. 	78 NO
  15. 	77 YES

  1. SQL> select dest_id,error from v$archive_dest;
  3.    DEST_ID ERROR
  4. ---------- -----------------------------------------------------------------
  5. 	 1
  6. 	 2
  7. 	 3
  8. 	 4

  1. SQL> select database_role,open_mode from v$database;
  3. DATABASE_ROLE	 OPEN_MODE
  4. ---------------- --------------------
  5. PRIMARY 	 READ WRITE


（2）、备库
  1. SQL> select sequence#,status from v$standby_log;
  3.  SEQUENCE# STATUS
  4. ---------- ----------
  5. 	80 ACTIVE
  6. 	 0 UNASSIGNED
  7. 	 0 UNASSIGNED
  8. 	 0 UNASSIGNED

  2. SQL> set lines 200
  3. SQL> col DATABASE_ROLE for a30;
  4. SQL> select database_role,open_mode from v$database;
  6. DATABASE_ROLE		       OPEN_MODE
  7. ------------------------------ --------------------
  8. PHYSICAL STANDBY	       READ ONLY WITH APPLY

  1. SQL> select process,status from v$managed_standby;
  3. PROCESS   STATUS
  4. --------- ------------
  5. ARCH	  CLOSING
  6. ARCH	  CONNECTED
  7. ARCH	  CONNECTED
  8. ARCH	  CONNECTED
  9. MRP0	  APPLYING_LOG
  10. RFS	  IDLE
  11. RFS	  IDLE
  12. RFS	  IDLE
  14. 8 rows selected.













posted @ 2017-03-18 23:12  小海蟹  阅读(1304)  评论(0)    收藏  举报