Reorganizar Base de Datos con PRETORIA

Cuando estamos reorganizando la Base de Datos, a veces es necesario ajustar los parametros de almacenamiento de las tablas.
Es una tarea ardua si consideramos que una Base de Datos mediana tiene miles de tablas.

Existe una utileria que nos ayuda a modificar los parametros de almacenamiento de las tablas tales como:

PCTUSED
PCTFREE
INITIAL
NEXT
MINEXTENTS
MAXEXTENTS
PCTINCREASE
FREELISTS
FREELIST GROUPS
INITRANS
MAXTRANS
TABLESPACE
DEGREE

Es importante leer todo el manual que acompana a la utileria, ya que es muy extensa.

Voy a describir brevemente a PRETORIA.

Que es Pretoria?
Pretoria es una herramienta para manipular los Oracle Indexfiles.
Parsea el archivo indexfile, hace una busqueda y reemplaza los parametros de storage.

Como trabaja Pretoria?
Pretoria necesita 2 archivos de entrada:

Oracle Indexfile: un archivo indexfile contiene todas las sentencias de creacion de tablas e indices. Sin embargo estas sentencias estan todas comentadas.

Archivo que contiene los nuevos parametros de storage: este archivo es creado facilmente con algun PL/SQL o paquete UTL_FILE


