EM Express Login – Get Flash

I have 12c setup on OEL 6.7 on  OVM and while trying to access EM express, I was receiving the error

em_express_error

Just for the notes, a user can log into EM Express using URL

https://<hostname>:<port>/em

and port details can be found using

lsnrctl status | grep HTTP
or  
select dbms_xdb_config.getHttpsPort() from dual;

Clicking on the “Get Flash” takes you to abode flash page from where you can download the rpm for flash.

[root@oel67-12c Downloads]# ls -lrt
-rw-r--r-- 1 root root 7270732 May 30 11:43 flash-plugin-11.2.202.621-release.x86_64.rpm
[root@oel67-12c Downloads]#
[root@oel67-12c Downloads]# rpm -ivh flash-plugin-11.2.202.621-release.x86_64.rpm
Preparing... ########################################### [100%]
1:flash-plugin ########################################### [100%]
[root@oel67-12c Downloads]#

After installing the rpm I still received the same error.So lets debug

[root@oel67-12c Downloads]# cd /usr/lib64/mozilla/plugins/
[root@oel67-12c plugins]# ls -lrth
total 368K
-rwxr-xr-x. 1 root root 5.2K Jul 25  2010 librhythmbox-itms-detection-plugin.so
-rwxr-xr-x. 1 root root  78K Oct 28  2013 libtotem-narrowspace-plugin.so
-rwxr-xr-x. 1 root root  70K Oct 28  2013 libtotem-mully-plugin.so
-rwxr-xr-x. 1 root root 105K Oct 28  2013 libtotem-gmp-plugin.so
-rwxr-xr-x. 1 root root  97K Oct 28  2013 libtotem-cone-plugin.so
lrwxrwxrwx  1 root root   41 May 30 11:43 libflashplayer.so -> /usr/lib64/flash-plugin/libflashplayer.so

[root@oel67-12c plugins]# ls -lrt /usr/lib64/flash-plugin/libflashplayer.so
-rwxr-xr-x 1 root root 0 Apr 26 20:10 /usr/lib64/flash-plugin/libflashplayer.so

A zero byte file, something is wrong!!! I downloaded the .tar.gz version of the file from adobe and after untar

[root@oel67-12c Downloads]# ls -lrt
total 48064
drwxr-xr-x  6 1001  1001     4096 Apr 26 20:10 usr
-r--r--r--  1 1001  1001     4009 Apr 26 20:10 readme.txt
-rw-r--r--  1 1001  1001 19314752 Apr 26 20:10 libflashplayer.so
drwxr-xr-x  2 1001  1001     4096 Apr 26 20:10 LGPL
-rw-r--r--  1 root root   7270732 May 30 11:43 flash-plugin-11.2.202.621-release.x86_64.rpm
-rw-r--r--  1 root root  20408320 May 30 11:51 install_flash_player_11_linux.x86_64.tar

Hmmm, so the file is ~18MB. Lets copy the file to /usr/lib64/mozilla/plugins/ and change the permissions

[root@oel67-12c plugins]# cp -p /root/Downloads/libflashplayer.so .
[root@oel67-12c plugins]# ls -lrt
total 19232
-rwxr-xr-x. 1 root root     5264 Jul 25  2010 librhythmbox-itms-detection-plugin.so
-rwxr-xr-x. 1 root root    79752 Oct 28  2013 libtotem-narrowspace-plugin.so
-rwxr-xr-x. 1 root root    70904 Oct 28  2013 libtotem-mully-plugin.so
-rwxr-xr-x. 1 root root   106984 Oct 28  2013 libtotem-gmp-plugin.so
-rwxr-xr-x. 1 root root    99176 Oct 28  2013 libtotem-cone-plugin.so
-rw-r--r--  1 1001 1001 19314752 Apr 26 20:10 libflashplayer.so
[root@oel67-12c plugins]# chown root:root libflashplayer.so
[root@oel67-12c plugins]# chmod 775 libflashplayer.so

And, now lets refresh the page and there you go !!!

em_express

 

Advertisements

Fixed Table x$ktfbue has not statistics

While playing around 12.1.0.2 db, I ran my usual script to check tablespace usage details and it took ~59 secs (Elapsed: 00:00:59.02), which was high.
Ran the sql using “gather_plan_statistics” hint and below lines in the execution plan were of interest –

