ORA-01017: invalid username/password; logon denied

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

ORA-01017: invalid username/password; logon denied

### Full Error

ERROR:
ORA-01017: invalid username/password; logon denied

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

Error occurred while trying to connect a database using RAC service name in 11.2.0.3.0 version

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

Connect using sqlplus using database service name

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

#########################################
# 1) Tnsping & Try Connecting Database over network
#########################################

C:\Users\abc123>tnsping siddb_test
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:06:51
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sidhost)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = siddb_SVC)))
OK (30 msec)
C:\Users\abc123>sqlplus sidusr/sidpass@siddb_test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:09:27 2016
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

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

#########################################
# 2) Connect from DB Server
#########################################

As the Remote connections using service name is failing. I tried logging directly to the database to verify if passwords are working fine.

[oracle@sidhost2 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 28 21:26:16 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> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      siddb
SQL> conn sidusr/sidpassConnected.SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      siddb
SQL>

So the password is the right password. But it doesnt work using remote connection only.

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

#########################################
# 3) Try connecting over remote without Service Name
#########################################

By default, every database will have a database service registered with the same database name. For eg:, IN siddb database, we have created a service called siddb_SVC. But a default service named "siddb" will be available in database and can be connected.

So i've modified the tnsnames to use default database service "siddb"

C:\Users\abc123>tnsping siddb_test
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:40:31
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = sidhost)(PORT = 1521)))
 (CONNECT_DATA = (SERVICE_NAME = siddb)))
OK (10 msec)
C:\Users\abc123>sqlplus sidusr/sidpass@siddb_test
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:40:35 2016
Copyright (c) 1982, 2010, 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> exit
Disconnected from 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
C:\Users\abc123>

Now i can connect database using default service "siddb" but not using "siddb_SVC"

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

#########################################
# 4) Check Database Listener
#########################################

As the remote connections happens via listener, lets check the listener services.

lsnrctl status service

[oracle@sidhost2 dbs]$ . oraenv_+ASM2
The Oracle base remains unchanged with value /ofa/u01/app/oracle/admin
[oracle@sidhost2 dbs]$ lsnrctl status service
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2016 11:35:32
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
TNS-01101: Could not find service name service
[oracle@sidhost2 dbs]$ lsnrctl status LISTENER
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-AUG-2016 11:35:38
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                18-AUG-2016 14:42:45
Uptime                    4 days 20 hr. 52 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ofa/u01/app/oracle/grid/11.2.0.4/network/admin/listener.ora
Listener Log File         /ofa/u01/app/oracle/admin/diag/tnslsnr/sidhost2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.22.333.444)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=111.22.333.555)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "siddev" has 1 instance(s).
...
Service "siddb" has 1 instance(s).
  Instance "siddb2", status READY, has 1 handler(s) for this service...
Service "siddb_SVC" has 2 instance(s).  Instance "SIDNEW", status READY, has 1 handler(s) for this service...  Instance "siddb2", status READY, has 1 handler(s) for this service...
[oracle@sidhost2 dbs]$

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

#########################################
# 5) Reason for Failure
#########################################

From the above listener service output we can see that "siddb_SVC" service has 2 instances registered "SIDNEW" & "siddb2".

siddb2 seems to be right service. But SIDNEW is another database instance.

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

#########################################
# 6) Check Database Service
#########################################

[oracle@sidhost2 dbs]$ srvctl status service -d SIDNEW
[oracle@sidhost2 dbs]$ srvctl config database -d SIDNEW
Database unique name: SIDNEW
Database name: SIDNEW
Oracle home: /ofa/u001/app/oracle/product/11.2.0.4
Oracle user: oracle
Spfile: +SHAREDDATA07/SIDNEW/spfileSIDNEW.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: SIDNEW
Database instances: SIDNEW1,SIDNEW,SIDNEW3
Disk Groups: SHAREDDATA07,SHAREDFRA01,SHAREDREDO01,SHAREDREDO02,SHAREDREDO03
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@sidhost2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Aug 23 11:38:57 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> sho parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      siddb_SVC
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SIDNEW
SQL>

From above output, we can clearly see that service name was registered wrongly in a different database.

One of our member in the DBA team, has created SIDNEW database using "siddb" template but didn't change all the parameters which caused this confusion.

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

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

#### Modify the database service name.

SQL> Alter system set service_names='SIDNEW_SVC' sid='*';
System altered.
SQL> sho parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      SIDNEW_SVC
SQL> sho parameter db_n
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SIDNEW
SQL>

=====================================================================================================================
After modifying the service name, the connections are working good.
=====================================================================================================================

