PRACTICAS TEMA 1. - Cursos Administración Oracle …cursos.atica.um.es/DBA9i1/pdf/practicas/CursoDBA9i1_practicas_2p.pdf · Asignar la variable NLS_LANG para nuestro país y comprobar

Administración Básica de Oracle9i – Prácticas resueltas

Índice de contenidoARQUITECTURA DE LA BASE DE DATOS...................................................................................2

ARRANQUE Y PARADA DE LA BASE DE DATOS......................................................................17

FICHERO DE CONTROL.................................................................................................................31

FICHEROS REDO LOG....................................................................................................................35

TABLESPACES.................................................................................................................................42

ESPACIO DE “UNDO”......................................................................................................................51

USUARIOS, PERFILES, PRIVILEGIOS Y ROLES........................................................................85

TRABAJOS (JOBS)..........................................................................................................................118

AUDITORÍA....................................................................................................................................132

COPIAS DE SEGURIDAD..............................................................................................................153

© Juan Luis Serradilla Amarilla y Francisco Fernández Martínez 1


PRACTICAS TEMA 1.

ARQUITECTURA DE LA BASE DE DATOS.

1.1. Comprobar las variables de entorno necesarias para conectarnos a la BD.

1.2. Identificar los procesos que componen instancia.

1.3. Ver el tamaño de la SGA de la BD.

1.4. Comprobar valores de parámetros del init relacionados con el tamaño de la SGA.

1.5. Comprobar ficheros que componen la BD y ubicarlos en la estructura OFA.

1.6. Identificar la estructura lógica de la BD: tablespaces, segmentos, extensiones.

1.7. Consultar información sobre la base de datos (v$database) y la instancia (v$instance).

1.8. Localizar el proceso “servidor” asociado a mi sesión (v$process y v$session). ¿Es un servidor dedicado o compartido?

1.9. ¿Cuanto ocupa la Dictionary cache y la Library cache en tu BD? (v$sgastat)

1.10. Ver la actividad de la Library Cache (v$librarycache).

1.11. Ver las sentencias SQL que guarda la Shared-Pool (v$sqlarea).

1.12. Crear un fichero de autenticación y activar su uso.

1.13. ¿Qué ocurre si “quitamos” la variable LD_LIBRARY_PATH?

1.14. Asignar la variable NLS_LANG para nuestro país y comprobar el cambio en las respuestas de Oracle desde sqlplus.

1.15. Subir el tamaño de la shared-pool de 8 a 12M y comprobar cómo aumenta el espacio libre.

1.16. Comprobar el funcionamiento de la caché de redolog, como protectora del contenido de la caché de datos.

1.17. Comprobar el funcionamiento de la caché de datos, en lo que se refiere a la mejora del rendimiento cuando se repite una consulta.

Recursos.- Máquina: cursos.atica.um.es- Usuario unix: curso51 a curso70- Conexión máquina unix: ssh con Secure Shell



- Bases de datos: CURSO51 a CURSO70- Conexión a bd: sqlplus desde sesión unix, usuario “/ as sysdba”

En primer lugar, si ya no lo está, hay que arrancar la base de datos. En el ejemplo vemos el arranque de la base de datos CURSOxy, desde el usuario curso01 de la máquina mncs:

/home/DBA9i1/curso01 (CURSOxy)> sqlplusSQL*Plus: Release 9.2.0.1.0 - Production on Wed Oct 20 12:39:05 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Enter user-name: / as sysdbaConnected to an idle instance.

SQL> startupORACLE instance started.Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.Database opened.

1.1. Comprobar la asignación de variables de entorno necesarias para conectarnos a la BD.

Se trata de las variables $ORACLE_HOME, $ORACLE_SID, $LD_LIBRARY_PATH y $PATH.Nota: si no asignamos LD_LIBRARY_PATH en Linux, no podremos ejecutar sqlplus, obteniendo un error similar a:sqlplus: error while loading shared libraries: libclntsh.so: cannot open sharedobject file: No such file or directory

Solución:

/home/DBA9i1/curso01 (CURSOxy)> echo $ORACLE_HOME/u01/app/oracle/product/9.2.0.1.0/home/DBA9i1/curso01 (CURSOxy)> echo $ORACLE_SIDCURSO02/home/DBA9i1/curso01 (CURSOxy)> echo $PATH/u01/app/oracle/product/9.2.0.1.0/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:/home/DBA9i1/curso02/bin/home/DBA9i1/curso01 (CURSOxy)> echo $LD_LIBRARY_PATH/u01/app/oracle/product/9.2.0.1.0/lib

1.2. Identificar los procesos que componen instancia.

Los podemos identificar desde el S.O., por ejemplo, en Unix con el comando "ps -ef" que permite ver todos los procesos en ejecución en el sistema, filtrando por el nombre de la instancia de BD:

ps -ef|grep curso01



También lo podemos hacer consultando la vista dinámica V$PROCESS (o directamente en V$BGPROCESS), en la BD en cuestión:

select * from v$process where background is not null;

o bien

select * from v$process a, v$bgprocess b where a.ADDR=b.PADDR;

Solución:

/home/DBA9i1/curso01 (CURSOxy)> ps -ef|grep CURSOxyoracle 30205 1 0 09:32 ? 00:00:00 ora_pmon_CURSOxyoracle 30207 1 0 09:32 ? 00:00:00 ora_dbw0_CURSOxyoracle 30209 1 0 09:32 ? 00:00:00 ora_lgwr_CURSOxyoracle 30211 1 0 09:32 ? 00:00:00 ora_ckpt_CURSOxyoracle 30213 1 0 09:32 ? 00:00:00 ora_smon_CURSOxyoracle 30215 1 0 09:32 ? 00:00:00 ora_reco_CURSOxycurso01 30528 30492 0 12:58 pts/11 00:00:00 grep CURSOxy

SQL> select username,program from v$process where background is not null;USERNAME PROGRAM--------------- ------------------------------------------------curso01 [email protected] (PMON)curso01 [email protected] (DBW0)curso01 [email protected] (LGWR)curso01 [email protected] (CKPT)curso01 [email protected] (SMON)curso01 [email protected] (RECO)6 rows selected.

SQL> select name,description from v$process a, v$bgprocess b where a.ADDR=b.PADDR;

NAME DESCRIPTION----- ----------------------------------------------------------------PMON process cleanupDBW0 db writer process 0LGWR Redo etc.CKPT checkpointSMON System Monitor ProcessRECO distributed recovery6 rows selected.

1.3. Ver el tamaño de la SGA de la BD.

Hay dos vistas dinámicas de la BD que nos dan información sobre el tamaño y la estructura de la SGA: V$SGA y V$SGASTAT.

select * from v$sga;select * from v$sgastat;



Solución:

SQL> select * from v$sga;NAME VALUE-------------------- ----------Fixed Size 450560Variable Size 29360128Database Buffers 4194304Redo Buffers 143360

SQL> select * from v$sgastat;POOL NAME BYTES----------- -------------------------- ---------- fixed_sga 450560 buffer_cache 4194304 log_buffer 133120shared pool krvxrr 126528shared pool KGK heap 1836shared pool KQR M PO 214032shared pool KQR S PO 10000shared pool KQR S SO 2304shared pool sql area 2343928shared pool 1M buffer 2098176shared pool KGLS heap 287508shared pool kglsim sga 134144shared pool parameters 1680shared pool free memory 5534064shared pool PL/SQL DIANA 406632shared pool PL/SQL MPCODE 43652shared pool library cache 1834420shared pool miscellaneous 1428188shared pool PLS non-lib hp 1808shared pool UNDO STAT INFO 52416shared pool joxs heap init 4220shared pool kgl simulator 563372shared pool sim memory hea 71480shared pool trigger inform 64shared pool Checkpoint queue 113344shared pool dictionary cache 1610880shared pool SYSTEM PARAMETERS 96952shared pool sim trace entries 98304shared pool trace events array 72000shared pool ksm_file2sga region 148652shared pool network connections 48600shared pool KSXR receive buffers 1032500shared pool character set object 300844shared pool FileIdentificatonBlock 127884shared pool KSXR large reply queue 166024shared pool message pool freequeue 833032shared pool KSXR pending reply queue 90292shared pool KSXR pending messages que 840636shared pool event statistics per sess 162624shared pool fixed allocation callback 180shared pool latch nowait fails or sle 6832041 rows selected.

SQL> select * from v$sgastat where name in ('library cache','dictionary cache','sql area',

'buffer_cache','log_buffer');POOL NAME BYTES



----------- -------------------------- ---------- buffer_cache 4194304 log_buffer 133120shared pool sql area 2343928shared pool library cache 1834420shared pool dictionary cache 1610880

1.4. Comprobar valores de parámetros del init relacionados con el tamaño de la SGA.

Los parámetros de inicialización más importantes que afectan al tamaño de la SGA son: shared_pool_size, db_cache_size, db_block_size, log_buffer, large_pool_size y java_pool_size.

Solución:

SQL> show parameter shared_pool_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------shared_pool_size big integer 8388608

SQL> show parameter db_cache_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_cache_size big integer 4194304

SQL> show parameter db_block_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_size integer 2048

SQL> show parameter log_bufferNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_buffer integer 32768

SQL> show parameter large_pool_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------large_pool_size big integer 0

SQL> show parameter java_pool_sizeNAME TYPE VALUE------------------------------------ ----------- ------------------------------java_pool_size big integer 0

1.5. Comprobar ficheros que componen la BD y ubicarlos en la estructura OFA.

Los ficheros que componen la BD los podemos consultar desde el propio S.O. o desde la BD. Desde el S.O., y teniendo en cuenta la estructura OFA los encontraremos en /u0?/oradata/CURSOxy, dado que nuestros puntos de montaje son /u01, /u02, /u03 y /u04:

ls -l /u0?/oradata/CURSOxy

Podemos localizar cada tipo de fichero (datafile, control y redolog) por la extensión; por ejemplo: dbf, ctl y log.



¡¡¡ Ojo !!!, ésto no es más que una convención, las extensiones pueden ser otras, o incluso no existir.

Otra opción es acceder a la información de los ficheros de control desde la propia BD, consultando las vistas dinámicas V$DATAFILE, V$TEMPFILE, V$CONTROLFILE y V$LOGFILE:

select * from v$datafile;select * from v$tempfile;select * from v$logfile;select * from v$controlfile;

Solución:

/home/DBA9i1/curso01 (CURSOxy)> ls -l /u0?/oradata/$ORACLE_SID/u02/oradata/CURSOxy:total 279012-rw-rw---- 1 oracle dba 4384768 oct 20 13:28 ora_control1-rw-rw---- 1 oracle dba 272631808 oct 20 13:04 system01.dbf-rw-rw---- 1 oracle dba 4196352 oct 20 09:28 tools01.dbf-rw-rw---- 1 oracle dba 4196352 oct 20 09:28 users01.dbf/u03/oradata/CURSOxy:total 45376-rw-rw---- 1 oracle dba 4384768 oct 20 13:28 ora_control2-rw-rw---- 1 oracle dba 4196352 oct 20 13:26 rbs01.dbf-rw-rw---- 1 oracle dba 38799360 oct 20 08:36 temp01.dbf/u04/oradata/CURSOxy:total 1524-rw-rw---- 1 oracle dba 512512 oct 20 13:26 redo01.log-rw-rw---- 1 oracle dba 512512 oct 20 09:28 redo02.log-rw-rw---- 1 oracle dba 512512 oct 20 09:28 redo03.log

SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/home/u02/oradata/CURSOxy/system01.dbf/home/u02/oradata/CURSOxy/tools01.dbf/home/u03/oradata/CURSOxy/rbs01.dbf/home/u02/oradata/CURSOxy/users01.dbf

SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------/home/u03/oradata/CURSOxy/temp01.dbf

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/home/u04/oradata/CURSOxy/redo01.log/home/u04/oradata/CURSOxy/redo02.log/home/u04/oradata/CURSOxy/redo03.log

SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/home/u02/oradata/CURSOxy/ora_control1/home/u03/oradata/CURSOxy/ora_control2



1.6. Identificar la estructura lógica de la BD: tablespaces, segmentos, extensiones.

En el DD de la BD tenemos vistas que nos permiten comprobar la estructura lógica de la BD: DBA_TABLESPACES, DBA_DATA_FILES, DBA_SEGMENTS y DBA_EXTENTS.

select * from dba_tablespaces;select * from dba_datafiles;select * from dba_tempfiles;select * from dba_segments;select * from dba_extents;

Solución:

SQL> select tablespace_name from dba_tablespaces order by tablespace_name;

TABLESPACE_NAME------------------------------SYSTEMTEMPTOOLSUNDO_RBS1USERS

SQL> select tablespace_name,file_name from dba_data_files order by tablespace_name, file_name;

TABLESPACE_NAME------------------------------FILE_NAME--------------------------------------------------------------------------------SYSTEM/home/u02/oradata/CURSOxy/system01.dbfTOOLS/home/u02/oradata/CURSOxy/tools01.dbfUNDO_RBS1/home/u03/oradata/CURSOxy/rbs01.dbfUSERS/home/u02/oradata/CURSOxy/users01.dbf

SQL> select tablespace_name,file_name from dba_temp_files order by tablespace_name, file_name;TABLESPACE_NAME------------------------------FILE_NAME--------------------------------------------------------------------------------TEMP/home/u03/oradata/CURSOxy/temp01.dbf

SQL> select tablespace_name,segment_type,count(*) segmentosfrom dba_segmentsgroup by tablespace_name,segment_type;

TABLESPACE_NAME SEGMENT_TYPE SEGMENTOS------------------------------ ------------------ ----------USERS TABLE 1SYSTEM CACHE 1



SYSTEM INDEX 442SYSTEM TABLE 369SYSTEM CLUSTER 10SYSTEM LOBINDEX 49SYSTEM ROLLBACK 1SYSTEM LOBSEGMENT 49SYSTEM INDEX PARTITION 24SYSTEM TABLE PARTITION 27UNDO_RBS1 TYPE2 UNDO 511 rows selected.

SQL> select tablespace_name,segment_type,count(*)from dba_extentsgroup by tablespace_name,segment_type;

TABLESPACE_NAME SEGMENT_TYPE EXTENSIONES------------------------------ ------------------ -----------USERS TABLE 1SYSTEM CACHE 1SYSTEM INDEX 605SYSTEM TABLE 750SYSTEM CLUSTER 51SYSTEM LOBINDEX 49SYSTEM ROLLBACK 27SYSTEM LOBSEGMENT 60SYSTEM INDEX PARTITION 24SYSTEM TABLE PARTITION 27UNDO_RBS1 TYPE2 UNDO 1011 rows selected.

1.7. Consultar información sobre la base de datos (v$database) y la instancia (v$instance).

Podemos obtener información de la base de datos y de la instancia, de las vistas V$DATABASE y V$INSTANCE, respectivamente.

Solución:

SQL> select name,created,log_mode,checkpoint_change#,open_modefrom v$database;NAME CREATED LOG_MODE CHECKPOINT_CHANGE# OPEN_MODE--------- --------- ------------ ------------------ ----------CURSOxy 13-AUG-04 NOARCHIVELOG 753958 READ WRITE

SQL> select instance_name,host_name,version,startup_time,status,archiver,logins,database_status from v$instance;INSTANCE_NAME----------------HOST_NAME----------------------------------------------------------------VERSION STARTUP_T STATUS ARCHIVE LOGINS DATABASE_STATUS----------------- --------- ------------ ------- ---------- -----------------CURSOxycursos.atica.um.es9.2.0.1.0 20-OCT-04 OPEN STOPPED ALLOWED ACTIVE



1.8. Localizar el proceso “servidor” asociado a mi sesión (v$process y v$session). ¿Es un servidor dedicado o compartido?

Toda sesión tiene dos vertientes: cliente y servidor. En el cliente tenemos el proceso de usuario que inicia la sesión y en el servidor de base de datos tendremos el proceso que sirve las peticiones de dicha sesión; que puede ser un servidor dedicado o compartido. En las vistas V$SESSION y V$PROCESS tenemos toda la información relativa a sesiones y procesos, respectivamente.

Solución:

SQL> show userUSER is "SYSTEM"SQL> select a.SERVER, a.username dbuser,a.OSUSER, a.PROCESS user_process,

a.machine, a.terminal, a.program user_program,b.spid server_process, b.program server_programfrom v$session a, v$process bwhere a.username=USER and a.PADDR=b.ADDR;

SERVER DBUSER OSUSER--------- ------------------------------ ------------------------------USER_PROCESS MACHINE------------ ----------------------------------------------------------------TERMINAL USER_PROGRAM------------------------------ ------------------------------------------------SERVER_PROCE SERVER_PROGRAM------------ ------------------------------------------------DEDICATED SYSTEM curso0130580 cursos.atica.um.espts/11 [email protected] (TNS V1-V3)30581 [email protected] (TNS V1-V3)

SQL> !ps -fp 30581UID PID PPID C STIME TTY TIME CMDoracle 30581 30580 0 13:36 ? 00:00:01 oracleCURSOxy(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))

El proceso servidor asociado a mi sesión es un servidor DEDICADO.

1.9. ¿Cuanto ocupa la Dictionary cache y la Library cache en tu BD? (v$sgastat)

En la vista V$SGASTAT hay información detallada sobre las partes de la SGA.

Solución:

SQL> select * from v$sgastat where name like '%cache';POOL NAME BYTES----------- -------------------------- ---------- buffer_cache 4194304shared pool library cache 1567084shared pool dictionary cache 1610880



1.10. Ver la actividad de la Library Cache (v$librarycache).

En la vista V$LIBRARYCACHE podemos ver los ratios (en “tantos por uno”).

Solución:

SQL> select namespace,gethitratio from v$librarycache;NAMESPACE GETHITRATIO--------------- -----------SQL AREA .806060606TABLE/PROCEDURE .713888889BODY 0TRIGGER 1INDEX .46969697CLUSTER .953020134OBJECT 1PIPE 1JAVA SOURCE 1JAVA RESOURCE 1JAVA DATA 111 rows selected.

1.11. Ver las sentencias SQL que guarda la Shared-Pool (v$sqlarea).

En la vista V$SQLAREA tenemos el contenido del “área SQL” de la sharedpool.

Solución:

SQL> select sql_text from v$sqlarea;SQL_TEXT--------------------------------------------------------------------------------commitALTER DATABASE OPENgrant sysdba to systemALTER DATABASE MOUNTSELECT USER FROM DUALselect o.owner#,o.obj#,decode(o.linkname,null, decode(u.name,null,'SYS',u.name),o.remoteowner), o.name,o.linkname,o.namespace,o.subname from user$ u, obj$ o where u.user#(+)=o.owner# and o.type#=:1 and not exists (select p_obj# from dependency$ where p_obj# = o.obj#) for update...

1.12. Crear el fichero de autenticación y activarlo (orapwd).

Los pasos a seguir son:! Crear fichero en $ORACLE_HOME/dbs con la utilidad orapwd.! Añadir el parámetro remote_login_passwordfile al init.ora.! Parar y arrancar la BD.



Solución:

$ orapwd file=$ORACLE_HOME/dbs/orapwCURSOxy password=miclave entries=5$ chmod g+w orapwCURSOxy

$ echo "remote_login_passwordfile=EXCLUSIVE" >>/u01/app/oracle/admin/CURSOxy/pfile/initCURSOxy.ora$ echo >> /u01/app/oracle/admin/CURSOxy/pfile/initCURSOxy.ora

SQL> CONNECT / AS SYSDBASQL> SHUTDOWN IMMEDIATESQL> exit

SQL> CONNECT SYS AS SYSDBAEnter password: miclaveSQL> STARTUP

1.13. ¿Qué ocurre si “quitamos” la variable LD_LIBRARY_PATH?

! Comprobar su valor y dejarla en blanco.! Ejecutar sqlplus.! Volver a asignar el valor original y ejecutar de nuevo sqlplus.

Solución:

$ echo $LD_LIBRARY_PATH/u01/app/oracle/product/9.2.0.1.0/lib$ unset LD_LIBRARY_PATH$ sqlplussqlplus: error while loading shared libraries: libclntsh.so: cannot open sharedobject file: No such file or directory$ export LD_LIBRARY_PATH=/u01/app/oracle/product/9.2.0.1.0/lib$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Wed Feb 2 10:18:27 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL>

1.14. Asignar la variable NLS_LANG para nuestro país y comprobar el cambio en las respuestas de Oracle desde sqlplus.

! Comprobar el valor de NLS_LANG y ver la fecha del sistema desde sqlplus.! Salir de sqlplus y asignar spanish_spain a NLS _LANG.! Ejecutar sqlplus y comprobar que pide “usuario” y no “username”.! Comprobar de nuevo la fecha del sistema desde sqlplus y verificar el cambio de formato.

Solución:

$ echo $NLS_LANG

$ sqlplus



Enter user-name: / as sysdbaSQL> select sysdate from dual;SYSDATE---------02-FEB-05SQL> exit

$ export NLS_LANG=spanish_spain$ sqlplusIntroduzca el nombre de usuario: / as sysdbaSQL> select sysdate from dual;SYSDATE--------02/02/05SQL> exit

1.15. Subir el tamaño de la shared-pool de 8 a 12M y comprobar cómo aumenta el espacio libre.

! Comprobar el valor de shared_pool_size, así como el espacio libre en la shared_pool.! Asignarle 12M y volver a comprobar el valor del parámetro, así como el espacio libre que tiene ahora la

shared-pool.! Finalmente, volver a dejar la shared-pool con 8M y comprobar de nuevo los valores anteriores.

Solución:


SQL> select sum(bytes) from v$sgastat where pool='shared pool' and name like'%free%';SUM(BYTES)---------- 8922756

SQL> alter system set shared_pool_size=12M;System altered.



SQL> alter system set shared_pool_size=8M;System altered.

SQL> show parameter shared_pool_size



NAME TYPE VALUE------------------------------------ ----------- ------------------------------shared_pool_size big integer 8388608


1.16. Comprobar el funcionamiento de la caché de redolog, como protectora del contenido de la caché de datos. Para ello iniciaremos una transacción y provocaremos una caída de la BD, comprobando que al arrancarla de nuevo, se mantendrá la integridad de la misma.

! Consultar el contenido y la estructura de la tabla BORRAME del usuario SYSTEM.! Insertar una fila sin hacer commit y forzar la caída de la BD.! Arrancar de nuevo la BD y comprobar que la fila insertada no está (pues no se hizo commit).! Repetir la inserción de la fila, esta vez haciendo commit; y forzar la caída de la BD otra vez.! Arrancar la BD una vez más y comprobar que ahora la fila si está (ya que se validó la transacción con

commit).

Solución:

SQL> select * from system.borrame;C1----------Primera

SQL> desc system.borrame Name Null? Type ----------------------------------------- -------- ---------------------------- C1 VARCHAR2(10)

SQL> insert into system.borrame values ('Segunda');1 row created.

SQL> select * from system.borrame;C1----------PrimeraSegunda

SQL> shutdown abortORACLE instance shut down.

SQL> connect / as sysdbaConnected to an idle instance.

SQL> startupORACLE instance started.Database mounted.Database opened.SQL> select * from system.borrame;C1



----------Primera

SQL> insert into system.borrame values ('Segunda');1 row created.

SQL> commit;Commit complete.


SQL> shutdown abortORACLE instance shut down.

SQL> connect / as sysdbaConnected to an idle instance.

SQL> startupORACLE instance started.Database mounted.Database opened.


1.17. Comprobar el funcionamiento de la caché de datos, en lo que se refiere a la mejora del rendimiento cuando se repite una consulta. ¿Por qué la segunda vez que se lanza la misma consulta tarda menos?

! Activar la medición de tiempos en sqlplus con SET TIMING ON.! Lanzar la consulta SELECT COUNT(*) FROM IDL_UB1$.! Volver a lanzar la misma consulta.! Comprobar que la segunda ejecución tarda mucho menos, ya que los datos ya se cargaron en la caché de

datos al lanzarla la primera vez; y por tanto se acceden directamente en memoria y no en disco.

Solución:

SQL> set timing onSQL> select count(*) from IDL_UB1$; COUNT(*)---------- 3798Elapsed: 00:00:02.65

SQL> r COUNT(*)---------- 3798



Elapsed: 00:00:00.05



PRACTICAS TEMA 2.

ARRANQUE Y PARADA DE LA BASE DE DATOS.

2.1. Fichero init.ora. Arranque y parada de la BD.

! Localizar el fichero init.ora (y spfile si existe) de nuestra BD:! Anotar el valor de los parámetros: db_block_size, shared_pool_size, db_cache_size, log_buffer, processes.! Crear fichero /home/CURSO/cursoxy/init01xy.ora, copia del initCURSOxy.ora, y modificar processes=9.! Arrancar la BD y comprobar valor de parámetros, ¿ha tomado el nuevo valor? ¿por qué?.! Parar la BD y arrancar con el init01xy.ora. Comprobar parámetro processes. Abrir otra conexión de sqlplus y ver qué

ocurre.! Crear un spfile a partir del init.ora que hay en $ORACLE_HOME/dbs. ¿Donde está? ¿qué contiene? Modificar el

parámetro processes=9 en dicho spfile. Finalmente, dejar processes=10.! Parar la BD de forma normal, estando conectado algún usuario y ver qué ocurre. Y qué sucede cuando todos los

usuarios se desconectan.! Repetir la parada de la BD, estando conectado algún usuario, de forma que no espere a que se desconecten.

2.2. Arranque de la BD. Comprobar las diferentes fases en el arranque de la BD.! Arranca sólo la instancia (NOMOUNT) y consulta algún parámetro (db_cache_size, shared_pool_size, log_buffer,

processes, etc). Qué ocurre al acceder a V$CONTROLFILE.! Ahora monta la BD y vuelve a consultar V$CONTROLFILE. Qué sucede al leer DBA_USERS.! Abre la BD en modo READ ONLY y crea una tabla. Activa el modo READ WRITE y vuelve a crear la tabla,

observando qué sucede.

2.3. Fichero alert.log y ficheros de traza. Diccionario de datos.! Busca y consulta el fichero de alert de la BD. Comprueba el último arranque de la BD.! Busca si hay ficheros de traza, si son de usuario o de procesos background. Mira el contenido.! Saca la lista de vistas del DD y fíjate en los comentarios asociados a cada una. Consulta las columnas de dichas

vistas.! Consulta las vistas V$INSTANCE, V$DATABASE, V$SESSION, V$PROCESS, DBA_USERS.

2.4. Impedir las conexiones de usuarios, de modo que el DBA sí pueda conectarse. Intenta conectarte como scott/tiger. Volver a permitir conexiones de usuarios.

2.5. Forzar un checkpoint y hacer un insert en la tabla SCOTT.DEPT. Inmediatamente después hacer SHUTDOWN ABORT. Arrancar y comprobar SCOTT.DEPT. Volver a repetir el insert, haciendo commit antes del SHUTDOWN ABORT; y comprueba el contenido de SCOTT.DEPT.

2.6. Conéctate como SCOTT y haz update sobre DEPT (sin hacer commit). Desde otra sesión, cierra la BD con SHUTDOWN TRANSACTIONAL. ¿Qué pasa al hacer commit en la sesión de SCOTT?

2.7. Conéctate como usuario scott/tiger. Activa la traza y haz una query con una join entre EMP y DEPT (select a.ename, b.dname from emp a, dept b where a.deptno=b.deptno;). Desactiva la traza y analiza el fichero que se ha generado con el comando tkprof.



2.1. Fichero init.ora. Arranque y parada de la BD.

! Localiza el fichero init.ora de tu BD.La ubicación por defecto es $ORACLE_HOME/dbs; y para el caso de usar OFA, $ORACLE_BASE/admin/$ORACLE_SID/pfile (que se suele asignar a la variable PFILE)./home/CURSO/curso23 (CURSO23)> ls -l$ORACLE_HOME/dbs/init$ORACLE_SID.oralrwxrwxrwx 1 oracle dba 51 24 oct 08:42/u01/app/oracle/product/9.2.0.1.0/dbs/initCURSO23.ora ->/u01/app/oracle/admin/CURSO23/pfile/initCURSO23.ora

