Oracle Data Guard 单实例到单实例配置(物理standby)

oi622

接上一篇oracle dataguard概念篇，现在进行下详细的配置，此次配置的是单实例到单实例，也就是说主库和备库都是基于文件系统的单实例。

主机环境：Red Hat Enterprise Linux Server release 6.3
Rdbms版本：11.2.0.3
其他详细信息如下所示：


需要注意的是，两边的/etc/hosts文件保证都包含对方的ip对应关系

oracle安装的目录是相同的
编辑/etc/hosts，分别添加以下内容
192.168.1.241 db1
192.168.1.242 dg
主库数据文件位置/oradata/beijing/map  归档位置 /oradata/beijing/arch
备库数据文件位置/oradata/shanghai/map 归档位置 /oradata/shanghai/arch
本次配置的是11G 的active data guard，主要分为两大部分：主库操作和备库操作，为了避免操作出现混乱，现列出以下几个步骤：

主库操作
1.打开Forced Logging
2.配置主库参数文件
3.打开主库归档
4.创建主库备份用来作为备库恢复
5.创建standby数据库的控制文件
6.创建配置备库参数文件
7.拷贝所需文件到备库


备库操作
1.设置备库环境变量
  1.1密码文件拷贝
  1.2监听配置
  1.3创建网络服务名
2.创建所需的目录
3.参数文件配置
4.恢复备库
  4.1恢复参数文件
  4.2恢复控制文件
  4.3恢复数据库
5.创建standby redo
  5.1主库创建
  5.2备库创建
6.网络服务名测试
  6.1主库测试
  6.2备库测试
7.开启实时应用
8.测试
主库操作
1.打开Forced Logging

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[oracle@db1 ~]$ mkdir /home/oracle/standby [oracle@db1 ~]$ mkdir /oradata/beijing/arch [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 14:19:10 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter database force logging ; Database altered.

2.配置主库参数文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

SQL> create pfile='/tmp/a.txt' from spfile ; File created. [oracle@db1 ~]$ cat /tmp/a.txt *.audit_file_dest='/oracle/app/oracle/admin/map/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/oradata/beijing/map/control01.ctl','/oradata/beijing/map/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='map' *.diagnostic_dest='/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=mapXDB)' *.memory_target=629145600 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_unique_name=beijing *.log_archive_config='dg_config=(beijing,shanghai)' *.log_archive_dest_1='location=/oradata/beijing/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=beijing' *.log_archive_dest_2='service=shanghai LGWR AFFIRM ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=shanghai' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.remote_login_passwordfile=EXCLUSIVE *.log_archive_format=%t_%s_%r.arc *.log_archive_max_processes=30 *.fal_server=shanghai *.standby_file_management=auto *.db_file_name_convert='/oradata/shanghai/map','/oradata/beijing/map' *.log_file_name_convert='/oradata/shanghai/map','/oradata/beijing/map'

保存退出

3.打开主库归档

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile='/tmp/a.txt' ; File created. SQL> startup mount; ORACLE instance started. Total System Global Area  626327552 bytes Fixed Size         2230952 bytes Variable Size        373294424 bytes Database Buffers     243269632 bytes Redo Buffers           7532544 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL>

4.创建主库备份用来作为备库恢复

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95

