lunes, 23 de diciembre de 2013

DataGuard 11G Physical Standby Oracle Base

Data Guard Physical Standby Setup in Oracle Database 11g Release 2

Data Guard is the name for Oracle's standby database solution, used for disaster recovery and high availability. This article contains an updated version of the 9i physical standby setup method posted here.
Related articles.

Assumptions

  • You have two servers (physical or VMs) with an operating system and Oracle installed on them. In this case I've used Oracle Linux 5.6 and Oracle Database 11.2.0.2.
  • The primary server has a running instance.
  • The standby server has a software only installation.

Primary Server Setup

Logging

Check that the primary database is in archivelog mode.
SELECT log_mode FROM v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL>
If it is noarchivelog mode, switch is to archivelog mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Enabled forced logging by issuing the following command.
ALTER DATABASE FORCE LOGGING;

Initialization Parameters

Check the setting for the DB_NAME and DB_UNIQUE_NAME parameters. In this case they are both set to "DB11G" on the primary database.
SQL> show parameter db_name

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_name         string  DB11G

SQL> show parameter db_unique_name

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_unique_name        string  DB11G

SQL>
The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY".
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DB11G,DB11G_STBY)';
Set suitable remote archive log destinations. In this case I'm using the fast recovery area for the local location, but you could specify an location explicitly if you prefer. Notice the SERVICE and the DB_UNIQUE_NAME for the remote location reference the standby location.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.
ALTER SYSTEM SET FAL_SERVER=DB11G_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

Service Setup

Entries for the primary and standby databases are needed in the "$ORACLE_HOME/network/admin/tnsnames.ora" files on both servers. You can create these using the Network Configuration Utility (netca) or manually. The following entries were used during this setup.
DB11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

DB11G_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11G.WORLD)
    )
  )

Backup Primary Database

If you are planning to use an active duplicate to create the standby database, then this step is unnecessary. For a backup-based duplicate, or a manual restore, take a backup of the primary database.
$ rman target=/

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create Standby Controlfile and PFILE

Create a controlfile for the standby database by issuing the following command on the primary database.
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/db11g_stby.ctl';
Create a parameter file for the standby database.
CREATE PFILE='/tmp/initDB11G_stby.ora' FROM SPFILE;
Amend the PFILE making the entries relevant for the standby database. I'm making a replica of the original server, so in my case I only had to amend the following parameters.
*.db_unique_name='DB11G_STBY'
*.fal_server='DB11G'
*.log_archive_dest_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'

Standby Server Setup (Manual)

Copy Files

Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Archivelogs and backups
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/archivelog /u01/app/oracle/fast_recovery_area/DB11G
$ scp -r oracle@ol5-112-dga1:/u01/app/oracle/fast_recovery_area/DB11G/backupset /u01/app/oracle/fast_recovery_area/DB11G

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs
Notice, the backups were copied across to the standby server as part of the FRA copy. If your backups are not held within the FRA, you must make sure you copy them to the standby server and make them available from the same path as used on the primary server.

Start Listener

Make sure the listener is started on the standby server.
$ lsnrctl start

Restore Backup

Create the SPFILE form the amended PFILE.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/initDB11G_stby.ora';
Restore the backup files.
$ export ORACLE_SID=DB11G
$ rman target=/

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

Create Redo Logs

Create online redo logs for the standby. It's a good idea to match the configuration of the primary server.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/DB11G/online_redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database (in case of switchovers). The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs. In my case, the following is standby redo logs must be created on both servers.
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;
Once this is complete, we can start the apply process.

Standby Server Setup (DUPLICATE)

Copy Files

Create the necessary directories on the standby server.
$ mkdir -p /u01/app/oracle/oradata/DB11G
$ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G
$ mkdir -p /u01/app/oracle/admin/DB11G/adump
Copy the files from the primary to the standby server.
$ # Standby controlfile to all locations.
$ scp oracle@ol5-112-dga1:/tmp/db11g_stby.ctl /u01/app/oracle/oradata/DB11G/control01.ctl
$ cp /u01/app/oracle/oradata/DB11G/control01.ctl /u01/app/oracle/fast_recovery_area/DB11G/control02.ctl

$ # Parameter file.
$ scp oracle@ol5-112-dga1:/tmp/initDB11G_stby.ora /tmp/initDB11G_stby.ora

$ # Remote login password file.
$ scp oracle@ol5-112-dga1:$ORACLE_HOME/dbs/orapwDB11G $ORACLE_HOME/dbs

Start Listener

When using active duplicate, the standby server requires static listener configuration in a "listener.ora" file. In this case I used the following configuration.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G.WORLD)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dga2.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
Make sure the listener is started on the standby server.
$ lsnrctl start

Create Standby Redo Logs on Primary Server

The DUPLICATE command automatically creates the standby redo logs on the standby. To make sure the primary database is configured for switchover, we must create the standby redo logs on the primary server.
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/DB11G/standby_redo04.log') SIZE 50M;

Create Standby Using DUPLICATE

Start the auxillary instance on the standby server by starting it using the temporary "init.ora" file.
$ export ORACLE_SID=DB11G
$ sqlplus / as sysdba

SQL> STARTUP NOMOUNT PFILE='/tmp/initDB11G_stby.ora';
Connect to RMAN, specifying a full connect string for both the TARGET and AUXILLARY instances. DO not attempt to use OS authentication.
$ rman TARGET sys/password@DB11G AUXILIARY sys/password@DB11G_STBY
Now issue the following DUPLICATE command.
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='DB11G_STBY' COMMENT 'Is standby'
    SET LOG_ARCHIVE_DEST_2='SERVICE=db11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G'
    SET FAL_SERVER='DB11G' COMMENT 'Is primary'
  NOFILENAMECHECK;
A brief explanation of the individual clauses is shown below.
  • FOR STANDBY: This tells the DUPLICATE command is to be used for a standby, so it will not force a DBID change.
  • FROM ACTIVE DATABASE: The DUPLICATE will be created directly from the source datafile, without an additional backup step.
  • DORECOVER: The DUPLICATE will include the recovery step, bringing the standby up to the current point in time.
  • SPFILE: Allows us to reset values in the spfile when it is copied from the source server.
  • NOFILENAMECHECK: Destination file locations are not checked.
Once the command is complete, we can start the apply process.

Start Apply Process

Start the apply process on standby server.
# Foreground redo apply. Session never returns until cancel. 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
If you prefer, you can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
Provided you have configured standby redo logs, you can start real-time apply using the following command.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