|  34 |      HASH GROUP BY                     |                    |      1 |      6 |      3 |00:00:58.68 |     129K|  54801 |  1160K|  1160K|  758K (0)|
|  35 |       VIEW                             | DBA_FREE_SPACE     |      1 |  21019 |     13 |00:00:00.01 |     129K|  54801 |       |       |          |
|  36 |        JOIN FILTER USE                 | :BF0000            |      1 |        |     13 |00:00:00.01 |     129K|  54801 |       |       |          |
|  37 |         UNION-ALL                      |                    |      1 |        |     19 |00:00:00.01 |     129K|  54801 |       |       |          |
|  38 |          NESTED LOOPS                  |                    |      1 |      1 |      0 |00:00:00.01 |       7 |      0 |       |       |          |
|  39 |           NESTED LOOPS                 |                    |      1 |      1 |      0 |00:00:00.01 |       7 |      0 |       |       |          |
|* 40 |            INDEX FULL SCAN             | I_FILE2            |      1 |      4 |      4 |00:00:00.01 |       1 |      0 |       |       |          |
|* 41 |            TABLE ACCESS CLUSTER        | FET$               |      4 |      1 |      0 |00:00:00.01 |       6 |      0 |       |       |          |
|* 42 |             INDEX UNIQUE SCAN          | I_TS#              |      4 |      1 |      4 |00:00:00.01 |       2 |      0 |       |       |          |
|* 43 |           TABLE ACCESS CLUSTER         | TS$                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 44 |            INDEX UNIQUE SCAN           | I_TS#              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  45 |          NESTED LOOPS                  |                    |      1 |     10 |     10 |00:00:00.01 |      25 |      0 |       |       |          |
|  46 |           NESTED LOOPS                 |                    |      1 |     10 |     10 |00:00:00.01 |      21 |      0 |       |       |          |
|* 47 |            TABLE ACCESS FULL           | TS$                |      1 |      4 |      4 |00:00:00.01 |       9 |      0 |       |       |          |
|* 48 |            FIXED TABLE FIXED INDEX     | X$KTFBFE (ind:1)   |      4 |      2 |     10 |00:00:00.01 |      12 |      0 |       |       |          |
|* 49 |           INDEX UNIQUE SCAN            | I_FILE2            |     10 |      1 |     10 |00:00:00.01 |       4 |      0 |       |       |          |
|  50 |          NESTED LOOPS                  |                    |      1 |  21007 |      9 |00:00:57.85 |     129K|  54801 |       |       |          |
|* 51 |           HASH JOIN                    |                    |      1 |      7 |      9 |00:00:00.01 |      13 |      0 |  1115K|  1115K|  471K (0)|
|  52 |            NESTED LOOPS                |                    |      1 |      7 |      9 |00:00:00.01 |      12 |      0 |       |       |          |
|  53 |             NESTED LOOPS               |                    |      1 |     32 |      9 |00:00:00.01 |      11 |      0 |       |       |          |
|* 54 |              TABLE ACCESS FULL         | TS$                |      1 |      4 |      4 |00:00:00.01 |       9 |      0 |       |       |          |
|* 55 |              INDEX RANGE SCAN          | RECYCLEBIN$_TS     |      4 |      8 |      9 |00:00:00.01 |       2 |      0 |       |       |          |
|  56 |             TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$        |      9 |      2 |      9 |00:00:00.01 |       1 |      0 |       |       |          |
|* 57 |            INDEX FULL SCAN             | I_FILE2            |      1 |      4 |      4 |00:00:00.01 |       1 |      0 |       |       |          |
|* 58 |           FIXED TABLE FULL             | X$KTFBUE           |      9 |   3125 |      9 |00:00:58.67 |     129K|  54801 |       |       |          |
|  59 |          NESTED LOOPS                  |                    |      1 |      1 |      0 |00:00:00.01 |      21 |      0 |       |       |          |
|  60 |           NESTED LOOPS                 |                    |      1 |      1 |      0 |00:00:00.01 |      21 |      0 |       |       |          |

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 3 Sql Plan Directives used for this statement

The E-Rows for X$KTFBUE is 3125 whereas A-Rows 9. Checking the stats on the table

SYS@orcl:1> set serveroutput on
SYS@orcl:1>
SYS@orcl:1>
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OWNER                         : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
GLOBAL_STATS                  :
USER_STATS                    :
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
-----------------

Looking at the directive details shows missing details on “F(SYS.TS$) – F(SYS.UNDO$) – F(SYS.X$KTFBUE)”

SYS@orcl:1> exec  print_table('select * from DBA_SQL_PLAN_DIR_OBJECTS where owner=''SYS'' and object_name=''X$KTFBUE'' ');
DIRECTIVE_ID                  : 16383234857226438245
OWNER                         : SYS
OBJECT_NAME                   : X$KTFBUE
SUBOBJECT_NAME                :
OBJECT_TYPE                   : TABLE
NOTES                         : NONONOYES
-----------------

