Michael Dinh

Subscribe to Michael Dinh feed Michael Dinh
Michael T. Dinh, Oracle DBA
Updated: 6 hours 55 min ago

rlwrap – there’s a rpm for that

Thu, 2016-08-18 23:09

Recently, there has been discussion about using rlwrap for Goldengate on Twitter feed.

Truthfully, I did not know there was RPM for this and I did not even know it was already installed.

It just worked, when I had set it up.

A very nice cheat sheet for using yum.

Yum Command Cheat Sheet for Red Hat Enterprise Linux

I started to investigate how was rlwrap installed on the system.

OS Version.
$ cat /etc/issue
Oracle Linux Server release 6.6
Kernel \r on an \m

$ cat /etc/oracle-release
Oracle Linux Server release 6.6
Where’s rlwrap?
$ which rlwrap
/usr/bin/rlwrap
What’s rlwrap RPM?
$ yum provides rlwrap
Loaded plugins: refresh-packagekit, security
rlwrap-0.42-1.el6.x86_64 : Wrapper for GNU readline
Repo        : epel
Matched from:

rlwrap-0.42-1.el6.x86_64 : Wrapper for GNU readline
Repo        : installed
Matched from:
Other       : Provides-match: rlwrap
What’s rlwrap dependencies?
$ yum deplist rlwrap
Loaded plugins: refresh-packagekit, security
Finding dependencies:
package: rlwrap.x86_64 0.42-1.el6
...
Output omitted from brevity
Configure alias using rlwrap.
$ alias sqlplus
alias sqlplus='rlwrap sqlplus'

$ alias ggsci
alias ggsci='rlwrap ./ggsci'
Using rpm commands.
$ rpm -q --whatprovides rlwrap
rlwrap-0.42-1.el6.x86_64

$ rpm -q --requires rlwrap
/usr/bin/env
libc.so.6()(64bit)
libc.so.6(GLIBC_2.11)(64bit)
libc.so.6(GLIBC_2.2.5)(64bit)
libc.so.6(GLIBC_2.3)(64bit)
libc.so.6(GLIBC_2.3.4)(64bit)
libc.so.6(GLIBC_2.4)(64bit)
libreadline.so.6()(64bit)
libtinfo.so.5()(64bit)
libutil.so.1()(64bit)
libutil.so.1(GLIBC_2.2.5)(64bit)
perl >= 0:5.006
perl(AutoLoader)
perl(Carp)
perl(Config)
perl(Data::Dumper)
perl(Exporter)
perl(Getopt::Std)
perl(POSIX)
perl(RlwrapFilter)
perl(constant)
perl(lib)
perl(strict)
perl(vars)
rpmlib(CompressedFileNames) <= 3.0.4-1
rpmlib(FileDigests) <= 4.6.0-1
rpmlib(PayloadFilesHavePrefix) <= 4.0-1
rpmlib(VersionedDependencies) <= 3.0.3-1
rtld(GNU_HASH)
rpmlib(PayloadIsXz) <= 5.2-1

Configuring Multiple local_listener

Thu, 2016-08-18 00:16

I was working on configuring multiple local listeners and having difficulties setting local_listener using full address list.

How would you set up local_listeners?

Demo: There are 4 listeners, 1-4.
$ lsnrctl status listener1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:08
Uptime                    0 days 0 hr. 0 min. 22 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1551)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener2

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1552)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:10
Uptime                    0 days 0 hr. 0 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1552)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1552)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener3

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:35

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1553)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:13
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1553)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1553)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener4

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:34:36

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1554)))
STATUS of the LISTENER
------------------------
Alias                     listener4
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:15
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener4/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1554)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1554)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Modify database LOCAL_LISTENER parameter.
alter system set LOCAL_LISTENER="(ADDRESS_LIST=
)";
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1553))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1554))
  6  )";
alter system set LOCAL_LISTENER="(ADDRESS_LIST=
                                *
ERROR at line 1:
ORA-00972: identifier is too long


alter system set LOCAL_LISTENER="(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1553))
  5  )";
alter system set LOCAL_LISTENER="(ADDRESS_LIST=
                                *
ERROR at line 1:
ORA-00972: identifier is too long


alter system set LOCAL_LISTENER="(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
  4  )";

System altered.

ARROW:(SYS@leo):PRIMARY> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS_LIST=
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1551))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1552))
                                                 )
ARROW:(SYS@leo):PRIMARY>
Using single quote works.
ARROW:(SYS@leo):PRIMARY>
alter system set LOCAL_LISTENER='(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1551))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1552))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1553))
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT=1554)))'
  6  ;

System altered.

ARROW:(SYS@leo):PRIMARY> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS_LIST=
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1551))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1552))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1553))
                                                 (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.56.11)(PORT=1554)))
ARROW:(SYS@leo):PRIMARY>

Interesting.
$ oerr ora 972
00972, 00000, "identifier is too long"
// *Cause:  An identifier with more than 30 characters was specified.
// *Action:  Specify at most 30 characters.
Configure tnsnames for listeners
$ cat tnsnames.ora
leo =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1531))
    )
    (CONNECT_DATA =
      (SID = leo)
    )
  )

LISTENER_1551_1554 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1551))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1552))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1553))
      (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1554))
    )
  )

$ tnsping LISTENER_1551_1554

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:48:43

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1551)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1552)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1553)) (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.11)(PORT= 1554))))
OK (0 msec)
Modify database LOCAL_LISTENER parameter using tnsnames and register listener.
ARROW:(SYS@leo):PRIMARY> alter system set LOCAL_LISTENER="LISTENER_1551_1554";

System altered.

ARROW:(SYS@leo):PRIMARY> alter system register;

System altered.

ARROW:(SYS@leo):PRIMARY> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_1551_1554
ARROW:(SYS@leo):PRIMARY>
Service is now ready.
$ lsnrctl status listener1

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:08
Uptime                    0 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1551)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener2

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1552)))
STATUS of the LISTENER
------------------------
Alias                     listener2
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:10
Uptime                    0 days 0 hr. 17 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1552)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1552)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener3

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:31

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1553)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:13
Uptime                    0 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1553)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1553)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

$ lsnrctl status listener4

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2016 21:51:33

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1554)))
STATUS of the LISTENER
------------------------
Alias                     listener4
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                17-AUG-2016 21:34:15
Uptime                    0 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/arrow/listener4/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1554)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.11)(PORT=1554)))
Services Summary...
Service "leo" has 1 instance(s).
  Instance "leo", status UNKNOWN, has 1 handler(s) for this service...
Service "leo_a" has 1 instance(s).
  Instance "leo01", status READY, has 1 handler(s) for this service...
The command completed successfully

Quick note on using nc (netcat)

Wed, 2016-08-17 07:43

Due to security hardening, telnet is not available.

$ rpm -q --whatprovides nc
nc-1.84-24.el6.x86_64

$ yum list nc
Loaded plugins: refresh-packagekit, security
epel/metalink                                                                                                           |  11 kB     00:00
epel                                                                                                                    | 4.3 kB     00:00
epel/primary_db                                                                                                         | 5.9 MB     00:03
public_ol6_UEKR3_latest                                                                                                 | 1.2 kB     00:00
public_ol6_latest                                                                                                       | 1.4 kB     00:00
Installed Packages
nc.x86_64                                                    1.84-24.el6                                                     @public_ol6_latest

$ nc -v -z -w 3 stackoverflow.com 80; echo $?
Connection to stackoverflow.com 80 port [tcp/http] succeeded!
0

Note to self for blocking locks

Sat, 2016-08-06 05:20
Session 1 starts UPDATE and nothing else.
++++++++++
Session 1:
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;

1 row updated.

02:53:45 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Session 2:
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
++++++++++
Session 3:
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;
++++++++++
Monitor blocking locks
$ sysdba @b.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 02:55:03 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947                        57 SQL*Net message from INACTIVE    1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           6827jhnufmcfx update t set object_id=100
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
              6827jhnufmcfx

ARROW:(SYS@leo):PRIMARY>
Blocking session is INACTIVE and the UPDATE SQL is available.

.

Session 1 execute SELECT following UPDATE.
++++++++++
Session 1:
02:56:16 ARROW:(MDINH@leo):PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2016-08-06 02:56:23

02:56:23 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Monitor blocking locks
ARROW:(SYS@leo):PRIMARY> @b

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947      7h35uxf5uhmm1     61 SQL*Net message from INACTIVE    0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           7h35uxf5uhmm1 select sysdate from dual
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
7h35uxf5uhmm1 7h35uxf5uhmm1

