Archivos mensuales: febrero 2013

Convertir campo tipo LONG a VARCHAR2 para compatibilizar con Oracle 7

 

 

 

Os pongo en contexto …

Resulta que necesito saber que vistas de un usuario determinado están usando un DBLINK hacia otra Base de datos y el DBLINK está almacenado en una Base de datos Oracle 7.

Para saber las vistas necesito consultar la vista all_views cuyo campo text me indica el nombre y contenido del DBLINK. Por tanto, necesito realizar una consulta de esa vista y campo y buscar el texto “@nombre_DBLINK”.

¿ Que sucede ?

Basicamente que el campo text de la vista all_views es de tipo LONG y por tanto, el realizar una búsqueda de una cadena determinada dentro de ese campo, usando por ejemplo “like” no funciona, nos dará el error “ORA-00932: tipos de dato inconsistentes. Pues para que funcionase debería ser de tipo CHAR o VARCHAR2.

Ejemplo:

select * from all_views
where text like ‘%@NOMBRE_DBLINK%’;
ORA-00932: tipos de dato inconsistentes

La solución pasa por convertir el campo text de tipo LONG a VARCHAR2 para poder consultar usando like (patrón de búsqueda).

Para este proceso, crearé una tabla llamada tabla_vistas con los campos que necesito consultar y con un campo tipo VARCHAR2 que será el que usaré para almacenar la información del campo text de la vista all_views y que es de tipo LONG.

CREATE TABLE tabla_vistas (
    propietario VARCHAR2(30),
    nombre_vista VARCHAR2(30),
    texto VARCHAR2(2000) -- En este campo almacenaré la información del campo text de all_views
    );

Una vez creada la tabla, cuyos tipos de datos en los campos coinciden con los que necesito de all_views excepto el campo “texto” que contendrá los datos sacados del campo “text” de tipo LONG, crearé un cursor de tipo explícito (que son los que usamos cuando una consulta devuelve más de una fila) que recorra la vista all_views y extraiga la información fila a fila y a su vez almacene cada fila en la nueva tabla creada, pero convirtiendo el campo text de all_views de LONG a VARCHAR2 para posteriormente, yo poder realizar una consulta sobre la tabla resultado usando la opción “like” para buscar una cadena determinada.

Esto se hace de la siguiente manera:

set serveroutput on size 320000
DECLARE
          CURSOR c1 IS
          SELECT owner,view_name, text FROM all_views;
          c_owner VARCHAR2(30);
          c_view_name VARCHAR2(30);
          c_text LONG;
          cadena2000 varchar2(2000);
    BEGIN
       OPEN c1;
      LOOP
          FETCH c1 INTO c_owner,c_view_name,c_text;
          cadena2000 := substr(c_text,1,2000);
          INSERT INTO tabla_vistas (propietario,nombre_vista,texto) VALUES (c_owner,c_view_name,cadena2000);
          EXIT WHEN c1%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE('No se han encontrado datos.');
       ENDLOOP;
       CLOSE c1;
    END;

Ahora ya podemos realizar la consulta sobre la tabla “tabla_vistas” que contendrá todas las filas de all_views con los campos que necesitábamos y con el campo “text” de tipo LONG convertido a tipo VARCHAR2 en el campo “texto” del ejemplo.

select * from tabla_vistas

where texto like '%@NOMBRE_DBLINK%'; -- Texto a buscar en la tabla resultados

Deshabilitar acceso a SQLPLUS usando “/as sysdba”

En la entrada de Blog anterior, expliqué como poder acceder como SYSDBA a sqlplus sin tener el password. Evidentemente, esto es un problema grave de seguridad, ya que si cualquiera se hace con el password del usuario oracle y/o cualquier usuario que pertenezca al grupo “dba” tendrá acceso como SYSDBA si hace lo explicado en la entrada de blog anterior. Esto es así porque en el fichero $ORACLE_HOME/rdbms/lib/config.* (.c o .s) que pertenece al grupo dba se dan privilegios implícitos para usar “/as sysdba” sin pedir contraseña a cualquier usuario de sistema operatativo que pertenezca a este grupo.

