Saturday, March 19, 2016

ORA-38788: More standby database recovery is needed


##########################
## Error
##########################

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

### Full Error

ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

##########################
#  Error Occurred
##########################

Error occured while turning flashback database on in 11.2.0.4.0 version of a standby database.

##########################
## Command Executed
##########################

Alter database flashback on;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Flashback Status in STandby
#########################################

Select name,DB_UNIQUE_NAME,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 FLASHBACK_ON
--------- ------------------------------ ------------------
DB01    FDB01                         NO

#### Turn on Flashback

SQL> Alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

This is a new standby database and it was created using active database duplication method. During duplication at the end of recovery, errors occurred which is why we are receiving the errors.

=====================================================================================================================

#########################################
# 3) Duplication Log
#########################################

executing command: SET until clause
Starting recover at 17-MAR-16
starting media recovery
media recovery failed
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/17/2016 18:33:17
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 standby start until change 2227661
ORA-00283: recovery session canceled due to errorsORA-19909: datafile 1 belongs to an orphan incarnationORA-01110: data file 1: '+SHAREDDATA02/fDB01/datafile/system.453.906748087'
Recovery Manager complete.

=====================================================================================================================

#########################################
# 4) Enable Mrp and see if it helps
#########################################

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> 

#########################################
# Alert Log
#########################################

Fri Mar 18 09:57:40 2016
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (FDB011)
Fri Mar 18 09:57:40 2016
MRP0 started with pid=41, OS id=6839
MRP0: Background Managed Standby Recovery process started (FDB011)
 started logmerger process
Fri Mar 18 09:57:45 2016
Managed Standby Recovery starting Real Time Apply
Fri Mar 18 09:57:45 2016
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 2227001) is orphaned on incarnation#=1
MRP0: Detected orphaned datafiles!
Recovery will possibly be retried after flashback...
Errors in file /ofa/u001/app/oracle/product/admin/FDB01/diag/rdbms/fDB01/FDB011/trace/FDB011_pr00_6891.trc:
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA02/fDB01/datafile/system.453.906748087'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 19909
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect from session
Fri Mar 18 09:58:06 2016
MRP0: Background Media Recovery process shutdown (FDB011)

=====================================================================================================================
If we can see from above, Datafile 1 is showing as orphan incarnation.
=====================================================================================================================

#########################################
# 5) Check Database Incarnation in Primary & Standby
#########################################

#### Primary Database

[oracle@host01 bin]$ . oraenv_DB01
The Oracle base remains unchanged with value /ofa/u001/app/oracle/product
[oracle@host01 bin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 18 09:59:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=2325795052)

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       DB01  2325795052       CURRENT 1          02-MAR-16
RMAN>

#### Standby Database

[oracle@host02 bin]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 18 10:00:11 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB01 (DBID=2325795052, not open)

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       DB01  2325795052       PARENT  1          02-MAR-16
2       2       DB01  2325795052       CURRENT 2216947    17-MAR-16
RMAN>

=====================================================================================================================
From Above output, we can see standby database is 2 incarnations. This actually happened during the time of duplication, it tries to catalog the files in SHAREDFRA02 as below,
=====================================================================================================================

searching for all files that match the pattern +SHAREDFRA02
List of Files Unknown to the Database
=====================================
File Name: +sharedfra02/snapcf_psoa03.f
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_1_seq_77.2069.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_3_seq_81.2070.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_2_seq_75.2071.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_1_seq_78.2072.906748295
File Name: +sharedfra02/FDB01/ARCHIVELOG/2016_03_17/thread_3_seq_82.2073.906748297
File Name: +sharedfra02/DRDB01/AUTOBACKUP/2016_03_17/s_906744772.2054.906744773
File Name: +sharedfra02/DRDB01/AUTOBACKUP/2016_03_15/s_906488122.826.906565615
File Name: +sharedfra02/DRDB01/FLASHBACK/log_1.1725.906565083
File Name: +sharedfra02/DRDB01/FLASHBACK/log_2.1484.906565087
File Name: +sharedfra02/DRDB01/FLASHBACK/log_3.1722.906565089
File Name: +sharedfra02/DRDB01/FLASHBACK/log_4.1720.906565093
File Name: +sharedfra02/DRDB01/FLASHBACK/log_5.2023.906733525
File Name: +sharedfra02/DRDB01/ARCHIVELOG/2016_03_17/thread_3_seq_64.1908.906681665
File Name: +sharedfra02/DRDB01/ARCHIVELOG/2016_03_17/thread_1_seq_60.1911.906681667
File Name: +sharedfra02/DRDB01/ARCHIVELOG/2016_03_17/thread_2_seq_58.1914.906681669

As it tries to catalog all the files in +SHAREDFRA02, the other database files will be skipped because it will have different DBID.

In our case, we have one more standby database in the same place called "DRDB01", which is also having same DBID which is of its primary.

So this is the reason, the datafile shows multiple incarnation as "DRDB01" database files are cataloged to "FDB01".

##########################
## Solution
##########################

