Which Oracle database version you use?

Which Oracle database version you use?

You can select multiple choices. You can vote for your test environment as well as for your production environment.

Which OS you like best for your oracle database

Which OS you like best for your oracle

Cast your vote.

Script to report used space in database tablespaces

Abstract
This script will report disk space used in database tablespaces.

Execution Environment:
Any client environment which can connect to database and execute SQL. For example, SQL*plus, iSQL*plus.

Required Privileges:
Need SELECT ANY TABLE privilege. Need direct privilege not via a role.

Usage:
If you are outside sqlplus then issue,
$ sqlplus username/password @TBS_USED_SPACE.SQL
or if you are inside sqlplus then issue,
SQL> @TBS_USED_SPACE.SQL

Instructions to use:
Copy the codes into a file named TBS_USED_SPACE.SQL and run that script.

Description of the Script
This script displays space usage and storage parameters of your tablespaces and datafiles.

Sample Input:
No input is required as it will report on all tablespaces in the database.

Sample Output:

Tablespace Datafiles

Name       Located in file_ID + File Name                     Size       Used
---------- -------------------------------------------------- ---------- ----------
EXAMPLE    5 D:\ORADATA\A\EXAMPLE01.DBF                        102,400K    79,232K
SYSAUX     3 D:\ORADATA\A\SYSAUX01.DBF                         286,720K   282,816K
SYSTEM     1 D:\ORADATA\A\SYSTEM01.DBF                         501,760K   493,632K
UNDOTBS1   2 D:\ORADATA\A\UNDOTBS01.DBF                         66,560K     9,920K
USERS      4 D:\ORADATA\A\USERS01.DBF                            5,120K     4,608K

Tablespace definitions

Name           Init     Next %Inc  Min         Max Stat Percent full
---------- -------- -------- ---- ---- ----------- ---- ------------------------------
EXAMPLE      65,536                  1  2147483645 OnL  |*********************       |
SYSAUX       65,536                  1  2147483645 OnL  |*************************** |
SYSTEM       65,536                  1  2147483645 OnL  |*************************** |
UNDOTBS1     65,536                  1  2147483645 OnL  |***                         |
USERS        65,536                  1  2147483645 OnL  |*************************   |

Main Script

SET ECHO off
REM NAME:   TBS_USED_SPACE.SQL
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT ANY TABLE privilege (directly - not via a role)
REM --------------------------------------------------------------------------
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com      
REM ------------------------------------------------------------------------ 
REM PURPOSE:
REM    To display space usage and storage parameters of your tablespaces
REM    and datafiles.
REM ---------------------------------------------------------------------------
REM EXAMPLE:
REM Tablespace Datafiles
REM 
REM Name       Located in file_ID + File Name                     Size       Used
REM ---------- -------------------------------------------------- ---------- ----------
REM EXAMPLE    5 D:\ORADATA\A\EXAMPLE01.DBF                        102,400K    79,232K
REM SYSAUX     3 D:\ORADATA\A\SYSAUX01.DBF                         286,720K   282,816K
REM SYSTEM     1 D:\ORADATA\A\SYSTEM01.DBF                         501,760K   493,632K
REM UNDOTBS1   2 D:\ORADATA\A\UNDOTBS01.DBF                         66,560K     9,920K
REM USERS      4 D:\ORADATA\A\USERS01.DBF                            5,120K     4,608K
REM 
REM Tablespace definitions
REM 
REM Name           Init     Next %Inc  Min         Max Stat Percent full
REM ---------- -------- -------- ---- ---- ----------- ---- ------------------------------
REM EXAMPLE      65,536                  1  2147483645 OnL  |*********************       |
REM SYSAUX       65,536                  1  2147483645 OnL  |*************************** |
REM SYSTEM       65,536                  1  2147483645 OnL  |*************************** |
REM UNDOTBS1     65,536                  1  2147483645 OnL  |***                         |
REM USERS        65,536                  1  2147483645 OnL  |*************************   |
REM
REM --------------------------------------------------------------------------
REM Main text of script follows:

set pause off
set feed off
set verify off
set pagesize 100
set linesize 250
clear screen
col tn   format a10 heading 'Name' trunc
col fn   format a50 heading 'Located in file_ID + File Name'
col bts  format a10 heading 'Size'
col used format a10 heading 'Used'

col ex   format        9999 heading 'NrExt'
col rs   format 999,999,999 heading 'RBSsize'
col init format     999,999 heading 'Init'
col next format     999,999 heading 'Next'
col mi   format         999 heading 'Min'
col ma   format  9999999999 heading 'Max'
col pct  format         990 heading '%Inc'
col st   format          a4 heading 'Stat'
col sn   format         a15 heading 'Segm Name'
col ts   format         a15 heading 'In Table Space'

create or replace view free_view
as
select file_id, sum(bytes) free_bytes
from sys.dba_free_space
group by file_id;

clear screen

prompt Tablespace Datafiles
select d.tablespace_name tn,
       f.file_id||' '||file_name fn,
       to_char(f.bytes/1024,'999,999')||'K' bts,
       to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id(+)
order by d.tablespace_name;

prompt
prompt                  Tablespace definitions

define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')"
col gr format a30 heading 'Percent full'
select d.tablespace_name tn,
       d.initial_extent init,
       d.next_extent next,
       d.pct_increase pct,
       d.min_extents mi, max_extents ma,
       decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st,
       rpad(&part1,29,' ')||'|' gr
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id
order by d.tablespace_name;
drop view free_view;
set feed on

Script to report free space and fragmentation in tablespaces

Abstract
This script will report free space and fragmentation in database tablespaces.

Execution Environment:
Any client environment which can connect to database and execute SQL. For example, SQL*plus, iSQL*plus.

Required Privileges:
Need SELECT privilege on view DBA_FREE_SPACE and DBA_DATA_FILES.

Usage:
If you are outside sqlplus then issue,
$ sqlplus username/password @aTBS_FREE_SPACE.SQL
or if you are inside sqlplus then issue,
SQL> @TBS_FREE_SPACE.SQL

Instructions to use:
Copy the script into a file named TBS_FREE_SPACE.SQL and run that script.

Description of the Script
This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace.

Sample Input:
No input is required as it will report on all tablespaces in the database.

Sample Output:

                     Database Tablespaces Freespace Summary

                   Free     Largest       Total      Available   Pct
   Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
---------------- -------- ------------ ------------ ------------ ----
USERS                   5          256        5,120          512   90
SYSTEM                  1        8,128      501,760        8,128   98
UNDOTBS1               13       49,088       66,560       54,400   18
EXAMPLE                 3       20,352      102,400       23,168   77
SYSAUX                  1        5,696      286,720        5,696   98
                 --------              ------------ ------------
sum                    23                   962,560       91,904

Script

