How to enable and disable Automatic SQL tuning

Enable:

BEGIN
dbms_auto_task_admin.enable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

Disable:

BEGIN
dbms_auto_task_admin.disable(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;

If desirable, Oracle also lets you enable or disable all automated maintenance tasks for all windows. Do this by calling the ENABLE or DISABLE procedure with no arguments:

EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

Sources:
dbms_auto_task_admin
Configuring Automated Maintenance Tasks

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

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

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)