ARROW:(SYS@leo):PRIMARY>
Note: SQL_ID=PREV_SQL_ID and SQL is not the blocking SQL.
ARROW:(SYS@leo):PRIMARY> !cat b.sql
col username for a15 trunc
col state for a10 trunc
col osuser for a10 trunc
col program for a15 trunc
col sid_serial for a12 trunc
col event for a20 trunc
col machine for a20 trunc
col sid for 999999
col wait_min for 999
col sql_text for a100 trunc
col seq# for 99999
col min for 999
col sql_text for a80 trunc
set lines 200 pages 10000 tab off trimspool off
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
s.sid||','||s.serial# sid_serial,
sql_id,
s.seq#,
s.event,
s.status,
round(s.last_call_et/60) min,
s.machine,
s.osuser,
s.program
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
order by 1,2
;
---
with s as (
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
decode(status,'INACTIVE',prev_sql_id,sql_id) sql_id
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
)
SELECT s.state, s.username, s.sql_id, sql_text
FROM v$sqlarea a, s
WHERE a.sql_id=s.sql_id
order by 1,2
;

ARROW:(SYS@leo):PRIMARY>
Nice Script from Jeffrey M. Hunter

http://www.idevelopment.info/data/Oracle/DBA_scripts/Locks/locks_blocking.sql

$ sysdba @locks_blocking.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 03:12:55 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


+------------------------------------------------------------------------+
| Report   : Blocking Locks                                              |
| Instance : leo                                                         |
+------------------------------------------------------------------------+


+------------------------------------------------------------------------+
| BLOCKING LOCKS (Summary)                                               |
+------------------------------------------------------------------------+

Number of blocking lock incidents: 2

Incident 1
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 27                                       22
Serial#               : 487                                      1947
Oracle User           : DEMO                                     MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20525                                    20521
Terminal              : pts/3                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=200

Incident 2
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 35                                       22
Serial#               : 795                                      1947
Oracle User           : SYSTEM                                   MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20706                                    20521
Terminal              : pts/7                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=2


+------------------------------------------------------------------------+
| LOCKED OBJECTS                                                         |
+------------------------------------------------------------------------+

Instance  SID / Serial#   Status    Locking Oracle User  Object Owner    Object Name               Object Type     Locked Mode
--------- --------------- --------- -------------------- --------------- ------------------------- --------------- -------------------------
leo       22 / 1947       INACTIVE  MDINH                MDINH           T                         TABLE           Row-Exclusive (SX)
leo       27 / 487        ACTIVE    DEMO                 MDINH           T                         TABLE           Row-Exclusive (SX)
leo       35 / 795        ACTIVE    SYSTEM               MDINH           T                         TABLE           Row-Exclusive (SX)

ARROW:(SYS@leo):PRIMARY>

No Fun with EM 12c

Thu, 2016-08-04 17:21

Confession: I have not been using OEM for decades since there was never a real need for it and it’s starting to take its toll on me.

Throughout the day, I keep getting paged from OEM – Message=Number of failed login attempts exceeds threshold value.

The information provided is utterly useless, e.g. what is the threshold value and what’s the error code.

What would be useful is to provide the SQL used for the check for ease of troubleshooting.

Then I found Finding the source of failed login attempts. (Doc ID 352389.1)

SQL> @pr "select username,os_username,userhost,client_id,trunc(timestamp),count(*) failed_logins from dba_audit_trail where returncode=1017 and timestamp>trunc(sysdate) group by username,os_username,userhost, client_id,trunc(timestamp) order by 5";
USERNAME              : JANE
OS_USERNAME           : oracle
FAILED_LOGINS         : 1

That wasn’t it.

SQL> @pr "select username,os_username,RETURNCODE,userhost,trunc(timestamp),count(*) failed_logins from dba_audit_trail where returncode<>0 and timestamp>trunc(sysdate) group by username,os_username,RETURNCODE,userhost,trunc(timestamp) order by 5";
USERNAME              : 
OS_USERNAME           : tomcat
RETURNCODE            : 28000
FAILED_LOGINS         : 1065
-------------------------
USERNAME              : JANE
OS_USERNAME           : oracle
RETURNCODE            : 1017
FAILED_LOGINS         : 1

$ oerr ora 28000
28000, 00000, "the account is locked"
// *Cause:   The user has entered wrong password consequently for maximum
//           number of times specified by the user's profile parameter
//           FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA

$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

1065 failed logins and no one even knows about this?

Lesson learned, there are many types of failed logins.

 


What convention to use for PDB?

Sat, 2016-07-30 11:55

I don’t know but best to have one.

With multi-tenant database, I would use OMF to keep it simple and to avoid having to remember about FILE_NAME_CONVERT.

When creating PDB, there may be specification for ADMIN USER as show below:

CREATE PLUGGABLE DATABASE april ADMIN USER april_admin IDENTIFIED BY oracle;

Here are some examples I have come across (Names and Blogs are not provided to protect the innocents):

create pluggable database PDB1 admin user pdb1_admin 
CREATE PLUGGABLE DATABASE TESTPDB ADMIN USER TESTADMIN
CREATE PLUGGABLE DATABASE devpdb2 ADMIN USER pdb2dba
CREATE PLUGGABLE DATABASE mypdb ADMIN USER pdbadmin
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_admadmin user App_Admin

It’s no big deal, right? It’s always possible to dig for it.

Just playing around.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_tbs.sql

 CON_ID NAME     TABLESPACE_NAME      FILE_NAME                                                                                  ONLINE_STATUS
------- -------- -------------------- ------------------------------------------------------------------------------------------ -------------
      1 CDB$ROOT SYSAUX               /oradata/TMNT_A/datafile/o1_mf_sysaux_crx20pot_.dbf                                        ONLINE
      1 CDB$ROOT UNDOTBS1             /oradata/TMNT_A/datafile/o1_mf_undotbs1_crx20ssy_.dbf                                      ONLINE
      1 CDB$ROOT USERS                /oradata/TMNT_A/datafile/o1_mf_users_crx20tq8_.dbf                                         ONLINE
      1 CDB$ROOT SYSTEM               /oradata/TMNT_A/datafile/o1_mf_system_crx20gsp_.dbf                                        SYSTEM
      2 PDB$SEED
      3 APRIL    SYSAUX               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_sysaux_crx522d1_.dbf       ONLINE
      3 APRIL    USERS                /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_users_crx522d2_.dbf        ONLINE
      3 APRIL    SYSTEM               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_system_crx522cr_.dbf       SYSTEM

8 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_role_privs.sql

GRANTEE              GRANTED_ROLE
-------------------- --------------------
C##GGS_ADMIN         CDB_DBA
C##GGS_ADMIN         CDB_DBA
DBSNMP               CDB_DBA
DBSNMP               CDB_DBA
SYS                  CDB_DBA
SYS                  CDB_DBA
C##GGS_ADMIN         DBA
C##GGS_ADMIN         DBA
MDINH                DBA
SYS                  DBA
SYS                  DBA
SYSTEM               DBA
SYSTEM               DBA
APRIL_ADMIN          PDB_DBA
C##GGS_ADMIN         PDB_DBA
C##GGS_ADMIN         PDB_DBA
SYS                  PDB_DBA
SYS                  PDB_DBA
DBA                  XDBADMIN
DBA                  XDBADMIN
SYS                  XDBADMIN
SYS                  XDBADMIN

22 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY> CREATE PLUGGABLE DATABASE donnie FROM april;

Pluggable database created.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_tbs.sql

 CON_ID NAME     TABLESPACE_NAME      FILE_NAME                                                                                  ONLINE_STATUS
------- -------- -------------------- ------------------------------------------------------------------------------------------ -------------
      1 CDB$ROOT SYSAUX               /oradata/TMNT_A/datafile/o1_mf_sysaux_crx20pot_.dbf                                        ONLINE
      1 CDB$ROOT UNDOTBS1             /oradata/TMNT_A/datafile/o1_mf_undotbs1_crx20ssy_.dbf                                      ONLINE
      1 CDB$ROOT USERS                /oradata/TMNT_A/datafile/o1_mf_users_crx20tq8_.dbf                                         ONLINE
      1 CDB$ROOT SYSTEM               /oradata/TMNT_A/datafile/o1_mf_system_crx20gsp_.dbf                                        SYSTEM
      2 PDB$SEED
      3 APRIL    SYSAUX               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_sysaux_crx522d1_.dbf       ONLINE
      3 APRIL    USERS                /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_users_crx522d2_.dbf        ONLINE
      3 APRIL    SYSTEM               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_system_crx522cr_.dbf       SYSTEM
      4 DONNIE