SET ECHO off 
REM NAME:   TBS_FREE_SPACE.SQL 
REM USAGE:"START TBS_FREE_SPACE.SQL" if you run inside sql*plus.
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM  SELECT ON DBA_FREE_SPACE and DBA_DATA_FILES view
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Displays tablespace free space and fragmentation for each 
REM    tablespace,  Prints the total size, the amount of space available, 
REM    and a summary of freespace fragmentation in that tablespace. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM     
REM
REM                     Database Tablespaces Freespace Summary
REM
REM                     Free     Largest       Total      Available   Pct
REM    Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
REM ---------------- -------- ------------ ------------ ------------ ----
REM USERS                   5          256        5,120          512   90
REM SYSTEM                  1        8,128      501,760        8,128   98
REM UNDOTBS1               13       49,088       66,560       54,400   18
REM EXAMPLE                 3       20,352      102,400       23,168   77
REM SYSAUX                  1        5,696      286,720        5,696   98
REM                  --------              ------------ ------------
REM sum                    23                   962,560       91,904
REM
REM ------------------------------------------------------------------------ 
REM Main text of script: 
 
ttitle - 
   center  'Database Tablespaces Freespace Summary'  skip 2 
 
comp sum of nfrags totsiz avasiz on report 
break on report 
 
col tsname  format         a16 justify c heading 'Tablespace' 
col nfrags  format     999,990 justify c heading 'Free|Frags' 
col mxfrag  format 999,999,990 justify c heading 'Largest|Frag (KB)' 
col totsiz  format 999,999,990 justify c heading 'Total|(KB)' 
col avasiz  format 999,999,990 justify c heading 'Available|(KB)' 
col pctusd  format         990 justify c heading 'Pct|Used' 
 
select 
  total.tablespace_name                       tsname, 
  count(free.bytes)                           nfrags, 
  nvl(max(free.bytes)/1024,0)                 mxfrag, 
  total.bytes/1024                            totsiz, 
  nvl(sum(free.bytes)/1024,0)                 avasiz, 
  (1-nvl(sum(free.bytes),0)/total.bytes)*100  pctusd 
from 
  dba_data_files  total, 
  dba_free_space  free 
where 
  total.tablespace_name = free.tablespace_name(+) 
  and total.file_id=free.file_id(+)
group by 
  total.tablespace_name, 
  total.bytes 
/ 

Following is the output whenever I run this script in my environment.

SQL>  start "E:\Documents\My Blog\Scripts\tbs_free_space.sql"

                     Database Tablespaces Freespace Summary

                   Free     Largest       Total      Available   Pct
   Tablespace     Frags    Frag (KB)       (KB)         (KB)     Used
---------------- -------- ------------ ------------ ------------ ----
USERS                   5          256        5,120          512   90
SYSTEM                  1        8,128      501,760        8,128   98
UNDOTBS1               13       49,088       66,560       54,400   18
EXAMPLE                 3       20,352      102,400       23,168   77
SYSAUX                  1        5,696      286,720        5,696   98
                 --------              ------------ ------------
sum                    23                   962,560       91,904

A JavaScript which will change the body font type in a web page

Following is a JavaScript code which will change the font type of a web page. It is done for both onClick as well as onMouseOver. In case of onMouseOver whenever you put your mouse on the intended font you will notice your page font type is changed. In case of onClick in order to affect the font type of your webpage you have to click on the text.

<body id="fontT">
<p>Onmouseover Effect of changing font type</p>
<a onMouseover=document.getElementById('fontT').style.fontFamily='courier'>  COURIER Font!</a>
<a onMouseover=document.getElementById('fontT').style.fontFamily='verdana'>  VERDANA Font!</a>
<a onMouseover=document.getElementById('fontT').style.fontFamily='arial'>  Arial Font!</a>

<p>Onclick Effect of changing font type</p>
<a onClick=document.getElementById('fontT').style.fontFamily='courier'>  COURIER Font!</a>
<a onClick=document.getElementById('fontT').style.fontFamily='verdana'>  VERDANA Font!</a>
<a onClick=document.getElementById('fontT').style.fontFamily='arial'>  Arial Font!</a>
</body>

A JavaScript which will change the background Image on a webpage

Following script will change the background image on a webpage. It is for both onClick as well as onMouseOver event. In case of onMouseOver whenever you put your mouse on the Change link you will notice your background image is changed. In case of onClick event in order to affect the background image you have to click on the text.

<script language="JavaScript">

var backImage = new Array(); 
backImage[0] = "1.jpg";
backImage[1] = "2.jpg";
backImage[2] = "3.jpg";
backImage[3] = "";

function changeBGImage(whichImage){
 if (document.body){
 document.body.background = backImage[whichImage];
 }
}

</script>
<p>Onmouseover Changing Background Image Effect </p>
<a href="#" onMouseOver="javascript:changeBGImage(0)">Change</a>
<a href="#" onMouseOver="javascript:changeBGImage(1)">Change</a>
<a href="#" onMouseOver="javascript:changeBGImage(2)">Change</a>
<a href="#" onMouseOver="javascript:changeBGImage(3)">Change</a>
<p>Onclick Changing Background Image Effect </p>
<a href="javascript:changeBGImage(0)">Change</a>
<a href="javascript:changeBGImage(1)">Change</a>
<a href="javascript:changeBGImage(2)">Change</a>
<a href="javascript:changeBGImage(3)">Change</a>

JavaScript which will change the background color

Following is a JavaScript code which will change the background color. It is done for both onClick as well as onMouseOver. In case of onMouseOver whenever you put your mouse in the intended color you will notice your background color is changed. In case of onClick in order to affect the background color you have to click on the text.

<script language="JavaScript">
<!--
function changeBGCOLOR(color){
document.bgColor = color;
}
//-->
</script>
Select Background Color:
<a href="#" onClick="javascript:changeBGCOLOR('#990000')"> <b><font color=#990000>Red</font></b></a>
<a href="#" onClick="javascript:changeBGCOLOR('#000099')"> <b><font color=#000099>Blue</font></b></a>
<a href="#" onClick="javascript:changeBGCOLOR('#009900')"> <b><font color=#009900>Green</font></b></a>
<a href="#" onClick="javascript:changeBGCOLOR('#FFFFFF')"> <b><font color=#000000>White</font></b></a>
<br />
Onmouseover Background Color:
<a href="#" onMouseOver="javascript:changeBGCOLOR('#990000')"> <b><font color=#990000>Red</font></b></a>
<a href="#" onMouseOver="javascript:changeBGCOLOR('#000099')"> <b><font color=#000099>Blue</font></b></a>
<a href="#" onMouseOver="javascript:changeBGCOLOR('#009900')"> <b><font color=#009900>Green</font></b></a>
<a href="#" onMouseOver="javascript:changeBGCOLOR('#FFFFFF')"> <b><font color=#000000>White</font></b></a>

Script to calculate average row size for all tables in a schema

Abstract:
This script will calculate the average row size for all tables in a schema, using the VSIZE function of SQL.

Execution Environment:
Any client environment which can connect to database and execute SQL. For example, SQL*plus, iSQL*plus.

Required Privileges:
No special privilege required as scripts needs to be executed with the user who owns the table to be analyzed.

Usage:
If you use sqlplus issue,
$ sqlplus username/password @avg_row_size.sql
or,
SQL> @avg_row_size.sql

Instructions to use:
Copy the script into a file named avg_row_size.sql Execute the script from SQL*Plus connected as the user whose schema tables need to be calculated. This script generates a script named getAvgRowSize.sql. In order to see the calculated average row size output run the getAvgRowSize.sql

Restriction to use:
By this script tables with LONG and LOB columns will not report row size properly. Also tables with object types will throw the following error and will also not report row size properly and will generate:
ORA-00932: inconsistent datatypes

Sample Input:
No input is required as it will calculate average row size of all tables who is running the script.

