a) standby database creation · a) standby database creation this post explains the method of...

This post covers following tasks: A- Standby Database Creation B- How to Check Whether Phys ical Standby is in Sync with the Primary or Not? C- Difference between failover and switchover

D- Data Guard Broker Configurations E- Quick Switchover & Fa ilover Operations

F- Inconsistent Properties in DGMGRL Configurations G- Recovering a lost Data Guard broker configuration file (ORA-16572) H- ORA-12638: Credential retrieval failed

I- Changing Protection Modes J- J- Onl ine Redo Logs, Archived Redo Logs, and Standby Redo Logs

A) Standby Database Creation This post explains the method of creating a Physical Standby Database using 11g RMAN Duplicate from active database feature which was introduced on 11gR1 onwards . This method is very efficient because we do not need the backup of primary database. Here in my case, I have created the standby on same server as testing standby. I will post the s tandby database on di fferent machine later on. So below are the s teps to create the s tandby database. Primary Database: DB_UNIQUE_NAME: prim Standby Database: DB_UNIQUE_NAME: stand

Fol low the below steps as in order:

Step 1: Enable Force Logging on the Primary database : It i s a best practice to place the primary database in force logging mode so that a l l operation are captured in the redo s tream.

On primary database: C:\>sqlplus sys/xxx@prim as sysdba SQL*Plus : Release 11.2.0.1.0 Production on Thu Jun 23 11:23:37 2011 Copyright (c) 1982, 2010, Oracle. Al l rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> a l ter database force logging; Database altered.

Step 2 : Create the password file for the Standby (for os other than window)

$ orapwd file=$ORACLE_HOME/dbs/orapwstand.ora password=xxxx In case of window create service as C:\>oradim -new -sid stand -intpwd xxxx -s tartmode m Instance created.

Note - Ensure that the same password is used as the one used while creating the password file on the Primary host or we can copy and paste the same password file and futher rename as pwd<s id>.ora in case of window (for example, in this case

pwdstand.ora)

Step 3 : Update network configuration files(tnsnames.ora)

Add the following entries to the tnsnames.ora file .

prim = (DESCRIPTION =

http://www.oraclemasters.in/?p=1255

http://blog.dbvisit.com/difference-between-failover-and-switchover/

http://neeraj-dba.blogspot.in/2011/10/active-standby-database-in-oracle-11g.html

http://neeraj-dba.blogspot.in/2011/10/active-standby-database-in-oracle-11g.html

(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521)) ) (CONNECT_DATA =

(SERVICE_NAME = prim) )

) s tand =

(DESCRIPTION = (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521)) ) (CONNECT_DATA =

(SERVICE_NAME = s tand) ) )

C:\>tnsping s tand TNS Ping Uti lity for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-JUN-2011 12:24:24

Copyright (c) 1997, 2010, Oracle. Al l rights reserved. Used parameter files:

D:\app\Neerajs\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 = xxxx)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = s tand))) OK (30 msec) Step 4 : Add a s tatic entry in the listener.ora file and reload or restart the listener. SID_LIST_LISTENER = (SID_LIST =

(SID_DESC = (GLOBAL_DBNAME = prim) (ORACLE_HOME = D:\app\Neerajs\product\11.2.0\dbhome_1)

(SID_NAME = prim) )

(SID_DESC = (GLOBAL_DBNAME = s tand) (ORACLE_HOME = D:\app\Neerajs\product\11.2.0\dbhome_1) (SID_NAME = s tand) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521)) )

C:\>lsnrctl

LSNRCTL for 32-bi t Windows: Version 11.2.0.1.0 - Production on 23-JUN-2011 12:22:41 Copyright (c) 1991, 2010, Oracle. Al l rights reserved. Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))) The command completed success ful ly LSNRCTL> s tatus

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521))) STATUS of the LISTENER

------------------------ Al ias LISTENER Vers ion TNSLSNR for 32-bi t Windows: Version 11.2.0.1.0 - Production

Start Date 22-JUN-2011 09:56:31 Uptime 1 days 2 hr. 26 min. 15 sec

Trace Level off Securi ty ON: Local OS Authentication SNMP OFF

Lis tener Parameter File D:\app\Neerajs\product\11.2.0\dbhome_1\network\admin\listener.ora Lis tener Log File d:\app\neerajs\product\11.2.0\dbhome_1\log\diag\tnslsnr\xxxx\listener Lis tening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx)(PORT=1521))) Services Summary... Service "prim" has 2 instance(s).

Instance "prim", status UNKNOWN, has 1 handler(s) for this service... Instance "prim", status READY, has 1 handler(s) for this service...

Service "primXDB" has 1 instance(s). Instance "prim", status READY, has 1 handler(s) for this service... Service "stand" has 1 instance(s).

Instance "stands", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully Step 5 : Create a init.ora file on the Standby host with just a single parameter Open notepad and add parameter and save it as s tand.ora DB_NAME=prim Step 6 : Create the required directories on the Standby host

Check the va lue of the parameter used in parameter file of the Primary database. Ensure that this dire ctory s tructure also exists on the Standby host. If the directory s tructure differs in any way on the Primary and Standby server, we will have to ensure that

the RMAN rcv fi le to create the Standby database is amended to reflect this as well.In my case, I have create a folder inside the "oradata" folder as s tand (D:\app\Neerajs \oradata\stand) and pfi le,dpdump,audit resp. ins ide "admin" folder

(D:\app\Neerajs\admin\stand) . Here we have not add the parameter for diverting the logfile and datafile .By default i s taking the "oradata" and "admin" folder. If we want to change the location of datafile or logfile then we change the location by using the below location . db_fi le_name_convert = ('Primary_location_datafi le','Standby_location_fi le') log_fi le_name_convert = ('Primary_location_logfi le','Standby_location_logfi le') We can add this parameter in active_standby.rcv Step 7 : Create the active_standby.rcv fi le DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER

SPFILE SET DB_UNIQUE_NAME='stand'

SET LOG_ARCHIVE_DEST_1=’LOCATION=D:\STAND\’ SET LOG_ARCHIVE_DEST_2='service=prim LGWR SYNC REGISTER VALID_FOR= (online_logfile,primary_role) DB_UNIQUE_NAME=prim '

SET STANDBY_FILE_MANAGEMENT='AUTO'

