Saturday, February 25, 2017

ODA X6-2 -- using ACFS snapshots for database backup and restore

ODA X6 uses ACFS as the filesystem for database files. (ACFS is the default, but ASM can also be used as well)
So, in this blog post, I will demonstrate an ACFS snapshot based database backup & restore operation.
Note that, creating oracle database files on ACFS is allowed in RDBMS 11.2.0.4 and in later releases.
In ODA X6-2 , we have 12C or 11.2.0.4 databases, so that we can benefit from ACFS features such as the snapshot functionality.

Here I m doing a demo ->


Checking the ACFS volumes:


ASMCMD> volinfo --all
Diskgroup Name: DATA

         Volume Name: COMMONSTORE
         Volume Device: /dev/asm/commonstore-74
         State: ENABLED
         Size (MB): 5120
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /opt/oracle/dcs/commonstore

         Volume Name: DATDEMO5
         Volume Device: /dev/asm/datdemo5-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMO5

         Volume Name: DATDEMODB1
         Volume Device: /dev/asm/datdemodb1-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB1

         Volume Name: DATDEMODB2
         Volume Device: /dev/asm/datdemodb2-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB2

         Volume Name: DATDEMODB3
         Volume Device: /dev/asm/datdemodb3-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB3

         Volume Name: DATDEMODB4
         Volume Device: /dev/asm/datdemodb4-74
         State: ENABLED
         Size (MB): 102400
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u02/app/oracle/oradata/DEMODB4

Diskgroup Name: RECO

         Volume Name: RECO
         Volume Device: /dev/asm/reco-296
         State: ENABLED
         Size (MB): 610304
         Resize Unit (MB): 64
         Redundancy: MIRROR
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /u03/app/oracle/
  • I have lots of ACFS volumes, including the one created for redologs (RECO). I choose to work with the ACFS volume named DATDEMO5, which is used by the DEMO5 database. The datafiles of the DEMO5 Database are located in DATDEMO5.
  • In order to test my ACFS snapshot based restore work, I create a table and drop it afterwards.But, jsut before dropping the table, I create an ACFS snapshot to be able to restore/rollback my database to a time before the table is dropped.


CREATING an ACFS SNAPSHOT:


[oracle@odademo ~]$ sqlplus "/as sysdba"SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 15:56:31 2017
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> create table erman as select * from dual;

Table created.

SQL> select * from erman;

D
-
X
  • At this point, I create my ACFS snapshot..  So my snapshot contains the table named erman.
[grid@odademo ~]$ acfsutil snap info /u02/app/oracle/oradata/DEMO5
    number of snapshots:  0
    snapshot space usage: 0  ( 0.00 )
[grid@odademo ~]$ acfsutil snap create ERMANSNAP  /u02/app/oracle/oradata/DEMO5
acfsutil snap create: Snapshot operation is complete.
[grid@odademo ~]$ acfsutil snap info /u02/app/oracle/oradata/DEMO5
snapshot name:               ERMANSNAP
snapshot location:           /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/ERMANSNAP
RO snapshot or RW snapshot:  RO
parent name:                 /u02/app/oracle/oradata/DEMO5
snapshot creation time:      Tue Feb 21 15:59:10 2017

    number of snapshots:  1
    snapshot space usage: 32768  (  32.00 KB )
  • After taking my snapshot, I drop the table named erman.
