Wednesday, October 18, 2017

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file

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

ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'

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

Error occured while trying to bring up the database after a server reboot.

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

startup from sqlplus prompt

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

#########################################
# 1) Start the Database
#########################################

SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 14 - see DBWR trace file
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'

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

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

Error is occuring after a server reboot. Looks like the file system mentioned in the error is not mounted.

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

##########################
## Solution 1
##########################

As from the above step we can see the filesystem(/u01) is not mounted. So mount the file system.

As a root user,

mount /u01

#### Shutdown the instance

shu immediate

SQL> shu immediate
ORA-01109: database not open

#### Then Bring the database up.

startup

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
Database opened.
SQL>

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

##########################
## Solution 2
##########################

#### If the filesystem can't be mounted and the datafile/tablespace data is not required then.

## Shut the instance

Shutdown immediate

SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

## Bring up the database in Mount State

Startup mount

SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
SQL>

#### Mark the datafile as Dropped.

Alter database datafile '/u01/ORALIN/datafiles/tbs01.dbf' offline drop;

SQL> Alter database datafile '/u01/ORALIN/datafiles/tbs01.dbf' offline drop;
Database altered.

#### Open the Database in Read-Write Mode

SQL> Alter database open;
Database altered.

#### You can Drop the tablespace and that User if its not required. In my case, it didnt work.

SQL> drop user ggadmin cascade;
drop user ggadmin cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'
ORA-06512: at line 1314
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'


SQL> Drop tablespace GGTBS including contents;
Drop tablespace GGTBS including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'
ORA-06512: at line 1314
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: '/u01/ORALIN/datafiles/tbs01.dbf'


#### Shutdown and restart Database to verify it comes up good.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2207256 bytes
Variable Size            1979711976 bytes
Database Buffers          150994944 bytes
Redo Buffers                4972544 bytes
Database mounted.
Database opened.
SQL> exit


SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORALIN  READ WRITE

SQL> select logins from v$instance;

LOGINS
----------
ALLOWED

SQL>

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



ORA-02266: unique/primary keys in table referenced by enabled foreign keys


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

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

### Full Error

SQL> truncate table ABC_OWNER.TAB1;
truncate table ABC_OWNER.TAB1
                         *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

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

Error occured while truncating a table in 11.2.0.4.0 version

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

truncate table ABC_OWNER.TAB1;

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

#########################################
# 1) Check the table relationship
#########################################

set lines 200
col owner for a20
col status for a10
col deferrable for a15
col validated for a18
col generated for a15
col deferred for a15

select owner,constraint_name,constraint_type,table_name,status,DEFERRABLE,DEFERRED,VALIDATED,GENERATED from dba_constraints where table_name='TAB1' and constraint_type in ('R','P');

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS     DEFERRABLE      DEFERRED        VALIDATED          GENERATED
-------------------- ------------------------------ - ------------------------------ ---------- --------------- --------------- ------------------ ---------------
ABC_OWNER            PK_TAB1                        P TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_TAB1_30                     R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_ERROR_3A                    R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME

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

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

"TAB1" table has relationship with other tables. From the above output we can see it has a primary constraint and 2 referential constraints(Child).

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

Solution would be to disable the constraints and then proceed with the truncate. This is a test environment and i'm doing some testing with the database export/import.

So i'm not worried about the data in it. But in case if you are worried about the data integrity then, best way is to find out what are the child tables and make sure this data is not required anymore and then perform your activity.

select 'alter table '||owner||'.'||table_name ||' disable constraint '||constraint_name||' cascade;' from dba_constraints where constraint_type in ('R','P') and table_name in ('TAB1') order by table_name;

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||'CASCADE;'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter table ABC_OWNER.TAB1 disable constraint PK_TAB1 cascade;
alter table ABC_OWNER.TAB1 disable constraint FK_TAB1_30 cascade;
alter table ABC_OWNER.TAB1 disable constraint FK_ERROR_3A cascade;

=====================================================================================================================
After Disabling the Constraints, truncate command worked.
=====================================================================================================================

SQL>  truncate table ABC_OWNER.TAB1;

Table truncated.

SQL>

OWNER                CONSTRAINT_NAME                C TABLE_NAME                     STATUS     DEFERRABLE      DEFERRED        VALIDATED          GENERATED
-------------------- ------------------------------ - ------------------------------ ---------- --------------- --------------- ------------------ ---------------
ABC_OWNER            PK_TAB1                        P TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_TAB1_30                     R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME
ABC_OWNER            FK_ERROR_3A                    R TAB1                           DISABLED   NOT DEFERRABLE  IMMEDIATE       NOT VALIDATED      USER NAME

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



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