Thursday, February 4, 2016

How to find information about CPUs on an AIX server

lsconf | grep Processor
Processor Type: PowerPC_POWER7
Processor Implementation Mode: POWER 7
Processor Version: PV_7_Compat
Number Of Processors: 5
Processor Clock Speed: 3000 MHz
  Model Implementation: Multiple Processor, PCI bus
+ proc0                                                                          Processor
+ proc4                                                                          Processor
+ proc8                                                                          Processor
+ proc12                                                                         Processor
+ proc16                                                                         Processor    

pmcycles -m
CPU 0 runs at 3000 MHz
CPU 1 runs at 3000 MHz
CPU 2 runs at 3000 MHz
CPU 3 runs at 3000 MHz
CPU 4 runs at 3000 MHz
CPU 5 runs at 3000 MHz
CPU 6 runs at 3000 MHz
CPU 7 runs at 3000 MHz
CPU 8 runs at 3000 MHz
CPU 9 runs at 3000 MHz
CPU 10 runs at 3000 MHz
CPU 11 runs at 3000 MHz
CPU 12 runs at 3000 MHz
CPU 13 runs at 3000 MHz
CPU 14 runs at 3000 MHz
CPU 15 runs at 3000 MHz
CPU 16 runs at 3000 MHz
CPU 17 runs at 3000 MHz
CPU 18 runs at 3000 MHz
CPU 19 runs at 3000 MHz
lparstat -i
Node Name                                  : myhost
Partition Name                             : myhost
Partition Number                           : 25
Type                                       : Shared-SMT-4
Mode                                       : Uncapped
Entitled Capacity                          : 0.50
Partition Group-ID                         : 32793
Shared Pool ID                             : 0
Online Virtual CPUs                        : 5
Maximum Virtual CPUs                       : 16
Minimum Virtual CPUs                       : 1
Online Memory                              : 8192 MB
Maximum Memory                             : 12288 MB
Minimum Memory                             : 1024 MB
Variable Capacity Weight                   : 128
Minimum Capacity                           : 0.10
Maximum Capacity                           : 16.00
Capacity Increment                         : 0.01
Maximum Physical CPUs in system            : 16
Active Physical CPUs in system             : 16
Active CPUs in Pool                        : 16
Shared Physical CPUs in system             : 16
Maximum Capacity of Pool                   : 1600
Entitled Capacity of Pool                  : 1300
Unallocated Capacity                       : 0.00
Physical CPU Percentage                    : 10.00%
Unallocated Weight                         : 0
Memory Mode                                : Shared
Total I/O Memory Entitlement               : 411.000 MB
Variable Memory Capacity Weight            : 128
Memory Pool ID                             : 0
Physical Memory in the Pool                : 224.000 GB
Hypervisor Page Size                       : 4K
Unallocated Variable Memory Capacity Weight: 0
Unallocated I/O Memory entitlement         : 0.000 MB
Memory Group ID of LPAR                    : 32793
Desired Virtual CPUs                       : 5
Desired Memory                             : 8192 MB
Desired Variable Capacity Weight           : 128
Desired Capacity                           : 0.50
Target Memory Expansion Factor             : -
Target Memory Expansion Size               : -
Power Saving Mode                          : Disabled
Sub Processor Mode                         : -

Find number of cores per processors (Oracle consider threads to be cores, when estimating for example cpu_count)
smtctl

This system is SMT capable.
This system supports up to 4 SMT threads per processor.
SMT is currently enabled.
SMT boot mode is not set.
SMT threads are bound to the same virtual processor.

proc0 has 4 SMT threads.
Bind processor 0 is bound with proc0
Bind processor 1 is bound with proc0
Bind processor 2 is bound with proc0
Bind processor 3 is bound with proc0


proc4 has 4 SMT threads.
Bind processor 4 is bound with proc4
Bind processor 5 is bound with proc4
Bind processor 6 is bound with proc4
Bind processor 7 is bound with proc4


proc8 has 4 SMT threads.
Bind processor 8 is bound with proc8
Bind processor 9 is bound with proc8
Bind processor 10 is bound with proc8
Bind processor 11 is bound with proc8


proc12 has 4 SMT threads.
Bind processor 12 is bound with proc12
Bind processor 13 is bound with proc12
Bind processor 14 is bound with proc12
Bind processor 15 is bound with proc12


proc16 has 4 SMT threads.
Bind processor 16 is bound with proc16
Bind processor 17 is bound with proc16
Bind processor 18 is bound with proc16
Bind processor 19 is bound with proc16

Wednesday, February 3, 2016

11.2.0.2 and onwards: v$parameter reflects the NLS session parameters, not the NLS instance parameters



After a migration of a database from single-byte to multi-byte character set, I wanted to compare the nls_parameters.

As a part of the migration, we had to change semantics on several of our tables from BYTE to CHAR to accommodate for expanding data.