9 rows selected.

Where are the tablespaces and datafiles for DONNIE?

CDB$ROOT:(SYS@tmnt):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         MOUNTED
CDB$ROOT:(SYS@tmnt):PRIMARY> alter pluggable database donnie open;

Pluggable database altered.

CDB$ROOT:(SYS@tmnt):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         READ WRITE NO
CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_tbs.sql

 CON_ID NAME     TABLESPACE_NAME      FILE_NAME                                                                                  ONLINE_STATUS
------- -------- -------------------- ------------------------------------------------------------------------------------------ -------------
      1 CDB$ROOT SYSAUX               /oradata/TMNT_A/datafile/o1_mf_sysaux_crx20pot_.dbf                                        ONLINE
      1 CDB$ROOT USERS                /oradata/TMNT_A/datafile/o1_mf_users_crx20tq8_.dbf                                         ONLINE
      1 CDB$ROOT UNDOTBS1             /oradata/TMNT_A/datafile/o1_mf_undotbs1_crx20ssy_.dbf                                      ONLINE
      1 CDB$ROOT SYSTEM               /oradata/TMNT_A/datafile/o1_mf_system_crx20gsp_.dbf                                        SYSTEM
      2 PDB$SEED
      3 APRIL    SYSAUX               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_sysaux_crx522d1_.dbf       ONLINE
      3 APRIL    USERS                /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_users_crx522d2_.dbf        ONLINE
      3 APRIL    SYSTEM               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_system_crx522cr_.dbf       SYSTEM
      4 DONNIE   SYSAUX               /oradata/TMNT_A/38DDD14641845D0EE0530100007F0289/datafile/o1_mf_sysaux_csso885x_.dbf       ONLINE
      4 DONNIE   USERS                /oradata/TMNT_A/38DDD14641845D0EE0530100007F0289/datafile/o1_mf_users_csso885y_.dbf        ONLINE
      4 DONNIE   SYSTEM               /oradata/TMNT_A/38DDD14641845D0EE0530100007F0289/datafile/o1_mf_system_csso885k_.dbf       SYSTEM

11 rows selected.

PDB DONNIE does not have ADMIN USER.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_role_privs.sql

GRANTEE              GRANTED_ROLE
-------------------- --------------------
C##GGS_ADMIN         CDB_DBA
C##GGS_ADMIN         CDB_DBA
C##GGS_ADMIN         CDB_DBA
DBSNMP               CDB_DBA
DBSNMP               CDB_DBA
DBSNMP               CDB_DBA
SYS                  CDB_DBA
SYS                  CDB_DBA
SYS                  CDB_DBA
C##GGS_ADMIN         DBA
C##GGS_ADMIN         DBA
C##GGS_ADMIN         DBA
MDINH                DBA
MDINH                DBA
SYS                  DBA
SYS                  DBA
SYS                  DBA
SYSTEM               DBA
SYSTEM               DBA
SYSTEM               DBA
APRIL_ADMIN          PDB_DBA
APRIL_ADMIN          PDB_DBA
C##GGS_ADMIN         PDB_DBA
C##GGS_ADMIN         PDB_DBA
C##GGS_ADMIN         PDB_DBA
SYS                  PDB_DBA
SYS                  PDB_DBA
SYS                  PDB_DBA
DBA                  XDBADMIN
DBA                  XDBADMIN
DBA                  XDBADMIN
SYS                  XDBADMIN
SYS                  XDBADMIN
SYS                  XDBADMIN

34 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY>

UPDATED: Looks like APRIL_ADMIN is for 2 different PDB's. Now that's confusing.

CDB$ROOT:(SYS@tmnt):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         READ WRITE NO
CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_role_privs.sql

GRANTEE              GRANTED_ROLE          CON_ID
-------------------- -------------------- -------
C##GGS_ADMIN         CDB_DBA                    1
C##GGS_ADMIN         CDB_DBA                    3
C##GGS_ADMIN         CDB_DBA                    4
DBSNMP               CDB_DBA                    1
DBSNMP               CDB_DBA                    3
DBSNMP               CDB_DBA                    4
SYS                  CDB_DBA                    1
SYS                  CDB_DBA                    3
SYS                  CDB_DBA                    4
C##GGS_ADMIN         DBA                        1
C##GGS_ADMIN         DBA                        3
C##GGS_ADMIN         DBA                        4
MDINH                DBA                        3
MDINH                DBA                        4
SYS                  DBA                        1
SYS                  DBA                        3
SYS                  DBA                        4
SYSTEM               DBA                        1
SYSTEM               DBA                        3
SYSTEM               DBA                        4
APRIL_ADMIN          PDB_DBA                    3
APRIL_ADMIN          PDB_DBA                    4
C##GGS_ADMIN         PDB_DBA                    1
C##GGS_ADMIN         PDB_DBA                    3
C##GGS_ADMIN         PDB_DBA                    4
SYS                  PDB_DBA                    1
SYS                  PDB_DBA                    3
SYS                  PDB_DBA                    4
DBA                  XDBADMIN                   1
DBA                  XDBADMIN                   3
DBA                  XDBADMIN                   4
SYS                  XDBADMIN                   1
SYS                  XDBADMIN                   3
SYS                  XDBADMIN                   4

34 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY>


Losing /u01

Wed, 2016-07-27 10:54

There was an incident where /u01 was lost.

This means having to reinstall and patch software again.

When installing grid and I encountered:

CRS is already configured on this node for crshome=/u01/app/oracle/product/11.2.0/grid

Ah Crap! This is me being melodramatic.

The system was using Oracle Restart, it needs to be removed.

# /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose  

In hindsight, it would have been much easier to delete from /etc/oracle.

Guess what? ASM will need to be recreated and there’s a post for that.

ReCreate ASM Disks

