Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for the ‘Data Guard and DR’ Category

REINSTATE STANDBY – DATAGUARD BROKER – ISSUES

Posted by Hendry chinnapparaj on March 1, 2011

REINSTATE STANDBY  – DATAGUARD BROKER – ISSUES

Problem:-

The Primary database / network is not available, so you did a Successful failover to Standby using Dataguard Broker – DR Site – is the PRIMARY NOW.

Then you try to reinstate the old primary as standby when you encounter errors

Errors

Dataguard error

ORA-16795: database resource guard detects that database re-creation is required

Primary alert log error

ORA-16009: remote archive log destination must be a STANDBY database

Tue Mar  1 11:38:11 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

LGWR: Failed to archive log 3 thread 1 sequence 9 (16009)

Standby alert log error

ORA-16009: remote archive log destination must be a STANDBY database

For failover:

DGMGRL> failover to zoeprd_js;

Performing failover NOW, please wait…

Failover succeeded, new primary is “zoeprd_js”

DGMGRL> failover to ‘ZOEPRD_js’;

Performing failover NOW, please wait…

Failover succeeded, new primary is “ZOEPRD_js”

DGMGRL> show configuration;

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database (disabled)

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

SUCCESS

Then you try to reinstate original primary as standby

In primary server – after backup alive

