High water mark and suspicious large indexes.

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--
--   NAAM:     show_hwm_simple_and_index.sql
--   DOEL:     HighWaterMark info.
--   TYPERING: select
--   PARAMETERS: -
--   BENODIGDE PRIVILEGES select_catalog_role
--   ANDERE BEPERKINGEN: -
--   OPMERKINGEN: -
--
--   WIJZIGINGSHISTORIE:
--  
--   Versie     Datum       Auteur           Omschrijving
--   ---------  ----------  ---------------  ------------------------------------
--   1.0        25-03-2002  Dik              eerste oplevering
--   2.0        2013             Dik              added index information
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
col table_name format a30
col owner format a12
col percentage_waisted format 999.99
col hwm_MB format 9999999.99
col rows_length_MB format 9999999.99
set verify off
undefine percentage_waisted_allowed
undefine min_number_blocks_to_check_on
set lines 150
prompt '***********************************************************************************'
prompt this is a simple script for looking at suspicious hwm
prompt on ixora there is a sparse_tables_8.sql which takes also initrans etc. into account
prompt BASED ON LAST STATISTICS !!
prompt Dik Pater
prompt '***********************************************************************************'
select d.owner
,      d.table_name
-- ,      d.blocks
-- ,      d.empty_blocks
,      d.blocks*p.value/1024/1024 hwm_MB
,      d.num_rows*avg_row_len/1024/1024 rows_length_MB
,      decode(
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
            ),0,100,
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
                )
) percentage_waisted
,     decode(to_char(num_rows),'0','!! '||num_rows||' !!!',to_char(num_rows)) records
, sel_index.*
from dba_tables  d
,    v$parameter p
, (  SELECT i.owner, i.index_name, table_owner, table_name, s1.bytes index_omvang, s2.bytes table_omvang , s2.bytes - s1.bytes table_minus_index
FROM dba_indexes i
, dba_segments s1
, dba_segments s2
WHERE i.table_owner= '&OWNER'
AND i.index_type= 'NORMAL'
and  s1.owner = i.owner
and  s1.segment_name = i.index_name
and  s2.owner = i.table_owner
AND  s2.segment_name = i.table_name
AND  s1.segment_type <> 'TABLE PARTITION'
AND  s2.segment_type <> 'TABLE PARTITION' ) sel_index
where p.name = 'db_block_size'
and     decode(
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
            ),0,100,
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
                )
) > &percentage_waisted_allowed
and  d.blocks > &min_number_blocks_to_check_on
AND  d.BLOCKS > 1    -- no sense in checking empty tables with no blocks allocated
AND sel_index.table_owner = d.owner
and sel_index.table_name = d.table_name
order by d.blocks*p.value/1024/1024 desc
/

King of the Netherlands and Oracle software and holidays.

Today Queens day has become King's day in Holland.

But the traditional date of 30 April is left next year, when King's day is on 27 April, the birthday of our king Willem Alexander.
One thing the king of the Netherlands  did not know is that this has impact on our holiday programming in Oracle software land ;-)

Some one asked me to fill a calendar with holidays, I decided to do this with an analytical query.

After 2013 the Queens day ( koninginnedag ) has become King's day, I used a case statement for it.

But when is EasterSunday ? Googled around I found oracle plsql code :
With eastersunday I can calculate Whitsun, Ascension Day.

create or replace

function eastersunday(p_year number default to_number(to_char(sysdate,'RRRR'))) 

  return date is

/* 

||  Jan Thuis,   April 2004 

||  Calculate the date of Easter Sunday 

|| 

||  Gregorian method (any year since 1583) based on algorithm of Oudin 

*/ 

  l_eastersunday date; 

  l_g integer; 

  l_c integer; 

  l_d integer; 

  l_e integer; 

  l_h integer; 

  l_k integer; 

  l_p integer; 

  l_q integer; 

  l_i integer; 

  l_j integer; 

  l_x integer; 

