Laboratorio: Creación manual de base de datos en Oracle 11gR2

LABORATORIO CREACION MANUAL (sin usar DBCA) DE BASE DE DATOS ORACLE

Este es un laboratorio que hice para mis alumnos de www.duoc.cl y me pareció interesante compartirlo con la comunidad en internet.

El ambiente es un Linux (oracle linux, redhat, Centos o similar). El software oracle es versión 11g R2. El software está instalado en un usuario llamado «oracle»

El prompt # para linea de comando asume usuario root

El prompt $ para linea de comando asume usuario oracle

Debe tener cuidado con el cut & paste, porque podria haber transaformación de algunos caracteres, especialmente las cremillas simples ‘

Dos cremillas simples seguidas » podria confundirse con doble cremilla »

1.- Permita el uso de Xwindows a usuarios distintos a root

# xhost +

2.- Abra una ventana como usuario oracle

# su – oracle

3.- Verifique variables de ambiente $ORACLE_HOME , $ORACLE_SID, $ORACLE_BASE

ejemplo $ echo $ORACLE_HOME

4.- Defina nombre de la la variable de instancia a valor «orion»

$ ORACLE_SID=orion; export ORACLE_SID

Esto operará exclusivamente en la ventana donde ejecute el comando. En el resto de las ventanas oracle seguirá operando el valor SID=DUOC
Si quiere trabajar en la instancia ORION siempre deberá ejecutar antes, como usuario oracle

$ ORACLE_SID=orion; export ORACLE_SID

5.- Preparacion de archivo de parametros initorion.ora

$ cd $ORACLE_HOME/dbs

Copie y pegue los siguientes valores al archivo «initorion.ora»
Lea detenidamente los parametros.

$ vi initorion.ora

*.audit_trail=’db’ # se guardará auditoria en la base de datos, tabla AUD$
*.compatible=’11.2.0.0.0′ #parametro que define compatibilidad con versiones previas de oracle
*.control_files=’/u01/app/oracle/oradata/orion/control01.ctl’, ‘/u02/oradata/orion/control02.ctl’ # nombre y ubicaciones de archivos de control
*.db_block_size=8192 #tamaño de bloque de la base de datos que se creará. No se puede modificar una vez creada la BD
*.db_domain=’duoc.cl’ # dominio para el nombre global de la base de datos
*.db_name=’orion’ #nombre de la base de datos
*.db_recovery_file_dest=» #directorio de la flash recovery area
#*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01/app/oracle’ # directorio de los archivos de diagnóstico, entre ellos el archivo de alerta alert_orion.log, parametro nuevo release 11g
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orionXDB)’
*.job_queue_processes=1000
*.log_archive_format=’orion_%t_%s_%r.dbf’
*.memory_target=422576128  #define tamaño de la SGA y PGA, parametro nuevo release 11g
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDO_ORION_TBS’

El simbolo # es para comentar una linea
grabe y Salga de vi

:wq
6.- Vaya al directorio home del usuario oracle

$ cd /home/oracle
$ pwd

En el directorio home de oracle crear archivo «crea_orion_db.sql»

$ vi crea_orion_db.sql

copie y pegue el siguiente comando

CREATE DATABASE orion
USER SYS IDENTIFIED BY  Duoc_2011
USER SYSTEM IDENTIFIED BY Duoc_2011
LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/orion/redo01a.log’, ‘/u02/oradata/orion/redo01b.log’) SIZE 100M,
GROUP 2 (‘/u01/app/oracle/oradata/orion/redo02a.log’, ‘/u02/oradata/orion/redo02b.log’) SIZE 100M,
GROUP 3 (‘/u01/app/oracle/oradata/orion/redo03a.log’, ‘/u02/oradata/orion/redo03b.log’) SIZE 100M
— 3 grupos con dos miembros cada grupo
MAXLOGFILES 5  — la base de datos puede tener hasta 5 grupos de redolog
MAXLOGMEMBERS 5 — cada grupo puede tener hasta 5 miembros
MAXLOGHISTORY 1000 — To view how often log switches occur in your database, you can query the dynamic performance view V$LOG_HISTORY
MAXDATAFILES 100 — cantidad maxima de datafiles en la BD
CHARACTER SET WE8ISO8859P1 — set de caracteres para español
NATIONAL CHARACTER SET AL16UTF16 — set de caracteres para columnas NCHAR, NLOG,  (japones, coreano, chino, etc)
EXTENT MANAGEMENT LOCAL — tablespace SYSTEM tiene administracion de extents local
DATAFILE ‘/u01/app/oracle/oradata/orion/system01.dbf’ SIZE 100M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED  –datafile de tablespace system
SYSAUX DATAFILE ‘/u01/app/oracle/oradata/orion/sysaux01.dbf’ SIZE 325M REUSE –datafile de tablespace sysaux
DEFAULT TABLESPACE users –tablespace default de la base de datos, en este caso USERS
DATAFILE ‘/u01/app/oracle/oradata/orion/users01.dbf’– datafile asociado al tablespace USERS
SIZE 20M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 — temporary tablespace de la base de datos
TEMPFILE ‘/u01/app/oracle/oradata/orion/temp01.dbf’ –tempfile del tablespace temporal default
SIZE 100M REUSE
UNDO TABLESPACE undo_orion_tbs –tablespace de UNDO, para guardar las imagenes que permiten realizar ROLLBACK
DATAFILE ‘/u01/app/oracle/oradata/orion/undo_orion_tbs01.dbf’
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
grabe y Salga de vi

