【DG】之创建Physical Standby Database




  • 1 环境说明
    两台数据库,分别为Beijing和shanghai,配置为Physical Standby Database。并进行主备切换测试。

    2 设置Primary Database
    2.1 启用Forced Logging

    SYS@BEIJING> col FORCE_LOGGING for a15
    SYS@BEIJING> select FORCE_LOGGING from v$database;
    FORCE_LOGGING
    ---------------
    NO

    启用Forced Logging

    SYS@BEIJING> alter database force logging;
    Database altered.

    2.2 检查密码文件

    [oracle@secdb1 dbs]$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/
    hc_BEIJING.dat  init.ora      lkBEIJING     spfileBEIJING.ora
    initdw.ora   initBEIJING.ora  orapwBEIJING  sqlnet.log

    保证所有节点的sys密码必须一致
    2.3  配置Standby Redo Log
    查看现有日日志组

    SYS@BEIJING> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER            IS_
    ---------- ------- ------- -------------------------------------
    1 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo01.log NO
    2 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo02.log NO
    3 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo03.log NO
    
    3 rows selected.

    增加standby redo log日志组

    SYS@BEIJING> alter database add standby logfile group 4
    ('/u01/app/oracle/oradata/BEIJING/disk1/stredo04_01.log',
    '/u01/app/oracle/oradata/BEIJING/disk2/stredo04_02.log') size 100m;
    Database altered.

    2.4  配置参数文件
    生成pfile

    SYS@BEIJING> create pfile from spfile;
    File created.

    在参数文件中增加如下部分

    DB_UNIQUE_NAME=BEIJING
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'
    CONTROL_FILES='/u01/app/oracle/oradata/BEIJING/disk1/control01.ctl',
    '/u01/app/oracle/oradata/BEIJING/disk2/control02.ctl',
    '/u01/app/oracle/oradata/BEIJING/disk3/control03.ctl'
    LOG_ARCHIVE_DEST_1=
    'LOCATION=/u01/app/oracle/arch/BEIJING/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=BEIJING'
    LOG_ARCHIVE_DEST_2=
    'SERVICE=SHANGHAI LGWR 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=30a
    FAL_SERVER=SHANGHAI
    FAL_CLIENT=BEIJING
    DB_FILE_NAME_CONVERT='SHANGHAI','BEIJING'
    LOG_FILE_NAME_CONVERT=
    '/u01/app/oracle/arch/SHANGHAI/','/u01/app/oracle/arch/BEIJING/'
    STANDBY_FILE_MANAGEMENT=AUTO

    关闭数据库以创建spfile

    SYS@BEIJING> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SYS@BEIJING> create spfile from pfile;
    
    File created.

    2.5  启用归档

    SYS@BEIJING> startup mount
    ORACLE instance started.
    Total System Global Area  314572800 bytes
    Fixed Size                  1219184 bytes
    Variable Size              75498896 bytes
    Database Buffers          234881024 bytes
    Redo Buffers                2973696 bytes
    Database mounted.
    
    SYS@BEIJING> alter database archivelog;
    
    Database altered.
    
    SYS@BEIJING> alter database open;
    
    Database altered.
    
    SYS@BEIJING> archive log list;
    
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/arch/BEIJING/
    Oldest online log sequence     2
    Next log sequence to archive   5
    Current log sequence           5

    3 设置Standby Database
    3.1 创建Primary Database的数据文件备份
    备份中不要包括日志文件

    SYS@BEIJING> shutdown immediate
    
    Database closed.
    Database dismounted.
    ORACLE instance shut down.

    备份数据文件:

    [oracle@secdb1 oradata]$ tar -zcvf BEIJING.tar *

    3.2 创建Standby Database的控制文件

    SYS@BEIJING> startup mount;
    
    ORACLE instance started.
    Total System Global Area  314572800 bytes
    Fixed Size                  1219184 bytes
    Variable Size              75498896 bytes
    Database Buffers          234881024 bytes
    Redo Buffers                2973696 bytes
    Database mounted.
    
    SYS@BEIJING> alter database create standby controlfile as '/home/oracle/control01.ctl';
    
    Database altered.
    
    SYS@BEIJING> alter database open;
    
    Database altered.

    3.3 创建Standby Database的参数文件

    SYS@BEIJING> create pfile from spfile;
    
    File created.

    编辑参数文件

    DB_NAME=BEIJING
    DB_UNIQUE_NAME=SHANGHAI
    LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)'
    CONTROL_FILES='/u01/app/oracle/oradata/SHANGHAI/disk1/control01.ctl','/u01/app/oracle/oradata/SHANGHAI/disk2/control02.ctl','/u01/app/oracle/oradata/SHANGHAI/disk3/control03.ctl'
    LOG_ARCHIVE_DEST_1=
    'LOCATION=/u01/app/oracle/arch/SHANGHAI/
    VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
    DB_UNIQUE_NAME=SHANGHAI'
    LOG_ARCHIVE_DEST_2=
    'SERVICE=BEIJING LGWR 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=30a
    FAL_SERVER=SHANGHAI
    FAL_CLIENT=BEIJING
    DB_FILE_NAME_CONVERT='BEIJING','SHANGHAI'
    LOG_FILE_NAME_CONVERT=
    '/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/disk1/',
    '/u01/app/oracle/oradata/BEIJING/disk2/','/u01/app/oracle/oradata/SHANGHAI/disk2/',
    '/u01/app/oracle/oradata/BEIJING/disk3/','/u01/app/oracle/oradata/SHANGHAI/disk3/'
    STANDBY_FILE_MANAGEMENT=AUTO
    user_dump_dest='/u01/app/oracle/SHANGHAI/udump'
    core_dump_dest='/u01/app/oracle/SHANGHAI/cdump'
    background_dump_dest='/u01/app/oracle/SHANGHAI/bdump'
    audit_file_dest='/u01/app/oracle/SHANGHAI/adump'

    3.4 拷贝数据库文件,控制文件,参数文件到Standby Database

    [oracle@secdb1 ~]$ scp initBEIJING.ora BEIJING.tar tnsnames.ora listener.ora 
    control01.ctl oracle@secdb2:/home/oracle
    
    The authenticity of host 'secdb2 (192.168.80.102)' can't be established.
    RSA key fingerprint is 90:77:13:80:91:dd:8c:42:c0:24:ee:f7:06:36:1c:7f.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added 'secdb2,192.168.80.102' (RSA) to the list of known hosts.
    oracle@secdb2's password:
    initBEIJING.ora                                        100% 1765     1.7KB/s   00:00
    BEIJING.tar                                            100%  156MB   4.2MB/s   00:37
    tnsnames.ora                                        100%  593     0.6KB/s   00:00
    listener.ora                                        100%  569     0.6KB/s   00:00
    control01.ctl                                       100% 6896KB   3.4MB/s   00:02
    
    [oracle@secdb2 oradata]$ tar -zxvf BEIJING.tar
    
    [oracle@secdb2 disk2]$ mv ../disk1/control01.ctl .
    
    [oracle@secdb2 ~]$ mv initBEIJING.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/dbs/initSHANGHAI.ora

    要保证Primary Database的目录和Standby Database的目录一致,Standby Database中不存在的目录要提前创建,包括oradata,admin,flash_recovery_area,arch
    3.5 创建密码文件

    [oracle@secdb2 dbs]$ orapwd file=orapwSHANGHAI password=oracle entries=30

    保证密码和主库一致
    3.6 编辑监听文件

    [oracle@secdb2 ~]$ mv listener.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/network/admin
    [oracle@secdb2 ~]$ mv tnsnames.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/network/admin
    [oracle@secdb2 ~]$ cd /u01/app/oracle/BEIJINGuct/10.2.0/db_1/network/admin/
    [oracle@secdb2 admin]$ vi listener.ora
    LISTENER=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521))
          (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
    SID_LIST_LISTENER=
      (SID_LIST=
        (SID_DESC=
          (GLOBAL_DBNAME=SHANGHAI)
          (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
          (SID_NAME=SHANGHAI))
        (SID_DESC=
          (SID_NAME=plsextproc)
          (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
          (PROGRAM=extproc)))
    
    [oracle@secdb2 admin]$ vi tnsnames.ora
    BEIJING=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
        (CONNECT_DATA=
          (SERVER=dedicated)
          (SERVICE_NAME=BEIJING)))
    SHANGHAI=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))
        (CONNECT_DATA=
          (SERVER=dedicated)
          (SERVICE_NAME=SHANGHAI)))
    BEIJING_S=
      (DESCRIPTION=
        (ADDRESS_LIST=
          (ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1526)))
        (CONNECT_DATA=
          (SERVER=shared)
          (SERVICE_NAME=BEIJING)))

    3.7 启动监听器

    [oracle@secdb2 admin]$ lsnrctl start
    LSNRCTL for Linux: Version 10.2.0.1.0 - production on 19-JAN-2013 21:40:16
    Copyright (c) 1991, 2005, Oracle.  All rights reserved.
    Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
    TNSLSNR for Linux: Version 10.2.0.1.0 - production
    System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521)))
    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))
    
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - production
    Start Date                19-JAN-2013 21:40:17
    Uptime                    0 days 0 hr. 0 min. 1 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
    
    Services Summary...
    Service "SHANGHAI" has 1 instance(s).
    Instance "SHANGHAI", status UNKNOWN, has 1 handler(s) for this service...
    Service "plsextproc" has 1 instance(s).
    Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
    The command completed successfully

    3.8 启动Standby Database
    3.8.1 启动Standby Database到mount阶段

    [oracle@secdb2 admin]$ sqlplus sys/oracle@SHANGHAI as sysdba
    SQL*Plus: Release 10.2.0.1.0 - production on Sat Jan 19 21:58:27 2013
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    Connected to an idle instance.
    
    SQL> create spfile from pfile;
    
    File created.
    
    SQL> startup mount;
    
    ORACLE instance started.
    Total System Global Area  314572800 bytes
    Fixed Size                  1219184 bytes
    Variable Size              96470416 bytes
    Database Buffers          213909504 bytes
    Redo Buffers                2973696 bytes
    
    Database mounted.

    3.8.2 启动Redo Apply

    SQL> alter database recover managed standby database disconnect from session;
    
    Database altered.
    
    SQL> select DATABASE_ROLE from v$database;
    
    DATABASE_ROLE
    ----------------
    PHYSICAL STANDBY

    3.8.3 测试Standby Database的归档操作
    在主库中切换日志组

    SYS@BEIJING> alter system switch logfile;
    
    System altered.

    在备库中查看归档日志是否正常

    SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
    
    SEQUENCE# FIRST_TIM NEXT_TIME
    ---------- --------- ---------
    4 17-JAN-13 19-JAN-13
    5 19-JAN-13 19-JAN-13
    6 19-JAN-13 19-JAN-13
    7 19-JAN-13 19-JAN-13
    8 19-JAN-13 19-JAN-13
    9 19-JAN-13 19-JAN-13
    10 19-JAN-13 19-JAN-13
    11 19-JAN-13 19-JAN-13
    
    8 rows selected.

    在备库中查看归档日志是否以应用

    SQL> select sequence#,applied from v$archived_log order by sequence#;
    
    SEQUENCE# APP
    ---------- ---
    4 NO
    5 YES
    6 YES
    7 YES
    8 YES
    9 YES
    10 YES
    11 YES
    
    8 rows selected.

    4 主备机切换
    4.1 切换原主库为备库

    SYS@BEIJING> alter database commit to switchover to physical standby;
    
    Database altered.

    4.2 检查原备库状态

    SQL> select switchover_status from v$database;
    
    SWITCHOVER_STATUS
    --------------------
    TO PRIMARY

    4.3 切换原备库为主库

    SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
    
    Database altered.
    
    SQL> alter database open;
    
    Database altered.

    4.4 检查新备库中的归档日志应用

    SYS@BEIJING> select sequence#,applied from v$archived_log order by sequence#;
    
    SEQUENCE# APP
    ---------- ---
    4 NO
    4 NO
    5 NO
    5 NO
    6 NO
    6 NO
    7 NO
    7 NO
    8 NO
    8 NO
    9 NO
    9 NO
    10 NO
    10 NO
    11 YES
    11 NO
    12 NO
    12 NO
    13 YES
    13 YES
    14 YES
    14 NO
    15 NO
    16 NO
    17 NO
    
    25 rows selected.
    
    SYS@BEIJING>

    发现归档日志没有应用
    4.5 启动Redo Apply

    SYS@BEIJING> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
    
    Database altered.

    4.6 再次检查归档日志是否应用
    可以看到redo已经应用

    SYS@BEIJING> select sequence#,applied from v$archived_log order by sequence#;
    
    SEQUENCE# APP
    ---------- ---
    4 NO
    4 YES
    5 YES
    5 NO
    6 YES
    6 NO
    7 YES
    7 NO
    8 NO
    8 YES
    9 NO
    9 YES
    10 NO
    10 YES
    11 YES
    11 YES
    12 YES
    12 NO
    13 YES
    13 YES
    14 YES
    14 NO
    15 YES
    16 YES
    17 YES
    18 YES
    19 YES
    20 YES
    21 YES
    22 YES
    23 YES
    24 YES

    Speak Your Mind

    *