Sample Output:
This script will generate a script named getAvgRowSize.sql. Executing getAvgRowSize.sql will generate output like below.

MM_MEMBER                              37
MM_MOVIE                               23
MM_MOVIE_TYPE                           8
MM_PAY_TYPE                             9
MM_RENTAL                              15
T                                       6

How this script works:
Execute this script will generate following type of query.
SELECT round(avg(nvl(vsize(COL1),0)) +
round(avg(nvl(vsize(COL2),0)) + ... +
round(avg(nvl(vsize(COLn),0))
Where COLn= number of cols. on the table

For each table this type of query will generate which eventually calculate average row size for all tables in a schema.

Main Script:

SET ECHO off 
REM NAME:   avg_row_size.sql
REM USAGE:"@avg_row_size.sql" 
REM -------------------------------------------------------------------------- 
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com    
REM -------------------------------------------------------------------------- 
REM REQUIREMENTS: 
REM    Should be run under the schema ID of the tables being reported. 
REM    Tables with LONG columns will not report row size properly!! 
REM -------------------------------------------------------------------------- 
REM PURPOSE: 
REM    Calculate the average row size for all tables in a schema. 
REM    It generates a script (getAvgRowSize.sql) of USER_TABLES and then runs 
REM    it.  The following type of SELECT is generated for table in USER_TABLES: 
REM         SELECT round(avg(nvl(vsize(COL1),0)) +   
REM                round(avg(nvl(vsize(COL2),0)) + ... +
REM                round(avg(nvl(vsize(COLn),0)) 
REM              
REM Where COLn=number of cols. on the table 
REM 
REM    Input:           NONE 
REM    Output:          getAvgRowSize.sql - Script that is generated and run 
REM     getAvgRowSize.lst - Report of each table and its 
REM                     average row length 
REM             
REM -------------------------------------------------------------------------- 
REM Main text of script follows: 
 
drop table column_counts; 
create table column_counts 
        ( 
        table_name, 
        column_count 
        ) 
        as 
        ( 
        select table_name, max(column_id) 
        from user_tab_columns 
        where data_type not like 'LONG%' AND table_name in 
        (select table_name from user_tables) 
        group by table_name 
        ) 
        ; 
set pages 0 
set tab on 
set trim on 
set verify off 
set feedback off 
set termout off 
set head off 
set lines 100 
set recsep off 
set embedded on 
spool getAvgRowSize.sql 
prompt column TB format A30 
prompt set head off recsep off 
prompt set lines 80 feedback off pages 0 
prompt spool getAvgRowSize 
REM 
column select_line format A8  
column end_line format A1  
column from_stmt format A34 word_wrap 
column col_nm format A100  
column col_val format A32  
column tnm1 noprint  
column tnmprint format A37   
column column_id noprint 
break on tnm1 skip 2 
set null '' 
clear breaks 
select UTC.table_name tnm1, 
        decode(column_id,1,'select ' || chr(39) || UTC.table_name || chr(39) || 
                ' TB, ', '        ') || 
        'round(avg(nvl(vsize('||column_name||'),0)),0)' || 
        decode(column_id,column_count, ' row_size from ' || UTC.table_name 
             || ';'|| chr(10)||chr(10), 
                ' +') col_nm 
from user_tab_columns UTC, column_counts CC 
where UTC.data_type not like 'LONG%' AND UTC.table_name = CC.table_name 
order by UTC.table_name, UTC.column_id; 
prompt spool off 
prompt exit 
spool off 
drop table column_counts; 
exit 

Execution and Output of the script

C:\Documents and Settings\User>sqlplus arju/a

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 20 14:18:56 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @"E:\Documents\My Blog\Scripts\average_row_size.sql"
drop table column_counts
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\User>sqlplus arju/a

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 20 14:19:12 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @"C:\Documents and Settings\User\getAvgRowSize.sql"
MM_MEMBER                              37
MM_MOVIE                               23
MM_MOVIE_TYPE                           8
MM_PAY_TYPE                             9
MM_RENTAL                              15
T                                       6
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Contents of getAvgRowSize.sql script
Following is the output of the script getAvgRowSize.sql

column TB format A30
set head off recsep off
set lines 80 feedback off pages 0
spool getAvgRowSize
select 'MM_MEMBER' TB, round(avg(nvl(vsize(MEMBER_ID),0)),0) +                                      
        round(avg(nvl(vsize(LAST),0)),0) +                                                          
        round(avg(nvl(vsize(FIRST),0)),0) +                                                         
        round(avg(nvl(vsize(LICENSE_NO),0)),0) +                                                    
        round(avg(nvl(vsize(LICENSE_ST),0)),0) +                                                    
        round(avg(nvl(vsize(CREDIT_CARD),0)),0) +                                                   
        round(avg(nvl(vsize(SUSPENSION),0)),0) +                                                    
        round(avg(nvl(vsize(MAILING_LIST),0)),0) row_size from MM_MEMBER;                           
                                                                                                    
select 'MM_MOVIE' TB, round(avg(nvl(vsize(MOVIE_ID),0)),0) +                                        
        round(avg(nvl(vsize(MOVIE_TITLE),0)),0) +                                                   
        round(avg(nvl(vsize(MOVIE_CAT_ID),0)),0) +                                                  
        round(avg(nvl(vsize(MOVIE_VALUE),0)),0) +                                                   
        round(avg(nvl(vsize(MOVIE_QTY),0)),0) row_size from MM_MOVIE;                               
                                                                                                    
select 'MM_MOVIE_TYPE' TB, round(avg(nvl(vsize(MOVIE_CAT_ID),0)),0) +                               
        round(avg(nvl(vsize(MOVIE_CATEGORY),0)),0) row_size from MM_MOVIE_TYPE;                     
                                                                                                    
select 'MM_PAY_TYPE' TB, round(avg(nvl(vsize(PAYMENT_METHODS_ID),0)),0) +                           
        round(avg(nvl(vsize(PAYMENT_METHODS),0)),0) row_size from MM_PAY_TYPE;                      
                                                                                                    
select 'MM_RENTAL' TB, round(avg(nvl(vsize(RENTAL_ID),0)),0) +                                      
        round(avg(nvl(vsize(MEMBER_ID),0)),0) +                                                     
        round(avg(nvl(vsize(MOVIE_ID),0)),0) +                                                      
        round(avg(nvl(vsize(CHECKOUT_DATE),0)),0) +                                                 
        round(avg(nvl(vsize(CHECKIN_DATE),0)),0) +                                                  
        round(avg(nvl(vsize(PAYMENT_METHODS_ID),0)),0) row_size from MM_RENTAL;                     
                                                                                                    
select 'T' TB, round(avg(nvl(vsize(A),0)),0) +                                                      
        round(avg(nvl(vsize(B),0)),0) +                                                             
        round(avg(nvl(vsize(C),0)),0) row_size from T;                                              
                                                                                                    
spool off
exit

RMAN-03002, RMAN-06059, ORA-19625 and ORA-27037 during RMAN archivelog backup

Problem Description

Starting backup at 19-OCT-2010 00:31:37
current log archived
released channel: channel1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 10/19/2010 00:32:03
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/admin/ORACLE/arch/arch_94049.log
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory
Additional information: 3

Cause of the Problem
The problem happened because Oracle archivelog files were deleted at OS level.

Solution of the Problem
Run following commands in your RMAN prompt to resolve the issue,

Rman> Crosscheck copy of archivelog all; 
Rman> Crosscheck archivelog all; 
Rman> Resync catalog; 
Rman> delete force obsolete; 
Rman> delete expired archivelog all ; 

After you run the above commands in your RMAN prompt RMAN will unlink all archived log entry from its repository. But due to oracle bug
- if still it can't delete or,
- delete force obsolete and delete expired archivelog all fails with segmentation errors or,
- delete is succeed but still you face RMAN-03002, RMAN-06059, ORA-19625 and ORA-27037 error then do the following:


Rman> Change archivelog '{location of archivelog which is deleted}' UNCATALOG ;

Please note the first archive log name would be present in the error message ORA-19625:

For example in our case our error output was:

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/admin/ORACLE/arch/arch_94049.log
ORA-27037: unable to obtain file status
HP-UX Error: 2: No such file or directory

So run the following command,

Rman> Change archivelog '/u01/app/oracle/admin/ORACLE/arch/arch_94049.log' uncatalog;

Run the archive log backup command check if you still get the error

Keeping specify the archive log file name reported in ORA-19625 till backup of archive log goes fine.

If there is so many archivelog you deleted from specify then alternatively you can do the following as each time specifying the name will take much time.

Rman> Change archivelog all uncatalog ;

Please note the above command will uncatalog the information about the Archive log from catalog database.

Script to delete duplicate rows from a table based on column values

This script will take a table name and list of columns as input which you desire to be unique and deletes all rows with the same value in these columns, leaving only the row with the minimum rowid. Note that this script does not deal with NULL values in the columns.

Restrictions of the Script: This script will not work if the column(s) to be filtered on having a datatype of LONG, LONG RAW, RAW, CLOB, NCLOB, BLOB, BFILE or an object datatype.

REM This script will delete duplicate rows from a table based on supplied columns
REM from a table.
REM You must save it into a file for example delete_dup.sql and run it from sql*plus
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com    
REM -------------------------------------------------------------------------- 
set echo off
set verify off heading off
undefine t
undefine c
prompt
prompt
prompt Enter name of table with duplicate rows
prompt
accept t prompt 'Table: '
prompt
select 'Table '||upper('&&t') from dual;
describe &&t
prompt
prompt Enter name(s) of column(s) which should be unique. If you want to provide more
prompt than one column, you must separate them with commas.
prompt
accept c prompt 'Column(s): '
prompt
delete from &&t
where rowid not in (select min(rowid) from &&t group by &&c)
/

Following is an example in my environment,

SQL> create table t(a number, b number, c number);

Table created.

SQL> insert into t values(1,1,1);

1 row created.

SQL> insert into t values(1,2,1);

1 row created.

SQL> insert into t values(1,2,5);

1 row created.

SQL> commit;

Commit complete.

SQL> @"E:\Documents\My Blog\Scripts\delete_dup.sql"


Enter name of table with duplicate rows

Table: T


Table T

1 row selected.

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER
 B                                                  NUMBER
 C                                                  NUMBER


Enter name(s) of column(s) which should be unique. If you want to provide more
than one column, you must separate them with commas.

Column(s): a,b


1 row deleted.

SQL> select * from t;

         1          1          1
         1          2          1

2 rows selected.

Script to list of users and roles having privileges on a table

-- 
-- This script will display the list of users and roles 
-- which have privileges on a specific table. It will also
-- display if privilege is granted through roles.
-- 
-- Parameters:  Table Name 
-- Script will ask you for the table name.
REM AUTHOR:  
REM    Mohammad Abdul Momin Arju
REM    http://arjudba.blogspot.com    
REM -------------------------------------------------------------------------- 
  
column ge   format a60 heading "Grantee" 
column priv format a10 heading "Privilege" 
  
break on ge 
set feedback on 
set pages 200  
select grantee||' Through role '||granted_role ge, 'SELECT' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='SELECT' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'UPDATE' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='UPDATE' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'INSERT' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='INSERT' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'DELETE' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='DELETE' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'INDEX' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='INDEX' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'ALTER' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='ALTER' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'REFERENCES' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='REFERENCES' ) 
connect by prior grantee=granted_role 
union 
select grantee||' Through role '||granted_role ge, 'EXECUTE' priv 
from dba_role_privs 
start with granted_role in 
(select grantee from dba_tab_privs where privilege='EXECUTE' ) 
connect by prior grantee=granted_role 
union 
select grantee|| ' Direct' ge , privilege priv
from sys.dba_tab_privs
where table_name  = upper('&TABLE_NAME') 
order by 1,2
/

Following is the sample output from my environment. In order to test the script I have created a role named mm and have assigned select privilege on this role to oraclist user so that I can verify my script.

SQL> conn arju/a
Connected.

SQL> create role mm;

Role created.

SQL> grant select on mm_movie to mm;

Grant succeeded.

SQL> grant resource to oraclist identified by a;

Grant succeeded.

SQL> grant mm to oraclist;

Grant succeeded.

SQL> grant create session to oraclist;

Grant succeeded.
SQL> -- This script will display the list of users and roles
SQL> -- which have privileges on a specific table. It will also
SQL> -- display if privilege is granted through roles.
SQL> --
SQL> -- Parameters:  Table Name
SQL> -- Script will ask you for the table name.
SQL>
SQL> column ge   format a60 heading "Grantee"
SQL> column priv format a10 heading "Privilege"
SQL>
SQL> break on ge
SQL> set feedback on
SQL> set pages 200
SQL> select grantee||' Through role '||granted_role ge, 'SELECT' priv
  2  from dba_role_privs
  3  start with granted_role in
  4  (select grantee from dba_tab_privs where privilege='SELECT' )
  5  connect by prior grantee=granted_role
  6  union
  7  select grantee||' Through role '||granted_role ge, 'UPDATE' priv
  8  from dba_role_privs
  9  start with granted_role in
 10  (select grantee from dba_tab_privs where privilege='UPDATE' )
 11  connect by prior grantee=granted_role
 12  union
 13  select grantee||' Through role '||granted_role ge, 'INSERT' priv
 14  from dba_role_privs
 15  start with granted_role in
 16  (select grantee from dba_tab_privs where privilege='INSERT' )
 17  connect by prior grantee=granted_role
 18  union
 19  select grantee||' Through role '||granted_role ge, 'DELETE' priv
 20  from dba_role_privs
 21  start with granted_role in
 22  (select grantee from dba_tab_privs where privilege='DELETE' )
 23  connect by prior grantee=granted_role
 24  union
 25  select grantee||' Through role '||granted_role ge, 'INDEX' priv
 26  from dba_role_privs
 27  start with granted_role in
 28  (select grantee from dba_tab_privs where privilege='INDEX' )
 29  connect by prior grantee=granted_role
 30  union
 31  select grantee||' Through role '||granted_role ge, 'ALTER' priv
 32  from dba_role_privs
 33  start with granted_role in
 34  (select grantee from dba_tab_privs where privilege='ALTER' )
 35  connect by prior grantee=granted_role
 36  union
 37  select grantee||' Through role '||granted_role ge, 'REFERENCES' priv
 38  from dba_role_privs
 39  start with granted_role in
 40  (select grantee from dba_tab_privs where privilege='REFERENCES' )
 41  connect by prior grantee=granted_role
 42  union
 43  select grantee||' Through role '||granted_role ge, 'EXECUTE' priv
 44  from dba_role_privs
 45  start with granted_role in
 46  (select grantee from dba_tab_privs where privilege='EXECUTE' )
 47  connect by prior grantee=granted_role
 48  union
 49  select grantee|| ' Direct' ge , privilege priv
 50  from sys.dba_tab_privs
 51  where table_name  = upper('&TABLE_NAME')
 52  order by 1,2
 53  /
Enter value for table_name: mm_movie
old  51: where table_name  = upper('&TABLE_NAME')
new  51: where table_name  = upper('mm_movie')

Grantee                                                      Privilege
------------------------------------------------------------ ----------
A Through role DBA                                           ALTER
                                                             DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
ARJU Through role DBA                                        ALTER
                                                             DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
ARJU Through role MM                                         SELECT
CTXSYS Through role CTXAPP                                   EXECUTE
                                                             INSERT
                                                             UPDATE
DBA Through role DELETE_CATALOG_ROLE                         DELETE
DBA Through role EXECUTE_CATALOG_ROLE                        EXECUTE
                                                             SELECT
DBA Through role EXP_FULL_DATABASE                           DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
DBA Through role GATHER_SYSTEM_STATISTICS                    DELETE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
DBA Through role IMP_FULL_DATABASE                           DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
DBA Through role OLAP_DBA                                    EXECUTE
                                                             SELECT
DBA Through role SELECT_CATALOG_ROLE                         EXECUTE
                                                             SELECT
DBA Through role WM_ADMIN_ROLE                               SELECT
DBA Through role XDBADMIN                                    ALTER
                                                             DELETE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
DBSNMP Through role OEM_MONITOR                              EXECUTE
                                                             SELECT
EXECUTE_CATALOG_ROLE Through role HS_ADMIN_ROLE              EXECUTE
                                                             SELECT
EXP_FULL_DATABASE Through role EXECUTE_CATALOG_ROLE          EXECUTE
                                                             SELECT
EXP_FULL_DATABASE Through role SELECT_CATALOG_ROLE           EXECUTE
                                                             SELECT
IMP_FULL_DATABASE Through role EXECUTE_CATALOG_ROLE          EXECUTE
                                                             SELECT
IMP_FULL_DATABASE Through role SELECT_CATALOG_ROLE           EXECUTE
                                                             SELECT
IX Through role AQ_ADMINISTRATOR_ROLE                        EXECUTE
                                                             SELECT
IX Through role AQ_USER_ROLE                                 EXECUTE
IX Through role SELECT_CATALOG_ROLE                          EXECUTE
                                                             SELECT
MGMT_VIEW Through role MGMT_USER                             SELECT
MM Direct                                                    SELECT
OE Through role XDBADMIN                                     ALTER
                                                             DELETE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
OLAPSYS Through role OLAP_DBA                                EXECUTE
                                                             SELECT
OLAP_DBA Through role SELECT_CATALOG_ROLE                    EXECUTE
                                                             SELECT
OLAP_USER Through role OEM_MONITOR                           EXECUTE
                                                             SELECT
OLAP_USER Through role SELECT_CATALOG_ROLE                   EXECUTE
                                                             SELECT
ORACLIST Through role MM                                     SELECT
SELECT_CATALOG_ROLE Through role HS_ADMIN_ROLE               EXECUTE
                                                             SELECT
SH Through role SELECT_CATALOG_ROLE                          EXECUTE
                                                             SELECT
SYS Through role AQ_ADMINISTRATOR_ROLE                       EXECUTE
                                                             SELECT
SYS Through role AQ_USER_ROLE                                EXECUTE
SYS Through role CTXAPP                                      EXECUTE
                                                             INSERT
                                                             UPDATE
SYS Through role DBA                                         ALTER
                                                             DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYS Through role DELETE_CATALOG_ROLE                         DELETE
SYS Through role EXECUTE_CATALOG_ROLE                        EXECUTE
                                                             SELECT
SYS Through role EXP_FULL_DATABASE                           DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYS Through role GATHER_SYSTEM_STATISTICS                    DELETE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYS Through role HS_ADMIN_ROLE                               EXECUTE
                                                             SELECT
SYS Through role IMP_FULL_DATABASE                           DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYS Through role LOGSTDBY_ADMINISTRATOR                      EXECUTE
SYS Through role MGMT_USER                                   SELECT
SYS Through role OEM_MONITOR                                 EXECUTE
                                                             SELECT
SYS Through role OLAP_DBA                                    EXECUTE
                                                             SELECT
SYS Through role OLAP_USER                                   DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYS Through role SELECT_CATALOG_ROLE                         EXECUTE
                                                             SELECT
SYS Through role XDBADMIN                                    ALTER
                                                             DELETE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYSMAN Through role DBA                                      ALTER
                                                             DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYSMAN Through role MGMT_USER                                SELECT
SYSTEM Through role AQ_ADMINISTRATOR_ROLE                    EXECUTE
                                                             SELECT
SYSTEM Through role DBA                                      ALTER
                                                             DELETE
                                                             EXECUTE
                                                             INSERT
                                                             SELECT
                                                             UPDATE
SYSTEM Through role MGMT_USER                                SELECT
WMSYS Through role WM_ADMIN_ROLE                             SELECT
XDB Through role CTXAPP                                      EXECUTE
                                                             INSERT
                                                             UPDATE

149 rows selected.

How to add a poll inside a blogger post

An example of adding a poll inside a blogger post is shown in best web hosting poll. It is not too much difficult to add a poll inside a blogger post. If you already know how to add a poll in your blogger sidebar then move to step 7.

Step 01: Login to your blogger account.

Step 02: Click on Design.

Step 03: Click on Add a Gadget.

Step 04: A new pop up "Add a Gadget" window will appear. Scroll down and find out "Poll
Survey your visitors by adding a poll to your blog." Click on Poll.

Step 05: "Create a poll" window will appear. Fill out the Question and Answers form. You can allow visitors to select multiple answers by checking the box "Allow visitors to select multiple answers". Also select poll closing date. And then save.

Step 06: Now click View Blog and you'll see your new poll in the blog. But that is not inside a post rather it is throughout the blog. Now you need to move this poll into a certain post.

Step 07: Right Click somewhere in your blog (from viewer perspective not in admin menu) and then click "View Source". You will see so many texts there. But don't worry. Use your browser CTRL+F and type the poll title. In the post http://arjudba.blogspot.com/2010/10/rate-your-best-and-cheapest-web-hosting.html my poll title was "Which web hosting you recommend?" Once you find it highlight the iframe tag and everything between it up to iframe. Copy the piece of (Ctrl+C) code and paste (CTRL+V) it into a notepad file.

Step 08: Now go into your writing post where you want to display the poll. Click on Edit HTML of your intended post and then paste this iframe code into your html code along with title. Now view your post.

Step 09: If your poll is displayed fine go back to your Design> Page Elements. In the "Add and Arrange Page Elements" click Edit on the gadget which you have added for your poll and then just remove that.

Web Hosting Reviews - Rate your best and cheapest web hosting sites

In this post I have selected the best web hosting companies from some of the world top webhosting companies. I myself has used many of them. With some of them I am very satisfied and with some of them I am angry. Most of the hosting companies I listed here are reliable and they support Apache, Python, PHP 4 or 5, MySQL 4, Microsoft FrontPage extension, unlimited domains, unlimited space, unlimited bandwidth, unlimited emails (including webmail) and many more. Many ones support Free Domain Name with Privacy & SSH. There is also an options for moneyback guarantee. So before you select a web hosting check whether they offer moneyback guarantee or not, whether they offer free ssh (I love ssh so choose web hosting who offers it free) and of course how good they are in support. If you look for support then I love live support 24*7. Most of the hosting run on Linux and Windows.

Web Hosting services is a very competitive industry. So it is very unlikely that you can't find cheapest and best web hosting. Some web hosts are giving very cheap web hosting rate (as low as $1.95!), including reduced first year fee, free months, discount coupon, free fist year etc. Note that, cheapest are not worst in case of web hosting. You can easily find a very cheap hosting from $4~$10 per month.

Listing below are the best web hosting services support PHP, MySQL, Python, FrontPage web hosting and almost all of it are linux web hosting. I have listed them based on popularity, performance, feature, support quality and price. Higher rating hosting are usually has better feature over price ratio. I have also kept an option to rate your hosting so that everyone will be aware about the rating of the best hosting.

RA NK	Visit Site	Price	Disk Space/ Band width /Email	List of Features	Moneyback Guarantee	Marketing Slogan	Rating/ Review
1		Baby Plan: $9.95/m $9.95/m(1 yr) $8.95/m(2 yrs) $7.95/m(3 yrs)	Unlimited	UNLIMITED Disk Space UNLIMITED Bandwidth FREE SiteBuilder EASY Control Panel 1-CLICK Script Installs 4,500 Free Website Templates 99.9% Uptime Guarantee 45 Day Money Back Guarantee 24/7/365 Technical Support $100 Google AdWords Credit	No Contract with a 45 Day Money Back Guarantee	Web Hosting made EASY and AFFORDABLE!	Unlimited Disk Space and Bandwidth FREE Site Building Tools and Templates 24/7/365 Award Winning Technical Support
2		$6.95/ month	Unlimited	UNLIMITED Domain Hosting FREE Domain Forever FREE Site Builder w/ templates Secure Shell, SSL, FTP, Stats CGI, Ruby (RoR), Perl, PHP, MySQL $50 Free Google Credit 99.9% Network Uptime Guarantee	Moneyback Guarantee	Affordable, Reliable Web Hosting Solutions	24/7 US-based Support Simple Scripts 1-Click installs Trusted by Millions of Sites SSH Secure Shell Access
3		$7.95/m(1 m) $6.95/m(6 m) $5.95/m(1 yr) $4.95/m(2 yrs) $3.95/m(3 yrs) $3.45/m(4 yrs)	Unlimited	Unlimited Domain Hosting Unlimited E-Mail Accounts Unlimited MySQL Databases FREE Site Builder FREE Domain for Life FREE Instant Setup Anytime Money Back Guarantee	Anytime Money Back Guarantee	The last hosting plan you'll ever need.	e-Commerce Shopping Carts $50 Google Ad Credits $25 Yahoo Ad Credits $50 MySpace Ad Credits
4		$5.95/month	Unlimited	Host UNLIMITED Domains Unlimited Pop/Imap Email Accounts SSH Access (Secure Shell) SSL, FTP, Stats CGI, Ruby (RoR), Perl, PHP, MYSQL Front Page Extensions Free Domain Forever Free Site Builder Best Support in the Industry	Moneyback Guarantee	REAL 24/7 Non-outsourced support	You can cancel anytime with NO cancellation fee! Simple Scripts FREE 1 click installer for ALL the best blogs, shopping cart, forums CMS, photo galleries, and more!
5		Economy plan: 3m:$4.99/m, 12m: $3.99/m, 24mos: $3.99/m, 36m: $2.99/m Deluxe Plan: 1m: $7.99/m, 2m: $6.99/m, 24m: $5.99/m, 36m: $4.99/m Ultimate plan: 1m: $14.99/m, 12m: $7.99/m, 24m: $7.99/m, 36m: $6.99/m	Depends based on Plan	Economoy plan:10 GB Space Unlimited Bandwidth 100 Email Accounts 10 MySQL Databases Deluxe Plan: 150 GBSpace Unlimited Bandwidth 500 Email Accounts 25 MySQL Databases Ultimate Plan: Unlimited Space+ & Bandwidth! 1000 Email Accounts Unlimited MySQL Databases Includes Free†† SSL Certificate!	Moneyback Guarantee	Fast, secure hosting from our Asia Pacific data center.	# 24x7 FTP access # Fast Asia Pacific-based servers # Daily Backups # Best-of-Breed Routers, Firewalls and Servers # Web site statistics # FREE Email Addresses # Google® Webmaster Tools # Ad Credits from MySpace®, Google® and Facebook®
6		$3.95/m(1 yr) $2.95/m(2 yrs) $2.49/m(3 yrs) $1.99/m(4 yrs) $1.99/m(5 yrs)	Unlimited	Free Domain Name Free Sitebuilder CGI-BIN, SSI (Server Side Includes) Frontpage Extensions, Cpanel Webmail (Browser Based Email) Log Files + Site Stats Customizable Error Pages Web File Manager, Spam Protection Fantastico Script Support	Moneyback Guarantee	Reliable Web Hosting at a Price You Can Afford	FREE Marketing Credits ($200 Value!) Google, Yahoo!, Facebook, Myads
7		Expert Plan: $3.95/m Unlimited Pro: $7.95/mo Business Plus: $7.95/m	for email limit imposed based on Expert Plan Unlimited Pro Business Plus	Domains Hosted Unlimited Dedicated IP Addresses 2 Domain Registration 1 Free 24/7 Support Free Online Web Editor Free	Moneyback Guarantee	We're Behind You With The BEST Support In The Industry & Guaranteed 99.9% Uptime	Rating/ Review
8		$7.95/m(1 m) $6.95/m(6 m) $5.95/m(1 y) $4.95/m(2 yrs) $3.95/m(3 yrs)	Depends on package	* FREE Domain For Life * FREE Site Builder * FREE Instant Setup * Unlimited Hosting Space * Unlimited GB's Of Transfer * Anytime Money Back	Anytime	Environmentally Friendly Hosting	# FREE $50 Google Ad Credits # FREE $25 Yahoo Ad Credits # FREE $50 Facebook & MySpace Ad Credits # FREE 1 Click Joomla Install # FREE 1 Click WordPress Install
9		$3.00/m for starter, $4.00/m for Basic, $5.00/m for Max.	Unlimited but limitation of number of mysql databases, adon domains.	Disk Space (GB) - unlimited Monthly Transfer Included (GB) - unlimited Data Backup	90 Day	Go Green with Inmotion Hosting	Rating/ Review
10		$4.95/month for starter, $9.95 for business	Depends on plan	FREE Blog, Shopping cart & more FREE Drag n Drop Site Builder Unlimited Email & FTP Accounts FFMpeg, SSH, Stats, PHP5, MySQL UNLIMITED Addon Domains	30 days	Professional Web hosting	Host Unlimited Addon Domains On ALL Of Our Plans!

Here please submit your opinion so that anyone who read this post can choose good hosting.

Which web hosting you recommend?

ORA-01994: GRANT failed: password file missing or disabled

Problem Description
Whenever it is tried to grant sysdba privilege to a user it failed with error ORA-01994.
SQL> GRANT sysdba to oraclist_arju;
GRANT sysdba to oraclist_arju
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

Cause of the Problem
The problem might happened due to any of the following reasons.
Cause 01: The parameter REMOTE_LOGIN_PASSWORDFILE is set to NONE.

Cause 02: There is no password file exists or if exist then possibly it is not in the location where oracle is looking for.

Cause 03: If there is no valid passwordfile even with parameter remote_login_passwordfile = exclusive and you login and startup the instance using OS authentication.

Cause 04: If there is password file exist in correct location then it has to be owned by the same Unix user that owns the instance, typically 'oracle'.

Solution of the Problem
Step 01:
- Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE.
You can check the setting by,

SQL> show parameter remote_login

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
remote_login_passwordfile            string      EXCLUSIVE

If it is set to NONE then set it to EXCLUSIVE by,
SQL> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
As it is set to spfile so need to restart in order affect the setting.

Step 02:
Make sure your password file name is correct. The password file's name should be $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and $ORACLE_HOME\database\PWD%ORACLE_SID%.ora on Windows.

If there does not have any password file then create one. For example on Unix,
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=newsyspassword entries=16 force=y

Step 03:
Check for the users who have granted SYSDBA privilege by checking dynamic view V$PWFILE_USERS.

Step 04:
Check the ownership and permission of the password file by,

$ ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID

If it is not ok then change it by,

$ chown oracle:dba $ORACLE_HOME/dbs/orapw$ORACLE_SID
$ chmod 4640 $ORACLE_HOME/dbs/orapw$ORACLE_SID

Creation of second standby database using RMAN DUPLICATE fails with RMAN-20242

Problem Description
You can run the following command to create a physical standby database from the primary database.

run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'BDDIPDC','BDDIPDRS'
set db_file_name_convert='/BDDIPDC/','/BDDIPDRS/'
set log_file_name_convert='/BDDIPDC/','/BDDIPDRS/'
set 'db_unique_name'='BDDIPDRS'
set control_files='+DATA/BDDIPDRS/control01.ctl','+DATA/BDDIPDRS/control02.ctl'
set db_recovery_file_dest='+RECOVERY/BDDIPDRS'
set DB_RECOVERY_FILE_DEST_SIZE='500G'
nofilenamecheck;
}

So you have one primary and one physical standby database. Now you want to add another standby database in your Data Guard environment. And you issued following command but it failed with
RMAN-20242

rman target sys/sys@bddipdc auxiliary sys/sys@bddipdrs
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE 
SET db_unique_name = 'BDDIPDRS' 
SET FAL_CLIENT = 'BDDIPDRS' 
SET FAL_SERVER = 'BDDIPDC' 
set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=x11106_stby2' 
set log_archive_dest_2 = 'db_unique_name=BDDIPDC SERVICE=BDDIPDC valid_for=(online_logfile, primary_role) REOPEN=60 OPTIONAL LGWR SYNC AFFIRM' 
CONTROL_FILES='+DATA/BDDIPDRS/control01.dbf' 
set DB_FILE_NAME_CONVERT='/BDDIPDC/','/BDDIPDRS/' 
set LOG_FILE_NAME_CONVERT='/BDDIPDC/','/BDDIPDRS/' 
NOFILENAMECHECK;

Following is the error output,

RMAN-8162: executing Memory Script

RMAN-3090: Starting backup at 01-NOV-10
RMAN-12016: using channel ORA_DISK_1
RMAN-571: ===========================================================
RMAN-569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-571: ===========================================================
RMAN-3002: failure of Duplicate Db command at 11/01/2010 04:26:39
RMAN-3015: error occurred in stored script Memory Script
RMAN-20242: specification does not match any archived log in the recovery 
catalog

Cause of the Problem
The problem happened due to oracle bug. If the source database has standby destination, then while copying archived logs from the source database the bug will fire. Oracle named this bug as Bug 6603587.

Solution of the Problem
Solution 01: This bug is fixed in Oracle 11.2 and in patchset 11.1.0.7. So upgrade your current oracle software is a solution.

Solution 02: For DUPLICATE DATABASE FOR STANDBY FROM ACTIVE, simply avoid using the DORECOVER clause. Logs will ship naturally as part of the standby log shipping.

Solution 03: For DUPLICATE TARGET DATABASE ... FROM ACTIVE, defer all standby destinations prior to executing the command. For example, issue
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=both sid='*';
where LOG_ARCHIVE_DEST_STATE_2 is standby destination.

how to add related posts to blogger

In stead of discussion much I will directly go to step by step procedure in order to add Related Posts widget to a blogger.

Note that, in order to make related posts work you must have related label posts.

Step 01: Log in to blogger home with your email and password. If you want to see screenshot about how it looks then have a look at http://arjudba.blogspot.com/2010/10/in-blogger-blog-post-reactions-email.html

Step 02: Click on "Design" and then navigate to "Edit HTML". Again if you want to look for screenshot of this step then visit Solution of blog post reactions, email post links, share buttons

Step 03: Backup your template before editing your template. Before editing your template, you may want to save a copy of it. So click on "Download Full Template" and then save the template. This is very good way to backup template before doing any changes in your template in order to avoid unforeseen problems.

Step 04: Click on "Expand Widget Templates" checkbox.

Step 05: Use keyboard shortcut key CTRL+F in your browser in order to find out the word </head>.
Copy the following codes and add just above of text </head> in your template.

<style>

#related-posts {
float : left;
width : 540px;
margin-top:5px;
margin-left : 5px;
margin-bottom:5px;
font : 11px Verdana;
margin-bottom:10px;
}
#related-posts .widget {
list-style-type : none;
margin : 5px 0 5px 0;
padding : 0;
}
#related-posts .widget h2, #related-posts h2 {
color : #333333;
font-size : 13px;
font-weight : bold;
margin : 5px 7px 0;
padding : 0 0 5px;
text-decoration : underline;
}
#related-posts a {
color : #054474;
font-size : 11px;
text-decoration : none;
}
#related-posts a:hover {
color : #054474;
text-decoration : none;
}
#related-posts ul {
border : medium none;
margin : 10px;
padding : 0;
}
#related-posts ul li {
display : block;
background : url("http://1.bp.blogspot.com/_8O68IaFjsdA/TLvdTI94ZOI/AAAAAAAAAsU/54GN3ODEzqA/s1600/bullet.jpg") no-repeat 0 0;
margin : 0;
padding-top : 0;
padding-right : 0;
padding-bottom : 1px;
padding-left : 16px;
margin-bottom : 5px;
line-height : 2em;
border-bottom:1px dotted #cccccc;
}