C:\Users\abc123>sqlplus sidusr/sidpass@siddb
SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 23 11:43:27 2016
Copyright (c) 1982, 2010, 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> exit
Disconnected from 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
C:\Users\abc123>tnsping siddb
TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 23-AUG-2016 11:43:34
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\Program Files (x86)\Oracle 11g\11g\network\admin\sqlnet.ora
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sidhost)(PORT=1521))(LOAD_BALANCE
=yes)(FAILOVER=on))(CONNECT_DATA=(SERVICE_NAME=siddb_SVC)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=60)(DELAY=10))))
OK (20 msec)
C:\Users\abc123>

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

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

We have a created a NFS mountpoint using Isilon storage which is shared in HP-UX and Linux Cluster servers. Creating file is working fine and also datapump also works good.

But when we try to store oracle database file (Datafiles) we are receiving the error.

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

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

### Full Error

channel ORA_DISK_4: starting datafile copy
input datafile file number=00008 name=/ofa/u001/SIDNAME/oradata/datafiles1/SIDNAME_tbs_ts1_01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_4 channel at 08/16/2016 23:46:02
ORA-19504: failed to create file "/nfs_mount_name/SIDNAME/bkp/tbs_TS1_8.tf"ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 12

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

Error occured while performing prepare phase using XTTS method in 11.2.0.4.0 version

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

$ORACLE_HOME/perl/bin/perl xttdriver.pl -p

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

#########################################
# 1) Check the file system mount Options
#########################################

cat "/etc/fstab"| grep -i mig

# cat "/etc/fstab"| grep -i mig
#----Oracle Migration-----
NASHOST:/ifs/nfspace /nfs_mount_name nfs defaults 0 0
#

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

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

The file system is mounted with the default options which will work for a normal file system usage. But to store oracle database datafiles, we need to add specific mount options.

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

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

Update /etc/fstab with the below options for HP-UX servers.

rw,bg,vers=3,proto=tcp,noac,forcedirectio,hard,nointr,timeo=600,rsize=32768,wsize=32768

# cat "/etc/fstab"| grep -i mig
#----Oracle Migration-----
#NASHOST:/ifs/nfspace /nfs_mount_name nfs defaults 0 0
NASHOST:/ifs/nfspace /nfs_mount_name nfs rw,bg,vers=3,proto=tcp,noac,forcedirectio,hard,nointr,timeo=600,rsize=32768,wsize=32768 0 0#

=====================================================================================================================
Unmount and Remount the File system. After the Prepare script worked good.
=====================================================================================================================

# umount /nfs_mount_name
# mount /nfs_mount_name
# bdf /nfs_mount_name
Filesystem          kbytes    used   avail %used Mounted on
NASHOST:/ifs/nfspace
                   5368709120 2783611400 2585097720   52% /nfs_mount_name
# 

$ pwd
/nfs_mount_name/SIDNAME/bkp
$ ls -ltr
total 352044008
-rw-r-----   1 oracle     dba        10485768192 Aug 17 11:21 tbs_TS1_23.tf
-rw-r-----   1 oracle     dba        31457288192 Aug 17 11:37 tbs_TS1_9.tf
$

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

Diagnostic Collection for a RAC server

When we raise a SR to oracle, they will require Diagnostic collection of log files for troubleshooting for that specific timelines when the error has occurred.

To collect diag we need to run the below procedure on all the nodes. So oracle recommends to use TFA. This note covers only diag collection on each node.

Below note is for 11.2 onwards.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Info before start the diag collection
#########################################

