Thinking Out Loud

March 21, 2013

Hard Coding 101

Filed under: oracle — mdinh @ 4:41 am

Just don’t do it!

I must be naive, but is there a reason prefix schema name for objects owned by the same owner?

There’s no harm if there is never a need to migrate the data from one schema to another. But it’s a night mare when there is.

Here’s a simple analogy. It’s like getting a tattoo of your first girl friend Jane and end up marrying  Jill. It’s a night mare.

Here is a test case

LAX:(MDINH@db01)> create or replace trigger mdinh.trig_t before insert on mdinh.t begin null; end;
  2  /

Trigger created.

LAX:(MDINH@db01)>
[oracle@lax:db01]/home/oracle
$ expdp logfile=t.log schemas=mdinh include=trigger dumpfile=t.dmp directory=DATA_PUMP_DIR

Export: Release 11.2.0.3.0 - Production on Wed Mar 20 21:27:10 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA logfile=t.log schemas=mdinh include=trigger dumpfile=t.dmp directory=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /tmp/t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:27:25

[oracle@lax:db01]/home/oracle
$ impdp schemas=mdinh remap_schema=mdinh:scott include=trigger sqlfile=trigger_ddl.sql dumpfile=t.dmp directory=DATA_PUMP_DIR

Import: Release 11.2.0.3.0 - Production on Wed Mar 20 21:29:22 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA schemas=mdinh remap_schema=mdinh:scott include=trigger sqlfile=trigger_ddl.sql dumpfile=t.dmp directory=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:29:27

[oracle@lax:db01]/home/oracle
$ cat /tmp/trigger_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
-- CONNECT SCOTT
CREATE TRIGGER "SCOTT"."TRIG_T"  before insert on mdinh.t begin null; end;
/

ALTER TRIGGER "SCOTT"."TRIG_T" ENABLE;

ALTER TRIGGER "SCOTT"."TRIG_T"
  COMPILE
    PLSQL_OPTIMIZE_LEVEL=  2
    PLSQL_CODE_TYPE=  INTERPRETED    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE'
;
[oracle@lax:db01]/home/oracle
$
Advertisements

March 19, 2013

Solve the mystery – Database crash without a clue

Filed under: oracle — mdinh @ 4:14 am

So there I was, troubleshooting root cause for database crash without a clue.

There are no trace files and no core dumps. The alert log is shown below?

What do you think happened?

Mar 18 20:50:08 2013
Thread 1 advanced to log sequence 5 (LGWR switch)
  Current log# 2 seq# 5 mem# 0: /oracle/flashrecovery/LAX_DB01/onlinelog/o1_mf_2_8nctxzw9_.log
Mon Mar 18 20:50:09 2013
Archived Log entry 56 added for thread 1 sequence 4 ID 0x55ed081e dest 1:
Mon Mar 18 20:55:18 2013
Adjusting the default value of parameter parallel_max_servers
from 40 to 35 due to the value of parameter processes (50)
Starting ORACLE instance (normal)
Mon Mar 18 20:55:27 2013
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 49 KB redo, 38 data blocks need recovery
Started redo application at
 Thread 1: logseq 4, block 3
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
  Mem# 0: /oracle/flashrecovery/LAX_DB01/onlinelog/o1_mf_1_8nctxrsl_.log
Recovery of Online Redo Log: Thread 1 Group 2 Seq 5 Reading mem 0
  Mem# 0: /oracle/flashrecovery/LAX_DB01/onlinelog/o1_mf_2_8nctxzw9_.log
Completed redo application of 0.04MB
Completed crash recovery at
 Thread 1: logseq 5, block 3, scn 642127
 38 data blocks read, 38 data blocks written, 49 redo k-bytes read
LGWR: STARTING ARCH PROCESSES

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[oracle@lax:db01]/u01/app/oracle/diag/rdbms/lax_db01/db01/trace
$ last reboot|head -1
reboot system boot 2.6.39-200.24.1. Mon Mar 18 20:53 – 21:12 (00:19)

The system was rebooted !!!

March 17, 2013

Oracle Move objects to another tablespace

Filed under: oracle — mdinh @ 4:46 am

