Fatal error in a UNIX box

Have you ever come across a fatal error like the one below on a seemingly perfect running environment ? well, I did face this error when trying to locate the IDs of  shared memory and semaphores of a problematic Oracle instance.

ld.so.1: sysresv: fatal: libclntsh.so.9.0: open failed: No such file or directory Killed

As usual, First GOOGLEd around but found a lot of irrelevant hits. Muttering about the situation and the lack of online help prompted me to backtrack a while and go through each and every word of the error to understand the issue. I then realized that it has got something to do with the linking of Oracle libraries.

Then I quickly queried the value of LD_LIBRARY_PATH environmental variable to see that appropriate $ORACLE_HOME/lib value was missing. I tried a vain attempt to resolve the issue by adding the missing library location to LD_LIBRARY_PATH and ran the command again, Voila!!! it worked. I was able to achieve the objective.

Conclusion :
I learnt two morals from this one, most importantly, for errors like "ld.so.1:.... fatal:" it is always good to look at the value of LD_LIBRARY_PATH as the first step towards trobleshooting it. Least importantly !!! I did not say GOOGLE when I first started babbling.....LOL

ORA-03297

Following several alerts from a particular database about high usage of a few tablespaces, i set out to look into the issue and found that a few of the tablespaces had been dragging their feet over 95% usage but i also noticed that a couple of the tablespaces were not being used at all with their used percentage as close to zero.

The tablespaces that had the used percentage as zero had been hogging up a lot of disk space in their datafiles while the ones that really require more space to be added had very little space available from the disk since the mount points were already used in the high 90s. This led to reducing the datafiles that are under utilized.

Heres is how the utilitzation of tablespaces look like

 

TABLESPACE                EXT_MGMT   TOTAL(MBs)  USED(MBs)  FREE(MBs) MAX_FREE(MBs) %Full
------------------------- ---------- ---------- ---------- ---------- ------------- -----
TOOLS                     L                1900    1574.13     325.88        174.94    83
UNDO                      L                2400    1976.13     423.88        400.94    82
USER_BASE                 L                1000        .13     999.88         51.94     0
CUSTOMER                  L                1900        .13    1899.88       1799.94     0 
INDX                      L                 175        .13     174.88         99.94     0
MISC                      L                 150      80.13      69.88         59.94    53

SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
  2  where tablespace_name='&tablespace_name' group by file_name
  3  order by file_name;
Enter value for tablespace_name: CUSTOMER
old   2: where tablespace_name='&tablespace_name' group by file_name
new   2: where tablespace_name='CUSTOMER' group by file_name

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- -------------------------                            

                 100 /prd/u01/CUSTOMER_02.DBF
                1800 /prd/u02/CUSTOMER_01.DBF

SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
  2  where tablespace_name='&tablespace_name' group by file_name
  3  order by file_name;
Enter value for tablespace_name: USER_BASE
old   2: where tablespace_name='&tablespace_name' group by file_name
new   2: where tablespace_name='USER_BASE' group by file_name

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- ---------------------------        
                 100 /prd/u01/USER_BASE_02.DBF
                 900 /prd/u02/USER_BASE_01.DBF

SQL> alter database datafile '/prd/u02/CUSTOMER_01.DBF' resize 100M;

Database altered.

SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
  2  where tablespace_name='&tablespace_name' group by file_name
  3  order by file_name;
Enter value for tablespace_name: CUSTOMER
old   2: where tablespace_name='&tablespace_name' group by file_name
new   2: where tablespace_name='CUSTOMER' group by file_name

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- --------------------------
                 100 /prd/u01/CUSTOMER_02.DBF
                 100 /prd/u02/CUSTOMER_01.DBF


SQL> alter database datafile '/prd/u02/USER_BASE_01.DBF' resize 100M;
alter database datafile '/prd/u02/USER_BASE_01.DBF' resize 100M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Then I tried to locate the tiny objects that MAY be filling up the datafile in question above to know how far back  can i reduce the datafile and found only one object in it and it happend to be a deleted table from the recyclebin.

 

SQL> select sum(bytes)/1024/1024,segment_name,segment_type,owner,tablespace_name,header_file 
  2  from dba_segments wheretablespace_name in ('USER_BASE') group by
  3  owner,tablespace_name,header_file,segment_name,segment_type;


SUM(BYTES)/1024/1024 SEGMENT_NAME               SEGMENT_TYPE OWNER TABLESPACE_NAME HEADER_FILE
-------------------- ------------               ------------ ----- --------------- -----------
944                  BIN$lLi0+QToAHbgRAADurJuKQ==$0    TABLE SCOTT USER_BASE       20

Wow....That was an interesting discovery, there is an object that is as big as the size of the datafile that it resides on !!!

SQL> select file_id,sum(bytes)/1024/1024,file_name from dba_data_files
  2  where tablespace_name='USER_BASE' group by file_id,relative_fno,file_name;

    FILE_ID SUM(BYTES)/1024/1024 FILE_NAME