++++++++++

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
/u01/app/oracle/product/11.2.0/grid/bin/crsctl query crs activeversion ... failed rc=-1 with message:
 Failure in execution (rc=-1, 0, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl query crs activeversion 

CRS is already configured on this node for crshome=/u01/app/oracle/product/11.2.0/grid
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home. 

# ps -ef|grep pmon
root     143547 143405  0 15:22 pts/2    00:00:00 grep pmon

++++++++++

# /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force -verbose  
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl stop resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl delete resource ora.cssd -f
Failure in execution (rc=-1, 256, No such file or directory) for command /u01/app/oracle/product/11.2.0/grid/bin/crsctl stop has -f
You must kill ohasd processes or reboot the system to properly 
cleanup the processes started by Oracle clusterware
Successfully deconfigured Oracle Restart stack
# 

++++++++++

# /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I/u01/app/oracle/product/11.2.0/grid/perl/lib -I/u01/app/oracle/product/11.2.0/grid/crs/install /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
Using configuration parameter file: /u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
LOCAL ADD MODE 
Creating OCR keys for user 'oracle', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE 
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node arrow successfully pinned.
Adding Clusterware entries to inittab

arrow     2016/07/26 15:30:21     /u01/app/oracle/product/11.2.0/grid/cdata/arrow/backup_20160726_153021.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
# 

++++++++++

$ ll /etc/oracle
total 2256
drwxrwx--- 2 oracle oinstall    4096 Jul 26 15:29 lastgasp
-rw-r----- 1 oracle oinstall      92 Jul 26 15:29 ocr.loc
-rw-r--r-- 1 root   root          16 Jul 26 15:29 ocr.loc.orig
-rw-r----- 1 root   oinstall     125 Jul 26 15:29 olr.loc
-rw-r--r-- 1 root   root           0 Jul 26 15:29 olr.loc.orig
drwxrwx--- 5 root   oinstall    4096 Jul 26 15:29 oprocd
drwxr-x--- 3 root   oinstall    4096 Jul 26 15:29 scls_scr
-rws--x--- 1 root   oinstall 2280039 Jul 26 15:59 setasmgid

+++++++++

ASM disk creation failed because it's already a MEMBER.

SQL> alter system set asm_diskstring='ORCL:*' scope=both;

System altered.

SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*';
CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk 'ORCL:REDO01_002' belongs to diskgroup "REDO01"
ORA-15033: disk 'ORCL:REDO01_001' belongs to diskgroup "REDO01"


SQL> CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*';
CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*'
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk 'ORCL:DATA01_002' belongs to diskgroup "DATA01"
ORA-15033: disk 'ORCL:DATA01_001' belongs to diskgroup "DATA01"

SQL> select HEADER_STATUS||','||path from v$asm_disk;

HEADER_STATUS||','||PATH
--------------------------------------------------------------------------------
MEMBER,ORCL:DATA01_001
MEMBER,ORCL:DATA01_002
MEMBER,ORCL:DATA01_003
MEMBER,ORCL:DATA01_004
MEMBER,ORCL:REDO01_005
MEMBER,ORCL:REDO01_001
MEMBER,ORCL:REDO01_002
MEMBER,ORCL:REDO01_003
MEMBER,ORCL:REDO01_004
MEMBER,ORCL:DATA01_005

10 rows selected.

SQL> 

ReCreate ASM Disks

Wed, 2016-07-27 08:33

The following ASM disks were created which does not follow convention:
ASM_REDO01_006, ASM_REDO01_007, ASM_REDO01_008, ASM_REDO01_009, and ASM_REDO01_010

It should be:
ASM_REDO01_001, ASM_REDO01_002, ASM_REDO01_003, ASM_REDO01_004, and ASM_REDO01_005

Hence, we will need to recreate them. Here’s the process for me to remember.

conn / as sysasm
drop diskgroup DATA01;
drop diskgroup REDO01;
SQL> show parameter disk

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string
asm_diskstring			     string	 ORCL:*

++++++++++

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_001
Disk "ASM_DATA01_001" is a valid ASM disk on device [120,33]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_002
Disk "ASM_DATA01_002" is a valid ASM disk on device [120,49]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_003
Disk "ASM_DATA01_003" is a valid ASM disk on device [120,65]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_004
Disk "ASM_DATA01_004" is a valid ASM disk on device [120,81]

# /etc/init.d/oracleasm querydisk -d ASM_DATA01_005
Disk "ASM_DATA01_005" is a valid ASM disk on device [120,97]

++++++++++

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_006
Disk "ASM_REDO01_006" is a valid ASM disk on device [120,113]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_007
Disk "ASM_REDO01_007" is a valid ASM disk on device [120,129]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_008
Disk "ASM_REDO01_008" is a valid ASM disk on device [120,145]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_009
Disk "ASM_REDO01_009" is a valid ASM disk on device [120,161]

$ /etc/init.d/oracleasm querydisk -d ASM_REDO01_010
Disk "ASM_REDO01_010" is a valid ASM disk on device [120,177]

++++++++++

$ ls -l /dev/|grep 120
drwxr-xr-x  6 root root       120 May  5 13:19 disk
brw-r-----  1 root disk  120,   0 May 18 08:35 emcpowera
brw-r-----  1 root disk  120,   1 May 18 08:42 emcpowera1
brw-r-----  1 root disk  120,  16 May 18 08:35 emcpowerb
brw-r-----  1 root disk  120,  17 May 18 08:43 emcpowerb1
brw-r-----  1 root disk  120,  32 May 18 00:19 emcpowerc
brw-r-----  1 root disk  120,  33 May 18 08:36 emcpowerc1
brw-r-----  1 root disk  120,  48 May 18 00:19 emcpowerd
brw-r-----  1 root disk  120,  49 May 18 08:36 emcpowerd1
brw-r-----  1 root disk  120,  64 May 18 00:19 emcpowere
brw-r-----  1 root disk  120,  65 May 18 08:36 emcpowere1
brw-r-----  1 root disk  120,  80 May 18 00:19 emcpowerf
brw-r-----  1 root disk  120,  81 May 18 08:36 emcpowerf1
brw-r-----  1 root disk  120,  96 May 18 00:29 emcpowerg
brw-r-----  1 root disk  120,  97 May 18 08:36 emcpowerg1
brw-r-----  1 root disk  120, 112 May 18 00:19 emcpowerh
brw-r-----  1 root disk  120, 113 May 18 08:36 emcpowerh1
brw-r-----  1 root disk  120, 128 May 18 00:19 emcpoweri
brw-r-----  1 root disk  120, 129 May 18 08:36 emcpoweri1
brw-r-----  1 root disk  120, 144 May 18 00:19 emcpowerj
brw-r-----  1 root disk  120, 145 May 18 08:36 emcpowerj1
brw-r-----  1 root disk  120, 160 May 18 00:19 emcpowerk
brw-r-----  1 root disk  120, 161 May 18 08:36 emcpowerk1
brw-r-----  1 root disk  120, 176 May 18 00:19 emcpowerl
brw-r-----  1 root disk  120, 177 May 18 08:36 emcpowerl1
brw-r-----  1 root disk  120, 192 May 18 08:35 emcpowerm
brw-r-----  1 root disk  120, 193 May 18 08:40 emcpowerm1

+++++++++

# /etc/init.d/oracleasm deletedisk ASM_REDO01_006
Removing ASM disk "ASM_REDO01_006":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_007
Removing ASM disk "ASM_REDO01_007":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_008
Removing ASM disk "ASM_REDO01_008":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_009
Removing ASM disk "ASM_REDO01_009":                        [  OK  ]

# /etc/init.d/oracleasm deletedisk ASM_REDO01_010
Removing ASM disk "ASM_REDO01_010": 

++++++++++

# /etc/init.d/oracleasm createdisk DATA01_001 /dev/emcpowerc1
Marking disk "DATA01_001" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_002 /dev/emcpowerd1
Marking disk "DATA01_002" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_003 /dev/emcpowere1
Marking disk "DATA01_003" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_004 /dev/emcpowerf1
Marking disk "DATA01_004" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk DATA01_005 /dev/emcpowerg1
Marking disk "DATA01_005" as an ASM disk:                  [  OK  ]

+++++++++

# /etc/init.d/oracleasm createdisk REDO01_001 /dev/emcpowerh1
Marking disk "REDO01_001" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_002 /dev/emcpoweri1
Marking disk "REDO01_002" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_003 /dev/emcpowerj1
Marking disk "REDO01_003" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_004 /dev/emcpowerk1
Marking disk "REDO01_004" as an ASM disk:                  [  OK  ]

# /etc/init.d/oracleasm createdisk REDO01_005 /dev/emcpowerl1
Marking disk "REDO01_005" as an ASM disk:                  [  OK  ]

+++++++++

# /etc/init.d/oracleasm listdisks
DATA01_001
DATA01_002
DATA01_003
DATA01_004
DATA01_005
REDO01_001
REDO01_002
REDO01_003
REDO01_004
REDO01_005

SQL> select HEADER_STATUS||','||path from v$asm_disk;

HEADER_STATUS||','||PATH
--------------------------------------------------------------------------------
PROVISIONED,ORCL:DATA01_001
PROVISIONED,ORCL:DATA01_002
PROVISIONED,ORCL:DATA01_003
PROVISIONED,ORCL:DATA01_004
PROVISIONED,ORCL:REDO01_005
PROVISIONED,ORCL:REDO01_001
PROVISIONED,ORCL:REDO01_002
PROVISIONED,ORCL:REDO01_003
PROVISIONED,ORCL:REDO01_004
PROVISIONED,ORCL:DATA01_005

10 rows selected.


SQL> CREATE DISKGROUP REDO01 EXTERNAL REDUNDANCY DISK 'ORCL:REDO*';

Diskgroup created.

SQL> CREATE DISKGROUP DATA01 EXTERNAL REDUNDANCY DISK 'ORCL:DATA*';

Diskgroup created.

SQL> show parameter disk

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups			     string	 REDO01, DATA01
asm_diskstring			     string	 ORCL:*

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

+++++++++

$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    511994   511936                0          511936              0             N  DATA01/
MOUNTED  EXTERN  N         512   4096  1048576     51195    51137                0           51137              0             N  REDO01/
ASMCMD> 

++++++++++

$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA01.dg
               ONLINE  ONLINE       arrow                                    
ora.LISTENER.lsnr
               ONLINE  ONLINE       arrow                                    
ora.REDO01.dg
               ONLINE  ONLINE       arrow                                    
ora.asm
               ONLINE  ONLINE       arrow                Started             
ora.ons
               OFFLINE OFFLINE      arrow                                    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       arrow                                    
ora.diskmon
      1        OFFLINE OFFLINE                                                   
ora.evmd
      1        ONLINE  ONLINE       arrow                                    
$

GoldenGate 12.2 Object Exclude

Sun, 2016-07-24 13:06

1.8 Details of Support for Objects and Operations in Oracle DDL

The following tables are excluded by default.

  "*.AQ$*", // advanced queues
  "*.DR$*$*", // oracle text
  "*.M*_*$$", // Spatial index
  "*.MLOG$*", // materialized views
  "*.OGGQT$*",
  "*.OGG$*", // AQ OGG queue table
  "*.ET$*", // Data Pump external tables
  "*.RUPD$*", // materialized views
  "*.SYS_C*", // constraints
  "*.MDR*_*$", // Spatial Sequence and Table
  "*.SYS_IMPORT_TABLE*",
  "*.CMP*$*", // space management, rdbms >= 12.1
  "*.DBMS_TABCOMP_TEMP_*", // space management, rdbms < 12.1
  "*.MDXT_*$*" // Spatial extended statistics tables

If you specify JOB_NAME for datapump, then you may have to manually add the exclusion.

Next, during migration from 10g to 12c with Goldengate, extract was failing with

2016-06-20 01:00:16  ERROR   OGG-00665  Oracle GoldenGate Capture for Oracle, e_db10g.prm:  OCI Error getting OCI_ATTR_NAME for UDT SYS.ANYDATA (status = 24328-ORA-24328: illegal attribute value), SQL.
2016-06-20 01:00:17  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_db10g.prm:  PROCESS ABENDING.

The following exclusion was added to the extract.

SCHEDULER$_JOB_ARG

It might be better to add “*.SCHEDULER$*”?


Troubleshooting ORA-02049: timeout: distributed transaction waiting for lock

Sat, 2016-07-23 17:45

Controlling Connections Established by Database Links

Tracing can be done using – ALTER SYSTEM SET EVENTS ‘2049 trace name ERRORSTACK level 3’;

Nice note from MOS.

How to Obtain a System State Trace BEFORE the Error ORA-2049 Occurs, While Still Experiencing the Contention (Doc ID 789517.1)

Commit after select from dblink – blocking lock.
Session 1:
++++++++++
04:44:51 ARROW:(DEMO@leo):PRIMARY>
04:45:30 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.01
04:45:36 ARROW:(DEMO@leo):PRIMARY> commit;

Commit complete.

Elapsed: 00:00:00.00
04:45:41 ARROW:(DEMO@leo):PRIMARY>
04:45:49 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;

++++++++++
Session 2:
04:45:18 ARROW:(DEMO@leo):PRIMARY> update t set id=100;

1 row updated.

Elapsed: 00:00:00.00
04:45:21 ARROW:(DEMO@leo):PRIMARY> @/media/sf_working/sql/b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   DEMO            32,17        74nhxnyztg454    402 SQL*Net message to c ACTIVE      0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           41,271       1qfpvr7brd2pq    113 enq: TX - row lock c ACTIVE      8 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   DEMO            7741d4und71ph with s as ( SELECT decode(level,1,'BLOCKING','WAITING') state, LPAD('*',(level-1
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
04:54:19 ARROW:(DEMO@leo):PRIMARY> select count(*) from t;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01
05:00:49 ARROW:(DEMO@leo):PRIMARY>
No commit after select from dblink – ORA-02049: timeout: distributed transaction waiting for lock.

Session is automatically killed based on database paraneter tributed_lock_timeout (default is 60s)

Session 1:
++++++++++
21:58:06 ARROW:(DEMO@leo):PRIMARY> update t set id=100;

1 row updated.

Elapsed: 00:00:00.01
21:58:14 ARROW:(DEMO@leo):PRIMARY>

Session 2:
+++++++++
21:58:45 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.00
21:58:51 ARROW:(DEMO@leo):PRIMARY> update t set id=1;
update t set id=1
       *
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:01:00.01
21:59:59 ARROW:(DEMO@leo):PRIMARY>

Don’t forget to commit or rollback after using dblinks.

UPDATED: Tried to determine if there was a way to monitor distributed transaction and in this test case was not able to.

Session 1: 
++++++++++
15:59:32 ARROW:(MDINH@leo):PRIMARY> update demo.t set id=100;

1 row updated.

Elapsed: 00:00:00.01
15:59:43 ARROW:(MDINH@leo):PRIMARY>

Session 2: Distributed Transaction
++++++++++
16:00:43 ARROW:(DEMO@leo):PRIMARY> select name from v$pdbs@mdinh_april;

NAME
------------------------------
APRIL

Elapsed: 00:00:00.00
16:00:47 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;
update t set id=-9999
       *
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:10:00.00
16:10:57 ARROW:(DEMO@leo):PRIMARY> update t set id=-9999;
update t set id=-9999
*
ERROR at line 1:
ORA-02049: timeout: distributed transaction waiting for lock


Elapsed: 00:10:00.01
16:23:03 ARROW:(DEMO@leo):PRIMARY>

Session 3: Monitoring
++++++++++
16:21:23 ARROW:(SYS@leo):PRIMARY> show parameter distributed_lock_timeout

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
distributed_lock_timeout             integer     600
16:21:35 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   22 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE      9 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:21:38 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   22 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE      9 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:22:10 ARROW:(SYS@leo):PRIMARY> @b.sql

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           26,3                           32 SQL*Net message from INACTIVE   23 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           16,23        1qfpvr7brd2pq     70 enq: TX - row lock c ACTIVE     10 arrow.localdomain    oracle     sqlplus@arrow.l

Elapsed: 00:00:00.01

STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           4cnt32uym27j2 update demo.t set id=100
WAITING    *DEMO           1qfpvr7brd2pq update t set id=-9999

Elapsed: 00:00:00.00
16:22:56 ARROW:(SYS@leo):PRIMARY> @b.sql

no rows selected

Elapsed: 00:00:00.01

no rows selected

Elapsed: 00:00:00.00
16:23:12 ARROW:(SYS@leo):PRIMARY>

GoldenGate 12.2 TROUBLESHOOTING USING GETENV

Thu, 2016-07-21 21:14

I was faced with a very strange situation.

When using SETENV (ORACLE_SID = db01), replicat ABEND with  OGG-00664  OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available

When using SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1), replicat starts without any issues.

I know what you are thinking, why not set both ORACLE_SID and ORACLE_HOME and you are correct since this is best practices.

The client already knows this and wanted to troubleshoot why it was failing?

Any ideas? I scoured the internet and MOS and finally found that using get_env was the key to troubleshooting.

Tried tracing the replicat and did not work since replicat abended.

NOTE:  Environment variables are all set correctly from the OS.

BAD:
REPLICAT R_2D2
SETENV (ORACLE_SID = db01)

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1

getenv (ORACLE_SID)
ORACLE_SID = b01
USERIDALIAS ggadmin

2016-07-21 18:33:46 ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory).
GOOD:
REPLICAT R_2D2

SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1)

getenv (ORACLE_HOME)
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1

getenv (ORACLE_SID)
ORACLE_SID = db01
USERIDALIAS ggadmin

Notice, there are 2 different ORACLE_HOMEs being used.

Bad one is ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/dbhome_1 since the HOME does not exists.

Wait a minute, how is it possible the HOME does not exists and where did Goldengate get this information ???

Let’s find the installation log.
/u01/app/oraInventory/logs> grep "MANAGER_PORT" installActions2016*.log|uniq
installActions2016-05-09_04-57-53PM.log:INFO: Setting value for the property:MANAGER_PORT in the bean:OGGInstallSettings
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                       
installActions2016-05-09_04-57-53PM.log: MANAGER_PORT              7809                                                                    
installActions2016-05-09_04-57-53PM.log:INFO: adding the variable MANAGER_PORT to command line args table
installActions2016-05-09_04-57-53PM.log:INFO: Setting variable 'MANAGER_PORT' to '7809'. Received the value from the command line.
installActions2016-05-09_04-57-53PM.log:INFO: This variable MANAGER_PORT is not added to the global context map

/u01/app/oraInventory/logs> grep DATABASE_HOME installActions2016-05-09_04-57-53PM.log
INFO: Setting value for the property:DATABASE_HOME in the bean:OGGInstallSettings
 DATABASE_HOME             /u01/app/oracle/product/11.2.0.3/dbhome_1  
 DATABASE_HOME             /u01/app/oracle/product/11.2.0.3/dbhome_1                               
 
INFO: adding the variable DATABASE_HOME to command line args table
INFO: Setting variable 'DATABASE_HOME' to '/u01/app/oracle/product/11.2.0.3/dbhome_1'. Received the value from the command line.
INFO: This variable DATABASE_HOME is not added to the global context map

There it is, Goldengate is using ORACLE_HOME defined from the installation.

DATABASE_LOCATION is not required, so why set it?

Admittedly, I have have been setting DATABASE_LOCATION but will not be doing so any more.

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=

Lastly, I have not attempted to reproduce the error. Too lazy. May be you can help?


Different names for 12c CDB

Thu, 2016-07-21 09:15

Truthfully, I am feeling a little lazy to write a nice blog.

Configuring listener.ora and tnsnames.ora, varying names for database, connecting to database.

Summary:
db_name                              string      tmnt
db_unique_name                       string      tmnt_a
instance_name                        string      tmnt01

ADR Home /u01/app/oracle/diag/rdbms/tmnt_a/tmnt

Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Details:
[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat listener.ora
LISTENER_TMNT =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
      (ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767))
    )
  )