/home/CURSO/curso23 (CURSO23)> ls -l$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora-rw-r--r-- 1 oracle oinstall 4413 24 oct 08:42/u01/app/oracle/admin/CURSO23/pfile/initCURSO23.ora

En este caso el fichero init.ora original está bajo $PFILE y bajo $ORACLE_HOME/dbs hay un enlace a dicho fichero.

! Anota el valor de los parámetros: db_block_size, shared_pool_size, db_cache_size, log_buffer, processes asignados en el init.ora de tu BD.

/home/CURSO/curso23 (CURSO23)> grep db_block_size$PFILE/init$ORACLE_SID.ora*** db_blcck_size no está asignado, por tanto toma el valor por defecto 2048./home/CURSO/curso23 (CURSO23)> grep db_cache_size$PFILE/init$ORACLE_SID.oradb_cache_size = 1M/home/CURSO/curso23 (CURSO23)> grep shared_pool_size$PFILE/init$ORACLE_SID.orashared_pool_size = 8M/home/CURSO/curso23 (CURSO23)> grep log_buffer$PFILE/init$ORACLE_SID.oralog_buffer = 32768/home/CURSO/curso23 (CURSO23)> grep processes$PFILE/init$ORACLE_SID.oraprocesses = 10

! Crea fichero /home/CURSO/cursoxy/init01xy.ora, copia del initCURSOxy.ora, y modifica processes= 9.

Puedes editar el init.ora con el vi o también añadir al final del mismo una línea con el nuevo valor del parámetro (este segundo método es el usado en el ejemplo, usa tú el que quieras)./home/CURSO/curso01 (CURSOxy)> cp $PFILE/init$ORACLE_SID.orainit0101.ora/home/CURSO/curso01 (CURSOxy)> echo processes = 9 >> init0101.ora/home/CURSO/curso01 (CURSOxy)> grep processes init0101.oraprocesses = 10processes = 9

! Arranca la BD (sólo si está parada) y comprueba los parámetros anteriores.SQL> connect / as sysdba



SQL> startupInstancia de ORACLE arrancada.Total System Global Area 24670100 bytesFixed Size 73620 bytesVariable Size 7647232 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesBase de datos montada.Base de datos abierta.

SQL> show parameter processesNAME TYPE VALUE------------------------------------ ------- ------------------------------processes integer 10

SQL> show parameter shared_pool_sizeNAME TYPE VALUE------------------------------------ ------- ------------------------------shared_pool_size string 4194304

SQL> show parameter db_cache_sizeNAME TYPE VALUE------------------------------------ ------- ------------------------------db_cache_size integer 4194304

SQL> show parameter log_bufferNAME TYPE VALUE------------------------------------ ------- ------------------------------log_buffer integer 32768

! Para la BD y arranca con el init01xy.ora. Comprueba el parámetro processes. Haz varias conexiones con sqlplus y comprueba qué ocurre.

SQL> shutdown immediateBase de datos cerrada.Base de datos desmontada.Instancia de ORACLE cerrada.SQL> startup pfile=init0101.oraInstancia de ORACLE arrancada.Total System Global Area 22908820 bytesFixed Size 73620 bytesVariable Size 5885952 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesBase de datos montada.Base de datos abierta.SQL> show parameter processesNAME TYPE VALUE------------------------------------ ------- ------------------------------processes integer 9

Después de abrir una sesión como el usuario SYSTEM (además de la que tenía como SYS, de la conexión como “/ as sysdba”); si intentas abrir una más, da un error, ya que no puede abrir más de 9 procesos (6 background, 1 PSEUDO y 2 sesiones)./home/CURSO/curso01 (CURSOxy)> sqlplusSQL*Plus: Release 9.2.0.1.0 - Production on Jue oct 26 13:26:18 2004(c) Copyright 2000 Oracle Corporation. All rights reserved.Introduzca el nombre de usuario: system



Introduzca su clave:ERROR:ORA-00020: maximum number of processes (9) exceeded

! Crear un spfile a partir del init.ora que hay en $ORACLE_HOME/dbs. ¿Donde está? ¿qué contiene? Modificar el parámetro processes=9 en dicho spfile. Finalmente, dejar processes=10.

La ubicación por defecto tanto del init.ora como del spfile, es $ORACLE_HOME/dbs; por tanto crear el spfile por defecto es muy sencillo:

SQL> create spfile from pfile;File created.SQL> !cat $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora...*.processes=10...

Este comando crea el spfile spfile$ORACLE_SID.ora en $ORACLE_HOME/dbs, de forma que en el siguiente arranque de la BD se usará (en lugar del init.ora). Contiene los mismo parámetros que tenía el init.ora a partir del cual se crea. Para modificar un parámetro en el spfile, no podemos editar el fichero (es binario), lo haremos desde una sesión de la BD:Al visualizar el contenido del spfile directamente se pueden ver “caracteres extraños”, pues se trata de un fichero binario.

SQL> alter system set processes=9 scope=spfile;System altered.SQL> !cat $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora²kA *.background_dump_dest='/u01/app/oracle/admin/CURSOxy/bdump'...*.processes=9...*.user_dump_dest='/u01/app/oracle/admin/CURSOxy/udump'SQL> alter system set processes=10 scope=spfile;

! Para la BD de forma normal, estando conectado un usuario (además del sys de tu sesión) y mira qué ocurre. Y qué sucede cuando todos los usuarios se desconectan.

SQL> shutdownSe queda esperando a que se desconecten los usuarios. Al cerrar la sesión del system que está abierta, termina de cerrar sin problemas. Se puede ver mirando las últimas líneas del alertSID.log.(estando conectado el system, además de la sesión del sys que está cerrando). El fichero de alert está en el directorio indicado por el parámetro BACKGROUND_DUMP_DEST; o por defecto en $ORACLE_HOME/rdbms/log. Si se usa OFA lo habitual es ubicarlo en $ORACLE_BASE/admin/$ORACLE_SID/bdump (indicado por la variable BDUMP).

/home/CURSO/curso01 (CURSOxy)> tail -26f $BDUMP/alert_CURSOxy.log...Shutting down instance (normal)License high water mark = 3



(desconectándose el system)Thu oct 26 14:08:23 2004ALTER DATABASE CLOSE NORMALThu oct 26 14:08:23 2004SMON: disabling tx recoverySMON: disabling cache recoveryThu oct 26 14:08:23 2004Thread 1 closed at log sequence 4199Thu oct 26 14:08:23 2004Completed: ALTER DATABASE CLOSE NORMALThu oct 26 14:08:23 2004ALTER DATABASE DISMOUNTCompleted: ALTER DATABASE DISMOUNTarchiving is disabled

! Repite la parada de la BD, estando conectado algún usuario, de forma que no espere a que se desconecten.

SQL> shutdown immediateBase de datos cerrada.Base de datos desmontada.Instancia de ORACLE cerrada.

2.2. Arranque de la BD (STARTUP). Comprobar las diferentes fases en el arranque de la BD (nomount, mount y open).

! Arrancar sólo la instancia (NOMOUNT) y consultar algún parámetro. Qué ocurre al acceder a V$CONTROLFILE y por qué.

Al arrancar sólo la instancia, podemos consultar los parámetros del init, ya que este fichero (o el spfile si exite) se lee justo antes de arrancar la instancia. Pero, al indicar NOMOUNT, todavía no se ha abierto el fichero de control, por lo que no tendremos acceso a su contenido. Y mucho menos a las tablas estáticas del DD que están almacenadas en la BD, cuyos ficheros tampoco se han abierto.SQL> startup nomountInstancia de ORACLE arrancada.Total System Global Area 24670100 bytesFixed Size 73620 bytesVariable Size 7647232 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytes

SQL> show parameter processesNAME TYPE VALUE------------------------------------ ------- ------------------------------processes integer 150

SQL> select count(*) from v$controlfile; COUNT(*)---------- 0

SQL> select * from dba_users;select * from dba_users *



ERROR en línea 1:ORA-01219: b. de datos no abierta: solo se permiten consultas en tablas/vistasfijas

! Ahora montar la BD y volver a consultar V$CONTROLFILE. Que sucede al leer DBA_USERS.

Al montar la BD, se abre el fichero de control leyendo su contenido, de forma que ya sabemos la ubicación y el nombre de cada uno de los ficheros que componen la BD.SQL> alter database mount;Base de datos modificada.

SQL> select name from v$controlfile;NAME-----------------------------------/u02/oradata/CURSOxy/control01.ctl/u03/oradata/CURSOxy/control02.ctl/u04/oradata/CURSOxy/control03.ctl

SQL> select * from dba_users;select * from dba_users *ERROR en línea 1:ORA-01219: b. de datos no abierta: solo se permiten consultas entablas/vistasfijas

SQL> select count(*) from v$datafile; COUNT(*)---------- 5

SQL> select count(*) from v$logfile; COUNT(*)---------- 3

! Cerrar la BD y abrirla en modo READ ONLY, y crear una tabla. Activar modo READ WRITE y volver a crear la tabla.

Para abrir la BD en modo read-only, primero debemos arrancar la BD sin abrirla (MOUNT).SQL> startup mountInstancia de ORACLE arrancada.Total System Global Area 24670100 bytesFixed Size 73620 bytesVariable Size 7647232 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesBase de datos montada.

SQL> alter database open read only;Base de datos modificada.

SQL> create table mitabla (c1 varchar2(2));create table mitabla (c1 varchar2(2))*



ERROR en línea 1:ORA-00604: error producido a nivel 1 de SQL recursivoORA-00372: el fichero 1 no puede ser modificado en este momentoORA-01110: fichero de datos 1: '/u02/oradata/CURSOxy/system01.dbf'

SQL> shutdown immediateBase de datos cerrada.Base de datos desmontada.Instancia de ORACLE cerrada.SQL> startupInstancia de ORACLE arrancada.Total System Global Area 24670100 bytesFixed Size 73620 bytesVariable Size 7647232 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesBase de datos montada.Base de datos abierta.SQL> create table mitabla (c1 varchar2(2));Tabla creada.

Como hemos visto, en modo read-only no podemos crear una tabla pues los ficheros de la BD están en modo de sólo lectura. Si abrimos la BD en modo normal (read-write), no tendremos problemas para crear la tabla.

2.3. Fichero alert.log y ficheros de traza. Diccionario de datos.! Busca y consulta el fichero de alert de la BD. Comprueba el último arranque de la BD./home/CURSO/curso01 (CURSOxy)> ls -l

$ORACLE_HOME/rdbms/log/alert_$ORACLE_SID.logls: /u01/app/oracle/product/9.2.0.1.0/rdbms/log/alert_CURSOxy.log: No existe el

fichero o el directorio/home/CURSO/curso01 (CURSOxy)> ls -l $BDUMP/alert_$ORACLE_SID.log-rw-rw-r-- 1 oracle dba 144537 nov 2 09:45

/u01/app/oracle/admin/CURSOxy/bdump/alert_CURSOxy.log

En este caso el fichero de alert no está en su ubicación por defecto, pues se está usando OFA y se ha modificado el parámetro BACKGROUND_DUMP_DEST consecuentemente.

/home/CURSO/curso01 (CURSOxy)> grep -i background$ORACLE_HOME/dbs/init$ORACLE_SID.ora

background_dump_dest = /u01/app/oracle/admin/CURSOxy/bdump

Se pueden ver las últimas líneas del fichero de alert con el comando “tail” de Linux./home/CURSO/curso01 (CURSOxy)> tail -26

/u01/app/oracle/admin/CURSOxy/bdump/alert_$ORACLE_SID.logTue Nov 2 09:44:58 2004Database mounted in Exclusive Mode.Completed: ALTER DATABASE MOUNTTue Nov 2 09:44:58 2004ALTER DATABASE OPENTue Nov 2 09:44:58 2004Thread 1 opened at log sequence 556 Current log# 1 seq# 556 mem# 0: /home/u04/oradata/CURSOxy/redo01.logSuccessful open of redo thread 1.Tue Nov 2 09:44:58 2004MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setTue Nov 2 09:44:58 2004SMON: enabling cache recoveryTue Nov 2 09:44:59 2004Undo Segment 1 OnlinedUndo Segment 2 Onlined



Undo Segment 3 OnlinedSuccessfully onlined Undo Tablespace 2.Tue Nov 2 09:44:59 2004SMON: enabling tx recoveryTue Nov 2 09:44:59 2004Database Characterset is WE8ISO8859P15replication_dependency_tracking turned off (no async multimaster replication

found)Completed: ALTER DATABASE OPENTue Nov 2 09:45:22 2004ALTER SYSTEM SET processes=20 SCOPE=SPFILE;

! Busca si hay ficheros de traza, si son de usuario o de procesos background. Mira el contenido.

