[Data Guard实验]一步一步创建Oracle单机物理Standby

DG作用: 容灾, 快速恢复, 减少MTTR.

软件环境: Windows 2003 SP2 + Oracle 10g R2

相关说明:
ORACLE_BASE=D:\oracle\product\10.2.0
ORACLE_HOME=D:\oracle\product\10.2.0\db_1
Primary DB <=> chicago
Standby DB <=> boston

详细步骤:

1. 首先通过DBCA创建chicago, DBCA创库过程这里就不多讲了, 自行google吧.

2. chicago创建完成后, 先让它开启强制归档:

duzz$sys@chicago>ALTER DATABASE FORCE LOGGING;

3. 检查有没有口令文件: D:\oracle\product\10.2.0\db_1\database\PWDchicago.ora, 没有则通过下面的命令创建一个:

linux下口令文件的路径和文件名和windows略有不同: $ORACLE_HOME\dbs\orapwSID

orapwd file=<fname> password=<password> entries=<users> force=<y/n>

4. 为chicago创建Standby日志组, 这样做是为了chicago以后能够快速从Primary切换到Standby做准备:

为了不与现有的联机重做日志冲突, 先找出最大的group号:

duzz$sys@chicago>select group#,bytes/1024/1024 mb from v$log;

    GROUP#         MB
---------- ----------
         1         50
         2         50
         3         50

查下路径, 等下Standby日志也放在这里:

duzz$sys@chicago>select member from v$logfile;

MEMBER
------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\REDO01.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\REDO02.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\REDO03.LOG

在chicago端创建Standby日志组, Oracle建议Standby日志组至少要比联机日志组多一个, 这里我们创建4个:

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO04.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO05.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO06.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO07.LOG') SIZE 50M;

(* 删除Standby日志组也很简单, 可以采用这条命令: ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;)
看看刚建的Standby日志组, 还都是UNASSIGNE, 这是应为chicago是Primary, 只有当切换成Standby角色时, Standby日志组才起作用:

duzz$sys@chicago>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARCHIVED  STATUS
---------- ---------- ---------- --------- ------------------------------
         4          0          0 YES       UNASSIGNED
         5          0          0 YES       UNASSIGNED
         6          0          0 YES       UNASSIGNED
         7          0          0 YES       UNASSIGNED

5. 接下来开始修改chicago的参数文件, 使其符合Primary角色:

首先, 我们先得到它的文本类型的参数文件:

duzz$sys@chicago>create pfile f<span style="color:#000000;">ro</span>m spfile;

File created.

在这个位置(D:\oracle\product\10.2.0\db_1\database\INITchicago.ORA)可以找到刚生成的文本参数文件, 做如下修改:

(#开头的为注释，实际使用时需删掉)

chicago.__db_cache_size=134217728
chicago.__java_pool_size=4194304
chicago.__large_pool_size=4194304
chicago.__shared_pool_size=58720256
chicago.__streams_pool_size=0
*.audit_file_dest='d:\oracle\product\10.2.0\admin\chicago\adump'
*.background_dump_dest='d:\oracle\product\10.2.0\admin\chicago\bdump'
*.compatible='10.2.0.1.0'
*.control_files='d:\oracle\product\10.2.0\oradata\chicago\control01.ctl','d:\oracle\product\10.2.0\oradata\chicago\control02.ctl','d:\oracle\product\10.2.0\oradata\chicago\control03.ctl'
*.core_dump_dest='d:\oracle\product\10.2.0\admin\chicago\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='chicago'
*.db_recovery_file_dest='d:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.log_archive_format='ARC%S_%R.%T'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\product\10.2.0\admin\chicago\udump'

<span style="color:#ff0000;">#以下参数为配置物理standby而额外追加的参数
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='D:\oracle\product\10.2.0\oradata\chicago\CONTROL01.CTL',
'D:\oracle\product\10.2.0\oradata\chicago\CONTROL02.CTL',
'D:\oracle\product\10.2.0\oradata\chicago\CONTROL03.CTL'
#归档日志的本地存储位置
LOG_ARCHIVE_DEST_1=
'LOCATION=D:\oracle\product\10.2.0\oradata\chicago\LOG_ARCHIVE_DEST_1\
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
#归档日志传送往standby的位置
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=boston'
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
#以下为chicago转换为Standby角色时所需要的初始化参数
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\boston','D:\oracle\product\10.2.0\oradata\chicago','D:\oracle\product\10.2.0\oradata\chicago','D:\oracle\product\10.2.0\oradata\boston'
#联机重做日志，先primary再standby，由于chicago在这里是standby角色所以放在后面
LOG_FILE_NAME_CONVERT=
'D:\oracle\product\10.2.0\oradata\boston','D:\oracle\product\10.2.0\oradata\chicago','D:\oracle\product\10.2.0\oradata\chicago','D:\oracle\product\10.2.0\oradata\boston'
STANDBY_FILE_MANAGEMENT=AUTO
</span>

6. 复制刚创建好的primary参数文件，按如下修改后创建standby的参数文件，红色字体表示与primary不同的地方:

(注意创建参数文件里所需的文件路径)

D:\oracle\product\10.2.0\db_1\database\INITboston.ORA

chicago.__db_cache_size=134217728
chicago.__java_pool_size=4194304
chicago.__large_pool_size=4194304
chicago.__shared_pool_size=58720256
chicago.__streams_pool_size=0
*.audit_file_dest='d:\oracle\product\10.2.0\admin\<span style="color:#ff0000;">boston</span>\adump'
*.background_dump_dest='d:\oracle\product\10.2.0\admin\<span style="color:#ff0000;">boston</span>\bdump'
*.compatible='10.2.0.1.0'
*.control_files='d:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\control01.ctl','d:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\control02.ctl','d:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\control03.ctl'
*.core_dump_dest='d:\oracle\product\10.2.0\admin\<span style="color:#ff0000;">boston</span>\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='chicago'
*.db_recovery_file_dest='d:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.log_archive_format='ARC%S_%R.%T'
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\product\10.2.0\admin\<span style="color:#ff0000;">boston</span>\udump'


DB_NAME=chicago
DB_UNIQUE_NAME=<span style="color:#ff0000;">boston</span>
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\CONTROL01.CTL',
'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\CONTROL02.CTL',
'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\CONTROL03.CTL'

LOG_ARCHIVE_DEST_1=
'LOCATION=D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston</span>\LOG_ARCHIVE_DEST_1\
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=<span style="color:#ff0000;">boston'</span>

LOG_ARCHIVE_DEST_2=
 'SERVICE=<span style="color:#ff0000;">chicago </span>LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=<span style="color:#ff0000;">chicago'</span>
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=<span style="color:#ff0000;">chicago</span>
FAL_CLIENT=<span style="color:#ff0000;">boston</span>
DB_FILE_NAME_CONVERT='D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">chicago'</span>,'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston'</span>,'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston'</span>,'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">chicago'</span>

LOG_FILE_NAME_CONVERT=
 'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">chicago'</span>,'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston'</span>,'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">boston'</span>,'D:\oracle\product\10.2.0\oradata\<span style="color:#ff0000;">chicago'</span>
STANDBY_FILE_MANAGEMENT=AUTO

7. 以primary的文本参数文件启动chicago，然后保存为服务器参数文件:

duzz$sys@chicago>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
duzz$sys@chicago>startup pfile='D:\oracle\product\10.2.0\db_1\database\INITchicago.ORA'
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1248116 bytes
Variable Size              67110028 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
duzz$sys@chicago>create spfile from pfile;

File created.

duzz$sys@chicago>

检查修改后的参数是否生效:

(db_file_name_convert和log_file_name_convert参数仅在处于standby角色时起作用, 配置格式: primary路径,standby路径,standby路径,primary路径)

duzz$sys@chicago>show parameter convert

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_file_name_convert                 string                            D:\oracle\product\10.2.0\orada
                                                                       ta\boston, D:\oracle\product\1
                                                                       0.2.0\oradata\chicago, D:\orac
                                                                       le\product\10.2.0\oradata\chic
                                                                       ago, D:\oracle\product\10.2.0\
                                                                       oradata\boston
log_file_name_convert                string                            D:\oracle\product\10.2.0\orada
                                                                       ta\boston, D:\oracle\product\1
                                                                       0.2.0\oradata\chicago, D:\orac
                                                                       le\product\10.2.0\oradata\chic
                                                                       ago, D:\oracle\product\10.2.0\

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
                                                                       oradata\boston
duzz$sys@chicago>
duzz$sys@chicago>show parameter log_archive_dest_1

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_1                   string                            LOCATION=D:\oracle\product\10.
                                                                       2.0\oradata\chicago\LOG_ARCHIV
                                                                       E_DEST_1  VALID_FOR=(ALL_LOGFI
                                                                       LES,ALL_ROLES)
                                                                       DB_UNIQUE_NAME=chicago
log_archive_dest_10                  string
duzz$sys@chicago>show parameter log_archive_dest_2

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
log_archive_dest_2                   string                            SERVICE=boston LGWR ASYNC
                                                                         VALID_FOR=(ONLINE_LOGFILES,P
                                                                       RIMARY_ROLE)
                                                                         DB_UNIQUE_NAME=boston
duzz$sys@chicago>

8. 检查primary是否处于归档模式, 没有归档的话需要启用归档模式, 如何启用归档模式自行google一下:

duzz$sys@chicago>archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\oracle\product\10.2.0\oradata\chicago\LOG_ARCHIVE_DEST_1
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence           12
duzz$sys@chicago>

9. 接下来备份chicago到boston, 方法很多, 这里我将采用冷备份:

关闭chicago，然后复制其数据文件和重做日志文件到boston:

duzz$sys@chicago>select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\USERS01.DBF

duzz$sys@chicago>select member from v$logfile;

MEMBER
------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\REDO01.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\REDO02.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\REDO03.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO04.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO05.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO06.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\CHICAGO\SREDO07.LOG

7 rows selected.

duzz$sys@chicago>

复制完成后目录(D:\oracle\product\10.2.0\oradata\boston)应有如下文件:

D:\oracle\product\10.2.0\oradata\boston>dir *.dbf *.log
 驱动器 D 中的卷是 soft
 卷的序列号是 244C-CD9B

 D:\oracle\product\10.2.0\oradata\boston 的目录

2011-11-17  01:37       125,837,312 SYSAUX01.DBF
2011-11-17  01:37       314,580,992 SYSTEM01.DBF
2011-11-17  01:37       209,723,392 UNDOTBS01.DBF
2011-11-17  01:37         5,251,072 USERS01.DBF

 D:\oracle\product\10.2.0\oradata\boston 的目录

2011-11-17  01:07        52,429,312 REDO01.LOG
2011-11-17  01:07        52,429,312 REDO02.LOG
2011-11-17  01:07        52,429,312 REDO03.LOG
2011-11-17  01:28        52,429,312 SREDO04.LOG
2011-11-17  01:27        52,429,312 SREDO05.LOG
2011-11-17  01:21        52,429,312 SREDO06.LOG
2011-11-17  01:21        52,429,312 SREDO07.LOG
              11 个文件  1,022,397,952 字节
               0 个目录  9,114,771,456 可用字节

D:\oracle\product\10.2.0\oradata\boston>

10. 复制完数据文件后，先不要打开chicago, 而是将其启动到mount状态，准备standby需要的控制文件:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\10.2.0\oradata\boston\control01.ctl';

创出一个控制文件后，将该文件拷贝成三分，再重命名为control01.ctl、control02.ctl、control03.ctl。

不要采用类似如下命令直接创建3个控制文件, 会导致3个文件的版本不一致，到时启动不了standby数据库:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\10.2.0\oradata\boston\control01.ctl';
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\10.2.0\oradata\boston\control02.ctl';
ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\product\10.2.0\oradata\boston\control03.ctl';

11. 创建standby服务（仅Windows）：

C:\>oradim -NEW -SID boston -INTPWD oracle -STARTMODE manual
Instance created.

12. 创建standby的口令文件：

C:\>orapwd file=D:\oracle\product\10.2.0\db_1\database\PWDboston.ora password=oracle entries=10

C:\>

13. 为primary和standby创建监听和服务名, 具体怎么操作google吧, 建议用netca和netmgr：

建好后文件D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora应包含如下内容:

BOSTON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = duzz)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = boston)
    )
  )

