Inbound connection timed out (ORA-3136)

After upgrade database to 10.2.x you can observe such entries in alertlog:

WARNING: inbound connection timed out (ORA-3136)

It is caused by the fact that in 10.2.x default value of parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds.

To fix it:

1. On server side in sqlnet.ora add line:

SQLNET.INBOUND_CONNECT_TIMEOUT=120

2. On server side in listener.ora add line:

INBOUND_CONNECT_TIMEOUT_<listener_name> = 110




How to clean datapump imp/exp

1. Check name and owner of running datapump job:

  1* select owner_name, job_name,state from dba_datapump_jobs

OWNER_NAME                     JOB_NAME                       STATE
------------------------------ ------------------------------ ------------------------------
DATAPUMP                       SYS_IMPORT_FULL_01             EXECUTING
DATAPUMP                       SYS_EXPORT_TABLE_01            NOT RUNNING

2. Attach to job :

impdp datapump/<pwd>@<SID> attach=SYS_IMPORT_FULL_01

3. Kill job:

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

Datapump - short info I

SQL> create directory datapump as '/app/oracle/export';

Directory created.

SQL> create user datapump identified by datapump;

User created.

SQL> grant create session, exp_full_database, imp_full_database to datapump;

Grant succeeded.

conn datapump:
SQL> create database link JDED_TEST connect to testdta identified by **** using 'JDED';

Database link created.

SQL> alter user datapump default tablespace reddtat;

User altered.

SQL> alter user datapump quota unlimited on reddtat;

expdp.par:
userid=datapump/****@JDERED
DIRECTORY=datapump
NETWORK_LINK=JDED_TEST
DUMPFILE=jded_test1.dmp
LOGFILE=jded_test1.log
tables=TESTDTA.DATAPUMP_1

impdp.par:
userid=datapump/****@JDERED
DIRECTORY=datapump
DUMPFILE=jded_test1.dmp
LOGFILE=jded_test_imp.log
REMAP_SCHEMA=TESTDTA:REDDTA
REMAP_TABLESPACE=TESTDTAT:REDDTAT
REMAP_TABLESPACE=TESDTAI:REDDTAT

Flashback - Time Machine

We have already database in flashback mode. Let's see what option in gives us:

1. See object in a past:

SQL> select count(*) from t1;

  COUNT(*)
----------
    132027

SQL> select count(*) from t1 as of timestamp to_timestamp('2008-11-26 07:00:00','YYYY-MM-DD HH24:MI:SS');

  COUNT(*)
----------
     40001

Alternatively you can use SCN:

SQL>  SELECT SCN,
2     to_char(TIME_DP, 'YYYY-MM-DD HH24:MI:SS') TIME_DP
3     from sys.SMON_SCN_TIME
4     where to_char(TIME_DP,'YYYY-MM-DD HH24') = to_char(sysdate-1,'YYYY-MM-DD HH24')
5    order by SCN_BAS;

           SCN TIME_DP
-------------- ------------------------------
 8704635105759 2008-11-26 07:00:29
 8704635105915 2008-11-26 07:05:34
 8704635106215 2008-11-26 07:10:31
 8704635106316 2008-11-26 07:15:34
 8704635106420 2008-11-26 07:20:32
 8704635106521 2008-11-26 07:25:35
 8704635106631 2008-11-26 07:30:32
 8704635106735 2008-11-26 07:35:35
 8704635106839 2008-11-26 07:40:32
 8704635106940 2008-11-26 07:45:35
 8704635107058 2008-11-26 07:50:32
 8704635107159 2008-11-26 07:55:35

SQL> select count(*) from t1 as of scn 8704635105759;

  COUNT(*)
----------
     40001




2. Turn database to some point in time:

SQL>  EXECUTE dbms_Flashback.Enable_At_Time(to_date('2008-11-26 07:00:00','YYYY-MM-DD HH24:MI:SS'));

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
     40001

SQL> EXECUTE dbms_Flashback.disable;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
    132027

Quick Flashback setup

1. Archive_log_dest and Archive_log_dest_duplex needs to be disabled. If any additionaly archive log destination is needed, use archive_log_dest_n='LOCATION=.....'

2. alter system set DB_RECOVERY_FILE_DEST_SIZE=<size>M;

3. alter system set DB_RECOVERY_FILE_DEST='<destination>';

4. Shutdown database

5. Startup mount

6. Alter database archivelog (just to be sure)

7. Alter database flashback on;

8. Alter database open

Monitoring:

1. select * from V$FLASH_RECOVERY_AREA_USAGE;

In case of lack of space, use rman to backup flasharea to tape and delete input:

run
{
allocate channel ch1 type = 'SBT_TAPE';
backup recovery area delete input;
}

Find High Water Mark for all datafiles in database

set serveroutput on
execute dbms_output.enable(20000);

declare
cursor c_dbfile is
select tablespace_name
,file_name
,file_id
,bytes
from sys.dba_data_files
order by tablespace_name,file_id;

cursor c_space(v_file_id in number) is
select block_id,blocks
from sys.dba_free_space
where file_id=v_file_id
order by block_id desc;

blocksize integer;
filesize integer;
extsize integer;

begin

/* get the blocksize of the database, needed to calculate the startaddress */