Move objects to another tablespace for tables, indexes, lobs, iots.

Table with LONG datatype can only be moved using export and import.

Note:
DBA_INDEXES – IOT1 – organization index tablespace users overflow tablespace ndat
DBA_TABLES    – IOT2 – organization index tablespace ndat overflow tablespace users

Example below is to move all objects out of USERS tablespace.

LAX:(MDINH@db01)> @c
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table dropped.
LAX:(MDINH@db01)> create table longt (id number, name long, junk raw(16)) tablespace users;

Table created.

LAX:(MDINH@db01)> create table tab(id int, name varchar2(30)) tablespace users;

Table created.

LAX:(MDINH@db01)> create bitmap index bm on tab(id);

Index created.

LAX:(MDINH@db01)> create table iot1(id int, name varchar2(30), constraint pk1 primary key (id))
  2  organization index tablespace users overflow tablespace ndat;

Table created.

LAX:(MDINH@db01)> create table iot2(id int, name varchar2(30), constraint pk2 primary key (id))
  2  organization index tablespace ndat overflow tablespace users;

Table created.

LAX:(MDINH@db01)> create table lob (id number, picture blob, resume clob)
  2  lob (picture) store as picturelobs (tablespace users index (tablespace users))
  3  lob (resume) store as (tablespace users index resumeidx (tablespace users));

Table created.

LAX:(MDINH@db01)> create index x on lob(id) tablespace users;

Index created.

LAX:(MDINH@db01)> create or replace procedure p( p_id in int, p_resume in varchar2 )
  2  as
  3  begin
  4  insert into lob(id,resume) values (p_id,p_resume);
  5  commit;
  6  end;
  7  /

Procedure created.

LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @o
LAX:(MDINH@db01)> set lines 130
LAX:(MDINH@db01)> col column_name for a30
LAX:(MDINH@db01)> col nm for a30
LAX:(MDINH@db01)> col tn for a30
LAX:(MDINH@db01)> set echo on
LAX:(MDINH@db01)> select owner||'.'||table_name nm, iot_name, iot_type from dba_tables where tablespace_name='USERS';

NM                             IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
MDINH.LONGT
MDINH.TAB
MDINH.SYS_IOT_OVER_15444       IOT2                           IOT_OVERFLOW
MDINH.LOB

LAX:(MDINH@db01)> select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS';

NM                             TN                             INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
MDINH.PK1                      MDINH.IOT1                     IOT - TOP
MDINH.RESUMEIDX                MDINH.LOB                      LOB
MDINH.SYS_IL0000015447C00002$$ MDINH.LOB                      LOB
MDINH.X                        MDINH.LOB                      NORMAL
MDINH.BM                       MDINH.TAB                      BITMAP

LAX:(MDINH@db01)> select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS';

NM                             COLUMN_NAME                    INDEX_NAME                     SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
MDINH.LOB                      RESUME                         RESUMEIDX                      SYS_LOB0000015447C00003$$
MDINH.LOB                      PICTURE                        SYS_IL0000015447C00002$$       PICTURELOBS

LAX:(MDINH@db01)> @m
LAX:(MDINH@db01)> set serverout on
LAX:(MDINH@db01)> -- MOVE LOB FIRST
LAX:(MDINH@db01)> declare
  2  l_sql varchar2(200);
  3  begin
  4  for x in (
  5  select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS'
  6  ) loop
  7  begin
  8  l_sql := 'alter table '||x.nm||' move lob('||x.column_name||') store as '||x.segment_name||' (tablespace nlob)';
  9  execute immediate l_sql;
 10  -- dbms_output.put_line(l_sql);
 11  exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
 12  end;
 13  end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> -- MOVE IOT FROM DBA_TABLES
LAX:(MDINH@db01)> declare
  2  l_sql varchar2(200);
  3  begin
  4  for x in (
  5  select owner||'.'||iot_name nm from dba_tables where tablespace_name='USERS' and iot_name is not null
  6  ) loop
  7  begin
  8  l_sql := 'alter table '||x.nm||' move tablespace ndat overflow tablespace ndat';
  9  execute immediate l_sql;
 10  -- dbms_output.put_line(l_sql);
 11  exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
 12  end;
 13  end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> -- MOVE IOT FROM DBA_INDEXES
