Funciones oracle

Trigger: es un bloque de código que se ejecuta automáticamente cuando ocurre algún evento (como inserción, actualización o borrado) sobre una determinada tabla (o vista).
Se crean para conservar la integridad y la coherencia de los datos.

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
  FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;
/

create or replace trigger NOMBREDISPARADOR
 MOMENTO-- before, after o instead of
 EVENTO-- insert, update o delete
 of CAMPOS-- solo para update
 on NOMBRETABLA
 NIVEL--puede ser a nivel de sentencia (statement) o de fila (for each row)
 when CONDICION--opcional
 begin
  CUERPO DEL DISPARADOR--sentencias
 end NOMBREDISPARADOR; 

ASM parameters

INSTANCE_TYPE
should be set to ASM for ASM instances. This is the only parameter
that must be defined. For database instances, this is set to the value RDBMS.

ASM_POWER_LIMIT controls the speed for a rebalance operation. Values range from 1 through 11, with 11 being the fastest. If omitted, this value defaults to 1.

ASM_DISKSTRING is an operating system–dependent value used by ASM to limit the set of disks considered for discovery. The default value is the null string, and this will be sufficient in most cases. A more restrictive value as shown above may reduce the time
required for ASM to perform discovery, and thus improve disk group mount times.

ASM_PREFERRED_READ_FAILURE_GROUPS specifies the failure groups that contain preferred read disk. This is useful in extended or stretched cluster databases that have
mirrored copies of data with one of the copies in close proximity to the server.

DIAGNOSTIC_DEST specifies the location of the Automatic Diagnostic Repository (ADR) home. Trace files, alert logs, core files, and incident files can be found under this
directory. The default value of this parameter is derived from the value of ORACLE_BASE.

ASM_DISKGROUPS is the list of names of disk groups to be mounted by an ASM instance at startup, or when the ALTER DISKGROUP ALL MOUNT command is used. Oracle Restart can mount disk groups if they are listed as dependencies even if they are not listed with the ASM_DISKGROUPS parameter. This parameter has no default value.

LARGE_POOL_SIZE specifies (in bytes) the size of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. The ASM instance makes use of automatic memory management, so this parameter serves as a minimum size that the large pool can be lowered to.

REMOTE_LOGIN_PASSWORDFILE specifies whether the Oracle software checks for a password file. The default value is EXCLUSIVE.

Automatic memory management is enabled by default on ASM instances,
event when the that MEMORY_TARGET parameter is not explicitly set. This is the only parameter you need to set for complete ASM memory management. Oracle Corporation strongly lrecommends that you use automatic memory management for ASM

SQL STATEMENTS

ASM_DISKSTRING

 ASM_DISKSTRING
El parametro ASM_DISKSTRING especifica los discos que la instancia ASM debe descubrir y usar para guardar los archivos

Archivelog mode

ARCHIVELOG mode

The mode of the database in which log transport services archives filled online redo logs to disk. Specify the mode at database creation or by using the SQL ALTER DATABASE ARCHIVELOG statement. You can enable automatic archiving either dynamically using the SQL ALTER SYSTEM ARCHIVE LOG START statement or by setting the initialization parameter LOG_ARCHIVE_START to true.

Running your database in ARCHIVELOG mode has several advantages over NOARCHIVELOG mode. You can:

• Back up your database while it is open and being accessed by users
• Recover your database to any desired point in time

To protect your database that is in ARCHIVELOG mode in case of failure, back up your archived logs.

See also archived redo log, NOARCHIVELOG mode, and redo log

 

 

archiver process (ARCn)

 On the primary database site, the process (or a SQL session performing an archival operation) that creates a copy of the online redo logs, either locally or remotely, for standby databases. On the standby database site, the ARCn process archives the standby redo logs to be applied by the managed recovery process (MRP). 

ARCH

Setting this attribute on the LOG_ARCHIVE_DEST_n initialization parameter indicates that the archiver process (ARCn) will create archived redo logs on the primary database and also transmit redo logs for archival at specified destinations. 

To see process

select *

from

   v$session

where

   type ='BACKGROUND';

Servicios Oracle

Servicios de bases de datos (servicios) son abstracciones lógicas para la gestión de cargas de trabajo de base de datos Oracle. Los servicios dividen las cargas de trabajo en grupos mutuamente disjuntos. Cada servicio representa una carga de trabajo con atributos comunes, los umbrales de nivel de servicio, y las prioridades. La agrupación se basa en los atributos de trabajo que podrían incluir la función de aplicación que se utilizará, la prioridad de la ejecución de la función de aplicación, la clase de trabajo que se logró, o el rango de datos utilizados en la función de aplicación o tipo de trabajo. Por ejemplo, la suite Oracle E-Business define un servicio para cada responsabilidad, tales como contabilidad general, cuentas por cobrar, la entrada de pedidos, y así sucesivamente. Cada servicio de base de datos tiene un nombre único.

PGA , SGA y Automatic Memory Management

The Program Global Area (PGA) is a memory buffer that contains data and control information for a server process. A PGA is created by Oracle when a server process is started.

When Automatic Memory Management is enabled, the database will automatically set the optimal distribution of memory. The distribution of memory will change from time to time to accomodate changes in the workload.

The System Global Area (SGA) is a group of shared memory structures that contains data and control information for one Oracle database. The SGA is allocated in memory when an Oracle database instance is starte

 emca -config dbcontrol db