SID_LIST_LISTENER_TMNT =
  (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = tmnt)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = tmnt)
    )
  )

USE_SID_AS_SERVICE_LISTENER_TMNT = ON
INBOUND_CONNECT_TIMEOUT_LISTENER_TMNT = 120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_TMNT = OFF
DIAG_ADR_ENABLED_LISTENER_TMNT = OFF
LOGGING_LISTENER_TMNT = OFF
TRACE_LEVEL_LISTENER_TMNT = OFF
SAVE_CONFIG_ON_STOP_LISTENER_TMNT = TRUE

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat tnsnames.ora
APRIL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = APRIL)
    )
  )

TMNT_A =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TMNT_A)
    )
  )

TMNT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SID = tmnt)
    )
  )

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ lsnrctl status listener_tmnt

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JUL-2016 06:58:32

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener_tmnt
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JUL-2016 06:23:03
Uptime                    0 days 0 hr. 35 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)))
Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
The command completed successfully

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ ps -ef|grep pmon
oracle    2572     1  0 Jul20 ?        00:00:02 ora_pmon_tmnt
oracle    8998  3716  0 06:58 pts/0    00:00:00 grep pmon

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:58:58 2016

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

Last Successful login time: Thu Jul 21 2016 06:57:32 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt_a

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:06 2016

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

