Statspack : Liberar Espacio Recreandolo

Cuando tomamos estadisticas con STATSPACK si no tenemos la precaucion de ir depurando (Ver en mi blog como DEPURAR) va a ir creciendo la cantidad de estadisticas y por mas que despues depuremos tendriamos que liberar el espacio.
Un metodo rapido para liberar espacio del tablespace PERFSTAT si es que no necesitamos conservar las estadisticas historicas es re.crearlo.


1 - Borrar todo el Statspack
SQL> @?/rdbms/admin/spdrop

2 - Eliminar el Tablespace
SQL> drop tablespace perfstat including contents;

3 - Eliminar los Datafiles
/u01/oradata/ORCL>rm perfstat01.dbf
/u01/oradata/ORCL>rm perfstat02.dbf

4 - Recrear tablespace
SQL> create tablespace PERFSTAT datafile '/u01/oradata/ORCL/perfstat01.dbf' size 2048M;

5 - Para recrearlo, ejecutar spcreate. Nos va a pedir que ingresemos : password para el ususario PERFSTAT, Tablespace Default y Temporary Tablespace

SQL> @?/rdbms/admin/spcreate

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data.  Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE

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

PERFSTAT                      PERMANENT

SYSAUX                          PERMANENT *

UNDO                              PERMANENT

USERS                             PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE

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

TEMP                           TEMPORARY *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

.............................................................

Creating Package STATSPACK...

Package created.

No errors.

Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>

Listo!

Scripts Para Depurar Snapshots Statspack

Scripts para depurar un rango de snapshots dejando las estadisticas de los ultimos 30 dias. Se recomienda ejecutar diariamente.


depura_statspack.sh

#!/bin/bash

################################################
# Depura las estadisticas del statspack dejando los ultimos 30 dias.  
#
#################################################

source ~/.bash_profile
sqlplus perfstat/perfstat @$HOME/scripts/depura_statspack.sql
exit

____________________________________________________________

depura_statspack.sql
spool /home/oracle/logs/depura_statspack.log
col menor format 999999 new_value losnapid
col mayor format 999999 new_value hisnapid
select min(snap_id) as menor, max(snap_id) as mayor from  stats$snapshot
where snap_time < sysdate - 30;
@?/rdbms/admin/sppurge
spool off
exit

SP2-0613: Unable to verify PLAN_TABLE format or existence

Al intentar habilitar el AUTOTRACE puede dar ese error 

SQL> set autotrace traceonly  

SP2-0613: Unable to verify PLAN_TABLE format or existence

SP2-0611: Error enabling EXPLAIN report

Eso es debido a que no existe la PLAN_TABLE en el esquema del usuario que estamos conectados.

Para solucionarlo ejecutar:

SQL> @?/rdbms/admin/utlxplan

Table created.

Luego :

SQL> set autotrace traceonly

Listo!

Como Migrar los SQL PROFILES de una Base a Otra

Cuando tenemos que migrar los sql profiles de una base de datos a otra, por ej los generados en Testing a Produccion debemos realizar los siguientes pasos
1 - En la Base Origen ; Verificar cuantos sql_profiles tenemos
select count(*) from dba_sql_profiles;

2 - Crear una Staging Table
Esta tabla no la podemos crear en SYS, en este ejemplo esta en SYSTEM, pero se puede usar cualquier usuario
sqlplus / as sysdba
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => ‘TAB_SQL_PROFILES’,schema_name=>’SYSTEM’);
     END;
/

3. Copiar los SQL PROFILES a la Staging Table
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_category => ‘%’,
staging_table_name => ‘TAB_SQL_PROFILES’,
staging_schema_owner=>’SYSTEM’);
END;
/

4. Verificar
 select count(*) from SYSTEM.TAB_SQL_PROFILES;

5 . Exportar los SQL PROFILES
expdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=SYSTEM.TAB_SQL_PROFILES
DIRECTORY=DATA_PUMP_DIR

(Verificar donde apunta el directorio select * from dba_directories;)

6. Transferir el dump al servidor de la Base Destino