begin

  l_g := mod(p_year,19); 

  l_c := floor(p_year/100); 

  l_d := l_c - floor(l_c/4); 

  l_e := floor((8 * l_c + 13)/25); 

  l_h := mod(l_d - l_e + 19 * l_g + 15 ,30); 

  l_k := floor(l_h / 28); 

  l_p := floor(29/(l_h + 1)); 

  l_q := floor((21 - l_g)/11); 

  l_i := l_h - l_k * (1 - l_k * l_p * l_q); 

  l_j := mod((p_year + floor(p_year/4 + l_i + 2 - l_d )),7); 

  l_x := 28 + l_i - l_j; 

  l_eastersunday := to_date('0103'||p_year,'DDMMYYYY') + l_x -1; 

  return l_eastersunday; 

END EASTERSUNDAY;

/

With this code I made this holiday query, simply it does this :
- Is it a workday then eerste_werkdag = datum.
- Is not a workday then eerste_werkdag = the next datum where werkdag = Y.


SELECT DATUM,
  Werkdag,
  CASE
    WHEN TO_CHAR(Datum,'DD-MM') = '31-12'
    AND Werkdag                 = 'N'
    Then Datum+2
    WHEN TO_CHAR(Datum,'DD-MM') = '30-12'
    And Werkdag                 = 'N'
    Then Datum+3    
    ELSE
      CASE
        WHEN WERKDAG = 'J'
        THEN DATUM
        ELSE Lead(Hulp Ignore Nulls) Over (Order By Datum)
      END
  END Eerste_Werkdag ,
  OPMERKING
FROM
  (SELECT DATUM,
    WERKDAG,
    CASE
      WHEN werkdag = 'J'
      THEN datum
      ELSE NULL
    END HULP,
    opmerking
  FROM
    (SELECT MAIN.DATUM,
      TO_CHAR(MAIN.DATUM,'day'),
      CASE
        WHEN trim(TO_CHAR(MAIN.DATUM,'day')) IN ('saturday', 'sunday' )
        OR feestdag.datum                    IS NOT NULL
        THEN 'N'
        ELSE 'J'
      END WERKDAG ,
      OPMERKING
    FROM
      (SELECT TRUNC( to_date('01-01-'
        ||'&&JAAR', 'DD-MM-YYYY') + LEVEL -1) DATUM
      FROM DUAL
        CONNECT BY LEVEL <=
        (SELECT DAYS_IN_YEAR
        FROM
          (SELECT TO_CHAR(TRUNC(input_date, 'YYYY'), 'YYYY')                      AS "YEAR",
            ADD_MONTHS(TRUNC(INPUT_DATE, 'YYYY'), 12) - TRUNC(input_date, 'YYYY') AS days_in_year
          FROM
            (SELECT ADD_MONTHS(TRUNC(TO_DATE('&&JAAR','YYYY')) -365, +12 * level) INPUT_DATE
            FROM dual
              CONNECT BY level <= 20
            )
          ) SEL
        WHERE SEL.YEAR = TO_CHAR(to_date('&&JAAR','YYYY'),'YYYY')
        )
      ) Main ,
      (SELECT *
      FROM
        (SELECT Datum,
          CASE
            WHEN Opmerking             ='Hemelvaartsdag'
            AND TO_CHAR(Datum,'DD-MM') = '05-05'
            THEN 'Hemelvaartsdag/Bevrijdingsdag'
            ELSE Opmerking
          END opmerking,
          row_number() Over (Partition BY Datum Order By Datum ) DUBBELFEEST
        FROM
          ( SELECT XXX_EASTERSUNDAY('&&JAAR') DATUM, '1e Paasdag' OPMERKING FROM DUAL
          UNION ALL
          SELECT XXX_EASTERSUNDAY('&&JAAR')+1, '2e Paasdag' FROM DUAL
          UNION ALL
          SELECT XXX_EASTERSUNDAY('&&JAAR')+39, 'Hemelvaartsdag' FROM DUAL
          UNION ALL
          SELECT XXX_EASTERSUNDAY('&&JAAR')+49, '1e Pinksterdag' FROM DUAL
          UNION ALL
          SELECT XXX_EASTERSUNDAY('&&JAAR')+50, '2e Pinksterdag' FROM DUAL
          UNION ALL
          SELECT TO_DATE('05-MAY-&&JAAR','DD-MON-YYYY'), 'Bevrijdingsdag' FROM DUAL
          UNION ALL
          SELECT TO_DATE('25-DEC-&&JAAR','DD-MON-YYYY'), '1e Kerstdag' FROM DUAL
          UNION ALL
          SELECT TO_DATE('26-DEC-&&JAAR','DD-MON-YYYY'), '2e Kerstdag' FROM dual
          UNION ALL
          SELECT TO_DATE('01-JAN-&&JAAR','DD-MON-YYYY'), 'Nieuwjaarsdag' FROM dual
          UNION ALL
          SELECT
            CASE
              WHEN
                &&JAAR > 2013
              THEN TO_DATE('27-APR-&&JAAR','DD-MON-YYYY')
              ELSE TO_DATE('30-APR-&&JAAR','DD-MON-YYYY')
            END ,
            'Koning..dag'
          FROM Dual
          )
        )
      WHERE Dubbelfeest=1
      ) FEESTDAG
    WHERE main.DATUM = FEESTDAG.DATUM (+)
    ORDER BY main.DATUM
    )
  ORDER BY Datum
  )