SET FAL_SERVER='prim' SET FAL_CLIENT='s tand' SET CONTROL_FILES='D:\app\Neerajs\oradata\stand \CONTROL.CTL’ ;

Step 8 : Start the Standby database instance in NOMOUNT state

C:\>sqlplus sys/xxxxx@stand as sysdba SQL*Plus : Release 11.1.0.7.0 - Production on Mon Mar 15 15:40:21 2010 Copyright (c) 1982, 2008, Oracle. Al l rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=’D:\stand.ora’ ORACLE instance s tarted.

Tota l System Global Area 217157632 bytes Fixed Size 2152328 bytes Variable Size 159385720 bytes Database Buffers 50331648 bytes

Redo Buffers 5287936 bytes Step 9 : From the Primary database,run the following RMAN command to create the Standby Database

C:\> rman target sys/xxxx@prim auxiliary sys/xxxx@stand cmdfile=D:\receive.rcv.txt

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 23 13:35:00 2011 Copyright (c) 1982, 2009, Oracle and/or i ts affiliates. Al l rights reserved.

connected to target database: PRIM (DBID=4046782611) connected to auxiliary database: PRIM (not mounted) RMAN> DUPLICATE TARGET DATABASE 2> FOR STANDBY 3> FROM ACTIVE DATABASE 4> DORECOVER 5> SPFILE 6> SET DB_UNIQUE_NAME='stand' 7> SET LOG_ARCHIVE_DEST_1='LOCATION=D:\stand\'

8> SET LOG_ARCHIVE_DEST_2='service=prim LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)' 9> Set STANDBY_FILE_MANAGEMENT='AUTO' 10> SET FAL_SERVER='prim'

11> SET FAL_CLIENT='s tand' 12> SET CONTROL_FILES='D:\app\Neerajs\oradata\stand\CONTROL.CTL'

13> ; Starting Duplicate Db at 23-JUN-11 us ing target database control file instead of recovery catalog a l located channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=10 device type=DISK contents of Memory Script: { backup as copy reuse targetfile 'D:\app\Neerajs\product\11.2.0\dbhome_1\DATABASE\PWDprim.ORA' auxiliary format 'D:\app\Neerajs\product\11.2.0\dbhome_1\DATABASE\PWDstand.ORA' targetfile 'D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEPRIM.ORA' auxiliary format 'D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTAND.ORA' ;

sql clone "alter system set spfile= ''D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTAND.ORA''"; }

executing Memory Script Starting backup at 23-JUN-11 a l located channel: ORA_DISK_1

channel ORA_DISK_1: SID=145 device type=DISK

Finished backup at 23-JUN-11 sql statement: alter system set spfile= ''D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILESTAND.ORA'' contents of Memory Script: {

sql clone "alter system set db_unique_name = ''s tand'' comment=

'''' scope=spfile"; sql clone "alter system set LOG_ARCHIVE_DEST_1 = ''LOCATION=D:\stand\'' comment=

'''' scope=spfile"; sql clone "alter system set LOG_ARCHIVE_DEST_2 =

''service=prim LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=spfile"; sql clone "alter system set STANDBY_FILE_MANAGEMENT =

''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set FAL_SERVER = ''prim'' comment=

'''' scope=spfile"; sql clone "alter system set FAL_CLIENT = ''s tand'' comment=

'''' scope=spfile"; sql clone "alter system set CONTROL_FILES =

''D:\app\Neerajs\oradata\stand\CONTROL.CTL'' comment= '''' scope=spfile"; shutdown clone immediate;

s tartup clone nomount; } executing Memory Script sql statement: alter system set db_unique_name = ''stand'' comment= '''' scope=spfile sql statement: alter system set LOG_ARCHIVE_DEST_1 = ''LOCATION=D:\stand\'' comment= '''' scope=spfile sql statement: alter system set LOG_ARCHIVE_DEST_2 = ''service=prim LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)'' comment= '''' scope=spfile sql statement: alter system set STANDBY_FILE_MANAGEMENT = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set FAL_SERVER = ''prim'' comment= '''' scope=spfile sql statement: alter system set FAL_CLIENT = ''stand'' comment= '''' scope=spfile sql statement: alter system set CONTROL_FILES = ''D:\app\Neerajs\oradata\stand\CONTROL.CTL'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not s tarted) Oracle instance s tarted Tota l System Global Area 318046208 bytes Fixed Size 1374332 bytes Variable Size 213911428 bytes Database Buffers 96468992 bytes Redo Buffers 6291456 bytes contents of Memory Script: { backup as copy current controlfile for standby auxiliary format 'D:\APP\NEERAJS\ORADATA\STAND\CONTROL.CTL'; } executing Memory Script

Starting backup at 23-JUN-11 us ing channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy copying standby control file output file name=D:\APP\NEERAJS\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFPRIM.ORA tag=TAG20110623T133533

RECID=5 STAMP=754580140

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 Finished backup at 23-JUN-11 contents of Memory Script: {

sql clone 'alter database mount standby database'; }

executing Memory Script sql statement: alter database mount standby database RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script: {

set newname for tempfile 1 to "D:\APP\NEERAJS\ORADATA\STAND\TEMP01.DBF"; switch clone tempfile a ll;

set newname for datafile 1 to "D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF"; set newname for datafile 2 to "D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF";

set newname for datafile 3 to "D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF"; set newname for datafile 4 to

"D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF"; set newname for datafile 5 to

"D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF"; backup as copy reuse datafile 1 auxiliary format

"D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF" datafile 2 auxiliary format "D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF" datafile 3 auxiliary format "D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF" datafile 4 auxiliary format "D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF" datafile 5 auxiliary format "D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF" ;

sql 'alter system archive log current'; } executing Memory Script

executing command: SET NEWNAME renamed tempfile 1 to D:\APP\NEERAJS\ORADATA\STAND\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 Starting backup at 23-JUN-11 us ing channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=D:\APP\NEERAJS\ORADATA\PRIM\SYSTEM01.DBF output file name=D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF tag=TAG20110623T133631 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:16 channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=D:\APP\NEERAJS\ORADATA\PRIM\SYSAUX01.DBF output file name=D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=D:\APP\NEERAJS\ORADATA\PRIM\EXAMPLE01.DBF