</style>

<script type='text/javascript'>
//<![CDATA[
var relatedTitles = new Array();
var relatedTitlesNum = 0;
var relatedUrls = new Array();
function related_results_labels(json) {
for (var i = 0; i < json.feed.entry.length; i++) {
var entry = json.feed.entry[i];
relatedTitles[relatedTitlesNum] = entry.title.$t;
for (var k = 0; k < entry.link.length; k++) {
if (entry.link[k].rel == 'alternate') {
relatedUrls[relatedTitlesNum] = entry.link[k].href;
relatedTitlesNum++;
break;
}
}
}
}
function removeRelatedDuplicates() {
var tmp = new Array(0);
var tmp2 = new Array(0);
for(var i = 0; i < relatedUrls.length; i++) {
if(!contains(tmp, relatedUrls[i])) {
tmp.length += 1;
tmp[tmp.length - 1] = relatedUrls[i];
tmp2.length += 1;
tmp2[tmp2.length - 1] = relatedTitles[i];
}
}
relatedTitles = tmp2;
relatedUrls = tmp;
}
function contains(a, e) {
for(var j = 0; j < a.length; j++) if (a[j]==e) return true;
return false;
}
function printRelatedLabels() {
var r = Math.floor((relatedTitles.length - 1) * Math.random());
var i = 0;
document.write('<ul>');
while (i < relatedTitles.length && i < 20) {
document.write('<li><a href="' + relatedUrls[r] + '">' + relatedTitles[r] + '</a></li>');
if (r < relatedTitles.length - 1) {
r++;
} else {
r = 0;
}
i++;
}
document.write('</ul>');
}
//]]>
</script>