DGMGRL> connect sys/*****

Connected.

DGMGRL> reinstate database ‘ZOEPRD_fc’

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> show configuration

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> enable configuration

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

Warning: ORA-16607: one or more databases have failed

New Primary Error in Alert log

Tue Mar  1 11:38:11 2011

LGWR: Error 16009 disconnecting from destination LOG_ARCHIVE_DEST_2                                  standby host ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOS                                 T=prdu009n2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ZOEPRD_fc_XPT)(                                 INSTANCE_NAME=zoeprd)(SERVER=dedicated)))’

LGWR: Error 16009 creating archivelog file ‘(DESCRIPTION=(ADDRESS_LI                                 ST=(ADDRESS=(PROTOCOL=tcp)(HOST=prdu009n2)(PORT=1521)))(CONNECT_DATA                                 =(SERVICE_NAME=ZOEPRD_fc_XPT)(INSTANCE_NAME=zoeprd)(SERVER=dedicated                                 )))’

Tue Mar  1 11:38:11 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_lgwr_3670184.tr                                 c:

ORA-16009: remote archive log destination must be a STANDBY database

Tue Mar  1 11:38:11 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

LGWR: Failed to archive log 3 thread 1 sequence 9 (16009)

New Standby  Error in Alert log

Redo Shipping Client Connected as PUBLIC

— Connected User is Valid

RFS[11]: Assigned to RFS process 9895980

RFS[11]: Database mount ID mismatch [0xa0162732:0xa015e92e]

RFS[11]: Not using real application clusters

Tue Mar  1 11:43:15 2011

Errors in file /u01/oracle/admin/zoeprd/udump/zoeprd_rfs_9895980.trc:

ORA-16009: remote archive log destination must be a STANDBY database

WORKAROUND Solution:-

Pre-requisite:- Flashback has to be ON for Primary & DR for this Solution to Work

Disable the Broker

DGMGRL> disable configuration

1.1.1.1        Steps as per oracle10g doc Step 4.2.10 – Establish Original Primary as Standby Using Flashback (Optional)

Once access to the failed production site is restored and if you had flashback database enabled, you can reinstate the original primary database as a physical standby of the new primary database.

On the standby site, note the SCN when the database became primary:

select to_char(standby_became_primary_scn) from v$database;

On the original production site, flashback and start managed recovery:

shutdown immediate;
startup mount;
flashback database to scn <standby_became_primary_scn>;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect;

NEW PRIMARY SERVER

SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

—————————————-

29609886505

ORIGINAL PRIMARY –

shutdown immediate;

startup mount;

flashback database to scn 29609886505;

alter database convert to physical standby;

shutdown immediate;

startup mount;

NEW PRIMARY SERVER

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             NO

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

DISABLED

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

SUCCESS

SQL> select protection_mode from v$database;

PROTECTION_MODE

——————–

MAXIMUM PERFORMANCE

zoeprd:/u01/oracle> dgrmgrl sys/*****

ksh: dgrmgrl:  not found

zoeprd:/u01/oracle> dgmgrl sys/****

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

Check in primary & dr

SQL> select protection_mode from v$database;

PROTECTION_MODE

——————–

MAXIMUM AVAILABILITY

Advertisements

Posted in Data Guard and DR | Leave a Comment »

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Posted by Hendry chinnapparaj on February 21, 2011

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Problem:-

The Dataguard / standby database has these errors when the MRP0 – Recover session was initiated.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session

Sun Feb 20 14:04:25 2011

Media Recovery Log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

Errors with log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

MRP0: Background Media Recovery terminated with error 328

Sun Feb 20 14:04:25 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Recovery interrupted!

Sun Feb 20 14:04:26 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Sun Feb 20 14:04:26 2011

MRP0: Background Media Recovery process shutdown (zoeprd)

Cause:-

The issue can be due to many reasons realting to archived logs and SCN having wrong meta entry / data.

Standby Oracle / control file is looking for a specific Archivelog / specific SCN which it thinks is required for Recovery which  is not available in the archived log. Somewhere in the log shipping / transfer

things got distorted inside the log causing issues / .

Even if you copy the logs from primary and register / replace the on standby and apply them – still the standby controlfile cannot recover them

Even if you recreate standby controlfile and recover, still comes up with same errors

So what do you do finally ? try this one as it worked fo rme

Fix:-

12.7 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.

Note:

The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolveable archive gap.

1.     On the standby database, stop the managed recovery process (MRP):

2.         SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.     On the standby database, find the SCN which will be used for the incremental backup at the primary database:

4.         SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> column current_scn format 999999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

     CURRENT_SCN
----------------
     29615710642

5.     In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:

6.         RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>
7.         DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

Note:

RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:

o        The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database

o        The backup is not cataloged at the source database

o        The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.

o        You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database, you must catalog it at the standby as described in Oracle Database Backup and Recovery Advanced User’s Guide. Backups on tape cannot be cataloged.

Rman>

Run

{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT  ‘/u03/oradata/backup/ForStandby_%U’;

      BACKUP INCREMENTAL FROM SCN 29615710642 DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

}

input datafile fno=00646 name=/u04/oradata/zoeprd/CB151_01.DBF

input datafile fno=00657 name=/u02/oradata/zoeprd/CBIDX156_01.DBF

input datafile fno=00668 name=/u02/oradata/zoeprd/CSH067_01.DBF

channel disk1: starting piece 1 at 20-02-2011 12:44:34

channel disk1: finished piece 1 at 20-02-2011 12:48:59

piece handle=/u03/oradata/backup/ForStandby_58m5501i_1_1 tag=FORSTANDBY comment=NONE

channel disk1: backup set complete, elapsed time: 00:04:25

Finished backup at 20-02-2011 12:48:59

Starting Control File and SPFILE Autobackup at 20-02-2011 12:49:00

piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743604540_1FJwU-jRQ_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 20-02-2011 12:49:02

released channel: disk1

8.     Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:

9.         SCP /tmp/ForStandby_* standby:/tmp
 
ftp drpu009n1
/u03/oradata/backup /u03/oradata/backup

10. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:

11.    RMAN> CATALOG START WITH '/tmp/ForStandby';

RMAN> catalog start with '/u03/oradata/backup';

searching for all files that match the pattern /u03/oradata/backup

List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

12. Recover the standby database with the cataloged incremental backup:

13.    RMAN> RECOVER DATABASE NOREDO;

Generic Note

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

Use RMAN> catalog backup piece and try the recovery. And when you do the recovery allocate the disk in RMAN as you have copied / ftp’ed these files to the disk

You might encounter these errors because the primary and standby archived logGs location are different. Either create the same directories as in primary or catalog these files

destination for restore of datafile 00647: /u02/oradata/zoeprd/CBIDX151_01.DBF
destination for restore of datafile 00658: /u04/oradata/zoeprd/CB157_01.DBF
channel ORA_DISK_2: reading from backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19870: error reading backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19505: failed to identify file "/u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp"
ORA-17503: ksfdopn:4 Failed to open file /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bk
failover to copy on device type SBT_TAPE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:20:20
ORA-19870: error reading backup piece 3pm53j82_1_2
ORA-19507: failed to retrieve sequential file, handle="3pm53j82_1_2", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   ANS1302E (RC2)    No objects on server match query

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 20-02-2011 13:25:01

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 269 not processed because file is read-only
datafile 270 not processed because file is read-only
datafile 271 not processed because file is read-only
…
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:26:29
ORA-27191: sbtinfo2 returned error
Additional information: 2

The above error shows – recover is not done and skips the read only datafiles as the SCN are still the same

14. In RMAN, connect to the primary database and create a standby control file backup:

15.    RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Run
{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u03/oradata/backup/sttbyctl.bck’;

backup current controlfile for standby format '/u03/oradata/backup/stbyctl.bck';
}

allocated channel: disk1
channel disk1: sid=634 devtype=DISK

Starting backup at 20-02-2011 13:42:19
channel disk1: starting compressed full datafile backupset
channel disk1: specifying datafile(s) in backupset
including standby control file in backupset
channel disk1: starting piece 1 at 20-02-2011 13:42:20
channel disk1: finished piece 1 at 20-02-2011 13:42:23
piece handle=/u03/oradata/backup/stbyctl.bck tag=TAG20110220T134219 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-02-2011 13:42:23

Starting Control File and SPFILE Autobackup at 20-02-2011 13:42:24
piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743607761_1FJzUyGbV_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-02-2011 13:42:43
released channel: disk1

16. Copy the standby control file backup to the standby system. For example:

17.    SCP /tmp/ForStandbyCTRL.bck standby:/tmp

:: No such file or directory
ftp> lcd /u03/oradata/backup
Local directory now /u03/oradata/backup
ftp> cd /u03/oradata/backup
250 CWD command successful.
ftp> binary
200 Type set to I.
ftp> put stbyctl.bck
200 PORT command successful.
150 Opening data connection for stbyctl.bck.
226 Transfer complete.
3260416 bytes sent in 0.144 seconds (2.211e+04 Kbytes/s)
local: stbyctl.bck remote: stbyctl.bck

18. Shut down the standby database and startup nomount:

19.    RMAN> SHUTDOWN;
20.    RMAN> STARTUP NOMOUNT;

21. In RMAN, connect to standby database and restore the standby control file:

22.    RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

connected to target database: zoeprd (not mounted)
connected to recovery catalog database

RMAN> run
2> {
3> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u03/oradata/backup/sttbyctl.bck';
4> RESTORE STANDBY CONTROLFILE FROM '/u03/oradata/backup/stbyctl.bck';
5> }

allocated channel: disk1
channel disk1: sid=816 devtype=DISK

Starting restore at 20-02-2011 13:48:13

channel disk1: restoring control file
channel disk1: restore complete, elapsed time: 00:00:04
output filename=/u02/oradata/zoeprd/control01.ctl
output filename=/u03/oradata/zoeprd/control02.ctl
output filename=/u04/oradata/zoeprd/control03.ctl
Finished restore at 20-02-2011 13:48:22
released channel: disk1

23. Shut down the standby database and startup mount:

24.    RMAN> SHUTDOWN;
25.    RMAN> STARTUP MOUNT;

26. If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

27.    RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/'; 
28.    RMAN> SWITCH DATABASE TO COPY;

29. If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.

30. On the standby database, clear all standby redo log groups (there may be more than 3):

31.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
32.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
33.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> column member format a50
SQL> l
  1* select * from v$logfile
SQL> /

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         9         STANDBY /u03/oradata/zoeprd/stdby_redo_09.log              NO
        10         STANDBY /u03/oradata/zoeprd/stdby_redo_10.log              NO
        11         STANDBY /u03/oradata/zoeprd/stdby_redo_11.log              NO
         4         ONLINE  /u02/oradata/zoeprd/redo_04a.log                   NO
         4         ONLINE  /u03/oradata/zoeprd/redo_04b.log                   NO
         5         ONLINE  /u02/oradata/zoeprd/redo_05a.log                   NO
         5         ONLINE  /u03/oradata/zoeprd/redo_05b.log                   NO
        12         STANDBY /u03/oradata/zoeprd/stdby_redo_12.log              NO
        13         STANDBY /u03/oradata/zoeprd/stdby_redo_13.log              NO
         6         ONLINE  /u02/oradata/zoeprd/redo_06a.log                   NO
         6         ONLINE  /u03/oradata/zoeprd/redo_06b.log                   NO
         7         ONLINE  /u02/oradata/zoeprd/redo_07a.log                   NO
         7         ONLINE  /u03/oradata/zoeprd/redo_07b.log                   NO

13 rows selected.

SQL> alter database clear logfile group 9;

Database altered.

SQL> alter database clear logfile group 10;

Database altered.

SQL> alter database clear logfile group 11;

Database altered.

SQL> alter database clear logfile group 12;

Database altered.

SQL> alter database clear logfile group 13;

Database altered.

34. On the standby database, restart Flashback Database:

35.    SQL> ALTER DATABASE FLASHBACK OFF; 
36.    SQL> ALTER DATABASE FLASHBACK ON;

37. On the standby database, restart MRP:

38.    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;

Also recreate the block_change_tracking file if it’s used as it may be disabled.

Posted in Data Guard and DR | Leave a Comment »

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

Posted by Hendry chinnapparaj on February 21, 2011

 

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

Problem

———–

The following errors occurs in standby database – seen in oracle alert log

Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p000_1470606.trc:

ORA-00600: internal error code, arguments: [3020], [124], [214081], [520307777], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

ORA-10564: tablespace ZOE_LM_INDEX_1M

ORA-01110: data file 124: ‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 65407

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p002_757812.trc:

ORA-00600: internal error code, arguments: [3020], [2], [43149], [8431757], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 43149)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: ‘/u02/oradata/zoeprd/undotbs01.dbf’

ORA-10560: block type ‘KTU UNDO BLOCK’

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p001_1200318.trc:

ORA-00600: internal error code, arguments: [3020], [466], [15128], [1954560792], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 466, block# 15128)

ORA-10564: tablespace DOCIDX021

ORA-01110: data file 466: ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 139733

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p000_1470606.trc:

ORA-00600: internal error code, arguments: [3020], [124], [214081], [520307777], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

ORA-10564: tablespace ZOE_LM_INDEX_1M

ORA-01110: data file 124: ‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 65407

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p002_757812.trc:

ORA-00600: internal error code, arguments: [3020], [2], [43149], [8431757], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 43149)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: ‘/u02/oradata/zoeprd/undotbs01.dbf’

ORA-10560: block type ‘KTU UNDO BLOCK’

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p001_1200318.trc:

ORA-00600: internal error code, arguments: [3020], [466], [15128], [1954560792], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 466, block# 15128)

ORA-10564: tablespace DOCIDX021

ORA-01110: data file 466: ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 139733

Sat Feb 19 08:19:16 2011

MRP0: Background Media Recovery terminated with error 12801

Sat Feb 19 08:19:16 2011

 

Cause:-

———

The Redo when shipped across network or somewhere along the way had some issues which caused the inconsistency, hence not able to syncup with the standby datafiles.

 

 

Fix:-

——–

 

Get the files from the primary and syncup the standby as below

 

There are 3 files affected

File numbers are 466, 124 and 2

1) You have to find out the tablespace name related to this datafiles

‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’ ==> File 466
‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’ ==> File 124
‘/u02/oradata/zoeprd/undotbs01.dbf’ ===> File 2

From primary sql> select tablespace_name from dba_data_files where file_name = ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’ ;
do the same for rest of 2 files also..

2) From primary sql> ALTER tablespace <tablespacename> begin backup;

3) Copy (SCP/FTP) the datafile file from PRIMARY to STANDBY

 

 

ftp <stby server name>

4) From primary sql> ALTER tablespace <tablespacename> end backup;

5) Start the recovery in standby
recover managed standby database using current logfile disconnect;
6) monitor the alert log if any issues

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted in Data Guard and DR | 1 Comment »

ReConfigure Data Guard using Cold Backup from Primary Database

Posted by Hendry chinnapparaj on December 20, 2010

Hendry Dasan

Monday, 20 December 2010

 

ReConfigure Data Guard using Cold Backup from Primary Database

 

The current status is

 

Primary001 – primary server

Standby001 – Standby server, but no database running.

 

Primary Server Details

 

Server name: Primary001

 

LOG_MODE     FORCE_LOGGING

———— ————-

ARCHIVELOG   YES

1 row selected.

Time End: 16/12/2010 5:59:54 PM

Elapsed Time for Script Execution: 1 sec

 

SELECT DBID,

NAME,

DB_UNIQUE_NAME,

LOG_MODE,

OPEN_MODE,

PROTECTION_MODE,

PROTECTION_LEVEL,

DATABASE_ROLE,

FLASHBACK_ON,

CURRENT_SCN

FROM V$DATABASE;

Time Start: 16/12/2010 5:15:40 PM

 

DBID NAME      DB_UNIQUE_NAME                 LOG_MODE     OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE

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

FLASHBACK_ON       CURRENT_SCN

—————— ———–

224883408 REPLICA    REPLICA_fc                      ARCHIVELOG   READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY

YES                 2.6969E+10

 

1 row selected.

Time End: 16/12/2010 5:15:41 PM

Elapsed Time for Script Execution: 1 sec

 

 

SELECT COUNT(*) TOTAL_DATAFILES FROM  V$DATAFILE;

SELECT COUNT(*) TOTAL_LOGFILES_AND_STBY_FILES FROM  V$LOGFILE;

SELECT COUNT(*) TOTAL_CONTROLFILES FROM  V$CONTROLFILE;

 

Time Start: 16/12/2010 5:16:24 PM

 

TOTAL_DATAFILES

—————

625

1 row selected.

 

TOTAL_LOGFILES_AND_STBY_FILES

—————————–

13

1 row selected.

 

TOTAL_CONTROLFILES

——————

3

1 row selected.

Time End: 16/12/2010 5:16:25 PM

Elapsed Time for Script Execution: 1 sec

 

 

SELECT SUM(BYTES)/(1024*1024*1024) DF_SIZE_IN_GB FROM V$DATAFILE;

SELECT SUM(BYTES)/(1024*1024*1024) LOGFILE_SIZE_IN_GB FROM V$LOGFILE A, V$LOG B WHERE A.GROUP#=B.GROUP#;

SELECT SUM(BYTES)/(1024*1024*1024) TEMPFILE_IN_GB FROM V$TEMPFILE WHERE STATUS=’ONLINE’;

 

Time Start: 16/12/2010 5:17:11 PM

 

DF_SIZE_IN_GB

————-

1995.90629

1 row selected.

 

LOGFILE_SIZE_IN_GB

——————

16

1 row selected.

 

TEMPFILE_IN_GB

————–

13.8212891

1 row selected.

Time End: 16/12/2010 5:17:19 PM

Elapsed Time for Script Execution: 8 secs

 

 

SELECT SUM(BYTES) /1024/1024/1024 “Read only in GB” FROM V$DATAFILE WHERE ENABLED=’READ ONLY’;

 

Time Start: 16/12/2010 5:17:55 PM

 

Read only in GB

—————

985.458984

1 row selected.

Time End: 16/12/2010 5:17:56 PM

Elapsed Time for Script Execution: 1 sec

 

 

— SHOW THE DATAFILES WHICH ARE OFFLINE

select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

 

 

Time Start: 16/12/2010 5:19:05 PM

no rows selected.

Time End: 16/12/2010 5:19:06 PM

Elapsed Time for Script Execution: 687 msecs

 

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/oracle/product/10.2/db_1/

dbs/spfileREPLICA.ora

 

Select * from v$tempfile;

 

Time Start: 16/12/2010 5:20:29 PM

 

FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- —————- ————- ———- ———- ——- ———- ———- ———- ———— ———-

NAME

——————————————————————————————————————————————–

1         78931268 06-AUG-08              3          1 ONLINE  READ WRITE 1.4840E+10    1811584   1.4840E+10       8192

/u02/oradata/REPLICA/temp01.dbf

 

1 row selected.

Time End: 16/12/2010 5:20:30 PM

Elapsed Time for Script Execution: 641 msecs

 

Select * from dba_temp_files;

Select count(*) from dba_data_files;

 

 

 

SELECT NAME “Read only” FROM V$DATAFILE WHERE ENABLED=’READ ONLY’;

 

 

Time Start: 16/12/2010 5:22:03 PM

 

Read only

——————————————————————————————————————————————–

/u04/oradata/REPLICA/CB001_01.DBF

/u04/oradata/REPLICA/CB002_01.DBF

/u04/oradata/REPLICA/CB003_01.DBF

/u04/oradata/REPLICA/CB004_01.DBF

/u04/oradata/REPLICA/CB005_01.DBF

/u04/oradata/REPLICA/CB006_01.DBF

/u04/oradata/REPLICA/CB007_01.DBF

/u04/oradata/REPLICA/CB008_01.DBF

/u04/oradata/REPLICA/CB009_01.DBF

/u04/oradata/REPLICA/CB010_01.DBF

/u04/oradata/REPLICA/CB011_01.DBF

/u04/oradata/REPLICA/CB012_01.DBF

/u04/oradata/REPLICA/CB013_01.DBF

/u04/oradata/REPLICA/CB014_01.DBF

/u04/oradata/REPLICA/CB015_01.DBF

/u04/oradata/REPLICA/CB016_01.DBF

/u04/oradata/REPLICA/CB017_01.DBF

/u04/oradata/REPLICA/CB018_01.DBF

/u04/oradata/REPLICA/CB019_01.DBF

/u04/oradata/REPLICA/CB020_01.DBF

/u04/oradata/REPLICA/CB021_01.DBF

/u04/oradata/REPLICA/CB022_01.DBF

/u04/oradata/REPLICA/CB023_01.DBF

/u04/oradata/REPLICA/CB024_01.DBF

/u04/oradata/REPLICA/CB025_01.DBF

/u04/oradata/REPLICA/CB026_01.DBF

/u04/oradata/REPLICA/CB027_01.DBF

/u04/oradata/REPLICA/CB028_01.DBF

/u04/oradata/REPLICA/CB029_01.DBF

/u04/oradata/REPLICA/CB030_01.DBF

/u04/oradata/REPLICA/CB031_01.DBF

/u04/oradata/REPLICA/CB032_01.DBF

/u04/oradata/REPLICA/CB033_01.DBF

/u04/oradata/REPLICA/CB034_01.DBF

/u04/oradata/REPLICA/CB035_01.DBF

/u04/oradata/REPLICA/CB036_01.DBF

/u04/oradata/REPLICA/CB037_01.DBF

/u04/oradata/REPLICA/CB038_01.DBF

/u04/oradata/REPLICA/CB039_01.DBF

/u04/oradata/REPLICA/CB040_01.DBF

/u04/oradata/REPLICA/CB041_01.DBF

/u04/oradata/REPLICA/CB042_01.DBF

/u04/oradata/REPLICA/CB043_01.DBF

/u04/oradata/REPLICA/CB044_01.DBF

/u04/oradata/REPLICA/CB045_01.DBF

/u04/oradata/REPLICA/CB046_01.DBF

/u04/oradata/REPLICA/CB047_01.DBF

/u04/oradata/REPLICA/CB048_01.DBF

/u04/oradata/REPLICA/CB049_01.DBF

/u04/oradata/REPLICA/CB050_01.DBF

/u04/oradata/REPLICA/CB051_01.DBF

/u04/oradata/REPLICA/CB052_01.DBF

/u04/oradata/REPLICA/CB053_01.DBF

/u04/oradata/REPLICA/CB054_01.DBF

/u04/oradata/REPLICA/CB055_01.DBF

/u04/oradata/REPLICA/CB056_01.DBF

/u04/oradata/REPLICA/CB057_01.DBF

/u04/oradata/REPLICA/CB058_01.DBF

/u04/oradata/REPLICA/CB059_01.DBF

/u04/oradata/REPLICA/CB060_01.DBF

/u04/oradata/REPLICA/CB061_01.DBF

/u04/oradata/REPLICA/CB062_01.DBF

/u04/oradata/REPLICA/CB063_01.DBF

/u04/oradata/REPLICA/CB064_01.DBF

/u04/oradata/REPLICA/CB065_01.DBF

/u04/oradata/REPLICA/CB066_01.DBF

/u04/oradata/REPLICA/CB067_01.DBF

/u04/oradata/REPLICA/CB068_01.DBF

/u04/oradata/REPLICA/CB069_01.DBF

/u04/oradata/REPLICA/CB070_01.DBF

 

70 rows selected.

Time End: 16/12/2010 5:22:04 PM

Elapsed Time for Script Execution: 797 msecs

 

 

 

 

 

Standby Server Details

 

 

Server name: standby001

 

 

 

OPERARTIONS

 

 

Shutdown the PRIMARY Database, Listeners and Standby

 

Login to primary001

 

SQL> shutdown immediate

 

oracle@primary001 scptest>ps -ef|grep tns

oracle  831554       1   0   Mar 12      – 167:37 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_fc_dg -inherit

oracle 1122458       1   0   Jul 22      – 64:15 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_public –inherit

 

lsnrctl stop REPLICA_public

lsnrctl stop REPLICA_fc_dg

 

Login to standby server – standby001 and stop the Listener is any running

 

oracle@standby001 oracle>ps -ef|grep tns

oracle  659472  880772   0 10:15:42  pts/1  0:00 grep tns

oracle  950348       1   0   Jul 16      –  9:53 /u01/oracle/product/10.2/db_1/bin/tnslsnr REPLICA_js_dg –inherit

 

lsnrctl stop REPLICA_js_dg

 

 

 

Copy / scp the datafiles to DR Server – standby001

 

Do a cleanup on the DR Server if required – check the relevant directories

 

scp -pr /u04/oradata/REPLICA/* oracle@standby001:/u04/oradata/REPLICA

 

scp -pr /u02/oradata/REPLICA/* oracle@standby001:/u02/oradata/REPLICA

 

pwd

lpwd

cd

lcd

 

Note:- scp by default is binary copy, so no worries

 

 

if required you can use TOAD to compare the files under both server directories after capturing them on the excel sheet

 

 

 

Create STANDBY Controlfile in Primary Server / Database

 

 

Login to primary001 Primary Server

 

SQL> startup

 

SQL> Alter database create standby controfile as ‘/u01/oracle/control01.ctl’;

 

Do few Log switches on Primary Server

 

SQL> alter system switch logfile;

/

/

 

 

SQL>

 

 

 

Copy / sftp this file to /u02/oradata/REPLICA

 

sftp  oracle@standby001

pwd: *****

cd /u02/oradata/REPLICA

lcd /u01/oracle

 

 

Also Copy / sftp the Archive logs to DR Server – standby001

 

 

Login to standby001 – DR Server and create the Archivelog Directory

 

cd /u03/oradata/flash_recovery_area/REPLICA_JS/archivelog

oracle@standby001 archivelog>pwd

/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog

mkdir 2010_12_19

 

 

 

 

 

 

sftp oracle@standby001

pwd:****

cd /u03/oradata/flash_recovery_area/REPLICA_JS/archivelog/2010_12_19

lcd /u03/oradata/flash_recovery_area/REPLICA_FC/archivelog/2010_12_19

 

sftp> mput *

 

oracle@standby001 archivelog>pwd

/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog

oracle@standby001 archivelog>ls -lrt

total 8

drwxr-xr-x   2 oracle   dba            2048 Dec 19 22:10 2010_12_19

drwxr-x—   2 oracle   dba            2048 Dec 20 12:55 2010_12_20

oracle@standby001 archivelog>cd 2010_12_19

oracle@standby001 2010_12_19>pwd

/u03/oradata/flash_recovery_area/REPLICA_JS/archivelog/2010_12_19

oracle@standby001 2010_12_19>ls -lrt

total 3212664

-r–r—–   1 oracle   dba          167424 Dec 19 15:31 o1_mf_1_1802_1E4rhl3O2_.arc

-r–r—–   1 oracle   dba        45288960 Dec 19 15:31 o1_mf_1_1803_1E4s16McD_.arc

-r–r—–   1 oracle   dba         2191360 Dec 19 15:31 o1_mf_1_1805_1E4sC2jT2_.arc

-r–r—–   1 oracle   dba        76392960 Dec 19 15:31 o1_mf_1_1804_1E4sBwGUH_.arc

-r–r—–   1 oracle   dba        45288960 Dec 19 15:38 o1_mf_1_1803_1E4sbI1M8_.arc

-r–r—–   1 oracle   dba         2191360 Dec 19 15:38 o1_mf_1_1805_1E4sbkaFE_.arc

-r–r—–   1 oracle   dba        76392960 Dec 19 15:38 o1_mf_1_1804_1E4sbT251_.arc

 

 

Startup the Standby Database in DR Server

 

Login to standby001 and recheck the initREPLICA.ora parameters if needed for stby parameters

Ensure the controlfile is pointing to the just copied standby controlfile under “/u02/oradata/REPLICA” directory.

 

Sqlplus / as sysdba

 

 

Startup nomount pfile=initREPLICA.ora

Alter database mount standby database;

 

 

Create Standby Log files and temp file

 

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE

‘/u02/oradata/REPLICA/temp01.dbf’ SIZE 14153M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED

TABLESPACE GROUP ”

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

alter system set standby_file_management=manual;

 

.alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_09.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_10.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_11.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_12.log’ SIZE 2G reuse;

alter database add standby logfile ‘/u03/oradata/REPLICA/stdby_redo_13.log’ SIZE 2G reuse;

 

alter system set standby_file_management=auto;

 

 

Enable Block Change Tracking in standby server

 

SQL> alter database enable block change tracking using file ‘/u01/oracle/product/10.2.0/db_1/dbs/REPLICA_bct.dbf’ reuse;

 

 

 

Stop the job_queue_processes  STANDBY database by setting them to 0

 

SQL> alter system set job_queue_processes=0 scope=memory;

 

SQL> alter database recover managed standby database disconnect from session

 

 

IF needed , do check in Primary Server / database

– CHECK THE STATUS IF VALID FOR STBY

select dest_id, status from v$archive_dest;

show parameter dest_state_2

 

 

— TO disable log shipping to stby site

alter system set log_archive_dest_state_2=defer scope=memory;

 

— TO disable log shipping to stby site

alter system set log_archive_dest_state_2=enable scope=memory;

 

 

Check the open_mode, protection_mode, database_role, Flashback Status in the STANDBY Server

 

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

 

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE    FLASHBACK_ON

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

REPLICA    MOUNTED    MAXIMUM AVAILABILITY PHYSICAL STANDBY YES

 

SQL> select * from v$archive_gap;

 

 

Check the open_mode, protection_mode, database_role, Flashback Status in the PRIMARY Server

 

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

 

NAME      OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE    FLASHBACK_ON

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

REPLICA    READ WRITE MAXIMUM AVAILABILITY PRIMARY          YES

 

 

 

Also run the ControlM Job and verify if the standby database is in sync with Primary

Run the Script from the PRIMARY Database

 

oracle@primary001 logs>cat REPLICA.stby_check.20101220_1134.log

 

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Dec 20 11:34:58 2010

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

PERFORMING – DATAGUARD STATUS CHECK FOR LOG_GAP <= 2 AND SYNCHRONIZATION_STATUS = OK

 

 

INST_NAME HOST_NAME  PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED TIME_APPLIED   LOG_GAP

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

REPLICA    primary001 MAXIMUM AVAILABILITY                     OK         1855        1855 20/12 11:30:57       0

 

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

Making initREPLICA.ora changes permanent in Standby

 

Alter system job_queue_processes=0 scope=spfile

….

 

Create spfile from pfile;

 

Shutdown immediate

Startup nomount

Alter database mount standby database

alter database recover managed standby database disconnect from session

 

 

 

 

 

TEST THE STANDBY IN READ ONLY MODE

 

 

In Primary – primary001

 

Login as db user

 

Bb15908

 

Create table test as select * from dba_users;

 

Alter system switch logfile;

 

/

 

Login to standby server – standby001

Note:- if you have enable DB Auditing , then the following is required to open the database in read only mode.

 

SQL> Recover managed standby database cancel;

Media recovery complete.

SQL> alter database open readonly;

alter database open readonly

*

ERROR at line 1:

ORA-02288: invalid OPEN mode

 

 

SQL> alter database open read only;

alter database open read only

*

ERROR at line 1:

ORA-16006: audit_trail destination incompatible with database open mode

 

 

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_trail                          string      DB

 

 

SQL> alter system set audit_trail=OS scope=spfile;

 

System altered.

 

Shutdown immediate

 

 

ORA-01109: database not open

 

 

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area 3321888768 bytes

Fixed Size                  2087680 bytes

Variable Size            1828717824 bytes

Database Buffers         1476395008 bytes

Redo Buffers               14688256 bytes

SQL> alter database mount standby database;

 

Database altered.

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_trail                          string      OS

 

 

 

SQL> alter database open read only;

 

SQL> select * from bb15908.test;

 

Make sure you set back the audit_trail=DB after this

 

 

 

Shutdown immediate

Startup nomount

Alter database mount standby database

 

 

SQL> alter system set audit_trail=DB scope=spfile;

 

System altered.

 

Shutdown immediate

Startup nomount

Alter database mount standby database;

alter database recover managed standby database disconnect from session;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted in Data Guard and DR | Leave a Comment »

Manually Switchback the OAT Database from primary001 to standby001

Posted by Hendry chinnapparaj on December 20, 2010

 

Hendry Dasan

Monday, 20 December 2010

 

 

 

Manually Switchback the OAT Database from primary001 to standby001

 

The current status is

Standby001 – standby

Primary001 – primary

 

 

 

 

PRIMARY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SESSIONS ACTIVE

 

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

 

Database altered.

 

 

SQL> shutdown immediate

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

 

Total System Global Area 2801795072 bytes

Fixed Size                  2086680 bytes

Variable Size            1711278312 bytes

Database Buffers         1073741824 bytes

Redo Buffers               14688256 bytes

Database mounted.

 

 

SQL> show parameter job_queue_processes

 

NAME                                 TYPE

———————————— ——————————–

VALUE

——————————

job_queue_processes                  integer

0

 

 

STANDBY

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SWITCHOVER LATENT

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

TO PRIMARY

 

 

 

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ;

 

Database altered.

 

SQL> select open_mode from v$database;

 

OPEN_MODE

———-

MOUNTED

 

SQL> alter database open;

 

Database altered.

 

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

 

SWITCHOVER_STATUS

——————–

SESSIONS ACTIVE

 

 

 

SQL> show parameter job_queue_processes

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     10

 

 

 

STANDBY

SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Media recovery complete.

 

PRIMARY

SQL> show parameter job_queue_processes

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     4

SQL>

SQL> select dest_id, status from v$archive_dest_status;

 

DEST_ID STATUS

———- ———

1 VALID

2 DEFERRED

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

 

10 rows selected.

 

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

 

System altered.

 

SQL> select dest_id, status from v$archive_dest_status;

 

DEST_ID STATUS

———- ———

1 VALID

2 VALID

3 INACTIVE

4 INACTIVE

5 INACTIVE

6 INACTIVE

7 INACTIVE

8 INACTIVE

9 INACTIVE

10 INACTIVE

 

10 rows selected.

 

 

STANDBY

SQL> select STATUS from v$managed_standby;

 

STATUS

————

CONNECTED

CLOSING

WAIT_FOR_LOG

IDLE

IDLE

 

SQL> select STATUS from v$managed_standby;

 

STATUS

————

CONNECTED

CLOSING

APPLYING_LOG

IDLE

IDLE

 

 

PRIMARY

oracle@standby001 logs>cat replica.stby_check.20101220_1224.log

 

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Dec 20 12:24:23 2010

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

 

 

PERFORMING – DATAGUARD STATUS CHECK FOR LOG_GAP <= 2 AND SYNCHRONIZATION_STATUS = OK

 

 

INST_NAME HOST_NAME  PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED TIME_APPLIED   LOG_GAP

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

replica    standby001 MAXIMUM AVAILABILITY                     OK         1863        1863 20/12 12:20:37       0

 

 

DATAGUARD STATUS CHECK RETURNED NO ROWS – CHECK V$ARCHIVED_LOG FOR POSSIBLE CAUSE

 

 

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

oracle@standby001 logs>

 

 

 

QL> SHOW PARAMETER JOB_QUEUE

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

job_queue_processes                  integer     10

 

Posted in Data Guard and DR | Leave a Comment »

Application Client Failover and DR Test

Posted by Hendry chinnapparaj on June 17, 2010

Application Client Failover and DR Test

Hendry Dasan – DBA

Thursday, 17 June 2010

Server and Database original setup Details

———————————————–

1.1     Environment Details – original setting

  • OS: IBM AIX 5.3 TL05
  • Hardware: IBM P-Series 595 Model
  • Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit)
  • ASM Oracle 11Gr2 Instance
  • Primary Site: 1 Node (DEVU009N3) – Dasan Street
  • Standby Site: 1 Node (DEVU009N4) – Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

Tnsnames.ora setting in the  remote client

———————————————-

CPS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cps)

)

)

Data Guard Config Now

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

Note:_ the primary database is already at the DR Site- FC – before this exercise.

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              134         134

18/05 12:01:30          0

Connection from the remote client – pointing to primary database server (Christ court)

select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_f

DOING A SWITCHOVER NOW from the current Primary Node(devu009n4) at Christ Court while the TOAD Client is still connected.

TEST1 à SWITCHOVER FROM CHRIST COURT TO DASAN STREET

DGMGRL> CONNECT SYS/CPSTEST

Connected.

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL>

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> show parameter service_name à Note:- the service_name changed back to cpstest_f in the standby role.

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_f

Connection from the remote TOAD client again – pointing to primary database server (Dasan Street)

select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

Also from the command prompt / remote sqlplus client connection to primary database

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

Thursday, June 17, 2010

Query the new Primary database

SQL>  show parameter service_name à Note:- The Primary database always has the service_names as ”cps”

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

Query the primary and standby database for any offline files

————————————————————-

select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’)

no rows selected

NST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              465         465

17/06 06:03:31          0

SQL> alter system switch logfile;

System altered.

select user from dual;

USER

——————————

CPS_OWNER

1 row selected.

select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n4

10.2.0.4.0        11-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL>

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL>

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n3

10.2.0.4.0        17-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

select user from dual;

USER

——————————

CPS_OWNER

1 row selected.

Apps tested here – successfully connected to the other database.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM AVAILABILITY OK                              475         475

17/06 13:56:23          0

Primary

————

SQL> select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

——————– ——————–

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

standby

———-

SQL> select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

TEST1 à SWITCHOVER FROM CHRIST COURT TO DASAN STREET successfully completed

TEST2 à FAILOVER FROM DASAN STREET TO CHRIST COURT

Primary server Query

———————

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

FFF

SQL> select database_role,PROTECTION_MODE from v$database;

DATABASE_ROLE    PROTECTION_MODE

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

PRIMARY          MAXIMUM AVAILABILITY

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

standby server Query

————————–

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

NOW KILL THE PRIMARY SERVER DEVU009N3 – CAN PULLOUT / DISABLE/ shutdown  THE NETWORK INTERFACE

After killing ther primary server, connect to dgmgrl tool in standby database –

may be a bit slow, but gets connected

– Now the primary server hangs / all the putty sessions hangs

STANDBY SERVER

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

— JUST HANGS HERE IN STANDBY SERVER – DEVU009N4

Error: ORA-16662: network timeout when contacting a remote database

DGMGRL> DGMGRL>

DGMGRL> failover to cpstest_f;

Performing failover NOW, please wait…

Failover succeeded, new primary is “cpstest_f”

DGMGRL>

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database (disabled)

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

——————– ——————–

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n4

10.2.0.4.0        17-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

SQL> select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

NOW BRING UP THE OLD PRIMARY SERVER – ENABLE NETWORK

INTERFACE

Reinstate the old primary as new standby

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2083760 bytes

Variable Size             138413136 bytes

Database Buffers          171966464 bytes

Redo Buffers                2109440 bytes

Database mounted.

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SO CONNECT TO NEW PRIMARY DATABASE AND ISSUE THE BELOW FOR

REINSTATING THE OLD PRIMARY AS NEW STANDBY DATABASE

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_j;

Reinstating database “cpstest_j”, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “cpstest_j” …

Reinstatement of database “cpstest_j” succeeded

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

NOW CONNECT TO THE NEW STANDBY DATABASE SERVER

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL> CONN / AS SYSDBA

Connected.

SQL> select name,status,checkpoint_change# from v$datafile where status not in

(‘ONLINE’,’SYSTEM’);  2

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

GMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL>

CONNECT TO PRIMARY AND SWITCH LOGS AND CHECK IF THEY ARE IN SYNC

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               4           4

17/06 15:28:37          0

Note:- After the failover, the protection mode changes to maxperformance.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK 33          33

21/06 14:48:59          0

1 row selected.

Check TOAD Connection

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION           STARTUP_TIME STATUS       PARALLEL    THREAD# ARCHIVER

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

LOG_SWITCH_WAIT LOGINS     SHUTDOWN_PENDING DATABASE_STATUS   INSTANCE_ROLE

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

ACTIVE_STATE BLOCKED

———— ——-

1 cpstest

devu009n4

10.2.0.4.0        17-JUN-10    OPEN         NO                1 STARTED

ALLOWED    NO               ACTIVE            PRIMARY_INSTANCE

NORMAL       NO

1 row selected.

Apps tested here – successfully connected to the other database.

Shalom!

Posted in Data Guard and DR | Leave a Comment »

ORA-16628: the broker protection mode is inconsistent with the database setting

Posted by Hendry chinnapparaj on May 24, 2010

Problem and Diagnosis

————————–

ORA-16628: the broker protection mode is inconsistent with the database setting

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

Warning: ORA-16607: one or more databases have failed

Cd /u01/oracle/product/10.2.0/db_1/admin/cpstest/bdump

Cat drccpstest.log

DG 2010-05-24-10:13:03        0 2 719363232 Operation CTL_GET_STATUS cancelled during phase 2, error = ORA-16628

DG 2010-05-24-10:14:03        0 2 0 RSM Error: protection mode ‘MaxPerformance’ set in Broker metadata is inconsistent with the actual protection mode ‘MAXIMUM AVAILABILITY’ currently set in database

DG 2010-05-24-10:14:03        0 2 0 RSM0: HEALTH CHECK ERROR: ORA-16628: the broker protection mode is inconsistent with the database setting

DGMGRL> show database verbose cpstest_f

Database

Name:            cpstest_f

Current status for “cpstest_f”:

Error: ORA-16628: the broker protection mode is inconsistent with the database setting

Cause

——–

You have changed the protection_mode to maximum availability in the database using Sql, but not in the data guard broker

Solution

———–

DGMGRL> Edit configuration set protection mode as maxavailability;

Succeeded.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

This gets replicated to standby database as well

Posted in Data Guard and DR | Leave a Comment »

Simple switchover from primary site to DR using Data Guard Broker

Posted by Hendry chinnapparaj on May 18, 2010

Hendry Dasan – DBA

Tuesday, 18 May 2010

Simple switchover from primary site to DR using Data Guard Broker

Server and Database original setup Details

———————————————–

1.1     Environment Details

  • OS: IBM AIX 5.3 TL05
  • Hardware: IBM P-Series 595 Model
  • Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit)
  • ASM Oracle 11Gr2 Instance
  • Primary Site: 1 Node (DEVU009N3) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

TEST2 à SWITCHOVER FROM DASAN STREET to CHRIST COURT

SQL>  show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL>

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> /

System altered.

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM AVAILABILITY OK                              141         141

18/05 13:18:10          0

CONNECTION FROM THE REMOTE CLIENT TO PRIMARY SERVER

 

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

Now I connect to the current standby server(devu009n4) using dgmrgl tool and invoke the switchover from JS to FC

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Make sure you connect as “sys” user for a successful switchover.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> conn / as sysdba

Connected.

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

without disconnecting the dgtest client, query the database again – the hostname indicates that the client has failedover to the other server

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL>

Also confirmed from this query

SQL> Select failover_type, failover_method, failed_over from v$session where username =’DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      YES

Also ensure that all the readonly datafiles and other files are ONLINE

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              149         146

18/05 13:27:03          3

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

——————– ——————–

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Posted in Data Guard and DR | Leave a Comment »

Oracle10g Database Failover Test using Data Guard Broker

Posted by Hendry chinnapparaj on May 18, 2010

Hendry Dasan – DBA

Tuesday, 18 May 2010

Oracle10g Database Failover Test using Data Guard Broker

The following is a step by step operation on the Data Guard Failover operations for Oracle10g Database in an Oracle11gR2 ASM Env. After the successful failover, the original data Guard Configuration is reverted with a switchover

Server and Database original setup Details

———————————————–

1.1     Environment Details

  • OS: IBM AIX 5.3 TL05
  • Hardware: IBM P-Series 595 Model
  • Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit)
  • ASM Oracle 11Gr2 Instance
  • Primary Site: 1 Node (DEVU009N3) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

BUT IN THE CURRENT SETTING THE PRIMARY IS “cpstest_f” and standby is “cpstest_j”

Connect to primary server – devu009n4 and query the below

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> connect sys/cpstest

Connected.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> select database_role,PROTECTION_MODE from v$database;

DATABASE_ROLE    PROTECTION_MODE

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

PRIMARY          MAXIMUM AVAILABILITY

In standby database server

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

——————– ——————–

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL>

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

NOW KILL THE PRIMARY SERVER DEVU009N4 – CAN PULLOUT / DISABLE THE NETWORK INTERFACE

After killing ther primary server, connect to dgmgrl tool in standby database – may be a bit slow, but gets connected

devu009n3 $

devu009n3 $ dgmgrl /

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> DGMGRL>

DGMGRL>

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

Error: ORA-16625: cannot reach the database

DGMGRL> failover to cpstest_j;

Performing failover NOW, please wait…

Failover succeeded, new primary is “cpstest_j”

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

——————– ——————–

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL>  select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database (disabled)

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> show database cpstest_j;

Database

Name:            cpstest_j

Role:            PRIMARY

Enabled:         YES

Intended State:  ONLINE

Instance(s):

cpstest

Current status for “cpstest_j”:

SUCCESS

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

query from the already connected remote client and test which server it connected to – the output below shows that the client has successfully failedover to the new primary server without being disconnected.

15:04:55 SQL> show user

USER is “DGTEST”

15:21:01 SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

Alert log in new primary shows now

PING[ARC0]: Heartbeat failed to connect to standby ‘(DESCRIPTION=(ADDRESS_LIST=(address=(protocol=TCP)(host=devu009n3)(port=1521)))(CONNECT_DATA=(SERVICE_NAME=cpstest_j_XPT)(INSTANCE_NAME=cpstest)(SERVER=dedicated)))’. Error is 16009.

Tue May 18 15:15:26 2010

ALTER SYSTEM SET log_archive_dest_state_2=’RESET’ SCOPE=BOTH;

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

NOW BRING UP THE OLD PRIMARY SERVER – ENABLE NETWORK INTERFACE

Reinstate the old primary as new standby

devu009n4 $ . oraenv

ORACLE_SID = [cpstest] ?

devu009n4 $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 15:16:47 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2083760 bytes

Variable Size             138413136 bytes

Database Buffers          171966464 bytes

Redo Buffers                2109440 bytes

Database mounted.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          MOUNTED

IMPORTANT – DON’T ISSUE THE FOLLOWING COMMAND IN THE OLD PRIMARY DATABASE AS SHOWN BELOW

YOU WILL GET  ORA-16795 ERRORS

GMGRL> connect sys/cpstest

Connected.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_f;

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

1.2      ORA-16795: database resource guard detects that database re-creation is required

Cause: In the act of failover or switchover, the database resource guard may have detected that re-creation of the database is necessary. This occurs when the database resource guard recognizes a situation in which the database in question cannot be a viable standby database for the new primary database. Until this error status is resolved for this database, information about this database and the broker configuration to which it belongs is unavailable to a broker client that is connected to this database. Therefore, all commands directed by that client to this database cannot be completed.
Action: Re-create (or flash back) the standby database. Connect to the primary database in the broker configuration and reenable broker management of that database. At this point you may connect to that standby database and resume issuing client commands. Alternatively, many client commands that cannot be completed at the standby database when in this error state can be completed successfully when issued to the primary database. In this case, simply reconnect to the primary database and retry the command.

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SO CONNECT TO NEW PRIMARY DATABASE AND ISSUE THE BELOW FOR REINSTATING THE OLD PRIMARY AS NEW STANDBY DATABASE

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_f;

Reinstating database “cpstest_f”, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “cpstest_f” …

Reinstatement of database “cpstest_f” succeeded

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               4           4

18/05 15:51:19          0

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

The remote client is still connected

15:21:06 SQL> show user

USER is “DGTEST”

15:53:11 SQL> show user

USER is “DGTEST”

15:53:16 SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

Query the new standby database now

SQL> conn / as sysdba

Connected.

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_f

DGMGRL> connect sys/cpstest

Connected.

DGMGRL>  show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Reverting to original data guard configuration

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               6           6

18/05 16:06:19          0

Remote client connected to devu009n3 as being the primary server

16:08:12 SQL> show user

USER is “DGTEST”

16:08:15 SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> conn / as sysdba

Connected.

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL>

Again check the remote client connection – failedover to primary database now

16:08:12 SQL> show user

USER is “DGTEST”

16:08:15 SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

16:08:24 SQL>

16:16:58 SQL>

16:16:59 SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

16:17:03 SQL> show user

USER is “DGTEST”

Now connect to PRIMARY DATABASE SERVER – devu009n4 and query

 

SQL> conn / as sysdba

Connected.

SQL> show parameter service_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

Check if the data guard is in sync

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system archive log current;

System altered.

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

—————————————————————-

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM PERFORMANCE  CHECK CONFIGURATION              16          16

18/05 16:23:02          0

On the primary database, change the protection mode back to maximum availability

select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL

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

CPSTEST   READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

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

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL

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

CPSTEST   READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Shalom !

Posted in Data Guard and DR | Leave a Comment »

Client Failover Test Setup and Test in Data Guard Broker Env

Posted by Hendry chinnapparaj on May 18, 2010

Client failover Test for DR

1.1     Environment Details

  • OS: IBM AIX 5.3 TL05
  • Hardware: IBM P-Series 595 Model
  • Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit)
  • ASM Oracle 11Gr2 Instance
  • Primary Site: 1 Node (DEVU009N3) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

PRIMARY

————–

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n3

SQL> SHOW parameter service_names

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

devu009n3 $ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 14-MAY-2010 16:16:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                13-MAY-2010 13:27:56

Uptime                    1 days 2 hr. 48 min. 27 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/oracle/diag/tnslsnr/devu009n3/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devu009n3.greatwest.com)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “cpstest_j” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_DGB” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_XPT” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_dgmgrl” has 1 instance(s).

Instance “cpstest”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Creating a service

Set echo on

SQL> exec DBMS_SERVICE.CREATE_SERVICE ( –

> service_name => ‘cps’, –

> network_name => ‘cps’, –

> failover_method => ‘BASIC’, –

> failover_type => ‘SELECT’, –

> failover_retries => 180, –

> failover_delay => 1);

PL/SQL procedure successfully completed.

Start the service

SQL> exec dbms_service.start_service(‘cps’);

PL/SQL procedure successfully completed.

Now the service added to listener

devu009n3 $ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 14-MAY-2010 16:18:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                13-MAY-2010 13:27:56

Uptime                    1 days 2 hr. 50 min. 42 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/oracle/diag/tnslsnr/devu009n3/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devu009n3.greatwest.com)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “cps” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_DGB” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_XPT” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_dgmgrl” has 1 instance(s).

Instance “cpstest”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

SQL> show parameter service_names

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

Note the value has changed to “cps” now from cpstest_j

creating an after startup trigger

SQL> @create_trigger.sql

SQL> set echo on

SQL> create or replace trigger manage_service after startup on database

2  declare

3     role varchar(30);

4  begin

5     select database_role into role from v$database;

6     if role = ‘PRIMARY’ then

7        DBMS_SERVICE.START_SERVICE(‘cps’);

8     Else

9        DBMS_SERVICE.STOP_SERVICE(‘cps’);

10     End if;

11  End;

12  /

Trigger created.

Create Oracle net service name on primary and standby

cps =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cps.greatwest.com)

)

)

devu009n3 $ tnsping cps

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 14-MAY-2010 16:44:27

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cps.greatwest.com)))

OK (0 msec)

devu009n4 $ tnsping cps

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 14-MAY-2010 16:42:28

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cps.greatwest.com)))

OK (10 msec)

Testing the Implementation

——————————–

Create user dgtest identified by dgtest;

Grant create session to dgtest;

Grant select_catalog_role to dgtest;

Connect to primary instance now

devu009n4 $ sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Fri May 14 17:16:43 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

—————————————————————-

VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO

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

1 cpstest

devu009n3

10.2.0.4.0        14-MAY-10 OPEN         NO           1 STARTED

ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

SQL> select failover_type,failover_method,failed_over from v$session where username=’DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      NO

devu009n3 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

DGMGRL> sys/cpstest

Unrecognized command “sys”, try “help”

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

atabase dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Select failover_type, failover_method, failed_over from v$session where username = ‘DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      YES

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

SQL> show parameter service

NAME                                 TYPE        VALUE

———————————— ———– ——————————

service_names                        string      cps

Also test from a remote client – my laptop – connect thru sqlplus to the primary server when the stby listener is down

– enter the tns string in my laptop 10g tns

cps =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n5.greatwest.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cps)

)

)

— note I have given a wrong server name for the first host, thereby trying to use the second host to connect

— it has connected to the primary server – second line – host

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Fri May 14 17:40:10 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select host_name from v$instance;

HOST_NAME

—————————————————————-

devu009n4

Posted in Data Guard and DR | Leave a Comment »