Day: March 5, 2016

ORA-19909: datafile 1 belongs to an orphan incarnation

One database might have multiple incarnations.

The following errors occurred in a standby database alert log :

MRP0: Detected orphaned datafiles!
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+DATA/PROD/datafile/system.238.875096219

Check standby database incarnation :

RMAN> list incarnation;

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   3779924890 PARENT 1           08-01-2015 10:00
2      2       PROD   3779924890 CURRENT 2620491565 01-02-2016 12:01

Reset database to old incarnation :

RMAN> reset database to incarnation 1; 

database reset to incarnation 1 


RMAN> list incarnation;

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    3779924890 CURRENT 1          08-01-2015 10:00
2       2       PROD    3779924890 ORPHAN  2620491565 01-02-2016 12:01

Then everything is working fine.

Advertisement

How to Understand and Use ESTIMATE and ESTIMATE_ONLY Parameters in DataPump

Be aware LOB segment size is not included in the estimation size of datapump.

ESTIMATE Parameter

The value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS.

BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is  the least accurate because of:

  • The table was created with a much bigger initial extent size than was needed for the actual table data
  • Many rows have been deleted from the table, or a very small percentage of each block is used.

STATISTICS: The estimated space used is calculated by using statistics for each table. If the table has been recently analyzed, the “estimate=statistics” would be the most accurate.

ESTIMATE_ONLY Parameter

Using estimate_only parameter will not generate the dump file other than a logfile .

The value for this parameter is either Y (yes) or N (no = default).

Y: Export estimates the space that would be consumed, but quits without actually performing the export operation.
N: Export does not only estimate, it performs an actual export of data, too.

BLOB EXCLUDED FROM ESTIMATION

If a table has LOBs, ESTIMATE dump file size does NOT take LOB size into consideration.

The table segment size is 34.43GB, and the blog segment size is 38.3GB. It seems the dadapump estimation size excludes blob segment size.

SQL> select bytes/1024/1024/1024 
       from dba_segments 
       where owner='TESTUSER' 
         and segment_name='TEST' 
         and segment_type='TABLE';

BYTES/1024/1024/1024
--------------------
 34.4365234

SQL> desc TESTUSER.TEST
 Name    Null?     Type
 ------- -------  ----------
 NAME    NOT NULL VARCHAR2(30)
 CREATED NOT NULL DATE
 ......
 ......
 DOC              BLOB
 ......
 ......


SQL> select owner,table_name,column_name,segment_name 
       from dba_lobs 
      where owner='TESTUSER' and table_name='TEST';

OWNER      TABLE_NAME  COLUMN_NAME       SEGMENT_NAME 
---------- ----------  ----------------  ------------------------
TESTUSER   TEST        SESSIONITEMLONG   SYS_LOB0000062845C00010$

SQL> select owner, segment_Name,bytes/1024/1024/1024 
       from dba_segments 
      where segment_name='SYS_LOB0000062845C00010$' 
        and owner='TESTUSER';

OWNER       SEGMENT_NAME             BYTES/1024/1024/1024
----------- ------------------------ ------------------
TESTUSER    SYS_LOB0000062845C00010$  38.3


$ expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=BLOCKS tables=TESTUSER.TEST;

....
....
....
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 35.50 GB
Total estimation using BLOCKS method: 35.50 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:50:11

$expdp \"/ as sysdba\" directory=DATAPUMP_DIR ESTIMATE_ONLY=y ESTIMATE=STATISTICS tables=TESTUSER.TEST;
......
......
......
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. estimated "TESTUSER"."TEST" 28.96 GB
Total estimation using STATISTICS method: 28.96 GB
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:51:31

From the above test, we can see LOB segment size is excluded from estimation size.