Step 06: Again use CTRL+F5 keyboard shortcut key to find out the text <data:post.body/>
Copy the following codes and add it just below of <data:post.body/>

<b:if cond='data:blog.pageType == "item"'>
<div id="related-posts">
<h2>Related Posts on <b:loop values='data:post.labels' var='label'><data:label.name/><b:if cond='data:label.isLast != "true"'>,</b:if><b:if cond='data:blog.pageType == "item"'>
<script expr:src='"/feeds/posts/default/-/" + data:label.name + "?alt=json-in-script&callback=related_results_labels&max-results=8"' type='text/javascript'/></b:if></b:loop> </h2>

<script type='text/javascript'> removeRelatedDuplicates(); printRelatedLabels(); </script>

</div></b:if>

Step 07: Click on Save Template and you have done it. Now verify that your related post is showing correctly.

In blogger blog post reactions, email post links, share buttons are not working

Blogger continuously add new features and sometimes you may wonder why some features are not working in your case. You might see that blog post reactions, email post links, share buttons are not working in any of the posts inside your blog.

You can enable blog post reactions, email post links, share buttons through,
- Login to your blogger account.
- Click on design.
- Click on Page Elements tab.
- Click on Edit for Blog Posts section.
- Configure Blog Posts pop up window will appear.
- Scroll down and find Post Page Options.
- Check the box Reactions, Show Email Post Links, Show Share Buttons
- Click Save.

