DataPump: UDE-00019: 'tables' parameter list is too long.

This is due to bug in Oracle <11. When you are using parameter TABLES=, it cannot be longer than 4000 characters. To ommit this you can create table containing list of all the tables you want to export :

CREATE TABLE expdp_tab (owner VARCHAR2(30), object_name VARCHAR2(128), object_type VARCHAR2(19));

INSERT INTO expdp_tab VALUES ('schema','table_name','TABLE');
....
commit;

and then replace TABLES=(...) in parfile with this:
INCLUDE=TABLE:"IN (SELECT OBJECT_NAME FROM EXPDP_TAB)"

Keep in mind one important thing. When you are running direct impdp from source database to local using NETWORK_LINK parameter, table with list of tables needs to be created on source database, if you create this on local, you will get error like this:
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPM$MCP", line 2614

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