---------- -------------------- ------------------------------
        20                  900 /prd/u02/USER_BASE_01.DBF
        21                  100 /prd/u01/USER_BASE_02.DBF
 

Now, when i tried to purge the recyclebin - it does not make any difference and i still see the table in   dba_segments as well as in the recyclebin.  
 

SQL> purge recyclebin;

Recyclebin purged.

SQL> select sum(bytes)/1024/1024,segment_name,segment_type,owner,tablespace_name,
  2  header_file from dba_segments wheretablespace_name in ('USER_BASE') group by
  3  owner,tablespace_name,header_file,segment_name,segment_type;


SUM(BYTES)/1024/1024 SEGMENT_NAME               SEGMENT_TYPE OWNER TABLESPACE_NAME HEADER_FILE
-------------------- ------------               ------------ ----- --------------- -----------
944                  BIN$lLi0+QToAHbgRAADurJuKQ==$0    TABLE SCOTT USER_BASE       20


SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,TS_NAME from dba_recyclebin;


OWNER OBJECT_NAME                    ORIGINAL_NAME TS_NAME
----- ------------------------------ ------------- ----------
SCOTT BIN$lLi0+QToAHbgRAADurJuKQ==$0 DEMO_TABLE    USER_BASE

so i tried a "PURGE TABLE" on the recyclebin named table and it worked, It no longer exists in the dba_segments  neither does it show up in the 
dba_recyclebin and i was able to successfully reduce the datafile.

SQL> purge table scott."BIN$lLi0+QToAHbgRAADurJuKQ==$0";

Table dropped.

SQL> alter database datafile '/prd/u02/USER_BASE_01.DBF' resize 100M;

Database altered.

SQL> select sum(bytes)/1024/1024,file_name from dba_data_files
  2  where tablespace_name='&tablespace_name' group by file_name
  3  order by file_name;
Enter value for tablespace_name: USER_BASE
old   2: where tablespace_name='&tablespace_name' group by file_name
new   2: where tablespace_name='USER_BASE' group by file_name

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- --------------------------
                 100 /prd/u01/USER_BASE_02.DBF
                 100 /prd/u02/USER_BASE_01.DBF
                                               
 

 The conclusion is.....

1) Keep purging the recycle bin from time to time, or may be set up a job to do that.

or

2) Advice the users to use the 'PURGE' keyword of the 'DROP TABLE' statement.

or

3) Turn off RECYCLEBIN - but this does not give you the option of flashing back your database to recover a lost table.

ORA-01623: log %s is current log for thread %s - cannot drop

In the process of refreshing a 9i Dev database I had to change the file locations of a few datafiles and the logfiles. Though the usage of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT would have made the job simpler but i just did not feel like using them. So, the controlfile was
re-created, recovery was invoked and thats when the issues started showing up one by one - detailed below.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  395387088 bytes
Fixed Size                   454864 bytes
Variable Size             285212672 bytes
Database Buffers          109051904 bytes
Redo Buffers                 667648 bytes
SQL> @C:\Oracle\admin\devel\scripts\crea_ctrl_20_oct_2010.sql

Control file created.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            c:\oracle\ora92\RDBMS
Oldest online log sequence     0
Current log sequence           0

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 505595471 generated at 10/19/2010 06:44:30 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04700.001
ORA-00280: change 505595471 for thread 1 is in sequence #4700


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4700.DBF
ORA-00279: change 505596642 generated at 10/19/2010 07:04:31 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04701.001
ORA-00280: change 505596642 for thread 1 is in sequence #4701
ORA-00278: log file 'H:\prod_hot_bkup\1_4700.DBF' no longer 
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4701.DBF
ORA-00279: change 505596644 generated at 10/19/2010 07:04:31 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04702.001
ORA-00280: change 505596644 for thread 1 is in sequence #4702
ORA-00278: log file 'H:\prod_hot_bkup\1_4701.DBF' no longer 
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4702.DBF
ORA-00279: change 505596648 generated at 10/19/2010 07:04:37 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04703.001
ORA-00280: change 505596648 for thread 1 is in sequence #4703
ORA-00278: log file 'H:\prod_hot_bkup\1_4702.DBF' no longer 
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

Issue #1
--------

The database engine is unable to locate the logfile, because the directory path cannot be found. So i try to drop the logfile groups to create new ones on the available directory paths.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'I:\ORACLE\ORADATA\devel\REDO01A.LOG'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files in thread 1
ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\devel\REDO02A.LOG'
ORA-00312: online log 2 thread 1: 'H:\ORACLE\ORADATA\devel\REDO02B.LOG'

SQL> alter database add logfile group 1 
  2> ('E:\ORACLE\ORADATA\devel\REDO01A.LOG') size 200M;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 
  2> ('E:\ORACLE\ORADATA\devel\REDO02A.LOG') size 200M;

Database altered.