You will be able to see blog post reactions, email post links, share buttons in each post of your blog. But you sometimes might wonder that though you have checked the boxes from Configure Blog Posts but those features are not working inside your post. This is expected if you use any third party template or you have changed the template code using "Edit HTML". Let's have an screenshot of what you actually done.

This is the output window which does not reflect the changes.

As you see from the above screenshot blog post reactions, email post links, share buttons are not displayed.

In order to solve the issue, do the following:
Step 01: Log in to blogger home with your email and password. You will see a window like below.

Step 02: Click on "Design" and then navigate to "Edit HTML".
Following is a screenshot for my blog.

Step 03: Backup your template before editing your template.
Before editing your template, you may want to save a copy of it. So click on "Download Full Template" and then save the template. This is very good way to backup template as if there is something wrong with your settings you can revert it back.

Step 04: Click on "Revert widget templates to default". A pop up window will appear. Note that, it will discard changes only to widget templates and your widget data will be unchanged. Click on ok on the pop up window.

Step 05: A confirmation window will appear saying that "Your changes have been saved. View Blog". Check your blog for the changes. Here is the screenshot of my blog.

ORA-00600: internal error code, arguments: [15264]

Problem Description
While dropping a table it fails with ORA-00600[15264] like below.

SQL> drop table oracle_dba;
drop table oracle_dba
           *