[oracle@db1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 14 16:06:48 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: MAP (DBID=1528459940) RMAN> run{ allocate channel c1 type disk; allocate channel c2 type disk; backup filesperset 2 database format '/home/oracle/standby/full_%d_%T_%s_%p'; sql 'alter system archive log current'; sql 'alter system archive log current'; sql 'alter system archive log current'; backup archivelog all format '/home/oracle/standby/arch_%d_%T_%s_%p'; }2> 3> 4> 5> 6> 7> 8> 9> using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=32 device type=DISK allocated channel: c2 channel c2: SID=157 device type=DISK Starting backup at 14-OCT-15 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set input datafile file number=00001 name=/oradata/beijing/map/system01.dbf input datafile file number=00004 name=/oradata/beijing/map/users01.dbf channel c1: starting piece 1 at 14-OCT-15 channel c2: starting full datafile backup set channel c2: specifying datafile(s) in backup set input datafile file number=00002 name=/oradata/beijing/map/sysaux01.dbf input datafile file number=00003 name=/oradata/beijing/map/undotbs01.dbf channel c2: starting piece 1 at 14-OCT-15 channel c2: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/full_MAP_20151014_2_1 tag=TAG20151014T160708 comment=NONE channel c2: backup set complete, elapsed time: 00:00:26 channel c2: starting full datafile backup set channel c2: specifying datafile(s) in backup set including current control file in backup set channel c2: starting piece 1 at 14-OCT-15 channel c1: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/full_MAP_20151014_1_1 tag=TAG20151014T160708 comment=NONE channel c1: backup set complete, elapsed time: 00:00:30 channel c1: starting full datafile backup set channel c1: specifying datafile(s) in backup set including current SPFILE in backup set channel c1: starting piece 1 at 14-OCT-15 channel c2: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/full_MAP_20151014_3_1 tag=TAG20151014T160708 comment=NONE channel c2: backup set complete, elapsed time: 00:00:01 channel c1: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/full_MAP_20151014_4_1 tag=TAG20151014T160708 comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 14-OCT-15 sql statement: alter system archive log current sql statement: alter system archive log current sql statement: alter system archive log current Starting backup at 14-OCT-15 current log archived channel c1: starting archived log backup set channel c1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=1 STAMP=893088283 channel c1: starting piece 1 at 14-OCT-15 channel c2: starting archived log backup set channel c2: specifying archived log(s) in backup set input archived log thread=1 sequence=4 RECID=2 STAMP=893088284 input archived log thread=1 sequence=5 RECID=3 STAMP=893088459 input archived log thread=1 sequence=6 RECID=4 STAMP=893088459 channel c2: starting piece 1 at 14-OCT-15 channel c1: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/arch_MAP_20151014_5_1 tag=TAG20151014T160742 comment=NONE channel c1: backup set complete, elapsed time: 00:00:00 channel c1: starting archived log backup set channel c1: specifying archived log(s) in backup set input archived log thread=1 sequence=7 RECID=5 STAMP=893088462 input archived log thread=1 sequence=8 RECID=6 STAMP=893088462 channel c1: starting piece 1 at 14-OCT-15 channel c2: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/arch_MAP_20151014_6_1 tag=TAG20151014T160742 comment=NONE channel c2: backup set complete, elapsed time: 00:00:00 channel c1: finished piece 1 at 14-OCT-15 piece handle=/home/oracle/standby/arch_MAP_20151014_7_1 tag=TAG20151014T160742 comment=NONE channel c1: backup set complete, elapsed time: 00:00:01 Finished backup at 14-OCT-15 released channel: c1 released channel: c2 RMAN>

5.创建standby数据库的控制文件

1 2 3 4 5

SQL> alter database create standby controlfile as '/home/oracle/standby/standby.ctl'; Database altered. SQL>

6.创建配置备库参数文件

1 2 3 4 5

SQL> create pfile='/home/oracle/standby.txt' from spfile ; File created. SQL>

7.拷贝所需文件到备库

1 2 3 4 5 6 7 8 9 10 11 12

[oracle@db1 ~]$ scp -r /home/oracle/standby/ dg:/home/oracle oracle@dg's password: a.txt                                                   100% 1136     1.1KB/s   00:00    arch_MAP_20151014_6_1                                   100%  139KB 138.5KB/s   00:00    full_MAP_20151014_4_1                                   100%   96KB  96.0KB/s   00:00    full_MAP_20151014_2_1                                   100%  374MB  74.9MB/s   00:05    standby.ctl                                             100% 9520KB   9.3MB/s   00:00    arch_MAP_20151014_7_1                                   100% 4608     4.5KB/s   00:00    full_MAP_20151014_1_1                                   100%  597MB  39.8MB/s   00:15    full_MAP_20151014_3_1                                   100% 9568KB   9.3MB/s   00:00    arch_MAP_20151014_5_1                                   100%  161KB 161.0KB/s   00:00    [oracle@db1 ~]$

备库操作

1.设置备库环境

  1.1密码文件拷贝

1 2 3 4

[oracle@dg ~]$ scp db1:/oracle/app/oracle/product/11.2/db_1/dbs/orapwmap /oracle/app/oracle/product/11.2/db_1/dbs/orapwmap oracle@db1's password: orapwmap                                                 100% 1536     1.5KB/s   00:00    [oracle@dg ~]$

  1.2监听配置

可以使用netca进行配置，或者使用nemgr

1 2	[oracle@dg ~]$ lsnrctl stop [oracle@dg ~]$ lsnrctl start

  1.3创建网络服务名

  可以使用配置工具netmgr配置，此次直接复制以下文件到指定位置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

[oracle@dg ~]$ cd $ORACLE_BASE/diag [oracle@dg admin]$ cat tnsnames.ora SHANGHAI =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = shanghai)     )   ) BEIJING =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.241)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = beijing)     )   )