:wq
Lea detenidamente la estructura del comando CREATE DATABASE

7.- Asegurese que TODOS los directorios referenciados en los archivos $ORACLE_HOME/dbs/initorion.ora y /home/oracle/crea_orion_db.sql , anteriormente creados existan.

Por ejemplo

$ ls /u01/app/oracle/oradata/orion/
$ ls /u02/oradata/orion/

Si no existen los puede crear con mkdir

$ mkdir -p /u01/app/oracle/oradata/orion/
$ mkdir -p /u02/oradata/orion/
8.- Además en el archivo de parámetros indicará que la base de datos que se va a crear será formateada en bloques de
tamaño 8K  DB_BLOCK_SIZE=8192

10.- No olvide si abre otra ventana xterm, Preparar la variable de ambiente para poder crear la instancia «orion»
$ ORACLE_SID=orion; export ORACLE_SID

11.- Verifique que la variable quedó bien definida
$ echo $ORACLE_SID
orion

12.- Ahora procederá a crear la base de datos utilizando los siguientes pasos.
12.1 Invoque sqlplus e inicie la base de datos en estado NOMOUNT

SQL> startup nomount pfile=?/dbs/initorion.ora

ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             260049280 bytes
Database Buffers          155189248 bytes
Redo Buffers                6094848 bytes

12.2 Ejecute el archivo de comandos CREATE DATABASE
SQL> @crea_orion_db

Database created.

La base está creada y abierta.
Si algo le falla, baje la instancia y elimite cualquier archivo intermedio que se haya creado

