当前位置: 首页 > DG > 正文

11GR2 搭建活动的物理DG/DATAGRUAD (READ ONLY模式)

由于换了一家公司,这家公司全部是用的11GR2+DG,11GR2没有玩过,DG也没有玩过,所以这里在自己的VM里面搭建一个11GR2+DG的测试环境,STANDBY启动到READ ONLY

测试环境为:OS REDHAT  5.6 X86_64,DB  11.2.0.2

安装环境与创建数据库这里就不用说了,很简单的。

整个环境为成4个步骤:

1,主备修改

  1.1 修改参数

  1.2 修改监听

  1.3 cp相关文件到standby上面

  1.4 创建standby日志

2,备备修改

  2.1 修改参数

  2.2 修改监听与测试

  2.3 启动到mount

  2.4 创建相关目录

3,主备duplicate数据库

4,测试DG是否成功

正在开始正式的测试过程

1.1 查看数据库是否在归档与是否强制LOGGING模式。

  1. SQL> select log_mode,force_logging from v$database;
  2.  
  3. LOG_MODE     FOR
  4. ------------ ---
  5. ARCHIVELOG   NO
  6.  
  7. SQL> ALTER DATABASE FORCE LOGGING;
  8.  
  9. Database altered.

1.2 查看数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。

  1. SQL> select group#,bytes/1024/1024 from v$log;
  2.  
  3.     GROUP# BYTES/1024/1024
  4. ---------- ---------------
  5.          1              50
  6.          2              50
  7.          3              50
  8.  
  9. SQL> select member from v$logfile;
  10.  
  11. MEMBER
  12. --------------------------------------------------------------------------------
  13. /u01/app/oracle/oradata/htz/redo03.log
  14. /u01/app/oracle/oradata/htz/redo02.log
  15. /u01/app/oracle/oradata/htz/redo01.log

1.3 创建standby日志组,位置与原日志组相同的路径。创建完成后查询是否成功

  1. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby01.log' size 50m;
  2.  
  3. Database altered.
  4.  
  5. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby02.log' size 50m;
  6.  
  7. Database altered.
  8.  
  9. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby03.log' size 50m;
  10.  
  11. Database altered.
  12.  
  13. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby04.log' size 50m;
  14.  
  15. Database altered.
  16. SQL> select group#,status,type,member from v$logfile;
  17.  
  18.     GROUP# STATUS  TYPE    MEMBER
  19. ---------- ------- ------- --------------------------------------------------
  20.          3         ONLINE  /u01/app/oracle/oradata/htz/redo03.log
  21.          2         ONLINE  /u01/app/oracle/oradata/htz/redo02.log
  22.          1         ONLINE  /u01/app/oracle/oradata/htz/redo01.log
  23.          4         STANDBY /u01/app/oracle/oradata/htz/standby01.log
  24.          5         STANDBY /u01/app/oracle/oradata/htz/standby02.log
  25.          6         STANDBY /u01/app/oracle/oradata/htz/standby03.log
  26.          7         STANDBY /u01/app/oracle/oradata/htz/standby04.log

1.4 修改相关的参数,与DG的参数就只与几个参数相关,大概就是日志,文件的位置的转换,GAP的处理,其实GAP已经会自动的处理,不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。

  1. SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(htz,htzb)';
  2.  
  3. System altered.
  4.  
  5. SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htz';
  6.  
  7. System altered.
  8.  
  9. SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=htzb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htzb';
  10.  
  11. System altered.
  12.  
  13. SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
  14.  
  15. System altered.
  16.  
  17. SQL> alter system set FAL_SERVER=htzb;
  18.  
  19. System altered.
  20.  
  21. SQL> alter system set FAL_CLIENT=htz;
  22.  
  23. System altered.
  24.  
  25. SQL> alter system set db_unique_name=htz scope=spfile;
  26.  
  27. System altered.
  28.  
  29. SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
  30.  
  31. System altered.
  32.  
  33. SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
  34.  
  35. System altered.
  36.  
  37. SQL> create pfile from spfile;
  38.  
  39. File created.