拷贝相同的文件内容到主库的tnsnames.ora中

2.创建所需的目录

1 2 3 4 5 6

[iyunv@dg ~]# mkdir /oradata [iyunv@dg ~]# chown -R oracle.oinstall /oradata/ [iyunv@dg ~]# su - oracle [oracle@dg ~]$ mkdir -p /oracle/app/oracle/admin/$ORACLE_SID/adump [oracle@dg ~]$ mkdir -p /oradata/shanghai/arch [oracle@dg ~]$ mkdir -p /oradata/shanghai/map

3.参数文件配置

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

[oracle@dg ~]$ vi /home/oracle/standby/a.txt *.audit_file_dest='/oracle/app/oracle/admin/map/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/oradata/shanghai/map/control01.ctl','/oradata/shanghai/map/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='map' *.diagnostic_dest='/oracle/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=mapXDB)' *.memory_target=629145600 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_unique_name=shanghai *.log_archive_config='dg_config=(beijing,shanghai)' *.log_archive_dest_1='location=/oradata/shanghai/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=shanghai' *.log_archive_dest_2='service=beijing LGWR AFFIRM ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=beijing' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.remote_login_passwordfile=EXCLUSIVE *.log_archive_format=%t_%s_%r.arc *.log_archive_max_processes=30 *.fal_server=beijing *.standby_file_management=auto *.db_file_name_convert='/oradata/beijing/map','/oradata/shanghai/map' *.log_file_name_convert='/oradata/beijing/map','/oradata/shanghai/map'

保存退出。

