ITPub博客

首页 > 数据库 > Oracle > oracle 11g dataguard

oracle 11g dataguard

原创 Oracle 作者:zhengbao_jun 时间:2014-03-28 13:44:42 0 删除 编辑

Data Guard Setup

The source database tst that will serve as a primary database is configured as specified in Annex 1. In the section a physical standby database will be created and Data Guard broker will be configured.

  1. Enable force logging.

    SQL> alter database force logging;

  2. Create Standby Redo Logs (SRL). In Annex 2 is the information related to the online redo log (ORL) files in the database. As there are three redo log groups with two members each we will create four members for each thread. Note that OMF (db_create_file_dest=’+DGDUP’) are used and the SRL are created with the same size as ORL.

    alter database add standby logfile thread 1 group 7 size 104857600;

    alter database add standby logfile thread 1 group 8 size 104857600;

    alter database add standby logfile thread 1 group 9 size 104857600;

    alter database add standby logfile thread 1 group 10 size 104857600;

    alter database add standby logfile thread 2 group 11 size 104857600;

    alter database add standby logfile thread 2 group 12 size 104857600;

    alter database add standby logfile thread 2 group 13 size 104857600;

    alter database add standby logfile thread 2 group 14 size 104857600;

  3. Configure redo transport on the primary for each instance to the standby database.

    SQL> alter system set log_archive_config=’dg_config=(tst,tststby)’ sid=’*’ scope=both;

    System altered.

    SQL>

    SQL> alter system set log_archive_dest_2=’service=tststby SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tststby’ sid=’*’ scope=both;

    System altered.

    SQL>

  4. Configure tnsnames alias and a static registration with the GI listeners for both primary and standby database and compliant with Data Guard Broker. With Oracle 11gR2 since the SCAN Listener was introduced the modification is made to both listeners in GI $OH and to the tnsnames.ora in RDBMS $OH. Annex 3 contains the tnsnames.ora and listener.ora. Reload the listeners.

    TSTSTBY

    (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))

    (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SID_NAME = tststby)

    )

    )

    SID_LIST_LISTENER =

    (SID_LIST =

    (SID_DESC =

    (GLOBAL_DBNAME = tst)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

    (SID_NAME = tst)

    )

    (SID_DESC =

    (GLOBAL_DBNAME = tststby)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

    (SID_NAME = tststby)

    )

    (SID_DESC =

    (GLOBAL_DBNAME = tst_DGMGRL)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

    (SID_NAME = tst)

    )

    (SID_DESC =

    (GLOBAL_DBNAME = tststby_DGMGRL)

    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_10)

    (SID_NAME = tststby)

    )

    )

  5. Create a oracle password file for the standby. To avoid error ORA-16191 specify ignorecase=y. The standby will be created as a single instance database and then will be RAC enabled. That is why three password files are created.

    orapwd file=orapwtststby entries=100 password=sys1 ignorecase=y

    orapwd file=orapwtststby1 entries=100 password=sys1 ignorecase=y

    orapwd file=orapwtststby2 entries=100 password=sys1 ignorecase=y

  6. Create a pfile for the standby database based on the pfile from the primary database.as in Annex 4 and set *.cluster_database=FALSE and *.db_name=’tststby’.
  7. Create $ORACLE_BASE/admin/tststby/adump directory on the standby DR site.
  8. Verify that connection to primary and standby can be made using the tnsnames aliases using the Oracle password files.

    sqlplus sys/sys1@tst as sysdba

    sqlplus sys/sys1@tst stbyas sysdba

  9. Prepare RMAN scripts to create the standby and set the parameters for the standby database. Start the instance on the standby DR site using the pfile created in step 6. Invoke RMAN connect to target and auxiliary instances. Make sure to connect to auxiliary using the tnsnsmes alias to avoid errors.Run the script. The output from the script is in Annex 5.

    run {

    allocate channel tst type disk;

    allocate channel tst1 type disk;

    allocate auxiliary channel tststby type disk;

    duplicate target database for standby from active database DORECOVER

    spfile

    parameter_value_convert “tst”,”tststby”

    set db_unique_name=”tststby”

    set db_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”

    set log_file_name_convert=”+DGDUP/tst”,”+DGDUP/tststby”

    set fal_client=”tststby”

    set fal_server=”tst”

    set standby_file_management=”AUTO”

    set log_archive_config=”dg_config=(tst,tststby)”

    set log_archive_dest_2=”service=tst SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=tst”;}

  10. Register the standby database with OCR. Create a shared ASM based spfile.. Make sure that *.cluster_database=TRUE and each instance pfile pints to the ASM based spfile. See Annex 6 for more information.

    [oracle@raclinux2 dbs]$ srvctl add database -d tststby -o /u01/app/oracle/product/11.2.0/db_10 -s mount -r physical_standby -c RAC

    [oracle@raclinux2 dbs]$ srvctl add instance -d tststby -i tststby2 -n raclinux2

    [oracle@raclinux2 dbs]$ srvctl add instance -d tststby -i tststby1 -n raclinux1

    Create a shared spfile on ASM.

    SQL> create spfile=’+DGDUP/spfiletststby.ora’ from memory;

    File created.

    SQL>

    Make sure

    [oracle@raclinux2 dbs]$ cat inittststby2.ora for instance 1 as well

    SPFILE=’+DGDUP/spfiletstsstby.ora’

    [oracle@raclinux2 dbs]$

    [oracle@raclinux2 trace]$ srvctl add service -d tststby -s tststby_s -r tststby2 -a tststby1

    [oracle@raclinux2 dbs]$ srvctl modify database -d tststby -p ’+DGDUP/spfiletststby.ora’

    [oracle@raclinux2 dbs]$ srvctl config database -d tststby

    Database unique name: tststby

    Database name:

    Oracle home: /u01/app/oracle/product/11.2.0/db_10

    Oracle user: oracle

    Spfile: +DGDUP/spfiletststby.ora

    Domain:

    Start options: mount

    Stop options: immediate

    Database role: PRIMARY

    Management policy: AUTOMATIC

    Server pools: tststby

    Database instances: tststby1,tststby2

    Disk Groups: DGDUP

    Mount point paths:

    Services: tststby_s

    Type: RAC

    Database is administrator managed

    [oracle@raclinux2 dbs]$

  11. Make sure that the standby database is operational. Test redo shipping and redo apply. See Annex 7 for details.

    SQL> select PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;

    PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE

    ——————– ——————– ——————– —————-

    MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED PHYSICAL STANDBY

    SQL>

  12. Set Data Guard Broker. Execute the following SQL on both primary and standby database.

    SQL> alter system set dg_broker_start=true scope=both sid=’*';

  13. Create Data Guard Configuration. From dgmgrl execute the following commands.