7. En la Base Destino : Crear la Staging Table
sqlplus / as sysdba
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => ‘TAB_SQL_PROFILES’,schema_name=>’SYSTEM’);
     END;
/

8. Importar los SQL PROFILE, con TABLE_EXISTS_ACTION=TRUNCATE, esto implica que si la tabla existe ejecuta el truncate y la carga con los datos del dump

impdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=SYSTEM.TAB_SQL_PROFILES DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=TRUNCATE

9. Verificar que se importaron
select count(*) from SYSTEM.TAB_SQL_PROFILES;

10. Verificar cuantos SQL PROFILES hay antes de desempaquetar
select count(*) from dba_sql_profiles;

11. Desempaquetar
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => ‘TAB_SQL_PROFILES’,
staging_schema_owner=>’SYSTEM’, replace=>FALSE);

12. Verificar que se agregaron bien los profiles. Tiene que dar el valor del resultado que dio el punto 10 + el punto 11, salvo que ya existiera previamente alguno de los profiles en ese caso puede dar menos.
select count(*) from dba_sql_profiles;

Statspack : Como depurar snapshots

Si estas trabajando con versiones de Oracle anteriores a 10g o no tenes el diagnostic pack, seguramente seguis usando statspacks.
Con el tiempo los snapshots se van acumulando y se hace necesario depurar.
Para depurar se ejecuta sppurge.sql, lista todos los snapshots que hay y nos va a requerir el Lo Snap ID que es el valor de snapshot desde el cual se quiere depurar y el Hi Snap ID que es el valor de snapshot hasta el cual se quiere depurar. Depura todos los snapshots identificados entre esos valores, hay que tener en cuenta al seleccionar los snapshots a depurar que este comando no tiene vuelta atras. 

SQL> connect perfstat/perfstat
Connected.

SQL> @$ORACLE_HOME/rdbms/admin/sppurge

     230  14 Jan 2015 19:10:01           5 s021
     231  14 Jan 2015 20:10:01           5 s021
     232  14 Jan 2015 21:10:02           5 s021
     233  14 Jan 2015 22:10:01           5 s021
     234  14 Jan 2015 23:10:01           5 s021
     235  15 Jan 2015 00:10:02           5 s021
     236  15 Jan 2015 01:10:01           5 s021
     237  15 Jan 2015 02:10:01           5 s021
     238  15 Jan 2015 03:10:01           5 s021
     239  15 Jan 2015 04:10:01           5 s021
     240  15 Jan 2015 05:10:01           5 s021
     241  15 Jan 2015 06:10:01           5 s021
     242  15 Jan 2015 07:10:01           5 s021
     243  15 Jan 2015 08:10:02           5 s021
     244  15 Jan 2015 09:10:01           5 s021
     245  15 Jan 2015 10:10:01           5 s021
     246  15 Jan 2015 11:10:02           5 s021
     247  15 Jan 2015 12:10:01           5 s021
     248  15 Jan 2015 13:10:01           5 s021
     249  15 Jan 2015 14:10:02           5 s021
     250  15 Jan 2015 15:10:01           5 s021
     251  15 Jan 2015 16:10:01           5 s021
     252  15 Jan 2015 17:10:01           5 s021

Warning
~~~~~~~
sppurge.sql deletes all snapshots ranging between the lower and
upper bound Snapshot Id's specified, for the database instance
you are connected to.  Snapshots identified as Baseline snapshots
which lie within the snapshot range will not be purged.

It is NOT possible to rollback changes once the purge begins.

You may wish to export this data before continuing.


Specify the Lo Snap Id and Hi Snap Id range to purge
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for losnapid: 230
Using 230 for lower bound.

Enter value for hisnapid: 252
Using 252 for upper bound.

Deleting snapshots 230 - 252.

Number of Snapshots purged: 23
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Purge of specified Snapshot range complete.


SQL> exit

Como verificar que opciones (Oracle Options) estan instaladas en la Base de Datos

Para verificar que opciones (Oracle Options) estan instaladas en la Base de Datos podemos consultar la vista v$option

Ejemplo :

SQL> set pagesize 1000

SQL> col parameter format a40

SQL> col value format a20

SQL> select * from v$option;

