Backup de tablas grandes

[Alextroy]

Hola.

Necesito hacer una backup periodico de una tabla de auditoria enorme.
Se supone que tengo que almacenar toda la información que ha registrado esa tabla a partir de un mes hacia atrás en una tabla similar en otro esquema, y eliminar la información que he guardado.

Propuse hacer un script que crease una tabla igual en otro esquema y la llenase de datos con un comando del tipo INSERT INTO TABLE (select * from..... where...) para luego eliminar los registros de la tabla de producción. Pero tras probar este metodo me comunican que es demasiado lento y no es viable.

Queria saber si algúen tiene experiencia con algún caso parecido y como ha podido solucionarlo. Mi conocimiento es bastante limitado, y mas o menos a trompicones voy sacando las cosas, pero lo referente a mejora de rendimiento, optimizacion de recursos y demás se me queda algo grande.

Un saludo

[huesos52]

Preguntas

tienes una tabla grande... debes pasar todos los registros a otra tabla y borrar de la tabla en producción?
Al momento de copiar los valores a la nueva tabla, es necesario hacer algún tipo de comparación? si ya existen o no?

Si te entendí algo, Mira el comando merge. te puede servir.

saludos

[jc3000]

Iniciado por Alextroy

Hola.

Necesito hacer una backup periodico de una tabla de auditoria enorme.
Se supone que tengo que almacenar toda la información que ha registrado esa tabla a partir de un mes hacia atrás en una tabla similar en otro esquema, y eliminar la información que he guardado.

Propuse hacer un script que crease una tabla igual en otro esquema y la llenase de datos con un comando del tipo INSERT INTO TABLE (select * from..... where...) para luego eliminar los registros de la tabla de producción. Pero tras probar este metodo me comunican que es demasiado lento y no es viable.

Queria saber si algúen tiene experiencia con algún caso parecido y como ha podido solucionarlo. Mi conocimiento es bastante limitado, y mas o menos a trompicones voy sacando las cosas, pero lo referente a mejora de rendimiento, optimizacion de recursos y demás se me queda algo grande.

Un saludo

Pues mira, la solucion que tu has propuesto me parece la mas coherente y la mas lógica : Acotar los datos que quieres traspasar y usar un INSERT.... SELECT. ¿ que es lento ?, no se a que llaman lentitud los que te han dicho eso. Y tampoco sé el volumen de datos que quieres traspasar.

Estos dias estoy haciendo algo parecido. El primer paso era pasar todos los datos a procesar a una temporal ( Datos de un año entero, unos 356.000.000 de registros ).

Lo que hize fué crear una tabla auxiliar identica a la original en un tablespace aparte ( solo para mi ), poner tablespace y tabla en NOLOGGING y tirar el INSERT SELECT :

INSERT /*+ APPEND*/ INTO TABLA_AUXILIAR
SELECT * FROM TABLA_ORIGEN
WHERE FECHA BETWEEN TO_DATE('01012009' ,'DDMMYYYY') AND TO_DATE('31122009' ,'DDMMYYYY')
/


El resultado fué que me insertó unos 356.000.000 de registros en una hora mas o menos. ¿ Esto se puede calificar de lento ?, yo creo que no.

No se si te he ayudado en algo........................

[8vio]

Ummmm la segunda tabla tiene constraint de algun tipo? si los colocas en NOVALIDATE podrian disminuir los tiempos. Igual con los indices.

Ahora un export/import de la tabla no te funcionaria?

Saludos

[Alextroy]

Hola.

Intentaré contestaros a todos.

La idea es coger la tabla de auditoria y vaciarla mensualmente volcando antes los datos en una tabla en otro tablespace identica a la original. Cada mes seria en una tabla nueva, o tal vez mire el tema de hacer una particionada por meses ¿que es mejor?

Como la tabla nueva la creo a partir del código de la original, entiendo que si tiene todas sus constraints. ¿seria mas rápido si las elimino?

En cuanto a lo del NOLOGGING, es lo que iba a probar ahora, tambén miraré lo del merge y el append, aunque no tengo ni idea de que es.

Ya os comentaré como ha ido.

Un saludo

[jc3000]

Iniciado por Alextroy

Hola.

Intentaré contestaros a todos.

La idea es coger la tabla de auditoria y vaciarla mensualmente volcando antes los datos en una tabla en otro tablespace identica a la original. Cada mes seria en una tabla nueva, o tal vez mire el tema de hacer una particionada por meses ¿que es mejor?

Como la tabla nueva la creo a partir del código de la original, entiendo que si tiene todas sus constraints. ¿seria mas rápido si las elimino?

En cuanto a lo del NOLOGGING, es lo que iba a probar ahora, tambén miraré lo del merge y el append, aunque no tengo ni idea de que es.