Test Log Transport

On the primary server, check the latest archived redo log and force a log switch.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;
Check the new archived redo log has arrived at the standby server and been applied.
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

Protection Mode

There are three protection modes for the primary database:
  • Maximum Availability: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If no standby location is available, it acts in the same manner as maximum performance mode until a standby becomes available again.
  • Maximum Performance: Transactions on the primary commit as soon as redo information has been written to the online redo log. Transfer of redo information to the standby server is asynchronous, so it does not impact on performance of the primary.
  • Maximum Protection: Transactions on the primary do not commit until redo information has been written to the online redo log and the standby redo logs of at least one standby location. If not suitable standby location is available, the primary database shuts down.
By default, for a newly created standby database, the primary database is in maximum performance mode.
SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>
The mode can be switched using the following commands. Note the alterations in the redo transport attributes.
-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements.
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
On the original standby database issue the following commands.
-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;
Once this is complete, test the log transport as before. If everything is working fine, switch the primary database back to the original server by doing another switchover. This is known as a switchback.

Failover

If the primary database is not available the standby database can be activated as a primary database using the following statements.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;
Since the standby database is now the primary database it should be backed up immediately.
The original primary database can now be configured as a standby. If Flashback Database was enabled on the primary database, then this can be done relatively easily (shown here). If not, the whole setup process must be followed, but this time using the original primary server as the standby.

Flashback Database

It was already mentioned in the previous section, but it is worth drawing your attention to Flashback Database once more. Although a switchover/switchback is safe for both the primary and standby database, a failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.
An alternative is to enable flashback database on the primary (and the standby if desired) so in the event of a failover, the primary can be flashed back to the time before the failover and quickly converted to a standby database. That process is shown here.

Read-Only Standby and Active Data Guard

Once a standby database is configured, it can be opened in read-only mode to allow query access. This is often used to offload reporting to the standby server, thereby freeing up resources on the primary server. When open in read-only mode, archive log shipping continues, but managed recovery is stopped, so the standby database becomes increasingly out of date until managed recovery is resumed.
To switch the standby database into read-only mode, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
To resume managed recovery, do the following.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
In 11g, Oracle introduced the Active Data Guard feature. This allows the standby database to be open in read-only mode, but still apply redo information. This means a standby can be available for querying, yet still be up to date. There are licensing implications for this feature, but the following commands show how active data guard can be enabled.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Since managed recovery continues with active data guard, there is no need to switch back to managed recovery from read-only mode in this case.

Snapshot Standby

Introduced in 11g, snapshot standby allows the standby database to be opened in read-write mode. When switched back into standby mode, all changes made whilst in read-write mode are lost. This is achieved using flashback database, but the standby database does not need to have flashback database explicitly enabled to take advantage of this feature, thought it works just the same if it is.
If you are using RAC, turn off all but one of the RAC instances. Make sure the instance is in MOUNT mode.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Make sure managed recovery is disabled.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Convert the standby to a snapshot standby. The following example queries the V$DATABASE view to show that flashback database is not enabled prior to the conversion operation.
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE OPEN;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

SQL>
You can now do treat the standby like any read-write database.
To convert it back to the physical standby, losing all the changes made since the conversion to snapshot standby, issue the following commands.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
SELECT flashback_on FROM v$database;

FLASHBACK_ON
------------------
NO

SQL>
The standby is once again in managed recovery and archivelog shipping is resumed. Notice that flashback database is still not enabled.
For more information see:

martes, 17 de diciembre de 2013

Insufficient Memory Target Errors

 

4.3.2 Insufficient Memory Target Errors

On Linux systems, if the operating system /dev/shm mount size is too small for the Oracle system global area (SGA) and program global area (PGA), then you encounter the following error:
ORA-00845: MEMORY_TARGET not supported on this system. 
The cause of this error is an insufficient /dev/shm allocation. The total memory size of the SGA and PGA, which sets the initialization parameter MEMORY_TARGET or MEMORY_MAX_TARGET, cannot be greater than the shared memory file system (/dev/shm) on your operating system.
Background
Automatic Memory Management (AMM) has been updated in Oracle ASM 11g Release 2. It manages both the SGA and PGA together. It is managed by the Memory Manager Process (MMAN). In this release, note the following changes to AMM:
  • It uses MEMORY_TARGET instead of SGA_TARGET
  • It uses MEMORY_MAX_TARGET instead of SGA_MAX_SIZE (defaults to MEMORY_TARGET)
  • It uses memory allocated by /dev/shm
If the value of max_target is set to a value greater than the allocation for the /dev/shm size, then you may encounter the error ORA-00845: MEMORY_TARGET not supported on this system.
Note:
An ORA-00845 error can also occur if /dev/shm is not properly mounted. To rule out this possibility, run the command df -k to ensure that /dev/shm is mounted. For example:
$ df -k 
 
Filesystem 1K-blocks Used Available Use% Mounted on 
shmfs 6291456 832356 5459100 14% /dev/shm
Solution
Increase the /dev/shm mountpoint size.
For example:
# mount -t tmpfs shmfs -o size=2g /dev/shm
To make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:
shmfs /dev/shm tmpfs size=2g 0

jueves, 21 de noviembre de 2013

Expresiones Regulares

http://www.oracle.com/technetwork/es/articles/sql/expresiones-regulares-base-de-datos-1569340-esa.HTML


REGEXP_LIKECondición que se puede utilizar en la cláusula WHERE de una sentencia SQL SELECT y que permite retornar aquellas filas que coinciden con el patrón especificado en una expresión regular.
REGEXP_COUNTFunción que permite contar el número de veces que un patrón aparece en una cadena de caracteres.
REGEXP_INSTRFunción que permite determinar la posición de inicio de un patrón específico en una cadena de caracteres.
REGEXP_REPLACEFunción que permite hacer búsqueda y reemplazo en una cadena de caracteres utilizando expresiones regulares para la búsqueda.
REGEXP_SUBSTRFunción para extraer de una cadena una subcadena de caracteres que coincidan con un patrón especificado en una expresión regular.

El patron de telefonos es este:

'([A-Z0-9]*\-*\.*\_*\@*\#*\s*)(.*)(\s*TELEFONO[E|FO|NO|AX|(\s)]*\s*(\-)*(\_)*(\.)*(\:)*[A-Z0-9]*\s*([\d]{1,7}\-*\s*[\d]{2,4}\-*\s*([\d]{2,6})*\-*\s*([\d]{2,7})*[Y]*))+(.*)([A-Z0-9]*\-*\.*\_*\@*\#*\s*)'
 
El patron de mail es este:  
'((\w.*)\s+)*(.+\s*@.+\.[A-Za-z]+)(.*)*
 

Prólogo
Históricamente Oracle había ofrecido prestaciones muy básicas para el uso de expresiones regulares y la manipulación de cadenas de caracteres. Aquellos desarrolladores que necesitaban hacer uso de expresiones regulares se veían prácticamente obligados a extraer los datos de la base para manipular las cadenas de caracteres en capas intermedias de la aplicación. A partir de la versión 10g, con la introducción de las llamadas funciones REGEXP (REGular EXPressions), Oracle nos ofrece una batería de sofisticadas y potentes herramientas nativas que permiten al desarrollador centralizar el procesamiento intensivo de cadenas de caracteres dentro de la base de datos Oracle y con los lenguajes SQL y PL/SQL.
Tabla de Contenidos
¿Qué son las Expresiones Regulares?
Antes de introducirnos bien en el mundo de las expresiones regulares es necesario que le perdamos un poco el miedo. Lo más común es que un ser humano huya desesperadamente cuando se encuentra por primera vez con una expresión regular como la siguiente:
^Ab*[0-8]\*(ha|ve)n$

Sin embargo, un buen manejo de las expresiones regulares es algo tan poderoso que vale la pena que nos tomemos un tiempo para aprender a utilizarlas. Además, saber manejar expresiones regulares nos servirá no solamente en el mundo de las bases de datos Oracle; las podremos usar también en otros lenguajes de programación como Perl, Java, .Net, Php y Unix Shell Scripting, entre otros.
La idea de este documento es hacer una introducción al tema de manera práctica, comenzando con ejemplos sencillos para luego ir aumentando la complejidad.
Comenzamos entonces definiendo a una expresión regular como una cadena de caracteres que definen un patrón de búsqueda. En una expresión regular encontramos literales y metacaracteres. Los literales se leen al pie de la letra. Los metacaracteres son caracteres que tienen un significado especial.
Tomemos por ejemplo la siguiente expresión regular:
[n|p]ata

Los metacaracteres son los corchetes y el símbolo pipe (|). El resto son literales.
Los corchetes agrupan a varios caracteres en un solo caracter.
El símbolo pipe indica un símbolo u otro; es decir la 'n' o la 'p'.
Luego, la expresión regular:
[n|p]ata

Coincide con las cadenas:
nata
pata

Seguramente el lector se preguntará cómo determinar qué elementos de una expresión regular son literales y qué elementos son metacaracteres. Más adelante encontrará la respuesta. Sigamos.
¿Para qué sirven las Expresiones Regulares?
En una base de datos Oracle existen diversos escenarios en que la implementación de expresiones regulares constituye una herramienta de gran utilidad:
  • Búsqueda de texto. Las expresiones regulares nos permiten hacer búsquedas complejas de cadenas de caracteres dentro de las columnas de nuestras tablas.
  • Formateo de datos. Podemos modificar los datos proyectados en las sentencias SELECT, invirtiendo palabras, agregando o quitando caracteres, etc.
  • Definición de constraints. A fin de forzar el contenido de una columna para que se ajuste a un formato determinado: casilla de correo, número telefónico, etc.
  • Manipulación de datos. Por ejemplo, regularizando datos en procesos de migración desde aplicaciones legacy y aplicando reglas de búsqueda y reemplazo (Search & Replace).
Entendiendo las Expresiones Regulares
Como se había dicho anteriormente, en una expresión regular encontramos literales y metacaracteres. Al analizar una expresión regular, lo primero que tenemos que hacer es determinar qué elementos son literales y qué elementos son metacaracteres (también llamados operadores). Existe una larga lista de metacaracteres soportados por Oracle. No es la intención estudiarlos a todos en este documento. A continuación iremos viendo algunos.
El metacaracter punto (.)
El metacaracter punto coincide con cualquier carácter. Por ejemplo, en la expresión regular
ca.a

Para que una cadena de caracteres coincida con dicho patrón:
- Debe estar el literal 'c'.
- Le debe seguir el literal 'a'
- Le debe seguir un y solamente un carácter cualquiera.
- Le debe seguir el literal 'a'
En la expresión regular encontramos 3 literales y el metacaracter punto. Analicemos las siguientes cadenas y veamos cuales coinciden con el patrón buscado y cuáles no.
Cadena¿Coincide?Análisis
casaSiCoinciden los literales y en la posición del operador punto aparece sólo un carácter
canaSiCoinciden los literales y en la posición del operador punto aparece sólo un carácter
caraSiCoinciden los literales y en la posición del operador punto aparece sólo un carácter
cantaNoEn la posición del punto aparece más de un carácter
palaNoEl literal 'p' en la primer posición no respeta el literal ´c´ definido en la expresión regular

El metacaracter suma (+)
El metacaracter suma coincide con una o más ocurrencias de la subexpresión que lo precede. Por ejemplo, analicemos la expresión regular
cas+a

Para que una cadena de caracteres coincida con dicho patrón:
- Debe estar el carácter 'c'
- Le debe seguir el carácter 'a'
- Le debe seguir una o mas ocurrencias del carácter 's'
- Le debe seguir el carácter 'a'
Vemos nuevamente tres literales y el metacaracter Suma. Busquemos el patrón en las siguientes cadenas y veamos las coincidencias.
Cadena¿Coincide?Análisis
cassa
cassssa
cassssssssa
SiEl símbolo más (+) es precedido por la subexpresión "s". Por lo tanto todas las cadenas que tienen una o más "s" en a partir de la tercer posición y respetan el resto de los literales, coinciden con el patrón representado en la expresión regular.
CaaNoEl patrón definido requiere al menos una ‘s’ en la tercer posición
LasaNoNo respeta todos los literales de la expresión regular
TassaNoNo respeta todos los literales de la expresión regular

El metacaracter asterisco (*)
Coincide con cero o más ocurrencias de la subexpresión que le precede al asterisco. Analicemos la expresión:
cas*a

Para que una cadena de caracteres coincida con dicho patrón:
- Debe estar el carácter 'c' - Le debe seguir el carácter 'a' - Le debe seguir cero o mas ocurrencias del carácter 's'. Es decir que el carácter 's' puede o no estar. - Le debe seguir el carácter 'a'
Analicemos qué pasa con las mismas cadenas de caracteres del ejemplo anterior:
Cadena¿Coincide?Análisis
Casa
cassssa
cassssssssa
SiEl símbolo asterisco (*) es precedido por la subexpresión "s". Por lo tanto todas las cadenas que tienen cero o más "s" a partir de la tercera posición y respetan el resto de los literales, coinciden con el patrón representado en la expresión regular.
caaSiEl patrón acepta la no existencia del carácter 's' en la tercera posición.
lasaNoNo respeta todos los literales de la expresión regular
tassaNoNo respeta todos los literales de la expresión regular

El metacaracter [char...]
Coincide con una sola ocurrencia de alguno de los caracteres de la lista entre corchetes. En la lista, todos los caracteres son tomados como literales excepto algunos caracteres que son interpretados como metacaracteres. Aquí nos limitaremos a mencionar el metacaracter '-' para representar rangos de caracteres. Analicemos la siguiente expresión regular:
ca[snt]a

Para que una cadena de caracteres coincida con dicho patrón:
- Debe estar el carácter 'c'
- Le debe seguir el carácter 'a'
- Le debe seguir uno y solamente uno de los siguientes caracteres: 's', 'n' o 't'.
- Le debe seguir el carácter 'a'
Cadena¿Coincide?Análisis
casaSiCoinciden los literales y en la posición del operador aparece sólo un carácter y es de los de la lista
canaSiCoinciden los literales y en la posición del operador aparece sólo un carácter y es de los de la lista
cataSiCoinciden los literales y en la posición del operador aparece sólo un carácter y es de los de la lista
caraNoEn la posición del operador aparece un solo carácter pero no está en la lista
cantaNoSi bien en la posición del operador aparecen literales de la lista; son más de uno
pasaNoNo respeta el primer literal de la lista

Como se dijo anteriormente, en la lista se puede utilizar el metacaracter rango '-'. Haciendo uso de este metacaracter la siguiente lista de caracteres:
[123456789]

Podemos expresarla como
[1-9]

Y la siguiente lista de caracteres
[abcdef]

Podemos expresarla como
[a-f]

Analicemos ahora la siguiente expresión regular:
a[3-7][f-i]9

Para que una cadena coincida con el patrón:
- Debe tener el literal 'a'
- Le debe seguir un numero de 3 a 7. Es decir: 3, 4, 5, 6 ó 7.
- Le debe seguir una letra de la f a la i. Es decir 'f', 'g', 'h' o 'i'
- Le debe seguir el numero 9
Cadena¿Coincide?Análisis
a3h9SiCoinciden los literales y en la posición de los operadores aparecen literales incluidos en los rangos especificados en el patrón de la expresión regular.
a33f9NoEn la posición del primer operador aparece más de un caracter
a3z9NoLa 'z' no está incluida en el rango del segundo operador

El metacaracter [^char...]
El sombrerito (^) que precede a la lista indica que los literales que le siguen no deben estar en la cadena de caracteres para que se produzca una coincidencia. Veamos,
ca[^snt]a

Para que una cadena de caracteres coincida con dicho patrón:
- Debe estar el carácter 'c'
- Le debe seguir el carácter 'a'
- Le debe seguir uno y solamente un carácter que no sea ni 's' ni 'n' ni 't'.
- Le debe seguir el carácter 'a'
Cadena¿Coincide?Análisis
casaNoCoinciden los literales y en la posición del operador aparece un carácter de los negados en la lista
canaNoCoinciden los literales y en la posición del operador aparece un carácter de los negados en la lista
cataNoCoinciden los literales y en la posición del operador aparece un carácter de los negados en la lista
caraSiCoinciden los literales y en la poscion del operador aparece un carácter que no está en la lista de literales negados.
cantaNoEn la posición del operador aparece más de un valor
paraNoNo respeta el primer literal de la lista

El metacaracter subexpresión (expr)
Considera a toda la expresión entre paréntesis como una unidad. La expresión puede ser una simple cadena de literales o una expresión compleja conteniendo otros metacaracteres. Analicemos la siguiente expresión regular
chau(hola)*chau

Ahora el asterisco precede a la expresión
hola

El asterisco indica que la expresión
hola

Puede aparecer cero ó más veces
Cadena¿Coincide?Análisis
chauchauSiCoinciden los literales y la expresión 'hola' aparece cero veces
chauholachauSiCoinciden los literales y la expresión 'hola' aparece una vez, es decir cero o más veces.
chauholaholachauSiCoinciden los literales y la expresión 'hola' aparece dos veces, es decir cero o más veces.
holachauNoNo aparecen el literal chau' que precede al ‘hola’
chauNoNo aparece el literal 'chau' que precede al ‘hola’

El metacaracter de anclaje de principio de línea (^)
Coincide con el principio de línea y el operador está representado con el caracter sombrerito (^). En la expresión regular
^hola

Los literales que componen la palabra 'hola' deben estar al inicio de la línea para que se produzca la coincidencia con el patrón expresado.
Cadena¿Coincide?Análisis
holaSiLos literales que componen la palabra 'hola' están al inicio de la línea
chauholaNoLa línea no comienza con los literales que conforman la palabra 'hola'
holachauSiLos literales que componen la palabra 'hola' están al inicio de la línea
holaholaSiLos literales que componen la palabra 'hola' están al inicio de la línea

El metacaracter de anclaje de fin de línea ($)
Coincide con el final de línea y el operador está representado con el carácter pesos ($). En la expresión regular
hola$

Los literales que componen la palabra 'hola' deben estar al final de la línea para que se produzca la coincidencia con el patrón expresado.
Cadena¿Coincide?Análisis
HolaSiLos literales que componen la palabra 'hola' están al final de la línea
chauholaSiLa línea finaliza con los literales que conforman la palabra 'hola'
holachauNoLos literales que componen la palabra 'hola' no están al final de la línea
holaholaSiLos literales que componen la palabra 'hola' están al final de la línea

El metacaracter de escape (\)
Precediendo a un metacaracter con el símbolo de escape, el metacaracter será interpretado como un literal. El doble carácter de escape (\\) permite considerar al carácter de escape como literal.
En la expresión regular
hola\*

Cadena¿Coincide?Análisis
holaNoFalta el literal '*' que sigue literal 'a'
Hola*SiCoinciden todos los literales
hol*NoFaltan los literales 'a' y ‘*’

