Thursday, November 15, 2012
ORA-01274 - Resolve Archive Gaps in Standby Database
ORA-01274 Resolve Gaps in Standby Database when STANDBY_FILE_MANAGEMENT is set to MANUAL
============================================================
************** Step By Step Procedure **************
#########################################
# Issue
#########################################
Standby was not in SYNC with primary because STANDBY_FILE_MANAGEMENT is set to MANUAL. On 09-Nov-2012 two new datafiles were added in Primary, because of this manual
settings the files got created in $ORACLE_HOME/dbs location instead of Diskgroup.
#########################################
# Alert Log Errors
#########################################
Fri Nov 09 11:19:20 2012
File #6 added to control file as 'UNNAMED00006' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file /u01/app/oracle/diag/rdbms/STDBY_gvl/STDBY_1/trace/STDBY_1_pr00_22594.trc:
ORA-01274: cannot add datafile '+STDBY_DATA/STDBY_stl/datafile/STDBY_data01.272.798895157' - file could not be created
#########################################
# 1) Database name & Mode
#########################################
set lines 200
col name for a15
col instance_name for a15
col log_mode for a10
col open_mode for a10
col database_Role for a20
col protection_mode for a20
col protection_level for a20
col remote_archive for a15
col flashback_on for a12
Select name,instance_name,dbid,log_mode,open_mode,database_role,protection_mode,protection_level,remote_archive,flashback_on from v$database,v$instance;
NAME INSTANCE_NAME DBID LOG_MODE OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL REMOTE_ARCHIVE FLASHBACK_ON
--------------- --------------- ---------- ---------- ---------- -------------------- -------------------- -------------------- --------------- ------------
STDBY STDBY_1 1579933494 ARCHIVELOG MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE ENABLED NO
SQL> select * from v$dataguard_config;
DB_UNIQUE_NAME
------------------------------
STDBY_gvl
STDBY_stl
#########################################
# 2) Gaps in Primary & Standby
#########################################
###############
Primary >>>>>>>
###############
### Find GAps
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP
BY THREAD#) ORDER BY 1;
Select thread#,max(sequence#) from v$archived_log group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 97
###############
Standby >>>>>>>
###############
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 97 83 14
#########################################
# 3) Place Standby in MRP mode
#########################################
SQL> alter database recover managed standby database disconnect from session;
Database altered.
MRP process termined with the below error in Alert log.
Wed Nov 14 04:58:33 2012
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/STDBY_gvl/STDBY_1/trace/STDBY_1_pr00_32449.trc:
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006'
Slave exiting with ORA-1111 exception
#########################################
# 4) Check datafile Status
#########################################
###############
Primary >>>>>>>
###############
set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;
FILE# NAME Size in MB CREATION_ STATUS LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
1 +STDBY_DATA/STDBY_stl/datafile/system.256.796313651 760 17-SEP-11 SYSTEM
2 +STDBY_DATA/STDBY_stl/datafile/sysaux.257.796313653 840 17-SEP-11 ONLINE
3 +STDBY_DATA/STDBY_stl/datafile/undotbs1.258.796313653 80 17-SEP-11 ONLINE
4 +STDBY_DATA/STDBY_stl/datafile/users.259.796313653 5 17-SEP-11 ONLINE
5 +STDBY_DATA/STDBY_stl/datafile/undotbs2.266.796313803 25 10-OCT-12 ONLINE
6 +STDBY_DATA/STDBY_stl/datafile/STDBY_omcbc_data01.272.798895451 1024 09-NOV-12 ONLINE
7 +STDBY_DATA/STDBY_stl/datafile/STDBY_omcbc_indx01.273.798895897 1024 09-NOV-12 ONLINE
###############
Standby >>>>>>>
###############
set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;
FILE# NAME Size in MB CREATION_ STATUS LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
1 +STDBY_DATA/STDBY_gvl/datafile/system.259.796323701 750 17-SEP-11 SYSTEM
2 +STDBY_DATA/STDBY_gvl/datafile/sysaux.262.796323701 830 17-SEP-11 RECOVER
3 +STDBY_DATA/STDBY_gvl/datafile/undotbs1.260.796323701 80 17-SEP-11 ONLINE
4 +STDBY_DATA/STDBY_gvl/datafile/users.263.796323701 5 17-SEP-11 ONLINE
5 +STDBY_DATA/STDBY_gvl/datafile/undotbs2.261.796323701 25 10-OCT-12 ONLINE
6 /u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006 0 09-NOV-12 RECOVER
#########################################
# 5) Check the file which is wrongly created
#########################################
set lines 200
col name for a75
select file#, name, status, creation_time from v$datafile where name like '%UNNAMED%';
FILE# NAME STATUS CREATION_
---------- --------------------------------------------------------------------------- ------- ---------
6 /u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006 RECOVER 09-NOV-12
SQL> sho parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';
NAME TOTAL_MB FREE_MB
--------------------------------------------------------------------------- ---------- ----------
STDBY_DATA 245823 242064
STDBY_RECO 65602 58545
#########################################
# 6) Rename/create Datafile to the correct Filename (Diskgroup)
#########################################
SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_6/dbs/UNNAMED00006' as '+STDBY_DATA\' size 1024m;
Database altered.
Verify the Filename is changed or not
set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;
FILE# NAME Size in MB CREATION_ STATUS LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
6 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_data01.281.799315125 1024 09-NOV-12 ONLINE
#########################################
# 7) Change the STANDBY_FILE_MANAGMENT to AUTO
#########################################
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;
System altered.
#########################################
# 8) Start the MRP
#########################################
Alter database recover managed standby database disconnect from session;
SQL> Alter database recover managed standby database disconnect from session;
Database altered.
#########################################
# 9) Check the MRP status
#########################################
set lines 200
Select process,client_process,thread#,sequence#,block#,blocks,status from v$managed_standby order by status;
PROCESS CLIENT_P THREAD# SEQUENCE# BLOCK# BLOCKS STATUS
--------- -------- ---------- ---------- ---------- ---------- ------------
MRP0 N/A 1 84 106642 122977 APPLYING_LOG
ARCH ARCH 1 97 124928 1886 CLOSING
ARCH ARCH 1 96 131072 969 CLOSING
ARCH ARCH 1 98 122880 98 CLOSING
ARCH ARCH 0 0 0 0 CONNECTED
RFS ARCH 0 0 0 0 IDLE
RFS LGWR 1 99 1090 1 IDLE
RFS UNKNOWN 0 0 0 0 IDLE
Now the logs started applying and GAPS are resolved now.
#########################################
# 10) Check the datafile status
#########################################
set lines 200
col name for a75
Select file#,name,bytes/1024/1024 "Size in MB",creation_time,status,last_time from v$datafile;
FILE# NAME Size in MB CREATION_ STATUS LAST_TIME
---------- --------------------------------------------------------------------------- ---------- --------- ------- ---------
1 +STDBY_DATA/STDBY_gvl/datafile/system.259.796323701 760 17-SEP-11 SYSTEM
2 +STDBY_DATA/STDBY_gvl/datafile/sysaux.262.796323701 840 17-SEP-11 ONLINE
3 +STDBY_DATA/STDBY_gvl/datafile/undotbs1.260.796323701 80 17-SEP-11 ONLINE
4 +STDBY_DATA/STDBY_gvl/datafile/users.263.796323701 5 17-SEP-11 ONLINE
5 +STDBY_DATA/STDBY_gvl/datafile/undotbs2.261.796323701 25 10-OCT-12 ONLINE
6 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_data01.281.799315125 1024 09-NOV-12 ONLINE
7 +STDBY_DATA/STDBY_gvl/datafile/STDBY_omcbc_indx01.282.799315233 1024 09-NOV-12 ONLINE
###############
Standby >>>>>>>
###############
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 98 98 0
SQL> sho parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string AUTO
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment