Looking for something? Try here..

Wednesday, February 28, 2018

Dropping pluggable database

This is a small post on how to drop a pluggable database if you are on multi tenant architecture of the Oracle 12c database.
Commands are as below and the explanation is provided after the command executions.
[oracle@xxxx ~]$ dblogin

SQL*Plus: Release Production on Tue Feb 20 01:56:45 2018

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

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

Enter value for container:
old   1: alter session set container=&container
new   1: alter session set container=
alter session set container=
ERROR at line 1:
ORA-65015: missing or invalid container name

SQL> select name, TOTAL_MB, FREE_MB, CON_ID from  v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB     CON_ID
------------------------------ ---------- ---------- ----------
RECOD1                           59731200   53564384          0
DBFS_DG                           1030816    1012720          0
DATAD1                          209018880   99355460          0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        16 QADB26                         READ WRITE NO
        17 PTETFSA                        READ WRITE NO
        18 DVBSODB                        READ WRITE NO
SQL> alter pluggable database PTETFSA close;
^Calter pluggable database PTETFSA close
ERROR at line 1:
ORA-01013: user requested cancel of current operation

SQL>  alter pluggable database PTETFSA close immediate;

Pluggable database altered.


Pluggable database dropped.

SQL> select name, TOTAL_MB, FREE_MB, CON_ID from  v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB     CON_ID
------------------------------ ---------- ---------- ----------
RECOD1                           59731200   53564384          0
DBFS_DG                           1030816    1012720          0
DATAD1                          209018880  101575024          0


Explanations are as below.

Line 1: dblogin is an alias used in my profile file defined as below.
alias dblogin='sqlplus sys/***** as sysdba @set_con'

set_con is a sql script with the following line
alter session set container=&container;

You don't pass a pdb name, the login defaults to root container.

Line 22: Checking for space information in the ASM diskgroup

Line 48: Close the pluggable database on both the instances as this is a RAC setup

Line 52: INCLUDING DATAFILES clause is required to delete any files associated with the pluggable database. The default is KEEP DATAFILES

Reference: Click here

Happy dropping!! 

Monday, September 4, 2017

Oracle GoldenGate and threaded_execution

Oracle database version:
Oracle GoldenGate version: Version OGGCORE_12.


What is this parameter all about?

The multi-threaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces. It applies to both BACKGROUND PROCESSES and SERVER PROCESSES. By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes. This feature can be helpful to reduce CPU and memory usage because it reduces the number of Oracle processes. It is disabled by default.

In one of our environment, we have enabled this parameter considering this a new 12c feature helping to utilize resources in an efficient way. So setting is as below
SQL> sho parameter threaded

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
threaded_execution                   boolean     TRUE

The detailed notes on this parameter or multi-threaded Oracle model can be found in the support note Multi-Threaded Oracle Model : 12c New (Doc ID 1958348.1)

With this setting turned on, I was trying to register an integrated extract on the multi tenant database (which is what we can do as we can't use classic extract if the database is multi tenant) which is throwing the below error.
GGSCI (exdevqadbadm01.wm.cginet as C##GGADMIN@pl360cdb2/CDB$ROOT) 2> register extract eo2dtfsa database container (ptetfsa) 

2017-08-02 12:26:51 ERROR OGG-08221 Cannot register or unregister EXTRACT EO2DTFSA because of the following SQL error: OCI Error 1,031. 
If you look at the error, this states that the error is due to insufficient privileges. The oracle user we use has has all the privileges granted as per Oracle documentation and the user has DBA role in addition as well.

There are many notes regarding the impacts of the threaded_execution setting to TRUE. Below are few examples and you will get a lot of notes related to the parameter

12c: threaded_execution=true Prevents OS Login As Sysdba
How to connect Non-sysdba Account via RMAN, When threaded_execution=TRUE

We have raised a support request with Oracle and they are working on the same.

Things tried as per Oracle's suggestion
Try to register extract with threaded_execution=FALSE and check what happens when flip the setting back.
With the setting changed to FALSE, I was able to register the process and add extract and the parameter flip to TRUE doesn't affect the extract process until I tried to stop the process after testing. 
The process couldn't find it's parent and became a zombie that I couldn't stop the process via GGSCI and the process in due course utilized all the cpu available on the server making the database to hang. I had to kill the process from OS using  "kill -9".

Now after sending all the required information, Oracle has filed a bug against the case 

We are waiting for Oracle's development team to work on this bug. I'll update this post when I'm successfully able to register the extract in the environment. 
Currently the database is running with threaded_execution=FALSE

Happy registering!!! :)

Update - 21 Sep 2017
Oracle has updated the ticket as below before closing

The following enhancement has been filed: 

Bug 26834790 - IE Support for db in THREADED_EXECUTION mode. 

So for now, to have GoldenGate to work with integrated extract one needs to set threaded_execution=FALSE.

Update - 06 Nov 2017
Enhancement  has been done. The fix for the bug is introduced in Oracle GoldenGate version
Hence if you are running OGG, then you can have threaded_execution=TRUE