Construyendo Expresiones Regulares complejas
Hasta aquí hemos visto algunos de los metacaracteres que se usan con más frecuencia. Combinando varios de estos metacaracteres en una sola expresión regular, podemos hacer construcciones más complejas y muy poderosas. Analicemos la siguiente expresión regular en la que encontramos varios metacaracteres
^hola[0-9]*chau[^a]+$

Hagamos un desglose de la expresión regular:
^holaAl principio de la línea debe estar la palabra 'hola'
[0-9]*Luego de la palabra 'hola' deben aparecer cero o mas dígitos del 0 al 9
chauLuego debe aparecer el literal 'chau'
[^a]+$La línea debe continuar hasta el final con uno o más caracteres distintos de 'a'

Busquemos el patrón en algunas cadenas de caracteres
Cadena¿Coincide?Análisis
hola123chaubSiComienza con el texto 'hola'. Le siguen varios dígitos entre 0 y 9. Le sigue el literal 'chau' y finaliza con al menos una letra distinta de 'a'.
holachaucSiComienza con el texto 'hola'. No aparecen dígitos del 0 al 9 (el asterisco indica que esto es aceptado para que haya coincidencia). Luego aparece el literal 'chau' y se finaliza con una letra distinta de 'a'
hola0chauaNoFinaliza con una ‘a’

Uso de las Expresiones Regulares en Oracle
Hasta aquí hemos visto un poco de teoría y algunos ejemplos prácticos para conocer y aprender un poco acerca de las expresiones regulares. Pero, ¿cómo podemos hacer uso de este conocimiento adquirido en una base de datos Oracle?
A partir de la versión 10g Oracle nos ofrece un grupo de nuevas funciones y condiciones para poder manejar expresiones regulares en el lenguaje SQL:
REGEXP_LIKECondición que se puede utilizar en la cláusula WHERE de una sentencia SQL SELECT y que permite retornar aquellas filas que coinciden con el patrón especificado en una expresión regular.
REGEXP_COUNTFunción que permite contar el número de veces que un patrón aparece en una cadena de caracteres.
REGEXP_INSTRFunción que permite determinar la posición de inicio de un patrón específico en una cadena de caracteres.
REGEXP_REPLACEFunción que permite hacer búsqueda y reemplazo en una cadena de caracteres utilizando expresiones regulares para la búsqueda.
REGEXP_SUBSTRFunción para extraer de una cadena una subcadena de caracteres que coincidan con un patrón especificado en una expresión regular.

Veamos algunos ejemplos de implementación de estas condiciones y funciones combinadas con el uso de expresiones regulares.
La condición REGEXP_LIKE es similar a la condición LIKE pero, a diferencia del LIKE, utiliza patrones basados en expresiones regulares para la búsqueda de coincidencias en las cadenas de caracteres analizadas.
En el siguiente ejemplo, vemos una tabla de empleados cuyos nombres fueron migrados de un sistema legacy. En muchos casos el nombre ha quedado separado del apellido por varios espacios en blanco:
SQL> select nombre from empleados;
 
NOMBRE
----------------------------------------
Fernando         Garcia
Marcelo  Burgos
       Marcelo         Ochoa
Gerardo     Tezza
Clarisa Maman Orfali
Rodolfo  Pajuelo Quispe
 
6 rows selected.

El único caso de un solo espacio entre el nombre y el apellido es el de Clarisa Maman Orfali. Veamos cómo podemos construir un query que detecte aquellas filas en que haya dos o más espacios separando nombres de apellidos.
SQL> select nombre
  2  from empleados
  3  where regexp_like(nombre, '[ ][ ][ ]*');
 
NOMBRE
----------------------------------------
Fernando         Garcia
Marcelo  Burgos
       Marcelo         Ochoa
Gerardo     Tezza
Rodolfo  Pajuelo Quispe

Corrijamos esta situación dejando un solo blanco de separación en donde hay dos o más espacios en blanco.
SQL> update empleados
  2  set nombre = regexp_replace(nombre, '[ ][ ][ ]*', ' ')
  3  where regexp_count(nombre, '[ ][ ][ ]*') > 0;
 
5 rows updated.

Veamos cómo quedo la tabla luego de la corrección
SQL> select nombre from empleados;
 
NOMBRE
----------------------------------------
Fernando Garcia
Marcelo Burgos
 Marcelo Ochoa
Gerardo Tezza
Clarisa Maman Orfali
Rodolfo Pajuelo Quispe
 
6 rows selected.

Bastante bien. Salvo el caso de Marcelo Ochoa, en donde nos quedó un espacio en blanco al principio de la línea. Eliminemos entonces los espacios en blanco que están al principio de la línea.
SQL> update empleados
  2  set nombre = regexp_replace(nombre, '^ ')
  3  where regexp_count(nombre, '^ ') > 0
  4  /
 
1 row updated.
 
SQL> select nombre from empleados;
 
NOMBRE
----------------------------------------
Fernando Garcia
Marcelo Burgos
Marcelo Ochoa
Gerardo Tezza
Clarisa Maman Orfali
Rodolfo Pajuelo Quispe
 
6 rows selected.

En este otro ejemplo vemos una tabla de impuestos en la que almacenamos un código de identificación tributaria numérico y que debe respetar el siguiente formato:
99-99999999-99 
 
SQL> select cuit from impuestos;
 
CUIT
-------------
20-20198123-9
10-43112345-8
1-22123987-9
8883838382
aa-75212456-x

Busquemos aquellos códigos que no están respetando el formato requerido:
select cuit
from impuestos
where not regexp_like (cuit, '[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]')
 
CUIT
-------------
1-22123987-9
8883838382
aa-75212456-x

A fin de evitar que la tabla siga admitiendo códigos que no respetan el formato, podemos crear una constraint que fuerce el ingreso de códigos que respeten el formato.
alter table impuestos
add constraint c_cuit
check (regexp_like(cuit,'[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]'))
enable novalidate
/
Table altered.

Veamos qué ocurre al ingresar nuevas filas a la table de impuestos
SQL> insert into impuestos (cuit) values ('20-17979866-2');
 
1 row created.
 
SQL> insert into impuestos (cuit) values ('1x-17979866-2');
insert into impuestos (cuit) values ('1x-17979866-2')
*
ERROR at line 1:
ORA-02290: check constraint (SYS.C_CUIT) violated
 
 
SQL> insert into impuestos values ('12888122812');
insert into impuestos values ('12888122812')
*
ERROR at line 1:
ORA-02290: check constraint (SYS.C_CUIT) violated

