Tuesday, February 16, 2016

How to remove dbms_scheduler jobs

To delete an obsolete scheduler job, use the procedure dbms_scheduler.drop_job procedure.
In a recent case, a large number of obsolete dbms_Scheduler jobs were laying around in the database, and needed a cleanup.
Most of them were not enabled, and all stakeholders deemed it safe to remove the jobs.

To loop through the jobs owned by the schema, I used the following simple script:
connect scott/tiger
set serveroutput on
set trimspool on
spool /tmp/drop_mva_scheduler_jobs.log
DECLARE

  CURSOR C1 IS
  SELECT JOB_NAME
  FROM USER_SCHEDULER_JOBS
  WHERE ENABLED='FALSE';

BEGIN
 FOR l_rec IN C1 LOOP
   dbms_output.put_line('Now dropping job: ' || l_rec.job_name);
   dbms_scheduler.drop_job(job_name=>'' || l_rec.job_name || '' );
 END LOOP;
END;
/
exit


Sources: Oracle Documentation

Thursday, February 4, 2016

How to create and start a service with dbms_service



Create the service. The first parameter name is the service name, can be up to 64 characters long.
The second parameter is the network name of the service as used in SQLNet connect descriptors for client connections
# sqlplus / as sysdba
SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');

Start the service:
SQL> exec dbms_service.start_service('online_users');

Update the service_names parameter. Without this, your new service would exist, but have to be manually started with every database restart:
SQL> alter system set service_names='proddb01, online_users' scope=both;

Check the parameter settings:
show parameter service

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
service_names                        string                            online_users, proddb01

A typical connect string when using JDBC would be
connect string : jdbc:oracle:thin:@//prodserver01:1521/online_users

If you are relying on tns naming, in other words, using a local tnsnames.ora file, make sure you set up your tnsnames.ora correctly.
The following is an example of a tnsnames.ora entry for the service we just created:
online_users =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = online_users)
    )
  )

Note: If you have specified a value for the database parameter domain_name, you need to add this to the SERVICE_NAME-clause of your tnsnames.ora entry, otherwise you will have trouble connecting:
show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_domain                            string      mydomain.com

In the case shown above, we have indeed specified a value for domain_name, and therefore the service_name will inherit this value by default.
So make sure you specify the FQDN in the SERVICE_NAME clause for your tnsnames.ora entry, like this:
(SERVICE_NAME = online_users.mydomain.com)

The command
lsnrctl services

will show you how the services are automatically registered with the listener.
The first 3 services are automatically created and always created by default with any Oracle installation. The last service registered with the listener is the one we created manually previously in this post:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.mydomain.com)(PORT=1521)))
Services Summary...
Service "proddb01" has 1 instance(s).
  Instance "proddb01", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "proddb01.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1 refused:0 state:ready
         LOCAL SERVER
Service "proddb01XDB.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER 
         (ADDRESS=(PROTOCOL=tcp)(HOST=myserver.mydomain.com)(PORT=19394))
Service "online_users.mydomain.com" has 1 instance(s).
  Instance "proddb01", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER


If for some reason the service is not registered automatically with the listener within reasonable time, you can try to force a registration, like this:
alter system register;


If your listener is running on the default port 1521, there should be no need to touch your listener configuration, except stop/start/reload.
If you use a non-default listener configuration, such as a port != 1521 or a listener name != LISTENER, you need to configure the parameter local_listener.
In the exammple below, I configure the listener to run on port 1526 instead of 1521:
alter system set local_listener='(address=(protocol=tcp)(host=myserver)(port=1526))' scope=both;
Once again, note that in this case, the parameter db_domain is set so you only need to include the server name, not the domain name. If you do use the FQDN, it doesn't make any difference to your setup.





Verify that your connection can be used by using sqlplus to create a connection:
sqlplus scott/tiger@online_users
SQL> select sys_context('userenv','SERVICE_NAME') FROM DUAL;

SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
online_users.mydomain.com



To remove a service:
-- stop the service
SQL> exec dbms_service.stop_service('online_users');

-- after stopping the service, it may be deleted
SQL> exec dbms_service.delete_service('online_users');

PL/SQL procedure successfully completed.




A useful query for checking relevant parameters:
set lines 200
column name format a30
column network_name format a30
column value format a60

select name,value
from v$system_parameter
where name in ('service_names','db_domain','local_listener');

SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

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;