Oracle & Unix

Fatal error in a UNIX box

2011-08-04T14:33:00.000+05:30

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

2011-08-04T14:28:00.000+05:30

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.

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

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

2011-07-21T17:52:00.000+05:30

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

2011-05-24T15:38:00.000+05:30

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]#

Clearing the RECYCLEBIN in an Oracle database

2010-11-11T21:55:00.003+05:30

How does a DBA clear objects from a recyclebin of another user in an Oracle database ?...It can be done in one of the three ways listed below.


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

OWNER      OBJECT_NAME                    ORIGINAL_NAME     TS_NAME
---------  ------------------------------ ----------------- ----------
USER1      BIN$b5VRkexOdOjgRAADurN2OQ==$0 TABLE1            TBL_SPACE
USER1      BIN$b5VRkexNdOjgRAADurN2OQ==$0 TABLE2            TBL_SPACE
USER1      BIN$b5VRkexMdOjgRAADurN2OQ==$0 TABLE3            TBL_SPACE
USER1      BIN$b5VRkexLdOjgRAADurN2OQ==$0 TABLE4            TBL_SPACE
USER1      BIN$b5VRkexJdOjgRAADurN2OQ==$0 TABLE5            TBL_SPACE

Method-1
--------


SQL> purge table user1."BIN$b5VRkexOdOjgRAADurN2OQ==$0";

Table purged.

Method-2
--------


SQL> purge table user1."TABLE2";

Table purged.

Method-3
--------


SQL> drop table user1."BIN$b5VRkexMdOjgRAADurN2OQ==$0";
drop table xdb."BIN$lMZuKzEkDFfgRAADurN2OQ==$0"
               *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

This is like killing the " mother of recyclebin evil " - we cannot perform DDL/DML on an object in recyclebin as the definition of the error states. The only reason for getting an ORA-38301 in this situation is because the RECYCLEBIN database parameter is turned on and turning it off lets a DBA issue a DROP TABLE command against a recyclebin table but at the cost of not being able to flashback the database to recover a lost table.


SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SQL> SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/10.2.0
                                                 /dbs/spfileprod_db.ora

SQL> alter system set recyclebin=off scope=both;

System altered.

SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF


SQL> drop table user1."BIN$b5VRkexMdOjgRAADurN2OQ==$0";

Table dropped.

sga_max_size and sga_target

2010-05-20T20:35:00.004+05:30

In this post, I am going to be demonstrating the basics of working of the sga_max_size and sga_target and also to cover some myths of playing around with these parameters.

To begin with, sga_max_size is set in the spfile to a value of 200M and then I try to set the sga_target and the story unfolds as below.


SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/10.2.0
                                                 /dbs/spfileprod_db.ora
SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             155194152 bytes
Database Buffers           50331648 bytes
Redo Buffers                2211840 bytes



SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 84M

SQL> sho parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 48M

SQL> sho parameter large

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0

SQL> sho parameter java_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 24M

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

Now, trying to set the sga_target to a value of 210M which is greater than that of sga_max_size gives out an error of ORA-02097 and ORA-00823, this shows that the sga_target can never be greater than the sga_max_size.


SQL> alter system set sga_target=210M scope=both;
alter system set sga_target=210M scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

However, sga_target can be set less than or equal to sga_max_size.


SQL> alter system set sga_target=200M scope=both;

System altered.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M

But an interesting point to be noted in here is that as soon as the sga_target is set to a non-zero value, it starts affecting the memory areas of the SGA which is evident from the change in the value of Database Buffers (db_cache_size) from 50331648 (48M) from the previous execution of SHO SGA to 92274688 (88M), while every other memory area remains unchanged.


SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes


SQL>  sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 84M

SQL> sho parameter java_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 24M

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0

SQL> sho parameter large_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0

Let's try to unset the sga_target and see if Database Buffers (db_cache_size) reverts back to what it was, but it does not.


SQL> alter system set sga_target=0 scope=both;

System altered.

SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes

SQL> sho parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL>  sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

It was hoped that bouncing the database would make a difference, on the contrary it did not. It is another discussion as to why the Database Buffers (db_Cache_size) does not change back to what it was even after unsetting the sga_target and bouncing the database, let's not get into the nitty-gritty of that.


SQL> sho sga

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes

SQL> sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1977560 bytes
Variable Size             113251112 bytes
Database Buffers           92274688 bytes
Redo Buffers                2211840 bytes
Database mounted.
Database opened.


SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL> sho parameter db_Cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

The Oracle server does not let you to resize the sga_max_size on the fly since it is not a dynamically changeable parameter. Let's now change sga_target to the value of sga_max_size. After this I will comment the sga_max_size and add sga_target=200M in the pfile, convert it into spfile and bounce the database.


SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 0


SQL> alter system set sga_max_size=0 scope=both;
alter system set sga_max_size=0 scope=both
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL> alter system set sga_target=200M scope=both;

System altered.

SQL> sho parameter sgA_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M



### MAIN SGA PARAMS ###
###sga_max_size=200M
sga_target=200M

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  1977528 bytes
Variable Size              75502408 bytes
Database Buffers          130023424 bytes
Redo Buffers                2211840 bytes
Database mounted.
Database opened.

SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M

SQL> sho parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0

SQL> sho parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0

SQL> sho parameter large_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 0

SQL> sho parameter java_pool_s

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_pool_size                       big integer 0

SQL> sho parameter streams_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
SQL>

It now shows that as soon as the sga_target is set and set_max_size is unset, all the other memory area related parameters get reset to a value of zero(0) - making it clear that the Automatic Shared Memory Management is fully functional and that Memory Manager (MMAN) background process is actively doing its job. Also has the Database Buffers (db_cache_size) taken a higher value of 130023424 (124M) against the previous 88M.

There is yet another point to be noted here, besides sga_max_size being commented in spfile - it takes a value equal to the sga_target.



SQL> sho parameter sga_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 200M
sga_target                           big integer 200M


UNIX$> grep -i "sga" initprod_db.ora
###sga_max_size=200M
sga_target=200M


UNIX$> grep -i "sga" $ORACLE_HOME/dbs/spfileprod_db.ora
*.sga_target=200M

The conclusions that we can draw from these little experiments are listed below.

1) sga_max_size cannot be changed without bouncing the database.

2) sga_target can be changed on the fly without bouncing the database but it can never be set to a value greater than the sga_max_size.

