Connect Amazon RDS from on premises gives ORA-12560: TNS:protocol adapter error

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

ORA-12560: TNS:protocol adapter error

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

I've created a new AWS RDS database and tried to connect the database using sqlplus from my laptop.

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

sqlplus

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

#########################################
# 1) Check the network connectivity
#########################################

Please check if the database inbound rules are allowed to connect this database.

Login to console and Choose RDS Under Database and then choose Instances

Select the Database you are trying to connect.

Click the Details Tab.

Click on the Security Groups. This will take you to the security groups page.

Click Inbound Tab and it shows its allowed to any ip.

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

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

#########################################
# 2) Dont use Single Quotes
#########################################

While trying to connect from Windows command line don't use single quotes.

=====================================================================================================================
Establish the connection now and it works good.
=====================================================================================================================

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

Connecting Amazon RDS from onpremis gives ORA-12170: TNS:Connect timeout occurred

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

ORA-12170: TNS:Connect timeout occurred

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

I've created a new AWS RDS database and tried to connect the database using sqlplus from my laptop.

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

sqlplus

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

#########################################
# 1) Check the network connectivity
#########################################

ORA-12170: TNS:Connect timeout occurred

Above error usually occurs when there is no network connectivity between source and target. As the connections in AWS RDS are controlled via Security Groups, lets check the Security Group Inbound Rules.

Login to console and Choose RDS Under Database and then choose Instances. Select the Database you are trying to connect.

Click the Details Tab.

Click on the Security Groups. This will take you to the security groups page.

Click Inbound Tab,

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

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

From above output we can see only 154.59.142.48/32 is allowed to login via 1521 port number.

So to connect from my laptop, i need to add my ip address or allow this instance to be accessible to everyone based upon your requirement.

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

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

#########################################
# 3) Add New Rule
#########################################

In "Inbound" tab, click Edit and a new Rule. Choose "Oracle-RDS" as type and in the source choose "Anywhere" (accessible to public)

Click Save and then the Inbound rules looks as below.

=====================================================================================================================
Establish the connection now and it works good.
=====================================================================================================================

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

Could not connect to the endpoint URL: "https://ec2.eu-west-1b.amazonaws.com/"

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

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

Error occured while performing issuing describe-instances command after connecting to ec2 instance via putty

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

aws ec2 describe-instances --region eu-west-1b

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

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

I'm a newbie to AWS at this time and didn't understand the concept of region and availability zones.

The region i've mentioned here is eu-west-1b. But that is the availability zone. The region name is "eu-west-1"

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

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

aws ec2 describe-instances --region eu-west-1

=====================================================================================================================
Now the command completed successfully.
=====================================================================================================================

Region & AZ's :

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html

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

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

Tablespace Quota's Missing..

Recently we did migration on one of the database from HP-UX to Linux. Post migration we have provided unlimited quotas on few of its tablespace.

Later some time it came in our compliance tool that user is having UNLIMITED TABLESPACE privilege granted.

So this privilege was revoked and later few days we observed that tablespace quota's were missing.

I've generated a scenario which explains this.

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

Tablespace Quota's Missing

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

Revoke unlimited tablespace from XDB;

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

#########################################
# Reproduce the Issue
#########################################

a) Grant unlimited quota on specific tablespace.

Alter user XDB quota unlimited on USER_DATA_TS01;

SQL> Alter user XDB quota unlimited on USER_DATA_TS01;
User altered.

b) Grant unlimited tablespace privilege to the user.

Grant unlimited tablespace to xdb;

SQL> Grant unlimited tablespace to xdb;
Grant succeeded.
SQL> 

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

#########################################
# Check the Quota
#########################################

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
USERNAME                       NAME                           QUOTA                           USED
------------------------------ ------------------------------ ------------------------- ----------
XDB                            USER_DATA_TS01                 Unrestricted                   57728

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

#########################################
# Scenario 1)
#########################################