PL/SQL procedure successfully completed.

SYS@orcl:1> exec  print_table('select * from DBA_SQL_PLAN_DIRECTIVES where DIRECTIVE_ID=''16383234857226438245'' ');
DIRECTIVE_ID                  : 16383234857226438245
TYPE                          : DYNAMIC_SAMPLING
ENABLED                       : NO
STATE                         : USABLE
AUTO_DROP                     : YES
REASON                        : JOIN CARDINALITY MISESTIMATE
CREATED                       : 28-MAY-16 09.21.20.000000 PM
LAST_MODIFIED                 : 28-MAY-16 11.12.20.000000 PM
LAST_USED                     : 28-MAY-16 10.21.29.000000000 PM
NOTES                         : MISSING_STATSNO{F(SYS.TS$) - F(SYS.UNDO$) - F(SYS.X$KTFBUE)}
-----------------

PL/SQL procedure successfully completed.

Lets gather fixed object stats :-

SYS@orcl:1> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:02:47.92

Ran the script again and it still took the same time, so checked the stats on X$KTFBUE and its still missing

SYS@orcl:1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OWNER                         : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
GLOBAL_STATS                  :
USER_STATS                    :
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
-----------------

PL/SQL procedure successfully completed.

Reviewed few documents

Query Against DBA_EXTENTS Performs Slowly After Upgrade to 11.2.0.3 (Doc ID 1453425.1)
Bug 5259025 – The fixed table x$ktfbue has no statistics (Doc ID 5259025.8)

Gathered the stats on fixed table x$ktfbue

SYS@orcl:1>  exec DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTFBUE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.09
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OWNER                         : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      : 9581
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   : 47
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   : 9581
LAST_ANALYZED                 : 28-may-2016 22:55:47
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
-----------------

Re-ran the sql script and Elapsed: 00:00:00.41

Oracle ASMLib

This blog post is more of a note for myself on configuring ASMLib.

ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.

ASMLib consists of the following components:

An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib

On my Oracle VMBox, I performed the below steps –

1. Check the installed packages related to oracleasm

[root@oel67-12c dev]# rpm -qa | grep oracleasm
[root@oel67-12c dev]#

2. As no rpm were installed, I started installing the packages

[root@oel67-12c dev]# yum install oracleasm-support
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracleasm-support.x86_64 0:2.1.8-1.el6 will be installed
--> Finished Dependency Resolution
..........................
Installed:
  oracleasm-support.x86_64 0:2.1.8-1.el6

Complete!
[root@oel67-12c dev]#
[root@oel67-12c dev]# yum install kmod-oracleasm
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package kmod-oracleasm.x86_64 0:2.0.8-13.el6_8 will be installed
--> Processing Dependency: kernel >= 2.6.32-642.el6 for package: kmod-oracleasm-2.0.8-13.el6_8.x86_64

...........................
Is this ok [y/N]: y
Downloading Packages:
(1/2): kernel-2.6.32-642.el6.x86_64.rpm
(2/2): kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm   

...........................

Installed:
  kmod-oracleasm.x86_64 0:2.0.8-13.el6_8

Dependency Installed:
  kernel.x86_64 0:2.6.32-642.el6

Complete!
[root@oel67-12c dev]#

3. Download the oracleasmlib rpm from http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html and install it. Without this RPM, you will not have the oracleasm-discover executable


[root@oel67-12c dev]# ls -lrt /media/sf_12cR1/oracleasmlib*
-rwxrwx--- 1 root vboxsf    13336 May 18 21:44 oracleasmlib-2.0.12-1.el6.x86_64.rpm

[root@oel67-12c sf_12cR1]# rpm -ihv oracleasmlib-2.0.12-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]
[root@oel67-12c sf_12cR1]#

4. Verify all the components are installed

[root@oel67-12c sf_12cR1]# rpm -qa | grep oracleasm
oracleasm-support-2.1.8-1.el6.x86_64
oracleasmlib-2.0.12-1.el6.x86_64
kmod-oracleasm-2.0.8-13.el6_8.x86_64
[root@oel67-12c sf_12cR1]# cd 
[root@oel67-12c ~]# which oracleasm
/usr/sbin/oracleasm
[root@oel67-12c ~]#

5. Configure oracleasm — will configure the on-boot properties of the Oracle ASM library driver as well as set the appropriate file permissions.

[root@oel67-12c dev]# oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
[root@oel67-12c dev]#

The configuration setting done above, can be checked in /etc/sysconfig/oracleasm

[root@oel67-12c dev]# ls -lrt /etc/sysconfig/oracleasm
lrwxrwxrwx 1 root root 24 May 18 22:02 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

6. Check the status

[root@oel67-12c ~]# oracleasm status
Checking if ASM is loaded: no
Checking if /dev/oracleasm is mounted: no
[root@oel67-12c ~]#

7. Load oracleasm module with the init command

[root@oel67-12c ~]# oracleasm init
Creating /dev/oracleasm mount point: /dev/oracleasm
Loading module "oracleasm": oracleasm
Configuring "oracleasm" to use device physical block size
Mounting ASMlib driver filesystem: /dev/oracleasm
[root@oel67-12c ~]#

8. Verify the oracleasm configuration

a. Perform ‘df –ha’, this should show you that /dev/oracleasmfs is mounted.


[root@oel67-12c ~]# df -ha
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_oel6712c-lv_root
                       45G  7.4G   36G  18% /
....................
oracleasmfs              0     0     0    - /dev/oracleasm

b. Execute the ‘lsmod’ command, to show the loaded oracleasm module.

[root@oel67-12c ~]# lsmod | grep oracleasm
Module                  Size  Used by
oracleasm              53591  1

9. Create the partition on disk , if not already done

[root@oel67-12c oracleasm]# lsblk
NAME                           MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                              8:0    0   50G  0 disk
├─sda1                           8:1    0  500M  0 part /boot
└─sda2                           8:2    0 49.5G  0 part
  ├─vg_oel6712c-lv_root (dm-0) 252:0    0 45.6G  0 lvm  /
  └─vg_oel6712c-lv_swap (dm-1) 252:1    0  3.9G  0 lvm  [SWAP]
sdb                              8:16   0   10G  0 disk
sdc                              8:32   0   12G  0 disk
sdd                              8:48   0   12G  0 disk
sde                              8:64   0   10G  0 disk
sdf                              8:80   0   12G  0 disk
sr0                             11:0    1 55.5M  0 rom  /media/VBOXADDITIONS_5.0.20_106931


[root@oel67-12c disks]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x50e6a869.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4):
Value out of range.
Partition number (1-4): 1
First cylinder (1-1305, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1305, default 1305):
Using default value 1305

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@oel67-12c disks]#
[root@oel67-12c disks]#
[root@oel67-12c disks]#
[root@oel67-12c disks]# lsblk
NAME                           MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                              8:0    0   50G  0 disk
├─sda1                           8:1    0  500M  0 part /boot
└─sda2                           8:2    0 49.5G  0 part
  ├─vg_oel6712c-lv_root (dm-0) 252:0    0 45.6G  0 lvm  /
  └─vg_oel6712c-lv_swap (dm-1) 252:1    0  3.9G  0 lvm  [SWAP]
sdb                              8:16   0   10G  0 disk
└─sdb1                           8:17   0   10G  0 part
sdc                              8:32   0   12G  0 disk
sdd                              8:48   0   12G  0 disk
sde                              8:64   0   10G  0 disk
sdf                              8:80   0   12G  0 disk
sr0                             11:0    1 55.5M  0 rom /media/VBOXADDITIONS_5.0.20_106931

Perform the same above steps for remaining disk.

10. Once the disk are created, label the disks as ASM disks and give then an ASM name.


[root@oel67-12c disks]# oracleasm createdisk data0101 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oel67-12c disks]# oracleasm createdisk data0102 /dev/sde1
Writing disk header: done
Instantiating disk: done
[root@oel67-12c disks]# ls -lrt
total 0
brw-rw---- 1 oracle oinstall 8, 17 May 18 22:49 DATA0101
brw-rw---- 1 oracle oinstall 8, 65 May 18 22:50 DATA0102

11. Check the configured disk using listdisks

[root@oel67-12c disks]# oracleasm listdisks
DATA0101

12. You can query the disk to check if its a valid ASM Disk

[root@oel67-12c disks]# oracleasm querydisk -d DATA0101
Disk "DATA0101" is a valid ASM disk on device [8,17]

[root@oel67-12c disks]# more /proc/partitions
major minor  #blocks  name
   8    17   10482381 sdb1

[root@oel67-12c sbin]# blkid|grep asm
/dev/sdb1: LABEL="DATA0101" TYPE="oracleasm"
/dev/sde1: LABEL="DATA0102" TYPE="oracleasm"
[root@oel67-12c sbin]#

The [8,17] is the major,minor number of the device with this partition. The value comes from /proc/partitions