[oracle@odademo ~]$ . oraenv
ORACLE_SID = [oracle] ? DEMO5
The Oracle base has been set to /u01/app/oracle
[oracle@odademo ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 16:01:18 2017

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> drop table erman;

Table dropped.

SQL> select * from erman;
select * from erman
              *
ERROR at line 1:
ORA-00942: table or view does not exist
[grid@odademo ~]$ . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base has been set to /u01/app/grid
  • At this point, I have no table named erman in my DEMO5 database.

RESTORING THE DATABASE FROM THE SNAPSHOT:

  • As seen above, the table named erman is dropped and now I restore my database using the ACFS snapshot that I took before dropping the table.
  • I first create a read-write snapshot from the read-only snapshot that I took earlier.
[grid@odademo ~]$ acfsutil snap create -w -p ERMANSNAP RW_ERMANSNAP /u02/app/oracle/oradata/DEMO5
acfsutil snap create: Snapshot operation is complete.
[grid@odademo ~]$ acfsutil snap info /u02/app/oracle/oradata/DEMO5
snapshot name:               ERMANSNAP
snapshot location:           /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/ERMANSNAP
RO snapshot or RW snapshot:  RO
parent name:                 /u02/app/oracle/oradata/DEMO5
snapshot creation time:      Tue Feb 21 15:59:10 2017

snapshot name:               RW_ERMANSNAP
snapshot location:           /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP
RO snapshot or RW snapshot:  RW
parent name:                 ERMANSNAP
snapshot creation time:      Tue Feb 21 16:02:33 2017

    number of snapshots:  2
    snapshot space usage: 475136  ( 464.00 KB )
  • Then, I relink the DEMO5 directory residing in my related ACFS volume to the relevant snapshot path.
[root@odademo DEMO5]# cd /u02/app/oracle/oradata/DEMO5/DEMO5
[root@odademo DEMO5]# mv datafile datafile_old
[root@odademo DEMO5]# ln -s /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile /u02/app/oracle/oradata/DEMO5/DEMO5

[root@odademo DEMO5]# ls -al
total 40
drwxr-x--- 3 oracle asmadmin 8192 Feb 21 16:10 .
drwxrwxr-x 6 oracle oinstall 4096 Feb 21 16:08 ..
lrwxrwxrwx 1 root   root       66 Feb 21 16:10 datafile -> /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile
drwxr-x--- 2 oracle asmadmin 8192 Feb 20 16:48 datafile_old

[root@odademo DEMO5]# ls -al datafile
lrwxrwxrwx 1 root root 66 Feb 21 16:10 datafile -> /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile

[root@odademo DEMO5]# ls -al /u02/app/oracle/oradata/DEMO5/.ACFS/snaps/RW_ERMANSNAP/DEMO5/datafile
total 1773176
drwxr-x--- 2 oracle asmadmin      8192 Feb 20 16:48 .
drwxr-x--- 3 oracle asmadmin      8192 Feb 20 16:43 ..
-rw-r----- 1 oracle asmadmin 734011392 Feb 21 15:45 o1_mf_sysaux_dbowmlrw_.dbf
-rw-r----- 1 oracle asmadmin 754982912 Feb 21 15:52 o1_mf_system_dbownowh_.dbf
-rw-r----- 1 oracle asmadmin  72359936 Feb 20 22:48 o1_mf_temp_dbowpgm7_.tmp
-rw-r----- 1 oracle asmadmin 319823872 Feb 21 15:54 o1_mf_undotbs1_dbowoh07_.dbf
-rw-r----- 1 oracle asmadmin   5251072 Feb 20 16:48 o1_mf_users_dbowy2g2_.dbf
  • As seen in the above ls command outputs, the directory that my database file suppose to reside, is linked to the ACFS snapshot that I took earlier.
  • So, now I start my database to see if it can be started and see if the table that I dropped is now available.
[oracle@odademo ~]$ . oraenv
ORACLE_SID = [oracle] ? DEMO5
The Oracle base has been set to /u01/app/oracle
[oracle@odademo ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 21 16:15:21 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size                  2932632 bytes
Variable Size             889192552 bytes
Database Buffers         3372220416 bytes
Redo Buffers               30621696 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'/u02/app/oracle/oradata/DEMO5/DEMO5/datafile/o1_mf_system_dbownowh_.dbf'
  • My database requires recovery!!? Why? It is because I only created an ACFS snapshot for the datafile volume, I didn't create an ACFS snapshot for the RECO volume, in which my redologs reside.
  • Anyways, let's recover the database to show you that it can be opened, and the media recovery is working :)
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.
  • So , at this point, I have restored and recovered my database.  So I can say that Oracle database can be restored from an ACFS snapshot.
SQL> select * from erman;
select * from erman
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit

  • However, the table named erman is not there. As explained earlier, this is expected, because we did not restore the redolog files.. I mean the redologs where in a different ACFS volume, so our snapshot restore didn't restore the redologs, and during the recovery, our table is dropped..:) interesting, isnt it? 

CONCLUSION:


Anyways, this blog post proves not only 1 thing, but 2, actually 3 :) ->
1)"The ACFS snapshot based restore mechanism" works as expected
2)"Oracle's media recovery mechanism" works as expected
3)We can get benefit from ACFS snapshot features even for the databases running on ODA platforms.