One issue that came up during our initial discussions on how to complete the migration, was if it would be beneficial to set nls_length_semantics to CHAR in the init.ora file. But according to Oracle's documentation, the NLS_LENGTH_SEMANTICS should be kept to BYTE at database level.(Check Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)", section G: "Summary of best practices") so that idea was rejected.

After the import was finished, I wanted to compare the parameters to quality check the new instance.

I queried the v$parameter view, as many DBAs would normally do and found that nls_length_semantics seemed to have been set to CHAR at the instance level:
select name,value from v$parameter where name = 'nls_length_semantics';
NAME VALUE
nls_length_semantics CHAR

After a quick search on My Oracle Support I found Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"

Turns out that this is not an error, but the result of a corrected bug (bug no 8722860).

In previous releases, a query of v$parameter or "show parameter" in sql*plus would reflect the instance parameters.
This is incorrect, as Oracle points out:

The documentation set states that :
* the SQL*Plus command show parameter displays the values of initialization parameters in effect for the current session.
* the V$PARAMETER view displays information about the initialization parameters that are currently in effect for the session


As Oracle puts it
"From 11.2.0.2 onwards V$PARAMETER and the SQL*Plus command show parameter reflect the session NLS settings as it should."

So make sure to use v$system_parameter instead, which is reflecting the correct instance setting:
select name,value from V$SYSTEM_PARAMETER where name = 'nls_length_semantics';
NAME VALUE
nls_length_semantics BYTE


Sources:
Doc ID 144808.1 "Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS)"
Doc ID 1368966.1 "NLS settings like NLS_DATE_FORMAT in spfile or init.ora ignored, incorrect or not as expected on Oracle 11.2.0.2 and higher"
Doc ID 241047.1: "The Priority of NLS Parameters Explained (Where To Define NLS Parameters)

Wednesday, January 20, 2016

What is the package SYS.USER_LOCK?

Recenty I was migrating a database with a package that wouldn't compile because it couldn't find the package called upon in its code:

SQL> ALTER PACKAGE mypackage compile body; 

Warning: Package body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MVA3.MYPACKAGE:

PLS-00201: identifier 'USER_LOCK.SLEEP' must be declared

According to PSOUG, there are "four separate ways to induce a sleep into PL/SQL."

•dbms_backup_restore
•dbms_drs
•dbms_lock
•user_lock


PSOUG continues: "Note that USER_LOCK package is not part of the default Oracle installation"

Install as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba @userlock.sql

Monday, January 18, 2016

How to work around error from opatch "LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo"

I recently had to investigate the patch level for an Oracle 10g database prior to an upgrade of an OAS application database.

This is the error I ran into:
myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

 
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Fix this by running the attachHome.sh script in $ORACLE_HOME/oui/bin.
The Oracle Universal Installer allows you to set up an Inventory from scratch, or to register an existing ORACLE_HOME in an already existing Inventory.


myserver:asdb:ora10g>cd $ORACLE_HOME/oui/bin
/u01/oracle/product/ora10g/oui/bin
myserver:asdb:ora10g>./attachHome.sh
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/oracle/oraInventory11g
'AttachHome' was successful.

Result:
myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/ora10g
Central Inventory : /u01/oracle/oraInventory11g
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /u01/oracle/product/ora10g/oui
Log file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/opatch2016-01-18_16-01-17PM.log

Lsinventory Output file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2016-01-18_16-01-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (4):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 4 products installed in this Oracle Home.


Interim patches (3) :

Patch  6979801      : applied on Fri Mar 06 10:47:02 CET 2009
   Created on 5 Feb 2009, 15:35:25 hrs PST8PDT
   Bugs fixed:
     6979801

Patch  6972843      : applied on Tue Oct 14 17:35:12 CEST 2008
   Created on 25 Jun 2008, 08:31:04 hrs UTC
   Bugs fixed:
     6972843

Patch  6823287      : applied on Tue Oct 14 17:26:32 CEST 2008
   Created on 30 Jun 2008, 10:27:42 hrs UTC
   Bugs fixed:
     6823287



--------------------------------------------------------------------------------

OPatch succeeded.

How to use dbms_metadata to generate DDL for constraints

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool all_mycons.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('CONSTRAINT','SYS_C006257121','SCOTT')  FROM DUAL;

-- For Foreign key constraints, use the REF_CONSTRAINT constant:
select dbms_metadata.get_ddl('REF_CONSTRAINT','MYFOREIGNKEY_FK','SCOTT')  FROM DUAL;

How to use dbms_metadata to generate DDL for a trigger

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool mytrigger.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('TRIGGER','MYTRIGGER','SCOTT')  FROM DUAL;

Tuesday, January 12, 2016

On a Linux system, what do the parameters shmall, shmmax and shmmni define?

shmall
* indicates the total amount of shared memory that the system can use at one time (measured in pages)

shmmax
* defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space, in bytes.
Oracle recommends that more than half of the physical memory is assigned to shmmax

shmmni
* defines the system wide maximum number of shared memory segments