Ya os comentaré como ha ido.

Un saludo

Te contesto por partes.

Es que tampoco das muchos datos. Si la tabla destino solo va a ser como un armario, vamos, que esa informacion la guardarás ahí y ya no la tocarás, salvo momentos muy, muy puntuales no creo necesario particionarla. Si por el contrario los accesos van a ser muy frecuentes, tal vez sería buena idea particionarla, siempre y cuando los accesos a la tabla vayan con el criterio de particionamiento, sino esos accesos van a ir mucho mas lentos.

Si la tabla la creas de la forma CREATE TABLE DESTINO AS SELECT * FROM ORIGINAL WHERE 1=0 , no te crea ninguna constraint, salvo los NOT NULL.

El append lo que hace es que en inserciones masivas y de gran número de registros te guarda la informacion de manera contigua en el tablespace en lugar de ir metiendo la información en los bloquecitos vacios que va encontrando en el tablespace. De esta manera tanto la Insercion como los accesos a la informacion es mas rápido ya que no tiene que perder tiempo primero buscando bloques libres primero y luego buscando la informacion en todo el tablespace ya que se encuentra toda junta.

Para que el APPEND funcione, el Tablespace, la Tabla ó ambos tienen que estar en NOLOGGING

¿ Para que necesitas el MERGE , si lo que quieres hacer es en definitiva una insercion masiva. ?

[Alextroy]

Hola.

Lo siento, he estado ocupado con otras cosas y no he podido ver nada de esto, pero lo retomo ahora.

Jc3000, contestando a tus preguntas, te diré que la idea es almacenar la información que sacamos de la otra tabla solo porque la ley lo obliga, pero muy rara vez hará falta acceder a ella. La idea de particionarla era porque pensé que así seria mas facil automatizar el proceso, pero bueno, eso me preocupa menos...

A lo que voy. No se para que sirve merge, lo dije porque alguien lo sigirió en este post pero no he tenido tiempo de mirarlo, aunque por tu comentario deduzco que no me va ayudar demasiado ¿no?

Voy a probar creando un tablespace NOLOGGIN y volcando la tabla con el APPEND.
Intentaré hacer una comparativa de tiempos, ahora mismo en mi tabla de pruebas tengo 1300000 registros (la tabla tiene 25 campos), que creo que serán suficientes para probar ¿no?

Ya comentaré mis impresiones.

Gracias y saludos

[huesos52]

El comando merge te lo recomendé por que pensé que el backup periódico requiere de un borrado de la tabla (cada mes por ejemplo) para luego hacer una inserción masiva como dices hacerlo de todos los registros que igual estaban anteriormente en la tabla.

Con el comando merge, puedes revisar si los registros a insertar ya se encuentran en la tabla, evitando una tarea que ya ha sido realizada y solo insertar los nuevos registros que se tienen desde desde el ultimo mes en este caso. Si han habido cambios en la tabla, se pueden actualizar en la opción MATCHED para mantener la tabla actualizada.

Si la inserción periodica de datos es de registros nuevos y no tocan en nada a los registros que ya se encuentran desde la ultima inserción, este comando no te servirá.

Como lo dije anteriormente, no he probado su rendimiento en grandes cantidades de datos por que administro un servidor muy pequeño en comparación a la cantidad de información que ustedes manejan.

saludos

[Alextroy]

Gracias por tu interes huesos, pero efectivamente, cada mes se creará una tabla nueva donde se volcarán los datos.

Lo que también me interesa saber es que una vez volcados los datos del mes pasado a la nueva tabla, tengo que borrarlos de forma rápida de la tabla original.

Solo se me ocurre hacerlo con DELETE, porque truncate no admite cláusulas, pero sospecho que este va a ser un proceso lento si son demasiados datos ¿no?
¿se os ocurre alguna otra forma de poder hacerlo sin que se demore demasiado ni comerme los recursos del servidor?

Tengo que tener en cuenta, que aunque los datos se borren y queden almacenados en otra tabla, en cualquier momento me podrian pedir que se restaurasen los de un mes completo, de modo que habria que respetar la integridad de los ROWID. ¿o es una tonteria esto que digo?

Saludos

[huesos52]

Repasa nuevamente el enlace que te dì acerca del comando merge. Nada pierdes con comparar los tiempos y recursos que te consume.

Fijate que si los datos ya se encuentran en la tabla, nisiquiera tienes que meterte con el rowid despues de insertado la primera vez.

Sin haberlo probado, considero que tiene que ser mucho mas optimo al solo insertar los datos de un solo mes y no toda la informaciòn de varios años (me imagino).

pruebalo y nos cuentas.

saludos

[jc3000]

