ITPub博客

首页 > 数据库 > Oracle > ORACLE 11g dataguard配置练习

ORACLE 11g dataguard配置练习

Oracle 作者:zhangyudong1987 时间:2015-10-22 16:52:57 0 删除 编辑

    两台服务器信息如下

    clip_image001

     

    主数据库 10.1.1.21

    安装数据库软件、创建数据库

     

    standby数据库10.1.1.23

     

     

    准备工作

    1、主数据库启用归档,经检查已经启用

    clip_image002

     

    2、standby数据库上面安装oracle 11g数据库软件,无需创建数据库实例

     

    3、standby上面创建必要的目录,并赋予权限

    [oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/admin/sap/adump

    [oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/oradata/sap/

    [oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/flash_recovery_area/sap/

    [oracle@standby ~]$ mkdir -p /home/oracle/app/oracle/admin/standby/adump

     

     

    4、在备用数据库上面配置启用监听器

    主数据库配置文件如下

     

    [oracle@standby admin]$ cat listener.ora

    # listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = standby)

    (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

    (SID_NAME = standby)

    )

    )

    LISTENER =

    (DESCRIPTION_LIST =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

    )

    )

    ADR_BASE_LISTENER = /home/oracle/app/oracle

     

    备用数据库listener配置如下

    [oracle@standby admin]$ cat listener.ora

    # listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = standby)

    (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)

    (SID_NAME = standby)

    )

    )

    LISTENER =

    (DESCRIPTION_LIST =

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

    )

    )

    ADR_BASE_LISTENER = /home/oracle/app/oracle

     

     

    5、为每个系统配置tnsnames文件

    配置如下

    [oracle@sap admin]$ cat tnsnames.ora

    # tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

    # Generated by Oracle configuration tools.

    SAP =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = sap)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = sap)

    )

    )

    STANDBY =

    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.23)(PORT = 1521))

    )

    (CONNECT_DATA =

    (SERVICE_NAME = standby)

    )

    )

    使用tnsping测试

    在主数据库上

    [oracle@sap admin]$ tnsping sap

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 21:50:22

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

    Used parameter files:

    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sap)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = sap)))

    OK (10 msec)

    [oracle@sap admin]$ tnsping standby

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 21:50:28

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

    Used parameter files:

    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.23)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = standby)))

    OK (10 msec)

     

     

    在standby数据库上面

    [oracle@standby bin]$ tnsping sap

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 22:00:43

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

    Used parameter files:

    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.21)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = sap)))

    OK (10 msec)

    [oracle@standby bin]$ tnsping standby

    TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-SEP-2014 22:00:49

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

    Used parameter files:

    /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = standby)))

    OK (0 msec)

    [oracle@standby bin]$

     

     

    配置standby数据库

    1、创建参数文件

    [oracle@standby dbs]$ echo 'DB_NAME=standby' > ./initstandby.ora

    [oracle@standby dbs]$ cat ./initstandby.ora

    DB_NAME=standby

     

    2、创建密码文件

    将主数据库的密码文件scp到standby数据库响应目录

     

    3、启动standby数据库到nomount

    [oracle@standby dbs]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 12 22:55:13 2014

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

    Connected to an idle instance.

    SQL>

    SQL> startup nomount

    ORACLE instance started.

    Total System Global Area 217157632 bytes

    Fixed Size                 2211928 bytes

    Variable Size                 159387560 bytes

    Database Buffers         50331648 bytes

    Redo Buffers                 5226496 bytes

    SQL>

     

     

    配置primary数据库

     

    1、创建standby logfile

    SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby01.log' size 50m;

    Database altered.

    SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby02.log' size 50m;

    Database altered.

    SQL>

    SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby03.log' size 50m;

    Database altered.

    SQL> alter database add standby logfile '/home/oracle/app/oracle/oradata/sap/standby04.log' size 50m;

    Database altered.

    SQL>

     

    2、rman duplicate 创建备用数据库

    使用rman连接两个数据库

    [oracle@sap admin]$ rman target / auxiliary sys/sys@standby

    Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 12 23:29:40 2014

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

    connected to target database: SAP (DBID=3375191994)

    connected to auxiliary database (not started)

     

    执行duplicate使用以下脚本

    run{

    allocate channel prmy1 type disk;

    allocate auxiliary channel stby1 type disk;

    duplicate target database for standby from active database

    spfile

    parameter_value_convert 'sap','standby'

    set 'db_unique_name'='standby'

    set control_files='/home/oracle/app/oracle/oradata/sap/control.ctl'

    set db_create_online_log_dest_1='/home/oracle/app/oracle/oradata/sap/'

    set db_create_online_log_dest_2='/home/oracle/app/oracle/oradata/sap/'

    set db_recovery_file_dest='/home/oracle/app/oracle/flash_recovery_area/'

    set db_recovery_file_dest_size='10G'

    nofilenamecheck;

    }

    allocated channel: prmy1

    channel prmy1: SID=191 device type=DISK

    allocated channel: stby1

    channel stby1: SID=129 device type=DISK

    Starting Duplicate Db at 13-SEP-14

    contents of Memory Script:

    {

    backup as copy reuse

    targetfile '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwsap' auxiliary format

    '/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstandby' ;

    }

    executing Memory Script

    Starting backup at 13-SEP-14

    Finished backup at 13-SEP-14

    contents of Memory Script:

    {

    backup as copy current controlfile for standby auxiliary format '/home/oracle/app/oracle/oradata/sap/control.ctl';

    }

    executing Memory Script

    Starting backup at 13-SEP-14

    channel prmy1: starting datafile copy

    copying standby control file

    output file name=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_sap.f tag=TAG20140913T030248 RECID=28 STAMP=858135769

    channel prmy1: datafile copy complete, elapsed time: 00:00:03

    Finished backup at 13-SEP-14

    contents of Memory Script:

    {

    sql clone 'alter database mount standby database';

    }

    executing Memory Script

    sql statement: alter database mount standby database

    contents of Memory Script:

    {

    set newname for tempfile 1 to

    "/home/oracle/app/oracle/oradata/sap/temp01.dbf";

    switch clone tempfile all;

    set newname for datafile 1 to

    "/home/oracle/app/oracle/oradata/sap/system01.dbf";

    set newname for datafile 2 to

    "/home/oracle/app/oracle/oradata/sap/sysaux01.dbf";

    set newname for datafile 3 to

    "/home/oracle/app/oracle/oradata/sap/undotbs01.dbf";

    set newname for datafile 4 to

    "/home/oracle/app/oracle/oradata/sap/users01.dbf";

    set newname for datafile 5 to

    "/home/oracle/app/oracle/oradata/sap/example01.dbf";

    set newname for datafile 6 to

    "/home/oracle/app/oracle/oradata/sap/lztest.dbf";

    set newname for datafile 7 to

    "/home/oracle/app/oracle/oradata/sap/trans_test.dbf";

    backup as copy reuse

    datafile 1 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/system01.dbf" datafile

    2 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/sysaux01.dbf" datafile

    3 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/undotbs01.dbf" datafile

    4 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/users01.dbf" datafile

    5 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/example01.dbf" datafile

    6 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/lztest.dbf" datafile

    7 auxiliary format

    "/home/oracle/app/oracle/oradata/sap/trans_test.dbf" ;

    sql 'alter system archive log current';

    }

    executing Memory Script

    executing command: SET NEWNAME

    renamed tempfile 1 to /home/oracle/app/oracle/oradata/sap/temp01.dbf in control file

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    Starting backup at 13-SEP-14

    channel prmy1: starting datafile copy

    input datafile file number=00001 name=/home/oracle/app/oracle/oradata/sap/system01.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/system01.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:45

    channel prmy1: starting datafile copy

    input datafile file number=00002 name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:35

    channel prmy1: starting datafile copy

    input datafile file number=00003 name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:15

    channel prmy1: starting datafile copy

    input datafile file number=00005 name=/home/oracle/app/oracle/oradata/sap/example01.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/example01.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:15

    channel prmy1: starting datafile copy

    input datafile file number=00006 name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:03

    channel prmy1: starting datafile copy

    input datafile file number=00007 name=/home/oracle/app/oracle/oradata/sap/trans_test.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/trans_test.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:03

    channel prmy1: starting datafile copy

    input datafile file number=00004 name=/home/oracle/app/oracle/oradata/sap/users01.dbf

    output file name=/home/oracle/app/oracle/oradata/sap/users01.dbf tag=TAG20140913T030258

    channel prmy1: datafile copy complete, elapsed time: 00:00:01

    Finished backup at 13-SEP-14

    sql statement: alter system archive log current

    contents of Memory Script:

    {

    switch clone datafile all;

    }

    executing Memory Script

    datafile 1 switched to datafile copy

    input datafile copy RECID=28 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/system01.dbf

    datafile 2 switched to datafile copy

    input datafile copy RECID=29 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/sysaux01.dbf

    datafile 3 switched to datafile copy

    input datafile copy RECID=30 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/undotbs01.dbf

    datafile 4 switched to datafile copy

    input datafile copy RECID=31 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/users01.dbf

    datafile 5 switched to datafile copy

    input datafile copy RECID=32 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/example01.dbf

    datafile 6 switched to datafile copy

    input datafile copy RECID=33 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/lztest.dbf

    datafile 7 switched to datafile copy

    input datafile copy RECID=34 STAMP=858135901 file name=/home/oracle/app/oracle/oradata/sap/trans_test.dbf

    Finished Duplicate Db at 13-SEP-14

    released channel: prmy1

    released channel: stby1

    RMAN>

     

     

    3、查看standby数据库当前状态

    SQL> select status from v$instance;

    STATUS

    ------------

    MOUNTED

    SQL> select name from v$datafile;

    NAME

    --------------------------------------------------------------------------------

    /home/oracle/app/oracle/oradata/sap/system01.dbf

    /home/oracle/app/oracle/oradata/sap/sysaux01.dbf

    /home/oracle/app/oracle/oradata/sap/undotbs01.dbf

    /home/oracle/app/oracle/oradata/sap/users01.dbf

    /home/oracle/app/oracle/oradata/sap/example01.dbf

    /home/oracle/app/oracle/oradata/sap/lztest.dbf

    /home/oracle/app/oracle/oradata/sap/trans_test.dbf

    7 rows selected.

     

     

     

    4、查看logfile

    SQL> select type,member from v$logfile;

    TYPE

    -------

    MEMBER

    ----------------------------------------------------------------------------------------------------

    ONLINE

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_3_b185ppnf_.log

    ONLINE

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_2_b185pl8b_.log

    ONLINE

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_1_b185pg4v_.log

    TYPE

    -------

    MEMBER

    ----------------------------------------------------------------------------------------------------

    ONLINE

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_1_b185pg9t_.log

    ONLINE

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_2_b185plcj_.log

    ONLINE

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_3_b185pqhx_.log

    TYPE

    -------

    MEMBER

    ----------------------------------------------------------------------------------------------------

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_4_b185pvwm_.log

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_4_b185pw0n_.log

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_5_b185q0o4_.log

    TYPE

    -------

    MEMBER

    ----------------------------------------------------------------------------------------------------

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_6_b185q4xb_.log

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_7_b185qcyq_.log

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_5_b185q0qr_.log

    TYPE

    -------

    MEMBER

    ----------------------------------------------------------------------------------------------------

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_6_b185q95r_.log

    STANDBY

    /home/oracle/app/oracle/oradata/sap/STANDBY/onlinelog/o1_mf_7_b185qd7s_.log

    14 rows selected.

     

    配置data guard

    1、将备用和主角色添加到standby数据库

    SQL> alter system set fal_server = sap;

    System altered.

    SQL> alter system set fal_client=standby;

    System altered.

    SQL> alter system set log_archive_config='dg_config=(sap,standby)';

    System altered.

    SQL> alter system set standby_file_management=auto;

    System altered.

    SQL> alter system set log_archive_dest_2='service=sap async db_unique_name=sap valid_for=(primary_role,online_logfile)';

    System altered.

     

     

    2、在standby数据库上启动log应用进程

    SQL> alter database recover managed standby database using current logfile disconnect;

    Database altered.

     

     

    3、返回主数据库,配置重做传输

    SQL>

    SQL> alter system set log_archive_config='dg_config=(sap,standby)';

    System altered.

    SQL>

    SQL> alter system set log_archive_dest_2='service=standby async db_unique_name=standby valid_for=(primary_role,online_logfile)';

    System altered.

    SQL> alter system switch logfile;

    System altered.

    SQL> alter system set fal_server = standby;

    System altered.

    SQL> alter system set fal_client=sap;

    System altered.

    SQL> alter system set standby_file_management=auto;

    System altered.

     

    至此配置完成。

     

     

    dataguard启动停止步骤

    停止过程

    1、standby数据库上停止redo日志应用

    alter database recover managed standby database cancel;

    2、关闭之数据库

    3、关闭standby数据库

     

    启动过程

    1、启动standby数据库

    2、启动主数据库

    3、standby数据库上执行redo日志应用

    alter database recover managed standby database using current logfile disconnect from session;

     

     


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21602877/viewspace-1815695/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-02-24

  • 博文量
    13
  • 访问量
    32750