User : root (As this collect OS diag logs, its recommended to run as root user else we will be able to capture all the info's)
ORACLE_HOME : ASM Grid Home
Script Used : diagcollection.sh (Inbuilt Script comes as a part of Grid software Installation)
Temporary Directory : /tmp/srini (To Store the generated files.)
Time Stamp when the
Error has occurred : 08/16/201614:20:00 ( Format is MM/DD/YYYY24HH:MM:SS)
Duration until you
want to capture : 2 Hours (Capture 2 hours from the time of error occurred)

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

#########################################
# 2) Run the Diag Collection
#########################################

Below Command need to run on all the nodes of the cluster as root user.

cd /tmp/srini

/ofa/u001/app/oracle/grid/11.2.0.4/bin/diagcollection.sh --chmos --incidenttime 08/16/201614:20:00 --incidentduration 02:00

Where,

GRID_HOME=/ofa/u001/app/oracle/grid/11.2.0.4

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

#########################################
# 3) Generated Files
#########################################

[root@SIDHOST srini]# ls -ltr
total 29148
-rw-r----- 1 root root   290141 Aug 16 16:41 sysconfig_SIDHOST_20160816_1639.txt
-rw-r----- 1 root root 29514879 Aug 16 16:41 osData_SIDHOST_20160816_1639.tar.gz
[root@SIDHOST srini]#

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

#########################################
# 4) Change OWnership
#########################################

As the files are generated as root user, i need it to change as oracle to FTP and upload to ORacle support.

chmod -R oracle:dba /tmp/srini/

[root@SIDHOST srini]# ls -ltr
total 29148
-rw-r----- 1 oracle dba   290141 Aug 16 16:41 sysconfig_SIDHOST_20160816_1639.txt
-rw-r----- 1 oracle dba 29514879 Aug 16 16:41 osData_SIDHOST_20160816_1639.tar.gz
[root@SIDHOST srini]# 

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

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

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

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

### Full Error

SQL> Truncate table SCOTT.EMP;
Truncate table SCOTT.EMP
                                *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

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

Error occured while trying to truncate a table in 11.2.0.4.0 version

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

Truncate table SCOTT.EMP;

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

#########################################
# 1) Check who is using the object
#########################################

set lines 200
col owner for a15
col object_name for a25
col ORACLE_USERNAME for a15
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID, lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS, lo.LOCKED_MODE
from   dba_objects do, v$locked_object lo where  do.OBJECT_ID = lo.OBJECT_ID and    do.OWNER = 'SCOTT' and    do.OBJECT_NAME = 'EMP';

OWNER           OBJECT_NAME                OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME                   PROCESS                  LOCKED_MODE
--------------- ------------------------- ---------- ---------- --------------- ------------------------------ ------------------------ -----------
SCOTT EMP             16036        105 SYS             oracle                         29849                              3
SCOTT EMP             16036        513 SYS             oracle                         18361                              3

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

#########################################
# 2) Session Details
#########################################

set lines 200
col username for a25
col osuser for a15
col program for a40
col machine for a20
col status for a10
select inst_id,sid,serial#,username,osuser,program,machine,status,floor(last_call_et/60) "AIM",logon_time from gv$session where sid in ('105','513') order by last_call_et desc;

INST_ID     SID    SERIAL# USERNAME                  OSUSER          PROGRAM                                  MACHINE              STATUS            AIM LOGON_TIM
------- ------- ---------- ------------------------- --------------- ---------------------------------------- -------------------- ---------- ---------- ---------
      1     513       5889 SYS                       oracle          sqlplus@SIDHOST (TNS V1-V3)             SIDHOST             INACTIVE         6983 11-AUG-16
      1     105      12623 SYS                       oracle          sqlplus@SIDHOST (TNS V1-V3)             SIDHOST             INACTIVE         6902 11-AUG-16

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

#########################################
# 3) See what queries they are running
#########################################

Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=105);

SQL> Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=513);

no rows selected

SQL> Select sql_text from v$sqlarea where (address,hash_value) in (select sql_Address,sql_hash_value from v$session where sid=105);

no rows selected

SQL>

#########################################
# 4) Reason for Failure
#########################################

From output of Step 1), we can see that those two sessions have tried accessing that object. Possibly modifying that table but its INACTIVE. While checking found that one of the developer logged in and tried some delete statement and didnt log off.

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

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

Kill those sessions if not required, or request the users to logoff from the sessions.

In my case i've killed the sessions as its not required.

Alter system kill session '513,5889' immediate;
Alter system kill session '105,12623' immediate;

SQL> Alter system kill session '513,5889' immediate;
System altered.
SQL> Alter system kill session '105,12623' immediate;
System altered.

=====================================================================================================================
Truncate command worked good after clearing the session.
=====================================================================================================================

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

SP2-0332: Cannot create spool file.

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

SP2-0332: Cannot create spool file.

### Full Error

SQL>  spool /u01/pass/$ORACLE_SID_Pass.log
SP2-0332: Cannot create spool file.

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

Error occured while trying to spool a file with $ORACLE_SID in 11.2.0.4.0 version in HP-UX Platform

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

spool /u01/pass/$ORACLE_SID_Pass.log

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

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

The output we are looking from spool command is "dbname_Pass.log". Here we are using $ symbol which seems to be a OS Limitation.

So to eliminate this error, we need to use ${VARIABLE_NAME}

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

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

spool /u01/pass/${ORACLE_SID}_Pass.log

ls -ltr /u01/pass