Pues la solución, es tan sencilla como sacar del grupo “dba” a todos aquellos usuarios de S.O y dejar el grupo vacio. De esta manera, al entrar a sqlplus, se nos solicitará el password.

Otra opción consiste en:

Editar el archivo “$ORACLE_HOME/rdbms/lib/config.c” y hacer referencia a un falso  grupo vacío. Posteriormente, “volver a vincular todos”, para volver a conectar todos los componentes de software de Oracle con el nuevo “grupo vacío” Oracle DBA.

Acceder como SYSDBA a SQLPLUS sin conocer el password

De entrada esta “pretensión” de querer entrar a sqlplus sin conocer el password del usuario “SYS” puede ser una pretensión un tanto ambiciosa, pero que como podréis comprobar es más necesario de lo habitual y no es tan difícil.

En ocasiones, si eres un DBA de esos a los que tu empresa te manda cada dos por tres a clientes diferentes donde han pasado diferentes DBA´s y te encuentras que cuando llegas al cliente, ni el mismo cliente conoce los passwords ni de SYS ni de SYSTEM. Pero almenos se tiene acceso al servidor con cuentas de usuario de sistema operativo, si es la de oracle, ya tenemos mucho ganado, sino con root nos bastaría, o incluso, simplemente con poder acceder a una cuenta de usuario que nos permita crear usuarios y asignarle el grupo que deseamos, ya tendríamos solucionado el problema de no conocer el password de SYS o SYSTEM.

Os pongo en situación:

No conocemos el password de SYS ni de SYSTEM. Pero tenemos que realizar tareas de administración.

Nos tenemos que plantear lo siguiente…

- ¿ Tenemos acceso al servidor con un usuario de sistema operativo ? ¿ cúal ?
- ¿ Tenemos acceso al servidor con el usuario de sistema operativo oracle ?
- ¿ Tenemos acceso al servidor con  el usuario root ?

Pues si tenemos acceso con “root” al sistema operativo es fácil. Si hacemos el “su” al usuario oracle ya podremos acceder al servidor de base de datos de dos maneras diferentes con perfil de DBA.

# su – oracle
$ whoami
oracle

– Ahora entramos a sqlplus: forma 1
$ sqlplus “/as sysdba”
$ show user
SQL> show user
USER is “SYS”
SQL>

– O  entramos a sqlplus: forma 2
$ sqlplus /nolog
$ show user
SQL> show user
USER is “”
SQL> connect /as sysdba
SQL> show user
USER is “SYS”

Si tenemos acceso con el usario “oracle”, pues simplemente ejecutar cualquiera de las formas anteriores del ejemplo.

Y sino tenemos acceso con “root” pero tenemos acceso con un usuario que tenga permisos para crear usuarios y cambiar el grupo. Lo que deberíamos hacer es crearnos un usuario con cualquier nombre y asignarlo al grupo “dba”, configuraríamos el .profile (.bash_profile) del usuario el entorno Oracle (Path, ORACLE_HOME, ORACLE_SID, etc…) y entraríamos posteriormente con ese usuario al S.O y luego a sqlplus como lo hemos hecho en cualquiera de los dos ejemplos anteriores.

Para hacer lo comentado anteriormente, primero crearemos el usuario:

# useradd usuario_dba -m -g dba
# passwd usuario_dba
Introduciremos el password.

Ahora configuraremos el entorno Oracle para este usuario. Para ello es necesario conocer algunas de las variables de entorno imprescindibles que tendremos en un “.profile” del usuario “oracle”. Este profile nos sirve de ejemplo y lo tendremos que adaptar al entorno que queremos administrar, es decir, cambiar el ORACLE_HOME y el ORACLE_SID  y adecuarlo al entorno que vamos a administrar.

Ejemplo de .bash_profile en Linux RedHat válido para el entorno Oracle:

#vi  .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

ORACLE_HOME=/u/oracle/11.2.0/ORCL
export ORACLE_HOME

PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
export PATH