As the standby database shows multiple incarnation, reset the incarnation of the database to the old one.

Reset database to incarnation 1;

RMAN> Reset database to incarnation 1;
database reset to incarnation 1
RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB01  2325795052       CURRENT 1          02-MAR-16
2       2       DB01  2325795052       ORPHAN  2216947    17-MAR-16
RMAN>

=====================================================================================================================
After database incarnation is reset, wait for around 5 minutes to get the database catch up. Now if you turn on the flashback it works.
=====================================================================================================================

[oracle@host03 bin]$ . oraenv_FDB01
The Oracle base remains unchanged with value /ofa/u001/app/oracle/product
[oracle@host03 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 18 10:16:05 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> Select name,DB_UNIQUE_NAME,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 FLASHBACK_ON
--------- ------------------------------ ------------------
DB01   FDB01                        NO

SQL> select distinct process from gv$managed_standby;

PROCESS
---------
ARCH
MRP0

SQL> Recover managed standby database cancel;
Media recovery complete.
SQL> Alter database flashback on;
Database altered.
SQL> Select name,DB_UNIQUE_NAME,flashback_on from v$database;
NAME      DB_UNIQUE_NAME                 FLASHBACK_ON
--------- ------------------------------ ------------------
DB01   FDB01                        YES

SQL> Recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select distinct process from gv$managed_standby;

PROCESS
---------
ARCH
MRP0

SQL>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================



Wednesday, March 16, 2016

ORA-39083: Object type PASSWORD_HISTORY failed to create with error:




##########################
## Error
##########################

ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string

### Full Error

Processing object type DATABASE_EXPORT/SCHEMA/PASSWORD_HISTORY
ORA-39083: Object type PASSWORD_HISTORY failed to create with error:
ORA-01861: literal does not match format string
Failing sql is:
 DECLARE SUBTYPE HIST_RECORD IS SYS.DBMS_PSWMG_IMPORT.ARRAYOFHISTORYRECORDS; HIST_REC HIST_RECORD; i number := 0; BEGIN i := i+1;  HIST_REC(i).USERNAME := 'IJV688'; H
IST_REC(i).PASSWORD := '2FDC0A236274214D'; HIST_REC(i).PASSWD_DATE := '2016/03/12 23:38:39'; i := i+1;  HIST_REC(i).USERNAME := 'IJV688'; HIST_REC(i).PASSWORD := '2FD
C0A236274214D'; HIST_REC(i).PASSWD_DATE := '0

##########################
#  Error Occurred
##########################

Error occured while importing a database dumpfile from 11.2 version to 10.2 version.

##########################
## Command Executed
##########################

Full Database Import

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reason for Failure
#########################################

The source database version is 11.2.0.4 and the target database version is 10.2.0.5.

So in 10g, the password history functions are different from 11g. So its quite obvious it has given error.

=====================================================================================================================

##########################
## Solution
##########################

While doing an import, use EXCLUDE=PASSWORD_HISTORY to avoid this issue.

$ more db1UK_full.par
JOB_NAME=db1R_FULL
directory=MIG_DIR
dumpfile=expdp_db1R_full_16Mar2016.dmp
logfile=impdp_db1R_to_db1UK_16Mar2016.log
parallel=4
FULL=Y
exclude=password_history
exclude=audit
$

=====================================================================================================================
After excluding password_history, the import worked good.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================



ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [AUDIT:"PUBLIC"."PUBLIC"]


##########################
## Error
##########################

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [AUDIT:"PUBLIC"."PUBLIC"]
ORA-01403: no data found

### Full Error

Processing object type DATABASE_EXPORT/AUDIT
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [AUDIT:"PUBLIC"."PUBLIC"]
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6409
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
c00000004724a490     15370  package body SYS.KUPW$WORKER
c00000004724a490      6436  package body SYS.KUPW$WORKER
c00000004724a490     12590  package body SYS.KUPW$WORKER
c00000004724a490      3397  package body SYS.KUPW$WORKER
c00000004724a490      7064  package body SYS.KUPW$WORKER
c00000004724a490      1340  package body SYS.KUPW$WORKER
c000000047235f18         2  anonymous block
Job "SYS"."db1_FULL" stopped due to fatal error at 16:16:31


##########################
#  Error Occurred
##########################

Error occured while importing a database dumpfile from 11.2 version to 10.2 version.

##########################
## Command Executed
##########################

Full Database Import

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reason for Failure
#########################################

The source database version is 11.2.0.4 and the target database version is 10.2.0.5.

While searching found that these may be because of the some internal bugs. Also we dont require the auditing information on this.

=====================================================================================================================

##########################
## Solution
##########################

As audit information is not required, i've excluded audit during import and it worked good.

$ more db1_full.par
JOB_NAME=db1_FULL
directory=MIG_DIR
dumpfile=expdp_db1_full_16Mar2016.dmp
logfile=impdp_db1_to_db1_16Mar2016.log
parallel=4
FULL=Y
exclude=audit
$

=====================================================================================================================
After excluding audit, the import worked good.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================