ORA-31693: Table data object "SCHEMA"."TABLE" failed to load/unload and is being skipped due to error:

Error:

ORA-31693: Table data object ""."" failed to load/unload and is being skipped due to error:

Reason: 

The error is showing due to the backup dump file is not accessible from the the second node in the RAC


Solution:

Following example will help to resolve the issue.

expdp username/passwd tables=schema.table dumpfile=backup.dmp logfile=backup.log cluster=N

or

Shutdown Database on second node and execute the following command


expdp username/passwd tables=schema.table dumpfile=backup.dmp logfile=backup.lo

ORA-28000 "the account is locked" in the standby database

Scenario:

The user attempts to logon and gets an error ORA-28000 "the account is locked" in the primary database which is configure with Dataguard.
The database administrator unlock this user's account in the primary database.  Still, the user cannot connect to the standby, getting the same error ORA-28000 for the particular user, But the account status shows open in the standby.

In Standby :


SYS@ORCL > conn BNSUSER/sbl123
ERROR:
ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

SYS@ORCL > select username,account_status from dba_users where username='BNSUSER';

USERNAME    ACCOUNT_STATUS
--------------------------
BNSUSER    OPEN


Reason :


 This is because the standby database is open read-only and cannot update any tables.  When a user's account has to be locked on the standby database, it is locked only in memory there.

Solution:

A privileged user (sysdba, the database administrator) must logon to the standby and unlock the account there.  A message ORA-28015 "Account unlocked, but the database is open for read-only access" confirms that the account is now unlocked in the standby database.  From then on, the user can logon to the standby database without getting any error.

SYS@ORCL > ALTER USER BNSUSER ACCOUNT UNLOCK;
ALTER USER BNSUSER ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-28015: Account unlocked, but the database is open for read-only access

SYS@ORCL > conn BNSUSER/SBL123
Connected.

RMAN Active Duplicate on RAC fails with ORA-17629 and ORA-17627: ORA-12154

RMAN active duplication of RAC database fails on restart operation with the following error messages:

oracle@said$ rman target sys@orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 2 14:19:40 2017

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

target database Password:
connected to target database: ORCL (DBID=209740460)

RMAN> connect auxiliary sys@stdby

auxiliary database Password:
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 02-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=1921 instance=ORCL1 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwORCL2' auxiliary format
 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwORCL1'   ;
}
executing Memory Script

Starting backup at 02-APR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2689 instance=ULTIMUS1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/02/2017 14:20:46
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/02/2017 14:20:45
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server

RMAN> exit

Reason:

ORA-12154 is raised whenever the TNS alias is not found on tnsnames.ora

After verification of tnsanames.ora files from both installations on Target and Auxiliary we see all the proper entries are there Still the RMAN duplicate command fails on ORA-12154

The reason for ORA-12154 being raised is because the process is looking for tnsnames.ora in a different ORACLE_HOME on the Auxiliary side.   This different ORACLE_HOME on Auxiliary side is usually the GRID HOME.

On 11G RAC Customers can configure SCAN Listener to handle failover connections on DB and ASM instances.

When RMAN duplicate is executed it needs to shutdown/startup the database several times.  Once the DB is down, a TNS connection can get lost on next startup since the corresponding service associated with the instance is no longer available.  The connection from RMAN thus fails when looking for the tnsnames.ora file under the wrong home.

Resolution:

There are two ways to workaround this problem

1) Copy the tnsnames.ora file form the RDBMS Home to the GRID Home

2) Establish a direct connection instead of using a TNS alias.  For example:

RMAN> connect auxiliary sys/pwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myauxiliaryhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=QRM)(UR=A)))

For Example:

oracle@said$ rman target sys@orcl

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Apr 2 14:21:10 2017

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

target database Password:
connected to target database: ORCL (DBID=209740460)

RMAN> connect auxiliary sys/sys123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.250.75)(PORT=1599))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))

connected to auxiliary database: ORCL (not mounted)


RMAN> duplicate target database for standby from active database;


Hope this will work for you!!!!!!!