1.5 把dbs下的内容同步到standby主机上面,主要是密码文件(一定要复制过去的)与init文件。

  1. [oracle@11g admin]$ rsync -avlR /u01/app/oracle/product/11.2.0/db_1/dbs/* 192.168.100.31:/
  2. oracle@192.168.100.31's password: 
  3. building file list ... done
  4. /u01/
  5. /u01/app/
  6. /u01/app/oracle/
  7. /u01/app/oracle/product/
  8. /u01/app/oracle/product/11.2.0/
  9. /u01/app/oracle/product/11.2.0/db_1/
  10. /u01/app/oracle/product/11.2.0/db_1/dbs/
  11. /u01/app/oracle/product/11.2.0/db_1/dbs/hc_DBUA0.dat
  12. /u01/app/oracle/product/11.2.0/db_1/dbs/hc_htz.dat
  13. /u01/app/oracle/product/11.2.0/db_1/dbs/hc_luo.dat
  14. /u01/app/oracle/product/11.2.0/db_1/dbs/init.ora
  15. /u01/app/oracle/product/11.2.0/db_1/dbs/inithtz.ora
  16. /u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZ
  17. /u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZA
  18. /u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz
  19. /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f
  20. /u01/app/oracle/product/11.2.0/db_1/dbs/spfilehtz.ora
  21.  
  22. sent 9764651 bytes  received 282 bytes  161403.85 bytes/sec
  23. total size is 9762574  speedup is 1.00

1.6  监听的修改,特别注意这里我们使用了静态的监听,是为了以后我们测试broker时使用的,如果你不用这个,那边可以用动态监听,

  1. $ lsnrctl stop
  2.  
  3. LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 31-AUG-2012 21:38:51
  4.  
  5. Copyright (c) 1991, 2009, Oracle.  All rights reserved.
  6.  
  7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
  8. The command completed successfully
  9. [oracle@11g admin]$ cat listener.ora
  10. SID_LIST_LISTENER =
  11.   (SID_LIST =
  12.     (SID_DESC =
  13.      (GLOBAL_DBNAME = htz)
  14.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  15.      (SID_NAME = htz)
  16.     )
  17.    )
  18.  
  19. LISTENER =
  20.   (DESCRIPTION =
  21.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
  22.   ) 
  23. [oracle@11g admin]$ cat tnsnames.ora
  24. HTZB =
  25.   (DESCRIPTION =
  26.     (ADDRESS_LIST =
  27.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
  28.     )
  29.     (CONNECT_DATA =
  30.       (SERVER = dedicate)
  31.       (SERVICE_NAME = htzb)
  32.     )
  33.   )
  34.  
  35. HTZ =
  36.   (DESCRIPTION =
  37.     (ADDRESS_LIST =
  38.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
  39.     )
  40.     (CONNECT_DATA =
  41.       (SERVER = dedicate)
  42.       (SERVICE_NAME = htz)
  43.     )
  44.   )
  45.  
  46. 启动监听
  47. [oracle@11g admin]$ lsnrctl start
  48.  
  49. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-SEP-2012 05:35:50
  50.  
  51. Copyright (c) 1991, 2010, Oracle.  All rights reserved.
  52.  
  53. Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
  54.  
  55. TNSLSNR for Linux: Version 11.2.0.2.0 - Production
  56. System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  57. Log messages written to /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
  58. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
  59.  
  60. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
  61. STATUS of the LISTENER
  62. ------------------------
  63. Alias                     LISTENER
  64. Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
  65. Start Date                07-SEP-2012 05:35:50
  66. Uptime                    0 days 0 hr. 0 min. 0 sec
  67. Trace Level               off
  68. Security                  ON: Local OS Authentication
  69. SNMP                      OFF
  70. Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  71. Listener Log File         /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
  72. Listening Endpoints Summary...
  73.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
  74. Services Summary...
  75. Service "htz" has 1 instance(s).
  76.   Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
  77. The command completed successfully

主库上面修改的内容差不多就是这些了。

下面就是库备的修改了。

2.1 修改监听

  1. [oracle@11gdg admin]$ cat listener.ora
  2. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.
  4.  
  5. SID_LIST_LISTENER =
  6.   (SID_LIST =
  7.     (SID_DESC =
  8.      (GLOBAL_DBNAME = htzb)
  9.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  10.      (SID_NAME = htz)
  11.     )
  12.    )
  13.  
  14. LISTENER =
  15.   (DESCRIPTION =
  16.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
  17.   ) 
  18. ADR_BASE_LISTENER = /u01/app/oracle
  19.  
  20. [oracle@11gdg admin]$ cat tnsnames.ora
  21. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  22. # Generated by Oracle configuration tools.
  23.  
  24.  
  25. HTZB =
  26.   (DESCRIPTION =
  27.     (ADDRESS_LIST =
  28.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
  29.     )
  30.     (CONNECT_DATA =
  31.       (SERVER = dedicate)
  32.       (SERVICE_NAME = htzb)
  33.     )
  34.   )
  35.  
  36. HTZ =
  37.   (DESCRIPTION =
  38.     (ADDRESS_LIST =
  39.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
  40.     )
  41.     (CONNECT_DATA =
  42.       (SERVER = dedicate)
  43.       (SERVICE_NAME = htz)
  44.     )
  45.   )
  46. [oracle@11gdg admin]$ lsnrctl stop
  47.  
  48. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:17
  49.  
  50. Copyright (c) 1991, 2010, Oracle.  All rights reserved.
  51.  
  52. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
  53. The command completed successfully
  54. [oracle@11gdg admin]$ lsnrctl start
  55.  
  56. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:23
  57.  
  58. Copyright (c) 1991, 2010, Oracle.  All rights reserved.
  59.  
  60. Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
  61.  
  62. TNSLSNR for Linux: Version 11.2.0.2.0 - Production
  63. System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  64. Log messages written to /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
  65. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
  66.  
  67. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
  68. STATUS of the LISTENER
  69. ------------------------
  70. Alias                     LISTENER
  71. Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
  72. Start Date                06-SEP-2012 18:13:24
  73. Uptime                    0 days 0 hr. 0 min. 0 sec
  74. Trace Level               off
  75. Security                  ON: Local OS Authentication
  76. SNMP                      OFF
  77. Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  78. Listener Log File         /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
  79. Listening Endpoints Summary...
  80.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
  81. Services Summary...
  82. Service "htzb" has 1 instance(s).
  83.   Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
  84. The command completed successfully
  85.  
  86. 测试监听是否正常
  87. [oracle@11gdg /]$ sqlplus sys/oracle@htz as sysdba
  88.  
  89. SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:20 2012
  90.  
  91. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  92.  
  93.  
  94. Connected to:
  95. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  96. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  97.  
  98. SQL> exit
  99. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  100. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  101. [oracle@11gdg /]$ sqlplus sys/oracle@htzb as sysdba
  102.  
  103. SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:24 2012
  104.  
  105. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  106.  
  107. Connected to an idle instance.

2.1 创建相关文件与修改参数文件

  1. 创建相关目录
  2. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/arch/htz
  3. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/htz
  4. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/oradata/htz
  5. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/admin/htz/adump
  6. 修改参数文件
  7. [oracle@11gdg /]$ cd $ORACLE_HOME/dbs
  8. [oracle@11gdg dbs]$ rm spfilehtz.ora
  9. [oracle@11gdg dbs]$ cat inithtz.ora
  10. htz.__db_cache_size=67108864
  11. htz.__java_pool_size=4194304
  12. htz.__large_pool_size=4194304
  13. htz.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
  14. htz.__pga_aggregate_target=134217728
  15. htz.__sga_target=180355072
  16. htz.__shared_io_pool_size=0
  17. htz.__shared_pool_size=96468992
  18. htz.__streams_pool_size=0
  19. *.audit_file_dest='/u01/app/oracle/admin/htz/adump'
  20. *.audit_trail='db'
  21. *.compatible='11.2.0.0.0'
  22. *.control_files='/u01/app/oracle/oradata/htz/control01.ctl','/u01/app/oracle/oradata/htz/control02.ctl'
  23. *.db_block_size=8192
  24. *.db_domain=''
  25. *.db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
  26. *.db_name='htz'
  27. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  28. *.db_recovery_file_dest_size=4227858432
  29. *.db_unique_name='HTZB'
  30. *.diagnostic_dest='/u01/app/oracle'
  31. *.dispatchers='(PROTOCOL=TCP) (SERVICE=htzXDB)'
  32. *.fal_client='HTZB'
  33. *.fal_server='HTZ'
  34. *.log_archive_config='DG_CONFIG=(htz,htzb)'
  35. *.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htzb'
  36. *.log_archive_dest_2='SERVICE=htz LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htz'
  37. *.log_archive_dest_state_1='ENABLE'
  38. *.log_archive_format='%t_%s_%r.dbf'
  39. *.log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/'
  40. *.memory_target=314572800
  41. *.open_cursors=300
  42. *.processes=150
  43. *.remote_login_passwordfile='EXCLUSIVE'
  44. *.undo_tablespace='UNDOTBS1'

1.3 创建spfile与启动数据库到mount

  1. 创建spfile,并启动数据库到NOMOUNT
  2. [oracle@11gdg dbs]$ sqlplus / as sysdba
  3.  
  4. SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:24:02 2012
  5.  
  6. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  7.  
  8. Connected to an idle instance.
  9.  
  10. SQL> startup nomount;
  11. ORACLE instance started.
  12.  
  13. Total System Global Area  313159680 bytes
  14. Fixed Size                  2226072 bytes
  15. Variable Size             239077480 bytes
  16. Database Buffers           67108864 bytes
  17. Redo Buffers                4747264 bytes
  18. SQL> create spfile from pfile;
  19.  
  20. File created.
  21.  
  22. SQL> startup force mount;
  23. ORACLE instance started.
  24.  
  25. Total System Global Area  313159680 bytes
  26. Fixed Size                  2226072 bytes
  27. Variable Size             239077480 bytes
  28. Database Buffers           67108864 bytes
  29. Redo Buffers                4747264 bytes
  30. ORA-00205: error in identifying control file, check alert log for more info
  31.  
  32.  
  33. SQL> show parameter spfile;
  34.  
  35. NAME                                 TYPE        VALUE
  36. ------------------------------------ ----------- ------------------------------
  37. spfile                               string      /u01/app/oracle/product/11.2.0
  38.                                                  /db_1/dbs/spfilehtz.ora

1.4 修改/etc/oratab文件,由于OS不一样,这里修改的位置也不一样,如果SUN  /var/opt/oracle/oratab,IBM的/etc/oratab

  1. [root@11gdg ~]# echo "htz:/u01/app/oracle/product/11.2.0/db_1:N">>/etc/oratab
  2. [root@11gdg ~]# tail -1 /etc/oratab
  3. htz:/u01/app/oracle/product/11.2.0/db_1:N

备库的相关操作到此就差不多,到了duplicate的时候了。

回到主备上面。

在前面我们修改了两个参数(db/log_file_name_convertt)到spfile文件中,还没有重启数据库,这里我们重启一下主数据库

3.1

  1. [oracle@11g admin]$ sqlplus / as sysdba
  2.  
  3. SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:50:13 2012
  4.  
  5. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  6.  
  7.  
  8. Connected to:
  9. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  11.  
  12. SQL> shutdown immediate;
  13. Database closed.
  14. Database dismounted.
  15. ORACLE instance shut down.
  16. SQL> startup
  17. ORACLE instance started.
  18.  
  19. Total System Global Area  313159680 bytes
  20. Fixed Size                  2226072 bytes
  21. Variable Size             247466088 bytes
  22. Database Buffers           58720256 bytes
  23. Redo Buffers                4747264 bytes
  24. Database mounted.
  25. Database opened.
  26. SQL> exit
  27. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  28. With the Partitioning, OLAP, Data Mining and Real Application Testing options

3.2 duplicate开始

  1. [oracle@11g admin]$ rman target sys/oracle@htz auxiliary sys/oracle@htzb
  2.  
  3. Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 05:50:46 2012
  4.  
  5. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
  6.  
  7. connected to target database: HTZ (DBID=1848107928)
  8. connected to auxiliary database: HTZ (not mounted)
  9.  
  10. RMAN> run {
  11. 2> allocate channel prmy1 type disk;
  12. 3> allocate channel prmy2 type disk;
  13. 4> allocate channel prmy3 type disk;
  14. 5> allocate channel prmy4 type disk;
  15. 6> allocate auxiliary channel stby type disk;
  16. 7> duplicate target database for standby from active database nofilenamecheck
  17. 8> ;
  18. 9> }
  19.  
  20. using target database control file instead of recovery catalog
  21. allocated channel: prmy1
  22. channel prmy1: SID=36 device type=DISK
  23.  
  24. allocated channel: prmy2
  25. channel prmy2: SID=37 device type=DISK
  26.  
  27. allocated channel: prmy3
  28. channel prmy3: SID=38 device type=DISK
  29.  
  30. allocated channel: prmy4
  31. channel prmy4: SID=39 device type=DISK
  32.  
  33. allocated channel: stby
  34. channel stby: SID=21 device type=DISK
  35.  
  36. Starting Duplicate Db at 07-SEP-12
  37.  
  38. contents of Memory Script:
  39. {
  40.    backup as copy reuse
  41.    targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' auxiliary format 
  42. '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz'   ;
  43. }
  44. executing Memory Script
  45.  
  46. Starting backup at 07-SEP-12
  47. Finished backup at 07-SEP-12
  48.  
  49. contents of Memory Script:
  50. {
  51.    backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/htz/control01.ctl';
  52.    restore clone controlfile to  '/u01/app/oracle/oradata/htz/control02.ctl' from 
  53. '/u01/app/oracle/oradata/htz/control01.ctl';
  54. }
  55. executing Memory Script
  56.  
  57. Starting backup at 07-SEP-12
  58. channel prmy1: starting datafile copy
  59. copying standby control file
  60. output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f tag=TAG20120907T055127 RECID=1 STAMP=793345887
  61. channel prmy1: datafile copy complete, elapsed time: 00:00:01
  62. Finished backup at 07-SEP-12
  63.  
  64. Starting restore at 07-SEP-12
  65.  
  66. channel stby: copied control file copy
  67. Finished restore at 07-SEP-12
  68.  
  69. contents of Memory Script:
  70. {
  71.    sql clone 'alter database mount standby database';
  72. }
  73. executing Memory Script
  74.  
  75. sql statement: alter database mount standby database
  76.  
  77. contents of Memory Script:
  78. {
  79.    set newname for tempfile  1 to 
  80. "/u01/app/oracle/oradata/htz/temp01.dbf";
  81.    switch clone tempfile all;
  82.    set newname for datafile  1 to 
  83. "/u01/app/oracle/oradata/htz/system01.dbf";
  84.    set newname for datafile  2 to 
  85. "/u01/app/oracle/oradata/htz/sysaux01.dbf";
  86.    set newname for datafile  3 to 
  87. "/u01/app/oracle/oradata/htz/undotbs01.dbf";
  88.    set newname for datafile  4 to 
  89. "/u01/app/oracle/oradata/htz/users01.dbf";
  90.    backup as copy reuse
  91.    datafile  1 auxiliary format 
  92. "/u01/app/oracle/oradata/htz/system01.dbf"   datafile 
  93. 2 auxiliary format 
  94. "/u01/app/oracle/oradata/htz/sysaux01.dbf"   datafile 
  95. 3 auxiliary format 
  96. "/u01/app/oracle/oradata/htz/undotbs01.dbf"   datafile 
  97. 4 auxiliary format 
  98. "/u01/app/oracle/oradata/htz/users01.dbf"   ;
  99.    sql 'alter system archive log current';
  100. }
  101. executing Memory Script
  102.  
  103. executing command: SET NEWNAME
  104.  
  105. renamed tempfile 1 to /u01/app/oracle/oradata/htz/temp01.dbf in control file
  106.  
  107. executing command: SET NEWNAME
  108.  
  109. executing command: SET NEWNAME
  110.  
  111. executing command: SET NEWNAME
  112.  
  113. executing command: SET NEWNAME
  114.  
  115. Starting backup at 07-SEP-12
  116. channel prmy1: starting datafile copy
  117. input datafile file number=00001 name=/u01/app/oracle/oradata/htz/system01.dbf
  118. channel prmy2: starting datafile copy
  119. input datafile file number=00002 name=/u01/app/oracle/oradata/htz/sysaux01.dbf
  120. channel prmy3: starting datafile copy
  121. input datafile file number=00003 name=/u01/app/oracle/oradata/htz/undotbs01.dbf
  122. channel prmy4: starting datafile copy
  123. input datafile file number=00004 name=/u01/app/oracle/oradata/htz/users01.dbf
  124. output file name=/u01/app/oracle/oradata/htz/undotbs01.dbf tag=TAG20120907T055135
  125. channel prmy3: datafile copy complete, elapsed time: 00:00:08
  126. output file name=/u01/app/oracle/oradata/htz/users01.dbf tag=TAG20120907T055135
  127. channel prmy4: datafile copy complete, elapsed time: 00:00:08
  128. output file name=/u01/app/oracle/oradata/htz/sysaux01.dbf tag=TAG20120907T055135
  129. channel prmy2: datafile copy complete, elapsed time: 00:00:56
  130. output file name=/u01/app/oracle/oradata/htz/system01.dbf tag=TAG20120907T055135
  131. channel prmy1: datafile copy complete, elapsed time: 00:01:06
  132. Finished backup at 07-SEP-12
  133.  
  134. sql statement: alter system archive log current
  135.  
  136. contents of Memory Script:
  137. {
  138.    switch clone datafile all;
  139. }
  140. executing Memory Script
  141.  
  142. datafile 1 switched to datafile copy
  143. input datafile copy RECID=1 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/system01.dbf
  144. datafile 2 switched to datafile copy
  145. input datafile copy RECID=2 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/sysaux01.dbf
  146. datafile 3 switched to datafile copy
  147. input datafile copy RECID=3 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/undotbs01.dbf
  148. datafile 4 switched to datafile copy
  149. input datafile copy RECID=4 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/users01.dbf
  150. Finished Duplicate Db at 07-SEP-12
  151. released channel: prmy1
  152. released channel: prmy2
  153. released channel: prmy3
  154. released channel: prmy4
  155. released channel: stby
  156. RMAN> exit
  157.  
  158.  
  159. Recovery Manager complete.

下面把备库启动到open only下面。并recover。

  1. SQL> alter database open read only;
  2.  
  3. Database altered.
  4.  
  5. SQL> alter database recover managed standby database using current logfile disconnect from session;
  6.  
  7. Database altered.
  8.  
  9.  
  10. 在alert日志中可以看到下面的内容:
  11.  
  12. Waiting for all non-current ORLs to be archived...
  13. All non-current ORLs have been archived.
  14. Media Recovery Log /u01/app/oracle/arch/htz/1_6_793343515.dbf
  15. Media Recovery Log /u01/app/oracle/arch/htz/1_7_793343515.dbf
  16. Media Recovery Log /u01/app/oracle/arch/htz/1_8_793343515.dbf
  17. Media Recovery Log /u01/app/oracle/arch/htz/1_9_793343515.dbf
  18. Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect  
  19. Media Recovery Waiting for thread 1 sequence 10 (in transit)
  20. Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
  21.   Mem# 0: /u01/app/oracle/oradata/htz/standby01.log

下面就是开始测试了。这里我们是用的SCOTT这个用户来做测试,在SCOTT用户下面创建一个表,在备库去查询看是否创建成功,与在主库上面SWIRCH一次日志,看alert中的日志输出内容。

  1. 在主库上面做测试
  2. [oracle@11g admin]$ sqlplus / as sysdba
  3.  
  4. SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012
  5.  
  6. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
  7.  
  8.  
  9. Connected to:
  10. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
  11. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  12.  
  13. SQL> alter database set standby to maximize  availability;
  14.  
  15. Database altered.
  16.  
  17. SQL> conn scott/tiger; 
  18. ERROR:
  19. ORA-28000: the account is locked
  20.  
  21.  
  22. Warning: You are no longer connected to ORACLE.
  23. SQL> conn / as sysdba
  24. Connected.
  25. SQL> alter user scott identified by oracle;
  26.  
  27. User altered.
  28.  
  29. SQL> alter user scott account unlock;
  30.  
  31. User altered.
  32.  
  33. SQL> create table scott.test1 as select * from dba_objects;
  34.  
  35. Table created.
  36.  
  37. 备库上面
  38.  
  39. SQL> select count(*) from scott.test1;
  40.  
  41.   COUNT(*)
  42. ----------
  43.      72391
  44.  
  45. 说明已经同步
  46.  
  47. 主库上面刷新日志。
  48.  
  49. SQL> conn / as sysdba
  50. Connected.
  51. SQL> alter system archive log current;
  52.  
  53. System altered.
  54.  
  55. 日志
  56. ALTER SYSTEM ARCHIVE LOG
  57. Fri Sep 07 06:04:45 2012
  58. LGWR: Standby redo logfile selected to archive thread 1 sequence 11
  59. LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
  60. Thread 1 advanced to log sequence 11 (LGWR switch)
  61.   Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/htz/redo02.log
  62. Fri Sep 07 06:04:48 2012
  63. Archived Log entry 12 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:
  64.  
  65. 备库日志
  66.  
  67. Standby controlfile consistent with primary
  68. RFS[5]: Selected log 5 for thread 1 sequence 11 dbid 1848107928 branch 793343515
  69. Thu Sep 06 18:40:13 2012
  70. 由于换了一家公司,这家公司全部是用的11GR2+DG,11GR2没有玩过,DG也没有玩过,所以这里在自己的VM里面搭建一个11GR2+DG的测试环境,STANDBY启动到READ ONLY

    测试环境为:OS REDHAT  5.6 X86_64,DB  11.2.0.2

    安装环境与创建数据库这里就不用说了,很简单的。

    整个环境为成4个步骤:

    1,主备修改

      1.1 修改参数

      1.2 修改监听

      1.3 cp相关文件到standby上面

      1.4 创建standby日志

    2,备备修改

      2.1 修改参数

      2.2 修改监听与测试

      2.3 启动到mount

      2.4 创建相关目录

    3,主备duplicate数据库

    4,测试DG是否成功

    正在开始正式的测试过程

     

    1.1 查看数据库是否在归档与是否强制LOGGING模式。

    1. SQL> select log_mode,force_logging from v$database;
    2.  
    3. LOG_MODE     FOR 
    4. ------------ --- 
    5. ARCHIVELOG   NO 
    6.  
    7. SQL> ALTER DATABASE FORCE LOGGING;
    8.  
    9. Database altered.

    1.2 查看数据库的日志组个数与大小,因为我们创建standby日志组的个数是原日志组个数+1再与thread的积,size不能小于原日志文件的大小。

    1. SQL> select group#,bytes/1024/1024 from v$log;
    2.  
    3.     GROUP# BYTES/1024/1024
    4. ---------- --------------- 
    5.          1              50
    6.          2              50
    7.          3              50
    8.  
    9. SQL> select member from v$logfile;
    10.  
    11. MEMBER
    12. -------------------------------------------------------------------------------- 
    13. /u01/app/oracle/oradata/htz/redo03.log
    14. /u01/app/oracle/oradata/htz/redo02.log
    15. /u01/app/oracle/oradata/htz/redo01.log

    1.3 创建standby日志组,位置与原日志组相同的路径。创建完成后查询是否成功

    1. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby01.log' size 50m;
    2.  
    3. Database altered.
    4.  
    5. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby02.log' size 50m;
    6.  
    7. Database altered.
    8.  
    9. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby03.log' size 50m;
    10.  
    11. Database altered.
    12.  
    13. SQL> alter database add standby logfile '/u01/app/oracle/oradata/htz/standby04.log' size 50m;
    14.  
    15. Database altered.
    16. SQL> select group#,status,type,member from v$logfile;
    17.  
    18.     GROUP# STATUS  TYPE    MEMBER
    19. ---------- ------- ------- -------------------------------------------------- 
    20.          3         ONLINE  /u01/app/oracle/oradata/htz/redo03.log
    21.          2         ONLINE  /u01/app/oracle/oradata/htz/redo02.log
    22.          1         ONLINE  /u01/app/oracle/oradata/htz/redo01.log
    23.          4         STANDBY /u01/app/oracle/oradata/htz/standby01.log
    24.          5         STANDBY /u01/app/oracle/oradata/htz/standby02.log
    25.          6         STANDBY /u01/app/oracle/oradata/htz/standby03.log
    26.          7         STANDBY /u01/app/oracle/oradata/htz/standby04.log

    1.4 修改相关的参数,与DG的参数就只与几个参数相关,大概就是日志,文件的位置的转换,GAP的处理,其实GAP已经会自动的处理,不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。

    1. SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(htz,htzb)';
    2.  
    3. System altered.
    4.  
    5. SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htz';
    6.  
    7. System altered.
    8.  
    9. SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=htzb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htzb';
    10.  
    11. System altered.
    12.  
    13. SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
    14.  
    15. System altered.
    16.  
    17. SQL> alter system set FAL_SERVER=htzb;
    18.  
    19. System altered.
    20.  
    21. SQL> alter system set FAL_CLIENT=htz;
    22.  
    23. System altered.
    24.  
    25. SQL> alter system set db_unique_name=htz scope=spfile;
    26.  
    27. System altered.
    28.  
    29. SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
    30.  
    31. System altered.
    32.  
    33. SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' scope=spfile;
    34.  
    35. System altered.
    36.  
    37. SQL> create pfile from spfile;
    38.  
    39. File created.

    1.5 把dbs下的内容同步到standby主机上面,主要是密码文件(一定要复制过去的)与init文件。

    1. [oracle@11g admin]$ rsync -avlR /u01/app/oracle/product/11.2.0/db_1/dbs/* 192.168.100.31:/
    2. oracle@192.168.100.31's password
    3. building file list ... done
    4. /u01/
    5. /u01/app/
    6. /u01/app/oracle/
    7. /u01/app/oracle/product/
    8. /u01/app/oracle/product/11.2.0/
    9. /u01/app/oracle/product/11.2.0/db_1/
    10. /u01/app/oracle/product/11.2.0/db_1/dbs/
    11. /u01/app/oracle/product/11.2.0/db_1/dbs/hc_DBUA0.dat
    12. /u01/app/oracle/product/11.2.0/db_1/dbs/hc_htz.dat
    13. /u01/app/oracle/product/11.2.0/db_1/dbs/hc_luo.dat
    14. /u01/app/oracle/product/11.2.0/db_1/dbs/init.ora
    15. /u01/app/oracle/product/11.2.0/db_1/dbs/inithtz.ora
    16. /u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZ
    17. /u01/app/oracle/product/11.2.0/db_1/dbs/lkHTZA
    18. /u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz
    19. /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f
    20. /u01/app/oracle/product/11.2.0/db_1/dbs/spfilehtz.ora
    21.  
    22. sent 9764651 bytes  received 282 bytes  161403.85 bytes/sec
    23. total size is 9762574  speedup is 1.00

    1.6  监听的修改,特别注意这里我们使用了静态的监听,是为了以后我们测试broker时使用的,如果你不用这个,那边可以用动态监听,

    1. $ lsnrctl stop
    2.  
    3. LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 31-AUG-2012 21:38:51
    4.  
    5. Copyright (c) 1991, 2009, Oracle.  All rights reserved.
    6.  
    7. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
    8. The command completed successfully
    9. [oracle@11g admin]$ cat listener.ora
    10. SID_LIST_LISTENER =
    11.   (SID_LIST =
    12.     (SID_DESC =
    13.      (GLOBAL_DBNAME = htz)
    14.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    15.      (SID_NAME = htz)
    16.     )
    17.    )
    18.  
    19. LISTENER =
    20.   (DESCRIPTION =
    21.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
    22.   ) 
    23. [oracle@11g admin]$ cat tnsnames.ora
    24. HTZB =
    25.   (DESCRIPTION =
    26.     (ADDRESS_LIST =
    27.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
    28.     )
    29.     (CONNECT_DATA =
    30.       (SERVER = dedicate)
    31.       (SERVICE_NAME = htzb)
    32.     )
    33.   )
    34.  
    35. HTZ =
    36.   (DESCRIPTION =
    37.     (ADDRESS_LIST =
    38.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
    39.     )
    40.     (CONNECT_DATA =
    41.       (SERVER = dedicate)
    42.       (SERVICE_NAME = htz)
    43.     )
    44.   )
    45.  
    46. 启动监听
    47. [oracle@11g admin]$ lsnrctl start
    48.  
    49. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 07-SEP-2012 05:35:50
    50.  
    51. Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    52.  
    53. Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
    54.  
    55. TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    56. System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    57. Log messages written to /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
    58. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
    59.  
    60. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.30)(PORT=1521)))
    61. STATUS of the LISTENER
    62. ------------------------ 
    63. Alias                     LISTENER
    64. Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    65. Start Date                07-SEP-2012 05:35:50
    66. Uptime                    0 days 0 hr. 0 min. 0 sec
    67. Trace Level               off 
    68. Security                  ON: Local OS Authentication
    69. SNMP                      OFF 
    70. Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    71. Listener Log File         /u01/app/oracle/diag/tnslsnr/11g/listener/alert/log.xml
    72. Listening Endpoints Summary...
    73.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.30)(PORT=1521)))
    74. Services Summary...
    75. Service "htz" has 1 instance(s).
    76.   Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
    77. The command completed successfully

    主库上面修改的内容差不多就是这些了。

    下面就是库备的修改了。

    2.1 修改监听

    1. [oracle@11gdg admin]$ cat listener.ora
    2. # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    3. # Generated by Oracle configuration tools.
    4.  
    5. SID_LIST_LISTENER =
    6.   (SID_LIST =
    7.     (SID_DESC =
    8.      (GLOBAL_DBNAME = htzb)
    9.      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    10.      (SID_NAME = htz)
    11.     )
    12.    )
    13.  
    14. LISTENER =
    15.   (DESCRIPTION =
    16.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
    17.   ) 
    18. ADR_BASE_LISTENER = /u01/app/oracle
    19.  
    20. [oracle@11gdg admin]$ cat tnsnames.ora
    21. # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
    22. # Generated by Oracle configuration tools.
    23.  
    24.  
    25. HTZB =
    26.   (DESCRIPTION =
    27.     (ADDRESS_LIST =
    28.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.31)(PORT = 1521))
    29.     )
    30.     (CONNECT_DATA =
    31.       (SERVER = dedicate)
    32.       (SERVICE_NAME = htzb)
    33.     )
    34.   )
    35.  
    36. HTZ =
    37.   (DESCRIPTION =
    38.     (ADDRESS_LIST =
    39.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.30)(PORT = 1521))
    40.     )
    41.     (CONNECT_DATA =
    42.       (SERVER = dedicate)
    43.       (SERVICE_NAME = htz)
    44.     )
    45.   )
    46. [oracle@11gdg admin]$ lsnrctl stop
    47.  
    48. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:17
    49.  
    50. Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    51.  
    52. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
    53. The command completed successfully
    54. [oracle@11gdg admin]$ lsnrctl start
    55.  
    56. LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 06-SEP-2012 18:13:23
    57.  
    58. Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    59.  
    60. Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
    61.  
    62. TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    63. System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    64. Log messages written to /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
    65. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
    66.  
    67. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.31)(PORT=1521)))
    68. STATUS of the LISTENER
    69. ------------------------ 
    70. Alias                     LISTENER
    71. Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
    72. Start Date                06-SEP-2012 18:13:24
    73. Uptime                    0 days 0 hr. 0 min. 0 sec
    74. Trace Level               off 
    75. Security                  ON: Local OS Authentication
    76. SNMP                      OFF 
    77. Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    78. Listener Log File         /u01/app/oracle/diag/tnslsnr/11gdg/listener/alert/log.xml
    79. Listening Endpoints Summary...
    80.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.31)(PORT=1521)))
    81. Services Summary...
    82. Service "htzb" has 1 instance(s).
    83.   Instance "htz", status UNKNOWN, has 1 handler(s) for this service...
    84. The command completed successfully
    85.  
    86. 测试监听是否正常
    87. [oracle@11gdg /]$ sqlplus sys/oracle@htz as sysdba
    88.  
    89. SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:20 2012
    90.  
    91. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    92.  
    93.  
    94. Connected to:
    95. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    96. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    97.  
    98. SQL> exit
    99. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    100. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    101. [oracle@11gdg /]$ sqlplus sys/oracle@htzb as sysdba
    102.  
    103. SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:19:24 2012
    104.  
    105. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    106.  
    107. Connected to an idle instance.

    2.1 创建相关文件与修改参数文件

    1. 创建相关目录
    2. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/arch/htz
    3. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/flash_recovery_area/htz
    4. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/oradata/htz
    5. [oracle@11gdg admin]$ mkdir -p /u01/app/oracle/admin/htz/adump
    6. 修改参数文件
    7. [oracle@11gdg /]$ cd $ORACLE_HOME/dbs
    8. [oracle@11gdg dbs]$ rm spfilehtz.ora
    9. [oracle@11gdg dbs]$ cat inithtz.ora
    10. htz.__db_cache_size=67108864
    11. htz.__java_pool_size=4194304
    12. htz.__large_pool_size=4194304
    13. htz.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
    14. htz.__pga_aggregate_target=134217728
    15. htz.__sga_target=180355072
    16. htz.__shared_io_pool_size=0
    17. htz.__shared_pool_size=96468992
    18. htz.__streams_pool_size=0
    19. *.audit_file_dest='/u01/app/oracle/admin/htz/adump' 
    20. *.audit_trail='db' 
    21. *.compatible='11.2.0.0.0' 
    22. *.control_files='/u01/app/oracle/oradata/htz/control01.ctl','/u01/app/oracle/oradata/htz/control02.ctl' 
    23. *.db_block_size=8192
    24. *.db_domain='' 
    25. *.db_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' 
    26. *.db_name='htz' 
    27. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' 
    28. *.db_recovery_file_dest_size=4227858432
    29. *.db_unique_name='HTZB' 
    30. *.diagnostic_dest='/u01/app/oracle' 
    31. *.dispatchers='(PROTOCOL=TCP) (SERVICE=htzXDB)' 
    32. *.fal_client='HTZB' 
    33. *.fal_server='HTZ' 
    34. *.log_archive_config='DG_CONFIG=(htz,htzb)' 
    35. *.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/htz/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=htzb' 
    36. *.log_archive_dest_2='SERVICE=htz LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=htz' 
    37. *.log_archive_dest_state_1='ENABLE' 
    38. *.log_archive_format='%t_%s_%r.dbf' 
    39. *.log_file_name_convert='/u01/app/oracle/oradata/htz/','/u01/app/oracle/oradata/htz/' 
    40. *.memory_target=314572800
    41. *.open_cursors=300
    42. *.processes=150
    43. *.remote_login_passwordfile='EXCLUSIVE' 
    44. *.undo_tablespace='UNDOTBS1' 

    1.3 创建spfile与启动数据库到mount

    1. 创建spfile,并启动数据库到NOMOUNT
    2. [oracle@11gdg dbs]$ sqlplus / as sysdba
    3.  
    4. SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 6 18:24:02 2012
    5.  
    6. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    7.  
    8. Connected to an idle instance.
    9.  
    10. SQL> startup nomount;
    11. ORACLE instance started.
    12.  
    13. Total System Global Area  313159680 bytes
    14. Fixed Size                  2226072 bytes
    15. Variable Size             239077480 bytes
    16. Database Buffers           67108864 bytes
    17. Redo Buffers                4747264 bytes
    18. SQL> create spfile from pfile;
    19.  
    20. File created.
    21.  
    22. SQL> startup force mount;
    23. ORACLE instance started.
    24.  
    25. Total System Global Area  313159680 bytes
    26. Fixed Size                  2226072 bytes
    27. Variable Size             239077480 bytes
    28. Database Buffers           67108864 bytes
    29. Redo Buffers                4747264 bytes
    30. ORA-00205: error in identifying control file, check alert log for more info
    31.  
    32.  
    33. SQL> show parameter spfile;
    34.  
    35. NAME                                 TYPE        VALUE
    36. ------------------------------------ ----------- ------------------------------
    37. spfile                               string      /u01/app/oracle/product/11.2.0
    38.                                                  /db_1/dbs/spfilehtz.ora

    1.4 修改/etc/oratab文件,由于OS不一样,这里修改的位置也不一样,如果SUN  /var/opt/oracle/oratab,IBM的/etc/oratab

    1. [root@11gdg ~]# echo "htz:/u01/app/oracle/product/11.2.0/db_1:N">>/etc/oratab
    2. [root@11gdg ~]# tail -1 /etc/oratab
    3. htz:/u01/app/oracle/product/11.2.0/db_1:N

    备库的相关操作到此就差不多,到了duplicate的时候了。

    回到主备上面。

    在前面我们修改了两个参数(db/log_file_name_convertt)到spfile文件中,还没有重启数据库,这里我们重启一下主数据库

    3.1

    1. [oracle@11g admin]$ sqlplus / as sysdba
    2.  
    3. SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:50:13 2012
    4.  
    5. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    6.  
    7.  
    8. Connected to:
    9. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    10. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    11.  
    12. SQL> shutdown immediate;
    13. Database closed.
    14. Database dismounted.
    15. ORACLE instance shut down.
    16. SQL> startup
    17. ORACLE instance started.
    18.  
    19. Total System Global Area  313159680 bytes
    20. Fixed Size                  2226072 bytes
    21. Variable Size             247466088 bytes
    22. Database Buffers           58720256 bytes
    23. Redo Buffers                4747264 bytes
    24. Database mounted.
    25. Database opened.
    26. SQL> exit
    27. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    28. With the Partitioning, OLAP, Data Mining and Real Application Testing options

    3.2 duplicate开始

    1. [oracle@11g admin]$ rman target sys/oracle@htz auxiliary sys/oracle@htzb
    2.  
    3. Recovery Manager: Release 11.2.0.2.0 - Production on Fri Sep 7 05:50:46 2012
    4.  
    5. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    6.  
    7. connected to target database: HTZ (DBID=1848107928)
    8. connected to auxiliary database: HTZ (not mounted)
    9.  
    10. RMAN> run {
    11. 2> allocate channel prmy1 type disk;
    12. 3> allocate channel prmy2 type disk;
    13. 4> allocate channel prmy3 type disk;
    14. 5> allocate channel prmy4 type disk;
    15. 6> allocate auxiliary channel stby type disk;
    16. 7> duplicate target database for standby from active database nofilenamecheck
    17. 8> ;
    18. 9> }
    19.  
    20. using target database control file instead of recovery catalog
    21. allocated channel: prmy1
    22. channel prmy1: SID=36 device type=DISK
    23.  
    24. allocated channel: prmy2
    25. channel prmy2: SID=37 device type=DISK
    26.  
    27. allocated channel: prmy3
    28. channel prmy3: SID=38 device type=DISK
    29.  
    30. allocated channel: prmy4
    31. channel prmy4: SID=39 device type=DISK
    32.  
    33. allocated channel: stby
    34. channel stby: SID=21 device type=DISK
    35.  
    36. Starting Duplicate Db at 07-SEP-12
    37.  
    38. contents of Memory Script:
    39. {
    40.    backup as copy reuse
    41.    targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz' auxiliary format 
    42.  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwhtz'   ;
    43. }
    44. executing Memory Script
    45.  
    46. Starting backup at 07-SEP-12
    47. Finished backup at 07-SEP-12
    48.  
    49. contents of Memory Script:
    50. {
    51.    backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/htz/control01.ctl';
    52.    restore clone controlfile to  '/u01/app/oracle/oradata/htz/control02.ctl' from 
    53.  '/u01/app/oracle/oradata/htz/control01.ctl';
    54. }
    55. executing Memory Script
    56.  
    57. Starting backup at 07-SEP-12
    58. channel prmy1: starting datafile copy
    59. copying standby control file
    60. output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_htz.f tag=TAG20120907T055127 RECID=1 STAMP=793345887
    61. channel prmy1: datafile copy complete, elapsed time: 00:00:01
    62. Finished backup at 07-SEP-12
    63.  
    64. Starting restore at 07-SEP-12
    65.  
    66. channel stby: copied control file copy
    67. Finished restore at 07-SEP-12
    68.  
    69. contents of Memory Script:
    70. {
    71.    sql clone 'alter database mount standby database';
    72. }
    73. executing Memory Script
    74.  
    75. sql statement: alter database mount standby database 
    76.  
    77. contents of Memory Script:
    78. {
    79.    set newname for tempfile  1 to 
    80.  "/u01/app/oracle/oradata/htz/temp01.dbf";
    81.    switch clone tempfile all;
    82.    set newname for datafile  1 to 
    83.  "/u01/app/oracle/oradata/htz/system01.dbf";
    84.    set newname for datafile  2 to 
    85.  "/u01/app/oracle/oradata/htz/sysaux01.dbf";
    86.    set newname for datafile  3 to 
    87.  "/u01/app/oracle/oradata/htz/undotbs01.dbf";
    88.    set newname for datafile  4 to 
    89.  "/u01/app/oracle/oradata/htz/users01.dbf";
    90.    backup as copy reuse
    91.    datafile  1 auxiliary format 
    92.  "/u01/app/oracle/oradata/htz/system01.dbf"   datafile 
    93. 2 auxiliary format 
    94.  "/u01/app/oracle/oradata/htz/sysaux01.dbf"   datafile 
    95. 3 auxiliary format 
    96.  "/u01/app/oracle/oradata/htz/undotbs01.dbf"   datafile 
    97. 4 auxiliary format 
    98.  "/u01/app/oracle/oradata/htz/users01.dbf"   ;
    99.    sql 'alter system archive log current';
    100. }
    101. executing Memory Script
    102.  
    103. executing command: SET NEWNAME
    104.  
    105. renamed tempfile 1 to /u01/app/oracle/oradata/htz/temp01.dbf in control file
    106.  
    107. executing command: SET NEWNAME
    108.  
    109. executing command: SET NEWNAME
    110.  
    111. executing command: SET NEWNAME
    112.  
    113. executing command: SET NEWNAME
    114.  
    115. Starting backup at 07-SEP-12
    116. channel prmy1: starting datafile copy
    117. input datafile file number=00001 name=/u01/app/oracle/oradata/htz/system01.dbf
    118. channel prmy2: starting datafile copy
    119. input datafile file number=00002 name=/u01/app/oracle/oradata/htz/sysaux01.dbf
    120. channel prmy3: starting datafile copy
    121. input datafile file number=00003 name=/u01/app/oracle/oradata/htz/undotbs01.dbf
    122. channel prmy4: starting datafile copy
    123. input datafile file number=00004 name=/u01/app/oracle/oradata/htz/users01.dbf
    124. output file name=/u01/app/oracle/oradata/htz/undotbs01.dbf tag=TAG20120907T055135
    125. channel prmy3: datafile copy complete, elapsed time: 00:00:08
    126. output file name=/u01/app/oracle/oradata/htz/users01.dbf tag=TAG20120907T055135
    127. channel prmy4: datafile copy complete, elapsed time: 00:00:08
    128. output file name=/u01/app/oracle/oradata/htz/sysaux01.dbf tag=TAG20120907T055135
    129. channel prmy2: datafile copy complete, elapsed time: 00:00:56
    130. output file name=/u01/app/oracle/oradata/htz/system01.dbf tag=TAG20120907T055135
    131. channel prmy1: datafile copy complete, elapsed time: 00:01:06
    132. Finished backup at 07-SEP-12
    133.  
    134. sql statement: alter system archive log current 
    135.  
    136. contents of Memory Script:
    137. {
    138.    switch clone datafile all;
    139. }
    140. executing Memory Script
    141.  
    142. datafile 1 switched to datafile copy
    143. input datafile copy RECID=1 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/system01.dbf
    144. datafile 2 switched to datafile copy
    145. input datafile copy RECID=2 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/sysaux01.dbf
    146. datafile 3 switched to datafile copy
    147. input datafile copy RECID=3 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/undotbs01.dbf
    148. datafile 4 switched to datafile copy
    149. input datafile copy RECID=4 STAMP=793304890 file name=/u01/app/oracle/oradata/htz/users01.dbf
    150. Finished Duplicate Db at 07-SEP-12
    151. released channel: prmy1
    152. released channel: prmy2
    153. released channel: prmy3
    154. released channel: prmy4
    155. released channel: stby
    156. RMAN> exit
    157.  
    158.  
    159. Recovery Manager complete.

    下面把备库启动到open only下面。并recover。

    1. SQL> alter database open read only;
    2.  
    3. Database altered.
    4.  
    5. SQL> alter database recover managed standby database using current logfile disconnect from session;
    6.  
    7. Database altered.
    8.  
    9.  
    10. 在alert日志中可以看到下面的内容:
    11.  
    12. Waiting for all non-current ORLs to be archived...
    13. All non-current ORLs have been archived.
    14. Media Recovery Log /u01/app/oracle/arch/htz/1_6_793343515.dbf
    15. Media Recovery Log /u01/app/oracle/arch/htz/1_7_793343515.dbf
    16. Media Recovery Log /u01/app/oracle/arch/htz/1_8_793343515.dbf
    17. Media Recovery Log /u01/app/oracle/arch/htz/1_9_793343515.dbf
    18. Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect  
    19. Media Recovery Waiting for thread 1 sequence 10 (in transit)
    20. Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
    21.   Mem# 0: /u01/app/oracle/oradata/htz/standby01.log

    下面就是开始测试了。这里我们是用的SCOTT这个用户来做测试,在SCOTT用户下面创建一个表,在备库去查询看是否创建成功,与在主库上面SWIRCH一次日志,看alert中的日志输出内容。

    1. 在主库上面做测试
    2. [oracle@11g admin]$ sqlplus / as sysdba
    3.  
    4. SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012
    5.  
    6. Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    11. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    12.  
    13. SQL> alter database set standby to maximize  availability;
    14.  
    15. Database altered.
    16.  
    17. SQL> conn scott/tiger; 
    18. ERROR:
    19. ORA-28000: the account is locked
    20.  
    21.  
    22. Warning: You are no longer connected to ORACLE.
    23. SQL> conn / as sysdba
    24. Connected.
    25. SQL> alter user scott identified by oracle;
    26.  
    27. User altered.
    28.  
    29. SQL> alter user scott account unlock;
    30.  
    31. User altered.
    32.  
    33. SQL> create table scott.test1 as select * from dba_objects;
    34.  
    35. Table created.
    36.  
    37. 备库上面
    38.  
    39. SQL> select count(*) from scott.test1;
    40.  
    41.   COUNT(*)
    42. ---------- 
    43.      72391
    44.  
    45. 说明已经同步
    46.  
    47. 主库上面刷新日志。
    48.  
    49. SQL> conn / as sysdba
    50. Connected.
    51. SQL> alter system archive log current;
    52.  
    53. System altered.
    54.  
    55. 日志
    56. ALTER SYSTEM ARCHIVE LOG
    57. Fri Sep 07 06:04:45 2012
    58. LGWR: Standby redo logfile selected to archive thread 1 sequence 11
    59. LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
    60. Thread 1 advanced to log sequence 11 (LGWR switch)
    61.   Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/htz/redo02.log
    62. Fri Sep 07 06:04:48 2012
    63. Archived Log entry 12 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:
    64.  
    65. 备库日志
    66.  
    67. Standby controlfile consistent with primary 
    68. RFS[5]: Selected log 5 for thread 1 sequence 11 dbid 1848107928 branch 793343515
    69. Thu Sep 06 18:40:13 2012
    70. Media Recovery Waiting for thread 1 sequence 11 (in transit)
    71. Recovery of Online Redo Log: Thread 1 Group 5 Seq 11 Reading mem 0
    72.   Mem# 0: /u01/app/oracle/oradata/htz/standby02.log
    73. Thu Sep 06 18:40:16 2012
    74. Archived Log entry 5 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:
    75.  
    76.  
    77.  
    78. System altered.
  71. Media Recovery Waiting for thread 1 sequence 11 (in transit)
  72. Recovery of Online Redo Log: Thread 1 Group 5 Seq 11 Reading mem 0
  73.   Mem# 0: /u01/app/oracle/oradata/htz/standby02.log
  74. Thu Sep 06 18:40:16 2012
  75. Archived Log entry 5 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:
  76.  
  77.  
  78.  
  79. System altered.

本文固定链接: http://www.htz.pw/2014/06/18/11gr2-datagruad-%e7%8e%af%e5%a2%83%e6%90%ad%e5%bb%baborker.html | 认真就输

该日志由 huangtingzhong 于2014年06月18日发表在 DG 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 11GR2 搭建活动的物理DG/DATAGRUAD (READ ONLY模式) | 认真就输
关键字: , , ,