ORACLE_SID=ORCL
export ORACLE_SID

LD_LIBRARY_PATH=/usr/lib64:$ORACLE_HOME/lib
export LD_LIBRARY_PATH

ORACLE_UNQNAME=ORCL
export ORACLE_UNQNAME

Las líneas anteriores adaptadas al servidor en concreto que queramos administrar serán las que deberemos añadir en nuestro “profile” del usuario que hayamos creado.

Una vez hecho esto, ya podemos salir y volver a entrar con el usuario para cargar el profile y ejecutar sqlplus como en los primeros ejemplos. Finalmente, comentar que si quisíeramos cambiar una vez conectados a sqlplus, por ejemplo, el password del usuario SYSTEM, lo podríamos hacer sin ningún problema ejecutando:

SQL> alter user SYSTEM identified by “<nuevo password>”;

¿ No es tan complicado verdad ?

ORA-01031: privilegios insuficientes … al hacer un GRANT

Al hacer un GRANT conectado tanto como el usuario SYSTEM y/o SYS en una versión Oracle 7.3.4 para dar permisos de un package de un usuario a otro me aparece el error Oracle:

ORA-01031: privilegios insuficientes

La solución más rápida a este error es conectarse como el usuario propietario del Package (u del objeto que nos dé problemas) y ejecutar el comando GRANT.

Ej.

$ sqlplus system/password_system
SQL> connect usuario/password_usuario
SQL> show user
el usuario es “usuario”
SQL>  GRANT DELETE, INSERT, SELECT, UPDATE ON OPS$USUARIO.PACKAGE_EJEMPLO TO OPS$USUARIO_DESTINO;

Pero en mi caso, no tenía el password del usuario en concreto y tampoco me dejaban cambiarlo por si afectaba a alguna aplicación. ¿ Que se puede hacer en este caso ?

Como sí que tenía el password de root a nivel de sistema operativo (Unix en este caso) hice lo siguiente:

# su – usuario
$ whoami
usuario
$ sqlplus /

Ahora ya estaba conectado como el usuario “usuario” y ya podía ejecutar el comando GRANT.

SQL> show user
el usuario es “usuario”
SQL>  GRANT DELETE, INSERT, SELECT, UPDATE ON OPS$USUARIO.PACKAGE_EJEMPLO TO OPS$USUARIO_DESTINO;

Fácil  ¿verdad?

Migrar usuario de versión 9i a 11GR2 con herramienta exp/imp

En la siguiente entrada de blog voy a mostrar un ejemplo de cómo exportar un esquema completo de un usuario Oracle de una base de datos en versión 9i hacia una versión 11GR2.

En muchas ocasiones, los DBA´s nos encontramos que tenemos que migrar Bases de datos a las últimas versiones y este proceso de migración se debe hacer de la manera más segura posible.  Pues una de estas maneras, desde mi punto de vista, es migrar la Base de datos esquema a esquema. La idea es la siguiente ….

Si debo migrar de una versión 9i a una 11GR2 y no quiero ir haciendo actualizaciones de versiones de Oracle. Una solución efectiva y rápida puede ser hacer lo siguiente:

- Instalar la nueva versión 11GR2 y crear la BD durante la creación con el mismo “perfil” que la Base de datos de versión anterior (en este caso la 9i), teniendo en cuenta cosas cómo por ejemplo poner el mismo CHARACTER SET, etc….

- Lo siguiente sería, exportar usando las herramientas de la versión 9i todos los esquemas, pero haciéndolo uno a uno. 

- Finalmente importar, usando el export de la nueva versión a la nueva Base de datos.

Seguidamente explico cómo hacer este export/import y explico que errores son los que comúnmente nos podemos encontrar durante este proceso.

Lo primero que tenemos que plantearnos es cómo exportar los esquemas que se encuentran en la versión vieja, en este ejemplo la 9i.

Se puede hacer de dos maneras. O bien ejecutando la herramienta export (exp) desde el mismo servidor donde se encuentre la base de datos, o bien, usando un cliente Oracle instalado en nuestro PC,  por ejemplo, y realizar el export.

