Tuesday, August 8, 2017

ORA-00261: log 11 of thread 1 is being archived or modified


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

ORA-00261: log 11 of thread 1 is being archived or modified

### Full Error

ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '+REDO01/oralin/onlinelog/group_11.1611.951155487'
ORA-00312: online log 11 thread 1: '+REDO02/oralin/onlinelog/group_11.1611.951155489'
ORA-00312: online log 11 thread 1: '+REDO03/oralin/onlinelog/group_11.1611.951155489'

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

Error occured while trying to drop standby redo log groups in dataguard standby database in 11.2.0.4.0 version

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

Alter database drop logfile group 11, group 14, group 18

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

#########################################
# 1) Reproduce the Error
#########################################

SQL> Alter database drop logfile group 11, group 14, group 18;
Alter database drop logfile group 11, group 14, group 18
*
ERROR at line 1:
ORA-00261: log 11 of thread 1 is being archived or modified
ORA-00312: online log 11 thread 1: '+REDO01/oralin/onlinelog/group_11.1611.951155487'
ORA-00312: online log 11 thread 1: '+REDO02/oralin/onlinelog/group_11.1611.951155489'
ORA-00312: online log 11 thread 1: '+REDO03/oralin/onlinelog/group_11.1611.951155489'

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

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

The purpose of dropping the redo log group is to recreate the redo log groups with an increased log size.

Redo log member is in ACTIVE state due to which we are unable to drop the redo log groups.

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

#########################################
# 3) Verify MRP process is down
#########################################

Make sure MRP process is down in standby database.

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

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

Clear the data stored in STANDBY Redo Log group to bring the REDO LOG MEMBER status from ACTIVE to INACTIVE state.

Alter database clear logfile group 11, group 14, group 18;

SQL> Alter database clear logfile group 11, group 14, group 18;
Database altered.


=====================================================================================================================
Once Redo logs are cleared. Drop command worked successfully.
=====================================================================================================================

SQL> Alter database drop logfile group 11, group 14, group 18;
Database altered.

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



Monday, June 12, 2017

addNode.sh raises java.lang.OutOfMemoryError Java heap space



We have a 2 node RAC cluster and adding one more node to it. while running addNode.sh script I'm receiving below error,

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

Exception java.lang.OutOfMemoryError: Java heap space occurred..
Exception in thread "Thread-65" java.lang.OutOfMemoryError: Java heap space
        at java.util.ArrayList.(ArrayList.java:113)
        at java.util.ArrayList.(ArrayList.java:120)

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

Error occured while running addNode.sh script to add a new node to a RAC Cluster in 11.2.0.4.0 version

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