3) If the sga_max_size is not set in either of the pfile or spfile and the sga_target is set then the sga_max_size takes the value of sga_target when you do a sho parameter sga_

Oracle Tuning Methodology

2010-03-22T18:16:00.000+05:30

Here is what Burleson Consulting (BC) has to say about the sequence of
tuning a performance situation in an Oracle database. Click on the slides
to read more on this from BC's website.

Data Guard Operational Modes

2010-03-03T20:47:00.004+05:30

Here is a quick map of the three different modes of operation in a Data Guard (DG) configuration. There are two snaps, one has a traditional white background and the other for reduced power consumption - when viewed

FTP automation on Windows and Unix

2010-02-17T16:41:00.001+05:30

Automation is one thing that I like the most, it makes my job easier on a daily basis - makes you sit back and relax and sprawl whilst a critical task is being completed by the script but in the long run the same script would be more than sufficient to get me fired out of my Organization since every thing is taken care by the machine against man - let me not sound more or less like a Cyborg from a James Cameron's famous movie ;-).

Here are two scripts that would enlable you to automate an FTP process from a source host to a destination.


###############
## U N I X  ###
###############

##########################
### FTP_Automation.ksh ###
##########################
#!/usr/bin/ksh

if [ "$#" -ne 3 ]
then
echo " USAGE: "
echo " ------ "
echo "\n"
echo " ksh FTP_Automation.ksh TARGET_HOST_OR_IP PASSWORD_OF_USERNAME USERNAME"
echo "\n"
echo " Examples : "
echo " ---------- "
echo "\n"
echo " ksh FTP_Automation.ksh unix_host_2 manager oracle"
echo "\n"
echo " ksh FTP_Automation.ksh 10.10.11.12 manager oracle"
echo "\n"
exit 1
fi

export target_host=$1
export username=$3
export password=2
ftp -niv << E_O_F
open $target_host
user $username $password
hash
bin
cd $HOME/unix2
put $HOME/unix1/file1
put $HOME/unix1/file2
put $HOME/unix1/file3
bye
E_O_F


#### The files are being copied from $HOME/unix1 directory
#### on unix_host_1 host (server) to $HOME/unix2 directory
#### on unix_host_2 host (server).


#### The files will be copied to $HOME/unix2 directory
#### Please change the locations and the file names in the 
#### script if you want the files to be copied to some other 
#### location.


###########
## E N D ##
###########


unix_host_1:$> ksh FTP_Automation.ksh 10.10.11.12 pass root


#####################
## W I N D O W S  ###
#####################

##########################
### FTP_Automation.txt ###
##########################

user user_name
pass_word
bin
hash
put "C:\Documents and Settings\oracle_and_unix\Desktop\file1.txt"
put "C:\Documents and Settings\oracle_and_unix\Desktop\file2.txt"
bye

#########
# E N D #
#########


##########################
### FTP_Automation.bat ###
##########################

ftp -n -s:C:\Documents and Settings\oracle_and_unix\Desktop\FTP_Automation.txt 10.10.11.12

#########
# E N D #
#########

The batch file FTP_Automation.bat implicitly calls the FTP_Automation.txt, the batch file (FTP_Automation.bat) can be scheduled in Scheduled Tasks of 'Control Panel' or simply run from the command prompt or even a double click on the batch file would inititate the ftp copy of files.

ORA-01801: date format is too long for internal buffer

2010-02-03T15:12:00.000+05:30


Target type=Agent 
Host=UNIX 
Occurred At=Feb 29, 2008 6:19:17 AM EST 
Message=25 successive severity upload requests have failed. Last 
severity upload error is ORA-01801: date format is too long for internal buffer 
Metric=Consecutive severity upload failure count 
Metric value=25 
Severity=Critical 
Acknowledged=No 
Notification Rule Name=Misconfigured agents 
Notification Rule Owner=SYSMAN

This is an out of the blue error that any environment would throw out as
part of the daily monitoring using OEM GC. This usually crops up from the
target host where the OEM agent takes care of the job of monitoring the
host and its instances and other processes. Any DBA out of his/her
instinctive efforts would try to bounce the agent thinking the agent has
gone crazy after having run for so many days or may be so many months.
But with an ./emctl upload from the AGENT_HOME/bin shows up
the same error again.

Some folks even try to bounce the OEM console giving it a vain attempt to
resolve the issue but to no avail. However the actual trick is to bounce
the OEM repository database along with the OEM console, to work around the
situation. The order of bounce to be followed is shown below.


Shutdown Order
--------------

OEM GC --> OEM Repository DB

OMS_HOME/opmn/bin/opmnctl stopall

ORACLE_HOME/bin/lsnrctl stop LISTENER

ORACLE_HOME/bin/dbshut


Startup Order
-------------

OEM Repository DB --> OEM GC

ORACLE_HOME/bin/dbstart

ORACLE_HOME/bin/lsnrctl start LISTENER

OMS_HOME/opmn/bin/opmnctl startall

Check for empty file in Unix systems

2010-01-25T15:27:00.006+05:30

Often, there arises a need to check if a particular file exists and/or is it empty ?, this usually is the scenario when developing shell scripts to automate stuff and a file is created by the script to hold intermediate results. So when the script is scheduled to run next time, it has to execute a sequence of commands if the file exists or it would execute a different set of commands when the file is no longer available.

Here is a simple code snippet which checks for the existence of a file and that it is NOT empty to return TRUE and it returns FALSE otherwise.



if [ -s test_file ]  ### if the test_file exists and that it is not empty
                     ### i.e., file has contents
then
echo "TRUE"          ### returns 'true' ONLY if the file exists and it is NOT empty,
                     ### i.e., file has contents - file is not of 0 bytes in size
else
echo "FALSE"         ### returns 'false' if the file exists and it is empty
                     ### (0 bytes in size).
                     ### returns 'false' also if the file does not exist
fi

For different results to appear, try creating a file 'test_file' and by changing its contents to make it a sized file and/or to make it an empty file.

Funny Oracle Error....

2010-01-23T15:59:00.002+05:30

I always have a habit of looking up for the errors returned by Oracle in the server itself using the 'oerr' utility before googling it out since 'oerr' gives you a suggestion of a quick fix saving you quite some time that is spent on rooting through the innumerable hits from Google for a workaround or a fix.

