Columns Affected by Extended Data Type

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, (more...)

rlwrap – there’s a rpm for that

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
 (more...)

Configuring Multiple local_listener

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  (more...)

Quick note on using nc (netcat)

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

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  (more...)

No Fun with EM 12c

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 (more...)

What convention to use for PDB?

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  (more...)

Losing /u01

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 (more...)

ReCreate ASM Disks

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  (more...)

GoldenGate 12.2 Object Exclude

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*$*",  (more...)