Thursday, October 29, 2015

Oracle 12c Container views - CDB_ Views

Today, I did get an opportunity to research on Containers and pluggable databases - the "single instance, multiple databases" concept that's one of the new features of Oracle 12c.

I was really curious to know where the container view data comes from, in other words, where does the data for the CDB_ views come from?

Well, this is what I found:

It is actually coming from a function:


SQL> select name,user,cdb from v$database;

NAME      USER                           CDB
--------- ------------------------------ ---
MY12CDB   SYS                            YES

1 row selected.

SQL> select dbms_metadata.get_ddl ('VIEW','CDB_OBJECTS') from dual;

DBMS_METADATA.GET_DDL('VIEW','CDB_OBJECTS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."CDB_OB
JECTS"  CONTAINER_DATA
 ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID",
 "DATA_OBJECT_ID", "OBJECT_TYPE", "CREAT
ED", "LAST_DDL_TIME", "TIMESTAMP", "STAT
US", "TEMPORARY", "GENERATED", "SECONDAR
Y", "NAMESPACE", "EDITION_NAME", "SHARIN
G", "EDITIONABLE", "ORACLE_MAINTAINED",
"CON_ID") AS
  SELECT "OWNER","OBJECT_NAME","SUBOBJECT_NAME
","OBJECT_ID","DATA_OBJECT_ID","OBJECT_T
YPE","CREATED","LAST_DDL_TIME","TIMESTAM
P","STATUS","TEMPORARY","GENERATED","SEC
ONDARY","NAMESPACE","EDITION_NAME","SHAR
ING","EDITIONABLE","ORACLE_MAINTAINED","
CON_ID" FROM CDB$VIEW("SYS"."DBA_OBJECTS
")


1 row selected.


Now, what is CDB$VIEW?

Upon further enquiry, I stumbled upon this wonderful blogpost that details the CDB Views. It provided a detailed explanation about the CDB Views in Oracle 12c.

Here are a queries for some of the common scenarios:

1. How many users have I created in each of the PDBs? How do I see it in one place?

Answer: The view used in CDB_USERS and here's the query:


select vp.con_id, vp.name, cu.user_id,cu.username
from
v$pdbs vp, cdb_users cu
where
cu.con_id = vp.con_id
and account_status = 'OPEN'
order by 1,3;




2. That's well and good. Now, how do I know how many objects have been created by each of these users?

Answer: Here you go:



col owner for a30
col con_id for 99999999999999999
COMPUTE SUM LABEL 'NUM_OBJ_IN_PDB' OF NUM_OBJ ON CON_ID
BREAK ON CON_ID SKIP 1
select con_id, owner, count(1) "NUM_OBJ" from cdb_objects
group by con_id,owner
order by 1,3;

3. Which are the datafiles and tablespaces associated with the PDBs and how to list them?


select vp.con_id, vp.name, cdf.file_id, cdf.file_name, cdf.tablespace_name, cdf.bytes/1024/1024 SIZE_MB, status 
from v$pdbs vp, cdb_data_files cdf
where
vp.con_id = cdf.con_id
order by 1,3;



A few helpful links in understanding Container and pluggable databases:


2. CDB and PDB Views


Wednesday, June 24, 2015

Is my server physical or virtual?

Am I real or virtual? That is the question! That's the realm of philosophy and adhyatma.

When and if I put this question to my server, it becomes technical and here's the answer.

On Linux

# dmidecode | more

or

dmidecode -s system-manufacturer


Sample Output

# dmidecode | more
# dmidecode 2.9
SMBIOS 2.4 present.
364 structures occupying 17030 bytes.
Table at 0x000E0010.

Handle 0x0000, DMI type 0, 24 bytes
BIOS Information
        Vendor: Phoenix Technologies LTD
        Version: 6.00
        Release Date: 04/14/2014
        Address: 0xEA050
        Runtime Size: 90032 bytes
        ROM Size: 64 kB
        Characteristics:
                ISA is supported
                PCI is supported
                PC Card (PCMCIA) is supported
                PNP is supported
                APM is supported
                BIOS is upgradeable
                BIOS shadowing is allowed
                ESCD support is available
                Boot from CD is supported
                Selectable boot is supported
                EDD is supported
                Print screen service is supported (int 5h)
                8042 keyboard services are supported (int 9h)
                Serial services are supported (int 14h)
                Printer services are supported (int 17h)
                CGA/mono video services are supported (int 10h)
                ACPI is supported
                Smart battery is supported
                BIOS boot specification is supported
                Function key-initiated network boot is supported
                Targeted content distribution is supported
        BIOS Revision: 4.6
        Firmware Revision: 0.0

Handle 0x0001, DMI type 1, 27 bytes
System Information
        Manufacturer: VMware, Inc.
        Product Name: VMware Virtual Platform
        Version: None
        Serial Number: VMware-42 33 91 c4 2a d5 cb 7d-e8 c3 e4 00 e0 96 b7 62
        UUID: 423391C4-2AD5-CB7D-E8C3-E400E096B762
        Wake-up Type: Power Switch
        SKU Number: Not Specified
        Family: Not Specified

# dmidecode -s system-manufacturer
VMware, Inc.


On Windows:

SYSTEMINFO command on the command prompt should do the trick:

START ==> RUN ==> cmd (open command prompt)

