Gestión de servicios de base de datos (creación, modificación y borrado)

Para verificar que servicios están corriendo para una base de datos:

[oracle@server01 ~]$ srvctl status service -d ORCL

No nos devuelve nada, no hay servicios corriendo en la base ORCL.

Creamos un nuevo servicio:

[oracle@server01 ~]$ srvctl add service -d ORCL -s SRVTST -r ORCL1,ORCL2 -P BASIC -e SELECT -m BASIC

(ver más abajo las opción que podemos utilizar en la creación)
En este ejemplo usamos:
-d  Base de datos
-s  Nombre del servicio
-r  Instancias disponibles
-P, -e y -m Caracteristicas del tipo de conexión.

Verificamos la configuración del nuevo servicio:

[oracle@server01 ~]$ srvctl config service -d ORCL
Service name: SRVTST
Service is enabled
Server pool: ORCL_SRVTST
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: ORCL1,ORCL2
Available instances:

12c Adaptive Optimization

One of the most interesting new features of the Oracle Database Optimizer is its ability to recognize its own mistakes and use execution statistics to automatically improve execution plans. Oracle calls this "Adaptive Optimization" and this talk will focus on how it works.

Kerry Osborne, November 19th, 2013

A Beginner’s Guide to Optimizer Hints

It is often easy to forget this, but in many ways it is after we hit the execute button that the really exciting stuff starts with our code. A number of engines silently spring into action; including the optimizer. The optimizer analyses your SQL statement and decides the most efficient way to execute it based on the objects involved in the statement and the conditions you’re subjecting them to. Your database automatically gathers stats about your objects – stuff like the number of rows, number of distinct values, of nulls, data distribution – and the optimizer uses this information in its decision-making. (You can study the explain plan to see what decisions the optimizer has taken.)  The optimizer arrives at its conclusions, often in barely a whisper of time.

And when the SQL statement is executed, you sit back and you feel like a genius.

And that’s it, right? THE END.

Optimizer Hints

Well, not necessarily. The optimizer is the hero of our story; let me introduce the potential villains, Optimizer Hints. An optimizer hint is a code snippet within an SQL statement controlling the decisions of the optimizer. Hints give us the opportunity, in cases where we have superior knowledge about the database, to influence the optimizer. In fact, the very name is a misnomer – they are not hints; they are commands that override the optimizer (as long as the hint is valid and the _OPTIMIZER_IGNORE_HINTS initialization parameter is not TRUE).

Continuar aqui

Activar archivelog en RAC Database (10gR2 y 11g)

Nos conectamos a una de las instancias de la base de datos que queremos pasar a modo archivelog.

[oracle@server01] export ORACLE_SID=ORCL1

[oracle@server01] sqlplus / as sysdba


Verificamos que la base efectivamente NO este ya en modo archivelog:

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     420
Current log sequence           421

Verificamos la configuración del destino de logs:
(podemos usar la FRA o un filesystem compartido por todos los nodos)

Si lo deseamos podemos modificar al tamaño del destino (db_recovery_file_dest_size).

En este ejemplo utilizamos la FRA.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 4407M


Ana vez hechas estas verificaciones y configuraciones, paramos la base de datos desde linea de comandos utilizando srvctl:

[oracle@server01] srvctl stop database -d ORCL

[oracle@server01] srvctl status database -d ORCL
Instance ORCL1 is not running on node server01
Instance ORCL2 is not running on node server02

Una vez baja en todos los nodos, luego la montamos:

[oracle@server01] srvctl start database -d ORCL -o mount

[oracle@server01] srvctl status database -d ORCL
Instance ORCL1 is running on node server01
Instance ORCL2 is running on node server02


Nos conectamos a una de las instancias y corremos el comando SQL para activar el modo archivelog:

[oracle@server01] sqlplus / as sysdba

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.


Verificamos que el modo archivelog efectivamente haya sido activado:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     420
Next log sequence to archive   421
Current log sequence           421

Nuevamente desde linea de comandos, paramos la base de datos en todos los nodos.

[oracle@server01] srvctl stop database -d ORCL

Volvemos a levantar la base en todos los nodos:

[oracle@server01]$ srvctl start database -d ORCL

[oracle@server01] srvctl status database -d ORCL
Instance ORCL1 is running on node server01
Instance ORCL2 is running on node server02


Nos conectamos para probar que las instancias hayan quedado abiertas y disponibles. 

Listo. Nuestra base de datos ya quedó configurada en modo archivelog.

Creación de servicios de base de datos en RAC 11G

A continuación vamos a ver como crear un servicio para una base de datos en particular, en un RAC 11G de dos nodos. Crear servicios resulta muy útil entre otras cosas para gestionar la base de datos, focalizandonos, por ejemplo, en diferentes aplicaciones.