Como es un archivo Indexfile?
Ejemplo:
REM CREATE TABLE "PORTAL30"."WWV_SYS_APPLICATION_TEMPLATES$" ("ID"
REM NUMBER(*,0), "TEMPLATE_SCHEMA" VARCHAR2(30) NOT NULL ENABLE,
REM "TEMPLATE_NAME" VARCHAR2(50) NOT NULL ENABLE, "TEMPLATE_TYPE"
REM VARCHAR2(50) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(30), "CREATED_ON"
REM DATE, "UPDATED_BY" VARCHAR2(30), "UPDATED_ON" DATE) PCTFREE 10
REM PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 16384 NEXT
REM 24576 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORTAL" ;
CREATE UNIQUE INDEX "PORTAL30"."WWV_SYS_APP_TEMPLS$_PK" ON
"WWV_SYS_APPLICATION_TEMPLATES$" ("ID" ) PCTFREE 10 INITRANS 2 MAXTRANS
255 STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PORTAL" LOGGING ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_APPLICATION_TEMPLATES$" ADD
REM CONSTRAINT "WWV_SYS_APP_TEMPLS$_PK" PRIMARY KEY ("ID") USING INDEX
REM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 40960 NEXT 40960
REM MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORTAL" ENABLE ;
REM ALTER TABLE "PORTAL30"."WWV_SYS_APPLICATION_TEMPLATES$" ADD
REM CONSTRAINT "WWV_SYS_APP_TEMPLS$_CK1" CHECK (template_type in
REM ('STRUCTURED', 'UNSTRUCTURED')) ENABLE NOVALIDATE ;
REM CREATE TABLE "PORTAL30"."WWV_SYS_APP_TEMPLATE_DETAILS$"
REM ("APP_TEMPLATE_ID" NUMBER(*,0), "ELEMENT" VARCHAR2(50), "THE_VALUE"
REM LONG) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
REM STORAGE(INITIAL 65536 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505
REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT

Como se crea un indexfile?
El archivo indexfile se crea con las utilerias export/import.

Crear un archivo dump usando EXP

exp system/manager@TEST FULL=Y ROWS=N File=expdat.dmp

No es necesario exportar los datos, solo la estructura logica.

Usar IMP para crear el archivo INDEXFILE a partir del archivo dump

imp system/manager FILE=expdat.dmp FULL=Y INDEXFILE=index.sql

No hay problema, nada se importara a la Base de Datos. Solo crea el indexfile.


Como crear el archivo de almacenamiento?
Existen varias maneras de crearlo.
Desde codigo PRO*C, PRO*COBOL, JDBC (Java), hasta PL/SQL.

Sintaxis:
Listado de los parametros de storage :
"OWNER"."SEGMENTNAME"."PARTITIONNAME" storage parameter 1 value 1 storage parameter n value n

Por ejemplo:
DWH"."CUSTOMERS" INITIAL 100M NEXT 100M PCTFREE 5 TABLESPACE CUST
DEFAULT_TABLE("DWH") INITIAL 1M NEXT 1M TABLESPACE DWH_DATA
DEFAULT_TABLE ("DWHADMIN") INITIAL 512K NEXT 512K TABLESPACE DWH_ADMIN_DATA

La explicacion a detalle puede ser leida en el manual de pretoria.

Ejecutar Pretoria
Mi ejemplo:

java Pretoria -i tabs_FULANO.txt -ot salidatab.txt -oi salidaidx.txt -oc salidaconstr.txt -s nextextents01.txt

Contenido de los archivos

tabs_FULANO.txt
REM CREATE TABLE "FULANO"."ADDRESS_TYPE" ("DR_TYPE_CD" VARCHAR2(2)
REM NOT NULL ENABLE, "DR_TYPE_DESC" VARCHAR2(30), "UPDATE_FLAG"
REM NUMBER(1, 0), "LA_LOD_DT" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 STORAGE(INITIAL 204800 NEXT 204800 MINEXTENTS 1
REM MAXEXTENTS 700 PCTINCREASE 1 FREELISTS 1 FREELIST GROUPS 1
REM BUFFER_POOL DEFAULT) TABLESPACE "DOJETKTBL02" LOGGING NOCOMPRESS ;
CONNECT FULANO;
CREATE UNIQUE INDEX "FULANO"."XDRSS_YPE" ON "ADDRESS_TYPE"
("ADR_TPE_MD" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
1064960 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "DOJETKTBLIDX02" NOLOGGING ;
REM ALTER TABLE "FULANO"."ADESS_TPE" ADD PRIMARY KEY
REM ("ADDR_TYPE_CD") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REM STORAGE(INITIAL 1064960 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 500
REM PCTINCREASE 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
REM TABLESPACE "QAZAQS" NOLOGGING ENABLE ;
REM CREATE TABLE "FULANO"."NTRS_BY_LH" ("PERIOD" VARCHAR2(6),
REM "LUNTHND_COSE_DT" DATE, "REL_ENTR" NUMBER(8, 0), "IZQUI_ENTR"
REM NUMBER(8, 0), "GUAU_ENTR" NUMBER(8, 0), "TAIL_ENTR_OJETK" NUMBER(8,
REM 0), "RETAIL_ENTR_WH" NUMBER(8, 0), "RETAIL_ENTR_AG" NUMBER(8, 0),
REM "IZQUI_NTR_OJETK" NUMBER(8, 0), "IZQUI_ENR_WH" NUMBER(8, 0),
REM "IZQUI_NTR_AGUA" NUMBER(8, 0), "GUAU_SELENA_OJETK" NUMBER(8, 0),
REM "GUAU_SELENA_WH" NUMBER(8, 0), "GUAU_SELENA_AG" NUMBER(8, 0)) PCTFREE
REM 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 163840 NEXT
REM 131072 MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 1 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "QWER" LOGGING
REM NOCOMPRESS ;
.
.
.

salidatab.txt


REM *************************************************************************************************
REM * This file was created using Pretoria 3b on Windows XP 5.1 x86
REM * Use 'set SQLBLANK on' in SQLPLUS 8.1.5 or higher to run execute this file - older versions use svrmgrl!
REM * Pls send bugs, recommendations, ... to kurt_van_meerbeeck@axi.be or kurtvm@pandora.be !
REM * Pretoria is FREE ! It may not be sold as a commercial product nor may it be part of
REM * a commercial project !!!
REM * Check out my homepage http://zap.to/knal
REM * Fri Feb 15 11:32:08 CST 2008
REM *************************************************************************************************
CONNECT FULANO;

CREATE UNIQUE INDEX "FULANO"."RESS_YPE"
ON "FULANO"."RESS_TPE"
( "DR_TPE_D" )
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE
( INITIAL 1064960
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 500
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE "ASDFAD" NOLOGGING ;

ALTER TABLE "FULANO"."ADDRESS_TYPE"
ADD PRIMARY KEY ( "A_YSDE_CD" ) USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE
( INITIAL 1064960
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 500
PCTINCREASE 1
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT ) TABLESPACE "ASDFDSVRE" NOLOGGING ENABLE ;






Pasitos para reorganizar la Base de Datos:
In short - following these steps :
1.Export your DB
2.Create an indexfile
3.Make your storage rules - remember - there is no sky !
4.Run Pretoria
5.Use the table output script to pre create your tables
6.Import your data (INDEX=N & CONSTRAINTS=N ... referential key constraints :-) )
7.Run the index output script
8.Import your data (ROWS=N INDEX=N CONSTRAINTS=Y)


Descargar Pretoria.

Oracle export and import by pipe

EXPORT pipe

#!/bin/ksh
rm -f export_pipe
mknod export_pipe p
chmod 666 export_pipe
nohup gzip -c < export_pipe > expdat.dmp.gz &
imp "\"/ as sysdba\"" file=export_pipe full=yes ignore=yes
log=exportTESTDB.log
rm -f export_pipe


IMPORT pipe
To import, I use gunzip and do the opposite!

mknod exp.pipe p
gunzip < expdat.dmp.gz > exp.pipe &
imp userid=system/manager file=exp.pipe full=y

Export con el usuario "sysdba"

exp "\"/ as sysdba\"" tables=fulano.tab file=fuldata.dmp log=tabexp.log direct=y

EXPORT by Pipe file

#!/bin/ksh
######################################################################
# PROGRAM NAME: xport_ux
# PURPOSE: Performs export of the database
# Compresses the export file on the fly while gziped.
#
# USAGE: xport_ux SID OWNER
######################################################################
CURRENT_DATE=`date "+%d%m%y-%H:%M"`
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_verify(): Verify that database is online
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_verify(){
STATUS=`ps -fu ${ORA_OWNER} |grep -v grep| grep ora_pmon_${ORA_SID}`
funct_chk_unix_command_status "Database is down for given SID($ORA_SID),Owner($ORA_OWNER). Can't perform export "
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_cleanup(): Cleanup interim files
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_cleanup() {
rm -f $PIPE_DEVICE
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_splitcompress_pipe(): Creates pipe for compressing of file
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_splitcompress_pipe() {
# Creates pipe for compressing
if [ ! -r ${PIPE_DEVICE} ]; then
/etc/mknod ${PIPE_DEVICE} p
fi

nohup gzip < ${PIPE_DEVICE} > ${ZFILE} &
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_build_parfile(): Creates parameter file
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_build_parfile() {
# This line makes sure that it always creates a new parameter file
echo " " >${PARFILE}
echo "userid="\"/ as sysdba\""">>${PARFILE}
echo "Full=Y">>${PARFILE}
#echo "tables=scott.t1">>${PARFILE}
#echo "owner=scott">>${PARFILE}
#echo "Grants=Y">>${PARFILE}
#echo "Indexes=Y">>${PARFILE}
#echo "Rows=Y">>${PARFILE}
#echo "Constraints=Y">>${PARFILE}
#echo "Compress=N">>${PARFILE}
echo "Consistent=Y">>${PARFILE}
echo "File=${PIPE_DEVICE}">>${PARFILE}
echo "Direct=Y">>${PARFILE}
echo "Statistics=none">>${PARFILE}
echo "Log=${EXPORT_DIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log">>${PARFILE}
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_export(): Export the database
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_export() {
# Remove old export file
rm -f ${ZFILE}

${ORACLE_HOME}/bin/exp parfile=${PARFILE}
if [ $? != 0 ]; then
echo `date` >> $LOGDIR/fullexp_${ORA_SID}_${CURRENT_DATE}.log
echo "EXPORT_FAIL: ${ORA_SID}, Export Failed" >> $LOGDIR/fullexp_${ORA_SID}_${CURRENT_DATE}.log
funct_cleanup
exit 1
fi
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_parm(): Check for input parameters
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_chk_parm() {
if [ ${NARG} -ne 2 ]; then
echo "EXPORT_FAIL: ${ORA_SID}, Not enough arguments passed"
exit 1
fi
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_chk_bkup_dir(): Create backup directories if not already existing
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_chk_bkup_dir() {
EXPORT_DIR=${BACKUPDIR}
if [ ! -d ${EXPORT_DIR} ]; then mkdir -p ${EXPORT_DIR}; fi
if [ ! -d ${LOGDIR} ]; then mkdir -p ${LOGDIR}; fi
ZFILE="${EXPORT_DIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.dmp.gz"
}

#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
# funct_get_vars(): Get environment variables
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
funct_get_vars(){
ORA_HOME=`sed /#/d ${ORATABDIR}|grep -i ${ORA_SID}|nawk -F ":" '{print $2}'`
ORA_BASE=`echo ${ORA_HOME}|nawk -F "/" '{for (i=2; i<=NF-2; i++) print "/"$i}'` ORACLE_BASE=`echo $ORA_BASE|tr -d " "` ORACLE_HOME=${ORA_HOME}; export ORACLE_HOME ORACLE_SID=${ORA_SID}; export ORACLE_SID } #::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: # funct_chk_unix_command_status(): Check exit status of Unix command #:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: funct_chk_unix_command_status() { if [ $? != 0 ]; then echo "`date`" >> ${LOGDIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log
echo "EXPORT_FAIL: ${1} " >> ${LOGDIR}/fullexp_${ORA_SID}_${CURRENT_DATE}.log
exit 1
fi
}

#######################################
# MAIN
#######################################
NARG=$#
ORA_SID=$1
ORA_OWNER=$2

# Set up environment
BACKUPDIR="/backups"
ORATABDIR=/var/opt/oracle/oratab
TOOLS="/u01/oracomn/admin/my_dba"

DYN_DIR="${TOOLS}/DYN_FILES"
PARFILE="/oracle/oracle9i/admin/scripts/export_${ORA_SID}.par"
LOGDIR="/backups/"

PIPE_DEVICE="/backups/${ORA_SID}_pipe"

echo "... Now exporting .... ${ORA_SID}"

funct_chk_parm
funct_get_vars
funct_verify
funct_chk_bkup_dir
funct_splitcompress_pipe
funct_build_parfile
funct_export
funct_cleanup

echo `date` >> $LOGDIR/${ORA_SID}_${CURRENT_DATE}.log
echo "${ORA_SID}, export completed successfully" >> $LOGDIR/${ORA_SID}_${CURRENT_DATE}.log

####################### END MAIN ###############################

Check dmp file generated

To check if a backup made ​​with the export utility is reliable we can use the
INDEXFILE parameter, which generates a file with the definitions of tables and indexes in a file. Do not worry, this parameter is not "load" information to the database:
Once done the backup with the export utility, we can test it with the import command.

imp system/passwd file=expfull.dmp log=testdmp.log fromuser=fulano touser=fulano tables=tablita indexfile=archivodat.txt








This way we can verify that the file expfull.dmp is in good condition.

The file contains the definitions archivodat.txt creating Tables and indexes.

The INDEXFILE parameter can only be used with FULL = Y, FROMUSER,TOUSER, TABLES parameters.