I stumbled upon an error when trying to pull the DDL of a materialized view using the dbms_metadata.get_ddl function, upon trying to find the cause/action of each of the errors in the returned error stack I was told to "Please contact someone who can fix the problem.", but who would that SOMEONE be....Hey Larry !!! Mr. Ellison, are you listening ;-)



SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','JUNK_MVIEW','BOGUS_SCHEMA') from dual;

ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-06512: at "SYS.UTL_XML", line 246
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7511
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



SQL> ! oerr ora 6502
06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause:
// *Action:



SQL> ! oerr ora 31605
31605, 00000, "the following was returned from %s in routine %s:\nLPX-%d: %s"
// *Cause:  An LPX routine (XML/XSL processing) returned an internal
//          error number to its PL/SQL wrapper routine in facility KUX which
//          provides the implementation for package UTL_XML.
// *Action: Look up the LPX error number and follow its corrective action.



SQL> ! oerr lpx 1
00001, 00000, "NULL pointer"
// *Cause:  A NULL pointer was detected as an internal error condition.
// *Action: This is a programming error by the caller of the XML parser.
//          Please contact someone who can fix the problem.

Windows Oracle database administration scripts for hot backup and export backup

2010-01-18T14:37:00.006+05:30

Automated conventional hot backup, export backup and their clean up script on a windows environment are pasted below - it is advised to go through the script / test on a Dev-Test environment before moving over to Prod since a couple of locations are to be hardcoded based on the environment. Most of the work is being carried out by pl/sql scripts, this gave me little chance to improvise my pl/sql skills ;-).

The .sql files in the .bat files create runtime sql files that will place the backup files in a folder named after the date string of the current day. The scripts are located in 'D:\oracle\backups\scripts' and the hot backup is taken in 'D:\oracle\backups\hot\DD-MON-YYYY' directory while the export backup is taken in 'D:\oracle\backups\exp\DD-MON-YYYY'. Based on your environment please change Drive letter and other locations, which has to be reflected in all the scripts and batch files.

Due to some limitations in the way this webpage is built some lines of the scripts may have been cropped, please click on the name of the script to open it in a new window which you can copy from.



###############################
# # #   H O T   B K U P   # # #
###############################



################################
### hot_backup_on_disk.bat  ####
################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-bkup_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-hot_bkup.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\03-arch_switch.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\04-ctrl_file.sql



###########################
####  01-bkup_dest.sql  ###
###########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\bkup_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\hot\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\bkup_dest_create.sql
exit