4.恢复备库

  4.1恢复参数文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[oracle@dg ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 16:40:52 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='/home/oracle/standby/a.txt' ; File created. SQL> startup nomount ; ORACLE instance started. Total System Global Area  626327552 bytes Fixed Size         2230952 bytes Variable Size        373294424 bytes Database Buffers     243269632 bytes Redo Buffers           7532544 bytes SQL>

  4.2恢复控制文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

[oracle@dg ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 14 16:43:09 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. connected to target database: MAP (not mounted) RMAN> restore standby controlfile from '/home/oracle/standby/standby.ctl'; Starting restore at 14-OCT-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/oradata/shanghai/map/control01.ctl output file name=/oradata/shanghai/map/control02.ctl Finished restore at 14-OCT-15 RMAN> SQL> select controlfile_type from v$database ; CONTROL ------- STANDBY

  4.3恢复数据库

由于此次是恢复到指定的目录，而不是和主库相同的目录，所以不是简单的restore

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97

SQL> select 'set newname for datafile ''' || name ||        ''' to ''/oradata/shanghai/gqxt/' ||        substr(name, instr(name, '/', -1) + 1) || ''';'   from v$datafile order by file#;  2    3    4   'SETNEWNAMEFORDATAFILE'''||NAME||'''TO''/ORADATA/SHANGHAI/GQXT/'||SUBSTR(NAME,IN -------------------------------------------------------------------------------- set newname for datafile '/oradata/shanghai/map/system01.dbf' to '/oradata/shang hai/gqxt/system01.dbf'; set newname for datafile '/oradata/shanghai/map/sysaux01.dbf' to '/oradata/shang hai/gqxt/sysaux01.dbf'; set newname for datafile '/oradata/shanghai/map/undotbs01.dbf' to '/oradata/shan ghai/gqxt/undotbs01.dbf'; set newname for datafile '/oradata/shanghai/map/users01.dbf' to '/oradata/shangh ai/gqxt/users01.dbf'; SQL> RMAN> run { set newname for datafile '/oradata/beijing/map/system01.dbf' to '/oradata/shanghai/map/system01.dbf'; set newname for datafile '/oradata/beijing/map/sysaux01.dbf' to '/oradata/shanghai/map/sysaux01.dbf'; set newname for datafile '/oradata/beijing/map2> 3> 4> /undotbs01.dbf' to '/oradata/shanghai/map/undotbs01.dbf'; set newname for datafile '/oradata/beijing/map/users01.dbf' to '/oradata/shanghai/map/users01.dbf'; restore database; switch datafile all; }5> 6> 7> 8> executing command: SET NEWNAME released channel: ORA_DISK_1 executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 14-OCT-15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=10 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00002 to /oradata/shanghai/map/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /oradata/shanghai/map/undotbs01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/standby/full_MAP_20151014_2_1 channel ORA_DISK_1: piece handle=/home/oracle/standby/full_MAP_20151014_2_1 tag=TAG20151014T160708 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /oradata/shanghai/map/system01.dbf channel ORA_DISK_1: restoring datafile 00004 to /oradata/shanghai/map/users01.dbf channel ORA_DISK_1: reading from backup piece /home/oracle/standby/full_MAP_20151014_1_1 channel ORA_DISK_1: piece handle=/home/oracle/standby/full_MAP_20151014_1_1 tag=TAG20151014T160708 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 14-OCT-15 RMAN> recover database ; Starting recover at 14-OCT-15 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 9 is already on disk as file /oradata/shanghai/arch/1_9_893081127.arc archived log for thread 1 with sequence 10 is already on disk as file /oradata/shanghai/arch/1_10_893081127.arc channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=5 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=6 channel ORA_DISK_1: reading from backup piece /home/oracle/standby/arch_MAP_20151014_6_1 channel ORA_DISK_1: piece handle=/home/oracle/standby/arch_MAP_20151014_6_1 tag=TAG20151014T160742 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/oradata/shanghai/arch/1_5_893081127.arc thread=1 sequence=5 archived log file name=/oradata/shanghai/arch/1_6_893081127.arc thread=1 sequence=6 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=7 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=8 channel ORA_DISK_1: reading from backup piece /home/oracle/standby/arch_MAP_20151014_7_1 channel ORA_DISK_1: piece handle=/home/oracle/standby/arch_MAP_20151014_7_1 tag=TAG20151014T160742 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/oradata/shanghai/arch/1_7_893081127.arc thread=1 sequence=7 archived log file name=/oradata/shanghai/arch/1_8_893081127.arc thread=1 sequence=8 archived log file name=/oradata/shanghai/arch/1_9_893081127.arc thread=1 sequence=9 archived log file name=/oradata/shanghai/arch/1_10_893081127.arc thread=1 sequence=10 unable to find archived log archived log thread=1 sequence=11 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 10/14/2015 16:51:16 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN of 1018211 RMAN>

5.创建standby redo

  5.1主库创建

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> alter database add standby logfile group 4 ('/oradata/beijing/map/standby01a.log','/oradata/beijing/map/standby01b.log') size 50M; Database altered. SQL> alter database add standby logfile group 5 ('/oradata/beijing/map/standby02a.log','/oradata/beijing/map/standby02b.log') size 50M; Database altered. SQL> alter database add standby logfile group 6 ('/oradata/beijing/map/standby03a.log','/oradata/beijing/map/standby03b.log') size 50M; Database altered. SQL> alter database add standby logfile group 7 ('/oradata/beijing/map/standby04a.log','/oradata/beijing/map/standby04b.log') size 50M; Database altered. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;     GROUP#    THREAD#  SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ----------      4     0          0 YES UNASSIGNED      5     0          0 YES UNASSIGNED      6     0          0 YES UNASSIGNED      7     0          0 YES UNASSIGNED

  5.2备库创建

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> alter database add standby logfile group 4 ('/oradata/shanghai/map/standby01a.log','/oradata/shanghai/map/standby01b.log') size 50M; Database altered. SQL> alter database add standby logfile group 5 ('/oradata/shanghai/map/standby02a.log','/oradata/shanghai/map/standby02b.log') size 50M; Database altered. SQL> alter database add standby logfile group 6 ('/oradata/shanghai/map/standby03a.log','/oradata/shanghai/map/standby03b.log') size 50M; Database altered. SQL> alter database add standby logfile group 7 ('/oradata/shanghai/map/standby04a.log','/oradata/shanghai/map/standby04b.log') size 50M; Database altered. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;     GROUP#    THREAD#  SEQUENCE# ARC STATUS ---------- ---------- ---------- --- ----------      4     0          0 YES UNASSIGNED      5     0          0 YES UNASSIGNED      6     0          0 YES UNASSIGNED      7     0          0 YES UNASSIGNED

6.网络服务名测试

  6.1主库测试

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

[oracle@db1 ~]$ tnsping beijing TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:10 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.241)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = beijing))) OK (0 msec) [oracle@db1 ~]$ tnsping shanghai TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:13 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = shanghai))) OK (0 msec) [oracle@db1 ~]$

  6.2备库测试

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