output file name=D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=D:\APP\NEERAJS\ORADATA\PRIM\UNDOTBS01.DBF output file name=D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF tag=TAG20110623T133631

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=D:\APP\NEERAJS\ORADATA\PRIM\USERS01.DBF output file name=D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF tag=TAG20110623T133631 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 23-JUN-11 sql statement: alter system archive log current

contents of Memory Script: { backup as copy reuse

archivelog l ike "D:\ARCHIVE\ARC0000000010_0754480982.0001" auxiliary format "D:\STAND\ARC0000000010_0754480982.0001" archivelog like "D:\ARCHIVE\ARC0000000011_0754480982.0001" auxi liary format "D:\STAND\ARC0000000011_0754480982.0001" ;

cata log clone archivelog "D:\STAND\ARC0000000010_0754480982.0001"; cata log clone archivelog "D:\STAND\ARC0000000011_0754480982.0001"; switch clone datafile a ll;

} executing Memory Script

Starting backup at 23-JUN-11 us ing channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=10 RECID=8 STAMP=754580181 output file name=D:\STAND\ARC0000000010_0754480982.0001 RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=11 RECID=9 STAMP=754580349 output file name=D:\STAND\ARC0000000011_0754480982.0001 RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 23-JUN-11 cata loged archived log

archived log file name=D:\STAND\ARC0000000010_0754480982.0001 RECID=1 STAMP=754580356 cata loged archived log archived log file name=D:\STAND\ARC0000000011_0754480982.0001 RECID=2 STAMP=754580357

datafile 1 switched to datafile copy input datafile copy RECID=5 STAMP=754580357 fi le name=D:\APP\NEERAJS\ORADATA\STAND\SYSTEM01.DBF

datafile 2 switched to datafile copy input datafile copy RECID=6 STAMP=754580358 fi le name=D:\APP\NEERAJS\ORADATA\STAND\SYSAUX01.DBF datafile 3 switched to datafile copy input datafile copy RECID=7 STAMP=754580358 fi le name=D:\APP\NEERAJS\ORADATA\STAND\UNDOTBS01.DBF datafile 4 switched to datafile copy input datafile copy RECID=8 STAMP=754580358 fi le name=D:\APP\NEERAJS\ORADATA\STAND\USERS01.DBF datafile 5 switched to datafile copy input datafile copy RECID=9 STAMP=754580358 fi le name=D:\APP\NEERAJS\ORADATA\STAND\EXAMPLE01.DBF contents of Memory Script: { set until scn 1062459; recover

s tandby clone database

delete archivelog ; }

executing Memory Script

executing command: SET until clause Starting recover at 23-JUN-11 a l located channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=13 device type=DISK

s tarting media recovery

archived log for thread 1 with sequence 10 i s already on disk as file D:\STAND\ARC0000000010_0754480982.0001 archived log for thread 1 with sequence 11 i s already on disk as file D:\STAND\ARC0000000011_0754480982.0001 archived log file name=D:\STAND\ARC0000000010_0754480982.0001 thread=1 sequence=10

archived log file name=D:\STAND\ARC0000000011_0754480982.0001 thread=1 sequence=11 media recovery complete, elapsed time: 00:00:03

Finished recover at 23-JUN-11 Finished Duplicate Db at 23-JUN-11 Recovery Manager complete.

Step 10 : Change the init.ora parameters related to redo transport and redo apply On primary :

SQL> a l ter system set fal_server='stand' scope=both; System altered.

SQL> a l ter system set fal_client='prim' scope=both;

System altered. SQL> a l ter system set standby_file_management=AUTO scope=both ;

System altered. SQL> a l ter system set LOG_ARCHIVE_DEST_1='LOCATION=D:\archive\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim' ; System a l tered. SQL> a l ter system set LOG_ARCHIVE_DEST_2='SERVICE=stand LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stand' ; System a l tered.

Step 11 : Shutdown the Standby database, add the Standby log fi les and then s tart rea l time recovery

On standby :

SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> s tartup ORACLE instance s tarted. Tota l System Global Area 5344731136 bytes Fixed Size 2153536 bytes Variable Size 3154117568 bytes Database Buffers 2147483648 bytes Redo Buffers 40976384 bytes

Database mounted. Database opened.

SQL> a l ter database add s tandby logfile group 4 s ize 50m ; Database altered.

SQL> a l ter database add s tandby logfile group 5 s ize 50m ; Database altered. SQL> a l ter database add s tandby logfile group 6 s ize 50m ;

Database altered. ele


Now to s tart real time redo data to be applied without waiting for the current standby redo log file to be archived, enable t he rea l -time apply.

SQL> a l ter database recover managed s tandby database using current logfile disconnect ; Media recovery complete.

Step 12 : Change the Protection Level of the Standby Database to MAXIMIZE AVAILABILITY On the Primary database:

SQL> shut immediate; Database dismounted. ORACLE instance shut down.

SQL> s tartup mount

ORACLE instance s tarted. Tota l System Global Area 318046208 bytes

Fixed Size 1374332 bytes Variable Size 251660164 bytes Database Buffers 58720256 bytes

Redo Buffers 6291456 bytes Database mounted. SQL> a l ter database add s tandby logfile group 4 s ize 50m ;

Database altered. SQL> a l ter database add s tandby logfile group 5 s ize 50m ;

Database altered.



The Data Guard "alter database set s tandby to maximize performance" is the default behavior. This command sets the highest level of performance, and protects from failure of any s ingle component. Oracle says that "'a l ter database set s tandby to

maximize performance" is useful for appl ications that can tolerate some data loss . For example, i f the last s tandby is unavailable, processing will continue on the primary instance. When the standby becomes available, re -synchronization is done automatica l ly. The maximize ava i labi l i ty mode protects from fa i lure of any s ingle Data Guard component.

SQL> a l ter database set standby database to maximize availability; Database altered.

SQL> a l ter database open; Database altered.

SQL> select protection_mode,protection_level from v$database; PROTECTION_MODE PROTECTION_LEVEL

----------------------------- --------------------------------- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY Reference: http://neeraj-dba.blogspot.com/2011/06/standby-database-in-oracle-11g.html

B) How to Check Whether Physical Standby is in Sync with the Primary or Not? The following tips will help you in monitoring sync between primary and standby databases. How To Check Whether Phys ica l