LAX:(MDINH@db01)> declare
  2  l_sql varchar2(200);
  3  begin
  4  for x in (
  5  select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS' and index_type in ('IOT - TOP')
  6  ) loop
  7  begin
  8  l_sql := 'alter table '||x.tn||' move tablespace ndat overflow tablespace ndat';
  9  execute immediate l_sql;
 10  -- dbms_output.put_line(l_sql);
 11  exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
 12  end;
 13  end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> -- MOVE TABLES
LAX:(MDINH@db01)> declare
  2  l_sql varchar2(200);
  3  begin
  4  for x in (
  5  select owner||'.'||table_name nm from dba_tables where tablespace_name='USERS' and iot_name is null
  6  ) loop
  7  begin
  8  l_sql := 'alter table '||x.nm||' move tablespace ndat';
  9  execute immediate l_sql;
 10  -- dbms_output.put_line(l_sql);
 11  exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
 12  end;
 13  end loop;
 14  end;
 15  /
!!!!! ERROR: alter table MDINH.LONGT move tablespace ndat

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> -- MOVE INDEXES
LAX:(MDINH@db01)> declare
  2  l_sql varchar2(200);
  3  begin
  4  for x in (
  5  select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS' and index_type in ('NORMAL','BITMAP')
  6  ) loop
  7  begin
  8  l_sql := 'alter index '||x.nm||' rebuild tablespace ndat';
  9  execute immediate l_sql;
 10  -- dbms_output.put_line(l_sql);
 11  exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
 12  end;
 13  end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @@o.sql
LAX:(MDINH@db01)> set lines 130
LAX:(MDINH@db01)> col column_name for a30
LAX:(MDINH@db01)> col nm for a30
LAX:(MDINH@db01)> col tn for a30
LAX:(MDINH@db01)> set echo on
LAX:(MDINH@db01)> select owner||'.'||table_name nm, iot_name, iot_type from dba_tables where tablespace_name='USERS';

NM                             IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
MDINH.LONGT

LAX:(MDINH@db01)> select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS';

no rows selected

LAX:(MDINH@db01)> select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS';

no rows selected

LAX:(MDINH@db01)>

March 11, 2013

port forwarding with plink

Filed under: linux,oracle — mdinh @ 4:07 am
Plink (a command-line interface to the PuTTY back ends)
C:\Users\dinh>plink
PuTTY Link: command-line connection utility
0.62 x64 Built by Tad DeVries http://splunk.net
Usage: plink [options] [user@]host [command]
       ("host" can also be a PuTTY saved session name)
Options:
  -V        print version information and exit
  -pgpfp    print PGP key fingerprints and exit
  -v        show verbose messages
  -load sessname  Load settings from saved session
  -ssh -telnet -rlogin -raw -serial
            force use of a particular protocol
  -P port   connect to specified port
  -l user   connect with specified username
  -batch    disable all interactive prompts
The following options only apply to SSH connections:
  -pw passw login with specified password
  -D [listen-IP:]listen-port
            Dynamic SOCKS-based port forwarding
  -L [listen-IP:]listen-port:host:port
            Forward local port to remote address
  -R [listen-IP:]listen-port:host:port
            Forward remote port to local address
  -X -x     enable / disable X11 forwarding
  -A -a     enable / disable agent forwarding
  -t -T     enable / disable pty allocation
  -1 -2     force use of particular protocol version
  -4 -6     force use of IPv4 or IPv6
  -C        enable compression
  -i key    private key file for authentication
  -noagent  disable use of Pageant
  -agent    enable use of Pageant
  -m file   read remote command(s) from file
  -s        remote command is an SSH subsystem (SSH-2 only)
  -N        don't start a shell/command (SSH-2 only)
  -nc host:port
            open tunnel in place of session (SSH-2 only)
  -sercfg configuration-string (e.g. 19200,8,n,1,X)
            Specify the serial configuration (serial only)

Configuration:

Oracle RDBMS on Virtual Box using NAT(eth0) and Host Only Networking(eth1) listening on port 1531.

Windows7 Host:

Ethernet adapter VirtualBox Host-Only Network:

   Connection-specific DNS Suffix  . :
   Description . . . . . . . . . . . : VirtualBox Host-Only Ethernet Adapter
   Physical Address. . . . . . . . . : 08-00-27-00-8C-10
   DHCP Enabled. . . . . . . . . . . : Yes
   Autoconfiguration Enabled . . . . : Yes
   Link-local IPv6 Address . . . . . : fe80::84bc:48b3:8e40:e0d1%42(Preferred)
   Autoconfiguration IPv4 Address. . : 169.254.224.209(Preferred)
   Subnet Mask . . . . . . . . . . . : 255.255.0.0
   Default Gateway . . . . . . . . . :
   DHCPv6 IAID . . . . . . . . . . . : 638058535
   DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-18-87-4B-A4-D4-BE-D9-60-5A-3F
   DNS Servers . . . . . . . . . . . : fec0:0:0:ffff::1%1
                                       fec0:0:0:ffff::2%1
                                       fec0:0:0:ffff::3%1
   NetBIOS over Tcpip. . . . . . . . : Enabled

OEL 6.3 Guest:

[oracle@lax:db01]/home/oracle
$ ifconfig -a
eth0      Link encap:Ethernet  HWaddr 08:00:27:CC:FF:D7
          inet addr:10.0.2.15  Bcast:10.0.2.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fecc:ffd7/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:74 errors:0 dropped:0 overruns:0 frame:0
          TX packets:95 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:24907 (24.3 KiB)  TX bytes:9397 (9.1 KiB)

eth1      Link encap:Ethernet  HWaddr 08:00:27:2C:2E:24
          inet addr:169.254.224.210  Bcast:169.254.255.255  Mask:255.255.0.0
          inet6 addr: fe80::a00:27ff:fe2c:2e24/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1049 errors:0 dropped:0 overruns:0 frame:0
          TX packets:853 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:157877 (154.1 KiB)  TX bytes:150602 (147.0 KiB)

lo        Link encap:Local Loopback
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:2206 errors:0 dropped:0 overruns:0 frame:0
          TX packets:2206 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0
          RX bytes:331342 (323.5 KiB)  TX bytes:331342 (323.5 KiB)

LISTENER port 1531

[oracle@lax:db01]/home/oracle
$ lsnrctl status listener_db01

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-MAR-2013 20:29:52

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lax.localdomain)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_db01
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                10-MAR-2013 19:56:07
Uptime                    0 days 0 hr. 33 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lax)(PORT=1531)))
Services Summary...
Service "lax_db01" has 2 instance(s).
  Instance "db01", status UNKNOWN, has 1 handler(s) for this service...
  Instance "db01", status READY, has 1 handler(s) for this service...
The command completed successfully

Port forwarding – connecting to server using IP address: 169.254.224.210

C:\Users\dinh>plink -batch -v -x -a -T -C -noagent -ssh -L 127.0.0.1:1521:169.254.224.210:1531 oracle@169.254.224.210 -pw oracle
Looking up host "169.254.224.210"
Connecting to 169.254.224.210 port 22
Server version: SSH-2.0-OpenSSH_5.3
Using SSH protocol version 2
We claim version: SSH-2.0-PuTTY_Local:_Dec_15_2011_21:59:21
Server supports delayed compression; will try this later
Doing Diffie-Hellman group exchange
Doing Diffie-Hellman key exchange with hash SHA-256
Host key fingerprint is:
ssh-rsa 2048 0f:4f:4c:21:99:0a:41:f3:bf:28:8c:e2:c9:1c:45:f6
Initialised AES-256 SDCTR client->server encryption
Initialised HMAC-SHA1 client->server MAC algorithm
Initialised AES-256 SDCTR server->client encryption
Initialised HMAC-SHA1 server->client MAC algorithm
Using username "oracle".
Using SSPI from SECUR32.DLL
Attempting GSSAPI authentication
GSSAPI authentication request refused
Sent password
Access granted
Initiating key re-exchange (enabling delayed compression)
Doing Diffie-Hellman group exchange
Doing Diffie-Hellman key exchange with hash SHA-256
Initialised AES-256 SDCTR client->server encryption
Initialised HMAC-SHA1 client->server MAC algorithm
Initialised zlib (RFC1950) compression
Initialised AES-256 SDCTR server->client encryption
Initialised HMAC-SHA1 server->client MAC algorithm
Initialised zlib (RFC1950) decompression
Opened channel for session
Local port 127.0.0.1:1521 forwarding to 169.254.224.210:1531
Started a shell/command
Opening forwarded connection to 169.254.224.210:1531
Nothing left to send, closing channel
Forwarded port closed
Opening forwarded connection to 169.254.224.210:1531
Nothing left to send, closing channel
Forwarded port closed
^C