DGMGRL> connect /

Connected.

DGMGRL> create configuration “DataGuard” as primary database is “tst” connect identifier is tst;

Configuration “DataGuard” created with primary database “tst”

DGMGRL>

DGMGRL> add database “tststby” as connect identifier is tststby;

Database “tststby” added

DGMGRL>

DGMGRL> enable configuration;

Enabled.

DGMGRL>

DGMGRL> show configuration;

Configuration – DataGuard

Protection Mode: MaxPerformance

Databases:

tst – Primary database

Warning: ORA-16532: Data Guard broker configuration does not exist

tststby – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

WARNING

DGMGRL>

See the resolution of the error in point 1 in Error and Fixes. After locating the files to ASM shared among the RAC instances we have,

DGMGRL> show configuration;

Configuration – DataGuard

Protection Mode: MaxPerformance

Databases:

tst – Primary database

tststby – Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

DGMGRL> show configuration verbose;

Configuration – DataGuard

Protection Mode: MaxPerformance

Databases:

tst – Primary database

tststby – Physical standby database (disabled)

Properties:

FastStartFailoverThreshold = ’30′

OperationTimeout = ’30′

FastStartFailoverLagLimit = ’30′

CommunicationTimeout = ’180′

FastStartFailoverAutoReinstate = ‘TRUE’

FastStartFailoverPmyShutdown = ‘TRUE’

BystandersFollowRoleChange = ‘ALL’

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

DGMGRL> show database “tst”;

Database – tst

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

tst1

tst2

Database Status:

SUCCESS

DGMGRL> show database “tststby”;

Database – tststby

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: (unknown)

Apply Lag: (unknown)

Real Time Query: OFF

Instance(s):

tststby2

Database Status:

DISABLED

DGMGRL>

  1. Turn Flashback Database feature on both Primary and Standby database.

    Issue on the primary

    SQL> alter database flashback on;

    Database altered.

    SQL>

    Issue on the Standby

    DGMGRL> edit database “tststby” set state=’apply-off’;

    Succeeded.

    DGMGRL>

    SQL> alter database flashback on;

    Database altered.

    SQL>

    DGMGRL> edit database “tststby” set state=’apply-on’;

    Succeeded.

    DGMGRL>

  2. Set Fast Start failover.

    DGMGRL> show fast_start failover;

    Fast-Start Failover: DISABLED

    Threshold: 30 seconds

    Target: (none)

    Observer: (none)

    Lag Limit: 30 seconds

    Shutdown Primary: TRUE

    Auto-reinstate: TRUE

    Configurable Failover Conditions

    Health Conditions:

    Corrupted Controlfile YES

    Corrupted Dictionary YES

    Inaccessible Logfile NO

    Stuck Archiver NO

    Datafile Offline YES

    Oracle Error Conditions:

    (none)

    DGMGRL> enable fast_start failover;

    Enabled.

    DGMGRL> show fast_start failover;

    Fast-Start Failover: ENABLED

    Threshold: 30 seconds

    Target: tststby

    Observer: (none)

    Lag Limit: 30 seconds

    Shutdown Primary: TRUE

    Auto-reinstate: TRUE

    Configurable Failover Conditions

    Health Conditions:

    Corrupted Controlfile YES

    Corrupted Dictionary YES

    Inaccessible Logfile NO

    Stuck Archiver NO

    Datafile Offline YES

    Oracle Error Conditions:

    (none)

    DGMGRL>