Standby is in Sync with the Primary or Not? 1. Check for GAP on s tandby

2. Check redo received on s tandby 3. Check redo applied on standby

Solution Execute following queries: A. On Primary SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Thread Last Sequence Generated ---------- ----------------------- 1 19

2 13 3 11

B. On Physical Standby SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Di fference"

FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH , (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Di fference ---------- ---------------------- --------------------- ---------- 1 19 19 0

2 13 13 0 3 11 11 0

C. On Physical Standby SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Now perform fol lowing checks : 1. Check for GAP (On Standby)

If query “C” returns any row then this means there are some archive logs missing on standby.

Example: SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Thread Low Sequence High Sequence ---------- ------------ -------------

1 8 9

This example shows sequence 8 and 9 from thread 1 are missing on standby. Hence, standby is not in sync with the primary.

If query “C” does not returns any row and output is “no row selected” than this means there i s no archive gap on s tandby.

http://neeraj-dba.blogspot.com/2011/06/standby-database-in-oracle-11g.html


2. Check for redo received on standby Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for a ll threads. If both va lues are same than this means that standby has received the last sequence generated on primary. If both va lues are not same then there are some archives missing on standby. Hence standby is not in sync with the primary.

Example: If “Last Sequence Generated” in query “A” shows value 25 for thread 1 and “Last Sequence Received” in query “B” shows va lue

20 for thread 1 than this means sequence 21 to 25 are missing on s tandby. Hence s tandby is not in sync with the primary. 3. Check for redo applied on standby

If va lue of “Difference” in query “B” i s 0 than this means all the redo received on primary i s applied on s tandby. Hence we can says standby is in sync with primary.

If va lue of “Difference” in query “B” i s not 0 than this means all the redo received on primary is not applied on standby. He nce we can says standby is not in sync with primary

Reference: http://www.oraclemasters.in/?p=1255

C) Difference between Switchover & Failover We often get asked what the difference between switchover and failover i s . The easiest way to understand is that:

Switchover – This is done when both primary and standby databases are available. It is pre-planned. Failover – This is done when the primary database is NO longer available (ie in a Disaster). It is not pre-planned.

A switchover (or graceful switchover) is a planned role reversal between the primary and the standby databases. This i s used when there i s a planned outage on the primary database or primary server and you do not want to have extended downtime on

the primary database. The switchover a llows you to switch the roles of the databases so that the standby databases now becomes a primary databases and a ll your users and applications can continue operations on the “new” primary database (on the s tandby server). During the switchover operation there is a small outage. How long the outage lasts, depends on a number

of factors including the network, the number and s izes of the redo logs. The switchover operation happens on both the primary and s tandby database. A failover operation is what happens when the primary database i s no longer available. The failover operation only happens on

the s tandby database. The failover operation activates the standby database and turns this into a primary database. This process cannot be reversed so the decision to failover should be carefully made. The failover process is initiated during a real disaster or severe outage.

Automatic Failover

Automatic failover i s where the software determines when the standby database should be activated to becom e the new primary database. There are numerous conditions that can occur (ie: network glitches/outages) in any system which theoretically could disrupt communications between the primary and standby sites. Because of the importance of this decision

and the number of variances, we believe i t is best not to automate this process but to leave it in the hands of a human. More information on what happens during a failover: http://blog.dbvisit.com/activating-standby-database-failover/.

D) Data Guard Broker Configuration Configuring Data Guard Broker for Switchover & Failover, General Review The Data Guard broker logically groups these primary and standby databases into a broker configuration that a llows the broker

to manage and monitor them together as an integrated unit. You can manage a broker configuration using either the Oracle Enterprise Manager graphical user interface or the Data Guard command -line interface. The following list describes some of the operations the broker automates and s implifies:

Creating Data Guard configurations that incorporate a primary database, a new or existing (physical, logical, or snapshot) standby database, redo transport services, and log apply services, where any of the databases could be Oracle Real Application Clusters (RAC) databases.

Adding additional new or existing (physical, snapshot, logical, RAC or non -RAC) standby databases to an existing Data

Guard configuration.

Managing an entire Data Guard configuration, including all databases, redo transport services, and log apply services,

through a client connection to any database in the configuration.

Managing the protection mode for the broker configuration.


http://blog.dbvisit.com/activating-standby-database-failover/

Invoking switchover or failover with a single command to initiate and control complex role changes across all

databases in the configuration.

Configuring failover to occur automatically upon loss of the primary database, increasing availability without manual intervention.

Monitoring the status of the entire configuration, capturing diagnostic information, reporting statistics such as the redo apply rate and the redo generation rate, and detecting problems quickly with centralized monitoring, testing,

and performance tools.

Step by Step Implementation Start Data Guard Broker Process On both Primary as well as Standby database s tart the Data Guard Broker process

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH; Static Entries in l istners.ora Edit the listener.ora on both nodes to add a static entry for DGMGRL This is to prevent the ORA-12154 error which can be observed on s tartup of the s tandby database after performing a switchover. Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