Grant Unlimited Quota on Tablespace and Revoke UNLIMITED TABLESPACE privilege.

SQL> alter user XDB quota unlimited on USER_DATA_TS01;
User altered.
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
no rows selected
SQL> 

#### From this we can understand that revoking unlimited tablespace privilege removes unlimited tablespace quota on any/all tablespace to that user.

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

#########################################
# Scenario 2)
#########################################

Grant Specific(1gB) Quota on Tablespace and REVOKE UNLIMITED TABLESPACE privilege.

SQL>  Alter user  XDB quota 1g on USER_DATA_TS01;
User altered.
SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
USERNAME                       NAME                           QUOTA                           USED
------------------------------ ------------------------------ ------------------------- ----------
XDB                            USER_DATA_TS01                    1,048,576                   57728
SQL> revoke unlimited tablespace from xdb;
Revoke succeeded.

#### Check Quota

SQL> select username,tablespace_name name,decode(greatest(max_bytes, -1),-1,'Unrestricted',
to_char(max_bytes/1024, '999,999,990')) quota, bytes/1024 used from dba_ts_quotas where username in ('XDB') order by 1,2;
  2
no rows selected
SQL> 

#### From this we can understand that revoking unlimited tablespace privilege removes all quota's on all tablespace granted to that user.

=====================================================================================================================
So the bottom line is before revoking unlimited tablespace privilege from the user, make a note of the quota's that user has and then once revoked grant the tablespace quota's back.
=====================================================================================================================

ORA-09945: Unable to initialize the audit trail file, PRCD-1222 : Online relocation of database "ORALIN" failed but database was restored to its original state

I was testing the online relocation of RAC One Node database from host08 to host09 server. Command was run from host08 server.

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

ORA-09945: Unable to initialize the audit trail file

### Full Error

[oracle@host08 trace]$ srvctl relocate database -d ORALIN -n host09 -v
Configuration updated to two instances
Online relocation failed, rolling back to original state
Configuration reverted back to one instance
PRCD-1222 : Online relocation of database "ORALIN" failed but database was restored to its original state
PRCD-1129 : Failed to start instance ORALIN_2 for database ORALIN
PRCR-1064 : Failed to start resource ora.ORALIN.db on node host09
CRS-5017: The resource action "ora.ORALIN.db start" encountered the following error:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device
. For details refer to "(:CLSN00107:)" in "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log".
CRS-2674: Start of 'ora.ORALIN.db' on 'host09' failed
[oracle@host08 trace]$

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

Error occured while doing a relocation of database instance of a One Node RAC in 11.2.0.4.0 version to a different server

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

srvctl relocate database -d ORALIN -n host09 -v

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

#########################################
# 1) Check the Full Error
#########################################

The above error shows that there is some problem with the audit file creation.

Lets check oragent_oracle.log file in host09 server as the problem ocurred while relocating the instance to host09.

[oracle@host09 audit]$ vi "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log"
[oracle@host09 audit]$
2015-05-26 16:57:41.499: [ USRTHRD][2234124608]{0:1:24} CrsCmd::destroy
2015-05-26 16:57:41.500: [ora.orawin.orawindev.svc][2234124608]{0:1:24} [check] clsnUtils::error Exception type=2 string=
CRS-5017: The resource action "ora.orawin.orawindev.svc check" encountered the following error:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
. For details refer to "(:CLSN00109:)" in "/ofa/rac/app/oracle/grid/11.2.0.4/log/host09/agent/crsd/oraagent_oracle//oraagent_oracle.log".

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

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

Online relocation command showed that error is because of the audit. But we shouldnt take that the audit got filled up for ORALIN database.

From above "oragent_oracle.log" we can see that the error occurred on "ora.orawin.orawindev.svc". So the database in problem is orawin.

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

#########################################
# 3) Check the audit location & Filesystem
#########################################

If we can login to orawin database then check audit location. (Show parameter audit)