Tanto en un caso como en el otro, nos tenemos que asegurar de que tendremos espacio de disco donde ubicar el fichero binario (con extensión .dmp) que genera la herramienta export y luego tendremos que copiarlo al servidor destino (en este caso la 11GR2) para posteriormente realizar el import.

 Si tenemos acceso al servidor donde están los esquemas de la 9i, lo mejor es hacer el export de esta manera. Ya que probablemente será más rápido y además, nos aseguraremos de que la herramienta export que utilizamos corresponde con la versión Oracle que hay instalada. Si por el contrario, realizamos el export desde un cliente Oracle instalado en nuestro PC, nos tenemos que asegurar que la versión que utilizamos para realizar el export, es cómo mínimo igual o inferior a la versión de la Base de datos origen. Porque sino es así obtendremos el siguiente mensaje de error al ejecutar la herramienta export:

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component ‘SET_NO_OUTLINES’ must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

Solución: Ejecutar el export desde el servidor o instalarse un cliente de la misma versión o inferior. Nunca superior.

Ejemplo de export de esquema que usaríamos para realizar el export desde nuestro PC Windows con cliente 9i instalado igual o inferior al instalado en el servidor “origen”:

exp system/password@SID9i owner=USUARIO file=esquema_USUARIO.dmp log=exp_USUARIO.log

Esta línea la podemos guardar en un fichero con extensión .bat y ejecutarlo desde la línea de comandos de Windows.

La ejecución de este export generará un fichero binario llamado “esquema_USUARIO.dmp” que será el que posteriormente copiaremos al servidor destino y usaremos para realizar el import a la nueva base de datos.

 Lo siguiente que se debería hacer es copiar el fichero generado con el export en el servidor destino donde tenemos instalada la versión 11GR2. Una vez copiado, en el servidor destino, entraremos a sqlplus y crearemos el usuario con el mismo nombre que tiene en el servidor origen y le asignaremos el tablespace correspondiente. Si el tablespace no existiese, que sería lo normal, lo deberíamos crear antes de crear el usuario y antes de lanzar el import.

Podemos basarnos en la versión 9i para sacar la información del tablespace e incluso del usuario. Si estamos acostumbrados a trabajar con herramientas como TOAD, se puede incluso sacar el código de creación del usuario y lo podemos usar para la creación en el nuevo entorno.

En resumen:

Tenemos el export (fichero .dmp) ya copiado en el servidor destino. Hemos creado el tablespace del usuario y hemos creado el usuario asignándole ese tablespace. Pues ahora, procederemos a hacer el import del usuario. Sino hubiésesmos creado el tablespace y el usuario, el import nos daría un error. Las nueva herramientas de export/import Datapump en el proceso de importación ya nos crea el usuario y su tablespace correspondiente. Pero en este caso como es una versión 9i, nos vemos obligados a usar los antiguos exp/imp que no lo hacen.

imp system/password@SID11G fromuser=USUARIO touser=USUARIO file=esquema_USUARIO.dmp log=imp_USUARIO.log

Ahora podríamos asegurarnos de que nos conectamos correctamente con el usuario y que tenemos todo el esquema creado y los permisos asignados correctamente. Si no tenemos todos los permisos, podemos usar el usuario de la base de datos origen para sacarlos, ya sea usando una herramienta como TOAD o bien podemos ejecutar las siguientes instrucciones que nos generarán los “grants” necesarios para otorgar los permisos.

accept newuser prompt ‘Entra el nombre de usuario para generar el scripts de los roles que tiene:’

select ‘grant ‘ ||granted_role || ‘ to &newuser’ ||
       decode(admin_option, ‘NO’, ‘;’, ‘YES’, ‘ with admin option;’) “ROLE”
from   dba_role_privs
where  grantee = ‘&newuser’
/

select ‘grant ‘ || privilege || ‘ to &newuser’ ||
       decode(admin_option, ‘NO’, ‘;’, ‘YES’, ‘ with admin option;’) “PRIV”
from   dba_sys_privs
where  grantee = ‘&newuser’
/