SID_LIST_LISTENER = (SID_LIST =

(SID_DESC = (GLOBAL_DBNAME = genoa1_js_dgmgrl) (ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

Create the configuration C:> dgmgrl Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys/xxxx

Connected. DGMGRL> CREATE CONFIGURATION 'CONFIG_NAME'

AS PRIMARY DATABASE IS 'PRIMARY_DB_NAME' CONNECT IDENTIFIER IS 'TNSNAME_ALIAS_PRIMARY';

Configuration "CONFIG_NAME" created with primary database "PRIMARY_DB_NAME"

Add the Standby database to the configuration DGMGRL> ADD DATABASE ' STANDBY_DB_NAME'

AS CONNECT IDENTIFIER IS 'TNSNAME_ALIAS_STANDY '; Database "STANDBY_DB_NAME" added DGMGRL> SHOW CONFIGURATION DGMGRL> ENABLE CONFIGURATION DGMGRL> SHOW CONFIGURATION DGMGRL> show database PRIMARY_DB_NAME DGMGRL> show database STANDBY_DB_NAME

DGMGRL> show database verbose PRIMARY_DB_NAME DGMGRL> show database verbose STANDBY_DB_NAME

Change the properties of a configured database DGMGRL> EDIT DATABASE 'PRIMARY_DB_NAME' SET PROPERTY 'LogXptMode'='SYNC';

Property "LogXptMode" updated Monitoring the configuration: DGMGRL> Help

DGMGRL> show configuration;

DGMGRL> Edit database ‘PRIM’ set property LogXptMode=’SYNC’; DGMGRL> Show database ‘PRIMARY_DB_NAME’ ‘LogXptStatus’; DGMGRL> Edit configuration set protection mode as MaxAvailability; DGMGRL> Enable fast_start failover; DGMGRL> Show database ‘DB_NAME’;

DGMGRL> Enable database ‘DB_NAME’; DGMGRL> Remove configuration; DGMGRL> Show database verbose ‘DB_NAME’; DGMGRL> Show database ‘DB_NAME’ ‘InconsistentProperties’; DGMGRL> Show database ‘DB_NAME’ ‘StatusReport;’

E- Switchover/Failover Procedures Firs t, launch the Data Guard Command-Line Interface and connect to the database. C:> dgmgrl DGMGRL for Linux: Version 11.1.0.6.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys@orcl1 Password: Connected. Then, i ssue switchover or fa ilover command depending on your situation. Generally, a switchover i s used to willingly pass the role of the primary database to the physical standby database (or, one of the physical standby databases), while a failover i s

typica lly done only when a major problem prevents you from normally using the primary database. Below is what you may see when you perform a switch over.

DGMGRL> switchover to ‘ORCL2’

(WARRNING: RECREATE DATA GUARD BROKER CONFIGURATION AFTER THIS OPERATION) . Performing switchover NOW. Please wait... Operation requires shutdown of instance "orclsid" on database "orcl1". Shutting down instance "orclsid"... ORA-01109: database not open Database dismounted.

ORACLE instance shut down. Operation requires shutdown of instance "orclsid" on database "orcl2". Shutting down instance "orclsid"... database not mounted ORACLE instance shut down.

Operation requires startup of instance "orclsid" on database "orcl1". Starting instance "orclsid"...

ORACLE instance s tarted. Database mounted. Operation requires startup of instance "orclsid" on database "orcl2".

Starting instance "orclsid"... ORACLE instance s tarted. Database mounted. Switchover succeeded. New primary i s "orcl2" DGMGRL> fa ilover to orcl2

Performing failover NOW, please wait...

Fa i lover succeeded, new primary i s "orcl2" To confi rm Data Guard switchover or failover has completed successfully, log in to Data Guard Command -Line Interface and

issue the "show configuration" command. DGMGRL> show configuration

Configuration Name: DRSolution Enabled: YES

Protection Mode: MaxAvailability Databases:

orcl2 - Primary database orcl1 - Phys ical s tandby database

Fast-Start Failover: DISABLED

Current s tatus for "DRSolution": SUCCESS The above sample shows what the display would look l ike after a switchover. In the case of a failover, the physical standby

database will show as "disabled" as below. DGMGRL> show configuration Configuration

Name: DRSolution Enabled: YES Protection Mode: MaxAvailability

Databases: orcl2 - Primary database

orcl1 - Phys ical s tandby database (disabled) Fast-Start Failover: DISABLED

Current s tatus for "DRSolution": Warning: ORA-16608: one or more databases have warnings As an additional note, as the former physical s tandby database of orcl2 is now operating as your new primary database, you may wish to use Enterprise Manager to help you manage orcl2. If that is the case, you will need to perform the following to recreate the DBControl repository to enable Enterprise Manager on host2. C:\> emca -config dbcontrol db -repos recreate STARTED EMCA at Sep 29, 2008 2:02:40 PM

EM Configuration Assistant, Version 11.1.0.5.0 Production Copyright (c) 2003, 2005, Oracle. Al l rights reserved.

Enter the following information: Database SID: orclsid

Database Control is already configured for the database orclsid You have chosen to configure Database Control for managing the database orclsid This will remove the existing configuration and the default settings and perform a fresh configuration Do you wish to continue? [yes(Y)/no(N)]: At this point, you should have your former physical standby database orcl2 running fully as your primary database server. After a fa ilover, the physical standby database will not be present in your current setup. In the case that the failed database could be brought back, issue the following command in Data Guard Command-Line Interface to reinstate the failed database as a phys ical standby database. DGMGRL> reinstate database orcl1 Reinstating database "orcl1", please wait...

Operation requires shutdown of instance "orclsid" on database "orcl1" Shutting down instance "orclsid"...

Database closed. Database dismounted. ORACLE instance shut down.

Operation requires startup of instance "orclsid" on database "orcl1"

Starting instance "orclsid"... ORACLE instance s tarted. Database mounted. Continuing to reinstate database "orcl1" ...

Operation requires shutdown of instance "orclsid" on database "orcl1" Shutting down instance "orclsid"...

ORA-01109: database not open Database dismounted.

ORACLE instance shut down. Operation requires startup of instance "orclsid" on database "orcl1"

Starting instance "orclsid"... ORACLE instance s tarted. Database mounted.

Continuing to reinstate database "orcl1" ... Reinstatement of database "orcl1" succeeded Reference: http://dev-notes.com/code.php?q=47

F- Inconsistent Properties If you get few inconsistencies, no issue, correct them with:

DGMGRL> show database 'rac2' InconsistentProperties; INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE rac2 ArchiveLagTarget 0 0 rac2 LogArchiveMaxProcesses 4 4 rac2 LogArchiveMinSucceedDest 1 1 rac2 LogArchiveTrace 0 (missing) 0 rac2 LogArchiveFormat %t_%s_%r.dbf (missing) %t_%s_%r.dbf

DGMGRL> edit database 'rac2' set property 'ArchiveLagTarget'=0; Property "ArchiveLagTarget" updated DGMGRL> edit database 'rac2' set property 'LogArchiveMaxProcesses'=4;

Property "LogArchiveMaxProcesses" updated DGMGRL> edit database 'rac2' set property 'LogArchiveMinSucceedDest'=1;

Property "LogArchiveMinSucceedDest" updated DGMGRL> edit database 'rac2' set property 'LogArchiveTrace'=0; Property "LogArchiveTrace" updated DGMGRL> edit database 'rac2' set property 'LogArchiveFormat'='%t_%s_%r.dbf'; Property "LogArchiveFormat" updated

DGMGRL> show database 'rac2' InconsistentProperties; INCONSISTENT PROPERTIES INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE Reference: http://blog.yannickjaquier.com/oracle/data-guard-broker-configuration-with-failover-and-switchover-testing.html

G- Recovering a lost Data Guard broker configuration file (ORA-16572) While teaching the Oracle11g Release 2 Data Guard course a while back I got a question about how to recover from the loss of a Data Guard broker configuration file. I didn’t know the answer right away so I did a bit of research and this is what I found

out. Bas ically there are 4 di fferent recovery scenarios possible, listed by level of difficulty s tarting with the easiest one:

1. Recovering a lost broker configuration file on a s tandby database while i t is still running

http://dev-notes.com/code.php?q=47

http://blog.yannickjaquier.com/oracle/data-guard-broker-configuration-with-failover-and-switchover-testing.html

2. Recovering a lost broker configuration file on a s tandby database while i t is not running 3. Recovering a lost broker configuration file on the primary database while it i s still running 4. Recovering a lost broker configuration file on the primary database while it i s not running

Before explaining how to recover a lost Data Guard broker configuration file, let’s explore my Data Guard configuration and take a look at the size and location of the actual broker configuration files itself.


Configuration - PeppiEnKokki

Protection Mode: MaxPerformance

Databases:

peppi - Primary database

kokki - Phys ical s tandby database


Configuration Status:

SUCCESS

el4$ l s -1hs $ORACLE_HOME/dbs/dr?peppi.dat

16K /u01/app/oracle/product/11.2.0/dbs/dr1peppi.dat


el5$ l s -1hs $ORACLE_HOME/dbs/dr?kokki.dat

16K /u01/app/oracle/product/11.2.0/dbs/dr1kokki.dat


As shown in the output above, my Data Guard configuration is ca lled “PeppiEnKokki”, named after a famous Dutch children TV

series. The primary database i s named “peppi” and is hosted on a system with a hostname “el4:, and there is one physical s tandby database named “kokki” which is running on a system with the hostname “el5”. Furthermore the output shows that

each database in the Data Guard configuration has two broker configuration f iles which are s tore in their $ORACLE_HOME/dbs directory. The reason behind two configuration files is that one of them stores the current configuration while the other sto res the previous version of the configuration. When the configuration gets updated the new configuration gets written to the

“oldest” configuration file and once this update is complete the broker switches from the current file to the updated file. Now that my Data Guard setup is clear we can proceed with the 4 different recovery scenario’s.

Scenario #1: Recovering a lost broker configuration file on a standby database while it is still running

The fi rst scenario deals with the loss of either one or both broker configuration files on a s tandby database while the s tandby database is still running. Before explaining how to recover this we fi rst need to create the situation where there is actually something to recover ;-) This is easily achieved by brutally deleting both broker configuration files on host “el5”.

el5$ rm /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat


l s : /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat: No such file or directory

The question is: Does the broker realize that its configuration files are missing?




Databases:





SUCCESS

The broker clearly does not recognize the fact that the configuration files are missing. This is more or less true because th e broker keeps the active configuration loaded in memory. However, if the standby database needs to be restarted a problem will show up as we will see in the next scenario. The question for now is: How do we recover the lost broker configuration files? The answer i s we don’t have to because the broker will recover from this error automatically without user intervention! Every time the broker configuration gets updated the new configuration will be written from memory to the inactive configuration fi le on disk. Thus the lost file will be recovered automatically upon the next change in the broker’s configuration.

If we want to trigger this process by hand we can simply re -enable the standby database in question as shown below:

DGMGRL> enable database kokki



We see that one broker configuration file has been re -created because we re-enabled the standby database. By re -enable the s tandby database another time the other broker configuration file will be re -created as well.





Now they are both back and we can proceed to the next scenario.

Scenario #2: Recovering a lost broker configuration file on a standby database while it is not running The second scenario deals with the loss of the active broker configuration file while the standby database is not running. Ag ain we s tart our journey by fi rst creating the actual problem by shutting down the standby database instance followed by removing both broker configuration files as shown below:

SQL> connect sys/oracle@kokki as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

el5$ rm /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat


l s : /u01/app/oracle/product/11.2.0/dbs/dr?kokki.dat: No such file or directory

The fi rst s tep in recovering from this situation is to s tartup the s tandby database instance:

SQL> connect sys/oracle@kokki as sysdba

Connected.

SQL> s tartup mount quiet

ORACLE instance s tarted.

Database mounted.

The s tandby database instance started normally and didn’t complain about the loss of the broker configuration files. Does the broker i tself realize that they are gone?




Databases:



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



ERROR

YES! The broker knows that i ts configuration files are indeed missing and i t starts recovery without user intervention. Behind the scenes it asks the primary database for the current configuration and writes it to disk. If we wait a while we can see that this

i s indeed the case:






Databases:





SUCCESS

As shown above the broker recovered one of the configuration files automatically and i t reports that the overall status of th e configuration is fine. Upon the next change in the configuration the other file will be recovered as well. If we want to trigger this process by hand we can simply re -enable the s tandby database just as we did in scenario #1.





Now that both files are recovered we can proceed to the next scenario.

Scenario #3: Recovering a lost broker configuration file on the primary database while it is still running This scenario deals with the loss of either one or both broker configuration files on the primary database while the primary database is still running. This scenario is pretty much the same as the first scenario. As before we begin setting up the problem

by removing the broker configuration files from disk.

el4$ rm /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat


l s : /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat: No such file or directory

Now that they are gone we will ask the broker for an overview of the configuration in order to determine i f it realizes that i ts

configuration files are missing.




Databases:





SUCCESS

The broker is not aware of the missing files and reports a successful overall status. Just like in the first scenario the broker will re-create the missing configuration files upon a configuration change by wri ting the new configuration to disk thereby in effect performing recovery. Again this recovery can be triggered by re-enable the primary database if we have the desire to do so.

DGMGRL> enable database peppi



As shown above one configuration file i s recovered and if we re-enable the primary database another time the other file will be recovered as well.

DGMGRL> enable database peppi




They are now both recovered and we will move on to our final scenario.

Scenario #4: Recovering a lost broker configuration file on the primary database while it is not running The final scenario deals with the loss of the active broker configuration file on the primary database while the primary database

i s not running. Again we start by knocking down the primary database instance followed by removing the broker configuration fi les from disk.

SQL> connect sys/oracle@peppi as sysdba

Connected.

SQL> shutdown abort

ORACLE instance shut down.

el4$ rm /u01/app/oracle/product/11.2.0/dbs/dr?peppi.dat



Now that we removed the broker configuration files, let’s see what happens i f we startup the primary database instance.

SQL> connect sys/oracle@peppi as sysdba

Connected.

SQL> s tartup quiet

ORACLE instance s tarted.

Database mounted.

Database opened.

The primary database instance starts up normally and doesn’t complain about the loss of the broker configuration files. One might expect that the broker will recover them automatically, just as i t did on the s tandby database, but this is simply not possible on the primary database. Even if we wait a long time, the broker configuration files will not show up automatically.



It i s clear that the broker didn’t recover the lost broker configuration files automatically, and the question arises: Is the broker actually aware that its configuration files are indeed gone? Let’s see what the broker has to say:


ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

Hmmm, the broker reports that there is no configuration at all! Is this really true or is the broker just incapable to distinguish between a non-existing configuration or the lost configuration? Examining the broker’s log file seems like a good thing to do in

order to determine what is really going on here.

$ ta i l $ORACLE_BASE/diag/rdbms/peppi/v1120/trace/drc*.log

………

2010-09-12 19:11:55.034 s tatus = ORA-16572

………

What i s an ORA-16672 ?

$ oerr ora 16572

16572, 00000, "Data Guard configuration file not found"

// *Cause: The Data Guard broker configuration file was either unavailable or

// did not exist.

// *Action: Verify that the configuration file was successfully created.

// If the DG_BROKER_CONFIG_FILE[1|2] initialization parameters were

// changed, ensure the file name on disk and the parameter value

// match, there i s space on the device, and the proper permissions

// are granted. For a RAC database, ensure that these initialization

// parameters are set to file locations that are shared by a ll

// ins tances of the RAC database.

The above makes clear that the broker configuration files are either not there or they can’t be accessed. We a lready know tha t they are gone, because we removed them ourselves, so there i s no need to verify this. The question is of course: How do we

recover the lost broker configuration files? The solution is either recreating the whole broker configuration from scratch or to copy a broker configuration file from one of

i ts s tandby databases to the primary database. I ’ll go for the second solution as shown below. It’s important to perform this action while the broker i tself is not running.

SQL> a l ter system set dg_broker_start=false;

System altered.

el4$ scp -q el5:$ORACLE_HOME/dbs/dr1kokki.dat $ORACLE_HOME/dbs/dr1peppi.dat



SQL> a l ter system set dg_broker_start=true;

System altered.

After this s tep we need to give the broker a bit of time so i t can do whatever it needs to do. After a while we can take a look at the broker configuration files and verify the overall configuration s tatus.







Databases:


kokki - Phys ical s tandby database (disabled)



SUCCESS

The broker reports an overall s tatus of SUCCESS despite the fact that the standby database is disabled. We can easily correct this by re-enabling the standby database in question:





Databases:





SUCCESS

Reference: http://prutser.wordpress.com/2010/10/26/recovering-a-lost-data-guard-broker-configuration-file-ora-16572/

H- ORA-12638: Credential retrieval failed

If the Oracle client was installed using the domain administrator account instead of the local

administrator, the following message could be displayed when trying to connect:

ORA-12638: Credential retrieval failed

From the wording of the error message one could think that this error means that he is using

the wrong password but this i s not the case. To solve this:

1) Go to /oracle home/network/admin and open sqlnet.ora

2) Search for SQLNET.AUTHENTICATION_SERVICES= (NTS) and change it to