ERROR at line 1:
ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []

Also dropping index failed with ORA-01418

SQL> drop index oracle_dba_I;
drop index oracle_dba_I
           *
ERROR at line 1:
ORA-01418: specified index does not exist

Even more pathetic is selecting on table oracle_dba fails with ORA-22806: not an object or REF.

SQL> select * from oracle_dba;
select * from oracle_dba
*
ERROR at line 1:
ORA-22806: not an object or REF

This is really terrible and poor oracle stuff!

Investigation of the Problem
If you look for trace file Call Stack you will see an entry

... kkdldidRecycle ...

The problem started to happen once drop is cancelled as it was taking time and then whenever it is issued again ORA-00600 with argument 15264 is returned.

Cause of the problem
The problem happened due to objects in recyclebin.

Solution of the Problem
Step 01: Purge recylcbin.
SQL> PURGE RECYCLEBIN;
Now try to drop the table. If it fails move to step 02.

Step 02: Log in as sysdba and purge DBA recycle bin.
SQL> PURGE DBA_RECYCLEBIN;

Try to drop the table. If it fails move to step 03.

If you want to know more about purge command then have a look at,
http://arjudba.blogspot.com/2008/09/all-purge-commands-in-oracle-delete.html
Step 03: Change the RECYCLEBIN initialization parameter.
If you use 10.1 oracle database then set the init.ora parameter "_recyclebin" = false, bounce the database and try again the same drop.