Sample Output

C:\Users\TEMP>systeminfo

Host Name:                 **********
OS Name:                   Microsoft Windows Server 2008 R2 Enterprise
OS Version:                6.1.7601 Service Pack 1 Build 7601
OS Manufacturer:           Microsoft Corporation
OS Configuration:          Member Server
OS Build Type:             Multiprocessor Free
Registered Owner:          **********
Registered Organization:   **********
Product ID:                ********************
Original Install Date:     12/23/2013, 2:07:40 PM
System Boot Time:          5/6/2015, 10:50:07 AM
System Manufacturer:       VMware, Inc.
System Model:              VMware Virtual Platform
System Type:               x64-based PC

Voila! There you go.

Monday, March 30, 2015

Set Hugepages for Oracle 11gR2 database

1. Execute the shell script provided in Note ID: 401749.1 to find out the ideal setting for the parameter vm.nr_hugepages, set it in /etc/sysctl.conf and reboot VM/Server.

Script from Note 401749.1

-bash-3.2$ cat huge_pages_calc.sh
#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com

# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed..."

read

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
    '2.2') echo "Kernel version $KERN is not supported. Exiting." ;;
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
esac

# End


-bash-3.2$ ./huge_pages_calc.sh

This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments. Before proceeding with the execution please note following:
 * For ASM instance, it needs to configure ASMM instead of AMM.
 * The 'pga_aggregate_target' is outside the SGA and
   you should accommodate this while calculating SGA size.
 * In case you changes the DB SGA size,
   as the new SGA will not fit in the previous HugePages configuration,
   it had better disable the whole HugePages,
   start the DB with new SGA size and run the script again.
And make sure that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m


Press Enter to proceed...

Recommended setting: vm.nr_hugepages = 3076


2. Set memlock limit at OS level.

a. Modify /etc/security/limits.conf and enter the below values:

oracle  soft    memlock
oracle  hard    memlock

memlock_value = (Huge_Pages * Huge_Page_Size)

Huge_Pages (value obtained by script in point 1):

or

sysctl -a | grep -i vm.nr_hugepages

Huge_Page_Size:

grep Hugepagesize /proc/meminfo


Note: You can check the current value or memlock using ulimit -l.


3. Set kernel parameters shmmax and shmall.

kernel.shmmax = (1/2 of RAM) and sga_max_size < kernel.shmmax

So, if sga_max_size is ½ or RAM, then set shmmax to around 60 or 70% of RAM.

kernel.shmall = (SGA_MAX_SIZE + PGA_AGGREGATE_TARGET)/PAGE_SIZE

PAGE_SIZE_VALUE:

$ getconf PAGE_SIZE


4. Disable Automatic Memory Management (AMM) if necesary as it is incompatible with HugePages.

Unset the init parameter memory_target parameter, if set.


5. After the reboot, start up the DB and check the below section in the alert log:

****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 10000 (20 GB) (alloc incr 64 MB)
Large Pages configured system wide = 10000 (20 GB)
Large Page size = 2048 KB

RECOMMENDATION:
  Total Shared Global Region size is 24 GB. For optimal performance,
  prior to the next instance restart increase the number
  of unused Large Pages by atleast 2292 2048 KB Large Pages (4584 MB)
  system wide to get 100% of the Shared
  Global Region allocated with Large pages
***********************************************************

Successful message would be as below:

****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 62 GB (100%)

Large Pages used by this instance: 31745 (62 GB)
Large Pages unused system wide = 1023 (2046 MB) (alloc incr 128 MB)
Large Pages configured system wide = 32768 (64 GB)
Large Page size = 2048 KB
***********************************************************


Notes:

1. USE_LARGE_PAGES è This parameter is new in 11gR2 (11.2.0.2) onwards. It is set to true by default.

In Oracle Database 11g Release 2 (11.2.0.2), if there are not enough large pages configured on the system, then regular sized pages will be used to allocate SGA memory. This can cause the free large pages to go unused, and the operating system can allocate a huge amount of memory to create page tables to map SGA into physical pages for the Oracle processes. This may lead to ORA-04030 errors and severe performance degradation on an instance.

In Oracle Database 11g Release 2 (11.2.0.3), Oracle allocates as much of the SGA as it can in large pages, and if it runs out, it will allocate the rest of the SGA using regular sized pages. This can cause the instance to create additional shared memory segments for the SGA, but the total SGA size will be unchanged. In this supported mixed page mode allocation, the database will exhaust the available large pages before switching to regular sized pages.


References:


2. 361323.1 - HugePages on Linux: What It Is... and What It Is Not...

3. 401749.1 - Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration

4. 361468.1 - HugePages on Oracle Linux 64-bit



7. http://www.oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64.php

8. 1392543.1 - Large Pages Information in the Alert Log

9. 1392497.1 - USE_LARGE_PAGES To Enable HugePages In 11.2

10. Bug 9195408 - DB STARTUP DOES NOT CHECK WHETHER HUGEPAGES ARE ALLOCATED- PROVIDE USE_HUGEPAGES

11. http://agorbyk.wordpress.com/2012/02/19/oracle-11-2-0-3-and-hugepages-allocation/

12. http://kevinclosson.wordpress.com/category/use_large_pages/


14. 803238.1 - Oracle Not Utilizing Hugepages