Solution Verification

In order to make sure that DR Data Guard implementation works I will implement a switchover, failover, reinstate a former primary as a standby after failover and some troubleshooting..

  1. Switchover

    DGMGRL> switchover to “tststby”;

    Performing switchover NOW, please wait…

    New primary database “tststby” is opening…

    Operation requires shutdown of instance “tst1″ on database “tst”

    Shutting down instance “tst1″…

    ORA-01031: insufficient privileges

    Warning: You are no longer connected to ORACLE.

    Please complete the following steps to finish switchover:

    shut down instance “tst1″ of database “tst”

    start up and mount instance “tst1″ of database “tst”

    DGMGRL>

    From sqlplus perform the requested activities.

    DGMGRL> show configuration;

    Configuration – DataGuard

    Protection Mode: MaxPerformance

    Databases:

    tststby – Primary database

    tst – (*) Physical standby database

    Fast-Start Failover: ENABLED

    Configuration Status:

    SUCCESS

    DGMGRL>

  2. Changing a protection Mode. Depending on how Redo transport is set the protection mode can be easily changed as shown below.

    DGMGRL> edit configuration set PROTECTION MODE AS MaxAvailability;

    Succeeded.

    DGMGRL> show configuration;

    Configuration – DataGuard

    Protection Mode: MaxAvailability

    Databases:

    tst – Primary database

    tststby – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:

    SUCCESS

    DGMGRL> edit configuration set PROTECTION MODE AS MaxProtection;

    Succeeded.

    DGMGRL> show configuration;

    Configuration – DataGuard

    Protection Mode: MaxProtection

    Databases:

    tst – Primary database

    tststby – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:

    SUCCESS

    DGMGRL>

    SQL> select NAME, DATABASE_ROLE, DATAGUARD_BROKER, PROTECTION_MODE,PROTECTION_LEVEL from v$database;

    NAME DATABASE_ROLE DATAGUAR PROTECTION_MODE PROTECTION_LEVEL

    ——— —————- ——– ——————– ——————–

    TST PRIMARY ENABLED MAXIMUM PROTECTION MAXIMUM PROTECTION

    SQL>

  3. Starting with Oracle 11.2.0.2 a SQL can be executed from dgmgrl utility. See an example of executing SQL.

    [oracle@raclinux1 ~]$ dgmgrl

    DGMGRL for Linux: Version 11.2.0.2.0 – 64bit Production

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

    Welcome to DGMGRL, type “help” for information.

    DGMGRL> connect /

    Connected.

    DGMGRL> sql “alter system archive log current”;

    Succeeded.

    DGMGRL> sql “alter system archive log current”;

    Succeeded.

    DGMGRL> sql “alter system archive log current”;

    Succeeded.

    DGMGRL>

  4. Failover. Connect to the standby database to perform the failover.

    DGMGRL> connect sys/sys1@tststby

    Connected.

    DGMGRL> failover to “tststby”;

    Performing failover NOW, please wait…

    Failover succeeded, new primary is “tststby”

    DGMGRL>

  1. Reinstate. the former primary database.

    DGMGRL> connect sys/sys1@tststby

    Connected.

    DGMGRL> failover to “tststby”;

    Performing failover NOW, please wait…

    Failover succeeded, new primary is “tststby”

    DGMGRL> reinstate database “tst”;

    Reinstating database “tst”, please wait…

    Operation requires shutdown of instance “tst1″ on database “tst”

    Shutting down instance “tst1″…

    ORA-03113: end-of-file on communication channel

    Please complete the following steps and reissue the REINSTATE command:

    shut down instance “tst1″ of database “tst”

    start up and mount instance “tst1″ of database “tst”

    DGMGRL>

    DGMGRL> connect /

    Connected.

    DGMGRL> show configuration;

    Configuration – DataGuard

    Protection Mode: MaxPerformance

    Databases:

    tststby – Primary database

    tst – Physical standby database (disabled)

    ORA-16661: the standby database needs to be reinstated

    Fast-Start Failover: DISABLED

    Configuration Status:

    SUCCESS

    DGMGRL> reinstate database “tst”;

    Reinstating database “tst”, please wait…

    Reinstatement of database “tst” succeeded

    DGMGRL>

    DGMGRL> show configuration;

    Configuration – DataGuard

    Protection Mode: MaxPerformance

    Databases:

    tst – Primary database

    tststby – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:

    SUCCESS

    DGMGRL>

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

上一篇: oracle 备用恢复
下一篇: dataguard 11g
请登录后发表评论 登录
全部评论

注册时间:2008-08-08

  • 博文量
    209
  • 访问量
    869811