Last Successful login time: Thu Jul 21 2016 06:58:58 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt_a):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@april

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:13 2016

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

Last Successful login time: Thu Jul 21 2016 06:59:06 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

APRIL:(SYSTEM@april):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus sys/oracle@tmnt_a as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:22 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@tmnt_a):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      tmnt
db_unique_name                       string      tmnt_a
global_names                         boolean     FALSE
instance_name                        string      tmnt01
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      tmnt_a
CDB$ROOT:(SYS@tmnt_a):PRIMARY> @/home/oracle/diag_info.sql

   INST_ID NAME                           VALUE                                                                                CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
         1 Diag Enabled                   TRUE                                                                                      0
         1 ADR Base                       /u01/app/oracle                                                                           0
         1 ADR Home                       /u01/app/oracle/diag/rdbms/tmnt_a/tmnt                                                    0
         1 Diag Trace                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace                                              0
         1 Diag Alert                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/alert                                              0
         1 Diag Incident                  /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/incident                                           0
         1 Diag Cdump                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/cdump                                              0
         1 Health Monitor                 /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/hm                                                 0
         1 Default Trace File             /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace/tmnt_ora_9016.trc                            0
         1 Active Problem Count           0                                                                                         0
         1 Active Incident Count          0                                                                                         0

11 rows selected.

tmnt

CDB$ROOT:(SYS@tmnt_a):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[07:00]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$

12.1.0.2 New Features

Tue, 2016-07-19 21:51

Just a quick note as I play with CDB/PDB.

No more triggers!

PDB State Management Across CDB

select con_name, instance_name, state from DBA_PDB_SAVED_STATES;
alter pluggable database PDB1 SAVE STATE;
alter pluggable database ALL SAVE STATE;
alter pluggable database ALL DISCARD STATE;
alter pluggable database ALL OPEN;

startup force mount exclusive restrict;
drop database;

Drop database still does not clean up ADR.

[19:32]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 19:32:38 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@cdb12c):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all open;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all SAVE state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

CON_NAME INSTANCE_NAME STATE
-------- ------------- --------------
PDB2     cdb12c        OPEN
PDB1     cdb12c        OPEN

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all DISCARD state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
CDB$ROOT:(SYS@cdb12c):PRIMARY> !ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfilecdb12c.ora  spfiletmnt.ora

CDB$ROOT:(SYS@cdb12c):PRIMARY> drop database;

Database dropped.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
CDB$ROOT:(SYS@cdb12c):PRIMARY>
CDB$ROOT:(SYS@cdb12c):PRIMARY> exit
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ps -ef|grep pmon
oracle    7151     1  0 13:04 ?        00:00:01 ora_pmon_tmnt
oracle   16898  5756  0 19:39 pts/1    00:00:00 grep pmon
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfiletmnt.ora
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/diag/rdbms/
cdb12c  tmnt_a
[19:40]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$

Create 12c CDB using dbca or sqlplus ???

Tue, 2016-07-19 17:27

How do you typically create database, using dbca or sqlplus?

I am still surprised with all the bloatware added for creating database using dbca.

Also, is there a reason to prefix PDB with pdb?

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 28
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ dbca -silent \
> -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true \
> -gdbName cdb12c -sid cdb12c \
> -SysPassword oracle -SystemPassword oracle \
> -numberOfPdbs 2 -pdbName pdb \
> -pdbadminUsername pdba -pdbadminPassword oracle \
> -emConfiguration NONE \
> -redoLogFileSize 100 \
> -storageType FS -datafileDestination /oradata \
> -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 \
> -totalMemory 1024 -databaseType MULTIPURPOSE \
> -initparams audit_trail=NONE

Registering database with Oracle Restart
4% complete
Copying database files
5% complete
6% complete
12% complete
17% complete
22% complete
30% complete
Creating and starting Oracle instance
32% complete
35% complete
36% complete
37% complete
41% complete
44% complete
45% complete
48% complete
Completing Database Creation
50% complete
53% complete
55% complete
63% complete
71% complete
74% complete
Creating Pluggable Databases
79% complete
84% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb12c/cdb12c.log" for further details.

[15:11]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 48
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 15:10 hc_cdb12c.dat
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 14:56 lkCDB12C
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 7680 Jul 19 15:07 orapwcdb12c
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 3584 Jul 19 15:11 spfilecdb12c.ora
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
#Backup file is  /u01/app/12.1.0.2/grid/srvm/admin/oratab.bak.arrow line added by Agent
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1
cdb12c:/u01/app/oracle/product/12.1.0.2/db_1:N          # line added by Agent

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:12:33 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
CDB12C    DV           Oracle Database Vault                    12.1.0.2.0                     VALID
CDB12C    APEX         Oracle Application Express               4.2.5.00.08                    VALID
CDB12C    OLS          Oracle Label Security                    12.1.0.2.0                     VALID
CDB12C    SDO          Spatial                                  12.1.0.2.0                     VALID
CDB12C    ORDIM        Oracle Multimedia                        12.1.0.2.0                     VALID
CDB12C    CONTEXT      Oracle Text                              12.1.0.2.0                     VALID
CDB12C    OWM          Oracle Workspace Manager                 12.1.0.2.0                     VALID
CDB12C    XDB          Oracle XML Database                      12.1.0.2.0                     VALID
CDB12C    CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
CDB12C    CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID
CDB12C    JAVAVM       JServer JAVA Virtual Machine             12.1.0.2.0                     VALID
CDB12C    XML          Oracle XDK                               12.1.0.2.0                     VALID
CDB12C    CATJAVA      Oracle Database Java Packages            12.1.0.2.0                     VALID
CDB12C    APS          OLAP Analytic Workspace                  12.1.0.2.0                     VALID
CDB12C    XOQ          Oracle OLAP API                          12.1.0.2.0                     VALID
CDB12C    RAC          Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF

16 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Manually created CDB.
[15:14]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ . oraenv <<< tmnt ORACLE_SID = [cdb12c] ? The Oracle base remains unchanged with value /u01/app/oracle 

