some info about datapump import

data pump is much efficient than imp/exp , which help me import 19Gb within one hour without generating redo logs.


LIQY@ADMP> @chk_session    

SUBSTR(USERNAME,1,15)                                SID    SERIAL# SQL_HASH_VALUE SERVER    STATUS   OSUSER
--------------------------------------------- ---------- ---------- -------------- --------- -------- ------------------------------
SUBSTR(MACHINE,1,20)
------------------------------------------------------------
SUBSTR(PROGRAM,1,25)                                                        TO_CHAR(LOGON_TIME,'DD-MON CPU_MINUTES
--------------------------------------------------------------------------- -------------------------- -----------
SYSMAN                                              1079          9     4281219134 DEDICATED ACTIVE
bill07
OMS                                                                         08-apr-2010 02:09:47        2.56233333

DBSNMP                                              1101          3              0 DEDICATED ACTIVE   oracle1
bill07
emagent@bill07 (TNS V1-V3                                                   08-apr-2010 02:11:20        2.78166667

SYS                                                  187      64288     3732391352 DEDICATED ACTIVE   oracle1
bill07
udi@bill07 (TNS V1-V3)                                                      19-apr-2010 15:54:17              .011

SYS                                                  269      16294              0 DEDICATED ACTIVE   oracle1
bill07
oracle@bill07 (DM00)                                                        19-apr-2010 15:54:17        .011833333

SYS                                                 1069       3981              0 DEDICATED ACTIVE   oracle1
bill07
oracle@bill07 (DW01)                                                        19-apr-2010 15:54:18        1.69966667

LIQY                                                 359         32     2579827540 DEDICATED ACTIVE   liqy
bill07
sqlplus@bill07 (TNS V1-V3                                                   19-apr-2010 15:55:18              .002


6 rows selected.


--SID 187, 269 and 1069 belong to datapump import, directly write to data file by DB writer.

LIQY@ADMP> @chk_session_wait

       SID SUBSTR(EVENT,1,25)                                                                  P1 SUBSTR(P1TEXT,1,10)
---------- --------------------------------------------------------------------------- ---------- ------------------------------
        P2 SUBSTR(P2TEXT,1,10)                    P3 SUBSTR(P3TEXT,1,10)
---------- ------------------------------ ---------- ------------------------------
       187 wait for unread message o                                                   1.3835E+19 channel co
1.3835E+19 channel ha                              0

       269 wait for unread message o                                                   1.3835E+19 channel co
1.3835E+19 channel ha                              0

       283 Streams AQ: qmn slave idl                                                            0
         0                                         0

       534 Streams AQ: waiting for t                                                            0
         0                                         0

       536 Streams AQ: qmn coordinat                                                            0
         0                                         0

      1017 Streams AQ: qmn slave idl                                                            0
         0                                         0

      1069 Datapump dump file I/O                                                               1 count
       256 intr                           4294967295 timeout

      1079 wait for unread message o                                                   1.3835E+19 channel co
1.3835E+19 channel ha                              0

      1101 Streams AQ: waiting for m                                                       602323 queue id
1.3835E+19 process#                                5 wait time

      1102 Streams AQ: waiting for t                                                            0
         0                                         0


10 rows selected.

--SID 187, 269 and 1069 belong to datapump import


2. insert data first , then rebuild indexes. Howerver, during data is written to data file, indexes remain valid and usable (not like directly path loading by sql*loader)

LIQY@ADMP> select segment_name, bytes from dba_segments where segment_name like '%NONVOICE03%';

SEGMENT_NAME                                                                           BYTES
--------------------------------------------------------------------------------- ----------
IMPDP_M1_SUREPAY_NONVOICE03                                                           524288
M1_SUREPAY_NONVOICE03                                                             1.5781E+10
M1_SUREPAY_NONVOICE03_IDX1                                                        1090519040
M1_SUREPAY_NONVOICE03_IDX2                                                        1237319680
M1_SUREPAY_NONVOICE03_IDX3                                                          20971520
M1_SUREPAY_NONVOICE03_IDX4                                                          20971520

6 rows selected.

--notice the indexes's size keep changing one by one.
LIQY@ADMP> /

SEGMENT_NAME                                                                           BYTES
--------------------------------------------------------------------------------- ----------
M1_SUREPAY_NONVOICE03                                                             1.5781E+10
M1_SUREPAY_NONVOICE03_IDX1                                                        1090519040
M1_SUREPAY_NONVOICE03_IDX2                                                        1384120320
M1_SUREPAY_NONVOICE03_IDX3                                                        1048576000
M1_SUREPAY_NONVOICE03_IDX4                                                         901775360


-rw-rw----   1 oracle1    dba1       26440704 Apr 19 13:31 ADMP_0001_525208771_80943.arc
-rw-rw----   1 oracle1    dba1         55296 Apr 19 13:31 ADMP_0001_525208771_80944.arc
-rw-rw----   1 oracle1    dba1       24170496 Apr 19 13:59 ADMP_0001_525208771_80945.arc
drwxrwxr-x   2 oracle1    dba1         16384 Apr 19 14:22 oldarch
-rw-rw----   1 oracle1    dba1       195064832 Apr 19 16:25 ADMP_0001_525208771_80946.arc

--no log file generated


-rw-rw-r--   1 oracle1    dba1       13533204480 Apr 19 15:32 expdp_M1_SUREPAY_NONVOICE03.dmp
-rw-rw-r--   1 oracle1    dba1       698085376 Apr 19 15:32 expdp_M1_SUREPAY_OTHER03.dmp
-rw-rw-r--   1 oracle1    dba1       19535446016 Apr 19 15:38 expdp_M1_SUREPAY_VOICE03.dmp

bill07:ADMP:/csa074/oradata/surepayimp> cat impdp_M1_SUREPAY_VOICE03.par
userid='/ as sysdba'
directory=surepay_dump_dir
dumpfile=expdp_M1_SUREPAY_VOICE03.dmp
logfile=impdp_M1_SUREPAY_VOICE03.log
tables=dbam1.M1_SUREPAY_VOICE03
CONTENT=data_only    # whit table & indexes exist (truncate before import)
#CONTENT=metadata_only
#REMAP_SCHEMA=dbam1:liqy
REMAP_TABLESPACE=surepay_tbl:m1_surepay_tbl
REMAP_TABLESPACE=surepay_idx:m1_surepay_idx
job_name=impdp_M1_SUREPAY_VOICE03

bill07:ADMP:/csa074/oradata/surepayimp> ll expdp_M1_SUREPAY_VOICE03.dmp
-rw-rw-r--   1 oracle1    dba1       19535446016 Apr 19 15:38 expdp_M1_SUREPAY_VOICE03.dmp
bill07:ADMP:/csa074/oradata/surepayimp> impdp parfile=impdp_M1_SUREPAY_VOICE03.par^C
bill07:ADMP:/csa074/oradata/surepayimp> cat impdp_M1_SUREPAY_NONVOICE03.log
;;;
Import: Release 10.2.0.2.0 - 64bit Production on Monday, 19 April, 2010 15:54:17

Copyright (c) 2003, 2005, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYS"."IMPDP_M1_SUREPAY_NONVOICE03" successfully loaded/unloaded
Starting "SYS"."IMPDP_M1_SUREPAY_NONVOICE03":  parfile=impdp_M1_SUREPAY_NONVOICE03.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBAM1"."M1_SUREPAY_NONVOICE03"             12.60 GB 41019272 rows
Job "SYS"."IMPDP_M1_SUREPAY_NONVOICE03" successfully completed at 16:25:32


bill07:ADMP:/csa074/oradata/surepayimp> impdp parfile=impdp_M1_SUREPAY_VOICE03.par

Import: Release 10.2.0.2.0 - 64bit Production on Monday, 19 April, 2010 16:53:35

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYS"."IMPDP_M1_SUREPAY_VOICE03" successfully loaded/unloaded
Starting "SYS"."IMPDP_M1_SUREPAY_VOICE03":  parfile=impdp_M1_SUREPAY_VOICE03.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DBAM1"."M1_SUREPAY_VOICE03"                18.19 GB 57052084 rows
Job "SYS"."IMPDP_M1_SUREPAY_VOICE03" successfully completed at 17:41:12

11g sqlplus doesn't hide password

Prior to 10g Oracle database, anyone can see the database account password using 'ps -ef |grep sqlplus ' , if  it is hard coded on same command line with sqlplus, e.g sqlplus  username/password@DBNAME

In 10g , Oracle "solved" this problem ,.

However, it comes back again in 11g. Searched metalink, no more document says password will be removed. :(

Too bad.  Lucky, we have alternative way.

> ps -ef |grep sqlplus
oravas11  4997  4748  0 13:41:31 pts/0     0:00 ied sqlplus perfstat/xxxxxxxx@VAS11GP
oravas11  4998  4997  0 13:41:31 ttyp1     0:00 sqlplus perfstat/xxxxxxxx@VAS11GP

two cases of missing indexes

Case 1:

SQL> select status, count(*) from dbaantispam.tb_sms_out group by status;

    STATUS   COUNT(*)
---------- ----------
         1     112459


SQL> alter session set current_schema=dbaantispam;

Session altered.

SQL> set autotrace on
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report

cd  $ORACLE_HOME/rdbms/admin/

SQL> @utlxplan

Table created.

SQL> set autotrace on

SQL> conn /
Connected.
SQL> set autotrace on


SQL>  SELECT sms_out_id, text, msisdn, messagetype FROM dbaantispam.tb_sms_out WHERE status = 0 ORDER BY sms_out_id ASC;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TB_SMS_OUT'
   2    1     INDEX (FULL SCAN) OF 'SMS_OUT_ID_PK' (UNIQUE)




Statistics
----------------------------------------------------------
        248  recursive calls
          0  db block gets
      31466  consistent gets
       3072  physical reads
          0  redo size
        467  bytes sent via SQL*Net to client
        461  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

As you can see there are lot of  consistent reads and physical reads .

SQL> @chk_indcol
Enter value for tbl: TB_SMS_OUT
old   1: select index_name,column_name,column_position from dba_ind_columns where table_name=upper('&tbl')
new   1: select index_name,column_name,column_position from dba_ind_columns where table_name=upper('TB_SMS_OUT')

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
SMS_OUT_ID_PK                  SMS_OUT_ID                         1

-- No index found on STATUS column
SQL> create index dbaantispam.tb_sms_out_statusidx on tb_sms_out(status) tablespace antispam_idx;

Index created.



SQL>  SELECT sms_out_id, text, msisdn, messagetype FROM dbaantispam.tb_sms_out WHERE status = 0 ORDER BY sms_out_id ASC;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_SMS_OUT'
   3    2       INDEX (RANGE SCAN) OF 'TB_SMS_OUT_STATUSIDX' (NON-UNIQ
          UE)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads
          0  redo size
        467  bytes sent via SQL*Net to client
        461  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

2 consistent reads and 1 physical reads only.



Case 2 :


alter session set current_schema=dbamsgctr;


SQL> set timing on
SQL> set pages 1000
SQL> SELECT '11:00-11:59', COUNT(destination_type) FROM dbamsgctr.tb_msg_transaction
  2   WHERE destination_type = 0 AND msg_type LIKE '%SMS%'
  3   AND transaction_timestamp >= TO_DATE('2010-03-16 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
  4   AND transaction_timestamp <= TO_DATE('2010-03-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss');


'11:00-11:5 COUNT(DESTINATION_TYPE)
----------- -----------------------
11:00-11:59                       0

Elapsed: 00:07:21.94

Execution Plan
----------------------------------------------------------




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     380503  consistent gets
     380195  physical reads
          0  redo size
        586  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Huge amounts of logical reads, because of this statement, during 3 hours ,  the "Buffer Cache Reads" is 278.4G

SQL> set timing on pages 1000
SQL> create index msg_idx2  on tb_msg_transaction(transaction_timestamp) tablespace msgctr_idx nologging parallel (degree 3 );


Index created.

Elapsed: 00:14:55.86

alter index msg_idx2 logging noparallel;



SQL> set autotrace on
SQL> SELECT '11:00-11:59', COUNT(destination_type) FROM dbamsgctr.tb_msg_transaction
  2   WHERE destination_type = 0 AND msg_type LIKE '%SMS%'
  3   AND transaction_timestamp >= TO_DATE('2010-03-16 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
  4   AND transaction_timestamp <= TO_DATE('2010-03-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss');

'11:00-11:5 COUNT(DESTINATION_TYPE)
----------- -----------------------
11:00-11:59                       0

Elapsed: 00:00:02.81

Execution Plan
----------------------------------------------------------




Statistics
----------------------------------------------------------
        164  recursive calls
          0  db block gets
         28  consistent gets
          4  physical reads
          0  redo size
        586  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Tremendous improvement !      minutes of run becomes 3 seconds !!! 
Again , housekeeping and Oracle developer understands  how Oracle database works is important.

blog templated widened

Finally I widened this blog's layout , which is using template rounders 4, leave those .gif picture unchanged. (Lazy to download it from blogblog.com and customize them and  upload to 3rd party web ).

Main time is spent on removing the post's right sidebar -- looks like a vertical line.  Hence, searching on keyword "width" throughout the xml file.   Finally identify it a GIF picture  from   http://www.blogblog.com/rounders4/rails_main.gif , as the "width" is fixed  inside a picture.

My solution is to comment it .

/*  background:url("http://www.blogblog.com/rounders4/rails_main.gif") repeat-y $startSide; */



References:

http://www.bloggerguide.lk/2008/04/rounders-with-wider-post-area.html

How To Change The Width Of The Blogger Templates

http://blogger-and-money.blogspot.com/2007/06/how-to-change-width-of-blogger.html

Rounders 4 with a wider post area

http://www.bloggerguide.lk/2008/11/rounders-4-with-wider-post-area.html

Key Words: widen blogger.com template rounder4

Private db link

private link can be created/dropped by owner only, it can't be achieved by using below command.



alter session set current_schema= ;

cluttered gviw backup files

Below info is from   http://my.opera.com/peterchenadded/blog/2008/12/27/gvim-7-1-swap-and-backup-files, which puzzled me few years.

A swap file is a file that contains changes made to the original file. it matches *.swp.
A backup file is created right after saving changes to a newly opened file. it is a copy of the file before changing the file. it matches *~ where the * is the original filename.
So logically, by definition you can put them together to make the original or original plus some or all changes made.
source: http://www.ph.unimelb.edu.au/~ssk/vim/recover.html

Normally, both files appear in the directory of the file being edited. This can make the directory very cluttered, so would be better to remove it some where else. To do this use

set directory=c:/vim_swap_files
set backupdir=c:/vim_swap_files

You will need to create the vim_swap_files folder before it will work.
Add the lines to the c:\program files\vim\_vimrc file or in vim edit -> startup settings and add the lines and you will never have to worry about it again.

Human Error Preventives

Being a DBA or system administrator, simple ENTER may bring us into a nightmare. 

It is unavoidable, however what we can do is to prevent it from happening at  low probability as possible. 

Good habit should help us. 
*** This thread would be updated  on-going , any comments are welcome.

1. shell environment settings.
Today I notice below are mentioned in cygwin's .bashrc

 # Some example alias instructions
# If these are enabled they will be used instead of any instructions
# they may mask.  For example, alias rm='rm -i' will mask the rm
# application.  To override the alias instruction use a \ before, ie
# \rm will call the real rm not the alias.

# Interactive operation...
 alias rm='rm -i'
 alias cp='cp -i'
 alias mv='mv -i'

Good ! In real world practices, I'd like these options to be there.


To be continued  ....


Interesting to see a from from Amazon, menting Human Error in Medicine, however looks not any for IT industry yet.