SQLNET.AUTHENTICATION_SERVICES= (NONE)

3) Save the file and try to connect, this should work

I- Changing Protection Modes Using the Data Guard Command Line Interface to change Protection Mode is a simple and efficient process. This document will deta il the steps using DGMGL to change to all three Data Guard Protection Modes.

For more information on the Data Guard Protection Modes see the post Changing the Data Guard Protection Modes.

Changing from Maximum Performance to Maximum Availability (Synchronous) Using DGMGRL connect to either the primary or the s tandby database.

1 2 3 4 5 6 7

8 9 10

[oracle@prod ~]$ DGMGRL DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect sys

Password: Connected. DGMGRL>

Once connected through DGMGRL use the SHOW CONFIGURATION command to see the Data Guard configuration along with the current protection mode.

1 2 3

4

5 6 7 8

DGMGRL> show configuration Configuration - proddb

Protection Mode: MaxPerformance Databases: proddb - Primary database s tandby - Phys ical standby database

http://prutser.wordpress.com/2010/10/26/recovering-a-lost-data-guard-broker-configuration-file-ora-16572/

9

10 11 12

13 14

15

Fast-Start Failover: DISABLED Configuration Status:

SUCCESS

DGMGRL>

In order to change from Maximum Performance to Maximum Availability the Log Transport Mode (LogXptMode) must be set to Synchronous (SYNC). Standby redo logs are also required. The EDIT DATABASE SET PROPERTY command is used to chnage the LogXptMode