[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs 
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:14:10 2016 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          MOUNTED
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
TMNT      XDB          Oracle XML Database                      12.1.0.2.0                     VALID
TMNT      CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
TMNT      CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$

GoldenGate and Networking

Fri, 2016-07-15 23:16

I have never delved into networking for GoldenGate since things just work. Due to security tightening, telnet is no longer available to verify opened port.

What’s to follow is a demo for ports being listened by GoldenGate when manager started and ports listened and when PUMP Extract is started as part using DYNAMICPORTLIST.

Also, how to test opened port using (nc – arbitrary TCP and UDP connections and listens)

Both source and target are running on the same host; however, the same principles still apply.

HAWK (source): PORT 7901 and DYNAMICPORTLIST 15100-15120

THOR (target):   PORT 7801 and DYNAMICPORTLIST 15200-15220

 

 

HAWK (source):

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01

$ cat dirprm/mgr.prm
PORT 7901
DYNAMICPORTLIST 15100-15120
ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW
USERIDALIAS ggs_admin
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
-- AUTOSTART ER *
-- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGCRITICALMINUTES 15

++++++++++

GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:06      00:00:47
EXTRACT     STOPPED     P_HAWK      00:00:00      00:00:35

GGSCI (arrow.localdomain) 2>
THOR (target):
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ cat dirprm/mgr.prm
PORT 7801
DYNAMICPORTLIST 15200-15220
ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
-- AUTOSTART ER *
-- AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGCRITICALMINUTES 15

++++++++++

GGSCI (arrow.localdomain) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     R_HAWK      00:00:00      70:18:54

GGSCI (arrow.localdomain) 2>
Ports 7801 and 7901 are in LISTEN mode and none of the ports from DYNAMICPORTLIST are LISTEN.
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ netstat -lnp|grep "LISTEN "|egrep "mgr|server"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::7801                     :::*                        LISTEN      2808/./mgr
tcp        0      0 :::7901                     :::*                        LISTEN      3068/./mgr
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
HAWK (source):  Start PUMP
GGSCI (arrow.localdomain) 1> start p*

Sending START request to MANAGER ...
EXTRACT P_HAWK starting

GGSCI (arrow.localdomain) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E_HAWK      00:00:06      00:02:03
EXTRACT     RUNNING     P_HAWK      00:00:00      00:01:50

GGSCI (arrow.localdomain) 3>

Ports 7801 and 7901 are in LISTEN mode and none of the ports from DYNAMICPORTLIST 15200

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ netstat -lnp|grep "LISTEN "|egrep "mgr|server"
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 :::7801                     :::*                        LISTEN      2808/./mgr
tcp        0      0 :::7901                     :::*                        LISTEN      3068/./mgr
tcp        0      0 :::15200                    :::*                        LISTEN      3297/./server
oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$
How to use nc to test opened port. What RPM is required to install nc. Use yum install nc.
[root@arrow ~]# yum whatprovides nc
Loaded plugins: refresh-packagekit, security
epel/metalink                                                                                   |  12 kB     00:00
epel                                                                                            | 4.3 kB     00:00
epel/primary_db                                                                                 | 5.8 MB     00:03
public_ol6_UEKR3_latest                                                                         | 1.2 kB     00:00
public_ol6_latest                                                                               | 1.4 kB     00:00
public_ol6_latest/primary                                                                       |  59 MB     00:26
public_ol6_latest                                                                                          36199/36199
nc-1.84-22.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : public_ol6_latest
Matched from:

nc-1.84-24.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : public_ol6_latest
Matched from:

nc-1.84-24.el6.x86_64 : Reads and writes data across network connections using TCP or UDP
Repo        : installed
Matched from:
Other       : Provides-match: nc

[root@arrow ~]#
DEMO:
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 7901
Connection to arrow 7901 port [tcp/tnos-sp] succeeded!

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 15200
Connection to arrow 15200 port [tcp/*] succeeded!

oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$ nc -zv arrow 15201
nc: connect to arrow port 15201 (tcp) failed: Connection refused
nc: connect to arrow port 15201 (tcp) failed: Connection refused
oracle@arrow:thor:/u01/app/12.2.0.1/ggs02
$

GoldenGate 12.2 DDLOPTIONS MAPSESSIONSCHEMA

Tue, 2016-07-12 23:36

This is probably not a 12.2 specific features and since I only tested for 12.2, I am not going to vouch for other versions.

Be careful when qualifying schema with DDL replication especially when the source and target schemas are not the same.

Example: Source schema is DEMO and Target schema is DEMO_REP

What happens to DDL replication when the following is used?

alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix;

oracle@arrow:hawk:/u01/app/12.2.0.1/ggs01
$ sqlplus demo/demo @t.sql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 12 20:36:55 2016

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

ARROW:(DEMO@hawk):PRIMARY> drop table demo.t purge;

Table dropped.

ARROW:(DEMO@hawk):PRIMARY> create table demo.t(id int);

Table created.

ARROW:(DEMO@hawk):PRIMARY> create unique index demo.t_pk_ix on demo.t(id);

Index created.

ARROW:(DEMO@hawk):PRIMARY> alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix;

Table altered.

ARROW:(DEMO@hawk):PRIMARY>
Target: ggserr.log
2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create table demo.t(id int) (size 27)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create table "DEMO_REP"."T"(id int) (size 35)].

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create unique index demo.t_pk_ix on demo.t(id) (size 46)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create unique index "DEMO_REP"."T_PK_IX" on "DEMO_REP"."T"(id) (size 62)].

2016-07-12 20:37:00  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix (size 79)].
2016-07-12 20:37:00  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix (size 87)].

2016-07-12 20:37:00  ERROR   OGG-00516  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Fatal error executing DDL replication: error 
[Error code [1418], ORA-01418: specified index does not exist SQL alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix 
/* GOLDENGATE_DDL_REPLICATION */], due to explicit ABEND error handling and filter [include all (default)].
2016-07-12 20:37:04  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  PROCESS ABENDING.

Notice using index demo.t_pk_ix did not get mapped accordingly.

How do we fixed this? The simple solution is don’t prefix schema name.
2016-07-12 20:53:39  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create table y(id int) (size 22)].
2016-07-12 20:53:39  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create table "DEMO_REP"."Y"(id int) (size 35)].

2016-07-12 20:53:39  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [create unique index y_pk_ix on y(id) (size 36)].
2016-07-12 20:53:39  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation [create unique index "DEMO_REP"."Y_PK_IX" on "DEMO_REP"."Y"(id) (size 62)].

2016-07-12 20:53:40  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table y add constraint y_pk primary key(id) using index y_pk_ix (size 69)].
2016-07-12 20:53:40  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."Y" add constraint y_pk primary key(id) using index y_pk_ix (size 82)].
I know what you are probably thinking. What if login to the schema is not allowed?

DDLOPTIONS MAPSESSIONSCHEMA DEMO, TARGET DEMO_REP, MAPSCHEMAS

2016-07-12 21:27:10  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL found, operation [alter table demo.t add constraint t_pk primary key(id) using index demo.t_pk_ix (size 79)].
2016-07-12 21:27:10  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL is of mapped scope, after mapping new operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index demo.t_pk_ix (size 87)].
2016-07-12 21:27:10  INFO    OGG-01369  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation mapped to target database [T], new DDL operation 
[alter table "DEMO_REP"."T" add constraint t_pk primary key(id) using index DEMO_REP.t_pk_ix (size 91)].
2016-07-12 21:27:10  INFO    OGG-00487  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation included [INCLUDE MAPPED], optype [ALTER], objtype [TABLE], objowner [DEMO_REP], objname [T].
2016-07-12 21:27:10  INFO    OGG-01562  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Source schema DEMO is mapped to target schema DEMO_REP to set the current schema for DDL execution.
2016-07-12 21:27:10  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Setting current schema for DDL operation to [DEMO_REP].
2016-07-12 21:27:10  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Executing DDL operation.
2016-07-12 21:27:10  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  DDL operation successful.
2016-07-12 21:27:10  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, r_hawk.prm:  Restoring current schema for DDL operation to [GGS_ADMIN].
Happy Replication!

Reference: https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters037.htm#GWURF451


RMAN 12c NF – SQL interface in RMAN


Sat, 2016-07-09 18:51

Admittedly, I did not realize the benefit at first until there came a need.

Example:


# Set ORACLE_SID
ORACLE_SID=$1
export ORACLE_SID
### This retrieve the numeric value from PROD1/PROD2, i.e. 1,2 respectively
n=`echo "${ORACLE_SID:(-1)}"`

run {
backup tag 'ARCHIVED_LOG' archivelog all delete input skip inaccessible;
alter database backup controlfile to trace as '/rman_bkup$n/$ORACLE_SID/cf_@.sql' REUSE RESETLOGS;
create pfile='/rman_bkup$n/$ORACLE_SID/init@.ora' from spfile;
create pfile from spfile;
}

 


Oracle Education Dissatisfaction

Sat, 2016-07-09 18:28

Just like anything, there is good and bad.