ODA X6-2-- decreasing the CPU core count

The command syntax that we use for decreasing the Cpu core count of ODA is not so different than the command syntax that we use for increasing it.

There is an important that we need to know for decreasing the CPU core count in ODA.
That is -> It is not supported! :)

Here; I try to decrease the cpu core count of an ODA from 8 to 4 and I get "reduction in number of cores is not supported"

[root@odademo ~]# odacli describe-cpucore

Node  Cores  Modified                       Job Status    
----- ------ ------------------------------ ---------------
0     8      February 23, 2017 12:23:26 PM EET Configured    

[root@odademo ~]# odacli update-cpucore --cores 4
reduction in number of cores is not supported

However, there is force argument which makes us force this action.

Here ->

[root@odademo ~]# odacli update-cpucore --cores 4 --force
{
  "jobId" : "9d294bfd-e582-4484-a75c-621b4ac1c2ca",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "February 25, 2017 10:46:59 AM EET",
  "description" : "CPU cores  service update",
  "updatedTime" : "February 25, 2017 10:46:59 AM EET"
}

[root@odademo ~]# odacli describe-cpucore

Node  Cores  Modified                       Job Status    
----- ------ ------------------------------ ---------------
0     4      February 25, 2017 10:47:02 AM EET Configured

So, using the "--force" argument, we can decrease the Cpu core count of an ODA X6.
However since it is not supported, I don't recommend to use it!
In my opinion, this can only be used after the first deployment, before we start using the machine.

ODA X6-2 , Increasing the CPU Core count in ODA

Capacity on Demand is not a new option for ODA. It was there earlier as well.
However, the new CLI (ODACLI) is introduced with ODA X6 and that's why I m writing this blog post.
Increasing  the CPU core count is a little different the earlier . (OAKCLI)

Note that, I did a test on an ODA X6-2M and  created the following demo/example to show you the command / syntax to be used for increasing the core count in ODA is as follows;

First I check, the current cpu cores.

[root@oak bin]# ./odacli describe-cpucore 
Node  Cores  Modified                       Job Status     
----- ------ ------------------------------ ---------------
0     8     February 20, 2017 12:10:18 PM EET Configured   

[root@odademo bin]# cat /proc/cpuinfo|grep processor| wc -l (Checking it from the OS)

16 (this is becauase of Hyper threading)

Then, I update the cpu cores ->

[root@oak bin]# ./odacli update-cpucore --cores 12
{
  "jobId" : "b53d8ab8-c59a-48bf-805f-fe1c477d6e6a",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "February 20, 2017 14:57:44 PM EET",
  "description" : "CPU cores  service update",
  "updatedTime" : "February 20, 2017 14:57:44 PM EET"
}

I check the running jobs to ensure my action is successfuly.

[root@oak bin]# ./odacli list-jobs

ID             Description                                 Created                             Status    
---------------------------------------- --------------------------------------------------------------------------- 
...
4d8bb3d     Provisioning service creation   February 20, 2017 2:01:05 PM EET    Success   
b53d8ab8-c59a     CPU cores  service update  February 20, 2017 2:57:44 PM EET    Success   

Lastly, I check the current cpu cores once again, to ensure my cpu core count is updated to 12.

[root@oak bin]# ./odacli describe-cpucore

Node  Cores  Modified                       Job Status     
----- ------ ------------------------------ ---------------
0     12     February 20, 2017 2:57:45 PM EET Configured   

[root@odademo ~]# cat /proc/cpuinfo|grep processor| wc -l (Checking it from the OS)