Cloud Control 12 udm to metric extension

Today I got a question to move udm from gridcontrol 11 to 12c metric extensions.
I used this inventarisation query as as startpoint, when I finished the query I thought that this could be usefull for other oracle dba' as well:

SELECT target_name,
  collection_name,
  aantal,
  SUM(aantal) OVER ( PARTITION BY collection_name ) running_total_by_coll_name,
  CASE
    WHEN SUM(aantal) OVER ( PARTITION BY collection_name ) > 1
    THEN 'POSSIBLE GENERIC METRIC EXTENSION'
    ELSE 'UNIQUE METRIC EXTENSION'
  END ME_OUTPUT
FROM
  (SELECT target_name,
    collection_name,
    COUNT(*) aantal
  FROM mgmt$target_metric_settings
  WHERE METRIC_NAME        ='SQLUDM'
  AND collection_name NOT IN ('UDM_GENERIC_SECURITY_1','UDM_GENERIC_SECURITY_2', 'UDM_GENERIC_BACKUP_CHECK' , 'UDM_GENERIC_UNUSABLE_INDEXES')
  GROUP BY target_name,
    collection_name
  )

sw ( switching directories on linux)

Sometimes i'm a little tired of cd-ing between directories.

A long time ago I had a sw function in korn shell made by Frans van der Meijs.

But now on linux we use bash.

After a small adjustment it runs on linux - bash as well, it can save time and typeing .

 Just make a file called .dests in your $HOME directory.

 put in some paths.
cat $HOME/.dests

/tmp
/home
$ORACLE_HOME
$ORACLE_HOME/rdbms/admin
$ORACLE_HOME/network/log
$TNS_ADMIN
$ORA_CRS_HOME

source this function

. $HOME/sw

Contents of the sw file :


DESTS_FILE=$HOME/.dests
sw()
{
  index=0
  while read LINE
  do
    if [ $? -ne 0 ]
    then break ;
    fi
    eval "MENU_ITEM[\${index}]=$LINE"
    index=`expr $index + 1 `
  done < $DESTS_FILE
  nr_of_dests=$index
  if [ $# -eq 1 ]
  then
    ANSWER=$1
  else
    echo ""
    echo "SW-INFO-01: Destinations"
    echo ""
    index=0
    while [ $index -lt $nr_of_dests ]
    do
      echo " --  ${index}   ${MENU_ITEM[${index}]} --"
      index=`expr $index + 1`
    done
    echo
    echo "Your choice [] "
    read ANSWER
  fi
  if test -d ${MENU_ITEM[$ANSWER]}
  then
    echo  "SW-INFO-02: Changing current working directory to ${MENU_ITEM[$ANSWER]} SUCCEEDED"
    cd ${MENU_ITEM[$ANSWER]}
  else
  echo  "SW-ERR-01 : Changing current working directory to ${MENU_ITEM[$ANSWER]} FAILED, DIRECTORY DOES NOT EXIST !"
  echo  "SW-INFO-03: please clean this entry from $HOME/.dests"
  fi
  pwd
}
filldest()
{
fc -l -10000 | awk '{print $2,$3}' | grep "cd \/" | sort | uniq | sed 's/cd //'>> $HOME/.dests
}
echo "SW-INFO-00: Use command >> sw << to switch directories ."
echo "SW-INFO-03: Use command >> filldest << to fill $DESTS_FILE ."


Usage :

sw 1
you switch to destination 1
sw 11
you switch to destination 11
sw
It will give you the menu.
Make a choice ....
HAPPY SWITCHING.

ORA-30009: Not enough memory for CONNECT BY operation and lotto

SQL> @lotto
SQL> select winnend_getal
2 from (
3 select winnend_getal, count(*)
4 from (
5 select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
6 from
7 ( select level n from dual
8 connect by level <= &aantal_nummers )
9 )
10 group by winnend_getal order by count(*) desc
11 )
12 where rownum <=6
13 /
Enter value for aantal_nummers: 1000000
old 8: connect by level <= &aantal_nummers )
new 8: connect by level <= 1000000 )
select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
*
ERROR at line 5:
ORA-30009: Not enough memory for CONNECT BY operation


