Oracle Apps DBA Info: Dataguard

Showing posts with label Dataguard. Show all posts

Sunday, December 22, 2013

ORA-19502: write error on file "", block number (block size=) in Dataguard

We have received an alert in the morning that the Primary Database is out of Sync. Primary and Standby are with difference of 9 archives.

I have logged on to the primary and identified the below error.

ORA-19502: write error on file "", block number (block size=)

SQL> set line 200
col DEST_NAME for a50
col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'
/
   DEST_ID DEST_NAME                                          STATUS    BINDING    ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
         1 LOG_ARCHIVE_DEST_1                                 VALID     OPTIONAL
         2 LOG_ARCHIVE_DEST_2                                 ERROR     OPTIONAL   ORA-19502: write error on file "", block number  (block size=)

In the above output the STATUS column is showing ERROR.
I have logged on to the Standby database and found the archive mount point is full. I removed the applied archives and freed up some space. How to check Sync Status on Standby
Defer'd and Enable'd the shipping on Primary Database.

SQL> alter system set log_archive_dest_state_2=defer;

System Altered.

SQL> alter system set log_archive_dest_state_2=enable;

System Altered.

SQL> set line 200
SQL> col DEST_NAME for a50
SQL> col BINDING for a10

SQL> select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'
/
   DEST_ID DEST_NAME                                          STATUS    BINDING    ERROR
---------- -------------------------------------------------- --------- ---------- -----------------------------------------------------------------
         1 LOG_ARCHIVE_DEST_1                                 VALID     OPTIONAL
         2 LOG_ARCHIVE_DEST_2                                 VALID     OPTIONAL

Now the STATUS column is showing Valid and the ERROR column is null.
Archive started shipping to Standby Database and got applied.
How to check Sync Status on Standby

Monday, September 16, 2013

Standby Sync Status Check

TOP

-----------------------------------
-This script is used to check Standby Sync Status
- Script Name : sync_dr.sql
- Usage : @sync_dr
----------------------------------

select name,open_mode,database_role from v$database
/

set line 200
col DEST_NAME for a50
col BINDING for a10
select PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
/
archive log list