CHICAGO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = duzz)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = chicago)
    )

测试服务是否连通:

C:\Documents and Settings\Administrator>tnsping chicago

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-NOV-2011 21:36:56

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
d:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = duzz)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = chicago)))
OK (20 msec)

C:\Documents and Settings\Administrator>tnsping boston

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 16-NOV-2011 21:36:58

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
d:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = duzz)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = boston)))
OK (30 msec)

C:\Documents and Settings\Administrator>

14. 好了boston的准备工作已经做的差不多了, 接下来启动standby到mount状态:

C:\>set oracle_sid=boston

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 16 21:34:58 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\INITboston.ORA';

File created.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1248116 bytes
Variable Size              67110028 bytes
Database Buffers          134217728 bytes
Redo Buffers                7139328 bytes
Database mounted.

15. 应用重做日志, DISCONNECT FROM SESSION表示将这个操作切换到后台:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL>

16. 创建boston的standby日志组:

SQL> select max(group#) from v$log;

MAX(GROUP#)
-----------
          3
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\boston\SREDO04.LOG') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\boston\SREDO04.LOG') SIZE 50M
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

报错了, google后发现是因为在备库添加standby redo log需要先停MRP(受管恢复操作):

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\boston\SREDO04.LOG') SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\boston\SREDO05.LOG') SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\boston\SREDO06.LOG') SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('D:\ORACLE\PRODUCT\10.2.0\ORADATA\boston\SREDO07.LOG') SIZE 50M;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARCHIVED  STATUS
---------- ---------- ---------- --------- ------------------------------
         4          0          0 YES       UNASSIGNED
         5          0          0 YES       UNASSIGNED
         6          0          0 YES       UNASSIGNED
         7          0          0 YES       UNASSIGNED