24 (this is becausae of Hyper Threading, it means 12 Cpus cores -- as expected)

ODA X6-2M -- the initial deployment / reimaging / OS reimage / Deployment of GRID + RDBMS

Recently I gave a presentation on ODA X6-2 and EBS. After the presentation, I also did a demo, and for that demo, I used the ODA X6-2M machine deployed in my office a few days ago.


I did the deployment by taking the notes and screenshots and here I m sharing it with you.
I hope you will find this useful.

Note that, my goal was to reinstall/reimage the ODA X6-2M, because it was already deployed earlier for another demo and the IP addresses configured in it, were not suitable for our network.
Note that, we can't update the public and private networks using "odacli update-network" command, once the GI Home is deployed to our ODA. It is not allowed as seen in the picture below;



  • First, I have dowloaded the OS image from Oracle Support. I downloaded the OS image using "Oracle Database Appliance X6-2S , X6-2M and X6-2 L (Doc ID 2144642.1)" The OS image is -> Patch 23530609
  • Then I deployed the OS image using : ODA Oracle Database Appliance Bare Metal Restore Procedure X5-2 and X6-2 (Doc ID 1373599.1) In order to be able to use ILOM, I changed ILOM ip address using BIOS. (pressing F2 during the boot) (note that: ILOM connect info is root/changeme) .. We see the ILOM ip address change in the following picture;


             Once the ILOM is configured, the OS reimaging is very straigth forward actually.
             We see the ODA X6-2 OS deployment/reimaging in the following pictures.






  • After the deployment OS , I could connect to the OS using ssh. In my case, the root password after deployment is "welcome1". After OS reimaging, I login as root and executed configure-firstnet command to configure btbond1 . After this point, our ODA became available via the network.
  • Next, I downloaded GI, 12C home and 11g home patches and upload them to /tmp
  • I updated our repository with the patches->

        23494985 12.1.0.2.161018 Grid Infrastructure Software
        23494992 12.1.0.2.161018 Database Software
        23494997 11.2.0.4.161018 Database Software

          Actually, I have used the installation zip files that are delivered with ODA X6-2M.
          So I didn't need to download any patches, but still I downloaded those patches.

  • The installation zip files for 12C GI Home, 11.2.0.4 and 12cr2 RDBMS Homes were delivered with ODA. They were located in the directory named /opt/oracle/oak. So, I uploaded them to the ODA repository to make them available for the deployment.

Example:

cd /opt/oracle/oak
[root@oak oak]# /opt/oracle/dcs/bin/odacli update-repository -f /opt/oracle/oak/oda-sm-12.1.2.9.0-161116-GI-12.1.0.2.zip
{
  "jobId" : "388e8183-099e-46dd-bc10-9551ede0f137",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "February 20, 2017 03:56:38 AM PST",
  "description" : "Repository Update",
  "updatedTime" : "February 20, 2017 03:56:38 AM PST"
}

[root@oak oak]# /opt/oracle/dcs/bin/odacli update-repository -f /opt/oracle/oak/oda-sm-12.1.2.9.0-161007-DB-11.2.0.4.zip 
{
  "jobId" : "5779252a-8690-4d7f-a36b-8dcca84de1dc",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "February 20, 2017 03:57:26 AM PST",
  "description" : "Repository Update",
  "updatedTime" : "February 20, 2017 03:57:26 AM PST"
}
[root@oak oak]# /opt/oracle/dcs/bin/odacli update-repository -f /opt/oracle/oak/oda-sm-12.1.2.9.0-161116-DB-12.1.0.2.zip 
{
  "jobId" : "fe5e6ed6-5e3c-46e9-9ad5-473baadf0679",
  "status" : "Created",
  "message" : null,
  "reports" : [ ],
  "createTimestamp" : "February 20, 2017 03:57:53 AM PST",
  "description" : "Repository Update",
  "updatedTime" : "February 20, 2017 03:57:53 AM PST"
}

         Note: this repository is under: /opt/oracle/oak/pkgrepos/orapkgs/

  • After I updated the repository, I got the following files there.. (as expected)