major — The major number of the device with this partition. The major number in the /proc/partitions, (3), corresponds with the block device ide0, in /proc/devices.

minor — The minor number of the device with this partition. This serves to separate the partitions into different physical devices and relates to the number at the end of the name of the partition.

#blocks — Lists the number of physical disk blocks contained in a particular partition.

name — The name of the partition.

13. oracleasm-discover


[root@oel67-12c sbin]# oracleasm-discover
-bash: /usr/sbin/oracleasm-discover: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

For the above error install/lib/ld-linux.so.2

[root@oel67-12c ~]# yum install /lib/ld-linux.so.2
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
public_ol6_latest/filelists                                                                                                                                                             |  61 MB     00:43
Resolving Dependencies
--> Running transaction check
---> Package glibc.i686 0:2.12-1.192.el6 will be installed
Dependencies Resolved

..................
Complete!
[root@oel67-12c ~]#

[root@oel67-12c ~]# ls -lrt /lib/ld-linux.so.2
lrwxrwxrwx 1 root root 10 May 18 23:25 /lib/ld-linux.so.2 -> ld-2.12.so
[root@oel67-12c ~]#
[root@oel67-12c ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DATA0101 [20964762 blocks (10733958144 bytes), maxio 512]
Discovered disk: ORCL:DATA0102 [20964762 blocks (10733958144 bytes), maxio 512]
[root@oel67-12c ~]#

12cR1 RAC Installation on OEL7

To build Oracle Clusterware Database at Home, I believe , RAC ATTACK is the best place to learn. Its is a free curriculum and platform for hands-on learning labs related to Oracle RAC. While reviewing the article, I thought to perform 12cR1 RAC installation on OEL 7.2.

Attached is the document :- 12c_RAC_on_OEL7

The attached article is inspired by

RAC ATTACK :- https://en.wikibooks.org/wiki/RAC_Attack_-_Oracle_Cluster_Database_at_Home/RAC_Attack_12c

Tim Hall’s article :- https://oracle-base.com/articles/12c/oracle-db-12cr1-rac-installation-on-oracle-linux-7-using-virtualbox 

Deploying Oracle RAC Database 12c on RHEL 7 – Best Practices :- https://www.redhat.com/en/resources/deploying-oracle-rac-database-12c-rhel-7-best-practices

Big Thank you to RAC Attack members!!!

I hope the document helps some of you. Please feel free to comment.

Its all about learning 🙂

Online Partition Move fails with ORA-00932 – 12.1.0.2

Oracle 12c introduced feature to move table partitions and sub-partitions as online operations.So, this blog is related to this feature and the issue I faced.

The database was upgraded from 11.2.0.4 to 12.1.0.2 and this issue is seen in upgraded databases only.

Lets start!!!

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2015','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2015','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2015','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2015','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2015','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2015','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2015','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2015','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2015','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2015','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2015','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2016','dd-mm-yyyy'))
   ) 
   TABLESPACE USERS;

SQL> insert into sales_part
    select
    'Oracle Enterprise Edition' as product,
     mod(rownum,5) as channel_id,
     mod(rownum,1000) as cust_id ,
     5000 as amount_sold,
     to_date
     ('01.' || lpad(to_char(mod(rownum,6)+4),2,'0') || '.2015' ,'dd.mm.yyyy')
     as time_id
     from dual connect by level commit;

Commit complete.

SQL> create index idx_sale_local on sales_part (cust_id,time_id)local tablespace users;

Index created.

SQL> create index idx_sale_global on sales_part (channel_id)  tablespace users;

Index created.

SQL>                        
SQL> exec dbms_stats.gather_table_stats('ANAND','SALES_PART', cascade => true);

PL/SQL procedure successfully completed.

SQL>

Table and Index details

SQL>@partition_table 

TABLE_NAME      PARTITION_NAME            HIGH_VALUE                                                                            LAST_ANALYZED      TABLESPACE_NAM   NUM_ROWS
--------------- ------------------------- ------------------------------------------------------------------------------------- ------------------ -------------- ----------
SALES_PART      SALE_JAN                  TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_FEB                  TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_MAR                  TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_APR                  TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_MAY                  TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33334
SALES_PART      SALE_JUN                  TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33334
SALES_PART      SALE_JUL                  TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_AUG                  TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_SEP                  TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS               33333
SALES_PART      SALE_OCT                  TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_NOV                  TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0
SALES_PART      SALE_DEC                  TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      02-OCT-15          USERS                   0

12 rows selected.

SQL> @index
Enter value for owner: ANAND
Enter value for table_name: SALES_PART