1 2 3

DGMGRL> Edit database ‘standby’ set property logxptmode=SYNC; Property "logxpt mode updated DGMGRL>

Next we change the protection mode to Maximum Availability. The protection mode is changed using the EDIT

CONFIGURATION SET PROTECTION MODE AS command.

1

2 3

DGMGRL> Edit configuration set protection mode as maxavailability ;

Succeeded. DGMGRL>

You can verify that the protection mode has changed using the SHOW CONFIGURATION command.

1

2 3 4 5 6 7 8 9 10

11 12 13

14 15


Configuration - proddb

Protection Mode: MaxAvailability Databases:

proddb - Primary database s tandby - Phys ical standby database


Configuration Status: SUCCESS DGMGRL>

From Maximum Performance to Maximum Protection (Synchronous) To change from Maximum Performance to Maximum Protection the LogXptMode parameter must be set to SYNC. Use the EDIT

DATABASE SET PROPERTY command to change the LogXptMode.

1 2

DGMGRL> Edit database s tandby set property logxptmode=SYNC; Property "logxptmode" updated

Next we set the protection mode to Maximum Protection. Note: changing to Maximum Protection mode requires the restart of the primary database. Ensure you are logged into DGMGRL with a user that has SYSDBA privileges.

1

2 3

4 5 6