In my case, i'm unable to login to the database, so checked the filesystems which reached 100%

[oracle@host09 bin]$ df -h /ofa/oracle_11.2.0.4_home
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oracle-lv0205
                       20G   19G     0 100% /ofa/oracle_11.2.0.4_home
[oracle@host09 bin]$

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

#########################################
# 4) Check which folder is using more space
#########################################

[oracle@host09 oracle_11.2.0.4_home]$ du -sh *
14G     dbs

[oracle@host09 dbs]$ pwd
/ofa/oracle_11.2.0.4_home/dbs
[oracle@host09 dbs]$ ls arch*
arch1_19_871216596.dbf  arch1_31_871216596.dbf  arch1_43_871216596.dbf  arch1_51_871216596.dbf  arch1_60_871216596.dbf  arch1_70_871216596.dbf
arch1_20_871216596.dbf  arch1_32_871216596.dbf  arch1_44_871216596.dbf  arch1_52_871216596.dbf  arch1_61_871216596.dbf  arch1_71_871216596.dbf
arch1_21_871216596.dbf  arch1_33_871216596.dbf  arch1_45_871216596.dbf  arch1_53_871216596.dbf  arch1_62_871216596.dbf  arch1_72_871216596.dbf
arch1_22_871216596.dbf  arch1_34_871216596.dbf  arch1_46_871216596.dbf  arch1_54_871216596.dbf  arch1_63_871216596.dbf  arch1_73_871216596.dbf
arch1_23_871216596.dbf  arch1_35_871216596.dbf  arch1_47_871216596.dbf  arch1_55_871216596.dbf  arch1_64_871216596.dbf  arch1_74_871216596.dbf
arch1_24_871216596.dbf  arch1_36_871216596.dbf  arch1_4_870194590.dbf   arch1_56_871216596.dbf  arch1_65_871216596.dbf  arch1_75_871216596.dbf
arch1_25_871216596.dbf  arch1_37_871216596.dbf  arch1_4_870252683.dbf   arch1_57_871216596.dbf  arch1_66_871216596.dbf  arch1_76_871216596.dbf
arch1_26_871216596.dbf  arch1_38_871216596.dbf  arch1_4_870864967.dbf   arch1_5_870194590.dbf   arch1_67_871216596.dbf  arch1_77_871216596.dbf
arch1_27_871216596.dbf  arch1_39_871216596.dbf  arch1_4_870965663.dbf   arch1_5_870864967.dbf   arch1_6_870194590.dbf   arch1_78_871216596.dbf
arch1_28_871216596.dbf  arch1_40_871216596.dbf  arch1_48_871216596.dbf  arch1_5_870965663.dbf   arch1_6_870864967.dbf
arch1_29_871216596.dbf  arch1_41_871216596.dbf  arch1_49_871216596.dbf  arch1_58_871216596.dbf  arch1_68_871216596.dbf
arch1_30_871216596.dbf  arch1_42_871216596.dbf  arch1_50_871216596.dbf  arch1_59_871216596.dbf  arch1_69_871216596.dbf
[oracle@host09 dbs]$

=====================================================================================================================
If above we can see that the files are the archivelogs.
=====================================================================================================================

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

Backup and Delete the archive log files. (As this is the development database, i removed it without a backup)

[oracle@host09 dbs]$ rm arch*
[oracle@host09 dbs]$ df -h .
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oracle-lv0205
                       20G  8.1G   11G  43% /ofa/oracle_11.2.0.4_home
[oracle@host09 dbs]$

=====================================================================================================================
Now database relocation worked fine.
=====================================================================================================================

[oracle@host08 trace]$ srvctl relocate database -d ORALIN -n host09 -v
Configuration updated to two instances
Instance ORALIN_2 started
Services relocated
Waiting for up to 30 minutes for instance ORALIN_1 to stop ...
Instance ORALIN_1 stopped
Configuration updated to one instance
[oracle@host08 trace]$

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