SQL> !ls -ltr /u01/pass
total 204
-rw-r-----   1 oracle     nogroup      12113 Jun 17 10:33 DBL_Pass.log
-rw-r-----   1 oracle     nogroup       3922 Jun 20 16:48 DBW_Pass.log

SQL>

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

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
=====================================================================================================================

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
=====================================================================================================================

ORA-14063: Unusable index exists on unique/primary constraint key

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

ORA-14063: Unusable index exists on unique/primary constraint key

### Full Error

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14063: Unusable index exists on unique/primary constraint key
Failing sql is:
ALTER TABLE "ABC_OWNER"."ABC_TABLE" ADD CONSTRAINT "ABC_TABLE_PK" PRIMARY KEY ("RULE_NAME") USING INDEX "ABC_OWNER"."ABC_TABLE_PK"  ENABLE
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14063: Unusable index exists on unique/primary constraint key

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

Error occured while doing a full import in 11.2.0.4.0 version

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

Schema level import command.

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

#########################################
# 1) Check index status in Source Database
#########################################

#### Check the indexes which are not VALID in Source Database.

SET LINES 200
col owner for a15
col index_type for a15
col tablespace_name for a15
col table_owner for a15

Select owner,index_name,index_type,table_owner,table_name,table_type,tablespace_name,status,LAST_ANALYZED from dba_indexes where STATUS='UNUSABLE';

OWNER           INDEX_NAME                     INDEX_TYPE      TABLE_OWNER     TABLE_NAME                     TABLE_TYPE  TABLESPACE_NAME STATUS     LAST_ANAL
--------------- ------------------------------ --------------- --------------- ------------------------------ ----------- --------------- ---------- ---------
ABC_OWNER       ABC_TABLE_PK               NORMAL          ABC_OWNER       ABC_TABLE                  TABLE       IDX_TS1    UNUSABLE

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

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

Error is occuring because in the source database itself the indexes are in UNUSABLE state, so when we are trying to import in target database it fails to get created.

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

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

Rebuild all the UNUSABLE indexes in source database and then perform an export in source and import in target.

@@@@ Rebuild Indexes

set pages 100
select 'Alter index ' || owner||'.' || index_name ||' rebuild online parallel 8;' from dba_indexes where STATUS='UNUSABLE';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINEPARALLEL8;'
----------------------------------------------------------------------------------------------------
Alter index ABC_OWNER.ABC_TABLE_PK rebuild online parallel 8;

=====================================================================================================================
After this import worked good.
=====================================================================================================================

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

ORA-00990: missing or invalid privilege, GRANT GLOBAL REWRITE TO "ABC_OWNER"

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

ORA-00990: missing or invalid privilege
Failing sql is:
GRANT GLOBAL REWRITE TO "ABC_OWNER"

### Full Error

ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-00990: missing or invalid privilege
Failing sql is:
GRANT GLOBAL REWRITE TO "ABC_OWNER"

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

Error occured while import of database in 11.2.0.4.0 version

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

Schema level import...

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

#########################################
# 1) Check Database registry
#########################################

set lines 200
col action_time for a30
col action for a25
col namespace for a9
col version for a10
col id for 99999999
col comments for a25
col bundle_series for a25
select * from registry$history;

