Monday, August 13, 2012

STDIN, STDOUT and STDERR


/dev/stdin standard input

/dev/stdout standard output

/dev/stderr standard error 


STANDARD INPUT AND OUTPUT

Commands take a stream of characters as input (input stream) and generate a stream of characters as output (output stream). These are called streams as they have no internal structure. Even the NEWLINE character is not treated differently.

While the input stream for a command forms standard input, the output stream of a command is called standard output. The UNIX shell usually treats the terminal keyboard as standard input and the terminal screen as standard output. However, standard input and output may be redirected to files.

STANDARD ERROR

The system also provides for a third stream, the standard error stream. Most commands use the standard error stream to display error messages. By default, standard error is displayed on the standard output.standard error may be redirected to an output file using the appropriate file descriptor.

A file descriptor is a number associated with a file of data.

0 - standard input (/dev/stdin -> /proc/self/fd/0)
1 - standard output (/dev/stdout -> /proc/self/fd/1)
2 - standard error (/dev/stderr -> /proc/self/fd/2)

$ cat file1 1> newfile 2> errmesg

This will write the contents of file1 to newfile. Any error messages are redirected to the errmesg file.

Now, lets look at some examples of how the STDIN, STDOUT and STDERR work.


$ ls -ltr
total 20
-rw-r--r-- 1 oracle dba   45 Jan 12 23:19 file1

$ cat file1
this is line 1
this is line 2
this is line 3

$ cat file1 > file1.out 2> file1.err    

The above command will redirect the contents of file1 to file1.out while any error/warning messages will be written to file1.err. This command can also be written as shown below where the 1> and 2> denote the STDOUT and STDERR numerically. The 0> for STDIN is not really needed since by default the STDIN is either a keyboard to key in commands or contents from a file.

$ cat file1 1> file1.out 2> file1.err


$ ls -ltr
total 20
-rw-r--r-- 1 oracle dba   45 Jan 12 23:19 file1
-rw-r--r-- 1 oracle dba   45 Jan 12 23:20 file1.out
-rw-r--r-- 1 oracle dba    0 Jan 12 23:20 file1.err


$ cat file1.out
this is line 1
this is line 2
this is line 3

$ cat file1.err

Lets look at another example, file2 does not exist but the redirectional error/warning messages will not be displayed on the screen


$ cat file1.err

$ cat file2
cat: file2: No such file or directory

$ cat file2 1> file2.out 2> file2.err

$ ls -tlr file2*
-rw-r--r-- 1 oracle dba    0 Jan 12 23:21 file2.out
-rw-r--r-- 1 oracle dba   38 Jan 12 23:21 file2.err

$ cat file2.out

$ cat file2.err
cat: file2: No such file or directory

A point to be noted is file2.err now has the error message that was otherwise displayed on screen

Let us now avoid the STDOUT and just work with STDIN and STDERR

$ cat file5
cat: file5: No such file or directory

$ cat file5 2> file5.err

$ ls -tlr file5*
-rw-r--r-- 1 oracle dba   38 Jan 12 23:24 file5.err

$ cat file5.err
cat: file5: No such file or directory

There is also a possibility of appending something to the STDERR like below

$ cat file6
cat: file6: No such file or directory

$ cat file6 2>> file5.err

$ cat file5.err
cat: file5: No such file or directory
cat: file6: No such file or directory

Now, comes that most interesting part that we see most often in production environments. The 2>&1, which means that the output going to the file descriptor 2 (STDERR) should go to the same place as file descriptor 1 (STDOUT). It can better be understood with an example.

$ cat file7
cat: file7: No such file or directory

$ cat file7 2>&1
cat: file7: No such file or directory

$ cat file7 1> file7.out 2>&1

The above command tries to redirect (1>) the contents of a (non-existing) file,file7,to file7.out (STDOUT) - also any error/warning messages that are intended for STDERR should also be redirected (1>) to file7.out (2>&1)

$ ls -tlr file7*
-rw-r--r-- 1 oracle dba   38 Aug 12 23:30 file7.out

$ cat file7.out
cat: file7: No such file or directory

Thursday, March 15, 2012

Oracle Enterprise Manager Cloud Control

aaaah.... Its been a very long time since I showed up on this space, actually I had been busy with the new job that I took up and then stabilizing the environment etc. The new job sure has some new and exciting opportunities for me in store, as usual I am deploying shell scripts to make my job simpler and easier (this part I do in most of the environments that I work to become even lazier...LOL). The most new thing I was able to work on was the deployment of OEM 12c.

I was told that setting up an OEM environment is very tricky and makes you exhausted in no time which did happen to me but I learned that if you follow the Oracle documentation line by line, it pays off really well. So I went through the prerequisites of setting up 12c from the Oracle documentation and I was able to complete the installation without much hassles. If you set out to install 12c in your environment, better first scan through the Oracle documentation.

Thursday, August 4, 2011

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.

Thursday, July 21, 2011

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.

Tuesday, May 24, 2011

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

Thursday, November 11, 2010

Clearing the RECYCLEBIN in an Oracle database

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.
                                                 

Thursday, May 20, 2010

sga_max_size and sga_target

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_


Monday, March 22, 2010

Oracle Tuning Methodology

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.















Wednesday, March 3, 2010

Data Guard Operational Modes

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









Wednesday, February 17, 2010

FTP automation on Windows and Unix

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.

Wednesday, February 3, 2010

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



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



Monday, January 25, 2010

Check for empty file in Unix systems

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.




Saturday, January 23, 2010

Funny Oracle Error....

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.




Monday, January 18, 2010

Windows Oracle database administration scripts for hot backup and export backup

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"

Friday, December 18, 2009

HTML space report

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


Wednesday, November 18, 2009

spfile in 9i, 10g and 11g

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.

Monday, November 9, 2009

ORA-00600 during controlfile recreation

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


Wednesday, November 4, 2009

Datafile naming error

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.


Monday, October 12, 2009

Oracle 11gR2 Database Parameters - Reference

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.