TABLE_OWNER     TABLE_NAME            INDEX_OWNER     INDEX_NAME            TABLESPACE_NAME   NUM_ROWS      CLUST STATUS                   INDEX_TYPE
--------------- --------------------- --------------- --------------------- --------------- ---------- ---------- ------------------------ ------------
ANAND           SALES_PART            ANAND           IDX_SALE_GLOBAL       USERS               200000       6084 VALID                    NORMAL
ANAND           SALES_PART            ANAND           IDX_SALE_LOCAL                            200000     199784 N/A                      NORMAL

SQL> @partition_index
Enter value for index_name: IDX_SALE_LOCAL
Enter value for owner: ANAND

Index                                                                                                                   Number
Name                  PARTITION_NA STATUS                   TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE    of Rows
--------------------- ------------ ------------------------ --------------- -------------- ----------- ------------ ----------
IDX_SALE_LOCAL        SALE_JAN     USABLE                   USERS                                                            0
                      SALE_FEB     USABLE                   USERS                                                            0
                      SALE_MAR     USABLE                   USERS                                                            0
                      SALE_APR     USABLE                   USERS                    65536     1048576                   33333
                      SALE_MAY     USABLE                   USERS                    65536     1048576                   33334
                      SALE_JUN     USABLE                   USERS                    65536     1048576                   33334
                      SALE_JUL     USABLE                   USERS                    65536     1048576                   33333
                      SALE_AUG     USABLE                   USERS                    65536     1048576                   33333
                      SALE_SEP     USABLE                   USERS                    65536     1048576                   33333
                      SALE_OCT     USABLE                   USERS                                                            0
                      SALE_NOV     USABLE                   USERS                                                            0
                      SALE_DEC     USABLE                   USERS                                                            0

Now I plan to move Partition SALE_JUN to tablespace DEMO.

SQL> alter table anand.sales_part move partition sale_jun online tablespace demo
  2  update indexes (
  3  anand.idx_sale_local (partition sale_jun tablespace demo)
  4  );
alter table anand.sales_part move partition sale_jun online tablespace demo
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

After searching for the issue in MOS and reviewing DOC Id 2028583.1, I applied patch 15894842 and 20123899

Oracle Database 12c                                                  12.1.0.2.0
There are 1 products installed in this Oracle Home.

List of Bugs fixed by Installed Patches:

Bug        Fixed by  Installed at                   Description
            Patch
---        --------  ------------                   -----------
20123899   20123899  Fri Oct 02 10:32:54 UTC 2015   INSERT IN TO STAT TABLE FAILS WITH ORA-932
                                                    INCONSISTENT DATATYPES
15894842   15894842  Fri Oct 02 10:31:52 UTC 2015   INSERT IN TO STAT TABLE FAILS WITH ORA-00932
                                                    INCONSISTENT DATATYPES

Even after applying the above patches, the online partition move failed with the same error. This time I traced the session –

SQL> alter session set events '932 trace name errorstack level 3';

Session altered.

SQL>alter table anand.sales_part move partition sale_jun online tablespace demo
    update indexes (
     anand.idx_sale_local (partition sale_jun tablespace demo)
     );
 alter table anand.sales_part move partition sale_jun online tablespace demo
                   *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

The trace file showed

*** 2015-10-02 10:56:40.306
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
----- Current SQL Statement for this session (sql_id=8qwf072kqbt3g) -----
insert into histgrm$ select :1, col#, row#, bucket, endpoint, intcol#, epvalue, ep_repeat_count, epvalue_raw, spare1, spare2 from histgrm$ where obj# = :2

----- Call Stack Trace -----

So the issue is related to HISTGRM$ table. After spending few minutes searching for issue in MOS, I found the below useful documents:-

Bug 20703000 : INSERT INTO STAT TABLE HISTGRM$ FAILS WITH ORA-00932: INCONSISTENT DATATYPES
Moving Table Partition With CLOB Creates ORA-604 , ORA-932 (Doc ID 2040742.1)

After applying the patch 20703000, online partition move worked fine and below is the changed sql on histgrm$

