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


Wednesday, August 8, 2012

Modify retention period of workflow queues

Yesterday, there was a requirement to increase the retention period of WF_DEFERRED and WF_BPEL_Q so that the developers could troubleshoot issues involving business events.

This can be done this way:

1. Check the retention period of existing workqueues.

SQL> SELECT owner, name, retention FROM all_queues WHERE name LIKE 'WF%';


OWNER           NAME                                                RETENTION

--------------- --------------------------------------------- ------------------------------
APPS                 WF_BPEL_Q                                                0
APPLSYS         WF_INBOUND_QUEUE                             0
APPLSYS         WF_OUTBOUND_QUEUE                          0
APPLSYS         WF_SMTP_O_1_QUEUE                           0
APPLSYS         WF_DEFERRED_QUEUE_M                        0
APPLSYS         WF_DEFERRED                                            0
APPLSYS         WF_ERROR                                           0
APPLSYS         WF_REPLAY_OUT                                   604800
APPLSYS         WF_IN                                                 604800
APPLSYS         WF_OUT                                              604800
APPLSYS         WF_REPLAY_IN                                      604800
APPLSYS         WF_JMS_IN                                          0
APPLSYS         WF_CONTROL                                       0
APPLSYS         WF_NOTIFICATION_IN                            0
APPLSYS         WF_NOTIFICATION_OUT                         0
APPLSYS         WF_WS_JMS_IN                                    0
APPLSYS         WF_JAVA_DEFERRED                              0
APPLSYS         WF_JAVA_ERROR                                   0
APPLSYS         WF_WS_JMS_OUT                                 0
APPLSYS         WF_JMS_JMS_OUT                                0
APPLSYS         WF_WS_SAMPLE                                   0
APPLSYS         WF_JMS_OUT                                      0

22 rows selected.



Notice that the retention period for WF_BPEL_Q and WF_DEFERRED are set to 0.

2. Increase the retention period of both these queues as below:

exec dbms_aqadm.alter_queue
      (  queue_name => 'APPLSYS.WF_DEFERRED',
         retention_time => 86400  -- in seconds. So, it is for 1 day. 
      );

exec dbms_aqadm.alter_queue
      (  queue_name => 'APPLSYS.WF_BPEL_Q',
         retention_time => 86400 -- in seconds. So, it is for 1 day. 
      );

3. Again, check the retention period of existing workqueues.

SQL> SELECT owner, name, retention FROM all_queues WHERE name LIKE 'WF%';


OWNER           NAME                                                           RETENTION

--------------- ---------------------------------------------           ------------------------------
APPS                WF_BPEL_Q                                                              86400
APPLSYS         WF_INBOUND_QUEUE                                       0
APPLSYS         WF_OUTBOUND_QUEUE                                    0
APPLSYS         WF_SMTP_O_1_QUEUE                                     0
APPLSYS         WF_DEFERRED_QUEUE_M                                  0
APPLSYS         WF_DEFERRED                                                         86400
APPLSYS         WF_ERROR                                                     0
APPLSYS         WF_REPLAY_OUT                                             604800
APPLSYS         WF_IN                                                           604800
APPLSYS         WF_OUT                                                        604800
APPLSYS         WF_REPLAY_IN                                                604800
APPLSYS         WF_JMS_IN                                                     0
APPLSYS         WF_CONTROL                                                  0
APPLSYS         WF_NOTIFICATION_IN                                       0
APPLSYS         WF_NOTIFICATION_OUT                                    0
APPLSYS         WF_WS_JMS_IN                                               0
APPLSYS         WF_JAVA_DEFERRED                                         0
APPLSYS         WF_JAVA_ERROR                                              0
APPLSYS         WF_WS_JMS_OUT                                            0
APPLSYS         WF_JMS_JMS_OUT                                           0
APPLSYS         WF_WS_SAMPLE                                              0
APPLSYS         WF_JMS_OUT                                                 0

22 rows selected.


Notes:

1. Before increasing the retention period, do note that there will be a performance overhead because of this increase. This is because the queue is not cleared till one day and hence, the processing of BES events may become slow.


2. The default value of retention period is 1 day for all the queues.


Tuesday, August 7, 2012

Check if UTL_FILE and FND_FILE are working fine

Yesterday, while troubleshooting a vexing ORA-29280 error, I came across 2 useful PL/SQL anonymous blocks that can be used to test FND_FILE and UTL_FILE to find out whether they are indeed working fine.


UTL_FILE
set serveroutput on 
DECLARE 
  file_location VARCHAR2(256) := '<first entry on utl_file_dir>'; 
  file_name VARCHAR2(256) := 'utlfile1.lst'; 
  file_text VARCHAR2(256) := 'THIS IS A TEST'; 
  file_id UTL_FILE.file_type; 
BEGIN 
  file_id := UTL_FILE.fopen(file_Location, file_name, 'W'); 
  UTL_FILE.put_line(file_id, file_text); 
  UTL_FILE.fclose(file_id); 
EXCEPTION 
  WHEN UTL_FILE.INVALID_PATH 
  THEN dbms_output.put_line('Invalid path ' || SQLERRM); 
    WHEN OTHERS 
  THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM); 
END; 
/



References: Metalink (MOS) Note ID: 261693.1


FND_FILE
set serveroutput on
exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

HTH.



Tuesday, March 13, 2012

Maximum number of Datafiles for an instance

I'm back after a long hiatus! Hopefully, with stuff that you will all appreciate and use, in your day to day activities. So, here goes.

Objective

To find the maximum number of datafiles for a given instance (instance = database, both are interchangeable, unless we are talking of RAC).

Options

1. Using init file:

Check the parameter db_files.

e.g: SQL> show parameter db_files


2. Using control file:

Check the parameter MAXDATAFILES after generating the controlfile trace.

eg:

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 64
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607


3. Execute the below query in sqlplus:

SQL> select records_total from v$controlfile_record_section where type='DATAFILE';

Wednesday, February 23, 2011

Alternatives to Flash version of My Oracle Support

I am sure you all know this but this is for those who do not! :)

For those who think that the flash version of MOS is slow (including yours truly), Oracle provides the html version of MOS.

For those who were accustomed to using the Metalink (when MOS was Metalink and George W Bush was the president of the US of A - not the father, I'm talking about the son) patch search and download page, do not fret. You can use the classic patches window to get the old page (and a lot of memories) back!

To open any MOS note id, you can use this link. Only substitute Note ID 221211.1 with the note id that you want to open.