If you use 10.2g oracle database and later version then run the following command:

SQL> ALTER SESSION SET RECYCLEBIN=OFF;
and try again the same drop.

DIM-04503: Message 4503 not found

problem Description
While starting an oracle instance using oradim command or whenever you invoke oradim it fails with DIM-04503 message like below.
E:\xampp\mysql\bin>oradim -start -sid orcl
DIM-04503: Message 4503 not found; product=RDBMS; facility=ORADIM

Cause of the Problem
The problem happened due to incorrect setting of ORACLE_HOME environmental variable. This can also happen if you don't set ORACLE_HOME environmental variable on your windows environment.

Solution of the Problem
Set ORACLE_HOME environmental variable to the location where you have installed oracle software. For example,
E:\xampp\mysql\bin>set ORACLE_HOME=E:\oracle\product\10.2.0\db_2
And now startup database using oradim.
E:\xampp\mysql\bin>oradim -start -sid orcl

Note that, if you have correct ORACLE_HOME setting then invoking oradim will show following output.

C:\Documents and Settings\User>oradim
ORADIM:  [options].  Refer to manual.
Enter one of the following command:
Create an instance by specifying the following options:
     -NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]
 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
 [-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
Edit an instance by specifying the following options:
     -EDIT -SID sid | -ASMSID sid [-SYSPWD pass]
 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
 [-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]
Delete instances by specifying the following options:
     -DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc
Startup services and instance by specifying the following options:
     -STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]
 [-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]
Shutdown service and instance by specifying the following options:
     -SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]
 [-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]
 Query for help by specifying the following parameters: -? | -h | -help

Note that if you set environmental variable using set command from command prompt then it will be set only for that session. If you want to set as permanent basis then,
- Right click on My Computer icon.
- Click on Properties.
- Go to Advanced tab.
- Click on Environmental Variable.
- Now you can set environmental variable permanently for a user or for system.
- Click on New either for a particular user or for system. If you set for system it will affect all users of the machine.
- Two textbox will appear. In the Variable Name field type ORACLE_HOME and in the Variable Value type the path of your oracle home.
- Click ok and you are done.