select value
into blocksize
from v$parameter
where name = 'db_block_size';

/* retrieve all datafiles */

for c_rec1 in c_dbfile
loop
filesize := c_rec1.bytes;
for c_rec2 in c_space(c_rec1.file_id)
loop
extsize := ((c_rec2.block_id - 1)*blocksize + c_rec2.blocks*blocksize);
if extsize = filesize
then
filesize := (c_rec2.block_id - 1)*blocksize;
else
/* in order to shrink the free space must be uptil end of file */
exit;
end if;
end loop outer;
if filesize = c_rec1.bytes
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can not be resized, no free space at end of file.')
;
dbms_output.put_line('.');
else
if filesize < 2*blocksize
then
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||2*blocksize
||' Bytes, Actual size: '||c_rec1.bytes||' Bytes');
dbms_output.put_line('.');
else
dbms_output.put_line('Tablespace: '
||' '||c_rec1.tablespace_name||' Datafile: '||c_rec1.file_name);
dbms_output.put_line('Can be resized uptil: '||filesize
||' Bytes, Actual size: '||c_rec1.bytes);
dbms_output.put_line('.');
end if;
end if;
end loop;
end;
/

Move outline between databases (different schemas)

create outline out_1 on
<query>;

create private outline out_prv from out_1;

delete from system.ol$hints;
commit;

insert into system.ol$hints select * from outln.ol$hints@remote_db where ol_name='remote_outline'
;

update system.ol$hints set ol_name='out_prv'

.... change system.ol$hints column user_table_name;

exec dbms_outln_edit.refresh_private_outline('OUT_PRV');

create or replace outline out_1 from private out_prv;

10g Upgrade: Failed to load Media Management Library

Error while rman:
RMAN-03009: failure of allocate command on ch1 channel at 09/13/2008 12:42:27
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

Solution:
cd $ORACLE_HOME/lib
ln -s /usr/lib/libnwora.a libobk.a

CATPROC INVALID in DBA_REGISTRY

SQL> SELECT SUBSTR(comp_id,1,15) comp_id,
status,
SUBSTR(version,1,10) version,
SUBSTR(comp_name,1,30) comp_name
FROM dba_registry ORDER BY 1
  2    3    4    5    6  ;


COMP_ID                        STATUS      VERSION
------------------------------ ----------- ------------------------------
COMP_NAME
--------------------------------------------------------------------------------
CATALOG                        VALID       9.2.0.7.0
Oracle9i Catalog Views

CATPROC                        INVALID     9.2.0.7.0
Oracle9i Packages and Types

SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)
----------
        0


SQL> exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

PL/SQL procedure successfully completed.

SQL>SELECT SUBSTR(comp_id,1,15) comp_id,
status,
SUBSTR(version,1,10) version,
SUBSTR(comp_name,1,30) comp_name
FROM dba_registry ORDER BY 1
  2    3    4    5    6  ;

COMP_ID                        STATUS      VERSION
------------------------------ ----------- ------------------------------
COMP_NAME
--------------------------------------------------------------------------------
CATALOG                        VALID       9.2.0.7.0
Oracle9i Catalog Views

CATPROC                        VALID       9.2.0.7.0
Oracle9i Packages and Types

Force different index usage in stored outline

 update system.ol$hints set hint_text='INDEX_RS_ASC(@"SEL$1" "table_name"@"SEL$1" "index_name")' where hint#=7;

Force Oracle 10g to use 9.2.0 optimizer for specific query only

There is a parameter optimizer_features_enable which by default is set to 10.2.0.x in Oracle 10.2.0.x. Changing this to 9.2.0 cause that all features of 10.2.0 optimizer are disabled. If you need optimizer 9.2.0 to be applied in only one specific query (because there are no possibilities to use hints, playing with statistics also doesn't help, and execution plan from ver 9.2.0 performs much better), you can do this using stored outlines. This is how to do this:

1. alter system set use_stored_outlines = TRUE

2. create outline OUTLN_1 on <your statement>;

3. create private outline OUTLN_1_PRV from OUTLN_1;

4. select * from <schema>.ol$hints where name='OUTLN_1_PRV'

5. Now you have modify your private outline:

a) first of all you need to find in output from point 4 hint# containining OPTIMIZER_FEATURED_ENABLED and change this:
update <schema>.ol$hints set
hint_text='OPTIMIZER_FEATURES_ENABLE(''9.2.0'')'
where ol_name = 'OUTLN_1_PRV' and hint#=x;

b) also you need to reset access path, so need to find (also from output 4) line containing something like FULL, INDEX etc, and delete this:
delete from <schema>.ol$hints where ol_name='OUTLN_1_PRV'
and hint#=x;

6. exec dbms_outln_edit.refresh_private_outline('OUTLN_1_PRV');

7. create or replace outline OUTLN_1 from private OUTLN_1_PRV;