Problem - One Data file was missing at Standby. After restore from incremental backup archive logs were not being applied. While trying to register manually, it was giving message that file already registered. But on checking using select thread#, sequence#, archived, applied, status from v$archived_log;, it shows that files are not being applied but registered.
File# Name
47 E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF
From Alert Log
Alter database recover managed standby database disconnect from session
Fri Nov 20 20:42:34 2015
MRP0 started with pid=28, OS id=3512
started logmerger process
Fri Nov 20 20:42:39 2015
Managed Standby Recovery not using Real Time Apply
Fri Nov 20 20:42:39 2015
Errors in file e:\oracle\oracle_base\diag\diag\rdbms\testdbdr\testdbdr\trace\testdbdr_dbw0_2768.trc:
ORA-01157: cannot identify/lock data file 47 - see DBWR trace file
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
MRP0: Background Media Recovery terminated with error 1110
Errors in file e:\oracle\oracle_base\diag\diag\rdbms\testdbdr\testdbdr\trace\testdbdr_pr00_1604.trc:
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
ORA-01157: cannot identify/lock data file 47 - see DBWR trace file
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
Slave exiting with ORA-1110 exception
Errors in file e:\oracle\oracle_base\diag\diag\rdbms\testdbdr\testdbdr\trace\testdbdr_pr00_1604.trc:
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testbdr\testdbdr_DTL_IDX_1.DBF'
ORA-01157: cannot identify/lock data file 47 - see DBWR trace file
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
Recovery Slave PR00 previously exited with exception 1110
Errors in file e:\oracle\oracle_base\diag\diag\rdbms\testdbdr\testdbdr\trace\testdbdr_mrp0_3512.trc:
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
ORA-01157: cannot identify/lock data file 47 - see DBWR trace file
ORA-01110: data file 47: 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
Completed: Alter database recover managed standby database disconnect from session
==============================================
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Register archivelog F:\ORACLE_ARCHIVES\ARCH_DR_testPRD_1_131714_869157235.ARC already exists
ORA-16089 signalled during: alter database register logfile 'F:\oracle_archives\nrtprd\ARCH_DR_testPRD_1_131714_869157235.ARC'...
Background – It was added in v$datafile but physically it was not available. Although “standby_file_managment” server parameter was in “Auto” mode.
First created same datafile at standby and enabled MRP.
From Alert Log
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
alter database create datafile 47 as 'F:\DATAFILES\testdb\testdbdr_DTL_IDX_1.DBF'
ORA-1119 signalled during: alter database create datafile 47 as 'F:\DATAFILES\testb\testdbdr_DTL_IDX_1.DBF'...
Sat Nov 21 16:37:15 2015
Archived Log entry 727 added for thread 1 sequence 132439 ID 0xc2d39bf dest 1:
Sat Nov 21 16:37:18 2015
RFS[2]: Selected log 4 for thread 1 sequence 132440 dbid 184840561 branch 869157235
Sat Nov 21 16:38:02 2015
alter database create datafile 47 as 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
--
--
Completed: alter database create datafile 47 as 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF'
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
alter database recover managed standby database disconnect from session
At this stage recovery process started. But it was fetching wrong sequence.
Step 1 – Make particular datafile offline
SQL> alter database datafile 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF' offline drop;
(now Status was showing as Recover - select file#, name, status from v$datafile;)
From Alert Log
Sat Nov 21 19:23:45 2015
alter database datafile 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF' offline drop
Completed: alter database datafile 'E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF' offline drop
Step 2 – Connect to RMAN and list backups
RMAN> list backup
Step3 – Restore particular datafile from incremental backup
RMAN> restore datafile 47 from tag 'TAG20151119T205113';
From Alert Log
Sat Nov 21 19:48:13 2015
Full restore complete of datafile 47 E:\ORACLE\ORADATA\testdbdr\testdbdr_DTL_IDX_1.DBF. Elapsed time: 0:17:07
checkpoint is 919173201796
Doubtful command was given or not - RMAN> recover datafile 47 with current controlfile;
Step 4 – Recover the database
RMAN>Recover Database
Comments
Post a Comment