Dec 12, 2016

ORA-19909: datafile 1 belongs to an orphan incarnation

When I tried to Flashback on in standby database then suddenly below message found in alert log and standby database not able to apply archive-logs also. So, here are messages from standby database alert log:

-- Message from alert log ( in standby database)

Mon Dec 12 12:43:24 2016
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (PROD)
Mon Dec 12 12:43:24 2016
MRP0 started with pid=26, OS id=22750
MRP0: Background Managed Standby Recovery process started (PROD)
 started logmerger process
Mon Dec 12 12:43:29 2016
Managed Standby Recovery not using Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 5983588137777) is orphaned on incarnation#=2
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /oracle/diag/rdbms/PROD/PROD/trace/PROD_pr00_22755.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u03/oradata/PROD/PROD/system01.dbf'
Recovery Slave PR00 previously exited with exception 19909
Completed: alter database recover managed standby database disconnect from session
Mon Dec 12 12:43:51 2016
MRP0: Background Media Recovery process shutdown (PROD)

RCA:

Primary and standby have different incarnations:

-- in standby db

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-NOV-2016 00:31:04
2       2       PROD  3680942556       PARENT  5982448719535 30-NOV-2016 22:31:15
4       4       PROD  3680942556       ORPHAN  5983354187673 09-DEC-2016 04:01:27
3       3       PROD  3680942556       CURRENT 5983354274164 10-DEC-2016 04:02:53

RMAN>


-- in primary db

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-NOV-2016 00:31:04
2       2       PROD  3680942556       CURRENT 5982448719535 30-NOV-2016 22:31:15

RMAN>


-- Solution:

You need to reset the standby database's incarnation to match the primary's:

-- in standby

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-NOV-2016 00:31:04
2       2       PROD  3680942556       PARENT  5982448719535 30-NOV-2016 22:31:15
4       4       PROD  3680942556       ORPHAN  5983354187673 09-DEC-2016 04:01:27
3       3       PROD  3680942556       CURRENT 5983354274164 10-DEC-2016 04:02:53

RMAN> reset database  to incarnation 2;

database reset to incarnation 2

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  3680942556       PARENT  5982315579513 30-NOV-2016 00:31:04
2       2       PROD  3680942556       CURRENT 5982448719535 30-NOV-2016 22:31:15
4       4       PROD  3680942556       ORPHAN  5983354187673 09-DEC-2016 04:01:27
3       3       PROD  3680942556       ORPHAN  5983354274164 10-DEC-2016 04:02:53

RMAN>

-- Verify MRP is active or not

$ ps -ef|grep mrp
oracle   23358 21699  0 12:56 pts/1    00:00:00 grep mrp
$

-- Verify standby database status and role

SQL> select name,open_mode ,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PROD      MOUNTED              PHYSICAL STANDBY

SQL>

-- now start MRP in standby database
$ !sql
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>

-- Verify the MRP process

$ ps -ef|grep mrp
oracle   23455     1  0 12:58 ?        00:00:00 ora_mrp0_PROD
oracle   24125 21699  0 13:11 pts/1    00:00:00 grep mrp
$

Now archive-logs are applying.

Reference
Metalink Note 554358.1 - MRP Recovery Failed With ORA-19909

Dec 3, 2016

Resolve Gaps in Standby with incremental backup


Sometimes due to some activity or archive log header corruption, standby database will be in out of synch and gap will come. This can be verified from queries or alert logs of standby database.

Note: Primary instance is in RAC ( 2-node) and standby is in Stand-alone server.

Real-time steps:

Example: ( from alert log)
…………………………
…………………………
Mon Oct 10 02:20:17 2016
Media Recovery Log /u02/prod_archivelogs/2016_10_10/thread_1_seq_142321.362.924828783
Media Recovery Waiting for thread 2 sequence 51848
Fetching gap sequence in thread 2, gap sequence 51848-51849
FAL[client]: Error fetching gap sequence, no FAL server specified
…………………………
…………………………

Here one archive log is missing, i.e., thread_2_seq_51848.xxx
So, Now you verify whether you have that archive log in production or not. If available then copy and catlog/ register it to recover again. Otherwise you can take an incremental backup and recover it. Here are the following steps:
Steps:
1.   At standby check the current scn.
sql> select to_char(current_scn) from v$database;
   5967451101100
2. Create a standby control file from Primary Database.
sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/incremental_bkp/standby_control.ctl';

3. Copy the standby controlfile to standby server.
$ scp -r -p '/u02/incremental_bkp/standby_control.ctl' oracle@160.192.1.2:/u03/incremental_bkp

4. Take incremental backup on primary starting from scn# of standby database
rman> Backup incremental from SCN 5969890030000 database tag='FOR_STANDBY' format '/u02/incremental_bkp/%d_%t_%s_%p';

Note: Take scn some less value than standby.
5. Move the backup pieces to the standby host

6. Shut down the standby database and replace the current controlfile with new one.
            cp control01.ctl control01.ctl_bkp
            cp control02.ctl control01.ctl_bkp
            cp standby_control.ctl control01.ctl
            cp standby_control.ctl control02.ctl

7. Now mount the standby database.
            sql> startup nomount;
            sql> alter database mount standby database;

8. If Primary and standby datafiles location are different then go with below steps.
            rman> catalog start with '/u02/flash_recovery_area/PROD/ORADATA';
            rman> switch database to copy;
            Some time we can get below error while above command try to execute.
Err:
RMAN> switch database to copy;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 05/30/2016 23:35:44
RMAN-06571: datafile 1 does not have recoverable copy
It happened if standby database is in different incarnation with primary.

In Standby:
RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  2886743190       PARENT  1          25-MAR-16
2       2       PROD  2886743190       PARENT  5965541899890 27-MAR-16
4       4       PROD  2886743190       ORPHAN  5969888275728 30-MAY-16
3       3       PROD  2886743190       CURRENT 5969888309840 30-MAY-16

In primary:

RMAN> list incarnation;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD  2886743190       PARENT  1          25-MAR-16
2       2       PROD  2886743190       CURRENT  5965541899890 27-MAR-16

NOW change the standby database incarnation which is there in primary.
In standby:

            RMAN> reset database to incarnation 2;
                        database reset to incarnation 2

            rman> switch database to copy;
            Now shutdown and mount the database again.

9.  Recover the standby database with incremental backup pieces.
            rman> crosscheck backup;
            rman> delete expired backup;
            rman> catalog start with ' /u03/incremental_bkp';
            rman> recover database noredo;

10. Now verify change in scn status.
            SQL> select to_char(current_scn) from v$database;
             CURRENT_SCN
                        ---------------------------
                        5970008638143

11. On the standby database, start the Managed Recovery Process.
            sql>alter database recover managed standby database disconnect from session;

Now recovery is started. MRP process also started. You can catalog the archivelogs. It will apply archivelogs automatically. 


Translate >>