$GRID_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={oralinux3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={oralinux3vip}"

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

#########################################
# 1)  Node Addition Log
#########################################

$GRID_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={oralinux3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={oralinux3vip}"
Starting Oracle Universal Installer...
Checking swap space: must be greater than 500 MB.   Actual 2246 MB    Passed
Oracle Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

Performing tests to see whether nodes ngmlx549,oralinux3 are available
............................................................... 100% Done.
.
-----------------------------------------------------------------------------
Cluster Node Addition Summary
Global Settings
   Source: $GRID_HOME
   New Nodes
Space Requirements
   New Nodes
      oralinux3
         $GRID_HOME: Required 32.49GB : Available 57.31GB
Installed Products
   Product Names
      Oracle Grid Infrastructure 11g 11.2.0.4.0
      Java Development Kit 1.5.0.51.10
      Installer SDK Component 11.2.0.4.0
      Oracle One-Off Patch Installer 11.2.0.3.4
      Oracle Universal Installer 11.2.0.4.0
      Oracle RAC Required Support Files-HAS 11.2.0.4.0
      Oracle USM Deconfiguration 11.2.0.4.0
      Oracle Configuration Manager Deconfiguration 10.3.1.0.0
      Enterprise Manager Common Core Files 10.2.0.4.5
      Oracle DBCA Deconfiguration 11.2.0.4.0
      Oracle RAC Deconfiguration 11.2.0.4.0
      Oracle Quality of Service Management (Server) 11.2.0.4.0
      Installation Plugin Files 11.2.0.4.0
      Universal Storage Manager Files 11.2.0.4.0
      Oracle Text Required Support Files 11.2.0.4.0
      Automatic Storage Management Assistant 11.2.0.4.0
      Oracle Database 11g Multimedia Files 11.2.0.4.0
      Oracle Multimedia Java Advanced Imaging 11.2.0.4.0
      Oracle Globalization Support 11.2.0.4.0
      Oracle Multimedia Locator RDBMS Files 11.2.0.4.0
      Oracle Core Required Support Files 11.2.0.4.0
      Bali Share 1.1.18.0.0
      Oracle Database Deconfiguration 11.2.0.4.0
      Oracle Quality of Service Management (Client) 11.2.0.4.0
      Expat libraries 2.0.1.0.1
      Oracle Containers for Java 11.2.0.4.0
      Perl Modules 5.10.0.0.1
      Secure Socket Layer 11.2.0.4.0
      Oracle JDBC/OCI Instant Client 11.2.0.4.0
      Oracle Multimedia Client Option 11.2.0.4.0
      LDAP Required Support Files 11.2.0.4.0
      Character Set Migration Utility 11.2.0.4.0
      Perl Interpreter 5.10.0.0.2
      PL/SQL Embedded Gateway 11.2.0.4.0
      OLAP SQL Scripts 11.2.0.4.0
      Database SQL Scripts 11.2.0.4.0
      Oracle Extended Windowing Toolkit 3.4.47.0.0
      SSL Required Support Files for InstantClient 11.2.0.4.0
      SQL*Plus Files for Instant Client 11.2.0.4.0
      Oracle Net Required Support Files 11.2.0.4.0
      Oracle Database User Interface 2.2.13.0.0
      RDBMS Required Support Files for Instant Client 11.2.0.4.0
      RDBMS Required Support Files Runtime 11.2.0.4.0
      XML Parser for Java 11.2.0.4.0
      Oracle Security Developer Tools 11.2.0.4.0
      Oracle Wallet Manager 11.2.0.4.0
      Enterprise Manager plugin Common Files 11.2.0.4.0
      Platform Required Support Files 11.2.0.4.0
      Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
      RDBMS Required Support Files 11.2.0.4.0
      Oracle Ice Browser 5.2.3.6.0
      Oracle Help For Java 4.2.9.0.0
      Enterprise Manager Common Files 10.2.0.4.5
      Deinstallation Tool 11.2.0.4.0
      Oracle Java Client 11.2.0.4.0
      Cluster Verification Utility Files 11.2.0.4.0
      Oracle Notification Service (eONS) 11.2.0.4.0
      Oracle LDAP administration 11.2.0.4.0
      Cluster Verification Utility Common Files 11.2.0.4.0
      Oracle Clusterware RDBMS Files 11.2.0.4.0
      Oracle Locale Builder 11.2.0.4.0
      Oracle Globalization Support 11.2.0.4.0
      Buildtools Common Files 11.2.0.4.0
      HAS Common Files 11.2.0.4.0
      SQL*Plus Required Support Files 11.2.0.4.0
      XDK Required Support Files 11.2.0.4.0
      Agent Required Support Files 10.2.0.4.5
      Parser Generator Required Support Files 11.2.0.4.0
      Precompiler Required Support Files 11.2.0.4.0
      Installation Common Files 11.2.0.4.0
      Required Support Files 11.2.0.4.0
      Oracle JDBC/THIN Interfaces 11.2.0.4.0
      Oracle Multimedia Locator 11.2.0.4.0
      Oracle Multimedia 11.2.0.4.0
      Assistant Common Files 11.2.0.4.0
      Oracle Net 11.2.0.4.0
      PL/SQL 11.2.0.4.0
      HAS Files for DB 11.2.0.4.0
      Oracle Recovery Manager 11.2.0.4.0
      Oracle Database Utilities 11.2.0.4.0
      Oracle Notification Service 11.2.0.3.0
      SQL*Plus 11.2.0.4.0
      Oracle Netca Client 11.2.0.4.0
      Oracle Advanced Security 11.2.0.4.0
      Oracle JVM 11.2.0.4.0
      Oracle Internet Directory Client 11.2.0.4.0
      Oracle Net Listener 11.2.0.4.0
      Cluster Ready Services Files 11.2.0.4.0
      Oracle Database 11g 11.2.0.4.0
-----------------------------------------------------------------------------

Instantiating scripts for add node (Monday, 12 June 2017 09:47:20 o'clock BST)
.                                                                 1% Done.
Instantiation of add node scripts complete
Copying to remote nodes (Monday, 12 June 2017 09:47:25 o'clock BST)
..........................SEVERE:Abnormal program termination. An internal error has occured. Please provide the following files to Oracle Support :
"/u01/oracle/oraInventory/logs/addNodeActions2017-06-12_09-45-47AM.log"
"/u01/oracle/oraInventory/logs/oraInstall2017-06-12_09-45-47AM.err"
"/u01/oracle/oraInventory/logs/oraInstall2017-06-12_09-45-47AM.out"
SQL>

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

#########################################
# 2)  Install Error Logs
#########################################

[oracle@oralinux1 oui]$ cat "/u01/oracle/oraInventory/logs/oraInstall2017-06-12_09-45-47AM.err"
Exception java.lang.OutOfMemoryError: Java heap space occurred..
Exception in thread "Thread-65" java.lang.OutOfMemoryError: Java heap space        at java.util.ArrayList.(ArrayList.java:113)
        at java.util.ArrayList.(ArrayList.java:120)
        at oracle.cluster.deployment.ractrans.FileDescriptor.(FileDescriptor.java:143)
        at oracle.cluster.deployment.ractrans.FileDescriptor.(FileDescriptor.java:111)
        at oracle.cluster.deployment.ractrans.DirectoryMap.processDir(DirectoryMap.java:238)
        at oracle.cluster.deployment.ractrans.DirectoryMap.processDir(DirectoryMap.java:287)
        at oracle.cluster.deployment.ractrans.DirectoryMap.processDir(DirectoryMap.java:287)
        at oracle.cluster.deployment.ractrans.DirectoryMap.(DirectoryMap.java:169)
        at oracle.cluster.deployment.ractrans.DirListing.(DirListing.java:301)
        at oracle.cluster.deployment.ractrans.DirListing.(DirListing.java:165)
        at oracle.cluster.deployment.ractrans.RACTransferCore.createDirListing(RACTransferCore.java:211)
        at oracle.cluster.deployment.ractrans.RACTransfer.createDirListing(RACTransfer.java:1908)
        at oracle.cluster.deployment.ractrans.RACTransfer.transferDirStructureToNodes(RACTransfer.java:620)
        at oracle.cluster.deployment.ractrans.RACTransfer.transferDirToNodes(RACTransfer.java:257)
        at oracle.ops.mgmt.cluster.ClusterCmd.transferDirToNodes(ClusterCmd.java:3191)
        at oracle.ops.mgmt.cluster.ClusterCmd.transferDirToNodes(ClusterCmd.java:3109)
        at oracle.sysman.oii.oiip.oiipg.OiipgClusterOps.transferDirToNodes(OiipgClusterOps.java:947)
        at oracle.sysman.oii.oiif.oiifw.OiifwClusterCopyWCCE.doOperation(OiifwClusterCopyWCCE.java:544)
        at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
        at oracle.sysman.oii.oiif.oiifw.OiifwAddNodePhaseWCDE.doOperation(OiifwAddNodePhaseWCDE.java:313)
        at oracle.sysman.oii.oiif.oiifb.OiifbCondIterator.iterate(OiifbCondIterator.java:171)
        at oracle.sysman.oii.oiic.OiicPullSession.doOperation(OiicPullSession.java:1380)
        at oracle.sysman.oii.oiic.OiicSessionWrapper.doOperation(OiicSessionWrapper.java:295)
        at oracle.sysman.oii.oiic.OiicInstaller.run(OiicInstaller.java:579)
        at oracle.sysman.oii.oiic.OiicInstaller.runInstaller(OiicInstaller.java:969)
        at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:906)
java.lang.OutOfMemoryError: Java heap space
        at java.nio.CharBuffer.wrap(CharBuffer.java:350)
        at sun.nio.cs.StreamEncoder$CharsetSE.implWrite(StreamEncoder.java:378)
        at sun.nio.cs.StreamEncoder.write(StreamEncoder.java:136)
        at java.io.OutputStreamWriter.write(OutputStreamWriter.java:191)
        at java.io.BufferedWriter.flushBuffer(BufferedWriter.java:111)
        at java.io.PrintStream.write(PrintStream.java:458)
        at java.io.PrintStream.print(PrintStream.java:602)
        at oracle.sysman.oii.oiic.OiicSilentInstallPhaseProgressListener.displayProgress(OiicSilentInstallPhaseProgressListener.java:372)
        at oracle.sysman.oii.oiic.OiicSilentInstallPhaseProgressListener.addBytes(OiicSilentInstallPhaseProgressListener.java:341)
        at oracle.sysman.oii.oiic.OiicSilentInstallPhaseProgressListener.addPercentage(OiicSilentInstallPhaseProgressListener.java:555)
        at oracle.sysman.oii.oiix.OiixProgressUpdator$OiixProgressUpdatingWorker.run(OiixProgressUpdator.java:199)
        at java.lang.Thread.run(Thread.java:637)
[oracle@oralinux1 oui]$

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

#########################################
# 3)  Check the Java Memory Parameters
#########################################

Check Java Memory parameters defined in $GRID_HOME/oui/oraparam.ini

cd $GRID_HOME/oui

cat oraparam.ini | grep -i java

[oracle@oralinux1 oui]$ cat oraparam.ini | grep -i memory
JRE_MEMORY_OPTIONS=" -mx1024m"
[oracle@oralinux1 oui]$

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

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

During Node addition process, it copies entire GRID_HOME files from existing node to the new node. Java Memory parameters defined in oraparam.ini file is 1024 MB, but looks like this memory is not sufficient.

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

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

Increase JAVA Memory to a higher value

[oracle@oralinux1 oui]$ cat oraparam.ini | grep -i memory
JRE_MEMORY_OPTIONS=" -mx2048m"
[oracle@oralinux1 oui]$

=====================================================================================================================
After increasing it to double the memory, addNode.sh script is running good.
=====================================================================================================================

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



Monday, May 8, 2017

touch: cannot touch `a': No space left on device

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

[oracle@oralinux audit]$ touch a
touch: cannot touch `a': No space left on device

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

Database is giving alerts that audit files cant be created. Tried to create a file and gave above error

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

touch a

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

#########################################
# 1) Check the File system Usage
#########################################

[oracle@oralinux audit]$ df -h .
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oracle_data-lv_orasid01
                      9.9G  6.8G  2.6G  73% /ofa/ORASID01
[oracle@oralinux audit]$
#### Create a new file
[oracle@oralinux audit]$ touch a
touch: cannot touch `a': No space left on device
[oracle@oralinux audit]$

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

#########################################
# 2) Check Inode of the file system
#########################################

cat /proc/sys/kernel/sem

ipcs -ls

[oracle@oralinux audit]$ cat /proc/sys/kernel/sem
250     524288  256     2048
[oracle@oralinux audit]$ ipcs -ls

------ Semaphore Limits --------
max number of arrays = 2048
max semaphores per array = 250
max semaphores system wide = 524288
max ops per semop call = 256
semaphore max value = 32767

df -i .

[oracle@oralinux audit]$ df -i .
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
/dev/mapper/vg_oracle_data-lv_orasiD01
                     1310720 1310720       0  100% /ofa/ORASID01
[oracle@oralinux audit]$

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

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

From above output, its clear the Inode of that particular file system has reached 100%, even though file system is having sufficient space, the Inode has reached 100%, so new files are not allowed.

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

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

#### Remove the files which are older than n number of days.

rm aud*

As i dont need audit files i have cleared everything.

=====================================================================================================================
Post removal of files, new audit files can be created.
=====================================================================================================================

Its better to set up a monitoring to check Inodes before it reaches 100%

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



Tuesday, April 18, 2017

ORA-19909: datafile 1 belongs to an orphan incarnation while performing active database duplication to create new standby database


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

ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA04/drsid01/datafile/system.446.939940085'

### Full Error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/18/2017 16:54:22
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 5990833
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA04/drsid01/datafile/system.446.939940085'

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

Error occured while creating a new standby database using active database duplication in 11.2.0.4 version

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

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
set cluster_database='false'
set db_unique_name='fsid01'
set db_create_online_log_dest_1='+SHAREDREDO01'
set db_create_online_log_dest_2='+SHAREDREDO02'
set db_create_online_log_dest_3='+SHAREDREDO03'
set db_create_file_dest='+SHAREDDATA04'
set db_recovery_file_dest='+SHAREDFRA04'
set control_files='+SHAREDDATA04/fsid01/standby_fsid01.ctl'
dorecover nofilenamecheck;
}

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

#########################################
# 1) Snippet of database logs
#########################################

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/system.446.939940085 for datafile 1 with checkpoint SCN of 5984647
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/sysaux.447.939940151 for datafile 2 with checkpoint SCN of 5984647
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/undotbs1.386.939923955 for datafile 3 with checkpoint SCN of 5984647
...
...
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for datafile  1 to
 "+SHAREDDATA04/drsid01/datafile/system.446.939940085";
   set newname for datafile  2 to
 "+SHAREDDATA04/drsid01/datafile/sysaux.447.939940151";
...
...
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_1_seq_225.271.941647725" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_3_seq_207.1182.941644927" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_3_seq_208.1206.941647725" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_1_seq_224.274.941644927" auxiliary format
 "+SHAREDFRA04"   archivelog like
 "+SHAREDFRA04/psid01/archivelog/2017_04_18/thread_2_seq_217.275.941647727" auxiliary format
 "+SHAREDFRA04"   ;
   catalog clone start with  "+SHAREDFRA04";
   catalog clone datafilecopy  "+SHAREDDATA04/drsid01/datafile/system.446.939940085",
 "+SHAREDDATA04/drsid01/datafile/sysaux.447.939940151",
 "+SHAREDDATA04/drsid01/datafile/undotbs1.386.939923955",
 "+SHAREDDATA04/drsid01/datafile/admin_data_ts01.455.939940317",
...
...
datafile 74 switched to datafile copy
input datafile copy RECID=76 STAMP=941648028 file name=+SHAREDDATA04/drsid01/datafile/undotbs3.390.939925417

contents of Memory Script:
{
   set until scn  5990833;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 18-APR-17

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 04/18/2017 16:54:22
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 5990833
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '+SHAREDDATA04/drsid01/datafile/system.446.939940085'

Recovery Manager complete.


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

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

The database we are trying to duplicate is fsid01. But from the above logs we can see that its using another database called "drsid01" which is the another standby database for this same primary.

sql statement: alter database mount standby database
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/system.446.939940085 for datafile 1 with checkpoint SCN of 5984647
Using previous duplicated file +SHAREDDATA04/drsid01/datafile/sysaux.447.939940151 for datafile 2 with checkpoint SCN of 5984647

And all the datafiles are referenced to drsid01 instead of fsid01 database.

A duplicate was performed before/previously for drsid01 database, and now duplicate command is using the Datafile(s) which now are being reused, instead of restoring the datafile FROM primary database(psid01)

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

#########################################
# 3) How RMAN Duplicate Works?
#########################################

RMAN automatically optimizes a DUPLICATE command that is a repeat of a previously failed DUPLICATE command. The repeat DUPLICATE command notices which data files were successfully copied earlier and does not copy them again. This applies to all forms of duplication, whether they are backup-based (with or without a target connection) or active database duplication. The automatic optimization of the DUPLICATE command can be especially useful when a failure occurs during the duplication of very large databases.

If you do not want RMAN to automatically recover from a failed DUPLICATE operation, specify the keyword NORESUME to disable the functionality. Using the keyword NORESUME in the first invocation of DUPLICATE prevents a subsequent DUPLICATE command for the new database from using this automatic optimization.

Furthermore, using NORESUME in the first invocation of duplicate will prevent that a subsequent duplicate (in case of failure) uses the functionality. Here after using NORESUME, RMAN restored datafile 3 from the latest backup being used thus RMAN did not look for old archivelog.

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

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

#### Modified the duplicate command and added NORESUME.

SQL> !more psid01_to_fsid01_dup.rcv
connect target sys/****@psid01;
connect auxiliary sys/****@dup;
spool log to 'rman_dup_fsid01.log';
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
set cluster_database='false'
set db_unique_name='fsid01'
set db_create_online_log_dest_1='+SHAREDREDO01'
set db_create_online_log_dest_2='+SHAREDREDO02'
set db_create_online_log_dest_3='+SHAREDREDO03'
set db_create_file_dest='+SHAREDDATA04'
set db_recovery_file_dest='+SHAREDFRA04'
set control_files='+SHAREDDATA04/fsid01/standby_fsid01.ctl'
dorecover nofilenamecheck noresume;
}


=====================================================================================================================
Now the duplication is working fine.
=====================================================================================================================

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
...
...
executing command: SET NEWNAME
Starting backup at 18-APR-17
channel prmy1: starting datafile copy
input datafile file number=00003 name=+SHAREDDATA04/psid01/datafile/undotbs1.335.939312685
channel prmy2: starting datafile copy
input datafile file number=00011 name=+SHAREDDATA04/psid01/datafile/undotbs2.344.939312711
channel prmy3: starting datafile copy
input datafile file number=00012 name=+SHAREDDATA04/psid01/datafile/undotbs3.345.939312715
channel prmy4: starting datafile copy
input datafile file number=00072 name=+SHAREDDATA04/psid01/datafile/undotbs1.336.939742283
~

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



Sunday, March 12, 2017

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

Sunday, February 19, 2017

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



Sunday, August 28, 2016

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



Friday, August 19, 2016

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



Tuesday, August 16, 2016

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



Monday, June 20, 2016

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



Saturday, March 19, 2016

ORA-38788: More standby database recovery is needed


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

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

### Full Error

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

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

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

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

Alter database flashback on;

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

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

Select name,DB_UNIQUE_NAME,flashback_on from v$database;

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

#### Turn on Flashback

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#### Primary Database

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

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

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

connected to target database: DB01 (DBID=2325795052)

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB01  2325795052       CURRENT 1          02-MAR-16
RMAN>

#### Standby Database

[oracle@host02 bin]$ rman target /

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

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

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

RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB01  2325795052       PARENT  1          02-MAR-16
2       2       DB01  2325795052       CURRENT 2216947    17-MAR-16
RMAN>

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

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

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

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

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

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

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

Reset database to incarnation 1;

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

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

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

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

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

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


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

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

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

SQL> select distinct process from gv$managed_standby;

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

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

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

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

SQL>

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