Port forwarding – connecting to server using PuTTY Saved Session: vm-lax_db01

Note: the IP address of the server used to forward port still needs to be listed unless the IP is added to hosts file.

C:\Users\dinh>plink -v -x -a -T -C -noagent -ssh -L 127.0.0.1:1521:169.254.224.210:1531 oracle@vm-lax_db01 -pw oracle
Looking up host "169.254.224.210"
Connecting to 169.254.224.210 port 22
Server version: SSH-2.0-OpenSSH_5.3
Using SSH protocol version 2
We claim version: SSH-2.0-PuTTY_Local:_Dec_15_2011_21:59:21
Server supports delayed compression; will try this later
Doing Diffie-Hellman group exchange
Doing Diffie-Hellman key exchange with hash SHA-256
Host key fingerprint is:
ssh-rsa 2048 0f:4f:4c:21:99:0a:41:f3:bf:28:8c:e2:c9:1c:45:f6
Initialised AES-256 SDCTR client->server encryption
Initialised HMAC-SHA1 client->server MAC algorithm
Initialised AES-256 SDCTR server->client encryption
Initialised HMAC-SHA1 server->client MAC algorithm
Using username "oracle".
Using SSPI from SECUR32.DLL
Attempting GSSAPI authentication
GSSAPI authentication request refused
Sent password
Access granted
Initiating key re-exchange (enabling delayed compression)
Doing Diffie-Hellman group exchange
Doing Diffie-Hellman key exchange with hash SHA-256
Initialised AES-256 SDCTR client->server encryption
Initialised HMAC-SHA1 client->server MAC algorithm
Initialised zlib (RFC1950) compression
Initialised AES-256 SDCTR server->client encryption
Initialised HMAC-SHA1 server->client MAC algorithm
Initialised zlib (RFC1950) decompression
Opened channel for session
Local port 127.0.0.1:1521 forwarding to 169.254.224.210:1531
Started a shell/command
Opening forwarded connection to 169.254.224.210:1531
Nothing left to send, closing channel
Forwarded port closed

Connect using SQL*Plus

C:\Users\dinh>sqlplus mdinh/mdinh@127.0.0.1:1521/lax_db01

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 11 00:00:14 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
db01

SQL> select db_unique_name from v$database;

DB_UNIQUE_NAME
------------------------------
lax_db01

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

March 10, 2013

Linux Tips and Tricks

Filed under: linux — mdinh @ 4:23 am

find with regex

Version tested:

$ uname -a
Linux lax.localdomain 2.6.39-200.24.1.el6uek.x86_64 #1 SMP Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

Find 1 directory below admin, directories adump, bdump, cdump, udump
File extensions: .aud, .trc, .trm
File size: zero byte
File name: sbtio.log
Print files deleted older that 1 day.

find /u01/app/oracle/admin/*/[abcu]dump -type f \( -name “*\.aud” -o -name “*\.tr[cm]” -o -size 0c -o -name “sbtio.log” \) -mtime +1 -print -exec rm {} \;

Example:

[oracle@lax:db01]/home/oracle
$ find /u01/app/oracle/admin/*/[abcu]dump -type f \( -name "*\.aud" -o -name "*\.tr[cm]" -o -size 0c -o -name "sbtio.log" \) -mtime +1 -print -exec rm {} \;
/u01/app/oracle/admin/db01/adump/db01_ora_1927_1.aud
/u01/app/oracle/admin/db01/adump/db01_ora_1937_1.aud
/u01/app/oracle/admin/db01/adump/db01_ora_1960_1.aud
/u01/app/oracle/admin/db01/adump/db01_ora_1987_1.aud
/u01/app/oracle/admin/db01/bdump/test.trc
/u01/app/oracle/admin/db01/bdump/t.1
/u01/app/oracle/admin/db01/udump/my.trc
/u01/app/oracle/admin/db01/udump/t.2