##########################
###  02-hot_bkup.sql  ####
##########################


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\hot_bkup_copy.sql
declare
cursor ts is select distinct(tablespace_name) 
from dba_data_files;
cursor fn (ts_name in VARCHAR2) is select file_name 
from dba_data_files
where tablespace_name=ts_name order by 1;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Datafiles --');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
for c in ts
loop
dbms_output.put_line('alter tablespace '||c.tablespace_name - 
||' begin backup;');
for d in fn (c.tablespace_name)
loop
dbms_output.put_line('host copy '||d.file_name|| - 
' D:\oracle\backups\hot\'||dir||'\.');
end loop;
dbms_output.put_line('alter tablespace '|| - 
c.tablespace_name||' end backup;');
dbms_output.put_line(chr(0));
end loop;
end;
/
spool off
@D:\oracle\backups\scripts\hot_bkup_copy.sql
exit



#############################
####  03-arch_switch.sql ####
#############################

###
### The LOG_ARCHIVE_FORMAT parameter
### plays a significant role in here
### the format of archive files that 
### is used here would be
### ${ORACLE_SID}_ARCH_*_*001, which
### has to be changed appropriately
### based on your environment
###


set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\arch_switch_copy.sql
declare
prev_seq number;
i number;
j number;
k number;
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
select sequence# into i from v$log where status='CURRENT';
prev_seq:=i-1;
execute immediate ('alter system switch logfile');
select sequence# into j from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
select sequence# into k from v$log where status='CURRENT';
execute immediate ('alter system switch logfile');
dbms_output.put_line(chr(0));
dbms_output.put_line('-----------------------');
dbms_output.put_line('-- Copying Logfiles ---');
dbms_output.put_line('-----------------------');
dbms_output.put_line(chr(0));
dbms_output.put_line('host copy - 
D:\oracle\oradata\arch\PROD_ARCH_*'||prev_seq||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||i||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||j||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
dbms_output.put_line('host copy -
D:\oracle\oradata\arch\PROD_ARCH_*'||k||-
'_*001 D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\arch_switch_copy.sql
exit



##########################
###  04-ctrl_file.sql  ###
##########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\ctrl_file_copy.sql
declare
dir varchar2(20);
 
begin
select to_char(sysdate,'DD-MON-YYYY') into 
dir from dual;
execute immediate 'alter session set tracefile_identifier='||''''||dir||'''';
execute immediate ('alter database backup controlfile to trace');
execute immediate 'alter database backup controlfile to'||''''||'D:\oracle\backups\hot\'||dir||'\controlfile_'||dir||'_bin.bkup'||'''';
dbms_output.put_line('host move D:\oracle\admin\PROD\udump\*'-
||dir||'*.trc D:\oracle\backups\hot\'||dir||'\.');
end;
/
spool off
@D:\oracle\backups\scripts\ctrl_file_copy.sql
exit



###############################
# # #   E X P   B K U P   # # #
###############################



####################################
###   export_backup_on_disk.bat  ###
####################################

set ORACLE_SID=PROD

set ORACLE_HOME=D:\oracle\10.2.0

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\01-exp_dest.sql

%ORACLE_HOME%\bin\sqlplus -S "/as sysdba" @D:\oracle\backups\scripts\02-exp_bkup.sql



#########################
###  01-exp_dest.sql  ###
#########################

set head off
set feed off
set line 150
set serveroutput on
spool D:\oracle\backups\scripts\exp_dest_create.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host mkdir D:\oracle\backups\exp\'||dir);
end;
/
spool off
@D:\oracle\backups\scripts\exp_dest_create.sql
exit



#########################
###  02-exp_bkup.sql  ###
#########################

set head off
set feed off
set line 300
set serveroutput on
spool D:\oracle\backups\scripts\exp_bkup_full.sql
declare
dir varchar2(20);
begin
select to_char(sysdate,'DD-MON-YYYY') into dir from dual;
dbms_output.put_line('host exp system/manager file=D:\oracle\backups\exp\'||dir||'\PROD_full.dmp log=D:\oracle\backups\exp\'||dir||'\PROD_full.log full=y buffer=10485760 consistent=y compress=n');
end;
/
spool off
@D:\oracle\backups\scripts\exp_bkup_full.sql
exit



#######################################
# # #   C L E A N U P   J O B S   # # #
#######################################



###########################
### delete_hot_bkup.bat ###
###########################

###
### Keeps the last 5 days of hot backups  
### on disk while deleting anything older
### than that
###

forfiles /p "D:\oracle\backups\hot" /s /m *.* /d -05 /c "cmd /c del @path"



###########################
### delete_exp_bkup.bat ###
###########################

###
### keeps the last 5 days of export backups
### on disk while deleting anything older than
### that
###

forfiles /p "D:\oracle\backups\exp" /s /m *.* /d -05 /c "cmd /c del @path"

HTML space report

2009-12-18T15:49:00.006+05:30

Have you ever received a request from the Business people about the space usage of a database to be delivered to their inboxes on a daily basis, well yup, I did and since it had come from some one in real high authority - I decided to generate a pleasant report of the usage of database.

I wrote a shell script that has an in-built sql script which retrieves information from the dba_* views. This result is spooled as an html file and sent to the recipient(s) as an attachment. The shell script takes the ORACLE_SID (database name) as the command line argument. Please review the script once after pasting it into your editor of choice for editting/spacing errors.



####################################################
# Name : space_report.ksh
#
# Usage : ksh space_report.ksh ORACLE_SID
#
####################################################

#!/usr/bin/ksh

if [ "$#" -lt 1 ]
then
echo " \nUsage : ksh space_report.ksh ORACLE_SID "
echo "\n"
exit 1
fi

export ORATAB=/var/opt/oracle/oratab   ## change this per your env.
export date_string=`date '+%d-%b-%Y'`
export ORACLE_SID=$1
export ORACLE_HOME=`grep $ORACLE_SID $ORATAB|head -1|awk -F":" '{print $2}'`
export To_id=to_id@oracleandunix.blogspot.com
export Cc_id=cc_id@oracleandunix.blogspot.com
export LOG_DIR=$HOME    
export LOG_FILE=$LOG_DIR/${ORACLE_SID}_space_report_${date_string}.html
export CAP_ORACLE_SID=`echo $ORACLE_SID|tr [:lower:] [:upper:]`


$ORACLE_HOME/bin/sqlplus -S "/as sysdba" << E_O_F

set markup html on
spool $LOG_FILE
set pages 50
set lines 100
set echo off
set serveroutput on
set feed off
col "TABLESPACE" format a25
col "FREE" format 9999999.99
col "MAX_FREE" format 9999999.99
col "NEXT_EXT" format a8
col "% Full" format 9999
col TOTAL format 999999.99
col USED format 999999.99
col SIZE format 9999.99

select name "DB NAME", to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "REPORT RUN TIME" 
from v$database;
prompt
prompt
select substr(t.tablespace_name,1,25) "TABLESPACE",
decode(b.EXTENT_MANAGEMENT,'LOCAL','L','DICTIONARY','D',b.EXTENT_MANAGEMENT) 
"EXT MGMT",
t.totspace/1024/1024  "TOTAL (MBs)",
round((t.totspace-sum(f.bytes))/1024/1024,2) "USED (MBs)",
round(sum(f.bytes)/1024/1024,2) "FREE (MBs)",
round(max(f.bytes)/1024/1024,2) "MAX FREE (MBs)",
round(((t.totspace-sum(f.bytes)) / t.totspace)*100) "% Full"
from dba_free_space f, dba_tablespaces b,
(select tablespace_name,sum(bytes) totspace
from dba_data_files
group by tablespace_name) t
where t.tablespace_name=f.tablespace_name
and t.tablespace_name=b.tablespace_name
group by t.tablespace_name,t.totspace,
b.next_extent,b.EXTENT_MANAGEMENT,b.tablespace_name
order by b.tablespace_name
prompt
prompt
prompt
select max('TOTAL SIZE OF THE DATABASE IS            ') "DESCRIPTION",
rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs' "SIZE"
from dba_Data_Files
union all
select max('USED SIZE OF THE DATABASE IS             '),
rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs'
from dba_segments
union all
select max('FREE SPACE AVAILABLE IN THE DATABASE IS  '),
rpad(round(sum(bytes)/1024/1024/1024,2),5,'0')||' GBs'
from dba_free_space;
prompt
prompt
E_O_F

(
cat << E_O_F
Space report for $ORACLE_SID Database
E_O_F
echo "\n"
/usr/bin/uuencode $LOG_FILE $LOG_FILE
)|mailx -s "Space Report - $CAP_ORACLE_SID" -c "$Cc_id" $To_id

#####################
### END OF SCRIPT ###
#####################

spfile in 9i, 10g and 11g

2009-11-18T18:30:00.024+05:30

There is a quite a difference in how your ouput shows up in 9i, 10g and 11g when you do a "show parameter spfile", here is how it shows up if the instance was brought up using an spfile.



Oracle 9i
SQL> sho parameter spfile

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
spfile                     string      ?/dbs/spfile@.ora

Oracle 10g
SQL> sho parameter spfile

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
spfile                     string      /oracle/product/10.2.0/dbs/spfile10G.ora
                                                
Oracle 11g

SQL> sho parameter spfile

NAME                       TYPE        VALUE
-------------------------- ----------- ------------------------------
spfile                     string      /oracle/product/11.1.0/dbs/spfile11G.ora

The output in 9i does not show the full ORACLE_HOME location, neither does it display the spfile name while in 10g and above the full ORACLE_HOME location gets displayed along with the spfile name.

ORA-00600 during controlfile recreation

2009-11-09T16:39:00.008+05:30

The destructive errors like ORA-00600, ORA-07445 etc show up at unusual times, one such perfect example of getting an ORA-00600 is explained below - it appeared during the execution of CREATE CONTROLFILE script. Before we go any further, here is a quick background of what's being done. A 10.2.0.1 database is to be migrated from one server to another.

As a proactive DBA, which I am not most of the time ;-) - I dumped the control file to the trace and went about creating the scripts to copy the files from the source to destination. All went well until I confronted ORA-600 during the control file recreation.

I had to quickly check the Oracle version which i am currently using, just to make sure there is not a mis-match between the source and destination Oracle binaries. The error pointed me out to a trace file that Oracle usually generates when it needs to provide more information on a particular error.



SQL> CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00600: internal error code, arguments: [kccscf_1], [9], [106012], [65535],
[], [], [], []

here is the error entry in alert log



Errors in file /prd/u01/PROD/udump/prod_ora_12326.trc:
ORA-00600: internal error code, arguments: [kccscf_1], [9], [106012], [65535], [], [], [], []
ORA-1503 signalled during: CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG


UNIX$> more /prd/u01/PROD/udump/prod_ora_12326.trc

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kccscf_1], [9], [106012], [65535], [], [], [], []
Current SQL statement for this session:
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 5
    MAXDATAFILES 12144
    MAXINSTANCES 12
    MAXLOGHISTORY 106012

Everything looked fine but there is no clue as to what's causing the mighty ORA-600 in this occasion, I gave it a couple of tries myself to troubleshoot before turning over to Mr. Ellison's boys for help!!!. A severity - 1 was raised for this issue and I was pointed out at the value of MAXLOGHISTORY to be equal to 65535 as the maximum limit against the value that I had as 106012.

This looks silly, how come a trace controlfile that was dumped from an SqlPlus session have a value not recommended by Oracle itself. It was later reported by the Oracle technician that its a Bug in 10.2.0.1 which has been fixed in 10.2.0.4. Corrected script is shown below.



CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 5
    MAXDATAFILES 12144
    MAXINSTANCES 12
    MAXLOGHISTORY 65535

Datafile naming error

2009-11-04T14:45:00.010+05:30

A text editor formatting flaw could really end a life of a DBA in jeopardy, that is what I felt when I realized that one of the tablespaces that I created had a filename with white spaces - such a database gone live into the production is a nightmarish experience. Here is the situation, a tablespace creation script (vi editor) had a filename broken down to the next line and I thought it just came down to the next line after having reached the end of line, but it wasn't. It looked like shown below



CREATE TABLESPACE "USERS_BIG"
LOGGING
DATAFILE '/prd/u01/oradata/prod/users_big01.dbf' SIZE 2500M REUSE, 
'/prd/u01/oradata/prod/use
rs_big02.dbf' SIZE 2500M REUSE,
'/prd/u01/oradata/prod/users_big03.dbf' SIZE 5000M REUSE;

This is what it looks like from the inside, of the database and that of the Operating System.



SQL> select file_name from dba_data_files 
  2  where tablespace_name like '%BIG%';

FILE_NAME
------------------------------------------------------
/prd/u01/oradata/prod/users_big01.dbf
/prd/u01/oradata/prod/use
rs_big02.dbf

/prd/u01/oradata/prod/users_big03.dbf

UNIX:/prd/u01/oradata/prod > ls -ltr
total 184320194
-rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users02.dbf
-rw-r-----   1 oracle   dba      2621448192 May 22 17:12 use
rs_big02.dbf
-rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users01.dbf
-rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools02.dbf
-rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools01.dbf

Here are some vain efforts to locate the file individually, but nothing worked out.



UNIX:/prd/u01/oradata/prod > ls -ltri
total 184320194
        20 -rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users02.dbf
        19 -rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users01.dbf
        30 -rw-r-----   1 oracle   dba      2621448192 May 22 17:12 use
rs_big02.dbf
        18 -rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools02.dbf
        17 -rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools01.dbf

UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -print
./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec ls -ltr {} \;
-rw-r-----   1 oracle   dba      2621448192 May 22 17:12 ./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec ls -ltr {} \;|awk '{print $9}'
./use
rs_big02.dbf

UNIX:/prd/u01/oradata/prod > cp "users_big02.dbf" tempos.dbf
cp: cannot access users_big02.dbf

I made a copy of the broken name datafile to a corrected name datafile at the OS level and then renamed it at the database level by including the whitespaces in the source datafile argument of the 'alter database rename file' statement. The copying at the OS level required the entire white spaces to be included in the source file name argument of the 'cp' command, while being enclosed in double quotes (") as shown below.



UNIX:/prd/u01/oradata/prod > cp "use
> rs_big02.dbf" users_big02.dbf

SQL> select file_name from dba_data_files 
  2  where tablespace_name like '%BIG%';

FILE_NAME
----------------------------------------------------
/prd/u01/oradata/prod/users_big01.dbf
/prd/u01/oradata/prod/use
rs_big02.dbf

/prd/u01/oradata/prod/users_big03.dbf
/prd/u01/oradata/prod/users_big04.dbf

  
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO                           ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
INDX                           ONLINE
PERFSTAT                       ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
USERS_BIG                      ONLINE

9 rows selected.


SQL> alter tablespace users_big offline normal;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO                           ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
INDX                           ONLINE
PERFSTAT                       ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
USERS_BIG                      OFFLINE

9 rows selected.

SQL> !ls -ltr
total 185090258

-rw-r-----   1 oracle   dba      5242888192 May 23 04:22 users_big03.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:22 users_big01.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:22 use
rs_big02.dbf
-rw-r-----   1 oracle   dba      10485768192 May 23 04:22 undo.dbf
-rw-r-----   1 oracle   dba      209715712 May 23 04:22 redo_PROD_2a.log


SQL> select sum(bytes)/1024/1024,file_name from dba_data_files 
  2  where tablespace_name like '%USER%' 
  3  group by file_name order by file_name;

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- -----------------------------------------------
                     /prd/u01/oradata/prod/use
                     rs_big02.dbf

                5000 /prd/u01/oradata/prod/users01.dbf
                5000 /prd/u01/oradata/prod/users02.dbf
                5000 /prd/u01/oradata/prod/users03.dbf
                5000 /prd/u01/oradata/prod/users04.dbf
                     /prd/u01/oradata/prod/users_big01.dbf
                     /prd/u01/oradata/prod/users_big03.dbf
                     /prd/u01/oradata/prod/users_big04.dbf

8 rows selected.


SQL> alter database rename file '/prd/u01/oradata/prod/use
  2  rs_big02.dbf' to '/prd/u01/oradata/prod/users_big02.dbf';

Database altered.


SQL> select sum(bytes)/1024/1024,file_name from dba_data_files 
  2  where tablespace_name like '%USER%'group by file_name 
  3  order by file_name;

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- ------------------------------------------------
                5000 /prd/u01/oradata/prod/users01.dbf
                5000 /prd/u01/oradata/prod/users02.dbf
                5000 /prd/u01/oradata/prod/users03.dbf
                5000 /prd/u01/oradata/prod/users04.dbf
                     /prd/u01/oradata/prod/users_big01.dbf
                     /prd/u01/oradata/prod/users_big02.dbf
                     /prd/u01/oradata/prod/users_big03.dbf
                     /prd/u01/oradata/prod/users_big04.dbf

8 rows selected.


SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                CHANGE#    TIME
---------- ------- ------- ----------------- ---------- ---------
        18 OFFLINE OFFLINE OFFLINE NORMAL             0
        19 OFFLINE OFFLINE OFFLINE NORMAL             0
        20 OFFLINE OFFLINE OFFLINE NORMAL             0
        21 OFFLINE OFFLINE OFFLINE NORMAL             0

SQL> alter tablespace USERS_BIG online;

Tablespace altered.


SQL> select * from v$recover_file;

no rows selected


SQL> select sum(bytes)/1024/1024,file_name from dba_data_files 
  2  where tablespace_name like '%USER%' 
  3  group by file_name order by file_name;

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- ----------------------------------------------------
                5000 /prd/u01/oradata/prod/users01.dbf
                5000 /prd/u01/oradata/prod/users02.dbf
                5000 /prd/u01/oradata/prod/users03.dbf
                5000 /prd/u01/oradata/prod/users04.dbf
                2500 /prd/u01/oradata/prod/users_big01.dbf
                2500 /prd/u01/oradata/prod/users_big02.dbf
                5000 /prd/u01/oradata/prod/users_big03.dbf
                5000 /prd/u01/oradata/prod/users_big04.dbf

8 rows selected.

Removing the file at the OS level is a tricky job, the safest is to locate the inode number of the file and use the 'inum' flag of the 'find' command to remove it with the 'exec' flag as shown below



UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -print
./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec ls -ltr {} \;
-rw-r-----   1 oracle   dba      2621448192 May 23 04:22 ./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec rm {} \;
UNIX:/prd/u01/oradata/prod >


UNIX:/prd/u01/oradata/prod > ls -tlr
total 184320194
-rw-r-----   1 oracle   dba      1048584192 May 23 04:30 sysaux.dbf
-rw-r-----   1 oracle   dba      10485768192 May 23 04:42 undo.dbf
-rw-r-----   1 oracle   dba      5242888192 May 23 04:45 users_big04.dbf
-rw-r-----   1 oracle   dba      5242888192 May 23 04:45 users_big03.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:45 users_big02.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:45 users_big01.dbf
-rw-r-----   1 oracle   dba      9961480192 May 23 04:45 system01.dbf


SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO                           ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
INDX                           ONLINE
PERFSTAT                       ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
USERS_BIG                      ONLINE

9 rows selected.

Oracle 11gR2 Database Parameters - Reference

2009-10-12T14:47:00.005+05:30

Ever since I did the installation of 11g in July this year, I had been longing for a web link of all the database parameters, static and dynamic views of 11g so I could look it for reference just like the 10gR2 and 9iR2, to my surprise - I finally hit it. Master index of parameters and data dictionary description in 11gR2 and a description of v$views in 11gR2 is in here.

Metric Collection Error in Grid Control

2009-10-07T16:56:00.024+05:30



Target Name=prod.world 
Target type=Database Instance 
Host=unixhost1 
Occurred At=Aug 27, 2009 10:49:40 PM EDT 
Message=Metric evaluation error start - Target is in broken state. 
Reason - Get dynamic property error,Dynamic Category property error 
Severity=Metric Error Start 
Acknowledged=No 
Notification Rule Name=Database Availability and Critical States 
Notification Rule Owner=SYSMAN

If you have OEM Grid Control (OEM-GC) configured on your environment, then the above is sometimes a routine alert page for a database which is a 9.2.0.6.0 being monitored by an OMS of version 10.2.0.5.0. The status for such a database in the Database sub-tab of the Targets tab on OEM-GC shows up some thing like below. The details like database name, server name etc have been shaded out for obvious reasons.
Click on the screen shot for an enlarged view of the picture.

Select the database and hit the configure button on top of the page to configure its settings to change the monitoring account from dbsnmp to the sysdba account. The below screenshots show a step by step procedure.

Change the role to SYSDBA to blank out the Monitor USername and enter 'sys' as the username its password.

Now click the Test Connection to check if the password is accepted and the connection was successful to the database. If not, check if the credentials are correct.

Click Next on top of the page and select "Skip these steps" and click Next for a review and submit it for all the changes to take effect.

The final screen shows the result of successful configuration, click OK to be routed back to the Database Target.

The above solution also fixes the issue of a 9.2.0.6.0 target database core dumping when being associated with an OMS of 10.2.0.5.0

Users recreation with quotas, privileges and roles

2009-10-01T16:51:00.011+05:30

It is quite often in some Oracle shops to refresh databases or a specific schemas of a database using the traditional exp/imp method while retaining the same roles and privileges and other resources is a nerve racking job. Here are some scripts to make life easy for such a DBA to retain the user quotas, privileges and roles (QPR). These scripts generate the sql statements to recreate the QPRs and are named in a sequential order and are to be run in the same sequetial order. Its a known fact that there are misspellings in the script names, which were created when it was a real tizzy situation.


------------------------------
--- 01_useR_recreation.sql ---
------------------------------

set line 150
select 'create user '||username||' identified by values '||
''''||password||''''||' default tablespace '||
DEFAULT_TABLESPACE||' temporary tablespace '||
TEMPORARY_TABLESPACE||' profile '||PROFILE||';' from dba_users 
order by username;

-------------------------------------
--- 02_useR_Quota_recreateion.sql ---
-------------------------------------

set serveroutput on size 1000000 
set line 150
declare
a number;
cursor q is select username,max_bytes,tablespace_name 
from dba_ts_quotas order by username;
begin
for i in q
loop
if i.max_bytes=-1 then
dbms_output.put_line('alter user '||i.username||
' quota unlimited'||' on '||i.tablespace_name||';');
else
dbms_output.put_line('alter user '||i.username||
' quota '||i.max_bytes||' on '||i.tablespace_name||';');
end if;
end loop;
end;
/


---------------------------------------
--- 03_useR_sys_priv_recreation.sql ---
---------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users 
order by 1;
cursor priv (user_nm in varchar2) is select 
distinct(grantee),privilege from dba_sys_privs where 
grantee=user_nm and grantee not in 
('CONNECT','SYSTEM','RESOURCE','SYS','EXP_FULL_DATABASE',
'TSMSYS','RECOVERY_CATALOG_OWNER','SCHEDULER_ADMIN',
'AQ_ADMINISTRATOR_ROLE','DIP','OEM_ADVISOR','DBA',
'IMP_FULL_DATABASE','OEM_MONITOR','DBSNMP','OUTLN') 
order by grantee;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in priv(i.username)
loop
dbms_output.put_line('grant '||j.privilege||' to '
||i.username||';');
end loop;
end loop;
end;
/


------------------------------------
--- 04_uSer_role_recreateion.sql ---
------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users order 
by 1;
cursor role (user_nm in varchar2) is select distinct 
GRANTEE,granted_role from dba_role_privs where grantee 
not in ('DBSNMP','DIP','OUTLN','SYS','SYSTEM','TSMSYS',
'EXP_FULL_DATABASE','DBA','SELECT_CATALOG_ROLE',
'IMP_FULL_DATABASE','EXECUTE_CATALOG_ROLE',
'LOGSTDBY_ADMINISTRATOR') and grantee=user_nm order by 
grantee;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in role(i.username)
loop
dbms_output.put_line('grant '||j.granted_role||' to '
||i.username||';');
end loop;
end loop;
end;
/


-----------------------------------------
--- 05_user_tab_privs_recreateion.sql ---
-----------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users 
order by 1;
cursor tab_priv (user_nm in varchar2) is select 
distinct(grantee),owner,table_name,privilege
from dba_tab_privs where grantee not in ('SYSTEM',
'PUBLIC','SELECT_CATALOG_ROLE','GATHER_SYSTEM_STATISTICS',
'EXP_FULL_DATABASE','SYS','EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE','LOGSTDBY_ADMINISTRATOR',
'AQ_USER_ROLE','AQ_ADMINISTRATOR_ROLE','HS_ADMIN_ROLE',
'DBA','IMP_FULL_DATABASE','OEM_MONITOR','DBSNMP',
'OUTLN') and grantee=user_nm order by grantee,owner;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in tab_priv(i.username)
loop
dbms_output.put_line('grant '||j.privilege||' on '||j.owner
||'.'||j.table_name||' to '||i.username||';');
end loop;
end loop;
end;
/

Here is a bonus script to only recreate the passwords of users in a database.


----------------------------------------
--- user_old_password_recreation.sql ---
----------------------------------------

set pages 0
set line 100

select 'alter user '||username||' identified by values '||
''''||password||''''||';' from dba_users order by username;

PS: Please format the lines of code once they have been pasted in your editor as the lines may have been broken down to the next ones while publishing the scripts in a web page

Split utility of Unix

2009-08-28T17:06:00.008+05:30

There is a 'split' utility in unix, which allows one to split a sufficiently big file into small chunks of equal sizes. The man pages of split give out more options that could be used based on the need, however a simple example has been shown below in which a text file having 1000 lines has been split into 10 equal chunks of 100 lines each.

The split files have been merged with the help of a 'cat' command in a 'for' loop. Extreme care must be taken when splitting binary/dmp files as splitting succeeds but the merging shows misleading results and the file size of the subjected file before and after the split does not seem to match.

First off, we create a file named 'un_split_file.out', which has 1000 lines - a partial look of it is shown below



UNIX:/prd/u01/acme> export i=0
UNIX:/prd/u01/acme> echo $i
0
UNIX:/prd/u01/acme> while [ "$i" -ne 1000 ]
> do
> echo "This is line $i" >> un_split_file.out
> i=`expr $i \+ 1`
> done &

UNIX:/prd/u01/acme> wc -l split_file.out
    1000 split_file.out

UNIX:/prd/u01/acme > head -10 un_split_file.out
This is line 0
This is line 1
This is line 2
This is line 3
This is line 4
This is line 5
This is line 6
This is line 7
This is line 8
This is line 9

UNIX:/prd/u01/acme > tail -10 un_split_file.out
This is line 990
This is line 991
This is line 992
This is line 993
This is line 994
This is line 995
This is line 996
This is line 997
This is line 998
This is line 999

UNIX:/prd/u01/acme > ls -ltr un_split_file.out
-rw-r--r--   1 oracle   dba        16890 Aug 25 05:19 un_split_file.out

Now comes the usage of 'split' command, here 'split' has been passed with 4 arguments



-l 100 -> Line Count, which means after every 100 lines from the beginning of 
the files, a new file will be created

-a 2  -> Based on the line count parameter,required number of split files 
will be created with a 2 characted substring. The substring by default has 
the following trend aa, ab, ac and so on.

Third argument is the name of the file to be split

Fourth argument is the text for naming of the split files


UNIX:/prd/u01/acme> split -l 100 -a 2 un_split_file.out split_file.part_

UNIX:/prd/u01/acme> ls -ltr
total 544
-rw-r--r--   1 oracle   dba        16890 Aug 25 05:19 un_split_file.out
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_aj
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ai
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ah
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ag
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_af
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ae
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ad
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ac
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ab
-rw-r--r--   1 oracle   dba         1590 Aug 25 05:22 split_file.part_aa
UNIX:/prd/u01/acme>

The old file 'un_split_file.out' will be moved to 'un_split_file.out.deleted' so it
does not conflict with the new file that will be created by merging the split files
using the 'cat' command.



UNIX:/prd/u01/acme> mv un_split_file.out un_split_file.out.deleted

UNIX:/prd/u01/acme> for i in `ls split_file.part*`
> do
> cat $i >> un_split_file.out
> done &

The result is the creation of a file named 'un_split_file.out' which has just all
the contents like it did before being split. The split/merge operation does not
remove the source or the original files as seen below.



UNIX:/prd/u01/acme> ls -ltr un_split*
-rw-r--r--   1 oracle   dba        16890 Aug 25 05:19 un_split_file.out.deleted
-rw-r--r--   1 oracle   dba        16890 Aug 25 05:25 un_split_file.out
UNIX:/prd/u01/acme> wc -l un_split_file.out
    1000 split_file.out
UNIX:/prd/u01/acme>

UNIX:/prd/u01/acme > ls -ltr
total 578
-rw-r--r--   1 oracle   dba        16890 Aug 25 05:19 un_split_file.out.deleted
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_aj
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ai
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ah
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ag
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_af
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ae
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ad
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ac
-rw-r--r--   1 oracle   dba         1700 Aug 25 05:22 split_file.part_ab
-rw-r--r--   1 oracle   dba         1590 Aug 25 05:22 split_file.part_aa
-rw-r--r--   1 oracle   dba        16890 Aug 25 05:25 un_split_file.out

Fetching ip addresses of a server names list

2009-08-06T17:59:00.005+05:30

server_names_to_ip

This is a script to fetch the ip addresses of server names, the script takes a run time argument of a unique server names list in a file and uses the nslookup command of unix to get the fully qualified server name, its ip address and its alias. The output is stored in the srvr_nmes_2_ip.out file.

The script has to be run on any server on your environment which should be on the same network as do the other servers in the unique server names list. The script is designed for a UNIX environment.



############################################################
#
# Pass a unique server names list as the run time argument
#
# Usage : ksh server_names_2_ip.ksh server_list.parm
#
# Output file : srvr_nmes_2_ip.out
#
############################################################
#
# Name : server_names_2_ip.ksh
#
############################################################
#!/usr/bin/ksh

### pass the server list file as a runtime argument ($1)

server_list=$1
touch srvr_nmes_2_ip.out

for i in  $(cat $server_list)
do
  F_Q_N=`nslookup $i|grep -i "Name"|awk -F"    " '{print $2}'`
  F_Q_N_num=`nslookup $i|grep -in "Name"|awk -F":" '{print $1}'`
  ip_line_nul=`expr $F_Q_N_num \+ 1`
  alias_num=`expr $ip_line_nul \+ 1`
  ip_address=`nslookup $i|sed -n "$F_Q_N_num","$alias_num"p|grep "Address"| \
awk -F"  " '{print $2}'`
  alias_name=`nslookup $i|sed -n "$F_Q_N_num","$alias_num"p|grep "Aliases"| \
awk -F"  " '{print $2}'`
  echo "$F_Q_N | $ip_address | $alias_name" >> srvr_nmes_2_ip.out
done

###################
## End Of Script ##
###################

#########################################
## Now, view the file srvr_nmes_2_ip.out
## for output
#########################################

Error in naming a datafile

2009-07-29T15:13:00.004+05:30

At times life as a DBA gets so tiring that you goof up even a simplest task of adding a datafile to a tablespace, I did too (chuckles), I entered a comma(,) instead of a period(.) for the datafile extentsion.



SQL> alter tablespace BLOB_TS add datafile
  2  '/prd/u01/oradata/prod/lob08,dbf' size 5000M;

Only after hitting return did I realize that I made a blunder in the datafile name, I had to immediately take the datafile offline to prevent any users from filling it up with data.



SQL> alter database datafile '/prd/u01/oradata/prod/lob08,dbf' offline;

Database altered.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
RECOVER /prd/u01/oradata/prod/lob08,dbf

The status above shows that the datafile needs recovery, I ran a 'RECOVER DATAFILE' command to recover the datafile and have its status changed from 'RECOVER' to 'OFFLINE'. In order for this to be successful, the same log sequence should be running in the database if the database is in no-archive mode or it could be a different log sequence if the database is in archive mode but the archive logs be present on disk to be applied if needed.

In my case, it was in archive mode and luckily the same log sequence was running when I ran the 'RECOVER DATAFILE' command



SQL> recover datafile '/prd/u01/oradata/prod/lob08.dbf';
Media recovery complete.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
OFFLINE /prd/u01/oradata/prod/lob08,dbf

Now the file has to be copied with the corrected name at the OS level.



SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 10:58 lob08,dbf

SQL> !cp "lob08,dbf" "lob08.dbf"
SQL>

SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 10:58 lob08,dbf
-rw-r-----   1 oracle   dba      524296192 Jul 16 11:10 lob08.dbf

The file is then renamed at the database level and should be brought online.



SQL> alter database rename file '/prd/u01/oradata/prod/lob08,dbf' to
  2  '/prd/u01/oradata/prod/lob08.dbf';

SQL> alter database datafile '/prd/u01/oradata/prod/lob08.dbf' online;

Database altered.

SQL> select status,name from v$datafile where name like '%lob08%';

STATUS  NAME
------- ----------------------------------------------------------------
ONLINE  /prd/u01/oradata/prod/lob08.dbf

It is now safe to remove the file with comma(,) in its name at the OS level



SQL> !rm "lob08,dbf"
SQL> !ls -ltr lob08*
-rw-r-----   1 oracle   dba      524296192 Jul 16 11:10 lob08.dbf

ORA-06510, ORA-06512 errors during export.

2009-07-29T15:08:00.003+05:30

Have you ever received an error like ORA-06510 or ORA-06512 during an export backup of a database ? - The reason behind this is some Oracle shops have a specific database user using which the databases are exported out rather than the traditional admin users like SYS, SYSTEM or an OS authenticated user "/".

The non-admin database user running the export backup does not have an execute privilege on the two packages namely DBMS_EXPORT_EXTENSION and DBMS_DEFER_IMPORT_INTERNAL. A successful execute grant on the two packages to the non-admin user resolves the issue of ORA-06510 and ORA-06512



EXP-00008: ORACLE error 6510 encountered
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50
ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126
ORA-06512: at line 1

SQL> select grantee,owner,table_name,privilege from dba_tab_privs where 
  2  grantee='EXPORT_USER';

no rows selected

SQL> grant execute on DBMS_EXPORT_EXTENSION to EXPORT_USER;

Grant succeeded.

SQL> grant execute on DBMS_DEFER_IMPORT_INTERNAL to EXPORT_USER;

Grant succeeded.

SQL> select grantee,owner,table_name,privilege from dba_tab_privs where 
  2  grantee='EXPORT_USER';

GRANTEE    OWNER      TABLE_NAME                     PRIVILEGE
---------- ---------- ------------------------------ ----------
EXPORTER   SYS        DBMS_EXPORT_EXTENSION          EXECUTE
EXPORTER   SYS        DBMS_DEFER_IMPORT_INTERNAL     EXECUTE

11g Installation

2009-07-15T15:36:00.001+05:30

Weeeeeehhho, today I got my hand over the installation of 11gR1(11.1.0.6.0), actually the installation process is no different than its predecessors. The X-windows utility that I used did drag the feet around in the whole installation process as I had to wait patiently (HEIGHTS OF PATIENCE) to get the control over to the buttons/text boxes etc. Although it took quite a while for the installation to complete but in the end it brought a big grin on my face.