PARAMETER                                VALUE

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

Partitioning                             FALSE

Objects                                  TRUE

Real Application Clusters                FALSE

Advanced replication                     FALSE

Bit-mapped indexes                       FALSE

Connection multiplexing                  TRUE

Connection pooling                       TRUE

Database queuing                         TRUE

Incremental backup and recovery          TRUE

Instead-of triggers                      TRUE

Parallel backup and recovery             FALSE

Parallel execution                       FALSE

Parallel load                            TRUE

Point-in-time tablespace recovery        FALSE

Fine-grained access control              FALSE

Proxy authentication/authorization       TRUE

Change Data Capture                      FALSE

Plan Stability                           TRUE

Online Index Build                       FALSE

Coalesce Index                           TRUE

Managed Standby                          FALSE

Materialized view rewrite                FALSE

Materialized view warehouse refresh      FALSE

Database resource manager                FALSE

Spatial                                  FALSE

Automatic Storage Management             FALSE

Export transportable tablespaces         FALSE

Transparent Application Failover         TRUE

Fast-Start Fault Recovery                FALSE

Sample Scan                              TRUE

Duplexed backups                         FALSE

Java                                     TRUE

OLAP Window Functions                    TRUE

Block Media Recovery                     FALSE

Fine-grained Auditing                    FALSE

Application Role                         FALSE

Enterprise User Security                 FALSE

Oracle Data Guard                        FALSE

Oracle Label Security                    FALSE

OLAP                                     FALSE

Basic Compression                        FALSE

Join index                               FALSE

Trial Recovery                           FALSE

Data Mining                              FALSE

Online Redefinition                      FALSE

Streams Capture                          FALSE

File Mapping                             FALSE

Block Change Tracking                    FALSE

Flashback Table                          FALSE

Flashback Database                       FALSE

Transparent Data Encryption              FALSE

Backup Encryption                        FALSE

Unused Block Compression                 FALSE

Oracle Database Vault                    FALSE

Result Cache                             FALSE

SQL Plan Management                      FALSE

SecureFiles Encryption                   FALSE

Real Application Testing                 FALSE

Flashback Data Archive                   FALSE

DICOM                                    TRUE

Active Data Guard                        FALSE

Server Flash Cache                       FALSE

Advanced Compression                     FALSE

XStream                                  FALSE

Deferred Segment Creation                FALSE

65 rows selected.

Enterprise Manager : Como cambiar el tiempo de timeout de la sesion

Es muy común que estemos trabajando con Enterprise Manager y nos cambiamos de pantalla a la terminal a hacer verificaciones y cuando queremos volver a Enterprise Manager nos obliga a volvernos a loguear, esto aunque es una medida de seguridad puede resultar bastante molesto y hacernos perder tiempo sobre todo en situaciones criticas.

En OEM 11gR2 para poder cambiar el valor del idle time out tenemos que editar el archivo

$ORACLE_HOME/<your_hostname>_<SID>/sysman/config/emoms.properties

y agregar al final 

oracle.sysman.eml.maxInactiveTime=minutos

por ejemplo aumentarlo a dos horas 

oracle.sysman.eml.maxInactiveTime=120

grabamos al salir el archivo y reiniciamos el servicio

emctl stop dbconsole

emctl start dbconsole

SQLPLUS : Como configurar el editor

Cuando queremos editar en sqlplus debemos ejecutar el comando ED o EDIT y  nos abren el editor default del sistema operativo.

SQL>ed

Si queremos configurar que edite con el vi en unix-linux podemos configurar en nuestra sesion de sqlplus 

SQL> define_editor =vi

Si queremos dejar el editor configurado para que lo tome por default debemos agregar en el archivo $ORACLE_HOME/sqlplus/admin/glogin.sql  
define_editor ='vi'

Cuando ejecutamos el editor nos edita la ultima sentencia ejecutada

Ejemplo:
SQL>define_editor =vi
SQL>select status from v$instance;
SQL>ed

abre el vi con la sentencia 
select status from v$instance;

ejecutamos los comandos de vi y salimos por ejemplo con :x  graba y sale
y volvemos a la interface de SQLPLUS

SQL>