Find 2 directories below rdbms, directory trace

find /u01/app/oracle/diag/rdbms/*/*/trace -type f \( -name “*\.tr[cm]” -o -size 0c -o -name “sbtio.log” \) -mtime +1 -print -exec rm {} \;

Example:

[oracle@lax:db01]/home/oracle
$ find /u01/app/oracle/diag/rdbms/*/*/trace -type f \( -name "*\.tr[cm]" -o -size 0c -o -name "sbtio.log" \) -mtime +1 -print -exec rm {} \;
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_vkrm_2033.trc
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_j001_2313.trm
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_arc0_2028.trm
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_vkrm_2305.trm
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ckpt_2137.trc
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_dm00_2223.trc
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_dbrm_1942.trm

Find and remove directory cdmp*, core*

find /u01/app/oracle/admin/*/[bc]dump -type d \( -name “cdmp*” -o -name “core*” \) -mtime +1 -print -exec rm -rf {} \;

Example:

[oracle@lax:db01]/home/oracle
$ find /u01/app/oracle/admin/*/[bc]dump -type d \( -name "cdmp*" -o -name "core*" \) -mtime +1 -print -exec rm -rf {} \;
/u01/app/oracle/admin/db01/cdump/cdmp
find: `/u01/app/oracle/admin/db01/cdump/cdmp': No such file or directory
/u01/app/oracle/admin/db01/cdump/core1
find: `/u01/app/oracle/admin/db01/cdump/core1': No such file or directory

Find and delete files NOT new than the provided file
find . \( ! -newer db01_ora_1937_2.aud \) -print -exec rm {} \;

Example:

[oracle@lax:db01]/u01/app/oracle/admin/adump
$ ls -alrt
total 376
-rw-r----- 1 oracle oinstall  763 Feb 27 22:26 db01_ora_6746_2.aud
-rw-r----- 1 oracle oinstall  763 Feb 27 22:30 db01_ora_6851_2.aud
-rw-r----- 1 oracle oinstall  769 Feb 27 22:30 db01_ora_6898_1.aud
-rw-r----- 1 oracle oinstall  970 Feb 28 07:09 db01_ora_6918_1.aud
-rw-r----- 1 oracle oinstall  765 Feb 28 07:11 db01_ora_31831_2.aud
-rw-r----- 1 oracle oinstall  771 Feb 28 07:11 db01_ora_31865_1.aud
-rw-r----- 1 oracle oinstall  775 Feb 28 07:11 db01_ora_31870_1.aud
-rw-r----- 1 oracle oinstall  763 Feb 28 09:44 db01_ora_1950_2.aud
-rw-r----- 1 oracle oinstall  769 Feb 28 09:44 db01_ora_1984_1.aud
-rw-r----- 1 oracle oinstall  773 Feb 28 09:44 db01_ora_1989_1.aud
drwxr-xr-x 4 oracle oinstall 4096 Feb 28 09:52 ..
-rw-r----- 1 oracle oinstall  763 Mar  2 22:11 db01_ora_1976_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  2 22:11 db01_ora_2010_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:11 db01_ora_2015_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2078_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2079_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2080_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2081_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2082_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2083_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2088_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:22 db01_ora_2089_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:24 db01_ora_2090_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:24 db01_ora_2091_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:24 db01_ora_2092_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2097_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2098_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2099_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2100_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2101_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2102_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2103_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2104_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2105_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2106_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:26 db01_ora_2107_1.aud
-rw-r----- 1 oracle oinstall  970 Mar  2 22:27 db01_ora_2111_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  2 22:28 db01_ora_2112_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  2 22:28 db01_ora_2146_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2152_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2180_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2181_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2182_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2183_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2184_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2185_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2190_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2191_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2192_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2193_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  2 22:28 db01_ora_2194_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  4 07:15 db01_ora_1987_2.aud
-rw-r----- 1 oracle oinstall  768 Mar  4 07:15 db01_ora_2021_1.aud
-rw-r----- 1 oracle oinstall  968 Mar  4 07:16 db01_ora_2026_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  4 20:27 db01_ora_1927_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  4 20:27 db01_ora_1961_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  4 20:27 db01_ora_1966_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  4 20:29 db01_ora_2010_2.aud
-rw-r----- 1 oracle oinstall  970 Mar  4 22:37 db01_ora_2445_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  5 22:01 db01_ora_1960_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  5 22:01 db01_ora_1994_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  5 22:01 db01_ora_1999_1.aud
-rw-r----- 1 oracle oinstall  970 Mar  5 23:12 db01_ora_2160_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  6 06:28 db01_ora_1937_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  6 06:28 db01_ora_1971_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:28 db01_ora_1976_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:29 db01_ora_2022_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:31 db01_ora_2046_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:33 db01_ora_2063_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:35 db01_ora_2071_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:35 db01_ora_2073_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:45 db01_ora_2103_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:45 db01_ora_2111_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:46 db01_ora_2120_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:48 db01_ora_2132_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:52 db01_ora_2165_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:59 db01_ora_2189_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 07:00 db01_ora_2194_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 07:01 db01_ora_2219_1.aud
-rw-r----- 1 oracle oinstall  970 Mar  6 07:31 db01_ora_2260_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  7 20:48 db01_ora_1956_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  7 20:48 db01_ora_1990_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:48 db01_ora_1995_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:51 db01_ora_2045_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:53 db01_ora_2058_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:54 db01_ora_2067_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:56 db01_ora_2081_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:57 db01_ora_2094_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:59 db01_ora_2112_1.aud
-rw-r----- 1 oracle oinstall  970 Mar  7 21:45 db01_ora_2546_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  9 19:32 db01_ora_2353_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  9 19:32 db01_ora_2387_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  9 19:32 db01_ora_2392_1.aud
drwxr-xr-x 2 oracle oinstall 4096 Mar  9 19:32 .
[oracle@lax:db01]/u01/app/oracle/admin/adump
$ find . \( ! -newer db01_ora_1937_2.aud \) -print -exec rm {} \;
./db01_ora_2091_1.aud
./db01_ora_2160_1.aud
./db01_ora_31865_1.aud
./db01_ora_2015_1.aud
./db01_ora_2097_1.aud
./db01_ora_2180_1.aud
./db01_ora_2107_1.aud
./db01_ora_2191_1.aud
./db01_ora_6918_1.aud
./db01_ora_1976_2.aud
./db01_ora_2194_1.aud
./db01_ora_2090_1.aud
./db01_ora_2081_1.aud
./db01_ora_2192_1.aud
./db01_ora_2079_1.aud
./db01_ora_2152_1.aud
./db01_ora_2083_1.aud
./db01_ora_2182_1.aud
./db01_ora_2105_1.aud
./db01_ora_2104_1.aud
./db01_ora_1994_1.aud
./db01_ora_2181_1.aud
./db01_ora_1927_2.aud
./db01_ora_6851_2.aud
./db01_ora_2102_1.aud
./db01_ora_6746_2.aud
./db01_ora_2078_1.aud
./db01_ora_1960_2.aud
./db01_ora_1937_2.aud
./db01_ora_1989_1.aud
./db01_ora_2193_1.aud
./db01_ora_2082_1.aud
./db01_ora_2146_1.aud
./db01_ora_2183_1.aud
./db01_ora_2101_1.aud
./db01_ora_2184_1.aud
./db01_ora_2111_1.aud
./db01_ora_1984_1.aud
./db01_ora_1987_2.aud
./db01_ora_2112_2.aud
./db01_ora_31831_2.aud
./db01_ora_2021_1.aud
./db01_ora_2106_1.aud
./db01_ora_1966_1.aud
./db01_ora_2010_1.aud
./db01_ora_2103_1.aud
./db01_ora_2190_1.aud
./db01_ora_2089_1.aud
./db01_ora_2098_1.aud
./db01_ora_2088_1.aud
./db01_ora_2099_1.aud
./db01_ora_2080_1.aud
./db01_ora_31870_1.aud
./db01_ora_2185_1.aud
./db01_ora_2010_2.aud
./db01_ora_2092_1.aud
./db01_ora_2026_1.aud
./db01_ora_1950_2.aud
./db01_ora_6898_1.aud
./db01_ora_2100_1.aud
./db01_ora_1999_1.aud
./db01_ora_2445_1.aud
./db01_ora_1961_1.aud
[oracle@lax:db01]/u01/app/oracle/admin/adump
$ ls -alrt
total 124
drwxr-xr-x 4 oracle oinstall 4096 Feb 28 09:52 ..
-rw-r----- 1 oracle oinstall  769 Mar  6 06:28 db01_ora_1971_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:28 db01_ora_1976_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:29 db01_ora_2022_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:31 db01_ora_2046_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:33 db01_ora_2063_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:35 db01_ora_2071_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:35 db01_ora_2073_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:45 db01_ora_2103_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:45 db01_ora_2111_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:46 db01_ora_2120_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:48 db01_ora_2132_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:52 db01_ora_2165_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 06:59 db01_ora_2189_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 07:00 db01_ora_2194_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  6 07:01 db01_ora_2219_1.aud
-rw-r----- 1 oracle oinstall  970 Mar  6 07:31 db01_ora_2260_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  7 20:48 db01_ora_1956_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  7 20:48 db01_ora_1990_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:48 db01_ora_1995_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:51 db01_ora_2045_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:53 db01_ora_2058_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:54 db01_ora_2067_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:56 db01_ora_2081_2.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:57 db01_ora_2094_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  7 20:59 db01_ora_2112_1.aud
-rw-r----- 1 oracle oinstall  970 Mar  7 21:45 db01_ora_2546_1.aud
-rw-r----- 1 oracle oinstall  763 Mar  9 19:32 db01_ora_2353_2.aud
-rw-r----- 1 oracle oinstall  769 Mar  9 19:32 db01_ora_2387_1.aud
-rw-r----- 1 oracle oinstall  773 Mar  9 19:32 db01_ora_2392_1.aud
drwxr-xr-x 2 oracle oinstall 4096 Mar  9 19:33 .