[oracle@dg ~]$ tnsping beijing TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:49 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.241)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = beijing))) OK (0 msec) [oracle@dg ~]$ tnsping shanghai TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-OCT-2015 17:14:52 Copyright (c) 1997, 2011, Oracle.  All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.242)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = shanghai))) OK (0 msec) [oracle@dg ~]$

测试只是说明网络是通的，最好使用

sqlplus system/oracle@beijing

sqlplus system/oracle@beijing

7.开启实时应用

查看备库状态，以只读方式打开数据库

1 2 3 4 5 6 7 8 9 10

SQL> select status from v$Instance ; STATUS ------------ MOUNTED SQL> SQL> alter database open read only ; Database altered.

启动redo apply，在此过程中注意监控备库日志的变化

1 2 3 4 5 6 7 8

SQL> alter database recover managed standby database using current logfile disconnect from session ; Database altered. [oracle@dg ~]$ cd /oracle/app/oracle/diag/rdbms/shanghai/map/trace/ [oracle@dg trace]$ tail -f alert_map.log Media Recovery Waiting for thread 1 sequence 11 (in transit)

主库切归档，查看备库和alert中变化

1 2 3

SQL> alter system switch logfile ; System altered.

查看备库alert日志

1 2 3 4 5 6 7 8 9 10

Media Recovery Waiting for thread 1 sequence 11 (in transit) Wed Oct 14 17:29:44 2015 Archived Log entry 7 added for thread 1 sequence 11 rlc 893081127 ID 0x5b1ab6a4 dest 2: RFS[2]: Selected log 4 for thread 1 sequence 12 dbid 1528459940 branch 893081127 Wed Oct 14 17:29:44 2015 Media Recovery Log /oradata/shanghai/arch/1_11_893081127.arc Media Recovery Waiting for thread 1 sequence 12 (in transit) Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0   Mem# 0: /oradata/shanghai/map/standby01a.log   Mem# 1: /oradata/shanghai/map/standby01b.log

8.测试

主库创建表

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> create table testdg (num int) ; Table created. SQL> insert into testdg values(1) ; 1 row created. SQL> commit ; Commit complete. SQL>

备库查看

1 2 3 4 5

SQL> select * from testdg ;        NUM ----------      1

主库创建表空间，添加数据文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> create tablespace testdg datafile '/oradata/beijing/map/testdg1.dbf' size 1m ; Tablespace created. SQL> select name from v$datafile ; NAME -------------------------------------------------------------------------------- /oradata/beijing/map/system01.dbf /oradata/beijing/map/sysaux01.dbf /oradata/beijing/map/undotbs01.dbf /oradata/beijing/map/users01.dbf /oradata/beijing/map/testdg1.dbf SQL>

备库查看alert日志，查看是否创建

alert日志

1 2 3 4

Wed Oct 14 17:33:50 2015 Recovery created file /oradata/shanghai/map/testdg1.dbf Successfully added datafile 5 to media recovery Datafile #5: '/oradata/shanghai/map/testdg1.dbf'

1 2 3 4 5 6 7 8 9

SQL> select name from v$datafile ; NAME -------------------------------------------------------------------------------- /oradata/shanghai/map/system01.dbf /oradata/shanghai/map/sysaux01.dbf /oradata/shanghai/map/undotbs01.dbf /oradata/shanghai/map/users01.dbf /oradata/shanghai/map/testdg1.dbf

到这一步，ADG的创建就结束了，后续其他见下篇。。。