Profundizando el conocimiento y uso de las expresiones regulares en Oracle
Hemos visto varios ejemplos para demostrar el potencial y utilidad que nos ofrecen las expresiones regulares en las bases de datos Oracle. Existen muchos más metacaracteres que no hemos mencionado aquí. Las funciones tampoco las hemos visto en profundidad; hay más argumentos para hacer búsquedas que ignoren mayúsculas y minúsculas, etc. Para seguir profundizando el tema, aquí dejamos algunos links de interés:
Using Regular Expressions in Database Applications
Oracle® Database Advanced Application Developer's Guide
11g Release 2 (11.2)
Part Number E25518-04
http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_regexp.htm#ADFNS1003
Introducing Oracle Regular
Expressions
An Oracle White Paper
Author: Peter Linsley
http://www.oracle.com/technetwork/database/focus-areas/application-development/twp-regular-expressions-133133.pdf
Regular-Expressions.info
The Premier website about Regular Expressions
http://www.regular-expressions.info/

martes, 19 de noviembre de 2013

martes, 5 de noviembre de 2013

CAMBIAR PASSWORD ASM

1. How to create asmsnmp user
——————————————-

First Check which all users are present for ASM >>

SQL> select * from v$pwfile_users;
no rows selected

OR

ASMCMD> lspwusr
Username sysdba sysoper sysasm 
This shows no user are present

A) Create a password file if not already present
$orapwd file=/u01/app/11.2.0/grid/dbs/orapw+ASM password=


++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Remember below important point about password file in ASM
If there are two nodes with +ASM1 running on node 1 and +ASM2 running on node2.
– Pre 11gR2 –
Password file on Node1: orapw+ASM1
Password file on Node2: orapw+ASM2
– 11gR2 –
Password file on Node1: orapw+ASM
Password file on Node2: orapw+ASM
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


B) Copy the password file to other nodes
$ scp orapw+ASM :/u01/app/11.2.0/grid/dbs/

copy the password files to other nodes if you have more than 2 nodes in the RAC.

C) create user and give sysdba privileges

SQL>create user asmsnmp identified by ;
SQL> grant sysdba to asmsnmp;


Check again for users in ASM  >>

SQL> select * from v$pwfile_users;
 
USERNAME              SYSDB                SYSOP             SYSAS
——————————————————————————————————————— —– —– —————————————————————————————————————–
SYS                    TRUE                TRUE                 TRUE
ASMSNMP                TRUE                FALSE                FALSE

SQL> show parameter pass
NAME                                   TYPE                            VALUE
———————————— —————————————————————————————– ——————————————————————————————————
remote_login_passwordfile             string                          EXCLUSIVE

The ASM instance parameter “REMOTE_LOGIN_PASSWORDFILE” has to be set to EXCLUSIVE or you will get an ORA-01999 error.

Also you can check  user detials from asmcmd

ASMCMD> lspwusr
Username        sysdba         sysoper       sysasm
SYS             TRUE           TRUE          FALSE
ASMSNMP         TRUE           FALSE         FALSE



How to Change password for ASMSNMP
——————————————————–
ASMCMD> lspwusr
Username  sysdba sysoper sysasm
SYS       TRUE   TRUE     TRUE
ASMSNMP   TRUE   FALSE    FALSE

ASMCMD> orapwusr --modify --password ASMSNMP
Enter password: *******
(give new password and press enter)
orapwusr attempts to update passwords on all nodes in a cluster. The command requires the SYSASM privilege to run. A user logged in as SYSDBA cannot change its password using this command.

Test new asmsnmp password >>
————————–
$ sqlplus asmsnmp/asmsnmp as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 6 12:57:30 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL>

martes, 15 de octubre de 2013

CHECK CONSTRAINT FECHA

I am trying to check add the following constraint but Oracle returns the error shown below.

ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > SYSDATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= SYSDATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (SYSDATE + 730))));
Error:
Error report:
SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"
*Cause:    An attempt was made to use a date constant or system variable,
           such as USER, in a check constraint that was not completely
           specified in a CREATE TABLE or ALTER TABLE statement.  For
           example, a date was specified without the century.
*Action:   Completely specify the date constant or system variable.
           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
           which a bug permitted to be created before version 8.
share|improve this question

3 Answers

 
     
A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs, i.e.

CREATE OR REPLACE TRIGGER trg_check_dates
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  IF( :new.CloseDate <= SYSDATE )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
          'Invalid CloseDate: CloseDate must be greater than the current date - value = ' || 
          to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.CloseDate > add_months(SYSDATE,12) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
         'Invalid CloseDate: CloseDate must be within the next year - value = ' || 
         to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.StartDate <= add_months(:new.CloseDate,24) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
          'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' || 
          to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) ||
          ' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
END;

jueves, 3 de octubre de 2013

FECHA FORMATO DDSPTH

SQL> SELECT TO_CHAR(SYSDATE,'MONTH DDSPTH') from dual;
TO_CHAR(SYSDATE,'MONTHDDSPTH')
------------------------------------------------------------------------
OCTOBER   THIRD
SQL> SELECT TO_CHAR(SYSDATE,'YEAR MONTH DDSPTH') from dual;
TO_CHAR(SYSDATE,'YEARMONTHDDSPTH')
--------------------------------------------------------------------------------
TWENTY THIRTEEN OCTOBER   THIRD
SQL> SELECT TO_CHAR(SYSDATE,'YEAR MONTH DAY DDSPTH') from dual;
TO_CHAR(SYSDATE,'YEARMONTHDAYDDSPTH')
--------------------------------------------------------------------------------
TWENTY THIRTEEN OCTOBER   THURSDAY  THIRD

miércoles, 18 de septiembre de 2013

How to convert non-partitioned table to partition table using re-definition

SQL> set pagesize 200SQL> set long 999999
SQL> set linesize 150
SQL> select dbms_metadata.get_ddl('TABLE','OUT_CDR','CR_2') from dual;

DBMS_METADATA.GET_DDL('TABLE','OUT_CDR','CR_2')
--------------------------------------------------------------------------------