7

8 9 10 11 12

DGMGRL> edit configuration set protection mode as maxprotection;

Operation requires shutdown of instance "proddb" on database "proddb" Shutting down instance "proddb"...

Database closed. Database dismounted. ORACLE instance shut down.

Operation requires startup of instance "proddb" on database "proddb"

Starting instance "proddb"... ORACLE instance s tarted. Database mounted. Database opened. DGMGRL>

You can verify that the protection Mode has changed using the SHOW CONFIGURATION command.

1 2 3

4 5

6 7 8

9 10

11 12 13 14 15

DGMGRL> show configuration; Configuration - proddb

Protection Mode: MaxProtection

Databases: proddb - Primary database s tandby - Phys ical standby database


Configuration Status: SUCCESS DGMGRL>

From Maximum Protection to Maximum Performance (Asynchronous) The LogXptMode setting for Maximum Performance mode is Asynchronous (ASYNC). Use the EDIT DATABASE SET PROPERTY command to change the LogXptMode to ASYNC.

1 2

3 4 5

DGMGRL> Edit database s tandby set property logxptmode=ASYNC; Error: ORA-16805: change of LogXptMode property violates overall protection mode.

Fa i led. DGMGRL>

In the previous examples we were upgrading the protection mode. When upgrading the protection the LogXptMode parameter should be set to the appropriate transport mode prior to changing the protection. In the case of downgrading the protection

mode you change the protection mode first then change transport mode.

1

2 3

DGMGRL> Edit configuration set protection mode as maxperformance;

Succeeded. DGMGRL>

When downgrading from Maximum Performance there is no need to bounce the database. The last thing to do is to the set the LogXptMode to ASYNC.

1 2

3

DGMGRL> Edit database s tandby set property logxptmode=ASYNC; Property "logxptmode" updated

DGMGRL>

You can verify that the protection mode has changed using the SHOW CONFIGURATION command.

1 2 3 4 5 6 7 8 9 10

11 12

13 14 15

DGMGRL> show configuration Configuration - proddb Protection: MaxPerformance Databases: proddb - Primary database s tandby - Phys ical standby database Fast-Start Failover: DISABLED


SUCCESS DGMGRL>

The Data Guard Broker Command Line Interface greatly simplifies the transition from one protection mode to another.

J- Online Redo Logs, Archived Redo Logs, and Standby Redo Logs The most crucial s tructures for Data Guard recovery operations are online redo logs, archived redo logs, and standby redo logs. Redo data transmitted from the primary database is received by the remote file server (RFS) process on the s tandby system where the RFS process wri tes the redo data to archived log files or s tandby redo log files. Redo data can be applied either after the redo is written to the archived redo log file or s tandby redo log file, or, if real-time apply i s enabled, directly from the s tandby redo log file as it is being filled.

This documentation assumes that you a lready understand the concepts behind online redo logs and archived redo logs. Section 2.5.1 supplements the basic concepts by providing information that i s specific to Data Guard configurations. Section 2.5.2

provides detailed information about using s tandby redo log files. See Oracle Database Administrator's Guide for more information about redo logs and archive logs, and Section 6.2.1 for information about real-time apply.

Online Redo Logs and Archived Redo Logs The transmission of redo is integral to maintaining the transactional consistency of the primary and standby databases. Both onl ine redo logs and archived redo logs are required in a Data Guard environment:

Onl ine redo logs Every instance of an Oracle primary database and logical s tandby database has an online redo log to protect the database in case of an instance failure. Physical s tandby databases do not use an online redo log, because physical

s tandby databases are not opened for read/write I/O. Changes are not made to the physical standby database and new redo data is not generated.

Archived redo logs An archived redo log is required because archiving is the method used to keep s tandby databases transactionally cons istent with the primary database. Primary databases, and both physical and logical standby databases all use an

archived redo log. Oracle databases are set up, by default, to run in ARCHIVELOG mode so that the archiver (ARCn) process automatically copies each filled online redo log file to one or more archived redo log files.

Unl ike physical standby databases, logical standby databases are open databases that generate redo data and have multiple log files, including online redo log files, archived redo log files, and s tandby redo log files (if configured).

Both the size of the online redo log files and the frequency with which a log switch occurs can affect the generation of the archived redo log files at the primary s ite. The Oracle Database High Availability Overview provides recommendations for log group s izing. An Oracle database will attempt a checkpoint at each log switch. Therefore, if the size of the online redo log file is too small, frequent log switches lead to frequent checkpointing and negatively affect system performance on the s tandby database.

See Also: Oracle Database Administrator's Guide for more details about configuring redo logs, archive logs, and log groups

Standby Redo Logs A standby redo log i s similar to an online redo log, except that a s tandby redo log is used to store redo data received from

another database. A s tandby redo log i s required i f you want to implement:

The maximum protection and maximum availability levels of data protection (described in Section 1.4 and in more deta il in Section 5.6)

Real-time apply (described in Section 6.2)

Cascaded destinations (described in Appendix E) A s tandby redo log provides a number of advantages:

Standby redo log files can reside on raw devices, which may be important i f either or both the primary and s tandby databases reside in a Real Application Clusters environment.

Standby redo log files can be multiplexed using multiple members, improving reliability over archived log files.

During a failover, Data Guard can recover and apply more redo data from standby redo log files than from the

archived log files alone.

The archiver (ARCn) process or the log writer (LGWR) process on the primary database can transmit redo data directly

to remote s tandby redo log files, potentially eliminating the need to register a partial archived log file (for example, to recover after a standby database crashes). See Chapter 5 for more information.

http://docs.oracle.com/cd/B19306_01/server.102/b14239/standby.htm#i72326



http://docs.oracle.com/cd/B19306_01/server.102/b14231/toc.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1022881



http://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm#i1029121

http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1178539

http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1023371

http://docs.oracle.com/cd/B19306_01/server.102/b14239/cascade_appx.htm#g639625

http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#g1296022

a) standby database creation · a) standby database creation this post explains the method of...

Documents