Elapsed: 00:00:06.32
SQL> select winnend_getal
2 from (
3 select winnend_getal, count(*)
4 from (
5 select round(DBMS_RANDOM.VALUE(1,45)) winnend_getal
6 from
7 (
8 with n(n) as ( select 0
9 from dual
10 union all
11 select n+1 from n
12 where n<=&aantal_nummers
13 )
14 select * from n
15 )
16 )
17 group by winnend_getal order by count(*) desc
18 )
19 where rownum <= 6
20 /
Enter value for aantal_nummers: 1000000
old 12: where n<=&aantal_nummers
new 12: where n<=1000000

WINNEND_GETAL
-------------
35
15
12
9
41
11

6 rows selected.

Elapsed: 00:00:46.28
SQL>

I found this on asktom from a reply of user Sokrates ....

BTW Hope someone will win the lotto ....

filsystem_ioORA-56708

In DBCONSOLE I received the following message on a hpux 11.31 machine while trying to run the io calibrate feature of 11g.
DBCONSOLE> Information

The following Initialization parameters need to be set to the specified
value in order to run the I/O calibration tool successfully:
filesystemio_options - ASYNCH or SETALL
This error is a bit misleading...because filesystemio_options is already set, the cause is that the async io is disabled at the operating level......

cat calibrate_io.sql
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (8, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
end;
/

SQL> @"calibrate_io.sql"
DECLARE
*
ERROR at line 1:
ORA-56708: Could not find any datafiles with asynchronous i/o capability
ORA-06512: at "SYS.DBMS_RMIN", line 456
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 1285
ORA-06512: at line 7

SQL> show parameter asyn

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
disk_asynch_io boolean
TRUE
tape_asynch_io boolean
TRUE
SQL> show parameter filesys

NAME TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
filesystemio_options string
SETALL
SQL> select name,asynch_io from v$datafile f,v$iostat_file i
where f.file#=i.file_no
and (filetype_name='Data File' or filetype_name='Temp File');

NAME ASYNCH_IO
------------------------------ ---------------------------

/oracle/dikpater/db/apps_st/da ASYNC_OFF
ta11g/DIKPATEROCP/datafile/o1_
mf_undotbs1_6677o0hr_.dbf

ora-27492

job_queue_processes checked it was 1000 ;

exec dbms_ijob.set_enabled(true) ;

Solved the problem.

11gR2 SP2-1503: Unable to initialize Oracle call interface

$ which sqlplus
/oracle/system1/db/tech_st/11.2.0/bin/sqlplus
ibes01@system1: sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
****************************************************************************
The cause is that the variable ORA_TZFILE was pointing to a 10g environment.
****************************************************************************
$ set | grep TZ
ORA_TZFILE=/oracle/system1/db/tech_st/10.2.0/oracore/zoneinfo/timezlrg.dat
TZ=MET-1METDST

$ unset ORA_TZFILE
$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 24 08:58:55 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name:

imp hang import hang 25475

If your import doesn't continues
and current statement is :
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
then you might be using the wrong client s/w.
In our case we used 9206 instead of 9208. The import hangs.
Thanks to Frank van Bortel who run into this case.

listener not registered dbca dbconsole 11g filegroup12.jar missing CompEMdbconsole.pm not found

I made a mistake, file win32_11gR2_database_2of2.zip was not extracted correctly I think.
So I missed a lot of filegroup.jar which was reported during the installation...;-)

So not noting this, I tried to create a database with dbca, all went ok except the dbconsole went wrong.
The errors I received where misleading :
1. Listener is not up or database service is not registered
2. CompEMdbconsole.pm not found.