CREATE TABLE "CR_2"."OUT_CDR" ( "ID" NUMBER(32,0) NOT NULL ENABLE, "CDATE" DATE NOT NULL ENABLE,
"DDATE" DATE NOT NULL ENABLE,
"ACCTSESSIONID" VARCHAR2(100),
"CALLINGNO" VARCHAR2(100),
"CALLEDNO" VARCHAR2(100) NOT NULL ENABLE,
"AREACODE" VARCHAR2(100),
"PREFIX" VARCHAR2(100),
"SESSIONTIME" NUMBER(32,0),
"BILLABLETIME" NUMBER(32,0),
"RATE" NUMBER(32,4),
"CALL_COST" NUMBER(32,4),
"CURRENTBILL" NUMBER(32,4),
"DISCONNECTCAUSE" VARCHAR2(50),
"SOURCEIP" VARCHAR2(100),
"DESTIP" VARCHAR2(100),
"BILLABLE" NUMBER(32,0) NOT NULL ENABLE,
"LESS" NUMBER(32,0) NOT NULL ENABLE,
"ACCID" NUMBER(32,0),
"IN_DDATE" DATE,
"IN_PREFIX" VARCHAR2(100),
"IN_SESSIONTIME" NUMBER(32,0),
"IN_BILLABLETIME" NUMBER(32,0),
"IN_RATE" NUMBER(32,4),
"IN_CALL_COST" NUMBER(32,4),
"IN_MONEYLEFT" NUMBER(32,4),
"IN_DISCONNECTCAUSE" VARCHAR2(50),
"IN_BILLABLE" NUMBER(32,0),
"IN_LESS" NUMBER(32,0), "SWITCH_ID
" NUMBER(32,0) NOT NULL ENABLE,
"USER_ID" NUMBER(32,0) NOT NULL ENABLE,
"IN_USER_ID" NUMBER(32,0),
"PROCESSED" NUMBER(1,0),
CONSTRAINT "OUT_CDR_PK" PRIMARY KEY ("ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 168820736 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "CDR_INDX_SPC" ENABLE, CONSTRAINT "OUT_CDR_UQ" UNIQUE ("CDATE", "CALLEDNO", "USER_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 522190848 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "CDR_INDX_SPC" ENABLE, CONSTRAINT "OUT_CDR_UQ_2" UNIQUE ("DDATE", "CALLEDNO", "USER_ID")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSSTORAGE(INITIAL 521142272 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "CDR_INDX_SPC" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGSTORAGE(INITIAL 2013265920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE "OUT_CDR_NEW_SPC"


Step 02: Let's determine if the table OUT_CDR can be redefined online.
SQL> exec dbms_redefinition.can_redef_table('CR_2', 'OUT_CDR');
PL/SQL procedure successfully completed.

Step 03: Create a interim table which holds the same structure as the original table except constraints, indexes, triggers but add the partitioning attribute.I named the interim table as OUT_CDR_. Later we may drop it.

SQL> CREATE TABLE "CR_
2"."OUT_CDR_"2 ( "ID" NUMBER(32,0),
3 "CDATE" DATE ,
4 "DDATE" DATE ,
5 "ACCTSESSIONID" VARCHAR2(100),
6 "CALLINGNO" VARCHAR2(100),
7 "CALLEDNO" VARCHAR2(100) ,
8 "AREACODE" VARCHAR2(100),
9 "PREFIX" VARCHAR2(100),
10 "SESSIONTIME" NUMBER(32,0),
11 "BILLABLETIME" NUMBER(32,0),
12 "RATE" NUMBER(32,4),
13 "CALL_COST" NUMBER(32,4),
14 "CURRENTBILL" NUMBER(32,4),
15 "DISCONNECTCAUSE" VARCHAR2(50),
16 "SOURCEIP" VARCHAR2(100),
17 "DESTIP" VARCHAR2(100),
18 "BILLABLE" NUMBER(32,0) ,
19 "LESS" NUMBER(32,0) ,
20 "ACCID" NUMBER(32,0),
21 "IN_DDATE" DATE,
22 "IN_PREFIX" VARCHAR2(100),
23 "IN_SESSIONTIME" NUMBER(32,0),
24 "IN_BILLABLETIME" NUMBER(32,0),
25 "IN_RATE" NUMBER(32,4),
26 "IN_CALL_COST" NUMBER(32,4),
27 "IN_MONEYLEFT" NUMBER(32,4),
28 "IN_DISCONNECTCAUSE" VARCHAR2(50),
29 "IN_BILLABLE" NUMBER(32,0),
30 "IN_LESS" NUMBER(32,0),
31 "SWITCH_ID" NUMBER(32,0) ,
32 "USER_ID" NUMBER(32,0) ,
33 "IN_USER_ID" NUMBER(32,0),
34 "PROCESSED" NUMBER(1,0)
35 ) TABLESPACE "OUT_CDR_NEW_SPC"36 Partition by range(cdate)37 (38 partition P08152008 values less than (to_date('15-AUG-2008','DD-MON-YYYY')),39 partition P09012008 values less than (to_date('01-SEP-2008','DD-MON-YYYY')),40 partition P09152008 values less than (to_date('15-SEP-2008','DD-MON-YYYY')),41 partition P10012008 values less than (to_date('01-OCT-2008','DD-MON-YYYY')),42 partition P10152008 values less than (to_date('15-OCT-2008','DD-MON-YYYY')),43 partition P11012008 values less than (to_date('01-NOV-2008','DD-MON-YYYY')),44 partition P11152008 values less than (to_date('15-NOV-2008','DD-MON-YYYY')),45 partition P12012008 values less than (to_date('01-DEC-2008','DD-MON-YYYY')),46 partition P12152008 values less than (to_date('15-DEC-2008','DD-MON-YYYY')),47 partition P01012009 values less than (to_date('01-JAN-2009','DD-MON-YYYY')),48 partition P01152009 values less than (to_date('15-JAN-2009','DD-MON-YYYY')),49 partition P02012009 values less than (to_date('01-FEB-2009','DD-MON-YYYY')),50 partition PMAX values less than (maxvalue));
Table created.

Step 04: Initiates the redefinition process by calling dbms_redefinition.start_redef_table procedure.
SQL> exec dbms_redefinition.start_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');
PL/SQL procedure successfully completed.


Step 05: Copies the dependent objects of the original table onto the interim table. The COPY_TABLE_DEPENDENTS Procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. But this procedure does not clone the already registered dependent objects.In fact COPY_TABLE_DEPENDENTS Procedure is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table which in facr represents the post-redefinition table.

SQL> declare
2 error_count pls_integer := 0;
3 BEGIN
4 dbms_redefinition.copy_table_dependents('CR_2', 'OUT_CDR', 'OUT_CDR_',1, true, true, true, false,error_count);
5 dbms_output.put_line('errors := ' to_char(error_count));
6 END;
7 /PL/SQL procedure successfully completed.



Step 06: Completes the redefinition process by calling FINISH_REDEF_TABLE Procedure.
SQL> exec dbms_redefinition.finish_redef_table('CR_2', 'OUT_CDR', 'OUT_CDR_');
PL/SQL procedure successfully completed.


Step 07: Check the partitioning validation by,
SQL> Select partition_name, high_value from user_tab_partitions where table_name='OUT_CDR';PARTITION_NAME HIGH_VALUE------------------------------ ---------------------------------------------------------------------------------------------------P01012009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP01152009 TO_DATE(' 2009-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP02012009 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP08152008 TO_DATE(' 2008-08-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP09012008 TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP09152008 TO_DATE(' 2008-09-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP10012008 TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP10152008 TO_DATE(' 2008-10-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP11012008 TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP11152008 TO_DATE(' 2008-11-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP12012008 TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAP12152008 TO_DATE(' 2008-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAPMAX MAXVALUE13 rows selected.Check index status by,SQL> select index_name , status from user_indexes where table_name='OUT_CDR';INDEX_NAME STATUS------------------------------ --------OUT_CDR_PK VALIDOUT_CDR_UQ VALIDOUT_CDR_UQ_2 VALIDStep 08: Drop the interim table OUT_CDR_.SQL> DROP TABLE OUT_CDR_;Table dropped.

jueves, 22 de agosto de 2013

EXAMPLE BACKUP AS COPY



[oracle@localhost labs2]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Aug 22 17:37:16 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ACME (DBID=2003395651)
RMAN> backup incremental level 1 for recover of copy with tag 'backup_incr' database;
Starting backup at 2013-08-22:17:38:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/acme/system01.dbf
output file name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_system_91f4phck_.dbf tag=BACKUP_INCR RECID=2 STAMP=824146789
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/acme/sysaux01.dbf
output file name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_sysaux_91f4svo0_.dbf tag=BACKUP_INCR RECID=3 STAMP=824146910
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/acme/example01.dbf
output file name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf tag=BACKUP_INCR RECID=4 STAMP=824146928
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/acme/undotbs01.dbf
output file name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_undotbs1_91f4y845_.dbf tag=BACKUP_INCR RECID=5 STAMP=824146942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/acme/users01.dbf
output file name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_users_91f4yjjo_.dbf tag=BACKUP_INCR RECID=6 STAMP=824146945
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2013-08-22:17:42:25
Starting Control File and SPFILE Autobackup at 2013-08-22:17:42:26
piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2013_08_22/o1_mf_s_824146946_91f4yny0_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-08-22:17:42:33
RMAN> list copy of tablespace example;
List of Datafile Copies
=======================
Key     File S Completion Time     Ckp SCN    Ckp Time          
------- ---- - ------------------- ---------- -------------------
4       5    A 2013-08-22:17:42:08 2084754    2013-08-22:17:42:00
        Name: /u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf
        Tag: BACKUP_INCR

RMAN> backup incremental level 1 for recover of copy with tag 'backup_incr' database;
Starting backup at 2013-08-22:18:04:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/acme/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/acme/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/acme/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/acme/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-08-22:18:04:23
channel ORA_DISK_1: finished piece 1 at 2013-08-22:18:05:48
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2013_08_22/o1_mf_nnnd1_BACKUP_INCR_91f67v6c_.bkp tag=BACKUP_INCR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2013-08-22:18:05:48
Starting Control File and SPFILE Autobackup at 2013-08-22:18:05:48
piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2013_08_22/o1_mf_s_824148348_91f6bfxw_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-08-22:18:05:51
RMAN> list copy of tablespace example;
List of Datafile Copies
=======================
Key     File S Completion Time     Ckp SCN    Ckp Time          
------- ---- - ------------------- ---------- -------------------
4       5    A 2013-08-22:17:42:08 2084754    2013-08-22:17:42:00
        Name: /u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf
        Tag: BACKUP_INCR

RMAN> backup incremental level 1 for recover of copy with tag 'backup_incr' database;
Starting backup at 2013-08-22:18:19:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/acme/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/acme/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/acme/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/acme/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2013-08-22:18:19:55
channel ORA_DISK_1: finished piece 1 at 2013-08-22:18:21:00
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2013_08_22/o1_mf_nnnd1_BACKUP_INCR_91f74vxw_.bkp tag=BACKUP_INCR comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 2013-08-22:18:21:01
Starting Control File and SPFILE Autobackup at 2013-08-22:18:21:01
piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2013_08_22/o1_mf_s_824149261_91f76z85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-08-22:18:21:04
RMAN> list copy of tablespace example;
List of Datafile Copies
=======================
Key     File S Completion Time     Ckp SCN    Ckp Time          
------- ---- - ------------------- ---------- -------------------
4       5    A 2013-08-22:17:42:08 2084754    2013-08-22:17:42:00
        Name: /u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf
        Tag: BACKUP_INCR

RMAN> recover copy of tablespace example with tag 'backup_incr';        
Starting recover at 2013-08-22:18:31:13
using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=43 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: WARNING: Oracle Test Disk API
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00005 name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2013_08_22/o1_mf_nnnd1_BACKUP_INCR_91f67v6c_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2013_08_22/o1_mf_nnnd1_BACKUP_INCR_91f67v6c_.bkp tag=BACKUP_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00005 name=/u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2013_08_22/o1_mf_nnnd1_BACKUP_INCR_91f74vxw_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2013_08_22/o1_mf_nnnd1_BACKUP_INCR_91f74vxw_.bkp tag=BACKUP_INCR
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 2013-08-22:18:31:19
Starting Control File and SPFILE Autobackup at 2013-08-22:18:31:19
piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2013_08_22/o1_mf_s_824149879_91f7t7yj_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2013-08-22:18:31:22
RMAN> list copy of tablespace example;
List of Datafile Copies
=======================
Key     File S Completion Time     Ckp SCN    Ckp Time          
------- ---- - ------------------- ---------- -------------------
8       5    A 2013-08-22:18:31:18 2087900    2013-08-22:18:19:55
        Name: /u01/app/oracle/flash_recovery_area/ACME/datafile/o1_mf_example_91f4xrrq_.dbf
        Tag: BACKUP_INCR

RMAN>