select count(*) from v$archive_gap
/
select max(sequence#) from v$log_history
/
col MESSAGE for a150
select * from(select message from v$dataguard_status where message like '%Media Recovery Log%' order by message desc) where rownum<=5
/

Friday, September 13, 2013

Standby Database Troubleshooting Sync Issues

ORA-16047: DGID mismatch between destination setting and target database

The error shows a miss-match of DG_CONFIG parameter. Check the below parameters on both Primary and Standby databases.

On Primary:

SQL> show parameter log_archive_config

NAME TYPE VALUE

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

log_archive_config string DG_CONFIG=(RUDBA,RUDBA_STANDBY)

SQL> show parameter fal

NAME TYPE VALUE

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

fal_client string RUDBA

fal_server string RUDBA_STANDBY

SQL> show parameter log_archive_dest_2

log_archive_dest_2 string SERVICE=RUDBA_STANDBY LGWR ASYNC=20480

DB_UNIQUE_NAME=RUDBA_STANDBY OPTIONAL REOPEN=15

MAX_FAILURE=10 NET_TIMEOUT=30

SQL> show parameter log_archive_dest_state_2

log_archive_dest_state_2 string ENABLE

On Standby:

SQL> show parameter log_archive_config

NAME TYPE VALUE

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

log_archive_config string DG_CONFIG=(RUDBA,RUDBA_STANDBY)

SQL> show parameter fal

NAME TYPE VALUE

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

fal_client string RUDBA_STANDBY

fal_server string RUDBA

SQL> show parameter log_archive_dest_2

log_archive_dest_2 string SERVICE=RUDBA LGWR ASYNC=20480 DB_UNIQUE_NAME=RUDBA

OPTIONAL REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30

SQL> show parameter log_archive_dest_state_2

log_archive_dest_state_2 string DEFER

If any of the above parameters are not set correctly the below error will appear.

set line 200

col DEST_NAME for a50

col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'

DEST_ID DEST_NAME STATUS BINDING ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID OPTIONAL

2 LOG_ARCHIVE_DEST_2 DISABLED OPTIONAL ORA-16047: DGID mismatch between destination setting and tar database

If the parameters are set correct.

set line 200

col DEST_NAME for a50

col BINDING for a10

select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from v$ARCHIVE_DEST where status<>'INACTIVE'

DEST_ID DEST_NAME STATUS BINDING ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID OPTIONAL

2 LOG_ARCHIVE_DEST_2 VALID OPTIONAL

Friday, September 6, 2013

Recovering Standby Database when archives are missing at both Production and Standby

Recovering Standby Database when archives are missing at both Production and Standby

1.Determine the Current SCN on Standby database

2.Defer log shipping to Standby Database

3.Create Standby Controlfile on Primary

4.Create the Incremental Backup on Primary using SCN

5.Make the Incremental Backup Accessible at the Standby Database

6.Cancel MRP on Standby database

7.Catalog the Incremental Backup Files at the Standby Database

8.Apply the Incremental Backup to the Standby Database

9.Shutdown the Standby database

10.Remove and replace the current control files with latest

11.Mount the standby and enable MRM mode.

12.Enable log shipping to Standby Database

13.Check for the Sync Status

Determine the Current SCN in Standby database
Find the current SCN in standby database using below query.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 212189

Defer log shipping to Standby Database Issue the below command on Primary to database to disable shipping of archives to standby database.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

Create Standby Controlfile on Primary and copy to Standby Database

SQL> alter database create standby controlfile as 'standby.ctl';

Database altered.

Create the Incremental Backup on Primary using SCN

Create the needed incremental backup at the primary database, using BACKUP with the INCREMENTAL FROM SCN clause. In this case, less SCN is used to take backup. i.e. 212100 instead 212189.

-bash-3.2$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Sep 3 15:18:59 2013

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

connected to target database: HCCDBP (DBID=4238383777)

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 212100 DATABASE FORMAT '/hccora/bkup_%U';

Starting backup at 03-SEP-13

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1074 device type=DISK
backup will be obsolete on date 10-SEP-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/hccora/db/apps_st/data/users01.dbf
input datafile file number=00001 name=/hccora/db/apps_st/data/system01.dbf
input datafile file number=00002 name=/hccora/db/apps_st/data/sysaux01.dbf
input datafile file number=00003 name=/hccora/db/apps_st/data/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 03-SEP-13
channel ORA_DISK_1: finished piece 1 at 03-SEP-13
piece handle=/hccora/bkup_01oiub3b_1_1 tag=TAG20130903T151905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

using channel ORA_DISK_1
backup will be obsolete on date 10-SEP-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 03-SEP-13
channel ORA_DISK_1: finished piece 1 at 03-SEP-13
piece handle=/hccora/bkup_02oiub4e_1_1 tag=TAG20130903T151905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-SEP-13

RMAN> exit

Recovery Manager complete.

Make the Incremental Backup Accessible at the Standby Database The directory should not contain anything other than the backup pieces.

-bash-3.2$ pwd
/hccora/backup
-bash-3.2$ ls -ltr
total 9636
-rw-r----- 1 hccora dba 9568256 Sep3 15:21 bkup_02oiub4e_1_1
-rw-r----- 1 hccora dba278528 Sep3 15:21 bkup_01oiub3b_1_1

Cancel MRP on Standby database

Use the below command on standby database to bring the standby database out of managed recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Catalog the Incremental Backup Files at the Standby Database Register the backup sets in the RMAN repository.

-bash-3.2$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Sep 3 15:26:34 2013

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

connected to target database: HCCDBP (DBID=4238383777, not open)

RMAN> CATALOG START WITH '/hccora/backup';

using target database control file instead of recovery catalog
searching for all files that match the pattern /hccora/backup

List of Files Unknown to the Database
=====================================
File Name: /hccora/backup/bkup_02oiub4e_1_1
File Name: /hccora/backup/bkup_01oiub3b_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: /hccora/backup/bkup_02oiub4e_1_1
File Name: /hccora/backup/bkup_01oiub3b_1_1

Apply the Incremental Backup to the Standby Database

RMAN>RECOVER DATABASE NOREDO;

Starting recover at 03-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=372 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /hccora/db/apps_st/data/system01.dbf
destination for restore of datafile 00002: /hccora/db/apps_st/data/sysaux01.dbf
destination for restore of datafile 00003: /hccora/db/apps_st/data/undotbs01.dbf
destination for restore of datafile 00004: /hccora/db/apps_st/data/users01.dbf
channel ORA_DISK_1: reading from backup piece /hccora/backup/bkup_01oiub3b_1_1
channel ORA_DISK_1: piece handle=/hccora/backup/bkup_01oiub3b_1_1 tag=TAG20130903T151905
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 03-SEP-13

RMAN> exit

Shutdown the Standby database

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Remove and replace the current control files with latest Remove the old control files. Copy the control file taken in step 3.

-bash-3.2$ grep control $ORACLE_HOME/dbs/init$ORACLE_SID.ora
# of the control files.
# There should be at least two control files, preferably three,
# located on different disks. The control files can dynamically grow,
control_files = /hccora/db/apps_st/data/cntrl01.dbf,/hccora/db/apps_st/data/cntrl02.dbf,/hccora/db/apps_st/data/cntrl03.dbf
# The log writer parameters control the size of the log buffer
# The checkpoint interval and timeout control the frequency of

-bash-3.2$ rm -f /hccora/db/apps_st/data/cntrl01.dbf
-bash-3.2$ rm -f /hccora/db/apps_st/data/cntrl02.dbf
-bash-3.2$ rm -f /hccora/db/apps_st/data/cntrl03.dbf
-bash-3.2$ cp standby.ctl /hccora/db/apps_st/data/cntrl01.dbf
-bash-3.2$ cp standby.ctl /hccora/db/apps_st/data/cntrl02.dbf
-bash-3.2$ cp standby.ctl /hccora/db/apps_st/data/cntrl03.dbf

In this case there are 3 control files in standby database.

Mount the standby and enable MRM mode

-bash-3.2$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Sep 3 14:49:29 2013

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area521936896 bytes
Fixed Size2161272 bytes
Variable Size 218105224 bytes
Database Buffers289406976 bytes
Redo Buffers 12263424 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Enable log shipping to Standby Database on Primary Connect to primary database and enable log shipping.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Check for the Sync Status Run the below sqls to check the status on Primary database.

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 999999
COL APPLIED_TIME FORMAT A12

SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM(SELECT NAME DB_NAME FROM v$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM v$INSTANCE),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM v$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES' ),(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM v$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES')
/

Run the below sqls to check the status on standby database.

set line 200
col DEST_NAME for a50
col BINDING for a10
select PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
/

col MESSAGE for a150
select * from(select message from v$dataguard_status where message like '%Media Recovery Log%' order by message desc) where rownum<=5
/