PARSING IN CURSOR #140348537646088 len=257 dep=1 uid=0 oct=2 lid=0 tim=41782225111 hv=216722248 ad='8635af20' sqlid='66x6dxn6fpuu8'
insert into histgrm$ (obj#, col#, row#, bucket, endpoint, intcol#, epvalue,  ep_repeat_count, epvalue_raw, spare1, spare2 ) 
select :1, col#, row#, bucket, endpoint, intcol#, epvalue, ep_repeat_count, epvalue_raw, spare1, spare2 from histgrm$ where obj# = :2
END OF STMT

Hope this helps!!

PDB Saved state – 12.1.0.2

Prior to 12.1.0.2 version, whenever container database was restarted,the pluggable databases within the container database remained in MOUNT state. Startup trigger were written to open the database in READ-WRITE/READ-ONLY mode.Starting from 12.1.0.2, this can be done with PDB save state feature

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			     READ ONLY  NO
	 3 ALPDB			     READ WRITE NO

Lets create a new PDB

SQL> 
SQL> create pluggable database hydb admin user anand identified by anand123 role = (dba)
  2  file_name_convert =('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/hydb/')
  3  storage (maxsize 500M);
create pluggable database hydb admin user anand identified by anand123 role = (dba)
*
ERROR at line 1:
ORA-65113: value of MAX_PDB_STORAGE property for the PDB is too low

SQL> create pluggable database hydb admin user anand identified by anand123 role = (dba)
    file_name_convert =('/u01/app/oracle/oradata/orcl/pdbseed/','/u01/app/oracle/oradata/orcl/hydb/')
    storage (maxsize 1G);
  2    3

Pluggable database created.  

Check status

SQL> @cdb_pdbs

    PDB_ID	 DBID PDB_NAME	     STATUS
---------- ---------- -------------- ----------
	 3 2221989451 ALPDB	     NORMAL
	 2  385653993 PDB$SEED	     NORMAL
	 4 4008421982 HYDB	     NEW

SQL> show pdbs

    CON_ID CON_NAME		  OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ALPDB			  READ WRITE NO
	 4 HYDB 			  MOUNTED
SQL>  

SQL> alter pluggable database hydb open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME	      OPEN MODE  RESTRICTED
---------- ------------   ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ALPDB			  READ WRITE NO
	 4 HYDB 			  READ WRITE NO     
SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;

no rows selected

DBA_PDB_SAVED_STATES can be used to check PDBs in saved state. We have none PDBs in saved state as per the above output.

SQL> alter pluggable database hydb save state;

Pluggable database altered.

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;

NAME				 STATE
------------------------------- --------------
HYDB				 OPEN

Now lets, restart the container

SQL> shu abort
ORACLE instance shut down.

SQL> SQL> 
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  562039536 bytes
Database Buffers	  268435456 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------ ---------- ----------
	 2 PDB$SEED			       READ ONLY  NO
	 3 ALPDB			       MOUNTED    
	 4 HYDB 			       READ WRITE NO
SQL> 

To discard the save state

SQL> alter pluggable database HYDB discard state;

Pluggable database altered.

SQL> select a.name,b.state from v$pdbs a , dba_pdb_saved_states b
  2  where a.con_id = b.con_id;

no rows selected

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  562039536 bytes
Database Buffers	  268435456 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ----------------- ---------- ----------
	 2 PDB$SEED			       READ ONLY  NO
	 3 ALPDB			       MOUNTED    
	 4 HYDB 			       MOUNTED
SQL> 

ORDIM – Oracle Multimedia – Invalid after upgrade

ORDIM – The Oracle Multimedia component might be in INVALID state after the database upgrade to 12c. While I was testing the upgrade on test server, received below errors after the catctl.pl completion.

Serial   Phase #:69 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at catctl.pl line 1500.
    Time: 49s

------------------------------------------------------
Phases [0-73]         End Time:[2015_08_08 08:06:45]
------------------------------------------------------

Grand Total Time: 4848s

*** WARNING: ERRORS FOUND DURING UPGRADE ***

Due to errors found during the upgrade process, the post
upgrade actions in catuppst.sql have not been automatically run.

*** THEREFORE THE DATABASE UPGRADE IS NOT YET COMPLETE ***

 1. Evaluate the errors found in the upgrade logs
    and determine the proper action.
 2. Execute the post upgrade script as described in Chapter 3
    of the Database Upgrade Guide.


REASON:
catuppst.sql unable to run in Database: TESTSX Id: 0
        ERRORS FOUND: during upgrade CATCTL ERROR COUNT=7
------------------------------------------------------
Identifier ORDIM 15-08-08 07:56:26 Script = /oracle/app/product/12.1.0.2/rdbms/admin
ERROR = [ORA-06550: line 76, column 17: PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 76, column 5:
PL/SQL: SQL Statement ignored
]

Multiple insert and update sqls failed for tables in MDSYS schema.At this point, dba_registry showed

SQL> @dba_registry

COMP_ID  SCHEMA       STATUS       VERSION      COMP_NAME
-------- ------------ ------------ ------------ -----------------------------------
APS      SYS          REMOVED      11.1.0.7.0   OLAP Analytic Workspace
CATALOG  SYS          UPGRADED     12.1.0.2.0   Oracle Database Catalog Views
CATJAVA  SYS          VALID        12.1.0.2.0   Oracle Database Java Packages
CATPROC  SYS          INVALID      12.1.0.2.0   Oracle Database Packages and Types
CONTEXT  CTXSYS       VALID        12.1.0.2.0   Oracle Text
JAVAVM   SYS          VALID        12.1.0.2.0   JServer JAVA Virtual Machine
ORDIM    ORDSYS       INVALID      12.1.0.2.0   Oracle Multimedia
OWM      WMSYS        VALID        12.1.0.2.0   Oracle Workspace Manager
RAC      SYS          OPTION OFF   12.1.0.2.0   Oracle Real Application Clusters
SDO      MDSYS        REMOVED      11.1.0.7.0   Spatial
XDB      XDB          VALID        12.1.0.2.0   Oracle XML Database
XML      SYS          VALID        12.1.0.2.0   Oracle XDK
XOQ      SYS          REMOVED      11.1.0.7.0   Oracle OLAP API

Running UTLRP.sql would show below in the end :-

Locator INVALID OBJECTS: CS_SRS_TRIGGER – INVALID – TRIGGER
Locator INVALID OBJECTS: OGIS_CRS_DELETE_TRIGGER – INVALID – TRIGGER
Locator INVALID OBJECTS: OGIS_CRS_INSERT_TRIGGER – INVALID – TRIGGER

The reason for this is the missing VIEW “MDSYS”.”SDO_COORD_REF_SYSTEM”.

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW MDSYS.SDO_COORD_REF_SYSTEM ("SRID", "COORD_REF_SYS_NAME",
"COORD_REF_SYS_KIND", "COORD_SYS_ID", "DATUM_ID", "GEOG_CRS_DATUM_ID", "SOURCE_GEOG_SRID", "PROJECTION_CONV_ID",
"CMPD_HORIZ_SRID","CMPD_VERT_SRID", "INFORMATION_SOURCE", "DATA_SOURCE", "IS_LEGACY", "LEGACY_CODE", "LEGACY_WKTEXT",
"LEGACY_CS_BOUNDS", "IS_VALID", "SUPPORTS_SDO_GEOMETRY") AS SELECT SRID,COORD_REF_SYS_NAME,COORD_REF_SYS_KIND,COORD_SYS_ID,
DATUM_ID,GEOG_CRS_DATUM_ID,SOURCE_GEOG_SRID,PROJECTION_CONV_ID,CMPD_HORIZ_SRID,CMPD_VERT_SRID,INFORMATION_SOURCE,DATA_SOURCE,
IS_LEGACY,LEGACY_CODE,LEGACY_WKTEXT,LEGACY_CS_BOUNDS,IS_VALID,SUPPORTS_SDO_GEOMETRY FROM MDSYS.SDO_COORD_REF_SYS;

After creating the view, recompile the triggers and run utlrp.

grant SELECT on MDSYS.SDO_COORD_REF_SYSTEM to PUBLIC;

ALTER TRIGGER MDSYS.CS_SRS_TRIGGER COMPILE;
show errors
ALTER TRIGGER MDSYS.OGIS_CRS_DELETE_TRIGGER COMPILE;
show errors
ALTER TRIGGER MDSYS.OGIS_CRS_INSERT_TRIGGER COMPILE;
show errors

On production, I created the view as part of pre-task and then upgraded the database.The only change in view ddl was I had to remove “NONEDITIONABLE” keyword to create it as compatible parameter was set to 11.2.0.3.

On Production

SQL> @find_obj
Enter value for object_name: SDO_COORD_REF_SYSTEM
old   1: select owner,object_name,object_id,object_type,status,created,last_ddl_time from dba_objects where object_name=upper('&object_name')
new   1: select owner,object_name,object_id,object_type,status,created,last_ddl_time from dba_objects where object_name=upper('SDO_COORD_REF_SYSTEM')

OWNER        OBJECT_NAME                OBJECT_ID OBJECT_TYPE        STATUS  CREATED                   LAST_DDL_TIME
------------ ------------------------- ---------- ------------------ ------- ------------------------- -------------------------
PUBLIC       SDO_COORD_REF_SYSTEM        39533538 SYNONYM            VALID   18-08-15 01:30:26         18-08-15 01:30:26
MDSYS        SDO_COORD_REF_SYSTEM        39500022 VIEW               VALID   17-08-15 18:28:03         18-08-15 01:30:26