Los ficheros de traza de los procesos background y de usuario, se generarán en los directorios indicados en los parámetros BACKGROUND_DUMP_DEST y USER_DUMP_DEST (o en la ubicación por defecto $ORACLE_HOME/rdbms/log, si dichos parámetros no están asignados./home/CURSO/curso01 (CURSOxy)> grep -i dest $ORACLE_HOME/dbs/init$ORACLE_SID.ora# log_archive_dest = disk$rdbms:[oracle.archive]background_dump_dest = /u01/app/oracle/admin/CURSOxy/bdumpcore_dump_dest = /u01/app/oracle/admin/CURSOxy/cdumpuser_dump_dest = /u01/app/oracle/admin/CURSOxy/udump

/home/CURSO/curso01 (CURSOxy)> ls -lt /u01/app/oracle/admin/CURSOxy/udumptotal 120-rw-r----- 1 oracle dba 735 nov 2 09:44 curso01_ora_20604.trc-rw-r----- 1 oracle oinstall 652 nov 2 09:35 curso01_ora_20448.trc-rw-r----- 1 oracle oinstall 733 oct 29 13:42 curso01_ora_2792.trc...-rw-rw---- 1 oracle dba 735 sep 28 13:21 curso01_ora_25508.trc

/home/CURSO/curso01 (CURSOxy)> ls -lt /u01/app/oracle/admin/CURSOxy/bdumptotal 280-rw-rw-r-- 1 oracle dba 144537 nov 2 09:45 alert_CURSOxy.log-rw-r----- 1 oracle oinstall 614 nov 2 09:35 curso01_lgwr_2785.trc-rw-r----- 1 oracle oinstall 616 oct 29 13:19 curso01_lgwr_21906.trc...-rw-rw---- 1 oracle dba 657 ago 13 14:10 curso01_ora_13168.trc

Se puede ver el contenido de los ficheros usando el comando “more” de Linux, o “cat” si el fichero e corto.

! Saca la lista de vistas del DD y fíjate en los comentarios asociados a cada una. Consulta las columnas de dichas vistas.

SQL> select * from dictionary where table_name='DBA_TABLES';TABLE_NAME------------------------------COMMENTS--------------------------------------------------------------------------------DBA_TABLESDescription of all relational tables in the database

SQL> select * from dict_columns where table_name='DBA_TABLES';TABLE_NAME COLUMN_NAME------------------------------ ------------------------------COMMENTS--------------------------------------------------------------------------------DBA_TABLES DEGREE



The number of threads per instance for scanning the table...DBA_TABLES NUM_FREELIST_BLOCKSThe number of blocks on the freelist

46 rows selected.

! Consulta algunas vistas estáticas (como DBA_USERS, DBA_TABLESPACES, DBA_TABLES, DBA_ROLES), y algunas dinámicas (como V$INSTANCE, V$DATABASE, V$SESSION, V$PROCESS).

SQL> select username,ACCOUNT_STATUS,CREATED from dba_users;USERNAME ACCOUNT_STATUS CREATED------------------------------ -------------------------------- ---------SYS OPEN 13-AUG-04SYSTEM OPEN 13-AUG-04OUTLN OPEN 13-AUG-04DBSNMP OPEN 17-AUG-04SCOTT OPEN 28-SEP-04

SQL> selectrpad(username,14),rpad(osuser,10),rpad(machine,15),rpad(terminal,10),

rpad(program,27) from v$session where username is not null order by username;RPAD(USERNAME, RPAD(OSUSE RPAD(MACHINE,15 RPAD(TERMI RPAD(PROGRAM,27)-------------- ---------- --------------- ---------- ---------------------------SYSTEM juanlu cursos.atica.um pts/2 [email protected]

2.4. Impedir las conexiones de usuarios, de modo que el DBA sí pueda conectarse. Intenta conectarte como scott/tiger. Volver a permitir conexiones de usuarios.

En primer lugar vamos a parar la BD y a arrancarla en modo RESTRICT. Después intentaremos la conexión como el usuario SCOTT. Finalmente desactivaremos el modo RESTRICT y podremos conectarnos como SCOTT.

SQL> connect / as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

SQL> startup restrictInstancia de ORACLE arrancada.Total System Global Area 24670100 bytesFixed Size 73620 bytesVariable Size 7647232 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesBase de datos montada.Base de datos abierta.

SQL> connect scott/tigerERROR:ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilegeAviso: Ya no est conectado a ORACLE!



SQL> connect / as sysdbaConectado.SQL> alter system disable restricted session;Sistema modificado.

SQL> connect scott/tigerConectado.SQL> desc dept Nombre Nulo? Tipo ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)SQL> disconnectDesconectado de Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning optionJServer Release 9.2.0.1.0 - Production

2.5. Forzar un checkpoint y hacer un insert en la tabla SCOTT.DEPT. Inmediatamente después hacer SHUTDOWN ABORT. Arrancar y comprobar SCOTT.DEPT. Volver a repetir el insert, haciendo commit antes del SHUTDOWN ABORT; y comprueba el contenido de SCOTT.DEPT.

Se trata de comprobar los mecanismos de recuperación de la instancia. Vamos a insertar una fila en SCOTT.DEPT y a simular una caída de la BD con SHUTDOWN ABORT. Comprobaremos que, como no se hizo COMMIT, los datos insertados no están, al arrancar de nuevo la BD. Si repetimos el experimento haciendo COMMIT después del INSERT, comprobaremos que la información no se pierde; ya que al hacer commit estamos forzando el volcado a disco de los buffers redolog, que serán leídos en el siguiente arranque al recuperarse la instancia.

Recuerda que el checkpoint es el evento que fuerza la escritura de los bloques de datos q hay en la SGA, a disco, por parte del DBWR (previo volcado de los buffer redo log, por parte del LGWR).

SQL> connect / as sysdbaConectado.SQL> alter system checkpoint;Sistema modificado.SQL> insert into scott.dept values (99,'FORMACION','MURCIA');1 fila creada.SQL> SHUTDOWN ABORTInstancia de ORACLE cerrada.

SQL> startupInstancia de ORACLE arrancada....Base de datos abierta.

SQL> select * from scott.dept; DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO



40 OPERATIONS BOSTON

SQL> alter system checkpoint;Sistema modificado.SQL> insert into scott.dept values (99,'FORMACION','MURCIA');1 fila creada.SQL> commit;Validación terminada.SQL> shutdown abortInstancia de ORACLE cerrada.

SQL> startupInstancia de ORACLE arrancada.Total System Global Area 24670100 bytesFixed Size 73620 bytesVariable Size 7647232 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesBase de datos montada.Base de datos abierta.

SQL> select * from scott.dept; DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 99 FORMACION MURCIA

2.6. Conéctate como SCOTT y haz update sobre DEPT (sin hacer commit). Desde otra sesión, cierra la BD con SHUTDOWN TRANSACTIONAL. ¿Qué pasa al hacer commit en la sesión de SCOTT?

Recuerda que el SHUTDOWN TRANSACTIONAL espera a que terminen todas las transacciones en curso; por tanto esperará hasta que SCOTT haga commit. Una vez que SCOTT ha hecho commit, el shutdown cerrará su sesión al cabo de unos minutos.

(SESION DE SCOTT)SQL> connect scott/tigerConectado.SQL> update dept set deptno=88 where deptno=99;1 fila actualizada.

(SESION DEL SYS)SQL> CONNECT / AS SYSDBAConectado.SQL> shutdown transactional

(SESION DE SCOTT)SQL> !tail -7 $BDUMP/alert_CURSO22.logMon Apr 26 09:24:55 2004SMON: enabling cache recoverySMON: enabling tx recoveryMon Apr 26 09:24:57 2004Completed: ALTER DATABASE OPENMon Apr 26 09:34:42 2004Shutting down instance (transactional)



SQL> commit;Validación terminada.

SQL> !tail -10 $BDUMP/alert_CURSO22.logMon Apr 26 09:24:55 2004SMON: enabling cache recoverySMON: enabling tx recoveryMon Apr 26 09:24:57 2004Completed: ALTER DATABASE OPENMon Apr 26 09:34:42 2004Shutting down instance (transactional)Mon Apr 26 09:35:12 2004All transactions complete. Performing immediate shutdownLicense high water mark = 7

SQL> !tail -10 $BDUMP/alert_CURSO22.logSMON: disabling tx recoverySMON: disabling cache recoveryMon Apr 26 09:36:18 2004Thread 1 closed at log sequence 191Mon Apr 26 09:36:18 2004Completed: ALTER DATABASE CLOSE NORMALMon Apr 26 09:36:18 2004ALTER DATABASE DISMOUNTCompleted: ALTER DATABASE DISMOUNTarchiving is disabled

(SESION DEL SYS)Base de datos cerrada.Base de datos desmontada.Instancia de ORACLE cerrada.

2.7. Conéctate como usuario scott/tiger. Activa la traza y haz una query con una join entre EMP y DEPT (select a.ename, b.dname from emp a, dept b where a.deptno=b.deptno;). Desactiva la traza y analiza el fichero que se ha generado con el comando tkprof.

Desde sqlplus, el usuario conectado (p.e. SCOTT) puede activar la traza con ALTER SESSION SET SQL_TRACE=TRUE. Esto se hace justamente antes de lanzar las sentencias que se quieren analizar. Una vez terminadas las sentencias en cuestión, se vuelve a desactivar la traza con ALTER SESSION SET SQL_TRACE=FALSE. El fichero de traza se generará en el directorio indicado por el parámetro USER_DUMP_DEST, que por defecto es $ORACLE_HOME/rdbms/log, y en caso de usar OFA será el directorio $UDUMP, y será el fichero más reciente generado.

SQL> connect scott/tigerConectado.SQL> alter session set sql_trace=true;Sesión modificada.SQL> select a.ename, b.dname from emp a, dept b where a.deptno=b.deptno;ENAME DNAME---------- --------------SMITH RESEARCH...MILLER ACCOUNTING14 filas seleccionadas.



SQL> alter session set sql_trace=false;Sesión modificada.

SQL> !ls -lt $UDUMP|head -2total 24-rw-r----- 1 oracle dba 11435 26 Oct 09:55 ora_150400_curso22.trcSQL> exit

cursos (CURSO22):/home/CURSO/curso22 > tkprof $UDUMP/ora_150400_cursoxy.trcsalida_traza.txt explain=scott/tiger sys=no

TKPROF: Release 9.2.0.1.0 - Production on Lun Oct 26 09:56:31 2004(c) Copyright 2000 Oracle Corporation. All rights reserved.

cursos (CURSO22):/home/CURSO/curso22 > cat salida_traza.txt...********************************************************************************count = number of times OCI procedure was executedcpu = cpu time in seconds executingelapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = number of buffers gotten in current mode (usually for update)rows = number of rows processed by the fetch or execute call********************************************************************************...********************************************************************************select a.ename, b.dnamefrom emp a, dept b where a.deptno=b.deptnocall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 2 0 2 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 3 18 4 14------- ------ -------- ---------- ---------- ---------- ---------- ----------total 4 0.00 0.00 5 18 6 14...Optimizer goal: CHOOSE...Rows Execution Plan------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 14 NESTED LOOPS 15 TABLE ACCESS (FULL) OF 'EMP' 14 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 28 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)********************************************************************************...********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.00 0.00 2 0 2 0Execute 3 0.00 0.00 0 0 0 0Fetch 2 0.00 0.00 3 18 4 14------- ------ -------- ---------- ---------- ---------- ---------- ----------total 7 0.00 0.00 5 18 6 14...OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows



------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 13 0.00 0.00 0 0 0 0Execute 23 0.00 0.00 0 0 0 0Fetch 45 0.00 0.00 2 73 0 32------- ------ -------- ---------- ---------- ---------- ---------- ----------total 81 0.00 0.00 2 73 0 32...



PRACTICAS TEMA 3.

FICHERO DE CONTROL.

o Localizar el fichero de control desde el S.O y desde la propia BD.

o Consultar la información de la BD relativa al contenido del fichero de control. Forzar un checkpoint y volver a consultar. Consultar los registros que contiene el fichero de control.

o Añade una copia al fichero de control de la BD en “/home/u04/oradata/$ORACLE_SID”.

o Sacar una copia de seguridad del fichero de control.



3.1.Localizar el fichero de control desde el SO y desde la BD.! En primer lugar buscaremos los ficheros desde el SO. El nombre no tiene por qué seguir un patrón

determinado, aunque en general se les suele poner la extensión “ctl” o se les incluye la palabra “control” en el nombre.

/home/CURSO/curso01 (CURSOxy)> ls -lat/u0?/oradata/$ORACLE_SID/*control*-rw-rw---- 1 oracle dba 4384768 nov 4 12:36 /u02/oradata/CURSOxy/ora_control1-rw-rw---- 1 oracle dba 4384768 nov 4 12:36 /u03/oradata/CURSOxy/ora_control2/home/CURSO/curso01 (CURSOxy)> ls -lat/u0?/oradata/$ORACLE_SID/*.ctlls: /u0?/oradata/CURSOxy/*.ctl: No existe el fichero o el directorio

! Ahora veremos como obtener la misma información desde la propia BD, de forma más directa.SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/home/u02/oradata/CURSOxy/ora_control1/home/u03/oradata/CURSOxy/ora_control2

3.2. Consultar la información de la BD relativa al contenido del fichero de control. Forzar un checkpoint y volver a consultar. Consultar los registros que contiene el fichero de control.

! En la vista V$DATABASE hay una serie de columnas, con el prefijo CONTROLFILE, que nos dan información sobre el fichero de control, por ejemplo la fecha de creación del fichero de control (CONTROLFILE_CREATED) o la última vez que se actualizó la copia del fichero de control (!"#$%"&'(&)*$(+)).

SQL> select CONTROLFILE_TYPE type,to_char(CONTROLFILE_CREATED,'dd/mm/yyyy hh24:mi') created,CONTROLFILE_SEQUENCE# secuence,CONTROLFILE_CHANGE# change,to_char(CONTROLFILE_TIME,'dd/mm/yyyy hh24:mi') time from v$database

TYPE CREATED SECUENCE CHANGE TIME------- ---------------- ---------- ---------- ----------------CURRENT 13/08/2004 14:15 1805 1115572 04/11/2004 12:47

! Si hacemos un checkpoint, que se registra en el fichero de control, se actualiza la columna V$DATABASE.CONTROLFILE_SEQUENCE#.

SQL> alter system checkpoint;Sistema modificado.

SQL> select CONTROLFILE_TYPE type,to_char(CONTROLFILE_CREATED,'dd/mm/yyyy hh24:mi') created,CONTROLFILE_SEQUENCE# secuence,CONTROLFILE_CHANGE# change,to_char(CONTROLFILE_TIME,'dd/mm/yyyy hh24:mi') time from v$database

TYPE CREATED SECUENCE CHANGE TIME



------- ---------------- ---------- ---------- ----------------CURRENT 13/08/2004 14:15 1806 1115772 04/11/2004 12:57

En la vista V$CONTROLFILE_RECORD_SECTION se pueden ver las distintas secciones que componen el fichero de control y los registros de los que consta cada una; así como el número de registros de cada tipo usados hasta el momento.SQL> select TYPE,RECORDS_USED,RECORDS_TOTAL

from v$controlfile_record_section;TYPE RECORDS_USED RECORDS_TOTAL-------------------- ------------ -------------DATABASE 1 1CKPT PROGRESS 0 11REDO THREAD 1 8REDO LOG 3 32DATAFILE 4 254FILENAME 8 319TABLESPACE 5 254TEMPORARY FILENAME 1 254RMAN CONFIGURATION 0 50LOG HISTORY 560 843OFFLINE RANGE 0 289ARCHIVED LOG 0 800BACKUP SET 0 303BACKUP PIECE 0 508BACKUP DATAFILE 0 523BACKUP REDOLOG 0 79DATAFILE COPY 0 509BACKUP CORRUPTION 0 276COPY CORRUPTION 0 303DELETED OBJECT 0 809PROXY COPY 0 572BACKUP SPFILE 0 56DATABASE INCARNATION 1 3623 rows selected.

3.3. Añade una copia al fichero de control de la BD en “/home/u04/oradata/$ORACLE_SID”.

! Tenemos que cerrar la BD antes de crear la copia del fichero de control. Después, copiamos el fichero de control en /home/u04/oradata/$ORACLE_SID. Añadimos el nuevo fichero en el parámetro control_files del init. Arrancamos la BD. Y finalmente, comprobamos que el nuevo fichero aparezca en V$CONTROLFILE.


/home/CURSO/curso01 (CURSOxy)> cp -p /home/u02/oradata/CURSOxy/ora_control1/home/u04/oradata/CURSOxy/ora_control3

¡¡¡ OJO !!! hay que usr la opción “-p” para mantener los permisos deloriginal./home/CURSO/curso01 (CURSOxy)> ls -l /home/u04/oradata/CURSOxy/ora_control3-rw-rw---- 1 curso01 dba 4384768 nov 4 13:15/home/u04/oradata/CURSOxy/ora_control3tl/home/CURSO/curso01 (CURSOxy)> cp $ORACLE_HOME/dbs/init$ORACLE_SID.orainitxy02.ora



/home/CURSO/curso01 (CURSOxy)> vi initxy02.oracontrol_files = (/home/u02/oradata/CURSOxy/ora_control1,/home/u03/oradata/CURSOxy/ora_control2, /home/u04/oradata/CURSOxy/ora_control3)

SQL> startup pfile=init0102.oraORACLE instance started.Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesSQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/home/u02/oradata/CURSOxy/ora_control1/home/u03/oradata/CURSOxy/ora_control2/home/u04/oradata/CURSOxy/ora_control3

3.4. Sacar una copia de seguridad del fichero de control.

! En primer lugar sacaremos una copia sobre un fichero de traza, que contendrá las sentecias sql necesarias para crear un nuevo fichero de control. Como estamos usando OFA la traza, al ser de usuario, se generará en el directorio $ORACLE_BASE/admin/$ORACLE_SID/udump, posiblemente apuntado por la variable UDUMP.

SQL> alter database backup controlfile to trace;Base de datos modificada.SQL> !ls -lt $UDUMP|head -2total 140-rw-r----- 1 oracle dba 4851 nov 4 13:32 curso01_ora_31562.trcSQL> !more $UDUMP/curso01_ora_31562.trc

! Después sacaremos una copia binaria del fichero de control que sólo utilizaremos en caso de emergencia (de perder el fichero de control y sus copias), para arrancar la BD y, posteriormente crear otro fichero de control nuevo.

SQL> alter database backup controlfile to'/home/u04/oradata/CURSOxy/ora_control4';Base de datos modificada.

SQL> select name from v$controlfile;NAME--------------------------------------------------------------------------------/home/u02/oradata/CURSOxy/ora_control1.ctl/home/u03/oradata/CURSOxy/ora_control2.ctl/home/u04/oradata/CURSOxy/ora_control3.ctl

SQL> !ls -lt /u0?/oradata/$ORACLE_SID/*control*-rw-rw---- 1 oracle dba 4384768 nov 4 13:40 /u02/oradata/CURSOxy/ora_control1-rw-rw---- 1 oracle dba 4384768 nov 4 13:40 /u03/oradata/CURSOxy/ora_control2-rw-rw---- 1 curso01 dba 4384768 nov 4 13:40 /u04/oradata/CURSOxy/ora_control3-rw-r----- 1 oracle dba 4384768 nov 4 13:39 /u04/oradata/CURSOxy/ora_control4



PRACTICAS TEMA 4.

FICHEROS REDO LOG.

o Localizar los ficheros redolog de la BD. ¿Cuántos grupos hay y cuántos miembros tiene cada grupo? ¿están correctamente distribuidos?.

o Comprobar el fichero redo log activo. ¿Qué ocurre al forzar un "log switch"? ¿y al forzar un checkpoint?

o Añade un miembro más a cada grupo: /u03/oradata/$ORACLE_SID/redo11.log, /u03/oradata/$ORACLE_SID/redo12.log, /u03/oradata/$ORACLE_SID/redo13.log.

o Añade un grupo más (grupo 4), con dos miembros de 1M: /u03/oradata/$ORACLE_SID/redo04.log y /u04/oradata/$ORACLE_SID/redo14.log. Añade 2 grupos más (grupo 5 y 6), con las mismas características.

o Elimina los miembros del grupo 1, de uno en uno. ¿Qué ocurre al eliminar el último?. Borrar los grupos 1, 2 y 3. ¡¡¡ Ojo con borrar el redo log activo !!!

o Cambiar el nombre de los miembros de redo de los grupos 4, 5 y 6; a redo1a.log, redo1b.log, redo2a.log, redo2b.log, redo3a.log, redo3b.log.



4.1. Localizar los ficheros redolog de la BD. ¿Cuántos grupos hay y cuántos miembros tiene cada grupo? ¿están correctamente distribuidos?.

Hay 3 grupos con 1 miembro cada uno. Están todos en el disco /u04. Podría ser interesante añadir un miembro a cada grupo en el disco /u03.

/home/CURSO/curso01 (CURSOxy)> ls -lt /u0?/oradata/$ORACLE_SID/*.log-rw-rw---- 1 oracle dba 512512 nov 8 11:18 /u04/oradata/CURSOxy/redo02.log-rw-rw---- 1 oracle dba 512512 nov 8 09:32 /u04/oradata/CURSOxy/redo01.log-rw-rw---- 1 oracle dba 512512 nov 8 09:32 /u04/oradata/CURSOxy/redo03.log

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/home/u04/oradata/CURSOxy/redo01.log/home/u04/oradata/CURSOxy/redo02.log/home/u04/oradata/CURSOxy/redo03.log

4.2. Comprobar el fichero redo log activo. ¿Qué ocurre al forzar un "log switch"? ¿y al forzar un checkpoint?

El fichero redolog activo nos los muestra V$LOG, con status CURRENT.

SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS---------- ---------- ---------- ---------- ---------- --- ----------------FIRST_CHANGE# FIRST_TIM------------- --------- 1 1 562 512000 1 NO INACTIVE 1146778 05-NOV-04 2 1 563 512000 1 NO CURRENT 1175473 08-NOV-04 3 1 561 512000 1 NO INACTIVE 1112763 04-NOV-04

Al forzar un “log switch”, se provoca el cambio de fichero redo log (current)

SQL> ALTER SYSTEM SWITCH LOGFILE;Sistema modificado.

SQL> SELECT * FROM V$LOG; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS---------- ---------- ---------- ---------- ---------- --- ----------------FIRST_CHANGE# FIRST_TIM------------- --------- 1 1 562 512000 1 NO INACTIVE 1146778 05-NOV-04 2 1 563 512000 1 NO ACTIVE 1175473 08-NOV-04 3 1 564 512000 1 NO CURRENT 1178187 08-NOV-04

Un Checkpoint NO supone un “log switch”. Podemos consultar el avance de los checkpoints en V$DATABASE.CONTROLFILE_SECUENCE#.



SQL> select CONTROLFILE_SEQUENCE#,checkpoint_change#,LOG_MODE from v$database;CONTROLFILE_SEQUENCE# CHECKPOINT_CHANGE# LOG_MODE--------------------- ------------------ ------------

1860 1178372 NOARCHIVELOG

SQL> alter system checkpoint;Sistema modificado.

SQL> select CONTROLFILE_SEQUENCE#,checkpoint_change#,LOG_MODE from v$database;CONTROLFILE_SEQUENCE# CHECKPOINT_CHANGE# LOG_MODE--------------------- ------------------ ------------

1861 1178432 NOARCHIVELOG

4.3. Añade un miembro más a cada grupo: “/u03/oradata/$ORACLE_SID/redo11.log”, “/u03/oradata/$ORACLE_SID/redo12.log”, “/u03/oradata/$ORACLE_SID/redo13.log”.

Hasta ahora había 3 grupos redolog, con 1 miembro cada uno bajo /u04. Al añadir un miembro a cada grupo bajo /u03, pasarán a tener 2 miembros cada uno (consultar V$LOG y V$LOGFILE), en discos distintos.

SQL> alter database add logfile member '/u03/oradata/CURSOxy/redo11.log' to group 1, '/u03/oradata/CURSOxy/redo12.log' to group 2, '/u03/oradata/CURSOxy/redo13.log' to group 3;Base de datos modificada.

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/home/u04/oradata/CURSOxy/redo01.log/home/u04/oradata/CURSOxy/redo02.log/home/u04/oradata/CURSOxy/redo03.log/u03/oradata/CURSOxy/redo11.log/u03/oradata/CURSOxy/redo12.log/u03/oradata/CURSOxy/redo13.log6 filas seleccionadas.

SQL> select GROUP#,MEMBERS,STATUS from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 1 2 INACTIVE 2 2 INACTIVE 3 2 CURRENT

4.4. Añade un grupo más (grupo 4), con dos miembros de 1M: /u03/oradata/$ORACLE_SID/redo04.log y /u04/oradata/$ORACLE_SID/redo14.log. Añade 2 grupos más (grupo 5 y 6), con las mismas características.

Tenemos ficheros redolog de 512Kb, y vamos a crearlos de 1M, para poder alargar el tiempo entre checkpoints. Para cada nuevo grupo crearemos 2 miembros en discos físicos distintos.



SQL> alter database add logfile group 4 ('/u03/oradata/CURSOxy/redo04.log', '/u04/oradata/CURSOxy/redo14.log') size 1M;Base de datos modificada.

SQL> alter database add logfile group 5('/u03/oradata/CURSOxy/redo05.log', '/u04/oradata/CURSOxy/redo15.log') size 1M;Base de datos modificada.

SQL> alter database add logfile group 6('/u03/oradata/CURSOxy/redo06.log', '/u04/oradata/CURSOxy/redo16.log') size 1M;Base de datos modificada.

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/home/u04/oradata/CURSOxy/redo01.log/home/u04/oradata/CURSOxy/redo02.log/home/u04/oradata/CURSOxy/redo03.log/u03/oradata/CURSOxy/redo11.log/u03/oradata/CURSOxy/redo12.log/u03/oradata/CURSOxy/redo13.log/u03/oradata/CURSOxy/redo04.log/u04/oradata/CURSOxy/redo14.log/u03/oradata/CURSOxy/redo05.log/u04/oradata/CURSOxy/redo15.log/u03/oradata/CURSOxy/redo06.log/u04/oradata/CURSOxy/redo16.log12 filas seleccionadas.

SQL> select GROUP#,MEMBERS,STATUS from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 1 2 INACTIVE 2 2 INACTIVE 3 2 CURRENT 4 2 UNUSED 5 2 UNUSED 6 2 UNUSED6 filas seleccionadas.

Finalmente haremos seis “log switch” para que se usen los nuevos redolog que hemos creado:SQL> alter system switch logfile;System altered.

Repetirlo seis veces.

4.5. Elimina los miembros del grupo 1, de uno en uno. ¿Qué ocurre al eliminar el último?. Borrar los grupos 1, 2 y 3. ¡¡¡ Ojo con borrar el redo log activo !!!

Vamos a ver cómo borrar miembros de redo, y también grupos redolog. No podemos borrar el redo log activo (current).



Si intentamos borrar el 1er miembro de un grupo, no podremos hacerlo (aunque tenga más miembros). Los borrados son siempre a nivel de BD, no se borran los ficheros a nivel del SO (habría que borrarlos una vez que hayamos hecho el borrado a nivel de BD ¡¡¡ ojo con esta operación, y borremos un redolog de la BD !!!).

SQL> alter database drop logfile member '/home/u04/oradata/CURSOxy/redo01.log';ERROR en línea 1:ORA-00362: se necesita un miembro para formar un logfile valido en el grupo 1ORA-01517: miembro log: '/u04/oradata/CURSOxy/redo01.log'

SQL> alter database drop logfile member '/u03/oradata/CURSOxy/redo11.log';Base de datos modificada.

Vamos a borrar los 3 grupos que tienen miembros de 512Kb; para quedarnos sólo con los nuevos de

1M.

SQL> alter database drop logfile group 1;Base de datos modificada.

SQL> alter database drop logfile group 2;Base de datos modificada.

SQL> alter database drop logfile group 3;ERROR en línea 1:ORA-01623: log 3 is current log for thread 1 - cannot dropORA-00312: online log 3 thread 1: '/home/u04/oradata/CURSOxy/redo03.log'ORA-00312: online log 3 thread 1: '/u03/oradata/CURSOxy/redo13.log'

SQL> select GROUP#,MEMBERS,STATUS from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 3 2 CURRENT 4 2 INACTIVE 5 2 INACTIVE 6 2 INACTIVE

SQL> alter system switch logfile;Sistema modificado.

SQL> alter database drop logfile group 3;ERROR at line 1:ORA-01624: log 3 needed for crash recovery of thread 1ORA-00312: online log 3 thread 1: '/home/u04/oradata/CURSOxy/redo03.log'ORA-00312: online log 3 thread 1: '/u03/oradata/CURSOxy/redo13.log'

SQL> select GROUP#,MEMBERS,STATUS from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 3 2 ACTIVE 4 2 CURRENT 5 2 INACTIVE 6 2 INACTIVESQL> alter system switch logfile;System altered.

SQL> select GROUP#,MEMBERS,STATUS from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 3 2 INACTIVE



4 2 INACTIVE 5 2 CURRENT 6 2 INACTIVE

SQL> alter database drop logfile group 3;Database altered.

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u03/oradata/CURSOxy/redo04.log/u04/oradata/CURSOxy/redo14.log/u03/oradata/CURSOxy/redo05.log/u04/oradata/CURSOxy/redo15.log/u03/oradata/CURSOxy/redo06.log/u04/oradata/CURSOxy/redo16.log6 filas seleccionadas.

4.6. Cambiar el nombre de los miembros de redo de los grupos 4, 5 y 6; a redo1a.log, redo1b.log, redo2a.log, redo2b.log, redo3a.log, redo3b.log.

¡¡¡ OJO, la BD debe estar sólo montada !!! por tanto hay que cerrar la BD y después sólo montarla. Despues se cambia el nombre a los ficheros (desde el S.O.); y entonces cambiamos el nombre a los redolog (desde la BD). Si intentamos cambiar el nombre en la BD, antes de cambiarlo desde el SO, cascará. Finalmente abrimos la BD y veremos los nuevos nombres en V$LOGFILE.


SQL> startup mountInstancia de ORACLE arrancada.Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesBase de datos montada.

SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u03/oradata/CURSOxy/redo04.log/u04/oradata/CURSOxy/redo14.log/u03/oradata/CURSOxy/redo05.log/u04/oradata/CURSOxy/redo15.log/u03/oradata/CURSOxy/redo06.log/u04/oradata/CURSOxy/redo16.log6 filas seleccionadas.

SQL> alter database rename file '/u03/oradata/CURSOxy/redo04.log' to'/u03/oradata/CURSOxy/redo1a.log';ORA-01512: error renaming log file /u03/oradata/CURSOxy/redo04.log - new file/u03/oradata/CURSOxy/redo1a.log not found



Linux Error: 2: No such file or directory

SQL> !mv /u03/oradata/CURSOxy/redo04.log /u03/oradata/CURSOxy/redo1a.log

SQL> alter database rename file '/u03/oradata/CURSOxy/redo04.log' to'/u03/oradata/CURSOxy/redo1a.log';Base de datos modificada.

SQL> !mv /u04/oradata/CURSOxy/redo14.log /u04/oradata/CURSOxy/redo1b.log

SQL> alter database rename file '/u04/oradata/CURSOxy/redo14.log' to'/u04/oradata/CURSOxy/redo1b.log';Base de datos modificada.









SQL> select group#,rpad(member,40) member from v$logfile; GROUP# MEMBER---------- ---------------------------------------- 4 /u03/oradata/CURSOxy/redo1a.log 4 /u04/oradata/CURSOxy/redo1b.log 5 /u03/oradata/CURSOxy/redo2a.log 5 /u04/oradata/CURSOxy/redo2b.log 6 /u03/oradata/CURSOxy/redo3a.log 6 /u04/oradata/CURSOxy/redo3b.log6 filas seleccionadas.

SQL> alter database open;Base de datos modificada.

SQL> select GROUP#,MEMBERS,STATUS from v$log; GROUP# MEMBERS STATUS---------- ---------- ---------------- 4 2 INACTIVE 5 2 CURRENT 6 2 INACTIVE



PRACTICAS TEMA 5.

TABLESPACES.

o Consultar los tablespaces que componen la BD. Comprobar los ficheros que tiene cada uno de ellos. ¿Cuáles son 'locales' y cuáles no?

o Crea el tablespace DATACURSOxx, NO manejado localmente, con el fichero /u02/oradata/CURSOxx/datacursoxx01.dbf, con un tamaño de 1M. Crea 3 tablas (TABLA01, TABLA02, TABLA03) de 256K sobre dicho tablespace. Borra la tabla TABLA02 y crea una tabla TABLA04 de 352K. ¿Qué ocurre y por qué?. Borra el tablespace DATACURSOxx y créalo de nuevo, manejado localmente. Vuelve a crear las tablas y repite el borrado de TABLA02; y la creación de TABLA04. ¿Qué ocurre esta vez y por qué?

o Pon el tablespace DATACURSOxx en modo READ-ONLY. Inserta una fila en alguna de sus tablas, ¿qué ocurre y por qué?. Borra la tabla TABLA01, ¿por qué se puede borrar?. Deja el tablespace DATACURSOxx en modo READ-WRITE. Repite el insert sobre TABLA02.

o Crea una tabla TABLA04 de 256K en el tablespace DATACURSOxx, ¿qué ocurre y por qué?. Activa el autoextend de su fichero, ajustando next 256K y maxsize 2M. Vuelve a crear la tabla TABLA04.

o Crea el tablespace INDCURSOxx de 1M con el fichero '/u02/oradata/CURSOxx/indcursoxx01.dbf'. Muévelo al directorio '/u03/oradata/CURSOxx'.

o Redimensionar el tablespace DATACURSOxx a 1M ¿qué ocurre y por qué?. Redimensionar el tablespace INDCURSOxx a 512K, y añadirle otro fichero de 512K.

o Crear un tablespace de “undo” UNDO_CURSOxx. Convertirlo en el tablespace de “undo” activo. Crear una tabla TABLA05 en el nuevo tablespace, ¿qué ocurre? Consultar la vista V$UNDOSTAT.

o Crear un tablespace temporal TEMP_CURSOxx de 1M. ¿Qué ocurre al ponerlo read-only? ¿y al moverlo? Haz que sea el tablespace temporal por defecto de la base de datos ¿que pasa al ponerlo offline? ¿y si lo borramos?



5.1. Consultar los tablespaces que componen la BD. Comprobar los ficheros que tiene cada uno. ¿Cuales son 'locales' y cuales no?

En el DD disponemos de vistas para consultar información sobre tablespaces (DBA_TABLESPACES, V$TABLESPACE) y los ficheros que los componen (DBA_DATA_FILES, DBA_TEMP_FILES, V$DATAFILE, V$TEMPFILE)

SQL> select tablespace_name,extent_management from dba_tablespaces;TABLESPACE_NAME EXTENT_MAN------------------------------ ----------SYSTEM DICTIONARYTOOLS LOCALUNDO_RBS1 LOCALTEMP LOCALUSERS LOCAL

SQL> SELECT rpad(TABLESPACE_NAME,10) tablespace,rpad(FILE_NAME,40) fichero, to_char(BYTES/1024/1024,'999.99') MB, to_char(increment_by*8/1024,'99.99') nextmb, to_char(MAXBYTES/1024/1024,'9999.99') maxmb FROM DBA_DATA_FILES;TABLESPACE FICHERO MB NEXTMB MAXMB---------- ---------------------------------------- ------- ------ --------SYSTEM /home/u02/oradata/CURSOxy/system01.dbf 260.00 40.00 8192.00TOOLS /home/u02/oradata/CURSOxy/tools01.dbf 4.00 1.25 8192.00UNDO_RBS1 /home/u03/oradata/CURSOxy/rbs01.dbf 4.00 .00 .00USERS /home/u02/oradata/CURSOxy/users01.dbf 4.00 5.00 8192.00

SQL> select rpad(name,40) name,CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'dd/mm/yyyy hh24:mi') CHECKPOINT_TIMEfrom v$datafile;

NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME---------------------------------------- ------------------ ----------------/home/u02/oradata/CURSOxy/system01.dbf 1179986 08/11/2004 13:06/home/u02/oradata/CURSOxy/tools01.dbf 1179986 08/11/2004 13:06/home/u03/oradata/CURSOxy/rbs01.dbf 1179986 08/11/2004 13:06/home/u02/oradata/CURSOxy/users01.dbf 1179986 08/11/2004 13:06

5.2. Crea el tablespace DATACURSOxx, NO manejado localmente, con el fichero /u02/oradata/datacursoxx01.dbf, con un tamaño de 1M. Crea 3 tablas (TABLA01, TABLA02, TABLA03) de 256K sobre dicho tablespace. Borra las tabla TABLA02 y crea una tabla TABLA04 de 352K. ¿Qué ocurre y por qué?. Borra el tablespace DATACURSOxx y créalo de nuevo, manejado localmente. Vuelve a crear las tablas y repite el borrado de TABLA02; y la creación de TABLA04. ¿Qué ocurre esta vez y por qué?

Los tablespaces 'manejados localmente' gestionan mejor los huecos libres, eliminando la fragmentación que, en el caso de tablespaces tradicionales, provoca el borrado de objetos. El espacio libre inicial en los 'tablespaces locales' es algo menor que en los 'tradicionales', ya que se pierde espacio para el bitmap.



SQL> Create tablespace DATACURSOxydatafile '/u02/oradata/CURSOxy/datacurso0101.dbf' size 1Mextent management dictionary;

Tablespace creado.

SQL> select tablespace_name,extent_management from dba_tablespaces where tablespace_name='DATACURSOxy';TABLESPACE_NAME EXTENT_MAN------------------------------ ----------DATACURSOxy DICTIONARY

SQL> create table TABLA01 (C1 VARCHAR2(4000))tablespace DATACURSOxy storage (initial 256K minextents 1);

Tabla creada.


Tabla creada.


Tabla creada.

SQL> drop table tabla02;Tabla borrada.

SQL> select bytes from dba_free_space where tablespace_name='DATACURSOxy'; BYTES---------- 247808 266240


ORA-01658: no se ha podido crear extensión INITIAL para el segmento en eltablespace DATACURSOxy

No se puede crear la tabla porque el tablespace NO es 'local' y no hay una extensión libre mayor o igual a 352Kb. Vamos a borrar el tablespace y a crearlo de nuevo 'local'. Veremos como siendo 'local' no tendremos problemas para crear la tabla de 352Kb.

SQL> ALTER TABLESPACE DATACURSOxy OFFLINE;Tablespace modificado.

SQL> DROP TABLESPACE DATACURSOxy;ORA-01549: el tablespace no está vacío, use la opción INCLUDING CONTENTS

SQL> DROP TABLESPACE DATACURSOxy INCLUDING CONTENTS;Tablespace borrado.

SQL> Create tablespace DATACURSOxydatafile '/u02/oradata/CURSOxy/datacurso0101.dbf' size 1M

reuseextent management local uniform size 128K;

Tablespace creado.



SQL> select tablespace_name,extent_management from dba_tablespaces where tablespace_name='DATACURSOxy';TABLESPACE_NAME EXTENT_MAN------------------------------ ----------DATACURSOxy LOCAL


Tabla creada.


Tabla creada.


Tabla creada.

SQL> drop table tabla02;Tabla borrada.

SQL> select bytes from dba_free_space where tablespace_name='DATACURSOxy'; BYTES---------- 262144 131072

SQL> create table TABLA02 (C1 VARCHAR2(4000)) tablespace DATACURSOxy storage (initial 352K minextents 1);Tabla creada.

SQL> select bytes from dba_free_space where tablespace_name='DATACURSOxy';ninguna fila seleccionada

Como se observa, siendo el tablespace local, se aprovechan los huecos que se generan con el borrado de tablas. La cuestión clave, en este caso, es encontrar un tamaño adecuado para “uniform size”. Una posibilidad es tener varios tablespaces: pequeño, mediano y grande con uniform size, por ejemplo, de 8K, 256K y 8M.

5.3. Pon el tablespace DATACURSOxx en modo READ-ONLY. Inserta una fila en alguna de sus tablas, ¿qué ocurre?. Borra la tabla TABLA01, ¿por qué se puede borrar?. Deja el tablespace DATACURSOxx en modo READ-WRITE. Repite el insert, ahora sobre TABLA02.

En un tablespace read only no se pueden actualizar datos (insert, update, delete), pero sí se pueden borrar objetos (drop table), ya que el borrado de objetos sólo afecta al DD (y no al tablespace).

SQL> alter tablespace DATACURSOxy read only;Tablespace modificado.

SQL> insert into tabla01 values ('PRIMERA FILA');ORA-00372: el fichero 6 no puede ser modificado en este momentoORA-01110: fichero de datos 6: '/u02/oradata/CURSOxy/datacurso0101.dbf'



SQL> DROP TABLE TABLA01;Tabla borrada.

SQL> alter tablespace DATACURSOxy read write;Tablespace modificado.

SQL> insert into tabla02 values ('PRIMERA FILA');1 fila creada.

SQL> commit;Validación terminada.

5.4. Crea una tabla TABLA04 en el tablespace DATACURSOxx, ¿qué ocurre y por qué?. Activa el autoextend de su fichero, ajustando next 256K y maxsize 2M. Vuelve a crear la tabla TABLA06.

Cuando el tablespace se llena no será posible crear más objetos, a no ser que esté activado el 'autoextend', de forma que permita la ampliación del tablespace lo suficiente para albergar el nuevo objeto.

SQL> select bytes from dba_free_space where tablespace_name='DATACURSOxy';no rows selected


ERROR en línea 1:ORA-01658: no se ha podido crear extensión INITIAL para el segmento en eltablespace DATACURSOxy

SQL> alter database datafile '/u02/oradata/CURSOxy/datacurso0101.dbf'autoextend on next 256K maxsize 2M;

Base de datos modificada.


Tabla creada.

SQL> select bytes from dba_free_space where tablespace_name='DATACURSOxy';ninguna fila seleccionada

SQL> SELECT rpad(segment_name,20) segmento, segment_type tipo, bytes fromdba_extents

where tablespace_name='DATACURSOxy';SEGMENTO TIPO BYTES-------------------- ------------------ ----------TABLA04 TABLE 131072TABLA04 TABLE 131072TABLA03 TABLE 131072TABLA03 TABLE 131072TABLA02 TABLE 131072TABLA02 TABLE 131072TABLA02 TABLE 1310725.33 TEMPORARY 1310725.33 TEMPORARY 1310729 filas seleccionadas.



5.5. Crea el tablespace INDCURSOxx de 1M con el fichero '/u02/oradata/CURSOxx/indcursoxx01.dbf'. Muévelo al directorio '/u03/oradata/CURSOxx'.

Para mover o renombrar un fichero de un tablespace, éste último debe estar OFFLINE.

SQL> Create tablespace INDCURSOxydatafile '/u02/oradata/CURSOxy/indcurso0101.dbf' size 1M

reuseextent management local uniform size 64K;

Tablespace creado.

SQL> alter tablespace indcurso01 offline;Tablespace modificado.

SQL> !mv /u02/oradata/CURSOxy/indcurso0101.dbf/u03/oradata/CURSOxy/indcurso0101.dbf

SQL> alter tablespace indcurso01 rename datafile '/u02/oradata/CURSOxy/indcurso0101.dbf' to '/u03/oradata/CURSOxy/indcurso0101.dbf';

Tablespace modificado.

SQL> alter tablespace indcurso01 online;Tablespace modificado.

SQL> select file_name from dba_data_files;FILE_NAME--------------------------------------------------------------------------------/home/u02/oradata/CURSOxy/system01.dbf/home/u02/oradata/CURSOxy/tools01.dbf/home/u03/oradata/CURSOxy/rbs01.dbf/home/u02/oradata/CURSOxy/users01.dbf/u02/oradata/CURSOxy/datacurso0101.dbf/u03/oradata/CURSOxy/indcurso0101.dbf6 rows selected.

5.6. Redimensionar el tablespace DATACURSOxx a 1M ¿qué ocurre y por qué? Redimensionar el tablespace INDCURSOxx a 512K, y añadirle otro fichero de 512K.

Se puede redimensionar un fichero reduciendo su tamaño sólo si el espacio recortado está libre. En el caso de DATACURSOxx no es posible reducirlo a 1M porque tiene espacio ocupado en el espacio recortado. Sin embargo, INDCURSOxx sí que puede ser reducido a 512K porque está vacío.

SQL> alter database datafile '/u02/oradata/CURSOxy/datacurso0101.dbf' resize 1M;

ORA-03297: file contains used data beyond requested RESIZE value

SQL> alter database datafile '/u03/oradata/CURSOxy/indcurso0101.dbf' resize512K;



Database altered.

SQL> alter tablespace INDCURSOxy add datafile'/u03/oradata/CURSOxy/indcurso0102.dbf' size 512K;

Tablespace altered.

SQL> select rpad(file_name,50),bytes from dba_data_files wheretablespace_name='INDCURSOxy';

RPAD(FILE_NAME,50) BYTES

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

/u03/oradata/CURSOxy/indcurso0101.dbf 524288

/u03/oradata/CURSOxy/indcurso0102.dbf 524288

5.7. Crear un tablespace de “undo” UNDO_CURSOxx. Convertirlo en el tablespace de “undo” activo. Crear una tabla TABLA05 en el nuevo tablespace, ¿qué ocurre? Consultar la vista V$UNDOSTAT.

Podemos tener varios tablespaces de “undo”, pero sólo uno de ellos estará activo en un momento dado (el indicado en el parámetro undo_tablespace). No se pueden crear objetos sobre un tablespace de “undo” (como puede ser una tabla).

Al cambiar de tablespace “undo” activo (con undo_tablespace), los segmentos de rollback que contiene el nuevo tablespace pasan a estar “online”, mientras que los del tablespace anterior se ponen offline.

SQL> create undo tablespace undo_curso01 datafile'/u03/oradata/CURSOxy/undo_curso0101.dbf' size 1M;

Tablespace created.

SQL> select * from dba_tablespaces where tablespace_name='UNDO_CURSOxy';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

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

MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN

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

ALLOCATIO PLU SEGMEN

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

UNDO_CURSOxy 2048 65536 1

2147483645 65536 ONLINE UNDO LOGGING NO LOCAL

SYSTEM NO MANUAL

SQL> CREATE TABLE TABLA05 (C1 VARCHAR2(2)) TABLESPACE UNDO_CURSOxy;

ORA-30022: Cannot create segments in undo tablespace

SQL> SHOW PARAMETER UNDO_TABLESPACE

NAME TYPE VALUE



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

undo_tablespace string UNDO_RBS1

SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME TABLESPACE_NAME STATUS

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

SYSTEM SYSTEM ONLINE

_SYSSMU1$ UNDO_RBS1 ONLINE



_SYSSMU4$ UNDO_CURSOxy OFFLINE





9 rows selected.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDO_CURSOxy;

System altered.

SQL> SELECT SEGMENT_NAME,TABLESPACE_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME TABLESPACE_NAME STATUS

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

SYSTEM SYSTEM ONLINE

_SYSSMU1$ UNDO_RBS1 OFFLINE



_SYSSMU4$ UNDO_CURSOxy ONLINE





9 rows selected.

SQL> select to_char(BEGIN_TIME,'dd/mm/yyyy hh24:mi') BEGIN_TIME,b.nameTABLESPACE,

UNDOBLKS,TXNCOUNT

from V$UNDOSTAT a, v$tablespace b

WHERE A.UNDOTSN=B.TS#

ORDER BY BEGIN_TIME;

BEGIN_TIME NAME UNDOBLKS TXNCOUNT

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

...



21/02/2005 13:38 UNDO_RBS1 8 1096

21/02/2005 13:48 UNDO_CURSOxy 2 1108

21/02/2005 13:58 UNDO_CURSOxy 10 1130

21/02/2005 14:08 UNDO_CURSOxy 0 0

En la vista V$UNDOSTAT se guarda información valiosa cuando se usa “undo automático”, pues podemos ver estadísticas (cada 10 minutos) del tablespace de undo usado (UNDOTSN), bloques de rollback consumidos (UNDOBLKS) y el nº total de transacciones de dicho periodo (TXNCOUNT).

5.8. Crear un tablespace temporal TEMP_CURSOxx de 1M. ¿Qué ocurre al ponerlo read-only? Haz que sea el tablespace temporal por defecto de la base de datos ¿que pasa al ponerlo offline? ¿y si lo borramos?

Un tablespace temporal no puede ponerse en modo “read only”, y tampoco se puede poner offline.

El tablespace temporal por defecto de la BD, además, no puede ser borrado.

SQL> show parameter sort_area_size

NAME TYPE VALUE

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

sort_area_size integer 65536

SQL> create temporary tablespace temp_curso01 tempfile'/u03/oradata/CURSOxy/temp_curso0101.dbf' size 1M extent management localuniform size 64K

Tablespace created.

SQL> alter tablespace temp_curso01 read only;

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

SQL> alter tablespace temp_curso01 offline;

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

SQL> alter database default temporary tablespace temp_curso01;

Database altered.

SQL> drop tablespace temp_curso01;

ORA-12906: cannot drop default temporary tablespace

SQL> alter database default temporary tablespace temp;

Database altered.



PRACTICAS TEMA 6.

ESPACIO DE “UNDO”.

1. Consultar el fichero de inicialización de la base de datos y averiguar cuales de los parámetros de “undo” están definidos y su valor actual.

/home/oracle (CURSO01)> cd $ADMIN/pfile

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> grep undo initCURSO01.ora

undo_management = autoundo_tablespace = undo_rbs1

2. Averiguar cuantos espacios de “undo” están definidos en la base de datos (nombre, características de almacenamiento, ...).

SQL> select tablespace_name, initial_extent, next_extent, min_extents, max_extents, status, contents from dba_tablespaces where contents='UNDO'

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS STATUS CONTENTS------------------------------ -------------- ----------- ----------- ----------- --------- ---------UNDO_RBS1 65536 1 2147483645 ONLINE UNDO

3. ¿En qué sistema/s de ficheros están definidos los anteriores espacios de almacenamiento?, ¿cuál es su tamaño?.

SQL> select tablespace_name, file_name, file_id, bytes 2 from dba_data_files where tablespace_name='UNDO_RBS1';

TABLESPACE_NAME------------------------------FILE_NAME----------------------------------------------------------- FILE_ID BYTES---------- ----------UNDO_RBS1/home/u03/oradata/CURSO01/rbs01.dbf



3 4194304

4. Consultar el valor actualmente activo de los parámetros de “undo” en la base de datos.

SQL> show parameters undo

NAME TYPE VALUE------------------------------------ ----------- ----------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string undo_rbs1

5. Intentar asignar el segmento de “rollback” r01 a la transacción actual. ¿Se genera error?, en caso afirmativo ¿cuál?. ¿cuál es el valor de “undo_suppress_errors”?.

SQL> set transaction use rollback segment r01;set transaction use rollback segment r01*ERROR at line 1:ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

6. Modificar dinámicamente el valor del parámetro “undo_suppress_errors” y volver a realizar el ejercicio anterior. ¿cuál es el resultado?.

SQL> alter system set undo_suppress_errors =TRUE;

System altered.

SQL> set transaction use rollback segment r01;

Transaction set.

7. Repetir el ejercicio número 4 y observar el cambio de valor de los parámetros.


NAME TYPE VALUE------------------------------------ ---------- ------------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean TRUEundo_tablespace string undo_rbs1



8. Crear un segundo espacio de almacenamiento de “undo” con las siguientes características:

Nombre ... UNDO02Localización ... /home/u03/oradata/CURSOxx/undo02.dbfTamaño ... 1MAutoextensible ... 2M, hasta 3M

Comprobar su creación.

SQL> create undo tablespace undo02 2 datafile '/home/u03/oradata/CURSO01/undo02.dbf' 3 size 1M 4 autoextend on next 2M maxsize 3M;

Tablespace created.

SQL> select tablespace_name, file_name 2* from dba_data_files where tablespace_name like 'UNDO%'

UNDO_RBS1/home/u03/oradata/CURSO01/rbs01.dbf

UNDO02/home/u03/oradata/CURSO01/undo02.dbf

9. Crear un espacio de almacenamiento de “undo” con las siguientes características:

Nombre ... UNDO03Localización ... /home/u03/oradata/CURSOxx/undo03.dbfTamaño ... 1M

Definir la cláusula “storage” por defecto: initial 100k, next 100k y maxextents 50.

SQL> create undo tablespace undo032 datafile '/home/u03/oradata/CURSO01/undo03.dbf'3 size 1M4* default storage (initial 100k next 100k maxextents 50)default storage (initial 100k next 100k maxextents 50)*ERROR at line 4:ORA-30024: Invalid specification for CREATE UNDO TABLESPACE

10. Modificar el espacio de “undo” por defecto, de forma que sea a partir de este momento UNDO02. Comprobarlo antes y despues de hacerlo.




undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean TRUEundo_tablespace string undo_rbs1

SQL> alter system set undo_tablespace=undo02;

System altered.


undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean TRUEundo_tablespace string UNDO02

11. Modificar el fichero de inicialización comentando la línea correspondiente a la variable “undo_tablespace”. ¿cuál es el espacio de “undo” que se habilita? (recuerde que al cerrar la base de datos era UNDO02, ver ejercicio anterior).

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 18 14:40:33 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba;Connected.

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> SQL> startupORACLE instance started.

Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.



Database opened.

SQL> connect systemEnter password: Connected. SQL> show parameters undo

NAME TYPE VALUE------------------------------------ ----------- ----------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string undo_rbs1

12. Modificar el fichero de inicialización y asignar a la variable “undo_tablespace” el valor “UNDO02”. Parar y arrancar la base de datos.

# Gestion Automatica de Rollback (UNDO). undo_management = autoundo_tablespace = UNDO02

"initCURSO01.ora" 125L, 5655C escritos


SQL*Plus: Release 9.2.0.1.0 - Production on Thu Nov 18 14:49:42 2004


SQL> connect /as sysdba;Connected.

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.

SQL> startupORACLE instance started.

Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.



Database opened.

SQL> connect systemEnter password: Connected.SQL> show parameters undo

NAME TYPE VALUE------------------------------------ --------- ---------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string UNDO02

13. Borrar el espacio de “undo” UNDO02.


NAME TYPE VALUE------------------------------------ ---------- ---------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string UNDO02

SQL> drop tablespace undo02;drop tablespace undo02*ERROR at line 1:ORA-30013: undo tablespace 'UNDO02' is currently in use

14. Asignar “UNDO_RBS1” como nuevo espacio de “undo”. Borrar el espacio “UNDO02”.

SQL> alter system set undo_tablespace='UNDO_RBS1';System altered.


NAME TYPE VALUE------------------------------------ ----------- -----------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string UNDO_RBS1



SQL> drop tablespace undo02;

Tablespace dropped.

15. Parar y arrancar la base de datos (debe recordarse que en el fichero initCURSOxx.ora el parámetro “undo_tablespace” tiene el valor “UNDO02”).




Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forced

16. Comentar el parámetro “undo_tablespace” en el fichero de inicialización, y arrancar la base de datos. ¿Qué espacio de “undo” tiene asignado?.

# Gestion Automaticaundo_management = auto#undo_tablespace = UNDO02


SQL*Plus: Release 9.2.0.1.0 - Production on Fri Nov 19 09:32:41 2004


SQL> connect / as sysdba;Connected to an idle instance.


Total System Global Area 34148352 bytes



Fixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.Database opened.SQL> show parameters undo

NAME TYPE VALUE------------------------------------ --------- ----------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string UNDO_RBS1

17. Comentar todos los parámetros correspondientes a “undo” en el fichero de inicialización, parar y arrancar la base de datos. Comprobar los valores de “undo”, ¿qué observa?.

# Gestion Automatica#undo_management = auto#undo_tablespace = UNDO02 /u01/app/oracle/admin/CURSO01/pfile (CURSO01)> sqlplus /nolog



SQL> connect /as sysdba;Connected.



Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.Database opened.




NAME TYPE VALUE------------------------------------ ----------- --------undo_management string MANUALundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string

18. Borrar el espacio de “undo” UNDO_RBS1. Habilitar de nuevo la gestión automática de “undo” y, parar y arrancar la base de datos. Comprobar los valores de “undo”, ¿qué espacio de “undo” se usa?.

SQL> c.form.from. 1* select * from dba_data_files where tablespace_name='UNDO_RBS1'SQL> r 1* select * from dba_data_files where tablespace_name='UNDO_RBS1'

FILE_NAME--------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS---------- ------------------------------ ---------- ---------- ---------RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS------------ --- ---------- ---------- ------------ ---------- -----------/home/u03/oradata/CURSO01/rbs01.dbf 3 UNDO_RBS1 4194304 2048 AVAILABLE 3 NO 0 0 0 4128768 2016

SQL> drop tablespace UNDO_RBS1;

Tablespace dropped.





# Gestion Automaticaundo_management = auto#undo_tablespace = UNDO02




SQL> connect / as sysdba;Connected to an idle instance.SQL> startupORACLE instance started.



NAME TYPE VALUE------------------------------------ --------------------undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string

19. En el ejercicio anterior, ¿se genera algún mensaje de error en el fichero de alertas?.

Starting up ORACLE RDBMS Version: 9.2.0.1.0.System parameters with non-default values: processes = 20 shared_pool_size = 8388608 sga_max_size = 34148352

...

Fri Nov 19 09:49:12 2004ALTER DATABASE MOUNTFri Nov 19 09:49:17 2004



Successful mount of redo thread 1, with mount id 2097158872.Fri Nov 19 09:49:17 2004Database mounted in Exclusive Mode.Completed: ALTER DATABASE MOUNTFri Nov 19 09:49:17 2004ALTER DATABASE OPENFri Nov 19 09:49:17 2004Thread 1 opened at log sequence 578 Current log# 5 seq# 578 mem# 0: /u03/oradata/CURSO01/redo2a.log Current log# 5 seq# 578 mem# 1: /u04/oradata/CURSO01/redo2b.logSuccessful open of redo thread 1.Fri Nov 19 09:49:17 2004MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setFri Nov 19 09:49:17 2004SMON: enabling cache recoverySMON: enabling tx recovery***Warning - Executing transaction without active Undo TablespaceFri Nov 19 09:49:17 2004Database Characterset is WE8ISO8859P15replication_dependency_tracking turned off (no async multimaster replication found)***Warning - Executing transaction without active Undo TablespaceCompleted: ALTER DATABASE OPEN

20. Crear un espacio llamado PRUEBA, que no sea de “undo”, e intentar asignarlo como espacio de “undo”. ¿Qué sucede?.

Nombre ... PRUEBALocalización ... /home/u03/oradata/CURSOxx/prueba01.dbf

Tamaño ... 1M

SQL> create tablespace prueba datafile '/home/u03/oradata/CURSO01/prueba01.dbf' size 1M

Tablespace created.

SQL> alter system set undo_tablespace=PRUEBA; alter system set undo_tablespace=PRUEBA*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-30012: undo tablespace 'PRUEBA' does not exist or of wrong type

21. Crear de nuevo el espacio de “undo” UNDO_RBS1 y asignarlo como tal.



Nombre ... UNDO_RBS1Localización ... /home/u03/oradata/CURSOxx/rbs01.dbfTamaño ... 1MSiguiente extensión ... 500kTamaño máximo ... 2M

SQL> create undo tablespace undo_rbs1 datafile '/home/u03/oradata/CURSO01/rbs01.dbf' 2* size 1M autoextend on next 500K maxsize 2Mcreate undo tablespace undo_rbs1 datafile '/home/u03/oradata/CURSO01/rbs01.dbf'*ERROR at line 1:ORA-01119: error in creating database file'/home/u03/oradata/CURSO01/rbs01.dbf'ORA-27038: skgfrcre: file exists

Nota. Cuando se borró el espacio UNDO_RBS1, no se eliminó el fichero correspondiente y se genera un error. Es necesario previamente borrar o renombrar “rbs01.dbf”; se elige renombrar el fichero por ser una operación más segura en este caso. ¡Cuidado con esta operación!.

SQL> !

/u01/app/oracle/admin/CURSO01/bdump (CURSO01)> cd /home/u03/oradata/CURSO01

/home/u03/oradata/CURSO01 (CURSO01)> mv rbs01.dbf rbs01.dbf.antiguo

/home/u03/oradata/CURSO01 (CURSO01)> exit

SQL>

SQL> create undo tablespace undo_rbs1 datafile '/home/u03/oradata/CURSO01/rbs01.dbf' 2 size 1M autoextend on next 500K maxsize 2M;

Tablespace created.

SQL> alter system set undo_tablespace=undo_rbs1

System altered.


NAME TYPE VALUE------------------------------------ ----------- ---------



undo_management string AUTOundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string UNDO_RBS1

22. Obtener la descripción completa de las vistas siguientes (consultar también la documentación en línea).

" DBA_UNDO_EXTENTS" V$UNDOSTAT" V$ROLLSTAT

SQL> desc dba_undo_extents

Name Null? Type ----------------------------------------- -------- -------------- OWNER CHAR(3) SEGMENT_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30) EXTENT_ID NUMBER FILE_ID NOT NULL NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER RELATIVE_FNO NUMBER COMMIT_JTIME NUMBER COMMIT_WTIME VARCHAR2(20) STATUS VARCHAR2(9)

Especialmente interesantes son las columnas “owner” -propietario del espacio de “undo”-, tablespace_name -nombre del espacio de “undo”-, y “status” -estado de la transacción en la extension: Active, contiene transacciónes activas, expired, el segmento no se utiliza, o unexpired, no hay transacciónes activas pero contiene datos que pueden ser requeridos por otras transacciónes-.

Según la nota 231509.1, las columnas “commit_jtime” y “commit_wtime” pierden su valor en la vista dba_undo_extents desde la version 9.2.0

SQL> desc v$undostat Name Null? Type-------------------------------------------------------------- BEGIN_TIME DATE END_TIME DATE UNDOTSN NUMBER UNDOBLKS NUMBER TXNCOUNT NUMBER MAXQUERYLEN NUMBER



MAXCONCURRENCY NUMBER UNXPSTEALCNT NUMBER UNXPBLKRELCNT NUMBER UNXPBLKREUCNT NUMBER EXPSTEALCNT NUMBER EXPBLKRELCNT NUMBER EXPBLKREUCNT NUMBER SSOLDERRCNT NUMBER NOSPACEERRCNT NUMBER

Muestra datos estadísticos relativos a consumo de espacio de “undo”, longitud de las consultas ejecutadas, ... Se devuelven valores nulos si se trabaja en modo manual.

Cada fila de la vista corresponde a un intervalo de 10 minutos (en total 1008 filas, correspondientes a un periodo de siete dias).

SQL> desc v$rollstat Name Null? Type ----------------------------------------- ---------------------- USN NUMBER LATCH NUMBER EXTENTS NUMBER RSSIZE NUMBER WRITES NUMBER XACTS NUMBER GETS NUMBER WAITS NUMBER OPTSIZE NUMBER HWMSIZE NUMBER SHRINKS NUMBER WRAPS NUMBER EXTENDS NUMBER AVESHRINK NUMBER AVEACTIVE NUMBER STATUS VARCHAR2(15) CUREXT NUMBER CURBLK NUMBER

En modo automático refleja el comportamiento de los segmentos de “undo” en el espacio de “undo.

23. Habilitar el modo de gestión manual de “undo. Parar y arrancar la base de datos.

# Gestion Automaticaundo_management = manualundo_tablespace = UNDO_RBS1











NAME TYPE VALUE------------------------------------ ---------- ----------undo_management string MANUALundo_retention integer 900undo_suppress_errors boolean FALSEundo_tablespace string UNDO_RBS1

SQL> alter system set undo_tablespace=UNDO02;alter system set undo_tablespace=UNDO02*ERROR at line 1:ORA-02097: parameter cannot be modified because specified value is invalidORA-30014: operation only supported in Automatic Undo Management mode

24. Obtener la descripción completa de las vistas siguientes (consultar también la documentación en línea):

- DBA_SEGMENTS- DBA_ROLLBACK_SEGS- V$ROLLNAME- V$ROLLSTAT

SQL> desc dba_segments

Nombre ¿Nulo? Tipo ----------------------------------------- -------- ------------- OWNER VARCHAR2(30) SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30)



SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) HEADER_FILE NUMBER HEADER_BLOCK NUMBER BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER RELATIVE_FNO NUMBER BUFFER_POOL VARCHAR2(7)

SQL> desc dba_rollback_segs

Nombre ¿Nulo? Tipo ----------------------------------------- -------- ------------- SEGMENT_NAME NOT NULL VARCHAR2(30) OWNER VARCHAR2(6) TABLESPACE_NAME NOT NULL VARCHAR2(30) SEGMENT_ID NOT NULL NUMBER FILE_ID NOT NULL NUMBER BLOCK_ID NOT NULL NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NOT NULL NUMBER PCT_INCREASE NUMBER STATUS VARCHAR2(16) INSTANCE_NUM VARCHAR2(40) RELATIVE_FNO NOT NULL NUMBER

SQL> desc V$rollname

Nombre ¿Nulo? Tipo ----------------------------------------- -------- -------------USN NUMBER NAME NOT NULL VARCHAR2(30)

SQL> desc v$rollstat

Nombre ¿Nulo? Tipo ----------------------------------------- -------- ------------- USN NUMBER EXTENTS NUMBER RSSIZE NUMBER



WRITES NUMBER XACTS NUMBER GETS NUMBER WAITS NUMBER OPTSIZE NUMBER HWMSIZE NUMBER SHRINKS NUMBER WRAPS NUMBER EXTENDS NUMBER AVESHRINK NUMBER AVEACTIVE NUMBER STATUS VARCHAR2(15) CUREXT NUMBER CURBLK NUMBER

25. Averiguar cuantos espacios de “undo” dedicados a gestión automática existen en la base de datos y ponerlos fuera de línea (comprobar este punto).

SQL> select tablespace_name, contents, status from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME CONTENTS STATUS------------------------------ --------- ---------UNDO_RBS1 UNDO ONLINEUNDO02 UNDO ONLINE

SQL> alter tablespace undo_rbs1 offline;

Tablespace altered.

SQL> alter tablespace undo02 offline;

Tablespace altered.

SQL> select tablespace_name, contents, status from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME CONTENTS STATUS------------------------------ --------- ---------UNDO_RBS1 UNDO OFFLINEUNDO02 UNDO OFFLINE

26. Averiguar de cuántos segmentos de "rollback" dispone la base de datos y cuáles de ellos están activos.

SQL> select count(*) from dba_rollback_segs where status='ONLINE';



COUNT(*)---------- 1

SQL> select owner, SEGMENT_NAME, TABLESPACE_NAME,STATUS from dba_rollback_segs order by owner, segment_name, status

PUBLIC _SYSSMU1$ UNDO_RBS1OFFLINE




PUBLIC _SYSSMU5$ UNDO_RBS1

OFFLINE

PUBLIC _SYSSMU6$ UNDO02OFFLINE



SYS SYSTEM SYSTEMONLINE

SQL> SELECT segment_name, tablespace_name, bytes, blocks, extentsFROM sys.dba_segmentsWHERE segment_type = 'ROLLBACK';

SYSTEMSYSTEM 1482752 724 27

Realizar la consulta anterior para tipo de segmento igual a “UNDO”.

SQL> SELECT segment_name, tablespace_name, bytes, blocks, extents



FROM sys.dba_segmentsWHERE segment_type like '%UNDO'

27. Idem para el número de segmentos fuera de línea.

SQL> select count(*) from dba_rollback_segs where status='OFFLINE';

COUNT(*)---------- 8

28. Averiguar si existe un espacio de almacenamiento de nombre RBS, si no crearlo. En adelante se trabajará con este espacio de almacenamiento exclusivo para los segmentos de "rollback".

Nombre ... RBSLocalización ... /home/u03/oradata/CURSOxx/rbs_manual01.dbfTamaño ... 1MSiguiente extensión ... 500kTamaño máximo ... 2M

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME------------------------------SYSTEMTOOLSUNDO_RBS1TEMPUSERSDATACURSO01INDCURSO01UNDO02PRUEBA

9 rows selected.

SQL> select tablespace_name from dba_tablespaces where tablespace_name ='RBS';

no rows selected

SQL> create tablespace rbs 2 datafile '/home/u03/oradata/CURSO01/rbs_manual01.dbf' 3 size 1M 4 default storage (initial 100k next 100k minextents 2



maxextents unlimited) 5* extent management dictionary

Tablespace created.

29. Crear un segmento de "rollback" público, en el espacio de almacenamiento anterior, llamado RP01.

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP01 TABLESPACE RBS 2 storage (initial 100k next 100k minextents 2 maxextents 10 optimal 500K);CREATE PUBLIC ROLLBACK SEGMENT RP01 TABLESPACE RBS*ERROR at line 1:ORA-01552: cannot use system rollback segment for non-system tablespace 'RBS'

Según la nota 1005227.6 para la solución a este problema, provocado porque debe haber como mínimo un segmento activado que no sea SYSTEM, deben seguirse los siguientes pasos:

" Crear un segmento de “rollback” en el espacio SYSTEM y activarlo." Crear los segmentos oportunos en el espacio dedicado a “rollback”." Borrar o desactivar el segmento creado en SYSTEM

SQL> create public rollback segment r_borrar tablespace system 2 storage (initial 100k next 100k minextents 2 maxextents 10 optimal 500K);

Rollback segment created.

SQL> alter rollback segment r_borrar online;

Rollback segment altered.

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP01 TABLESPACE RBS 2 storage (initial 100k next 100k minextents 2 maxextents 10 optimal 500K);


30. Observar los parámetros de almacenamiento con que se ha creado el segmento RP01. ¿Cuáles son?, ¿cuáles son los del espacio de almacenamiento RBS?.

SQL> select substr(segment_name,1,10) seg,



substr(tablespace_name,1,10) tables, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS from dba_rollback_segs where segment_name ='RP01';

SEG TABLES INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS---------- ---------- -------------- ----------- ----------- -----------RP01 RBS 102400 102400 2 10

SQL> select substr(tablespace_name,1,10) tables, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS from dba_tablespaces where tablespace_name ='RBS'

TABLES INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS---------- -------------- ----------- ----------- -----------RBS 102400 102400 2 2147483645

31. Crear un segmento de "rollback" privado llamado RPR01.

SQL> CREATE ROLLBACK SEGMENT RPR01 TABLESPACE RBS;


32. Activar los segmentos de "rollback" creados anteriormente.

SQL> alter rollback segment rp01 online;


SQL> ALTER ROLLBACK SEGMENT RPR01 ONLINE;


33. Averiguar cuáles de los segmentos de "rollback" anteriores son públicos y cuáles privados.

SQL> select owner, segment_name, status from dba_rollback_segs;

OWNER SEGMENT_NAME STATUS------ ------------------------------ ----------------SYS SYSTEM ONLINEPUBLIC _SYSSMU1$ OFFLINEPUBLIC _SYSSMU2$ OFFLINEPUBLIC _SYSSMU3$ OFFLINE



PUBLIC _SYSSMU4$ OFFLINEPUBLIC _SYSSMU5$ OFFLINEPUBLIC _SYSSMU6$ OFFLINEPUBLIC _SYSSMU7$ OFFLINEPUBLIC _SYSSMU8$ OFFLINEPUBLIC R_BORRAR ONLINEPUBLIC RP01 ONLINESYS RPR01 ONLINE

12 rows selected.

34. Averiguar en que espacios de almacenamiento está definido cada uno de los segmentos de "rollback" de la base de datos.

SQL> select substr(owner,1,10) prop, substr(segment_name,1,10) nombre, tablespace_name from dba_rollback_segs order by segment_name;

PROP NOMBRE TABLESPACE_NAME------ ---------- ------------------------------PUBLIC RP01 RBSSYS RPR01 RBSPUBLIC R_BORRAR SYSTEMSYS SYSTEM SYSTEMPUBLIC _SYSSMU1$ UNDO_RBS1PUBLIC _SYSSMU2$ UNDO_RBS1PUBLIC _SYSSMU3$ UNDO_RBS1PUBLIC _SYSSMU4$ UNDO_RBS1PUBLIC _SYSSMU5$ UNDO_RBS1PUBLIC _SYSSMU6$ UNDO02PUBLIC _SYSSMU7$ UNDO02

PROP NOMBRE TABLESPACE_NAME------ ---------- ------------------------------PUBLIC _SYSSMU8$ UNDO02

12 rows selected.

35. Averiguar cuáles son los parámetros de almacenamiento de los segmentos de "rollback".

SQL> select substr(owner,1,10) prop, substr(segment_name,1,10) nombre, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS , PCT_INCREASE from dba_rollback_segs where segment_name like 'R%';

PROP NOMBRE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE------ ---------- -------------- ----------- -----------



----------- ------------PUBLIC RP01 102400 102400 2 10 0SYS RPR01 102400 102400 2 32765 0PUBLIC R_BORRAR 102400 102400 2 10 0

36. Desactivar los segmentos de "rollback" RP01 y RPR01.

SQL> ALTER ROLLBACK SEGMENT RP01 offline;


SQL> ALTER ROLLBACK SEGMENT RPR01 offline;


37. Parar y arrancar la base de datos, comprobar de nuevo el ejercicio 33. ¿Qué sucede con los segmentos de "rollback" RP01 y RPR01?.

Vease resolución del ejercicio anterior.

38. Modificar el init.ora de la base de datos de forma que esté declarado el segmento de "rollback" RPR01. Parar y arrancar la base de datos.

En el fichero init<SID>.ora:

rollback_segments = ( RPR01 )


SQL*Plus: Release 9.2.0.1.0 - Production on Mon Nov 22 10:03:34 2004


SQL> connect /as sysdba;Connected.SQL> shutdown immediateDatabase closed.Database dismounted.



ORACLE instance shut down.SQL> startupORACLE instance started.


39. Volver a realizar los ejercicios 26 y 27.

Vease resolución de ejercicios anteriores.

40. Desactivar el segmento de "rollback" RP01.

SQL> alter rollback segment rp01 offline;


41. Intentar borrar los segmentos de "rollback" RP01 y RPR01, ¿qué sucede?.

SQL> drop rollback segment rp01;

Rollback segment dropped.

SQL> drop rollback segment rpr01;drop rollback segment rpr01*ERROR at line 1:ORA-01545: rollback segment 'RPR01' specified not available

42. Desactivar el segmento de "rollback" RPR01 y borrarlo.

SQL> alter rollback segment rpr01 offline;


SQL> drop rollback segment rpr01;




43. Realizar una parada y arranque de la base de datos (sin modificar el init.ora ni la variable donde se declaran los segmentos de "rollback"). ¿Cuál es el resultado?. Modificar el init.ora y volver a arrancar la base de datos.




SQL> connect / as sysdba;Connected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.


Total System Global Area 34148352 bytesFixed Size 450560 bytesVariable Size 29360128 bytesDatabase Buffers 4194304 bytesRedo Buffers 143360 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forced

Buscar el mensaje de error precedente en la documentación en línea. Modificar el init.ora y rearrancar la base de datos.

44. Crear de nuevo los segmentos de "rollback" ateniéndose a los siguientes parámetros de almacenamiento:

- minextents 1- maxextents 1- initial 100k- next 100k

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP01 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 1 6* MAXEXTENTS 1)CREATE PUBLIC ROLLBACK SEGMENT RP01*ERROR at line 1:



ORA-01556: MINEXTENTS for rollback segment must be greater than 1

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP01 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2 6* MAXEXTENTS 1) MAXEXTENTS 1) *ERROR at line 6:ORA-02221: invalid MAXEXTENTS storage option value

45. Idem pero modificando los siguientes parámetros:

- minextents 2" maxextents 20

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP01 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2 6* MAXEXTENTS 20)


46. Crear un segmento de "rollback" público llamado RP02 con los mismos parámetros que los anteriores y además:

" pctincrease 50

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP02 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2 6 MAXEXTENTS 20 7* PCTINCREASE 50) PCTINCREASE 50) *ERROR at line 7:ORA-02192: PCTINCREASE not allowed for rollback segment storage clauses

47. Crear un segmento de "rollback" público llamado RP02 con los mismos parámetros que los anteriores.



SQL> CREATE PUBLIC ROLLBACK SEGMENT RP02 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2 6* MAXEXTENTS 20)


48. Crear un segmento de "rollback" público llamado RP03 con un optimal de 50k. Idem con un optimal de 300K.

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP03 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2 6 MAXEXTENTS 20 7* OPTIMAL 50K ) CREATE PUBLIC ROLLBACK SEGMENT RP03*ERROR at line 1:ORA-01593: rollback segment optimal size (25 blks) is smaller than the computed initial size (100 blks)

Buscar el error producido en la documentación en línea.

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP03 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2 6 MAXEXTENTS 20 7* OPTIMAL 300K )


49. Repetir los ejercicios 26 y 27 anteriores.

Vease resolución de ejercicios anteriores.

50. Decrementar el tamaño de los segmentos de "rollback" RP02 y RP03. Activar RP02 y RP03, repetir la operación.

SQL> ALTER ROLLBACK SEGMENT RP02 SHRINK;ALTER ROLLBACK SEGMENT RP02 SHRINK*



ERROR at line 1:ORA-01598: rollback segment 'RP02' is not online

SQL> ALTER ROLLBACK SEGMENT RP03 shrink;ALTER ROLLBACK SEGMENT RP03 shrink*ERROR at line 1:ORA-01598: rollback segment 'RP03' is not online

SQL> ALTER ROLLBACK SEGMENT RP02 online;


SQL> c.rp02.rp03. 1* ALTER ROLLBACK SEGMENT rp03 onlineSQL> r 1* ALTER ROLLBACK SEGMENT rp03 online


SQL> ALTER ROLLBACK SEGMENT RP02 SHRINK;


SQL> c.rp02.rp03. 1* ALTER ROLLBACK SEGMENT rp03 SHRINKSQL> r 1* ALTER ROLLBACK SEGMENT rp03 SHRINK


51. Desactivar todos los segmentos de "rollback" (incluido el SYSTEM).

SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME STATUS------------------------------ ----------------SYSTEM ONLINE_SYSSMU1$ OFFLINE_SYSSMU2$ OFFLINE_SYSSMU3$ OFFLINE_SYSSMU4$ OFFLINE_SYSSMU5$ OFFLINE_SYSSMU6$ OFFLINE_SYSSMU7$ OFFLINE_SYSSMU8$ OFFLINER_BORRAR ONLINERP01 OFFLINE



SEGMENT_NAME STATUS------------------------------ ----------------RP02 ONLINERP03 ONLINE

13 rows selected.

SQL> alter rollback segment r_borrar offline;






SQL> alter rollback segment system offline; alter rollback segment system offline*ERROR at line 1:ORA-01597: cannot alter system rollback segment online or offline

52. Borrar todos los segmentos de "rollback" de la base de datos (excepto el SYSTEM).

SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME STATUS------------------------------ ----------------SYSTEM ONLINE_SYSSMU1$ OFFLINE_SYSSMU2$ OFFLINE_SYSSMU3$ OFFLINE_SYSSMU4$ OFFLINE_SYSSMU5$ OFFLINE_SYSSMU6$ OFFLINE_SYSSMU7$ OFFLINE_SYSSMU8$ OFFLINERP01 OFFLINE

10 rows selected.

SQL> drop rollback segment r_borrar;








53. Intentar borrar el segmento de "rollback" SYSTEM, ¿qué sucede?.

SQL> select segment_name, status from dba_rollback_segs where status='ONLINE';

SEGMENT_NAME STATUS------------------------------ ----------------SYSTEM ONLINE

SQL> drop rollback segment system;drop rollback segment system*ERROR at line 1:ORA-01544: cannot drop system rollback segment

54. Crear segmentos de "rollback" públicos RP01, RP02, RP03, ... RP20 con los parámetros:

- minextents 2- maxextents 20- initial 100k- next 100k- optimal 500K

Editar un fichero llamado crea_segmentos.sql, por ejemplo, que contenga todas las sentencias de creación del tipo:

CREATE PUBLIC ROLLBACK SEGMENT RP01 TABLESPACE RBS STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 2 MAXEXTENTS 20 OPTIMAL 500K)/CREATE PUBLIC ROLLBACK SEGMENT RP02



TABLESPACE RBS STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 2 MAXEXTENTS 20 OPTIMAL 500K)/...

Ejecutar desde sqlplus el fichero de la siguiente forma:

SQL> @crea_segmentos.sql

55. Poner todos los segmentos anteriores en línea.

Editar un fichero llamado enlínea_segmentos.sql, por ejemplo, que contenga todas las sentencias del tipo:

Alter ROLLBACK SEGMENT RP01 online;

Ejecutar desde sqlplus el fichero de la siguiente forma:

SQL> @enlínea_segmentos.sql

56. Modificar los segmentos de "rollback" RP01 y RP02, indicando los siguientes parámetros:

- minextents 1" initial 200k

SQL> alter rollback segment rp01 2 storage (minextents 1 3* initial 200k); initial 200k); *ERROR at line 3:ORA-02203: INITIAL storage options not allowed

57. Intentar crear el segmento de "rollback" RP21 con las mismas caracteristicas que los anteriores pero sin poseer los privilegios necesarios para realizarlo (conectarse cmo usuario SCOTT).

SQL> CREATE PUBLIC ROLLBACK SEGMENT RP21 2 TABLESPACE RBS 3 STORAGE (INITIAL 100K 4 NEXT 100K 5 MINEXTENTS 2



6 MAXEXTENTS 20 7* OPTIMAL 500K)CREATE PUBLIC ROLLBACK SEGMENT RP21*ERROR at line 1:ORA-01031: insufficient privileges

58. Intentar borrar el segmento de "rollback" RP21 y el RP20 sin poseer los privileios necesarios para realizarlo, desactivarlos previamente (conectarse como usuario SCOTT).

SQL> alter rollback segment rp20 offline;alter rollback segment rp20 offline*ERROR at line 1:ORA-01031: insufficient privileges

59. Intentar modificar los segmentos de "rollback" RP19 y el RP18, sin poseer los privilegios necesarios para realizarlo, conectarse como usuario SCOTT, asignando:

- maxextents 25- next 200k" optimal 1M

SQL> alter rollback segment rp18 2 storage (next 200k 3 maxextents 25 4* optimal 1M)alter rollback segment rp18*ERROR at line 1:ORA-01031: insufficient privileges

60. Conectarse como usuario SYSTEM y realizar el ejercicio anterior.

SQL> connect systemEnter password: Connected.

SQL> alter rollback segment rp18 2 storage (next 200k 3 maxextents 25 4* optimal 1M)




61. Realizar una inserción en la tabla SCOTT.DEPT, conectarse como SCOTT, previamente asignar a la transacción el segmento de "rollback" RP19. Los valores seran los siguientes:

- DEPTNO 50- DNAME INFORMATICA- LOC MURCIA

SQL> connect scottEnter password: Connected.SQL> SET TRANSACTION USE ROLLBACK SEGMENT rp19;

Transaction set.

SQL> insert into scott.dept values (50,'INFORMATICA','MURCIA');

1 row created.

SQL> commit;

Commit complete.

62. Modificar el parámetro OPTIMAL de los segmentos de "rollback" RP01 a RP10, fijándolo en 100K. ¿Es posible?, ¿tiene sentido?.

SQL> alter rollback segment rp05 storage(optimal 100k);


63. Consultar el valor del parámetro OPTIMAL de los segmentos de "rollback" definidos en la base de datos.

SQL> select a.segment_name, a.segment_id, b.optsize from dba_rollback_segs a, v$rollstat b 2 where a.segment_id=b.usn 3* order by a.segment_name

SEGMENT_NAME SEGMENT_ID OPTSIZE------------------------------ ---------- ----------RP01 10RP02 9 512000RP03 11 512000RP04 12 512000RP05 13 102400RP06 14 512000RP07 15 512000RP08 16 512000RP09 17 512000RP10 18 512000



RP11 19 512000

SEGMENT_NAME SEGMENT_ID OPTSIZE------------------------------ ---------- ----------RP12 20 512000RP13 21 512000RP14 22 512000RP15 23 512000RP16 24 512000RP17 25 512000RP18 26 1048576RP19 27 512000RP20 28 512000SYSTEM 0



PRACTICAS TEMA 7.

USUARIOS, PERFILES, PRIVILEGIOS Y ROLES.

7. Buscar en la documentación en línea el contenido de las vistas:

• dba_profiles• dba_roles• dba_users• dba_role_privs• dba_tab_privs• dba_sys_privs

SQL> DESC DBA_PROFILES Nombre ¿Nulo? Tipo ----------------------------------------- -------- ----------------------- PROFILE NOT NULL VARCHAR2(30) RESOURCE_NAME NOT NULL VARCHAR2(32) RESOURCE_TYPE VARCHAR2(8) LIMIT VARCHAR2(40)

SQL> DESC DBA_ROLES Nombre ¿Nulo? Tipo ----------------------------------------- -------- ----------------------- ROLE NOT NULL VARCHAR2(30) PASSWORD_REQUIRED VARCHAR2(8)

SQL> DESC DBA_USERS Nombre ¿Nulo? Tipo ----------------------------------------- -------- ----------------------- USERNAME NOT NULL VARCHAR2(30) USER_ID NOT NULL NUMBER PASSWORD VARCHAR2(30) ACCOUNT_STATUS NOT NULL VARCHAR2(32) LOCK_DATE DATE EXPIRY_DATE DATE DEFAULT_TABLESPACE NOT NULL VARCHAR2(30) TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30) CREATED NOT NULL DATE PROFILE NOT NULL VARCHAR2(30) INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) EXTERNAL_NAME VARCHAR2(4000)



SQL> DESC DBA_ROLE_PRIVS Nombre ¿Nulo? Tipo ----------------------------------------- -------- ----------------------- GRANTEE VARCHAR2(30) GRANTED_ROLE NOT NULL VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE VARCHAR2(3)

SQL> desc DBA_TAB_PRIVS Nombre ¿Nulo? Tipo------------------------------------------ -------- -------------------- GRANTEE NOT NULL VARCHAR2(30) OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) GRANTOR NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) GRANTABLE VARCHAR2(3) HIERARCHY VARCHAR2(3)

SQL> DESC DBA_SYS_PRIVS Nombre ¿Nulo? Tipo ----------------------------------------- -------- ----------------------- GRANTEE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3)

8. Conectarse como usuario SYSTEM a la base y crear un usuario llamado “administrador” autentificado por la base de datos. Indicar como "tablespace" por defecto USERS y como "tablespace" temporal TEMP; asignar una cuota de 500K en el "tablespace" USERS.

SQL> CREATE USER ADMINISTRADOR IDENTIFIED BY ADMIN 2 DEFAULT TABLESPACE USERS 3 TEMPORARY TABLESPACE TEMP 4 QUOTA 500K ON USERS;

User created.

SQL> SELECT USERNAME FROM DBA_USERS WHERE USERNAME='ADMINISTRADOR';

USERNAME ------------------------------ ADMINISTRADOR



9. Abrir una sesión sqlplus e intentar conectarse como usuario “administrador”, ¿qué sucede?, ¿por qué?.

/u01/app/oracle/admin/CURSO01/creacion (CURSO01)> sqlplus



Enter user-name: administradorEnter password: ERROR:ORA-01045: user ADMINISTRADOR lacks CREATE SESSION privilege; logon denied

10. Averiguar qué privilegios de sistema, roles y privilegios sobre objetos tiene concedidos el usuario “administrador”.

SQL> select * from dba_role_privs where grantee='ADMINISTRADOR';

no rows selected

SQL> select * from dba_tab_privs where grantee='ADMINISTRADOR';

no rows selected

SQL> select * from dba_sys_privs where grantee='ADMINISTRADOR';

no rows selected

11. Otorgar el privilegio “CREATE SESSION” al usuario “administrador” e intentar de nuevo la conexión sqlplus.

SQL> grant create session to administrador;

Grant succeeded.

SQL> connect administradorEnter password: Connected.SQL>

12. Conectarse como usuario “administrador” y crear un usuario llamado “prueba00” que tenga como "tablespace" por



defecto USERS y como "tablespace" temporal TEMP; asignar una cuota de 0K en el "tablespace" USERS. ¿Es posible hacerlo?.

SQL> show userUSER is "ADMINISTRADOR"

SQL> r 1 create user prueba00 identified by prueba00 2 default tablespace users 3 temporary tablespace temp 4* quota 0k on userscreate user prueba00 identified by prueba00 *ERROR at line 1:ORA-01031: insufficient privileges

13. Conectado como usuario SYSTEM, otorgar el privilegio “create user” al usuario “administrador” y repetir el ejercicio anterior.

/u01/app/oracle/admin/CURSO01 (CURSO01)> sqlplus



Enter user-name: systemEnter password:

Connected to:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning and Oracle Label Security optionsJServer Release 9.2.0.1.0 - Production

SQL> grant create user to administrador;

Grant succeeded.

SQL> connect administradorEnter password: Connected.

SQL> create user prueba00 identified by prueba00 2 default tablespace users 3 temporary tablespace temp 4* quota 0k on users



User created.

14. Averiguar que usuarios de la base de datos tienen asignado el privilegio “create user” de forma directa, ¿qué vista debe ser consultada?.

SQL> connect systemIntroduzca su clave: Connected.

SQL> desc dba_sys_privs Nombre ¿Nulo? Tipo ----------------------------------------- -------- ----------------------- GRANTEE NOT NULL VARCHAR2(30) PRIVILEGE NOT NULL VARCHAR2(40) ADMIN_OPTION VARCHAR2(3)

SQL> select * from dba_sys_privs where privilege ='CREATE USER';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA CREATE USER YESADMINISTRADOR CREATE USER NOIMP_FULL_DATABASE CREATE USER NO

15. Hacer lo mismo para el privilegio “create session”.

SQL> select * from dba_sys_privs where privilege ='CREATE SESSION';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA CREATE SESSION YESCONNECT CREATE SESSION NOADMINISTRADOR CREATE SESSION NO



RECOVERY_CATALOG_OWNER CREATE SESSION NO

16. Crear dos "tablespace" llamados NOMINA y ACADEMO, que contendrán datos relativos a las aplicaciones de nomina y datos académicos de los empleados de una empresa, según las siguientes características:

ACADEMO NOMINA

Tamaño inicial 1M 1MAutoextensible SI SIExtensión 200K 100KTamaño máximo 1400K 1500K

Parámetros almacenamiento

Initial 16K 16K

Next 16K 16KMinextents 1 1Maxextents 3 3

Localización /u02/oradata/<bd> /u02/oradata/<bd>

Consulte la ayuda en línea si no recuerda la sintaxis exacta de la sentencia.

SQL> create tablespace academo datafile '/u02/oradata/CURSO01/academo01.dbf' size 1M 2 autoextend on next 200k maxsize 1400K 3 default storage (initial 16k next 16k 4 minextents 1 maxextents 3);

Tablespace created.

SQL> create tablespace nomina datafile '/u02/oradata/CURSO01/nomina01.dbf' size 1M 2 autoextend on next 100K maxsize 1500K 3 default storage (initial 16k next 16k 4 minextents 1 maxextents 3);

Tablespace created.

17. Crear dos "tablespace" temporales, manejados de forma local, llamados TEMP_NOMINA y TEMP_ACADEMO con las siguientes características:

TEMP_ACADEMO TEMP_NOMINA



Tamaño inicial 500K 600KAutoextensible SI SIExtensión 50K 50KTamaño máximo 600K 700K

Localización /u04/oradata/<bd> /u04/oradata/<bd>

SQL> create temporary tablespace temp_academo 2 tempfile '/u04/oradata/CURSO01/temp_academo01.dbf' 3 size 500k autoextend on next 50k maxsize 600k 4 extent management local uniform size 100k;

Tablespace created.

SQL> create temporary tablespace temp_nomina 2 tempfile '/u04/oradata/CURSO01/temp_nomina01.dbf' 3 size 600k autoextend on next 50k maxsize 700k 4 extent management local uniform size 100k;

Tablespace created.

18. Estando conectado como usuario “administrador” probar a crear un rol llamado “administrador”, ¿qué ocurre?.

SQL> connect administradorEnter password: Connected.SQL> create role administrador;create role administrador*ERROR at line 1:ORA-01031: insufficient privileges

19. Idem estando conectado como usuario SYSTEM, ¿qué sucede?, ¿por qué?.

SQL> connect systemEnter password: Connected.SQL> create role administrador; create role administrador *ERROR at line 1:ORA-01921: role name 'ADMINISTRADOR' conflicts with another user or role name



20. Comprobar en el diccionario de datos los usuarios o roles que poseen el privilegio “CREATE ROLE”.

SQL> select * from dba_sys_privs where privilege ='CREATE ROLE';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA CREATE ROLE YESIMP_FULL_DATABASE CREATE ROLE NO

21. Crear un rol llamado “ADMIN”, asignarle los privilegios “create session”, “create user” y “CREATE ROLE”. Asignarlo al usuario administrador.

SQL> create role admin;

Role created.

SQL> grant create session to admin;

Grant succeeded.

SQL> c.session.user. 1* grant create user to adminSQL> r 1* grant create user to admin

Grant succeeded.

SQL> c.user.role. 1* grant create role to adminSQL> r 1* grant create role to admin

Grant succeeded.

SQL> grant admin to administrador;

Grant succeeded.

22. Consultar los privilegios de sistema que tiene asignados de forma directa el usuario “administrador”, revocarlos y asignarle el rol “admin.”.



SQL> select * from dba_sys_privs where grantee ='ADMINISTRADOR'

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---ADMINISTRADOR CREATE SESSION NOADMINISTRADOR CREATE USER NO

SQL> revoke create session from administrador;

Revoke succeeded.

SQL> c.session.user. 1* revoke create user from administradorSQL> r 1* revoke create user from administrador

Revoke succeeded.

SQL> grant admin to administrador;

Grant succeeded.

23. Crear, conectado como SYSTEM, un usuario llamado “prueba01” autenticado por base de datos al que no se le asigne "tablespace" por defecto ni temporal.

SQL> create user prueba01 identified by prueba01;

User created.

24. Consultar en las vistas correspondientes los "tablespaces" y la quota en cada uno de ellos que tiene los usuarios SYS, SYSTEM, “administrador”, “prueba00” y “prueba01”. ¿Qué ha ocurrido con el usuario “prueba01”?.

SQL> select substr(username,1,15) usuario, DEFAULT_TABLESPACE , TEMPORARY_TABLESPACE 2 from dba_users 3 where username in ('SYS','SYSTEM','ADMINISTRADOR','PRUEBA00','PRUEBA01');

USUARIO DEFAULT_TABLESPACE TEMPORARY_TABLESPACE------------------------------------------------------------------



--PRUEBA01 SYSTEM SYSTEMPRUEBA00 USERS TEMPADMINISTRADOR USERS TEMPSYSTEM SYSTEM TEMPSYS SYSTEM TEMP

SQL> select substr(username,1,15) usuario, tablespace_name, max_bytes from dba_ts_quotas where username in ('SYS','SYSTEM','ADMINISTRADOR','PRUEBA00','PRUEBA01')

USUARIO TABLESPACE_NAME MAX_BYTES--------------- ------------------------------ ----------ADMINISTRADOR USERS 512000

25. Crear un usuario llamado “prueba02” autenticado por base de datos, asignando como "tablespace" por defecto NOMINA y como "tablespace" temporal TEMP_NOMINA (no se le asignara cuota en NOMINA).

SQL> create user prueba02 identified by prueba02 2 default tablespace nomina 3 temporary tablespace temp_nomina;

User created.

26. Asignar al usuario “prueba01” los "tablespace" ACADEMO y TEMP_ACADEMO como "tablespace" de trabajo y temporal respectivamente (sin especificar cuota).

SQL> alter user prueba01 temporary tablespace temp_academo;

User altered.

SQL> alter user prueba01 default tablespace academo;

User altered.

27. Consultar en las vistas correspondientes los "tablespace" y la cuota en cada uno de ellos que tiene los usuarios “prueba01” y “prueba02”.

SQL> select * from dba_ts_quotas where username in ('PRUEBA01','PRUEBA02');

no rows selected

28. Crear un rol llamado “CONEXIÓN” y asignarle el permiso “CREATE SESSION”.



SQL> create role conexion;

Role created.

SQL> grant create session to conexion;

Grant succeeded.

29. Asignar el rol “CONEXIÓN” a los usuarios “prueba00”, “prueba01” y “prueba02”.

SQL> grant conexion to prueba00, prueba01, prueba02;

Grant succeeded.

30. Comprobar en la vista correspondiente cuales son los roles asignados a los usuarios “prueba00”, “prueba01” y “prueba02”.

SQL> select * from dba_role_privs where grantee in ('PRUEBA00','PRUEBA01','PRUEBA02');

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---PRUEBA00 CONEXION NO YESPRUEBA01 CONEXION NO YESPRUEBA02 CONEXION NO YES

31. Conectarse como usuario “prueba01” y crear la tabla siguiente en el "tablespace" ACADEMO:

CREATE TABLE CODIGOS(CODIGO varchar2(3),DESCRIPCION varchar2(20))

TABLESPACE ACADEMOSTORAGE (INITIAL 64K

NEXT 64KMINEXTENTS 5MAXEXTENTS 10);

¿Es posible hacerlo?, ¿falta algún permiso?.



SQL> connect prueba01Enter password: Connected.

SQL> CREATE TABLE CODIGOS 2 (CODIGO varchar2(3), 3 DESCRIPCION varchar2(20)) 4 TABLESPACE ACADEMO 5 STORAGE (INITIAL 64K 6 NEXT 64K 7 MINEXTENTS 5 8* MAXEXTENTS 10)CREATE TABLE CODIGOS*ERROR at line 1:ORA-01031: insufficient privileges

32. Crear un rol llamado “DESARROLLO” y asignarle los permisos "CREATE SEQUENCE", "CREATE SESSION", "CREATE SYNONYM", "CREATE TABLE" y "CREATE VIEW". Asignar el rol “DESARROLLO” a los usuarios “prueba00”, “prueba01” y “prueba02”.


SQL> create role desarrollo;

Role created.

SQL> grant create sequence, create session, create synonym, create table, create view to desarrollo;

Grant succeeded.

SQL> grant desarrollo to prueba00, prueba01, prueba02;

Grant succeeded.

33. Volver a repetir el ejercicio 25. ¿Se genera algún error?.

SQL> connect prueba01Introduzca su clave: Connected.



SQL> CREATE TABLE CODIGOS 2 (CODIGO varchar2(3), 3 DESCRIPCION varchar2(20)) 4 TABLESPACE ACADEMO 5 STORAGE (INITIAL 64K 6 NEXT 64K 7 MINEXTENTS 5 8* MAXEXTENTS 10)CREATE TABLE CODIGOS*ERROR at line 1:ORA-01950: no privileges on tablespace 'ACADEMO'

34. Asignar cuota ilimitada al usuario “prueba01” en el "tablespace" ACADEMO. Volver a repetir el ejercicio 26.


SQL> alter user prueba01 quota unlimited on academo;

User altered.


SQL> CREATE TABLE CODIGOS(CODIGO varchar2(3),DESCRIPCION varchar2(20))TABLESPACE ACADEMOSTORAGE (INITIAL 64KNEXT 64KMINEXTENTS 5MAXEXTENTS 10);

Table created.

35. Asignar cuota ilimitada al usuario “prueba02” en el "tablespace" NOMINA.


SQL> alter user prueba02 quota unlimited on academo;



User altered.

36. Obtener información sobre roles, privilegios de sistema, "tablespace" y cuotas para los usuarios “prueba00”, “prueba01” y “prueba02”.

SQL> select * from dba_role_privs where grantee in ('PRUEBA00','PRUEBA01','PRUEBA02');

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---PRUEBA00 CONEXION NO YESPRUEBA00 DESARROLLO NO YESPRUEBA01 CONEXION NO YESPRUEBA01 DESARROLLO NO YESPRUEBA02 CONEXION NO YESPRUEBA02 DESARROLLO NO YES

6 rows selected.

SQL> select * from dba_sys_privs where grantee in ('PRUEBA00','PRUEBA01','PRUEBA02') 2 ;

no rows selected

SQL> select USERNAME , TABLESPACE_NAME , BYTES from dba_ts_quotas where username in ('PRUEBA00','PRUEBA01','PRUEBA02');

USERNAME TABLESPACE_NAME BYTES------------------------------ ------------------------------ ----------PRUEBA02 ACADEMO 0PRUEBA01 ACADEMO 327680



37. Asignar cuota cero en el "tablespace" por defecto para el usuario “prueba01”, ¿siguen estando sus objetos?, ¿es posible crear algún otro? (probad a crear un tabla).

SQL> alter user prueba01 quota 0k on academo;

User altered.

SQL> select owner, table_name from dba_tables where owner='PRUEBA01';

OWNER TABLE_NAME------------------------------ ------------------------------PRUEBA01 CODIGOS


SQL> CREATE TABLE CODIGOS2(CODIGO varchar2(3),DESCRIPCION varchar2(20))TABLESPACE ACADEMOSTORAGE (INITIAL 64KNEXT 64KMINEXTENTS 5MAXEXTENTS 10); CREATE TABLE CODIGOS2(CODIGO varchar2(3),*ERROR at line 1:ORA-01536: space quota exceeded for tablespace 'ACADEMO'

38. Conectarse como usuario “prueba01” e intentar modificar su cuota en el "tablespace" ACADEMO, ¿es posible?.


SQL> alter user prueba01 quota unlimited on academo;alter user prueba01 quota unlimited on academo*ERROR at line 1:ORA-01031: insufficient privileges

39. Conectarse como usuario “prueba01” y modificar su clave, ¿es posible?.

SQL> alter user prueba01 identified by probando01;



User altered.

40. Averiguar que usuarios o roles de base de datos tienen asignado el privilegio ALTER USER.

SQL> connect systemIntroduzca su clave: Conectado.

SQL> select * from dba_sys_privs where privilege='ALTER USER';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA ALTER USER YES

41. Abrir una sesión con el usuario “administrador” y otra con el usuario “prueba02”. Siendo el usuario “administrador”, intentar borrar el usuario “prueba02”.

SQL> show userUSER es "SYSTEM"

SQL> drop user prueba02;drop user prueba02*ERROR en línea 1:ORA-01940: no se puede borrar un usuario conectado actualmente

42. Asignar el permiso DROP USER al rol ADMIN.

SQL> grant drop user to admin;

Grant succeeded.

43. Averiguar que usuarios o roles de base de datos tienen asignado el privilegio DROP USER.

SQL> select * from dba_sys_privs where privilege='DROP USER';

GRANTEE PRIVILEGE ADM



------------------------------ ---------------------------------------- ---ADMIN DROP USER NODBA DROP USER YESIMP_FULL_DATABASE DROP USER NO

44. Conectado como usuario "administrador", crear el usuario “prueba03” autentificado por base de datos y asignando cuotas en el "tablespace" ACADEMO (500K) y NOMINA (200K). Su "tablespace" temporal será TEMP.


SQL> create user prueba03 identified by prueba03 default tablespace academo temporary tablespace temp quota 500k on academo quota 200k on nomina

User created.

45. Comprobar en el fichero de inicialización si está activado el modo de limitación de recursos.

Editar con el editor vi, por ejemplo, el fichero de inicializacion.

46. Averiguar que usuarios de base de datos o que roles tienen asignado el privilegio “CREATE PROFILE”.

SQL> connect systemEnter password: Connected.SQL> select * from dba_sys_privs where privilege='CREATE PROFILE';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA CREATE PROFILE YESIMP_FULL_DATABASE CREATE PROFILE NO



47. Asignar el permiso “CREATE PROFILE” al rol ADMIN.

SQL> grant create profile to admin;

Grant succeeded.

48. Averiguar que perfiles están definidos en la base de datos y que límites de recursos fija cada uno de ellos.

SQL> r 1 select substr(profile,1,12) perfil, substr(resource_name,1,25) recurso, resource_type, 2 substr(limit,1,10) limite from dba_profiles 3* order by profile, resource_name

PERFIL RECURSO RESOURCE LIMITE------------ ------------------------- -------- ----------DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITEDDEFAULT CONNECT_TIME KERNEL UNLIMITEDDEFAULT CPU_PER_CALL KERNEL UNLIMITEDDEFAULT CPU_PER_SESSION KERNEL UNLIMITEDDEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITEDDEFAULT IDLE_TIME KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITEDDEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITEDDEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED

PERFIL RECURSO RESOURCE LIMITE------------ ------------------------- -------- ----------DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITEDDEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITEDDEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULLDEFAULT PRIVATE_SGA KERNEL UNLIMITEDDEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

16 rows selected.

49. Consultar que perfiles tiene asignados cada usuario de la base de datos.

SQL> select username, profile from dba_users order by username;



USERNAME PROFILE------------------------------ ------------------------------ADMINISTRADOR DEFAULTDBSNMP DEFAULTOUTLN DEFAULTPRUEBA00 DEFAULTPRUEBA01 DEFAULTPRUEBA03 DEFAULTSCOTT DEFAULTSYS DEFAULTSYSTEM DEFAULT

9 rows selected.

50. Crear un perfil llamado “DESARROLLO” con las siguientes especificaciones:

Sessions_per_user 2Cpu_per_session unlimitedCpu_per_call 6000Connect_time 480Idle_time 2Failed_login_attempts 2Password_life_time 120

SQL> r 1 create profile desarrollo 2 limit 3 sessions_per_user 2 4 cpu_per_session unlimited 5 cpu_per_call 6000 6 connect_time 480 7 idle_time 2 8 failed_login_attempts 2 9* password_life_time 120

Profile created.

51. Asignar el perfil anterior a los usuarios “prueba00”, “prueba01”, “prueba02” y “prueba03”.

SQL> alter user prueba00 profile desarrollo;

User altered.

SQL> c.00.01 1* alter user prueba01 profile desarrolloSQL> r



1* alter user prueba01 profile desarrollo

User altered.

SQL> c.01.02 1* alter user prueba02 profile desarrolloSQL> r 1* alter user prueba02 profile desarrollo

User altered.

SQL> c.02.03 1* alter user prueba03 profile desarrolloSQL> r 1* alter user prueba03 profile desarrollo

User altered.

52. Intentar la conexión dos veces como usuario “prueba01” fallando la contraseña, ¿qué sucede?. Comprobar si la cuenta ha sido bloqueada en la vista de base de datos correspondiente.

SQL> connect prueba01Enter password: ERROR:ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

...

SQL> connect prueba01Enter password: ERROR:ORA-28000: the account is locked

SQL> select username, lock_date from dba_users where username like 'PRUEBA%';

USERNAME LOCK_DATE------------------------------ ---------PRUEBA03PRUEBA00



PRUEBA01 22-NOV-04

53. Crear un usuario “prueba04” con el parámetro “password expire”, sus "tablespace" por defecto y temporal serán USERS (cuota 0k) y TEMP. Asignar los roles CONEXIÓN y DESARROLLO. Conectarse como usuario “prueba04”, ¿qué sucede?.

SQL> create user prueba04 identified by prueba04 2 default tablespace users 3 temporary tablespace temp 4 quota 0k on users 5* password expire

User created.

SQL> grant conexion, desarrollo to prueba04;

Grant succeeded.

SQL> connect prueba04Enter password: ERROR:ORA-28001: the password has expired

Changing password for prueba04New password: Retype new password: Password changedConnected.

54. Bloquear la cuenta del usuario “prueba04”, ¿qué sucede al conectarse de nuevo?.


SQL> alter user prueba04 account lock;

User altered.

SQL> connect prueba04Enter password: ERROR:ORA-28000: the account is locked



Warning: You are no longer connected to ORACLE.

55. Modificar el "tablespace" por defecto y el temporal del usuario “prueba01” de forma que sean NOMINA y TEMP_NOMINA.


SQL> alter user prueba04 default tablespace nomina;

User altered.

SQL> alter user prueba04 temporary tablespace temp_nomina;

User altered.

56. Comprobar cual es el valor del parámetro OS_AUTHENT_PREFIX en la base de datos.

Editar con vi o ejecutar la sentencia pg sobre el fichero de parametros de inicializacion (init<SID>.ora).

57. Cambia la identificación del usuario “prueba01” de forma que sea identificado por el sistema operativo.

SQL> alter user prueba01 identified externally;

User altered.

SQL> set head offSQL> select * from dba_users where username='PRUEBA01'

PRUEBA01 26 EXTERNALLOCKED(TIMED) 22-NOV-04 22-MAR-05ACADEMO TEMP_ACADEMO

22-NOV-04DESARROLLO DEFAULT_CONSUMER_GROUP

58. Modificar el parámetro OS_AUTHENT_PREFIX de forma que, en adelante, la cadena que identifique a un usuario externo sea “” (cadena vacía).

Editar con vi o ejecutar la sentencia pg sobre el fichero de parametros de inicializacion (init<SID>.ora). Indicar:



os_authent_prefix = ""

59. Desbloquear la cuenta del usuario “prueba04”.

SQL> alter user prueba03 account unlock;

60. Modificar los valores del perfil DEFAULT según se indica en la siguiente tabla:

Sessions_per_user 5Cpu_per_session unlimitedCpu_per_call 6000Connect_time 480Idle_time 60Failed_login_attempts 3Password_life_time 180

SQL> alter profile default 2 limit 3 sessions_per_user 5 4 cpu_per_session unlimited 5 cpu_per_call 6000 6 connect_time 480 7 idle_time 60 8 failed_login_attempts 3 9 password_life_time 180;

Profile altered.

61. Averiguar que usuarios o roles tienen asignado el privilegio “ALTER PROFILE”.

SQL> select * from dba_sys_privs where privilege='ALTER PROFILE';


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

DBA ALTER PROFILE YES



62. Asignar el privilegio anterior al rol ADMIN..

SQL> grant alter profile to admin;

Grant succeeded.

63. Comprobar los valores asignados al perfil “DESARROLLO”. Modificar el perfil “DESARROLLO”, desde el usuario “administrador”, según la siguiente tabla:

Sessions_per_user 5Connect_time DEFAULTIdle_time 30

¿Qué ha sucedido con el resto de los parámetros?. ¿Coincide el valor de “Connect_time” en este perfil con el que tiene en el perfil DEFAULT?.

SQL> select profile, substr(resource_name,1,25) nombre_recurso, substr(limit,1,20) limite from dba_profiles where profile = 'DESARROLLO';

PROFILE NOMBRE_RECURSO LIMITE----------------------------------------------------------------

---DESARROLLO COMPOSITE_LIMIT DEFAULTDESARROLLO SESSIONS_PER_USER 2DESARROLLO CPU_PER_SESSION

UNLIMITEDDESARROLLO CPU_PER_CALL 6000DESARROLLO LOGICAL_READS_PER_SESSION DEFAULTDESARROLLO LOGICAL_READS_PER_CALL DEFAULTDESARROLLO IDLE_TIME 2DESARROLLO CONNECT_TIME 480DESARROLLO PRIVATE_SGA DEFAULTDESARROLLO FAILED_LOGIN_ATTEMPTS 2DESARROLLO PASSWORD_LIFE_TIME 120

PROFILE NOMBRE_RECURSO LIMITE----------------------------------------------------------------

---DESARROLLO PASSWORD_REUSE_TIME DEFAULTDESARROLLO PASSWORD_REUSE_MAX DEFAULTDESARROLLO PASSWORD_VERIFY_FUNCTION DEFAULTDESARROLLO PASSWORD_LOCK_TIME DEFAULT



DESARROLLO PASSWORD_GRACE_TIME DEFAULT

16 rows selected.

SQL> alter profile desarrollo 2 limit SESSIONS_PER_USER 5 3 connect_time default 4 idle_time 30;

Profile altered.

64. Averiguar los privilegios de sistema y sobre objetos, así como los roles, que tiene asignados los roles por defecto “CONNECT”, “RESOURCE”, “DBA”, “EXP_FULL_DATABASE” e “IMP_FULL_DATABASE”.

¿Considera una buena política de seguridad asignar el rol “CONNECT” a todos los usuarios que precisan conectarse a la base de datos?.

SQL> select * from dba_role_privs where grantee in ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE') order by grantee, granted_role

GRANTEE GRANTED_ROLE ADM DEF

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

DBA DELETE_CATALOG_ROLE YES YES

DBA EXECUTE_CATALOG_ROLE YES YES

DBA EXP_FULL_DATABASE NO YES

DBA GATHER_SYSTEM_STATISTICS NO YES

DBA IMP_FULL_DATABASE NO YES

DBA SELECT_CATALOG_ROLE YES YES

EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES

EXP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES

IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES

IMP_FULL_DATABASE SELECT_CATALOG_ROLE NO YES

10 rows selected.



SQL> select * from dba_sys_privs where grantee in ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE') order by grantee,privilege;


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

CONNECT ALTER SESSION NO

CONNECT CREATE CLUSTER NO

CONNECT CREATE DATABASE LINK NO

CONNECT CREATE SEQUENCE NO

CONNECT CREATE SESSION NO

CONNECT CREATE SYNONYM NO

CONNECT CREATE TABLE NO

CONNECT CREATE VIEW NO

DBA ADMINISTER DATABASE TRIGGER YES

DBA ADMINISTER RESOURCE MANAGER YES

DBA ...

SQL> select grantee, table_name, privilege from dba_tab_privs where grantee in ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE') order by grantee, table_name, privilege

DBA DBMS_DEFER_QUERY EXECUTE

DBA DBMS_DEFER_SYS EXECUTE

65. ¿Puede asignarse el perfil “DESARROLLO” al rol “CONNECT”?. ¿Y el perfil “DEFAULT” al perfil “DESARROLLO”?:

No.



66. Averiguar que usuarios o roles de la base de datos tienen asignado el privilegio “DROP PROFILE”.

SQL> select * from dba_sys_privs where privilege='DROP PROFILE';

GRANTEE PRIVILEGE ADM----------------------------------------------------------------

-----DBA DROP PROFILE YESIMP_FULL_DATABASE DROP PROFILE NO

67. Asignar el privilegio “DROP PROFILE” al rol “ADMIN.”.

SQL> grant drop profile to admin;

Grant succeeded.

68. Conectarse como usuario “administrador” e intentar eliminar el perfil “DEFAULT”, ¿qué ocurre?.


SQL> drop profile default;drop profile default *ERROR at line 1:ORA-00931: missing identifier

SQL> drop profile default cascade;drop profile default cascade *ERROR at line 1:ORA-00931: missing identifier

69. Como usuario “administrador” crear el rol “SECRETO” identificado por la contraseña “total” y asignarlo al usuario “prueba04”.




SQL> create role secreto identified by total;

Role created.

SQL> grant secreto to prueba04;

Grant succeeded.

70. Averiguar que usuarios poseen el privilegio “ALTER ANY ROLE” (de forma directa o a través de roles).

SQL> select * from dba_sys_privs where privilege='ALTER ANY ROLE';

DBA ALTER ANY ROLE YES

71. ¿Qué valor tiene en la base de datos el parámetro MAX_ENABLED_ROLES?. Modificar su valor para que, en adelante, valga 40. Comprobar esta modificacion.

SQL> show parameters max_enabled_roles

max_enabled_roles integer 30

72. Averiguar que usuarios poseen el privilegio “GRANT ANY ROLE” (de forma directa o a través de roles).

SQL> select * from dba_sys_privs where privilege='GRANT ANY ROLE';

DBA GRANT ANY ROLE YES

73. Como usuario “administrador”, deasignar el rol “SECRETO” al usuario “prueba04”.

SQL> revoke secreto from prueba04;

Revoke succeeded.

74. Asignar el privilegio “GRANT ANY ROLE” al rol “ADMIN.”.




SQL> grant grant any role to admin;

Grant succeeded.

75. Repetir el ejercicio 66.

SQL> select * from dba_sys_privs where privilege='GRANT ANY ROLE';

ADMIN GRANT ANY ROLE NO

DBA GRANT ANY ROLE YES

76. Averiguar que usuarios poseen el privilegio “DROP ANY ROLE” (de forma directa o a través de roles).

SQL> select * from dba_sys_privs where privilege='DROP ANY ROLE';

DBA DROP ANY ROLE YES

IMP_FULL_DATABASE DROP ANY ROLE NO

77. Asignar permiso de conexión al usuario "prueba03", asignar el rol “SECRETO” al mismo usuario. Conectarse como este usuario e intentar borrar el rol.


SQL> grant conexion to prueba03;

Grant succeeded.

SQL> grant secreto to prueba03;

Grant succeeded.




SQL> drop role secreto;drop role secreto*ERROR at line 1:ORA-01031: insufficient privileges

78. En caso de que no lo tenga asignado, asignar el rol “CONEXION” y el rol “DESARROLLO” al usuario “prueba04”. Hacer que solo el rol “CONEXIÓN” este activo cuando se conecte.

SQL> select * from dba_role_privs where grantee='PRUEBA04';

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---PRUEBA04 CONEXION NO YESPRUEBA04 DESARROLLO NO YES

SQL> alter user prueba04 default role conexion;

User altered.

79. Comprobar en la vista apropiada del diccionario de datos los roles activos en la sesión.

SQL> select * from dba_role_privs where grantee='PRUEBA04';

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---PRUEBA04 CONEXION NO YESPRUEBA04 DESARROLLO NO NO

80. Conectado como usuario “prueba04”, activar el rol “DESARROLLO” y comprobar de nuevo en la vista apropiada del diccionario de datos los roles activos en la sesión.




SQL> select * from session_roles;

ROLE------------------------------CONEXION

SQL> set role all;

Role set.

SQL> select * from session_roles;

ROLE------------------------------CONEXIONDESARROLLO

81. Asignar el rol "CONNECT" al usuario "ADMIN". ¿Es preciso asignarle los permisos "CREATE PROCEDURE", "CREATE PUBLIC SYNONYM", "CREATE ROLE", "CREATE TRIGGER"?, ¿Los tiene ya asignados?.

SQL> grant connect to admin;

Grant succeeded.

SQL> select * from dba_sys_privs where grantee='CONNECT';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---CONNECT CREATE VIEW NOCONNECT CREATE TABLE NOCONNECT ALTER SESSION NOCONNECT CREATE CLUSTER NOCONNECT CREATE SESSION NOCONNECT CREATE SYNONYM NOCONNECT CREATE SEQUENCE NO



CONNECT CREATE DATABASE LINK NO

8 rows selected.

82. Conectarse como usuario SYSTEM y otorgar al usuario "prueba02" el permiso para seleccionar datos de la tabla códigos (pertenece al usuario "prueba01"). ¿Qué sucede?, ¿por qué?.

SQL> show userUSER es "SYSTEM"

SQL> grant select on prueba01.codigos to prueba02;grant select on prueba01.codigos to prueba02 *ERROR en línea 1:ORA-01031: privilegios insuficientes

83. Conectarse como usuario "prueba01" y otorgar al usuario "prueba02" el permiso para seleccionar datos de la tabla códigos; hacerlo de forma que "prueba02" también pueda otorgar el permiso a otros usuarios (opción ADMIN).


SQL> grant select on prueba01.codigos to prueba02 with grant option;

Grant succeeded.

84. Conectarse como usuario "prueba02" y otorgar al usuario "prueba03" el permiso para seleccionar datos de la tabla códigos.


SQL> grant select on prueba01.codigos to prueba03 ;

Grant succeeded.


SQL> select * from prueba01.codigos;



no rows selected

85. Conectarse como usuario "prueba01" y revocar al usuario "prueba02" el permiso para seleccionar datos de la tabla códigos.


SQL> revoke select on prueba01.codigos from prueba02;

Revoke succeeded.

86. Conectarse como usuario "prueba03" e intentar consultar la tabla códigos. ¿Qué ocurre?, ¿por qué?.


SQL> select * from prueba01.codigos;select * from prueba01.codigos *ERROR at line 1:ORA-00942: table or view does not exist



PRACTICAS TEMA 8.

TRABAJOS (JOBS).

87. Identificar los procesos de sistema operativo que corresponden al proceso gestor de colas corriendo en la instancia.

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> ps -ef|grep cj

oracle 20321 1 0 14:27 ? 00:00:00 ora_cjq0_CURSO01oracle 20525 19652 0 15:13 pts/172 00:00:00 grep cj

88. Comprobar el valor del parámetro job_queue_proceses en el fichero de inicialización.

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> grep job initCURSO01.ora

job_queue_processes = 10

89. Eliminar el proceso de sistema operativo gestor de colas mediante la orden “kill”. ¿Qué sucede?, ¿ha afectado a la instancia?, ¿se ha generado algún error en el fichero de alertas?, ¿y alguna traza?.

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> ps -ef|grep CURSO01oracle 20309 1 0 14:27 ? 00:00:00 ora_pmon_CURSO01oracle 20311 1 0 14:27 ? 00:00:00 ora_dbw0_CURSO01oracle 20313 1 0 14:27 ? 00:00:00 ora_lgwr_CURSO01oracle 20315 1 0 14:27 ? 00:00:00 ora_ckpt_CURSO01oracle 20317 1 0 14:27 ? 00:00:00 ora_smon_CURSO01oracle 20319 1 0 14:27 ? 00:00:00 ora_reco_CURSO01oracle 20542 1 0 15:16 ? 00:00:00 ora_cjq0_CURSO01oracle 20546 19652 0 15:17 pts/172 00:00:00 grep CURSO01

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> kill -9 20542

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> ps -ef|grep CURSO01oracle 20309 1 0 14:27 ? 00:00:00 ora_pmon_CURSO01oracle 20311 1 0 14:27 ? 00:00:00 ora_dbw0_CURSO01oracle 20313 1 0 14:27 ? 00:00:00 ora_lgwr_CURSO01oracle 20315 1 0 14:27 ? 00:00:00 ora_ckpt_CURSO01oracle 20317 1 0 14:27 ? 00:00:00 ora_smon_CURSO01oracle 20319 1 0 14:27 ? 00:00:00 ora_reco_CURSO01oracle 20548 19652 0 15:17 pts/172 00:00:00 grep CURSO01

/u01/app/oracle/admin/CURSO01/pfile (CURSO01)> ps -ef|grep CURSO01oracle 20309 1 0 14:27 ? 00:00:00 ora_pmon_CURSO01oracle 20311 1 0 14:27 ? 00:00:00 ora_dbw0_CURSO01oracle 20313 1 0 14:27 ? 00:00:00 ora_lgwr_CURSO01



oracle 20315 1 0 14:27 ? 00:00:00 ora_ckpt_CURSO01oracle 20317 1 0 14:27 ? 00:00:00 ora_smon_CURSO01oracle 20319 1 0 14:27 ? 00:00:00 ora_reco_CURSO01oracle 20556 1 0 15:21 ? 00:00:00 ora_cjq0_CURSO01oracle 20558 19652 0 15:21 pts/172 00:00:00 grep CURSO01

En el fichero de alertas aparece el mensaje:

Mon Nov 22 15:13:31 2004Restarting dead background process CJQ0CJQ0 started with pid=8Mon Nov 22 15:18:31 2004Restarting dead background process CJQ0CJQ0 started with pid=8

90. Fijar el parámetro job_queue_proceses en 1020 procesos. Parar y arrancar la base de datos. ¿Arranca con normalidad?, ¿qué valor toma el parámetro?.






SQL> startupORA-01078: failure in processing system parametersLRM-00122: value '1020' for 'job_queue_processes' must be between '0' and '1000'

91. Ver el contenido del paquete dbms_job.

Revisar documentacion en linea.

92. Ver la descripción de las vistas dba_jobs, user_jobs y dba_jobs_running. Buscar la descripción de cada uno de los campos en la documentación en línea.

SQL> desc dba_jobs Name Null? Type ----------------------------------------- -------- ---------------- JOB NOT NULL NUMBER LOG_USER NOT NULL VARCHAR2(30) PRIV_USER NOT NULL VARCHAR2(30) SCHEMA_USER NOT NULL VARCHAR2(30)



LAST_DATE DATE LAST_SEC VARCHAR2(8) THIS_DATE DATE THIS_SEC VARCHAR2(8) NEXT_DATE NOT NULL DATE NEXT_SEC VARCHAR2(8) TOTAL_TIME NUMBER BROKEN VARCHAR2(1) INTERVAL NOT NULL VARCHAR2(200) FAILURES NUMBER WHAT VARCHAR2(4000) NLS_ENV VARCHAR2(4000) MISC_ENV RAW(32) INSTANCE NUMBER

SQL> desc user_jobs Name Null? Type ----------------------------------------- -------- ------------- JOB NOT NULL NUMBER LOG_USER NOT NULL VARCHAR2(30) PRIV_USER NOT NULL VARCHAR2(30) SCHEMA_USER NOT NULL VARCHAR2(30) LAST_DATE DATE LAST_SEC VARCHAR2(8) THIS_DATE DATE THIS_SEC VARCHAR2(8) NEXT_DATE NOT NULL DATE NEXT_SEC VARCHAR2(8) TOTAL_TIME NUMBER BROKEN VARCHAR2(1) INTERVAL NOT NULL VARCHAR2(200) FAILURES NUMBER WHAT VARCHAR2(4000) NLS_ENV VARCHAR2(4000) MISC_ENV RAW(32) INSTANCE NUMBER

SQL> desc dba_jobs_running Name Null? Type ----------------------------------------- -------- ------------ SID NUMBER JOB NUMBER FAILURES NUMBER LAST_DATE DATE LAST_SEC VARCHAR2(8) THIS_DATE DATE THIS_SEC VARCHAR2(8) INSTANCE NUMBER

93. Como usuario SYSTEM crear un trabajo que llamara al procedimiento descrito seguidamente, también propiedad del SYSTEM, que permite analizar el esquema de un cierto numero de usuarios y generar las estadísticas internas usadas por el analizador sintáctico de Oracle.

Se ejecutara en el momento de su creación y con periodicidad semanal.

create table usuarios_estadisticas (username varchar2(30) not null,fecha date,error varchar2(80))



tablespace usersstorage (initial 16K next 16K maxextents 10)

CREATE OR REPLACE procedure analiza_usuarios ascursor usuarios is

select username,rowid from system.usuarios_estadisticas;werror varchar2(80);wrowid urowid;

beginFOR rec_usuarios IN usuarios LOOP

wrowid:=rec_usuarios.rowid;begin

DBMS_UTILITY.ANALYZE_SCHEMA(rec_usuarios.username,'ESTIMATE',NULL,33);

update system.usuarios_estadisticasset fecha=sysdate, error=nullwhere rowid=rec_usuarios.rowid;

exceptionwhen others thenwerror:=rpad(sqlerrm,80);update system.usuarios_estadisticas

set error=werror, fecha=sysdatewhere rowid=wrowid;

end;END LOOP;

end analiza_usuarios;/

Crear un "script" llamado crea_trabajo1.sql, por ejemplo, con el contenido siguiente:

DECLAREv_numjob integer;begindbms_job.submit(v_numjob,'SYSTEM.ANALIZA_USUARIOS;', SYSDATE, 'SYSDATE + 7');commit;DBMS_OUTPUT.PUT_LINE('Identificador de trabajo es: ' || v_numjob);end;/

Ejecutarlo desde sqlplus:

SQL> @crea_trabajo1.sql

Procedimiento PL/SQL terminado correctamente.



94. Fijar el numero de procesos de ejecucion de trabajos en cero y forzar la ejecución del trabajo, ¿se genera algún error?. ¿Ha variado el intervalo de ejecución?, ¿qué día de la semana se ejecutara si se fuerza de nuevo su ejecución mañana?.

SQL> alter system set job_queue_processes=0;

System altered.

SQL> select job,to_char(last_date, 'dd-mm-yyyy hh24:mi:ss'), to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25) from dba_jobs;

JOB TO_CHAR(LAST_DATE,' TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) ------ ------------------- ----------------------------------------- 2 22-11-2004 15:31:18 29-11-2004 15:31:18 SYSDATE + 7

SQL> exec dbms_job.run(2);

PL/SQL procedure successfully completed.

SQL> select job,to_char(last_date, 'dd-mm-yyyy hh24:mi:ss'), to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25) from dba_jobs;

JOB TO_CHAR(LAST_DATE,' TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) ---------- ------------------- ------------------------------------ 2 22-11-2004 15:37:11 29-11-2004 15:37:11 SYSDATE + 7

9. Observar, en caso de que se haya producido, el error generado en el ejercicio anterior en el fichero de alertas y comprobar si se ha creado algún fichero de traza; en caso de existir este ultimo, editarlo con el editor vi y ver su contenido.

10. Como usuario “prueba01” crear un trabajo que llamara al procedimiento descrito seguidamente, también propiedad de “prueba01”, que permite averiguar a cuales de las tablas propiedad del usuario tienen ocupado mas del 80% de las extensiones que le son permitidas. Insertara una fila en la tabla “tablas_revision” por cada una de las tablas que cumplan la condición.

Se ejecutara cada hora.

Previamente debe crearse la siguiente tabla:



CREATE TABLE tablas_revision(nombre_tabla varchar2(30),ocupacion number)storage (initial 100k next 100k);

¿En que espacio de almacenamiento se ha creado la tabla?, ¿con que parámetros de almacenamiento?.

CREATE OR REPLACE procedure chequear_tablas asextensiones integer;maximo_extensiones integer;ocupacion integer;porcentaje_extensiones integer := 80;cursor c_tablas is select table_name from user_tables;

begin FOR rec_tables IN c_tablas LOOP select count(*) into extensiones from user_extents where segment_name=rec_tables.table_name;

select max_extents into maximo_extensiones from user_tables where table_name=rec_tables.table_name;

ocupacion:=(round(extensiones*100/maximo_extensiones));

IF (ocupacion > porcentaje_extensiones) THEN insert into tablas_revision values (rec_tables.table_name, ocupacion); commit; END IF;

END LOOP;

end chequear_tablas;/


SQL> CREATE TABLE tablas_revision 2 (nombre_tabla varchar2(30), 3 ocupacion number) 4 storage (initial 100k next 100k);

Table created.



Para ver las caracteristicas de almacenamiento de la tabla consultar la vista "user_tables".

Crear un "script" llamado chequear_tablas.sql, por ejemplo, que contenga las sentencias de creacion del procedimiento:

SQL> @chequear_tablas.sql

Procedure created.

Crear un "script" llmado crea_trabajo2.sql, por ejemplo, con el contenido siguiente:

DECLAREv_numjob integer;begindbms_job.submit(v_numjob,'PRUEBA01.CHEQUEAR_TABLAS;', SYSDATE, 'SYSDATE + 60/1440');commit;DBMS_OUTPUT.PUT_LINE('Identificador de trabajo es: ' || v_numjob);end;/



SQL> select job,to_char(last_date, 'dd-mm-yyyy hh24:mi:ss'), to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25) from user_jobs

JOB TO_CHAR(LAST_DATE,' TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) -------- ------------------- ------------------------------------------ 3 23-11-2004 13:13:42 SYSDATE + 60/1440

11. Como usuario SYSTEM crear un trabajo que llamara al procedimiento descrito seguidamente, también propiedad del SYSTEM, que permite monitorizar el numero de sesiones activas de usuario en la instancia. Se ejecutara cada cinco minutos.

CREATE OR REPLACE procedure monitorizar_usuarios as



sesiones number(4);activas number(4);cursor c_usuarios isselect usuariofrom usuarios_monitorizadoswhere monitorizar = 'S';

beginFOR rec_usuarios IN c_usuarios LOOP

select nvl(count(*),0) into sesionesfrom v$sessionwhere username=rec_usuarios.usuarioand status in ('ACTIVE','INACTIVE');

select nvl(count(*),0) into activasfrom v$sessionwhere username=rec_usuarios.usuarioand status = 'ACTIVE';

insert into usuarios_monitorizados_logvalues (rec_usuarios.usuario,sysdate,sesiones,activas);

END LOOP;commit;end monitorizar_usuarios;/

Previamente deben crearse las siguientes tablas:

CREATE TABLE usuarios_monitorizados_log (usuario varchar2(30),fecha_log date,sesiones number(4),activas number(4))storage (initial 100k next 100k);

CREATE TABLE usuarios_monitorizados (usuario varchar2(30),monitorizar char(1),descripcion varchar2(80))storage (initial 100k next 100k);

SQL> CREATE TABLE usuarios_monitorizados_log (usuario varchar2(30),fecha_log date,sesiones number(4),activas number(4))



storage (initial 100k next 100k);

Table created.

SQL> CREATE TABLE usuarios_monitorizados (usuario varchar2(30),monitorizar char(1),descripcion varchar2(80))storage (initial 100k next 100k);

Table created.

Conectarse como usuario SYS y dar permisos sobre vista SESSION:

SQL> connect sys as sysdba;Enter password: Connected.SQL> show userUSER is "SYS"

SQL> grant select on V_$SESSION to system;

Grant succeeded.

Crear un "script" llamado monitorizar_usuarios.sql, por ejemplo, que contenga las sentencias de creacion del procedimiento:

SQL> @monitorizar_usuarios.sql

Procedure created.

Crear un "script" llmado crea_trabajo3.sql, por ejemplo, con el contenido siguiente:

DECLAREv_numjob integer;begindbms_job.submit(v_numjob,'SYSTEM.MONITORIZAR_USUARIOS;', SYSDATE, 'SYSDATE + 5/1440');commit;DBMS_OUTPUT.PUT_LINE('Identificador de trabajo es: ' || v_numjob);end;/





12. Consultar todos los trabajos definidos para el usuario SYSTEM, obteniendo el numero de trabajo, fecha de la próxima ejecución, intervalo, numero de fallos producidos, estado (“broken” o no) y su definición.

SQL> select job, to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25), failures, broken, what from dba_jobs where schema_user='SYSTEM';

JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -WHAT-------------------------------------------------------------------------- 1 25-11-2004 11:41:19 SYSDATE + 7 0 NSYSTEM.ANALIZA_USUARIOS;

2 29-11-2004 15:37:11 SYSDATE + 7 0 NSYSTEM.ANALIZA_USUARIOS;

4 23-11-2004 13:26:43 SYSDATE + 5/1440 NSYSTEM.MONITORIZAR_USUARIOS;

13. Repetir el ejercicio anterior para el usuario “prueba01”.

SQL> select job, to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25), failures, broken, what from dba_jobs where schema_user='PRUEBA01';

JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -WHAT-------------------------------------------------------------------------- 3 23-11-2004 13:13:42 SYSDATE + 60/1440 NPRUEBA01.CHEQUEAR_TABLAS;

14. Conectarse como usuario “prueba01” e intentar eliminar de la cola de trabajos a aquel trabajo que realiza la



monitorización de usuarios, ¿qué sucede?.


SQL> exec dbms_job.remove(4);BEGIN dbms_job.remove(4); END;*ERROR at line 1:ORA-23421: job number 4 is not a job in the job queueORA-06512: at "SYS.DBMS_SYS_ERROR", line 86ORA-06512: at "SYS.DBMS_IJOB", line 529ORA-06512: at "SYS.DBMS_JOB", line 171ORA-06512: at line 1

15. Conectado como usuario "prueba01", consultar la vista user_jobs y determinar sus trabajos.

SQL> select job, to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25), failures, broken, what from user_jobs;

JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -WHAT-------------------------------------------------------------------------- 3 23-11-2004 13:13:42 SYSDATE + 60/1440 NPRUEBA01.CHEQUEAR_TABLAS;

16. Marcar el trabajo, propiedad del usuario “prueba01”, que realiza el chequeo de tablas como “broken”. Consultar su estado en la vista user_jobs.

SQL> exec dbms_job.broken(3,TRUE);



JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -



WHAT-------------------------------------------------------------------------- 3 01-01-4000 00:00:00 SYSDATE + 60/1440 YPRUEBA01.CHEQUEAR_TABLAS;

17. Intentar marcar como “broken” el trabajo que realiza la monitorización de usuarios. ¿Qué sucede al realizarlo?, ¿quién es su poseedor?.

SQL> exec dbms_job.broken(4,TRUE);BEGIN dbms_job.broken(4,TRUE); END;*ERROR at line 1:ORA-23421: job number 4 is not a job in the job queueORA-06512: at "SYS.DBMS_SYS_ERROR", line 86ORA-06512: at "SYS.DBMS_IJOB", line 529ORA-06512: at "SYS.DBMS_JOB", line 245ORA-06512: at line 1

18. Conectarse como usuario SYSTEM y modificar el trabajo que llama al procedimiento de monitorización de usuarios para que se realice cada quince minutos.

SQL> exec dbms_job.interval(4,'SYSDATE + 15/1440');


SQL> select job, to_char(next_date, 'dd-mm-yyyy hh24:mi:ss'),substr(interval,1,25), failures, broken, what from dba_jobs where schema_user='SYSTEM';

JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -WHAT-------------------------------------------------------------------------- 1 25-11-2004 11:41:19 SYSDATE + 7 0 NSYSTEM.ANALIZA_USUARIOS;

2 29-11-2004 15:37:11 SYSDATE + 7 0 NSYSTEM.ANALIZA_USUARIOS;

4 23-11-2004 13:26:43 SYSDATE + 15/1440 N



SYSTEM.MONITORIZAR_USUARIOS;

19. Conectarse como usuario “prueba01” y modificar el trabajo que chequea la ocupación de las tablas para que se realice cada treinta minutos.


SQL> exec dbms_job.interval(3,'SYSDATE + 30/1440');



JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -WHAT------------------------------------------------------------------------- 3 01-01-4000 00:00:00 SYSDATE + 30/1440 YPRUEBA01.CHEQUEAR_TABLAS;

20. Forzar la ejecución inmediata del trabajo que realiza el chequeo de ocupación de tablas. ¿Conectado como que usuario debe realizarse?.

SQL> exec dbms_job.run(3);



JOB TO_CHAR(NEXT_DATE,' SUBSTR(INTERVAL,1,25) FAILURES B---------- ------------------- ------------------------- ---------- -WHAT-------------------------------------------------------------------------- 3 23-11-2004 14:13:27 SYSDATE + 30/1440 0 N



PRUEBA01.CHEQUEAR_TABLAS;

21. Conectado como usuario SYSTEM, eliminar el trabajo que realiza el chequeo de ocupación de tablas.


SQL> exec dbms_job.remove(3);BEGIN dbms_job.remove(3); END;*ERROR at line 1:ORA-23421: job number 3 is not a job in the job queueORA-06512: at "SYS.DBMS_SYS_ERROR", line 86ORA-06512: at "SYS.DBMS_IJOB", line 529ORA-06512: at "SYS.DBMS_JOB", line 171ORA-06512: at line 1

22. Repetir el ejercicio anterior conectado como usuario “prueba01”.


SQL> exec dbms_job.remove(3);



no rows selected



PRACTICAS TEMA 9.

AUDITORÍA.

1. Ver la descripción de las vistas ALL_DEF_AUDIT_OPTS, DBA_AUDIT_OBJECT, DBA_AUDIT_SESSION, DBA_AUDIT_STATEMENT, DBA_AUDIT_TRAIL, DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, y DBA_STMT_AUDIT_OPTS. Averiguar el significado de cada uno de los campos usando la documentación en línea.

SQL> desc ALL_DEF_AUDIT_OPTS Nombre ¿Nulo? Tipo ----------------------------------------- --------

--------------- ALT VARCHAR2(3) AUD VARCHAR2(3) COM VARCHAR2(3) DEL VARCHAR2(3) GRA VARCHAR2(3) IND VARCHAR2(3) INS VARCHAR2(3) LOC VARCHAR2(3) REN VARCHAR2(3) SEL VARCHAR2(3) UPD VARCHAR2(3) REF VARCHAR2(3) EXE VARCHAR2(3)

...

2. Ver la descripción de la vista SYS.AUD$.

SQL> desc sys.aud$

Name Null? Type----------------------------------------------------------------

-- SESSIONID NOT NULL NUMBER ENTRYID NOT NULL NUMBER STATEMENT NOT NULL NUMBER TIMESTAMP# NOT NULL DATE USERID VARCHAR2(30) USERHOST

VARCHAR2(128) TERMINAL



VARCHAR2(255) ACTION# NOT NULL NUMBER RETURNCODE NOT NULL NUMBER OBJ$CREATOR VARCHAR2(30) OBJ$NAME

VARCHAR2(128) AUTH$PRIVILEGES VARCHAR2(16) AUTH$GRANTEE VARCHAR2(30) NEW$OWNER VARCHAR2(30) NEW$NAME

VARCHAR2(128) SES$ACTIONS VARCHAR2(19) SES$TID NUMBER LOGOFF$LREAD NUMBER LOGOFF$PREAD NUMBER LOGOFF$LWRITE NUMBER LOGOFF$DEAD NUMBER LOGOFF$TIME DATE COMMENT$TEXT

VARCHAR2(4000) CLIENTID VARCHAR2(64) SPARE1

VARCHAR2(255) SPARE2 NUMBER OBJ$LABEL RAW(255) SES$LABEL RAW(255) PRIV$USED NUMBER SESSIONCPU NUMBER

3. Comprobar si esta habilitada la auditoría en la base de datos. Habilitar la auditoría en la base de datos en caso de que no este.

SQL> show parameters audit_trail

NAME TYPE VALUE---------------------------------------------------------audit_trail string NONE

Se debe parar la base de datos, editar el fichero de parametros de inicializacion, init<bd>.ora, añadiendo la linea "audit_trail=db", y volver a arrancar la base de datos.

4. Comprobar que usuarios de base de datos tienen asignados los privilegios AUDIT SYSTEM y AUDIT ANY.

SQL> connect systemEnter password:



Connected.

SQL> select * from dba_sys_privs where privilege ='AUDIT ANY';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA AUDIT ANY YESIMP_FULL_DATABASE AUDIT ANY NO

SQL> select * from dba_sys_privs where privilege ='AUDIT SYSTEM';

GRANTEE PRIVILEGE ADM------------------------------ ---------------------------------------- ---DBA AUDIT SYSTEM YES

5. Auditar todas las conexiones exitosas y fallidas a la base de datos.

SQL> audit session;

Audit succeeded.

6. Abrir una sesión a la base de datos como usuario “SYSTEM” y como usuario “prueba01”. Comprobar que información se ha guardado en la auditoría como consecuencia de las operaciones anteriores (consultar la vista DBA_AUDIT_TRAIL).

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario, to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss') tiempo_desconexion from dba_audit_trail order by username,timestamp,logoff_time;

USUARIO_SO USUARIO TIEMPO_CONEXION TIEMPO_DESCONEXION---------- ------------ ------------------ -------------------oracle PRUEBA01 29-11-004 11:53:50oracle SYSTEM 29-11-004 11:53:44 29-11-2004 11:53:50

7. Auditar las conexiones exitosas y fallidas a la base de datos de los usuarios “prueba01” y “prueba02”.



SQL> audit session by prueba01, prueba02;

Audit succeeded.

8. Conectarse a la base de datos como usuario “prueba02” introduciendo una clave errónea. Comprobar el apunte realizado en la auditoría del sistema (consultar la vista DBA_AUDIT_SESSION).

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario, to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss') tiempo_desconexion from dba_audit_session where username='PRUEBA02' order by username,timestamp,logoff_time

USUARIO_SO USUARIO TIEMPO_CONEXION TIEMPO_DESCONEXION---------- ------------ ------------------ -------------------oracle PRUEBA02 29-11-004 11:55:37

9. Auditar el uso de SELECT TABLE, DELETE TABLE, UPDATE TABLE e INSERT TABLE en cualquier caso (fallido o no) y por sentencia individual. Comprobar las opciones activas de auditoría (vista DBA_STMT_AUDIT_OPTS).

SQL> audit select table, delete table, update table, insert table by access;

Audit succeeded.

SQL> select user_name usuario, audit_option opcion, success, failure from DBA_STMT_AUDIT_OPTS order by user_name

USUARIO OPCION SUCCESS FAILURE------------------------------------------------------------PRUEBA01 CREATE SESSION BY ACCESS BY ACCESSPRUEBA02 CREATE SESSION BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS SELECT TABLE BY ACCESS BY ACCESS INSERT TABLE BY ACCESS BY ACCESS UPDATE TABLE BY ACCESS BY ACCESS DELETE TABLE BY ACCESS BY ACCESS

7 rows selected.

10. Conectarse como usuario “prueba01”. Comprobar si se ha creado la tabla “codigos_notas” en el "tablespace" ACADEMO, en caso contrario creadla.



CREATE TABLE CODIGOS_NOTAS(CODIGO varchar2(3),DESCRIPCION varchar2(20))TABLESPACE ACADEMOSTORAGE (INITIAL 64KNEXT 64KMINEXTENTS 3MAXEXTENTS 10);

Otorgar privilegios para consultar, insertar, actualizar y borrar registros de la tabla “codigos_notas” al usuario "prueba02".

Conectarse como usuario “prueba02” y realizar las siguientes operaciones:

• Seleccionar todos los registros de la tabla “codigos_notas”.• Insertar en la tabla “codigos_notas” los siguientes registros:

‘0’, ‘NO PRESENTADO’‘1’, ‘APROBADO’

Consultar los registros de auditoría generados para las operaciones anteriores (vista DBA_AUDIT_OBJECT).


SQL> CREATE TABLE CODIGOS_NOTAS 2 (CODIGO varchar2(3), 3 DESCRIPCION varchar2(20)) 4 TABLESPACE ACADEMO 5 STORAGE (INITIAL 64K 6 NEXT 64K 7 MINEXTENTS 3 8* MAXEXTENTS 10)

Table created.

SQL> grant select, insert, update, delete on codigos_notas to prueba02;

Grant succeeded.


SQL> select * from prueba01.codigos_notas;

no rows selected



SQL> insert into prueba01.codigos_notas values ('0','NO PRESENTADO');

1 row created.

SQL> insert into prueba01.codigos_notas values ('1','APROBADO');

1 row created.

SQL> commit;

Commit complete.

SQL> SQL> connect systemEnter password: Connected.

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario, to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss') tiempo_desconexion from dba_audit_session where username='PRUEBA02' order by username,timestamp,logoff_time;

USUARIO_SO USUARIO TIEMPO_CONEXION TIEMPO_DESCONEXION---------- ------------ ------------------ -------------------oracle PRUEBA02 29-11-004 11:55:37oracle PRUEBA02 29-11-004 12:08:54 29-11-2004 12:10:42

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario, to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, substr(owner,1,10) propie, substr(obj_name,1,15) objeto, substr(action_name,1,10) accion from dba_audit_object where username='PRUEBA02' order by os_username,timestamp, owner,obj_name,action_name;

USUARIO_SO USUARIO TIEMPO_CONEXION PROPIE OBJETO ACCION--------------------------------------------------------------------------- oracle PRUEBA02 29-11-004 12:08:54 SYS DUAL SELECToracle PRUEBA02 29-11-004 12:08:54 SYS DUAL SELECToracle PRUEBA02 29-11-004 12:08:54 SYS DUAL SELECToracle PRUEBA02 29-11-004 12:08:54 SYSTEM PRODUCT_PRIVS SELECT



oracle PRUEBA02 29-11-004 12:08:54 SYSTEM PRODUCT_PRIVS SELECToracle PRUEBA02 29-11-004 12:08:54 SYSTEM SQLPLUS_PRODUCT SELECToracle PRUEBA02 29-11-004 12:08:54 SYSTEM SQLPLUS_PRODUCT SELECToracle PRUEBA02 29-11-004 12:09:06 PRUEBA01 CODIGOS_NOTAS SELECToracle PRUEBA02 29-11-004 12:09:19 PRUEBA01 CODIGOS_NOTAS INSERToracle PRUEBA02 29-11-004 12:09:32 PRUEBA01 CODIGOS_NOTAS INSERT

11. Consultar las opciones por defecto de auditoría de objetos (ALL_DEF_AUDIT_OPTS).

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE--- --- --- --- --- --- --- --- --- --- --- --- ----/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

12. Especificar las opciones de auditoría por defecto para los objetos creados en un futuro de forma que se registre información siempre que se produzca un “alter”, “grant”, “insert”, “update” o “delete”.

SQL> audit alter, grant, insert, update, delete on default;

Audit succeeded.

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE--- --- --- --- --- --- --- --- --- --- --- --- ---S/S -/- -/- S/S S/S -/- S/S -/- -/- -/- S/S -/- -/-

El significado de cada una de las columnas es:

-/-: No auditoria por defecto.S/-: Auditado cuando sea exitosa la operación.-/S: Auditado cuando sea fallida la operación.

ALT ... ALTER AUD ... AUDIT COM ... COMMENT



DEL ... DELETE GRA ... GRANT IND ... INDEX INS ... INSERT LOC ... LOCK REN ... RENAME SEL ... SELECT UPD ... UPDATE REF ... REFERENCESEXE ... EXECUTE

13. Desactivar la auditoría de las conexiones a la base de datos.

SQL> select substr(user_name,1,12) usuario, privilege, success,failure from dba_priv_audit_opts order by user_name, privilege;

USUARIO PRIVILEGE SUCCESS FAILURE------------ ---------------------------------------- ---------- ----------PRUEBA01 CREATE SESSION BY ACCESS BY ACCESSPRUEBA02 CREATE SESSION BY ACCESS BY ACCESS ALTER ANY RULE BY ACCESS BY ACCESS CREATE ANY RULE BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS DROP ANY RULE BY ACCESS BY ACCESS EXECUTE ANY RULE BY ACCESS BY ACCESS

7 rows selected.

SQL> noaudit session;

Noaudit succeeded.




USUARIO PRIVILEGE SUCCESS FAILURE------------ ---------------------------------------- ---------- ----------PRUEBA01 CREATE SESSION BY ACCESS BY ACCESSPRUEBA02 CREATE SESSION BY ACCESS BY ACCESS ALTER ANY RULE BY ACCESS BY ACCESS CREATE ANY RULE BY ACCESS BY ACCESS DROP ANY RULE BY ACCESS BY ACCESS EXECUTE ANY RULE BY ACCESS BY ACCESS

6 rows selected.

SQL> noaudit session by prueba01, prueba02;

Noaudit succeeded.


USUARIO PRIVILEGE SUCCESS FAILURE------------ ---------------------------------------- ---------- ---------- ALTER ANY RULE BY ACCESS BY ACCESS CREATE ANY RULE BY ACCESS BY ACCESS DROP ANY RULE BY ACCESS BY ACCESS EXECUTE ANY RULE BY ACCESS BY ACCESS

14. Desactivar la auditoría de uso de SELECT TABLE, DELETE TABLE, UPDATE TABLE e INSERT TABLE (activada en ejercicios anteriores).

SQL> select substr(user_name,1,12) usuario, substr(audit_option, 1,15) opcion, success,failure from dba_stmt_audit_opts order by user_name, audit_option;

USUARIO OPCION SUCCESS FAILURE



------------ --------------- ---------- ---------- DELETE TABLE BY ACCESS BY ACCESS INSERT TABLE BY ACCESS BY ACCESS SELECT TABLE BY ACCESS BY ACCESS UPDATE TABLE BY ACCESS BY ACCESS

SQL> noaudit select table, delete table, update table, insert table;

Noaudit succeeded.

SQL> select substr(user_name,1,12) usuario, substr(audit_option, 1,15) opcion, success,failure from dba_stmt_audit_opts order by user_name, audit_option;

no rows selected

15. Activar la auditoría para cualquier operación de consulta, inserción o borrado que se efectué sobre la tabla “prueba01.codigos_notas”.

SQL> audit select, insert, delete on prueba01.codigos_notas;

Audit succeeded.

SQL> select substr(owner,1,12),substr(OBJECT_NAME,1,12), substr(object_type,1,10), DEL, INS, SEL, UPD from dba_obj_audit_opts where owner='PRUEBA01' order by object_name, object_type;

SUBSTR(OWNER SUBSTR(OBJEC SUBSTR(OB DEL INS SEL UPD------------ ------------ --------- --- --- --- ---PRUEBA01 CHEQUEAR_TAB PROCEDURE -/- -/- -/- -/-PRUEBA01 CODIGOS TABLE -/- -/- -/- -/-PRUEBA01 CODIGOS_NOTA TABLE S/S S/S S/S -/-PRUEBA01 TABLAS_REVIS TABLE -/- -/- -/- -/-

16. Conectado como usuario “prueba01” realizar una consulta del contenido de la tabla “codigos_notas”. Consultar los registros de auditoría generados (DBA_AUDIT_OBJECT).


SQL> select * from codigos_notas;

COD DESCRIPCION



--- --------------------0 NO PRESENTADO1 APROBADO

SQL> insert into codigos_notas values ('3','NOTABLE');

1 row created.

SQL> commit;


SQL> select substr(username,1,10) usuario, to_char(timestamp,'dd-mm-yyyy hh24:mi:ss') fecha, substr(owner,1,10) prop, substr(obj_name,1,15) objeto, ses_actions from dba_audit_object where owner='PRUEBA01' order by timestamp

USUARIO FECHA PROP OBJETO SES_ACTIONS--------------------------------------------------------------------------PRUEBA02 29-11-2004 12:09:06 PRUEBA01 CODIGOS_NOTASPRUEBA02 29-11-2004 12:09:19 PRUEBA01 CODIGOS_NOTASPRUEBA02 29-11-2004 12:09:32 PRUEBA01 CODIGOS_NOTASPRUEBA01 02-12-2004 10:09:30 PRUEBA01 CODIGOS_NOTAS ------S--S------

SES_ACTIONS Resumen de sesion, una cadena de 16 caracteres, uno por cada tipo de accion de la lista ordenada ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, y EXECUTE.Las posicones 14, 15, y 16 estan reservadas para usos futuros. Los caracteres son - para ninguno, S para éxito, F para fallo y B para ambos.

17. Desactivar la auditoría del ejercicio 15.

SQL> noaudit select, insert, delete on prueba01.codigos_notas;

Noaudit succeeded.



18. Desactivar todas las opciones de auditoría de objetos que existen por defecto.

SQL> noaudit all on default;

Noaudit succeeded.

19. Comprobar las características de almacenamiento del “audit trail”. Borrar todos los registros de auditoría correspondientes a la tabla “prueba01.codigos_notas”.

SQL> select substr(owner,1,10) prop,substr(table_name,1,8) tabla,substr(tablespace_name,1,10) espacio, INITIAL_EXTENT, NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_tables where table_name='AUD$';

PROP TABLA ESPACIO INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS

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

SYS AUD$ SYSTEM 10240 16384 1 121

20. Auditar cualquier cambio que se realice en el “audit trail”.

SQL> AUDIT INSERT, UPDATE, DELETE ON SYS.AUD$ BY ACCESS;

Audit succeeded.

SQL> select substr(owner,1,12) prop,substr(OBJECT_NAME,1,12) objeto, substr(object_type,1,10) tipo, DEL, INS, SEL, UPD from dba_obj_audit_opts where owner='SYS' and OBJECT_NAME ='AUD$' order by object_name, object_type

PROP OBJETO TIPO DEL INS SEL UPD------------ ------------ --------- --- --- --- ---SYS AUD$ TABLE A/A A/A -/- A/A

21. Considere un caso hipotético donde quiere auditarse la base de datos en base a los siguientes hechos detectados:

• Se han modificado ciertas cuotas, asignación de espacios de almacenamiento, “tablespaces”, y contraseñas de usuario.



• Se están produciendo un número considerable de abrazos mortales debido a que se están adquiriendo bloqueos exclusivos sobre tablas.

• Se ha detectado el borrado de registros de la tabla SCOTT.EMP

Se sospecha que los usuarios “prueba01” y “prueba02” son los responsables de las acciones anteriores.Determinar las distintas operaciones sobre las que habrá que efectuar una auditoría, así como consultar en las

distintas vistas para obtener la información precisa que permita descartar o confirmar las sospechas que han dado lugar a la activación de la auditoría.

Al usuario "prueba02" se le han asignado todos los permisos posibles sobre scott.emp:

SQL> grant all on scott.emp to prueba02;

Grant succeeded.

Para activar la auditoría se ejecutara el siguiente orden de sentencias:

SQL> audit alter, INDEX, RENAME ON DEFAULT BY SESSION;

Audit succeeded.

SQL> AUDIT SESSION BY prueba01, prueba02;

Audit succeeded.

SQL> AUDIT ALTER USER;

Audit succeeded.

SQL> AUDIT DROP USER;

Audit succeeded.

SQL> AUDIT LOCK TABLE BY ACCESS WHENEVER SUCCESSFUL;

Audit succeeded.

SQL> AUDIT DELETE ON scott.emp BY ACCESS WHENEVER SUCCESSFUL;

Audit succeeded.

Supongamos que el usuario prueba01 ejecuta las sentencias:

SQL> connect prueba01



Enter password: Connected.

SQL> ALTER USER prueba03 QUOTA 0 ON users;ALTER USER prueba03 QUOTA 0 ON users*ERROR at line 1:ORA-01031: insufficient privileges

SQL> DROP USER prueba04;DROP USER prueba04*ERROR at line 1:ORA-01031: insufficient privileges

Supongamos que el usuario prueba02 ejecuta las sentencias:


SQL> LOCK TABLE scott.emp IN EXCLUSIVE MODE;

Table(s) Locked.

SQL> DELETE FROM scott.emp WHERE mgr = 7698;

5 rows deleted.

SQL> ALTER TABLE scott.emp STORAGE (NEXT 500K);

Table altered.

SQL> CREATE INDEX scott.ename_index ON scott.emp (ename);CREATE INDEX scott.ename_index ON scott.emp (ename) *ERROR at line 1:ORA-01031: insufficient privileges

SQL> DELETE FROM scott.emp WHERE empno = 8000;

0 rows deleted.

Recuperamos ahora información sobre los registros de auditoría:



• Opciones de auditoría de sentencias fijadas.


SQL> select user_name, audit_option,success, failure from sys.dba_stmt_audit_opts;

USER_NAME AUDIT_OPTION SUCCESS FAILURE-----------------------------------------------------------------PRUEBA02 CREATE SESSION BY ACCESS BY ACCESSPRUEBA01 CREATE SESSION BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS LOCK TABLE BY ACCESS NOT SET

• Opciones de auditoría de privilegios.

SQL> select user_name, privilege,success, failure from sys.dba_priv_audit_opts;

USER_NAME PRIVILEGE SUCCESS FAILURE---------------------------------------------------------------PRUEBA02 CREATE SESSION BY ACCESS BY ACCESSPRUEBA01 CREATE SESSION BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS

• Opciones de auditoría para objetos del esquema SCOTT.

SQL> SELECT * FROM sys.dba_obj_audit_opts WHERE owner =



'SCOTT' AND object_name LIKE 'EMP%';

OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI------- -------------- --------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---SCOTT EMP TABLE -/- -/- -/- A/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

La información debe interpretarse de la siguiente forma:

• El carácter "-" indica que la opción de auditoría no esta fijada.

• EL carácter "S" indica que la opción de auditoría esta fijada “BY SESSION”.

• El carácter "A" indica que la opción de auditoría esta fijada “BY ACCESS”.

• Cada opción de auditoría puede ser para “WHENEVER SUCCESSFUL” y “WHENEVER NOT SUCCESSFUL”; las dos opciones están separadas por "/".

• Información de auditoría para la sentencia “AUDIT SESSION”.

SQL> select substr(os_username,1,10) usuario_so, substr(username,1,12) usuario, to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, to_char(logoff_time,'dd-mm-yyyy hh24:mi:ss') tiempo_desconexion from dba_audit_session order by username,timestamp,logoff_time;

USUARIO_SO USUARIO TIEMPO_CONEXION TIEMPO_DESCONEXION---------- ------------ ------------------ -------------------oracle PRUEBA01 29-11-004 11:53:50 29-11-2004 11:55:37oracle PRUEBA01 29-11-004 12:07:32 29-11-2004 12:08:54curso01 PRUEBA01 02-12-004 10:37:26 02-12-2004 10:38:25oracle PRUEBA02 29-11-004 11:55:37oracle PRUEBA02 29-11-004 12:08:54 29-11-2004 12:10:42curso01 PRUEBA02 02-12-004 10:38:25 02-12-2004 10:40:03oracle SYSTEM 29-11-004 11:53:44 29-11-2004 11:53:50oracle SYSTEM 29-11-004 12:10:42 29-11-2004 12:22:42



8 rows selected.

• Registros de auditoria generados para objetos.

SQL> select substr(username,1,12) usuario, to_char(timestamp,'dd-mm-yyy hh24:mi:ss') tiempo_conexion, substr(owner,1,10) propie, substr(obj_name,1,15) objeto, substr(action_name,1,15) accion from dba_audit_object where owner='SCOTT' order by os_username,usuario, timestamp, owner,obj_name,action_name;

USUARIO TIEMPO_CONEXION PROPIE OBJETO ACCION--------------------------------------------------------------------PRUEBA02 02-12-004 10:38:35 SCOTT EMP LOCKPRUEBA02 02-12-004 10:38:45 SCOTT EMP DELETEPRUEBA02 02-12-004 10:39:12 SCOTT EMP DELETE

Debe tenerse en cuenta que la opción para auditar las sentencias alter, index, ... es posterior a la creación del objeto.

Si, explícitamente, se audita la tabla “emp”.

SQL> audit alter, INDEX, RENAME ON scott.emp by session;

Audit succeeded.

SQL> connect prueba02Enter password: Connected.SQL> SQL> ALTER TABLE scott.emp STORAGE (NEXT 500K);

Table altered.

SQL> CREATE INDEX scott.ename_index ON scott.emp (ename);CREATE INDEX scott.ename_index ON scott.emp (ename) *ERROR at line 1:



ORA-01031: insufficient privileges



USUARIO TIEMPO_CONEXION PROPIE OBJETO ACCION--------------------------------------------------------------------PRUEBA02 02-12-004 10:38:35 SCOTT EMP LOCKPRUEBA02 02-12-004 10:38:45 SCOTT EMP DELETEPRUEBA02 02-12-004 10:39:12 SCOTT EMP DELETEPRUEBA02 03-12-004 10:13:29 SCOTT EMP SESSION REC

SQL> SELECT * FROM sys.dba_obj_audit_opts WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';

OWNER OBJECT_NAME OBJECT_TY ALT AUD------------------------------------------- --------- --- ---COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI--- --- --- --- --- --- --- --- --- --- --- --- --- ---SCOTT EMP TABLE S/S -/--/- A/- -/- S/S -/- -/- S/S -/- -/- -/- -/- -/- -/- -/-

Si se audita ahora por “acceso”.

SQL> audit alter, INDEX, RENAME ON scott.emp by access;

Audit succeeded.


SQL> ALTER TABLE scott.emp STORAGE (NEXT 1500K);



Table altered.

SQL> CREATE INDEX scott.ename_index ON scott.emp (ename);CREATE INDEX scott.ename_index ON scott.emp (ename) *ERROR at line 1:ORA-01031: insufficient privileges



USUARIO TIEMPO_CONEXION PROPIE OBJETO------------ ------------------ ---------- ---------------ACCION-------------------------PRUEBA02 02-12-004 10:38:35 SCOTT EMPLOCK

PRUEBA02 02-12-004 10:38:45 SCOTT EMPDELETE

PRUEBA02 02-12-004 10:39:12 SCOTT EMPDELETE

PRUEBA02 03-12-004 10:13:29 SCOTT EMPSESSION REC

PRUEBA02 03-12-004 10:16:45 SCOTT EMPALTER TABLE

Observemos que prueba02 no tiene permiso para crear índices.




SQL> select * from dba_sys_privs where grantee='PRUEBA02';

no rows selected

SQL> select * from dba_role_privs where grantee='PRUEBA02'

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---PRUEBA02 CONEXION NO YES

SQL> select * from dba_role_privs where grantee='CONEXION'

no rows selected

SQL> select * from dba_sys_privs where grantee='CONEXION'

GRANTEE PRIVILEGE ADM-------------------------------------------------------------CONEXION CREATE SESSION NO

Se le otorga permiso y se prueba a crear un índice como prueba02 sobre scott.emp. Aunque falle, registra el apunte.

SQL> grant create table to prueba02;

Grant succeeded.


SQL> CREATE INDEX scott.ename_index ON scott.emp (kk);CREATE INDEX scott.ename_index ON scott.emp (kk) *ERROR at line 1:ORA-00904: "KK": invalid identifier





USUARIO TIEMPO_CONEXION PROPIE OBJETO ACCION--------------------------------------------------------------------PRUEBA02 02-12-004 10:38:35 SCOTT EMP LOCKPRUEBA02 02-12-004 10:38:45 SCOTT EMP DELETEPRUEBA02 02-12-004 10:39:12 SCOTT EMP DELETEPRUEBA02 03-12-004 10:13:29 SCOTT EMP SESSION RECPRUEBA02 03-12-004 10:16:45 SCOTT EMP ALTER TABLEPRUEBA02 03-12-004 10:24:46 SCOTT ENAME_INDEX CREATE INDEX

6 rows selected.



PRACTICAS TEMA 10.

COPIAS DE SEGURIDAD.

1. Revisar la estructura de la base de datos. Indicar si cumple con las indicaciones OFA y el contenido de cada uno de los sistemas de ficheros.

Desde sistema operativo, mediante las sentencias "cd" y "ls -al"

2. Comprobar que usuarios y roles tienen asignado el rol “exp_full_database”.

SQL> select * from dba_role_privs where granted_role='EXP_FULL_DATABASE';

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---DBA EXP_FULL_DATABASE NO YESSYS EXP_FULL_DATABASE YES YES

3. ¿Podría un usuario que tuviera asignado el rol “dba” realizar una exportación total de la base de datos?, ¿por qué?.

4. Asignar el rol ”exp_full_database” al rol “admin”.

SQL> grant exp_full_database to admin;

Grant succeeded.

SQL> select * from dba_role_privs where granted_role='EXP_FULL_DATABASE';

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---DBA EXP_FULL_DATABASE NO YES



SYS EXP_FULL_DATABASE YES YESADMIN EXP_FULL_DATABASE NO YES

5. En el sistema de ficheros /export/<nombre_bbdd>, crear un fichero de parámetros llamado “par_admin01” que permita realizar una exportación total de la base de datos, incluyendo sus datos y lo permisos sobre todos los objetos, a un fichero llamado “total_admin01.dmp” (opciones “file”, “full”, “log”). Realizar la exportación como usuario “administrador”.

El registro de la exportación se guardará en el fichero “total_admin01.log”.

" Conectarse como usuario propietario de la base de datos." Situarse en el direcorio /export/<nombre_bbdd>. Editar con "vi" el fichero par_admin01.

buffer=102400file=/export/CURSO01/total_admin01.dmplog=/export/CURSO01/total_admin01.logfull=y

" Lanzar la exportación (se conectara como usuario "administrador").

/export/CURSO01 (CURSO01)> exp parfile=par_admin01

Export: Release 9.2.0.1.0 - Production on Jue Dic 9 12:37:16 2004


Usuario: administradorContraseña:

Conectado a: Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning and Oracle Label Security optionsJServer Release 9.2.0.1.0 - ProductionExportación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16

Exportando toda la base de datos .... exportando definiciones de tablespace. exportando perfiles. exportando definiciones de usuario. exportando roles. exportando costos de recursos. exportando definiciones de segmentos de rollback



. exportando enlaces a la base de datos

. exportando números de secuencia

. exportando alias de directorios

. exportando espacios de nombres de contexto

...

6. Editar el fichero “total_admin01.log” y comprobar si se ha producido algún error. ¿Qué información aparece en la ultima fila del fichero?.

Debe editarse el fichero usando el editor "vi" (también puede hacerse con un “tail”). Las ultimas líneas mostraran el siguiente mensaje:

/export/CURSO01 (CURSO01)> tail -f total_admin01.log. exportando vistas materializadas. exportando logs de instantáneas. exportando colas de trabajo. exportando grupos de refrescamiento y secundarios. exportando dimensiones. exportando acciones y objetos de procedimiento post-esquema. exportando tabla de historial de usuario. exportando opciones de auditoría por defecto y del sistema. exportando estadísticasLa exportación ha terminado correctamente y sin advertencias.

7. Editar el fichero “total_admin01.log” y verificar el orden de copia de los objetos de la base de datos, ¿cuál es?.

8. Comprobar si se ha exportado al fichero “total_admin01.dmp” el usuario “SYS”. ¿Y el “SYSTEM”?.

No en el caso del SYS. Sí en el caso de SYSTEM.

9. Exportar todos los registros correspondientes al “audit. trail”. Crear un fichero de parámetros de nombre "exporta_aud" con las siguientes líneas:

buffer=102400file=/export/CURSO01/exporta_aud.dmplog=/export/CURSO01/exporta_aud.logtables=(SYS.AUD$)

/export/CURSO01 (CURSO01)> exp parfile=exporta_aud


Copyright (c) 1982, 2002, Oracle Corporation. All rights



reserved.

Usuario: systemContraseña:


Exportando las tablas especificadas a través de la Ruta de Acceso Convencional ...El usuario actual ha cambiado a SYS. exportando la tabla AUD$ 114 filas exportadasLa exportación ha terminado correctamente y sin advertencias.

10. Como usuario “scott”, realizar una exportación de su esquema. Los parámetros se almacenarán en un fichero llamado “par_scott03”, la exportación en el fichero “scott_scott03.dmp” y el registro en el fichero “scott_scott03.log”. ¿Se ha realizado con éxito la exportación?. Usar las mismas opciones que en el ejercicio anterior.

El fichero de parámetros tendrá el siguiente contenido:

buffer=102400file=/export/CURSO01/scott_scott03.dmplog=/export/CURSO01/scott_scott03.logowner=(SCOTT)compress=y

/export/CURSO01 (CURSO01)> exp parfile=par_scott03



Usuario: scottContraseña:




. exportando acciones y objetos de procedimiento pre-esquema

. exportando nombres de biblioteca de funciones ajenas para el usuario SCOTT . exportando sinónimos de tipo público. exportando sinónimos de tipo privado. exportando definiciones de tipos de objetos para el usuario SCOTT Exportando los objetos de SCOTT .... exportando enlaces a la base de datos. exportando números de secuencia. exportando definiciones de agrupamiento. exportando las tablas de SCOTT a través de la Ruta de Acceso Convencional .... exportando la tabla BONUS 0 filas exportadas. exportando la tabla DEPT 5 filas exportadas. exportando la tabla EMP 9 filas exportadas. exportando la tabla SALGRADE 5 filas exportadas. exportando sinónimos. exportando vistas. exportando procedimientos almacenados. exportando operadores. exportando restricciones de integridad referencial. exportando disparadores. exportando tipos de índice. exportando índices bitmap, funcionales y extensibles. exportando acciones de posttables. exportando vistas materializadas. exportando logs de instantáneas. exportando colas de trabajo. exportando grupos de refrescamiento y secundarios. exportando dimensiones. exportando acciones y objetos de procedimiento post-esquema. exportando estadísticasLa exportación ha terminado correctamente y sin advertencias.

11. Como usuario “scott”, intentar realizar una exportación total de la base de datos. Los parámetros se almacenarán en un fichero llamado “par_scott04”, la exportación en el fichero “total_scott04.dmp” y el registro en el fichero “total_scott04.log”. ¿Se ha realizado con éxito la exportación?, ¿por qué?.



Copyright (c) 1982, 2002, Oracle Corporation. All rights



reserved.


Conectado a: Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning and Oracle Label Security optionsJServer Release 9.2.0.1.0 - ProductionEXP-00023: debe ser DBA para realizar una exportación de la base de datos completa o del tablespace(2)U(suarios) o (3)T(ablas): (2)U >

12. Conectado como usuario “scott”, realizar una exportación de sus tablas “emp” y “dept”. Los parámetros se almacenarán en un fichero llamado “par_scott05”, la exportación en el fichero “tablas_scott05.dmp” y el registro en el fichero “tablas_scott05.log”. ¿Hubo éxito en la exportación?.

El contenido del fichero de parámetros será:

buffer=102400file=/export/CURSO01/tablas_scott05.dmplog=/export/CURSO01/tablas_scott05.logtables=(EMP, DEPT)compress=y






Exportando las tablas especificadas a través de la Ruta de Acceso Convencional .... exportando la tabla EMP 9 filas exportadas. exportando la tabla DEPT 5



filas exportadasLa exportación ha terminado correctamente y sin advertencias.

13. Como usuario “prueba01”, intentar realizar una exportación de las tablas “scott.emp” y “scott.dept”. Los parámetros se almacenarán en un fichero llamado “par_prueba0101”, la exportación en el fichero “tablas_prueba0101.dmp” y el registro en el fichero “tablas_prueba0101.log”. ¿Qué sucede?.


buffer=102400file=/export/CURSO01/tablas_prueba0101.dmplog=/export/CURSO01/tablas_prueba0101.logtables=(SCOTT.EMP, SCOTT.DEPT)compress=y

/export/CURSO01 (CURSO01)> exp parfile=par_prueba0101



Usuario: prueba01Contraseña:


Exportando las tablas especificadas a través de la Ruta de Acceso Convencional ...EXP-00009: no tiene privilegios para exportar la tabla SCOTT de EMPEXP-00009: no tiene privilegios para exportar la tabla SCOTT de DEPTLa exportación ha terminado correctamente pero con advertencias.

14. Como usuario “prueba01”, realizar la exportación de la tabla “inexistente”. Los parámetros se almacenarán en un fichero llamado “par_prueba0102”, la exportación en el fichero “tablas_prueba0102.dmp” y el registro en el fichero “tablas_prueba0102.log”. ¿Se genera algún mensaje de error?; si es así, ¿qué prefijo tienen?. Si se ha producido algún mensaje de error, buscar en la documentación en línea su significado.




buffer=102400file=/export/CURSO01/tablas_prueba0102.dmplog=/export/CURSO01/tablas_prueba0102.logtables=(INEXISTENTE)compress=y

/export/CURSO01 (CURSO01)> exp parfile=par_prueba0102



Usuario: prueba01Contraseña:


Exportando las tablas especificadas a través de la Ruta de Acceso Convencional ...EXP-00011: PRUEBA01.INEXISTENTE no existeLa exportación ha terminado correctamente pero con advertencias.

15. Comprobar el valor del parámetro db_block_size en la base de datos.

/export/CURSO01 (CURSO01)> sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Jue Dic 9 13:21:11 2004


Introduzca el nombre de usuario: systemIntroduzca la contraseña:

Conectado a:Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production



With the Partitioning and Oracle Label Security optionsJServer Release 9.2.0.1.0 - Production

SQL> show parameters db_block_size

NAME TYPE VALUE---------------------------------------------------------db_block_size integer 2048

16. Realizar de nuevo los ejercicios 5 y 10 usando el modo directo de exportación. Defina el valor del parámetro RECORDLENGTH.El nombre de los ficheros usados será:

F.parámetros F.exportación F.registro

Ejercicio 6 Pard_admin01 Totald_admin01.dmp Totald_admin01.logEjercicio 10 Pard_admin02 Sistemad_admin02.dmp Sistemad_admin02.log

En el caso, por ejemplo, del ejercicio 10, el fichero de parámetros quedaría de esta forma:

file=/export/CURSO01/scottd_scott03.dmplog=/export/CURSO01/scottd_scott03.logowner=(SCOTT)compress=ydirect=yRECORDLENGTH=65535

/export/CURSO01 (CURSO01)> exp parfile=pard_scott03




Conectado a: Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning and Oracle Label Security optionsJServer Release 9.2.0.1.0 - ProductionExportación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16. exportando acciones y objetos de procedimiento pre-esquema. exportando nombres de biblioteca de funciones ajenas para el



usuario SCOTT . exportando sinónimos de tipo público. exportando sinónimos de tipo privado. exportando definiciones de tipos de objetos para el usuario SCOTT Exportando los objetos de SCOTT .... exportando enlaces a la base de datos. exportando números de secuencia. exportando definiciones de agrupamiento. exportando las tablas de SCOTT a través de la Ruta de Acceso Directa .... exportando la tabla BONUS 0 filas exportadas. exportando la tabla DEPT 5 filas exportadas. exportando la tabla EMP 9 filas exportadas. exportando la tabla SALGRADE 5 filas exportadas. exportando sinónimos. exportando vistas. exportando procedimientos almacenados. exportando operadores. exportando restricciones de integridad referencial. exportando disparadores. exportando tipos de índice. exportando índices bitmap, funcionales y extensibles. exportando acciones de posttables. exportando vistas materializadas. exportando logs de instantáneas. exportando colas de trabajo. exportando grupos de refrescamiento y secundarios. exportando dimensiones. exportando acciones y objetos de procedimiento post-esquema. exportando estadísticasLa exportación ha terminado correctamente y sin advertencias.

17. Conectarse como usuario “scott” y eliminar la tabla”emp”.

SQL> drop table emp;

Tabla borrada.

18. Como usuario “administrador”, importar la tabla “scott.emp” desde el fichero “scott_scott03.dmp”. Crear un fichero de parámetros llamado “par_imp_admin01” (parámetros file, fromuser y tables), el registro se guardara en el fichero “imp_tabla_emp”. ¿Termina con éxito la importación?.



El contenido del fichero de parámetros de importación es:

buffer=102400file=/export/CURSO01/scott_scott03.dmplog=/export/CURSO01/imp_tabla_emp.logtables=(EMP)fromuser=scotttouser=scott

/export/CURSO01 (CURSO01)> imp parfile=par_imp_admin01

Import: Release 9.2.0.1.0 - Production on Jue Dic 9 13:47:36 2004



Conectado a: Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning and Oracle Label Security optionsJServer Release 9.2.0.1.0 - Production

Archivo de exportación creado por EXPORT:V09.02.00 a través de la ruta de acceso convencional

Advertencia: Los objetos fueron exportados por SCOTT, no por usted

importación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16IMP-00007: tiene que ser DBA para importar objetos en la cuenta de otro usuarioIMP-00000: La importación no ha terminado correctamente

19. Comprobar que usuarios y roles tienen asignado el rol “imp_full_database”.

SQL> select * from dba_role_privs where granted_role='IMP_FULL_DATABASE';

GRANTEE GRANTED_ROLE ADM DEF------------------------------ ------------------------------ --- ---DBA IMP_FULL_DATABASE NO YES



SYS IMP_FULL_DATABASE YES YES

20. Asignar el rol “imp_full_database” al usuario “administrador”.

SQL> grant imp_full_database to administrador;

Concesión terminada correctamente.

21. Realizar de nuevo el ejercicio 18.




Usuario: administrador Contraseña:




importación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16. importando objetos de SCOTT en SCOTT. importando la tabla "EMP" 9 filas importadasActivando las restricciones...La importación ha terminado correctamente pero con advertencias.

22. Comprobar si los usuarios “prueba01” y “prueba02” tienen cuota en el “tablespace” USERS. En caso de que no la tengan, asignar una cuota de 1M a cada uno de ellos.

SQL> select tablespace_name, max_bytes from dba_ts_quotas where tablespace_name='USERS' and username in ('PRUEBA01','PRUEBA02');



ninguna fila seleccionada

SQL> alter user prueba01 quota 1M on users;

Usuario modificado.

SQL> alter user prueba02 quota 1M on users;

Usuario modificado.

SQL> select tablespace_name, max_bytes from dba_ts_quotas where tablespace_name='USERS' and username in ('PRUEBA01','PRUEBA02')

TABLESPACE_NAME MAX_BYTES------------------------------ ----------USERS 1048576USERS 1048576

23. Como usuario “administrador”, realizar una importación en el esquema de usuario “prueba01” de las tablas “emp” y “dept” pertenecientes al esquema de usuario “scott” (fichero “tablas_scott05.dmp”). Crear un fichero de parámetros llamado “par_imp_admin02” (parámetros file, log, grants –N-, fromuser, touser y tables); el registro se guardara en el fichero “imp_tablas_prueba01”.


buffer=102400file=/export/CURSO01/tablas_scott05.dmplog=/export/CURSO01/imp_tablas_prueba01tables=(EMP, DEPT)fromuser=scotttouser=prueba01grants=N





Conectado a: Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning and Oracle Label Security options



JServer Release 9.2.0.1.0 - Production



importación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16. importando objetos de SCOTT en PRUEBA01. importando la tabla "EMP" 9 filas importadas. importando la tabla "DEPT" 5 filas importadasActivando las restricciones...La importación ha terminado correctamente pero con advertencias.

24. Idem en el esquema “prueba02” para todo el esquema “scott” (fichero “scott_scott03.dmp”). Crear un fichero de parámetros llamado “par_imp_admin03” (parámetros file, log, grants –N-, full y touser); el registro se guardara en el fichero “imp_esquema_scott_prueba02”.


buffer=102400file=/export/CURSO01/scott_scott03.dmplog=/export/CURSO01/imp_esquema_scott_prueba02full=ygrants=Ntouser=prueba02










importación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16. importando objetos de SCOTT en PRUEBA02. importando la tabla "BONUS" 0 filas importadas. importando la tabla "DEPT" 5 filas importadas. importando la tabla "EMP" 9 filas importadas. importando la tabla "SALGRADE" 5 filas importadasActivando las restricciones...La importación ha terminado correctamente y sin advertencias.

25. Como usuario “administrador", realizar una importación a fichero usando el fichero “scott_scott03.dmp” (parámetro SHOW=Y). Crear un fichero de parámetros llamado “par_impfichero_admin04”; el registro se guardara en el fichero “imp_scott_fichero”.


buffer=102400file=/export/CURSO01/scott_scott03.dmplog=/export/CURSO01/imp_scott_ficherofull=yshow=y

/export/CURSO01 (CURSO01)> imp parfile=par_impfichero_admin04









importación realizada en el juego de caracteres WE8ISO8859P15 y el juego de caracteres NCHAR AL16UTF16. importando objetos de SCOTT en ADMINISTRADOR "BEGIN " "sys.dbms_logrep_imp.instantiate_schema(schema_name=>'SCOTT', export_db_name" "=>'CURSO01', inst_scn=>'2031207');" "COMMIT; END;" "CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER" ", "COMM" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI" "TIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FRE" "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" LOGGING" " NOCOMPRESS". . ignorando la tabla "BONUS"

"CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR" "CHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6" "5536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS" " 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" LOGGING NOCOM" "PRESS". . ignorando la tabla "DEPT"

"CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO" ) PCTFREE 10 INITRANS 2 " "MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 21474" "83645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TAB" "LESPACE "SYSTEM" LOGGING" "ALTER TABLE "DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING I" "NDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 65536 MI" "NEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS" " 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" LOGGING ENABLE "...El resultado de la importacion, las sentencias de creación, se almacenará en el fichero de log.


Documents

PRACTICAS TEMA 1. - Cursos Administración Oracle …cursos.atica.um.es/DBA9i1/pdf/practicas/CursoDBA9i1_practicas_2p.pdf · Asignar la variable NLS_LANG para nuestro país y comprobar