SQL>

在boston端添加完standby 联机日志后,在chicago端切换下日志,就可以boston端查到standby日志了:

chicago端切换日志:

duzz$sys@chicago>alter system switch logfile;

System altered.

duzz$sys@chicago>

boston端验证:

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD#  SEQUENCE# ARCHIVED  STATUS
---------- ---------- ---------- --------- ------------------------------
         4          1         20 YES       ACTIVE
         5          0          0 YES       UNASSIGNED
         6          0          0 YES       UNASSIGNED
         7          0          0 YES       UNASSIGNED

SQL>

17. 再验证boston端的归档日志自动传输和应用的情况:

先检查下boston端的归档日志情况:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME      NEXT_TIME       APPLIED
---------- --------------- --------------- ---------
         9 16-NOV-11       16-NOV-11       NO
        10 16-NOV-11       16-NOV-11       NO
        11 16-NOV-11       16-NOV-11       YES
        12 16-NOV-11       16-NOV-11       YES
        13 16-NOV-11       16-NOV-11       YES
        14 16-NOV-11       16-NOV-11       YES
        15 16-NOV-11       16-NOV-11       YES
        16 16-NOV-11       17-NOV-11       NO

8 rows selected.

SQL>

然后在chicago端做切换日志操作:

duzz$sys@chicago>select sequence#,archived,status from v$log;

 SEQUENCE# ARCHIVED  STATUS
---------- --------- ------------------------------------------------
        16 YES       INACTIVE
        17 NO        CURRENT
        15 YES       INACTIVE

duzz$sys@chicago>alter system switch logfile;

System altered.

duzz$sys@chicago>alter system switch logfile;

System altered.

duzz$sys@chicago>select sequence#,archived,status from v$log;

 SEQUENCE# ARCHIVED  STATUS
---------- --------- ------------------------------------------------
        19 NO        CURRENT
        17 YES       ACTIVE
        18 YES       ACTIVE

duzz$sys@chicago>

最后boston端检查是否自动传输和应用成功:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME      NEXT_TIME       APPLIED
---------- --------------- --------------- ---------
         9 16-NOV-11       16-NOV-11       NO
        10 16-NOV-11       16-NOV-11       NO
        11 16-NOV-11       16-NOV-11       YES
        12 16-NOV-11       16-NOV-11       YES
        13 16-NOV-11       16-NOV-11       YES
        14 16-NOV-11       16-NOV-11       YES
        15 16-NOV-11       16-NOV-11       YES
        16 16-NOV-11       17-NOV-11       NO
        17 17-NOV-11       17-NOV-11       NO
        18 17-NOV-11       17-NOV-11       NO

10 rows selected.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,applied FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME      NEXT_TIME       APPLIED
---------- --------------- --------------- ---------
         9 16-NOV-11       16-NOV-11       YES
        10 16-NOV-11       16-NOV-11       YES
        11 16-NOV-11       16-NOV-11       YES
        12 16-NOV-11       16-NOV-11       YES
        13 16-NOV-11       16-NOV-11       YES
        14 16-NOV-11       16-NOV-11       YES
        15 16-NOV-11       16-NOV-11       YES
        16 16-NOV-11       17-NOV-11       YES
        17 17-NOV-11       17-NOV-11       YES
        18 17-NOV-11       17-NOV-11       YES

10 rows selected.

SQL>

(第一次查询发现归档日志传输ok了, 但是没有自动应用, 于是我这里又让手动让它应用了一下, 其实是由于延迟的关系, 不用手动也可以自动应用的)

Ref:

Creating a Physical Standby Database

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#SBYDB00200

Managing a Physical Standby Database

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm#SBYDB00700

posted @ 2011-11-17 21:42 生活不是用来挥霍的阅读(281) 评论(0) 编辑收藏举报

生活不是用来挥霍的

[Data Guard实验]一步一步创建Oracle单机物理Standby

公告