ACTION_TIME                    ACTION                    NAMESPACE VERSION           ID COMMENTS                  BUNDLE_SERIES
------------------------------ ------------------------- --------- ---------- --------- ------------------------- -------------------------
22-JAN-07 04.03.57.000000 PM   APPLY                                            5689799 CPUJan2007
13-AUG-08 11.42.39.793310 AM   UPGRADE                   SERVER    10.2.0.3.0           Upgraded from 8.1.7.0.0
29-JAN-09 08.23.06.616350 PM   CPU                       SERVER    10.2.0.3.0   7592354 CPUJan2009
27-MAY-09 10.15.51.127263 PM   APPLY                     SERVER    10.2.0.4           4 CPUApr2009                CPU
27-MAY-09 12.56.09.358348 AM   UPGRADE                   SERVER    10.2.0.4.0           Upgraded from 10.2.0.3.0
01-AUG-09 04.06.44.638446 PM   APPLY                     SERVER    10.2.0.4           5 CPUJul2009                CPU
31-OCT-09 05.23.10.775509 PM   APPLY                     SERVER    10.2.0.4           6 CPUOct2009                CPU
24-APR-10 03.34.35.916439 PM   APPLY                     SERVER    10.2.0.4           7 CPUApr2010                CPU
27-JUL-10 07.47.22.432114 PM   APPLY                     SERVER    10.2.0.4           8 CPUJul2010                CPU
14-NOV-10 08.56.47.965186 AM   APPLY                     SERVER    10.2.0.4           9 CPUOct2010                CPU
14-NOV-10 08.59.00.083118 AM   CPU                                              6452863 view recompilation
30-JAN-11 07.49.41.612062 AM   APPLY                     SERVER    10.2.0.4          10 CPUJan2011                CPU
20-MAY-11 11.13.20.184868 AM   APPLY                     SERVER    10.2.0.4          11 CPUApr2011                CPU
10-NOV-11 06.31.40.801048 PM   VIEW RECOMPILE                                   8289601 view recompilation
10-NOV-11 06.31.40.830187 PM   UPGRADE                   SERVER    10.2.0.5.0           Upgraded from 10.2.0.4.0
24-JAN-12 02.57.57.932675 PM   APPLY                     SERVER    10.2.0.5           6 CPUJan2012                CPU
24-APR-12 07.11.15.922326 PM   APPLY                     SERVER    10.2.0.5           7 CPUApr2012                CPU
24-JUL-12 09.14.33.976507 PM   APPLY                     SERVER    10.2.0.5           8 CPUJul2012                CPU
23-OCT-12 02.30.22.601483 PM   APPLY                     SERVER    10.2.0.5           9 CPUOct2012                CPU
24-JAN-13 01.22.55.748863 PM   APPLY                     SERVER    10.2.0.5          10 CPUJan2013                CPU
24-APR-13 11.05.11.000911 AM   APPLY                     SERVER    10.2.0.5          11 CPUApr2013                CPU
24-JUL-13 05.15.10.280957 PM   APPLY                     SERVER    10.2.0.5          12 CPUJul2013                CPU
01-NOV-13 04.54.12.078568 PM   APPLY                     SERVER    10.2.0.5          12 CPUJul2013                CPU
10-SEP-14 12.44.10.156902 PM   VIEW INVALIDATE                                  8289601 view invalidation
10-SEP-14 12.44.28.942689 PM   UPGRADE                   SERVER    11.2.0.4.0           Upgraded from 10.2.0.5.0
10-SEP-14 12.51.57.396320 PM   APPLY                     SERVER    11.2.0.4           0 Patchset 11.2.0.2.0       PSU
18-OCT-14 10.26.17.616975 AM   APPLY                     SERVER    11.2.0.4           4 CPUOct2014                CPU
03-FEB-15 10.20.24.622435 PM   APPLY                     SERVER    11.2.0.4           5 CPUJan2015                CPU
18-APR-15 10.59.02.947126 PM   APPLY                     SERVER    11.2.0.4           6 CPUApr2015                CPU
18-JUL-15 10.21.02.380054 PM   APPLY                     SERVER    11.2.0.4           7 CPUJul2015                CPU
24-OCT-15 10.47.20.282415 PM   APPLY                     SERVER    11.2.0.4           8 CPUOct2015                CPU
22-JAN-16 09.47.02.078327 PM   APPLY                     SERVER    11.2.0.4      160119 CPUJan2016                CPU
Elapsed: 00:00:00.04
12:33:16 SQL>

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

#########################################
# 2) Check Database System Privilege
#########################################

Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;

13:20:42 SQL> Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -211 GLOBAL REWRITE                                    0
      -210 QUERY REWRITE                                     0

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

#########################################
# 3) Reason for Failure
#########################################

From Step 1), we can see that Database was upgraded from 8.1.7 version.
From Step 2), System privilege name is "GLOBAL REWRITE".

The GLOBAL REWRITE and REWRITE privileges were introduced in v8.1.3.  Then the privileges were renamed to GLOBAL QUERY REWRITE and QUERY REWRITE in v8.1.5.

The upgrade script does update both system privilege names when upgrading the database to v8.1.5.  So, the database was probably upgraded without this step being completed.

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

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

#### Solution would be to update the rows with the new privilege in Source Database

update SYSTEM_PRIVILEGE_MAP
 set name='QUERY REWRITE' where name='REWRITE';

update SYSTEM_PRIVILEGE_MAP
 set name='GLOBAL QUERY REWRITE' where name='GLOBAL REWRITE';

Commit;

=====================================================================================================================
I didnt update the rows in Source because we are migrating the database to 11.2.0.4 which already has the right system privilge name to it.
=====================================================================================================================

Target :

SQL>  Select * from SYSTEM_PRIVILEGE_MAP where name like '%REWRITE%' order by name;
 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -211 GLOBAL QUERY REWRITE                              0
      -210 QUERY REWRITE                                     0

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