Thinking Out Loud

August 24, 2016

Columns Affected by Extended Data Type

Filed under: 12c,oracle — mdinh @ 1:02 pm

I am not going to post how to convert to extended data type since there are many blogs on that already.

Just a reminder, there’s no going back; hence have backup and possibly minimize changes during testing to be able to restore (which is ideal and may not be feasible).

Before reverting to MAX_STRING_SIZE=STANDARD, columns affected by extended data type need to be identified.

From Oracle documentation, MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply
(for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).

EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

Test case:

[05:41]oracle@arrow:test:/media/sf_working/sql
$ sysdba @max_string_size.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 24 05:41: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


OWNER                TABLE_NAME                               COLUMN_NAME               DATA_TYPE  DATA_LENGTH CHAR_LENGTH C
-------------------- ---------------------------------------- ------------------------- ---------- ----------- ----------- -
MDINH                T                                        NAME                      VARCHAR2          5000        5000 B
MDINH                T2                                       T2                        RAW               2555           0
MDINH                T3                                       ID                        VARCHAR2         24000        6000 C
SYS                  DBA_ADDM_FINDINGS                        FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_FINDINGS                        MORE_INFO                 VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASKS                           ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASKS                           STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_ADDM_TASK_DIRECTIVES                 DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_ACTIONS                      MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_DEF_PARAMETERS               DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXECUTIONS                   ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXECUTIONS                   STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_EXEC_PARAMETERS              DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDINGS                     MORE_INFO                 VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_FINDING_NAMES                FINDING_NAME              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_PARAMETERS                   DESCRIPTION               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RATIONALE                    IMPACT_TYPE               VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RATIONALE                    MESSAGE                   VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_RECOMMENDATIONS              BENEFIT_TYPE              VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_TASKS                        ERROR_MESSAGE             VARCHAR2         32767       32767 B
SYS                  DBA_ADVISOR_TASKS                        STATUS_MESSAGE            VARCHAR2         32767       32767 B
SYS                  DBA_REGISTRY                             OTHER_SCHEMAS             VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_CHAIN_RULES                ACTION                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_CHAIN_RULES                CONDITION                 VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOBS                       PROGRAM_NAME              VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       PROGRAM_OWNER             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       RAISE_EVENTS              VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOBS                       SCHEDULE_NAME             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOBS                       SCHEDULE_OWNER            VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_JOB_RUN_DETAILS            ERRORS                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_JOB_RUN_DETAILS            OUTPUT                    VARCHAR2         32767       32767 B
SYS                  DBA_SCHEDULER_WINDOWS                    SCHEDULE_NAME             VARCHAR2         16000       16000 B
SYS                  DBA_SCHEDULER_WINDOWS                    SCHEDULE_OWNER            VARCHAR2         16000       16000 B
SYS                  DBA_VIEWS                                TEXT_VC                   VARCHAR2         32767       32767 B
SYS                  INT$DBA_VIEWS                            TEXT_VC                   VARCHAR2         32767       32767 B

40 rows selected.

test:(SYS@test):PRIMARY> show parameter max_string

NAME                                 TYPE                           VALUE
------------------------------------ ------------------------------ ------------------------------
max_string_size                      string                         EXTENDED

test:(SYS@test):PRIMARY> desc mdinh.t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(1000)
 NAME                                                           VARCHAR2(5000)

test:(SYS@test):PRIMARY> desc mdinh.t2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 T2                                                             RAW(2555)

test:(SYS@test):PRIMARY> desc mdinh.t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             VARCHAR2(6000 CHAR)

test:(SYS@test):PRIMARY> @nls.sql

PARAMETER                      SESSION                        DATABASE                       INSTANCE
------------------------------ ------------------------------ ------------------------------ ------------------------------
NLS_COMP                       BINARY                         BINARY                         BINARY
NLS_SORT                       BINARY                         BINARY
NLS_CALENDAR                   GREGORIAN                      GREGORIAN
NLS_CURRENCY                   $                              $
NLS_LANGUAGE                   AMERICAN                       AMERICAN                       AMERICAN
NLS_TERRITORY                  AMERICA                        AMERICA                        AMERICA
NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS          DD-MON-RR
NLS_TIME_FORMAT                HH.MI.SSXFF AM                 HH.MI.SSXFF AM
NLS_CHARACTERSET                                              AL32UTF8
NLS_ISO_CURRENCY               AMERICA                        AMERICA
NLS_DATE_LANGUAGE              AMERICAN                       AMERICAN
NLS_DUAL_CURRENCY              $                              $
NLS_RDBMS_VERSION                                             12.1.0.2.0
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR             HH.MI.SSXFF AM TZR
NLS_NCHAR_CONV_EXCP            FALSE                          FALSE                          FALSE
NLS_LENGTH_SEMANTICS           CHAR                           BYTE                           BYTE
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM       DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET                                        AL16UTF16
NLS_NUMERIC_CHARACTERS         .,                             .,
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR   DD-MON-RR HH.MI.SSXFF AM TZR

20 rows selected.

test:(SYS@test):PRIMARY>

Now it’s evident as to why there is no going back since SYS objects seem to be modified too.

That’s the easy part. Next is to create the database with identical components installed and hopefully full export/import will work.

Some useful information if you are thinking about migrating to extended data type.
12c Indexing Extended Data Types Part I (A Big Hurt)

12c Indexing Extended Data Types Part II (15 Steps)

August 19, 2016

rlwrap – there’s a rpm for that

Filed under: linux — mdinh @ 4:09 am

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

August 18, 2016

Configuring Multiple local_listener

Filed under: 11g,oracle — mdinh @ 5:16 am

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

August 17, 2016

Quick note on using nc (netcat)

Filed under: linux — mdinh @ 12:43 pm

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

August 6, 2016

Note to self for blocking locks

Filed under: oracle — mdinh @ 10:20 am

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>

August 4, 2016

No Fun with EM 12c

Filed under: oracle — mdinh @ 10:21 pm

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.

 

July 30, 2016

What convention to use for PDB?

Filed under: 12c,CDB,oracle — mdinh @ 4:55 pm

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>

July 27, 2016

Losing /u01

Filed under: 11g,ASM,oracle — mdinh @ 3:54 pm

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

Filed under: 11g,ASM,oracle — mdinh @ 1:33 pm

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                                    
$

July 24, 2016

GoldenGate 12.2 Object Exclude

Filed under: 12c,GoldenGate — mdinh @ 6:06 pm

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$*”?

Next Page »

Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 514 other followers