Issue #2
--------

One cannot drop an online redo logfile and cannot switch a logfile in mount state either to drop it.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for thread 1 - cannot drop
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\devel\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'


SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

Querying V$ views for the logfiles gives an insight of the state of the logfile groups. Then the locations of the logfile groups are changed but the STALE contents do not get cleared neither does it allow to drop one of the members of a logfile groups. As a next step to resolve the issue, the contents of the current online logfile group are cleared so the file is clean from corruption.

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          
---------- ---------- ---------- ---------- ---------- --- ---------------
         1          1          0  209715200          1 YES UNUSED         
         2          1          0  209715200          1 YES UNUSED         
         3          1          0  209715200          2 YES INVALIDATED    

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -------------------------------------------
         3 STALE   ONLINE  D:\ORACLE\ORADATA\devel\REDO03A.LOG
         3 STALE   ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

SQL> alter database rename file 'D:\ORACLE\ORADATA\devel\REDO03A.LOG' to 
  2> 'E:\ORACLE\ORADATA\devel\REDO03A.LOG';

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ---------------------------------------
         3 STALE   ONLINE  E:\ORACLE\ORADATA\devel\REDO03A.LOG
         3 STALE   ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

SQL> alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG';
alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 3
ORA-01517: log member: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'


SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------
         3         ONLINE  E:\ORACLE\ORADATA\devel\REDO03A.LOG
         3         ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

Yet, the third logfile group cannot be dropped because it is the current logfile and the database needs to be opened to switch the logfile.

SQL> alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG';
alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: 'E:\ORACLE\ORADATA\devel\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

Issue #3
--------

The ORACLE instance terminated, reason being the mismatch in the UNDO tablespace name. After the correction was made, the database would fail to open yet again. The database would not proceed further with either of the 'alter database open' and 'alter database open resetlogs' commands.

The sequence number had been reset to '1' when the last time the database was opened with a 'resetlogs' but the database could not come up to ensure a complete recovery as evident from the below results. So the database would require some more recovering but this time it starts afresh from sequence '1'

ALERT LOG
---------

Wed Oct 20 12:51:30 2010
Errors in file c:\oracle\admin\devel\udump\devel_ora_7580.trc:
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

devel_ora_7580.trc
------------------
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type


SQL> startup mount;
ORACLE instance started.

Total System Global Area  395387088 bytes
Fixed Size                   454864 bytes
Variable Size             285212672 bytes
Database Buffers          109051904 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------
         3         ONLINE  E:\ORACLE\ORADATA\devel\REDO03A.LOG
         3         ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\devel\SYSTEM01.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 505596650 generated at 10/20/2010 12:51:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 505596650 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\devel\SYSTEM01.DBF'


ORA-01112: media recovery not started


SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

The database recovery was started again and this time the online redo log that was created from the last incomplete recovery was applied to achieve a successful recovery and the database was opened with a 'resetlogs' keyword to reset the sequence number one more time.

SQL> recover database  using backup controlfile;
ORA-00279: change 505596650 generated at 10/20/2010 12:51:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 505596650 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\oracle\oradata\devel\REDO03B.LOG
Log applied.
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

java.lang.ClassLoader$NativeLibrary.load

You set out for a new Oracle installation. You patiently follow each and every step
of the "Checking the Software Requirements" section of the install manual to ensure
the host is ready from the software point of view to get Oracle binaries loaded.
You then desperately invoke the installer and just then something terribly goes
wrong that makes you feel disappointed. You see Java errors from the Oracle
Universal Installer (OUI) that makes you go crazy although you may have the most
updated Java version running on the host. The irony is, you are not even using a
telnet session instead you are directly logged into the Linux host to invoke the
OUI just as you would on a Windows host.

oel5> ./runInstaller
Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, redhat-5, 
UnitedLinux-1.0, asianux-1 or asianux-2
                                      Passed


All installer requirements met.

Preparing to launch Oracle Universal Installer from 
/tmp/OraInstall2011-05-12_11-18-59PM. Please wait ...oel5> Exception in thread 
"main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2011-05-12_11-18-59PM
/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such 
file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(Unknown Source)
        at java.lang.ClassLoader.loadLibrary(Unknown Source)
        at java.lang.Runtime.loadLibrary0(Unknown Source)
        at java.lang.System.loadLibrary(Unknown Source)
        at sun.security.action.LoadLibraryAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
        at sun.awt.DebugHelper.(Unknown Source)
        at java.awt.Component.(Unknown Source)
oel5> 

The issue is, a package named "libXp" is missing on your Linux host - installing
which would take care of these Java errors from the OUI. Load the host installation DVD, look for the libXp package and install it as root user. Now, try to invoke the
installer once again and it works just perfect.

[root@oel5 Server]# rpm -Uvh libXp-1.0.0-8.1.el5.i386.rpm
warning: libXp-1.0.0-8.1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libXp                  ########################################### [100%]
[root@oel5 Server]#