[root@oak clones]# ls -lrt
total 10002900
-rw-r--r-- 1 root root 2338547847 Oct  9 00:44 db112.161018.tar.gz
-rwxr-xr-x 1 root root 3554399076 Nov 17 11:06 db121.161018.tar.gz
-rwxr-xr-x 1 root root 4339995616 Nov 17 11:18 grid121.tar.gz
[root@oak clones]# pwd
/opt/oracle/oak/pkgrepos/orapkgs/clones

  • At that point, my ODA was ready for GI and Oracle Home deployment and I did the deployment using the management interface as shown in the following screenshots..( I only clicked the "Create Appliance" button :).  Note that: the management interace becomes available after the OS imaging + network configuration, and it is running from port 7093 by default. (url: https://ODA_OS_IP:7093/mgmt/index.html)






One important note for "Creating The Appliance": 
While creating the appliance ensure that you select your desired Database edition (Enterprise or Standard...)

This is because! ->  You cannot mix editions. The database edition you select on this page determines the database editions that you create in the appliance. To change editions, you must redeploy Oracle Database Appliance.

Well.. After this point, our ODA X6-2M was ready for demo.
I tested the machine and the Grid environment deployed on it by doing the following things and they all performed well.

Connecting to the ILOM Web Interface and checked out what can done there.
Connecting to the Appliance Manager Web interface and checked out what can done there.
Created 12C and 11.2.04 databases using Appliance Manager Web interface
Checked the running jobs(ODA jobs) from the Appliance Manager Web interface
Deleted a database using Appliance Manager Web interface
Showed the Appliance Manager ASR tab. (Automatic Service Request)
Showed the Enterprise Manager Express that can be installed by the Appliance Manager during the database deployment.
Updated the ODA cpu core counts using odacli  (Capacity on Demand)
Playing with the ACFS snapshot (creating ACFS snapshots, restoring an Oracle Database using ACFS snapshots)

Well.... That 's all for this topic. See you in my next article :)

Monday, February 20, 2017

ODA / EBS -- Oracle Database Appliance Portfolio, EBS on ODA, new ODA X6-2

Hello everyone,

I 'm speaking at Oracle Database Appliance Event in Istanbul!
Time & Place Double Tree by Hilton Moda , 23 Feb 2017 , from 10 AM to 2:30 PM (GMT+3)
I will present EBS on ODA, ODA High Availability and do a live demo to show the new ODA X6-2.
The presentation will be given in Turkish, but still you can drop by if you are in the area.

Click here for registration.

Merhabalar,

23 Şubat 2017 de Double Tree by Hilton Moda 'da yapılacak ODA etkinliğinde konuşmacıyım.
Etkinlikte, ODA 'nın EBS 'e faydaları ve yüksek erişilebilirlik konularını anlatacağım. Ayrıca yeni nesil bir ODA X6 medium cihazda canlı bir demo yapacağım.
Detayları aşağıdaki davetiyede bulabilirsiniz.

Tuesday, February 14, 2017

EBS 11i -- enabling debug for Oracle HTTP Server/Apache

In this post, I will give you the things that need to be done for enabling debug on Oracle HTTP Server, also known as Apache in EBS 11i.

Note that, this enablement can also be used to enable Jserv debugging. (Note that , Jserv is replaced by OC4J in EBS R12 and OC4J is replaced by Oracle Weblogic Server in EBS 12.2)

Setting up detailed logging on the web server:

WITH APPLMGR (APPL OS OWNER) user:
---------------------------------------------------------->
"Stop Apache"

cd $COMMON_TOP/admin/scripts/<CONTEXT_NAME>
[devappl@krbtest DEV_krbtest]$ sh adapcctl.sh stop

"Delete log files for having a clean start"

cd $IAS_ORACLE_HOME/Apache/Apache/logs
rm -r *
cd $IAS_ORACLE_HOME/Apache/Jserv/logs/jvm
rm -r *
cd $IAS_ORACLE_HOME/Apache/Jserv/logs
rm *

"Modify  $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.conf" and update ApJServLogLevel to be set to debug : "ApJServLogLevel debug"

"Modify the $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties and enabling logging"

Change "log=false" to be "log=true"
Change "log.channel=false" to "log.channel=true"
Modify the following if they are not already set ->

log.timestamp=true
log.channel.info=true
log.channel.servletException=true
log.channel.jservException=true
log.channel.warning=true
log.channel.servletLog=true
log.channel.critical=true
log.channel.debug=true
wrapper.bin.parameters=-DOXTALogDebugMsg=true

"Modify the $IAS_ORACLE_HOME/Apache/Apache/conf/httpd.conf and $IAS_ORACLE_HOME/Apache/Apache/conf/httpd_pls.conf files and enabling detailed logging"
modify LogLevel to be set to info -> "LogLevel debug"

"Modify the: $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsrv.app and add the line:
debugModules=Info immediately after the line: custom_auth = CUSTOM

*Modify  $IAS_ORACLE_HOME/Apache/Jserv/etc/zone.properties to enable debug for framework provider.
servlet.framework.initArgs=debuglevel -> should be set to 1, i.e : servlet.framework.initArgs=debuglevel=1

"Start Apache"

cd $COMMON_TOP/admin/scripts/<CONTEXT_NAME>
[devappl@krbtest DEV_krbtest]$ sh adapcctl.sh start

Monday, February 6, 2017

RDBMS -- ORA-00600: internal error code, arguments: [kqlInvObj:user]

Recently, one of my followers encountered  an ORA-00600 and asked me the solution for it using my forum.

The full error was "ORA-00600: internal error code, arguments: [kqlInvObj:user], [104], [], [], []" and it was encountered while issuing a simple create table statement like the one below;

SQL> CREATE TABLE temp_sales
2 ( ID number(25),
3 PNAME varchar2(23),
4 SALE_date date)
5 PARTITION BY RANGE (SALE_date)
6 (
7 PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-jan-2016','dd-MON-yyyy')),
8 PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-feb-2016','dd-MON-yyyy')),
9 PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')));
CREATE TABLE temp_sales

I see a need for sharing this info, because it is not documented in Oracle Support, or let's say it is not properly documented there.

Anyways, the first argument of ORA-600 error is normally the function, where the code is breaking. 

So in this ORA-600, Oracle was getting an exception during "kqlInvObj:user", which is not documented at all.

That's why I thought that this was caused by a bug  and this bug was related with some problematic records in sys.obj$ table.  (The second argument of this error (specific to kqlInvObj:user) is the owner of the object.)

So, the second argument "104" was the owner# which was stored in sys.obj$. 

Using the following query, I found the objects that belong to the user with the user number 104;

select * from sys.obj$ where owner#=104

Actually, I was going to recommend dropping/recreating/recompiling the objects owned by the related database user, but the query returned lots of records, so I made a deeper rearch and found the following bug ;

Bug 10256218 : IMPDP FAILS WITH ORA 600[KQLINVOBJ:USER], [94].

There was a workaround written in the definition of this bug and I instructed my follower to try it (after taking a full backup)

The workaround worked and the problem dissapeared!

The workaround given was the following;

1. SHUTDOWN IMMEDIATE or NORMAL
2. STARTUP RESTRICT
3. Create a new user called PATCH_USER:

create user PATCH_USER identified by p;

4. Update obj$
update sys.obj$
set owner# = (select user#
from sys.user$
where name = 'PATCH_USER')
where owner# in (88,94);   --REPLACE With your numbers

5. COMMIT;
6. Shutdown abort;
7. STARTUP
8. Drop user created in step 3:

drop user PATCH_USER cascade;

Attention: This is too risky! Requires a full backup as well.

Tuesday, January 31, 2017

EBS 12.2 - About Java Visual VM -- jvisualvm

Here is very interesting tool that comes by default with the EBS 12.2 installation.
It is called Java VisualVM(jvisualvm) and it is a GUI that gives us detailed info about Java applications running on a given JVM.
Using jvisualvm, we can do monitoring, troubleshooting, and profiling. For example, we can check heap sizes of oacore server, we can trigger garbage collector or we dump java threads.
In EBS 12.2, the jvisualvm is located under $COMMON_TOP.

We can use it via a Vnc session. All we have to do is to execute "$COMMON_TOP/util/jdk64/bin/jvisualvm"
                                                               
  Java Visual VM initializing...

Monitoring Tab for Oacore Server

Check Java SE documentation for further info:

Monday, January 30, 2017

EBS -- Excessive redo/archive generation due to "Initialization SQL Statement - Custom"

A few days ago, I analyzed an excessive redo generation problem in an EBS 12.2 environment which was recently upgraded from 11i.

The problem was the redo generation, as more than 100Gbs of Redo was generated per day and this has started to become a critical problem since, the dataguard transport was saturating the network.

When we analyzed the generated redologs/archives using Log Miner, we saw the following;
/* No SQL_REDO for temporary tables */
This comment is normally seen when there is a redo generated for a temporary table, so there must be an index on it. Becauase, Oracle don't generate redo  for temporary tables unless there are indexes on them.

In order to find the table, we used the following query;

SELECT sql_redo, table_name, COUNT (*), operation_code
FROM v$logmnr_contents
GROUP BY sql_redo, table_name,operation_code
HAVING COUNT (*) > 10

--operation_code=1 -> insert
--operation_code=2 -> delete
--operation_code=3 -> update

--It was the delete operation causing excessive redo in our case...

So, at the end we concluded the table GL_BIS_SEGVAL_INT for the cause. (the count for GL_BIS_SEGVAL_INT in table v$logmnr_contents was much higher than the others)

The redologs and archives which were created during the problematic period was containing the redo for GL_BIS_SEGVAL_INT.

Using this info, we reached the document named: Table GL.GL_BIS_SEGVAL_INT Generates a Lot of Archive Redo (Doc ID 2161480.1)

The solution suggested in the document was -> setting "Initialization SQL Statement - Custom" to NULL at site level (remove value BEGIN GL_SECURITY_PKG.INIT; END;).

When we checked the  Initialization SQL Statement - Custom, we saw that we had the following set there;

BEGIN IF (fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL') = 0) THEN mo_global.init ('S'); ELSE GL_SECURITY_PKG.init (); mo_global.init ('M'); END IF;END;

This profile was set in order to make Discoverer work properly with MOAC (Multiple Organization Access Control) and Developers didn't want to set it null, as it would affect the Discoverer.

So, rather than setting the "Initialization SQL Statement - Custom" to null, we set it to the following
->

BEGIN IF (fnd_profile.VALUE ('XLA_MO_SECURITY_PROFILE_LEVEL') = 0) THEN mo_global.init ('S'); ELSE mo_global.init ('M'); END IF;END;

So, we only remove the GL_SECURITY_PKG.init () from the profile and with this setting; Discoverer could continue to work and the archive/redo generation dramatically decreased and the system went back to normal.

The cause seemed to be the  the GL_SECURITY_PKG.INIT, as it had a delete statement inside "delete from GL_BIS_SEGVAL_INT" and it had insert statement as well..

 sql_stmt := 'INSERT INTO GL_BIS_SEGVAL_INT( ' ||
                    'segment_column_name,' ||
                    'segment_value,      ' ||
                    'parent_segment) ' ||
                    'SELECT ''' || segment_column_name || ''',' ||
                    'flex_value, parent_flex_value_low ' ||
                    'FROM FND_FLEX_VALUES ' ||
                    'WHERE flex_value_set_id=' || value_set_id;

So , these statements were executed in every login (the profile was set in site level) and as the GL_BIS_SEGVAL_INT temporary table had index on it, we ended up with lots of redo generated..
Anways, we implemented the solution and now all is fine :) 
Interesting isn't it :) Sessions doing dml on Global tables do generate redo ( undo for table(just a small amount) + undo for indexes), and setting  "Initialization SQL Statement - Custom" profile to a standard initialization routine can create a big throuble...