$ rm /u01/app/oracle/oradata/orion/*
$ rm /u02/oradata/orion/*
13 Ahora abrirá otra ventana para monitorear el archivo de alerta y ver como avanza la creacion de la base de datos

# tail -f /u01/app/oracle/diag/rdbms/orion/orion/trace/alert_orion.log

14.- Consulte las vistas v$datafile, v$controlfile, DICTIONARY, V$tablespace y DBA_TABLESPACES, v$fixed_tables

¿Qué sucede?

15.- Cuantos grupos de redolog tiene la base de datos test?

SQL>  SELECT * FROM V$LOG;

16.- Cuantos archivos de redolog tiene la base de datos orion? ¿cuantos miembros tiene cada grupo?

SQL>  SELECT * FROM V$LOGFILE;

17.- Ahora desde la consola de administracion con la base de datos abierta procederá a crear el catalogo (diccionario) y la opción
procedural (PL/SQL)

SQL>  @?/rdbms/admin/catalog.sql

Consulte las vistas DBA_USERS, DBA_REGISTRY, DBA_TABLESPACES

SQL>  select username from dba_users;
SQL>  @?/rdbms/admin/catproc.sql

Consulte las vistas DBA_USERS, DBA_REGISTRY, DBA_TABLESPACES

SQL>  select username from dba_users;
SQL>  select tablespace_name from dba_tablespaces;
SQL>  @?/sqlplus/admin/pupbld.sql
CATALOG.SQL  Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
CATPROC.SQL  Runs all scripts required for or used with PL/SQL.
PUPBLD.SQL  Required for SQL*Plus. Enables SQL*Plus to disable commands by user.

18.- Repita el paso 14.

19.- ¿Cuantos tablespaces tiene su base de datos?

SQL> select tablespace_name from dba_tablespaces;
20.- Qué usuarios existen en su base de datos?

SQL>  SELECT username FROM dba_users;

http://www.orafaq.com/wiki/List_of_default_database_users

21.- Qué tamaño tiene el tablespace system (originalmente se creó con tamaño inicial de 100M)

22.- Va a crear 2 tablespaces DUOC_DATA y DUOC_INDEX con administración de extents local

SQL>  CREATE TABLESPACE DUOC_DATA
DATAFILE ‘/u02/oradata/orion/duoc_data01.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL ;

SQL>  CREATE TABLESPACE DUOC_INDEX
DATAFILE ‘/u02/oradata/orion/duoc_index01.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL ;

23.- Verifique las propiedades de los tablespaces recien creados
SQL>  set linesize 200
SQL>  SELECT * FROM DBA_TABLESPACES;

24.- Crear dos usuarios llamados DUOC1 Y DUOC2 con password «duoc», con default tablespace DUOC_DATA

SQL>  CREATE USER duoc1 IDENTIFIED BY duoc
DEFAULT TABLESPACE duoc_data;
SQL>  CREATE USER duoc2 IDENTIFIED BY duoc
DEFAULT TABLESPACE duoc_data;
25.-  Consulte la vista DBA_USERS y determine cual es el tablespace temporal asociado tanto a duoc1 como a duoc2. Compruebe
que la password está guardada en su version de hash.
26.-  Busque y analice el archivo de alerta. Qué eventos fueron registrados en él

En una ventana conectado como oracle

$ tail -f /u01/app/oracle/diag/rdbms/orion/orion/trace/alert_orion.log
27.- ¿Como verificaría que la base de datos está formateada en bloques de 8K?

28.- Abra dos ventanas adicionales (como Oracle) y conectese a los usuarios que recién ha creado (duoc1 y duoc2 respectivamente)

$ sqlplus duoc1/duoc

¿qué sucede? ¿ qué error aparece? anótelo.

29.- Resuelva el problema de privilegios. Desde una consola de administración
SQL> GRANT CONNECT, RESOURCE TO DUOC1, DUOC2

El ROL CONNECT se le entrega a los usuarios finales, en este rol existe el privilegio de crear sesiones

El rol RESOURCE se le entrega a los desarrolladores, para puedan crear tablas, vistas, secuencias, etc. en su esquema.
30. repita el paso 28.
31.- En el usuario duoc1 crear la tabla orion que a continuación se indica

SQL> CREATE TABLE TEST (
id       NUMBER (38)   NOT NULL,
text     VARCHAR2 (40),
CONSTRAINT TEST_PK PRIMARY KEY  ( ID )
USING INDEX
TABLESPACE DUOC­_INDEX PCTFREE 10
STORAGE ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 2))
TABLESPACE DUOC_DATA
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 65536
NEXT  65536
PCTINCREASE 10
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/32.- ¿Cuantos segmentos se crearon con la sentencia anterior, y de qué tipos? Consulte la vista USER_SEGMENTS.
¿En qué tablespace se creó el índice? ¿ En qué tablespace se creó la tabla?
Anote esta información para comparar más adelante.
33.- ¿ Cuantos extents tiene cada segmento ? De qué tamaño se creó cada extent tanto en el índice como en la tabla.
Consulte la vista USER_EXTENTS

34.- Borre la tabla test

SQL> DROP TABLE TEST PURGE;
35.- En el usuario duoc1 crear la tabla test que a continuación se indica
SQL> CREATE TABLE TEST (
id       NUMBER (38)   NOT NULL,
text     VARCHAR2 (40),
CONSTRAINT TEST_PK PRIMARY KEY  ( ID ) )
/
36 ¿Cuantos segmentos se crearon con la sentencia anterior, y de qué tipos? Consulte la vista USER_SEGMENTS
¿En qué tablespace se creó el índice? ¿ En qué tablespace se creó la tabla?.
Explique la comparación con el caso anterior. Si no entiende pregunte al profesor
37.- ¿Cuantos extents tiene la tabla TEST? Consulte la vista USER_EXTENTS
SQL> desc user_extents
Name                                      Null?    Type
—————————————– ——– —————————-
SEGMENT_NAME                                       VARCHAR2(81)
PARTITION_NAME                                     VARCHAR2(30)
SEGMENT_TYPE                                       VARCHAR2(18)
TABLESPACE_NAME                                    VARCHAR2(30)
EXTENT_ID                                          NUMBER
BYTES                                              NUMBER
BLOCKS                                             NUMBER
SQL> SELECT EXTENT_ID,  BYTES , BLOCKS FROM user_extents
WHERE SEGMENT_NAME=’TEST’
/

38.- Ejecute el pequeño programa PL/SQL que a continuación se muestra, con el fin de hacer crecer la tabla y generar
extensiones adicionales

SQL>set server output on size 1000000

begin
for x in 1..10000 loop
begin
insert into test
(ID, TEXT)
values
(x,’Hola Mundooooooooooooooooooooo…. ‘);
exception
when dup_val_on_index then
dbms_output.put_line(‘Clave duplicada!! ‘||x);
end;
end loop;
end;
/

39.- ¿Cuantos extents nuevos se crearon con el procedimiento anterior? Consulte la vista USER_EXTENTS
SQL> SELECT EXTENT_ID,  BYTES , BLOCKS FROM user_extents
WHERE SEGMENT_NAME=’TEST’
/
40.- Baje la instancia ORION, elimine todo vestigio de la base de datos ORION.

$ rm       /home/oracle/crea_orion_db.sql
$ rm       /u01/app/oracle/product/11.2.0/db_1/dbs/initorion.ora
$ rmdir -r /u01/app/oracle/diag/rdbms/orion
$ rmdir -r /u01/app/oracle/oradata/orion
$ rmdir -r /u02/oradata/orion
$ rmdir -r /u01/app/oracle/admin/orion
41.- Usando el DBCA, recree la base de datos sirio usando el template que existe para ella.
FIN.

AYUDA: Material de apoyo a practica de laboratorio # 4