Unfortunately for me, my experience with Oracle education has been predominately bad and former manager experienced the same where he was able to retake a course almost a year later since the original was unrealistic for real world scenarios.

With Oracle education, the most simplistic setup is used, e.g. no ASM, no Grid Infrastructure as it may add a little more complexity to set up?

A little advice, try to find out who will be the instructor before registering for the course. Call and ask.

Next, there is a large and great Oracle community where you may learn more about technology and new features.

Here are 2 features I came to know from twitter feed and blogs which were never covered in the 12c Data Guard Administration.

ROLLING FORWARD A PHYSICAL STANDBY USING RECOVER FROM SERVICE COMMAND IN 12C (Doc ID 1987763.1) (Courtesy Nassyam Basha)
RMAN ACTIVE DUPLICATE USING BACKUPSET IN 12C(NEW FEATURE) (Doc ID 1987193.1) (Courtesy of Deiby Gomez)

 

 


GoldenGate 12.2 cases and quotes

Sun, 2016-06-12 19:10

Avoid using quotes and case sensitivities if you can.

Process Abends : OGG-00919 Error in COLMAP clause referring to : @GETENV(“GGHEADER”…. (Doc ID 1635935.1)

1. Replace double quote with single quote.

Change reference is GETENV statement to use single quotes rather than double quotes, as example is :

@GETENV(‘GGHEADER’,’BEFOREAFTERINDICATOR’)

2. Specify NOUSEANSISQLQUOTES in GLOBALS parameter.

The default is now USEANSISQLQUOTES.

USEANSISQLQUOTES

When capturing and mapping object names, such as table names, Oracle GoldenGate always recognizes double-quoted strings as case-sensitive object names,
regardless of whether USEANSISQLQUOTES or NOUSEANSISQLQUOTES is specified.

With USEANSISQLQUOTES enabled, Oracle GoldenGate treats a string within double quotes as a case-sensitive column name, and it treats a string within single quotes as a literal.

Demo:

$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (arrow.localdomain) 1> INFO CREDENTIALSTORE

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggs_user
  Userid: ggs_admin

Other domains:

  admin

To view other domains, use INFO CREDENTIALSTORE DOMAIN 

GGSCI (arrow.localdomain) 2> DBLOGIN USERIDALIAS GGS_USER

ERROR: Alias 'GGS_USER' not found in credential store..

GGSCI (arrow.localdomain) 3> DBLOGIN USERIDALIAS ggs_user

Successfully logged into database.

GoldenGate 12.2 discard vs exception table

Sun, 2016-06-12 11:44

I have seen several blogs demonstrating using database exception table for error handling.

Truth be told, I have never used the exception table method and curious to know what’s the difference.

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

First, there’s manual work to create database exception table for error handling.

Let’ me know how long it takes you to configure database exception table for error handling.

Second, with new features, you may need to update database exception table for error handling.

Third, there is no values for bind variables.

Discard functionality is built-in and with OGG 12.2 and the missing delete can easily be converted into insert.

Which method will you be implementing and why?

$ cat dirrpt/r_hawk.dsc

Oracle GoldenGate Delivery for Oracle process started, group R_HAWK discard file opened: 2016-06-11 13:04:48.441744
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (JANE, 1995-11-11:13:52:00, 256, PLANE) does not exist in table DEMO.TCUSTORD
ORA-01403: no data found

Operation failed at seqno 0 rba 190519
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = JANE
000000: 4a 41 4e 45                                     |JANE            |

ORDER_DATE = 1995-11-11 13:52:00
000000: 31 39 39 35 2d 31 31 2d 31 31 20 31 33 3a 35 32 |1995-11-11 13:52|
000010: 3a 30 30                                        |:00             |

PRODUCT_CODE = PLANE
000000: 50 4c 41 4e 45                                  |PLANE           |

ORDER_ID = 256
000000: 32 35 36                                        |256             |

PRODUCT_PRICE = 133300.00
000000: 31 33 33 33 30 30 2e 30 30                      |133300.00       |

PRODUCT_AMOUNT = 1
000000: 31                                              |1               |

TRANSACTION_ID = 100
000000: 31 30 30                                        |100             |

*
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE") = (WILL, 1994-09-30:15:33:00, 144, CAR) does not exist in table DEMO.TCUSTORD
ORA-01403: no data found

Operation failed at seqno 0 rba 190727
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTORD to DEMO.TCUSTORD
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = WILL
000000: 57 49 4c 4c                                     |WILL            |

ORDER_DATE = 1994-09-30 15:33:00
000000: 31 39 39 34 2d 30 39 2d 33 30 20 31 35 3a 33 33 |1994-09-30 15:33|
000010: 3a 30 30                                        |:00             |

PRODUCT_CODE = CAR
000000: 43 41 52                                        |CAR             |

ORDER_ID = 144
000000: 31 34 34                                        |144             |

PRODUCT_PRICE = 17520.00
000000: 31 37 35 32 30 2e 30 30                         |17520.00        |

PRODUCT_AMOUNT = 3
000000: 33                                              |3               |

TRANSACTION_ID = 100
000000: 31 30 30                                        |100             |

*
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE") = (JANE) does not exist in table DEMO.TCUSTMER
ORA-01403: no data found

Operation failed at seqno 0 rba 190905
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = JANE
000000: 4a 41 4e 45                                     |JANE            |

NAME = ROCKY FLYER INC.
000000: 52 4f 43 4b 59 20 46 4c 59 45 52 20 49 4e 43 2e |ROCKY FLYER INC.|

CITY = DENVER
000000: 44 45 4e 56 45 52                               |DENVER          |

STATE = CO
000000: 43 4f                                           |CO              |

*
Current time: 2016-06-11 13:09:07

OCI Error ORA-26787: The row with key ("CUST_CODE") = (WILL) does not exist in table DEMO.TCUSTMER
ORA-01403: no data found

Operation failed at seqno 0 rba 191043
Discarding record on action DISCARD on error 1403
Problem replicating DEMO.TCUSTMER to DEMO.TCUSTMER
Record not found
Mapping problem with delete record (target format)...
*
CUST_CODE = WILL
000000: 57 49 4c 4c                                     |WILL            |

NAME = BG SOFTWARE CO.
000000: 42 47 20 53 4f 46 54 57 41 52 45 20 43 4f 2e    |BG SOFTWARE CO. |

CITY = SEATTLE
000000: 53 45 41 54 54 4c 45                            |SEATTLE         |

STATE = WA
000000: 57 41                                           |WA              |

*
Process Abending : 2016-06-11 13:09:24

select * from exceptions

ARROW:(GGS_ADMIN@thor):PRIMARY> @pr "select * from exceptions";
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTORD
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25819152
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTORD
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25819772
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTMER
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25821712
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------
REP_NAME                      : R_HAWK
TABLE_NAME                    : DEMO.TCUSTMER
ERRNO                         : 1403
DBERRMSG                      : OCI Error ORA-01403: no data found, SQL 
OPTYPE                        : DELETE
ERRTYPE                       : DB
LOGRBA                        : 52
LOGPOSITION                   : 25822128
COMMITTIMESTAMP               : 11-JUN-16 01.17.37.000416 PM
-------------------------

PL/SQL procedure successfully completed.

Convert DELETE into INSERT

Not all the columns’ value are available and because I did not configured properly.

ARROW:(DEMO@thor):PRIMARY> desc DEMO.TCUSTORD
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CUST_CODE                                             NOT NULL VARCHAR2(4)
 ORDER_DATE                                            NOT NULL DATE
 PRODUCT_CODE                                          NOT NULL VARCHAR2(8)
 ORDER_ID                                              NOT NULL NUMBER
 PRODUCT_PRICE                                                  NUMBER(8,2)
 PRODUCT_AMOUNT                                                 NUMBER(6)
 TRANSACTION_ID                                                 NUMBER

ARROW:(DEMO@thor):PRIMARY> select count(*) from  DEMO.TCUSTORD
  2

ARROW:(DEMO@thor):PRIMARY> select count(*) from  DEMO.TCUSTORD;

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

ARROW:(DEMO@thor):PRIMARY> insert into DEMO.TCUSTORD ("CUST_CODE", "ORDER_DATE", "ORDER_ID", "PRODUCT_CODE")
  2  values ('JANE', '1995-11-11:13:52:00', 256, 'PLANE');

1 row created.

ARROW:(DEMO@thor):PRIMARY> select count(*) from  DEMO.TCUSTORD;

  COUNT(*)
----------
         1

ARROW:(DEMO@thor):PRIMARY>

Pages