March 6, 2013

Resetting Sequences

Filed under: 11g,oracle — mdinh @ 7:09 am

I know what you are thinking. Why on earth would you reset a sequence? Let me tell you why.

Schema A ‘s tables are being refreshed from production to development database.

Schema A contains the data and references sequences in Schema B.

From the scheme for things, sequence > max(value) from table or sequence < max(value) from table.

max(value) from table:

LAX:(MDINH@db01)> select max(id) from t;
100

sequence > max(value):

LAX:(MDINH@db01)> select s.nextval from dual;
1978979888

Sequence reset:

LAX:(MDINH@db01)> DECLARE
  l_seq NUMBER;
  2    3  BEGIN
  4    FOR x IN(
  5      SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
  6    ) LOOP
  7      EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
  8      SELECT s.nextval INTO l_seq FROM dual;
  9      EXECUTE immediate 'alter sequence s increment by 1';
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> select s.nextval from dual;
       101

sequence < max(value):

LAX:(MDINH@db01)> drop sequence s;

Sequence dropped.

LAX:(MDINH@db01)> create sequence s;

Sequence created.

LAX:(MDINH@db01)> select s.nextval from dual;
         1

Sequence reset:

LAX:(MDINH@db01)> DECLARE
  2    l_seq NUMBER;
  3  BEGIN
  4    FOR x IN(
  5      SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
  6    ) LOOP
  7      EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
  8      SELECT s.nextval INTO l_seq FROM dual;
  9      EXECUTE immediate 'alter sequence s increment by 1';
 10    END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> select s.nextval from dual;
       101

LAX:(MDINH@db01)>

Happy Resetting!

Blog at WordPress.com.