oracle 11g dataguard

zhengbao_jun發表於2014-03-28

原文網址 : http://blog.itpub.net/235507/viewspace-1131495/

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.

Enable force logging.

SQL> alter database force logging;
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;
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>
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)

)

)
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
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’.
Create $ORACLE_BASE/admin/tststby/adump directory on the standby DR site.
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
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”;}
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]$
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>
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=’*';
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>

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>
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..

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>
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>
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>
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>

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之11g DataGuard
2019-12-31
Oracle
Oracle 11g dataguard 配置簡約步驟
2018-06-27
Oracle
ORACLE 11G DATAGUARD 日誌中斷處理方案
2021-09-09
Oracle
【DG】DataGuard健康檢查 for 11g
2020-07-17
【DATAGUARD】Oracle Dataguard nologging 塊修復
2022-08-14
Oracle
Oracle 11g RAC DG備庫gv$dataguard_stats apply lag值較大
2022-10-19
OracleAPP
oracle dataguard broker 配置
2021-06-30
Oracle
Oracle 11.2.0.4 physical dataguard和snapshot dataguard切換
2018-03-14
Oracle
【DATAGUARD】Oracle Dataguard體系架構詳解
2021-08-12
Oracle架構
【DATAGUARD】Oracle19c dataguard新特性及部署
2020-03-14
Oracle
Oracle dataguard failover 實戰
2018-04-03
OracleAI
Oracle 單機配置DataGuard
2021-05-20
Oracle
oracle 19c dataguard silent install (oracle 19c dataguard 靜默安裝)
2020-12-26
Oracle
Oracle 11.2 DataGuard RAC To RAC搭建
2021-04-29
Oracle
【DATAGUARD】Oracle Dataguard物理備庫切換最佳實踐(sqlplus)
2021-09-26
OracleSQL
【Dataguard】Oracle多租戶環境對Dataguard的影響
2021-04-30
Oracle
關於11G DataGuard 日誌傳輸的案例
2019-04-10
oracle11g dataguard切換
2018-08-01
Oracle
Oracle Dataguard故障轉移(failover)操作
2020-07-28
OracleAI
7 Oracle DataGuard 命令列參考
2020-03-29
Oracle命令列
Oracle DataGuard 主備切換 (switchover) oracle11g
2021-08-31
Oracle
【DATAGUARD】Oracle19c Data Guard Broker
2020-03-30
Oracle
Oracle 11.2.0.4 Dataguard兩則故障處理
2021-03-12
Oracle
【DATAGUARD】Oracle21c Dataguard建立注意事項及主要引數介紹
2021-10-21
Oracle
Oracle 11G 安裝文件
2018-07-17
Oracle
benchmark 壓測Oracle 11g
2023-02-22
Oracle
sysbench壓測Oracle 11g
2023-02-16
Oracle
Oracle dataguard報錯：Error 1017 received logging on to the standby
2018-06-27
OracleError
Oracle資料庫（DataGuard）遷移方案(上)
2023-04-19
Oracle資料庫
Oracle資料庫（DataGuard）遷移方案（中）
2023-04-19
Oracle資料庫
Oracle資料庫（DataGuard）遷移方案（下）
2023-04-19
Oracle資料庫
【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料
2021-07-22
Oracle
Oracle 11G 安裝 bbed 工具
2019-01-14
Oracle
oracle 11g data guard維護
2020-05-30
Oracle
oracle 11g OEM在哪裡找到？？？
2020-11-17
Oracle
oracle 11g 常用命令
2020-04-05
Oracle
Oracle 11g RAC Silent Install For NFS
2022-03-04
OracleNFS
Oracle 11g刪除庫重建
2022-10-31
Oracle
Oracle 11G 修改scan_ip
2022-12-02
Oracle

oracle 11g dataguard

相關文章