Sigo pensando que el INSERT.... SELECT es mas efectivo, pero en cualquier caso, prueba la solucion que aporta Huesos, seguro que entre todos encontramos la mejor forma.

Ah, olvidate del ROWID, si no recuerdo mal, sirve para identificar internamente la situación de ese registro en el tablespace o en el datafile. Para lo que planteas no te sirve absolutamente para nada.

Ya nos contarás.

[Alextroy]

Hola.

Ya tengo algunos resultados con los de trabajar.

Haciendo el insert de forma habitual, es decir, sin APPEND y con la tabla en modo LOGGING, ha tardado 4 minutos en insertar 1315565 registros.
En cambio, con la tabla en modo NOLOGGING y con el APPEND, el tiempo se ha reducido a unos 45 segundos aproximadamente, lo cual es bastante significativo.

He vuelto a repasar el comando MERGE, pero creo que no es aplicable a este caso, ya que la tabla en la que voy a insertar estará vacia, por lo tanto insertará todos los datos ya que no tiene nada que comparar.

Me doy bastate por satisfecho con este método.
Como curisosidad, solo tengo una duda. Cuando la tabla está en modo NOLOGGING ¿que implica exactamente? Entiendo que no está escribiendo en el UNDO ¿no?, y por lo tanto ni se necesita hacer commit ni es posible hacer rollback ¿no es así?

Un saludo

[jc3000]

Iniciado por Alextroy

Como curisosidad, solo tengo una duda. Cuando la tabla está en modo NOLOGGING ¿que implica exactamente? Entiendo que no está escribiendo en el UNDO ¿no?, y por lo tanto ni se necesita hacer commit ni es posible hacer rollback ¿no es así?

Un saludo

No, no es eso, es a grosso modo, que ante una incineracion de la base de datos, lo unico que puedes recuperar de los datos de esa tabla, es lo que tengas en tus back-ups. En cambio en modo LOGGING puedes recuperar el estado de la tabla en cualquier momento en el tiempo en base a los archivos de redo

Mirate este link y lo entenderas

http://oracleenespanol.com/2008/07/0...ilema-parte-1/

[Alextroy]

Gracias, tiene buena pinta la página del enlace, lo malo es que el artículo del LOGGING y NOLOGGING no está acabado, y solo hace un repaso al funcionamiento de los redo log.

De todas formas, buscando un poquito he encontrado la segunda parte de ese mismo artículo pero en inglés (http://www.databases-la.com/?q=es/node/33)

No se si lo he entendido del todo (mi inglés es regular...) pero por lo poco que he pillado, parece que en modo NOLOGGING la información sobre los cambios, solo se registran en el buffer de redo, pero no en los ficheros y por ello en caso de desastre no se podrian recuperar la transacción. ¿voy encaminado?

A mi corto entender, esto significa que la mejora en el rendimiento viene dada por el tiempo que el DBWR se ahorra al no tener que escribir en estos ficheros.

Aún así, aunque esto que digo sea correcto, sigo sin tener muy claro si se escribe o no información en el UNDO, yo deduzco que no oero alguien me dijo en alguna ocasión que si, y de ser asi... ¿para qué? si de todas formas no se podria recuperar la información. Lo que me lleva de nuevo a deducir que no haria falta confirmar la transacción, y por ende no implicaria que no hay rollback posible... pero tu me comentas que no es así, asique...

[jc3000]

Iniciado por Alextroy

No se si lo he entendido del todo (mi inglés es regular...) pero por lo poco que he pillado, parece que en modo NOLOGGING la información sobre los cambios, solo se registran en el buffer de redo, pero no en los ficheros y por ello en caso de desastre no se podrian recuperar la transacción. ¿voy encaminado?

Asi es.

Iniciado por Alextroy

A mi corto entender, esto significa que la mejora en el rendimiento viene dada por el tiempo que el DBWR se ahorra al no tener que escribir en estos ficheros.

Efectivamente

Iniciado por Alextroy

Aún así, aunque esto que digo sea correcto, sigo sin tener muy claro si se escribe o no información en el UNDO, yo deduzco que no oero alguien me dijo en alguna ocasión que si, y de ser asi... ¿para qué? si de todas formas no se podria recuperar la información. Lo que me lleva de nuevo a deducir que no haria falta confirmar la transacción, y por ende no implicaria que no hay rollback posible... pero tu me comentas que no es así, asique...

No es así. Un insert es una sentencia DML y como tal necesita su validación ( COMMIT ) o su descarte ( ROLLBACK )

Tu esa informacion si que la puedes recuperar....... si la tienes en un back-up, lo que no puedes hacer es recuperar la información a una fecha dada por decirlo de alguna manera, mas o menos como funciona el Flashback