By extracting the win32_11gR2_database_2of2.zip . Create a new database.
Configured with netca a listener2 on port 1526.
sqlplus / as sysdba
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101
)(PORT=1526))'
SQL> /

Then I run PLEASE NOTE THE USE OF paramname oracle_hostname:
D:\app\Gebruiker\product\11.2.0\dbhome_2>emca -config dbcontrol db -paramname or
acle_hostname:localhost

STARTED EMCA at 25-jul-2010 13:25:22
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: demo
Listener port number: 1526
Listener ORACLE_HOME [ D:\app\Gebruiker\product\11.2.0\dbhome_2 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\app\Gebruiker\product\11.2.0\dbhome_2

Local hostname ................ 192.168.1.101
Listener ORACLE_HOME ................ D:\app\Gebruiker\product\11.2.0\dbhome_2
Listener port number ................ 1526
Database SID ................ demo
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
25-jul-2010 13:25:44 oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\app\Gebruiker\cfgtoollogs\emca\demo\e
mca_2010_07_25_13_25_21.log.
25-jul-2010 13:25:52 oracle.sysman.emcp.EMReposConfig uploadConfigDataToReposito
ry
INFO: Uploading configuration data to EM repository (this may take a while) ...
25-jul-2010 13:30:20 oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
25-jul-2010 13:30:31 oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
25-jul-2010 13:30:31 oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
25-jul-2010 13:32:01 oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
25-jul-2010 13:32:01 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
25-jul-2010 13:32:18 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
25-jul-2010 13:32:18 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
25-jul-2010 13:34:07 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
25-jul-2010 13:34:08 oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://192.168.1.101:1158/em <<<<
<<<<<<<
25-jul-2010 13:34:14 oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager
data will be encrypted. The encryption key has been placed in the file: D:/app/
Gebruiker/product/11.2.0/dbhome_2/192.168.1.101_demo/sysman/config/emkey.ora.
Please ensure this file is backed up as the encrypted data will become unusable
if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at 25-jul-2010 13:34:15

And now I can use dbconsole and prepare for the OCP exam.

DIA-49424 and perl not found

adrci> show home
ADR Homes:
diag\rdbms\dik\dik
adrci> ips add file diag\rdbms\dik\dik\trace\dik_dbrm_1260.trc package 4
DIA-49424: Directory outside ADR not allowed
adrci> ips add file \trace\dik_dbrm_1260.trc package 4
DIA-49424: Directory outside ADR not allowed
adrci> ips add file /trace/dik_dbrm_1260.trc package 4
Added file /trace/dik_dbrm_1260.trc to package 4

adrci> ips generate package 4 ;
perl wordt niet herkend als een interne
of externe opdracht, programma of batchbestand.
perl wordt niet herkend als een interne
of externe opdracht, programma of batchbestand.
Generated package 4 in file C:\Users\Gebruiker\ORA4031_20100724214430_COM_1.zip,
mode complete
adrci> exit

D:\>set path=%PATH%;D:\app\Gebruiker\product\11.2.0\dbhome_1\perl\bin

D:\>perl -v

This is perl, v5.10.0 built for MSWin32-x86-multi-thread

Copyright 1987-2007, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl". If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.


D:\>adrci

ADRCI: Release 11.2.0.1.0 - Production on Za Jul 24 22:11:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "d:\app\gebruiker"
adrci> show homes
ADR Homes:
diag\clients\user_gebruiker\host_1490817586_76
diag\clients\user_system\host_1490817586_76
diag\rdbms\dik\dik
diag\tnslsnr\gebruik-3i5s8n0\listener
adrci> set home diag\rdbms\dik\dik
adrci> ips generate package 4;
Generated package 4 in file D:\ORA4031_20100724214430_COM_2.zip, mode complete

ORA-04067

last week in forms10g we got ORA-04067.
In development and test we did not had this problem, checking the config files, searching metalink we could not solve this problem.
Finally we relinked the forms executable that solved the problem.
Statement used :
$ make -f ins_forms.mk install

rman disk backup from test server to another server

ON TEST

$ cd /tmp
$ ln -s /oracle/systemTEST/oraexport kloon

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'/tmp/kloon/kloon_set_test';

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'/oracle/systemTEST/oraexport/kloon_set_test';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/tmp/kloon/kloon_set_test';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> backup database include current controlfile ;


channel ORA_DISK_1: starting piece 1 at 05-FEB-10
channel ORA_DISK_1: finished piece 1 at 05-FEB-10
piece handle=/tmp/kloon/kloon_set_test tag=TAG20100205T131237
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-FEB-10


TRANSFER THIS FILE TO DVL system

then on DVL

1. create an initDVL.ora
include
*.compatible='10.2.0.4.0'
*.control_files='/oracle/systemDVL/control................adjust this
line
*.db_block_size=16384
*.db_domain='x.x.nl'
*.db_name='dvl'
*.job_queue_processes=10
*.local_listener='(ADDRESS=(protocol=tcp)(host=systemDVL)(PORT=1521))'
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=200M
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.DB_FILE_NAME_CONVERT='/oracle/systemTEST/oradata01/test/','/oracle/sys
temDVL/oradata01/dvl/'
*.LOG_FILE_NAME_CONVERT='/oracle/systemTEST/oraredo/test/','/oracle/syst
emDVL/oraredo/dvl/',


$ cd /tmp
$ ln -s /oracle/systemDVL/oraexport kloon

RMAN >
connect auxiliary sys/password
connect target sys/password@//TEST/db_on_test
run {
ALLOCATE auxiliary CHANNEL ch1 DEVICE TYPE disk format
'/tmp/kloon/kloon_set_test' ;
DUPLICATE TARGET DATABASE TO db_on_dvl ;
}

TOAD, timestamp ORA-03120 two-task conversion routine: integer overflow

When i did a select timestamp_column from table_with_timestamp_in_it
I received : ORA-03120 two-task conversion routine: integer overflow
We used an Oracle_home of 8.0, after changing this to 10, it worked fine.

dikpater: Oracle Certified Master

dikpater: Oracle Certified Master

UNIX tips

HPUX howto create a file of 1GB
$ dd if=/dev/zero of=file_1GB count=2000000
2000000+0 records in
2000000+0 records out
$ ls -l *GB
-rw-r--r-- 1 oracle dba 1024000000 Jan 7 09:29 file_1GB

Oracle Certified Master

Ik heb het gehaald.
2 zware dagen, in spanning afgewacht en beloond.
Mede dankzij de masterclasses van Ordina gegeven door Jeroen Evers, en dankzij mijn collega's die als sparring partner functioneerden.
Last but not least : Harald van Breederode bedankt voor het bijbrengen van de Oracle lesstof die er toch ingehamerd is en heel goed in de praktijk toepasbaar is.
PS het is inderdaad een examen voor de echte mannen...

Dik

MASTER OF DISASTER or MASTER OF ORACLE

Maandag en dinsdag het ocm examen (2e keer, de 1e keer ben ik gezakt) gedaan.
2 dagen alles uit jezelf halen op het gebied van Oracle.
Met een NDA op zak, kan ik inhoudelijk niet ingaan op de examenstof, maar ik kan wel aangeven dat dit voor een ORACLE DBA een must do is.
Hopend op een goede afloop wacht ik af......
Dik

ezconnect tnsping examples

Ping only hostname defaults to listener and port 1521
rac1:-) tnsping //rac1
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 20-NOV-2008 21:24:19
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the aliasAttempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=rac1.mycorpdomain.com))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.131)(PORT=1521)))OK (10 msec)
Ping host and port
rac1:-) tnsping //rac1:1528
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 20-NOV-2008 21:24:23
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the aliasAttempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=rac1.mycorpdomain.com))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.131)(PORT=1528)))OK (10 msec)
Ping host and port and servicename
rac1:-) tnsping //rac1:1528/patertje
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 20-NOV-2008 21:24:28
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the aliasAttempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=patertje))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.131)(PORT=1528)))OK (10 msec)
Ping host and port and sid
rac1:-) tnsping //rac1:1528/xyz
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 20-NOV-2008 21:24:33
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the aliasAttempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=xyz))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.131)(PORT=1528)))OK (0 msec)

flashback

Playing with snapshots I made a snapshot log on table x;
Testing my updates....
Then tried to do ;

select * from x
where id=1
as of timestamp sysdate -1/24 ;

Ora-01466 appears : Unable to read data table definition has changed.

Just logged this one to remember...