Vemos que la base ORCL no tiene servicios creados:
[oracle@server01]$ srvctl status service -d ORCL

Creamos el servicio con srvctl:
[oracle@server01]$ srvctl add service -d ORCL -s MYAPP -r ORCL1,ORCL2 -P BASIC -e SELECT -m BASIC

Vemos la configuración del servicio:
[oracle@server01]$ srvctl config service -d ORCL -s MYAPP
Service name: MYAPP
Service is enabled
Server pool: ORCL_MYAPP
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: ORCL1,ORCL2
Available instances:

Oracle Critical Patch Update Advisory - October 2013

Oracle corrige 129 vulnerabilidades en su actualización de seguridad de octubre

Siguiendo su ritmo de publicación trimestral de actualizaciones, Oracle publica su boletín de seguridad de octubre. Contiene parches para 129 vulnerabilidadesdiferentes en múltiples productos pertenecientes a 15 familias diferentes, que van desde el popular gestor de base de datos Oracle Database hasta Solaris o MySQL.A continuación se presenta un resumen del número de vulnerabilidades reportadas por familia:

• 4 vulnerabilidades corregidas en Oracle Database Server.
• 17 en Oracle Fusion Middleware.
• 4 en Oracle Enterprise Manager Grid Control.
• 1 en Oracle E-Business Suite.
• 2 en Oracle Supply Chain Products Suite.
• 8 en Oracle PeopleSoft Products.
• 9 en Oracle Siebel CRM.
• 2 en Oracle iLearning
• 6 en Oracle Industry Applications.
• 1 en Oracle Financial Services Software.

Performance is a Feature: Here is the Specification

To many people who build and run software, "performance" is a side-effect, an afterthought of designing and building "proper" features like buttons that book orders and reports that explain profitability. But great performance at scale doesn't happen by accident. The first step to great performance is to remember what performance is: it is the answer to the question, "What have people been experiencing?" Knowing what people experience when they use your software is possible only if you treat performance as a proper feature – a feature you analyze, design, build, test, and maintain.

Cary Millsap, July 31st, 2013

Download Slides

Fuente: http://www.red-gate.com

How does AUTO_SAMPLE_SIZE work in Oracle Database 11g?

When it comes to gathering statistics, one of the most critical decisions you have to make is, what sample size should be used? A 100% sample will ensure accurate statistics but could take a really long time. Whereas a 1% sample will finish quickly but could result in poor statistics.

The ESTIMATE_PERCENT parameter in the DBMS_STATS.GATHER_*_STATS procedures controls the sample size used when gathering statistics and its default value is AUTO_SAMPLE_SIZE.

In an earlier blog post, we talked about the new implementation of AUTO_SAMPLE_SIZE in Oracle Database 11g in terms of its improvements in the speed and accuracy of statistics gathering compared to the old AUTO_SAMPLE_SIZE prior to Oracle Database 11g.

In this post, we will offer a closer look at the how the new AUTO_SAMPLE_SIZE algorithm works and how it affects the accuracy of the statistics being gathered.

Continuar aqui

Compression in Oracle

Serie de artículos sobre Compression en Oracle Database por Jonathan Lewis en http://allthingsoracle.com

Compression in Oracle – Part 1: Basic Table Compression
Compression in Oracle – Part 2: Read-Only Data
Compression in Oracle – Part 3: OLTP Compression
Compression in Oracle – part 4: Basic Index Compression
Compression in Oracle – part 5: Costs of Index Compression

How do adaptive cursor sharing and SQL Plan Management interact?

We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact.  We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact in more details, and show an example. 

The simplest way to reason about the interaction is to remember that they are responsible for two different tasks.  ACS controls whether or not a child cursor is shared on a particular execution.  For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values.  SPM controls which plans the optimizer may choose.  If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.

Continuar aqui

How do I create statistics to make ‘small’ objects appear ‘large’ to the Optmizer?

I recently spoke with a customer who has a development environment that is a tiny fraction of the size of their production environment. His team has been tasked with identifying problem SQL statements in this development environment before new code is released into production.

The problem is the objects in the development environment are so small, the execution plans selected in the development environment rarely reflects what actually happens in production.

To ensure the development environment accurately reflects production, in the eyes of the Optimizer, the statistics used in the development environment must be the same as the statistics used in production. This can be achieved by exporting the statistics from production and import them into the development environment. Even though the underlying objects are a fraction of the size of production, the Optimizer will see them as the same size and treat them the same way as it would in production.

Below are the necessary steps to achieve this in their environment. I am using the SH sample schema as the application schema who's statistics we want to move from production to development.

Continuar aqui