17 octubre 2008

Creación de backups automaticos con Oracle 10g R2

En este post voy a explicar cómo realizar un bakcup de una base de datos en Oracle 10g R2 a través de un archivo bat, el cual, se ejecuta en el servidor a través de una tarea programada.
Lo primero que se debe de realizar es conectarse al servidor de base de datos como usuario sysman y realizar las siguientes acciones:
1.- Indicarle a Oracle el directorio en el cual se almacenará el backup de la base de datos a través de la siguiente sentencia SQL:
CREATE DIRECTORY nombre_del_directorio AS 'ruta_fisica'
donde:
nombre_del_directorio es el alias para Oracle de la ruta física en donde se almacenará el bakcup.
ruta_fisica es la ruta en el servidor en donde se almacenará el backup. Esta ruta debe de ser en el mismo equipo en donde se encuentra el servidor de Oracle, por ejemplo: C:/Respaldos
2.- Dar los permisos de lectura/escritura para dicho directorio a través de la siguiente sentencia SQL:
GRANT READ,WRITE ON DIRECTORY nombre_del_directorio TO usuario
donde:
usuario es la cuenta del usuario para la cual se desea realizar el backup de su esquema.
3.- Crear el archivo bat encargado de realizar el backup. Éste se realiza a través de la siguiente sentencia SQL:
EXPDP usuario/contraseña DIRECTORY=nombre_del_directorio DUMPFILE=nombre_del_archivo
donde:
nombre_del_archivo es el nombre del backup con extensión DMP.
Finalmente, el arhivo bat queda programado de la siguiente manera:
@echo off
set FECHA=%date%_%time%
set FECHA=%FECHA:/=%
set FECHA=%FECHA: =%
set FECHA=%FECHA::=%
set FECHA=%FECHA:,=%
set FECHA=%FECHA: =%

expdp usuario/contraseña directory=nombre_del_directorio dumpfile=%FECHA%.dmp
El script crea un archivo de backup con un nombre similar a: 17102008_11243038.DMP donde la estructura del nombre del backup es: FECHA_HORA.EXTENSION. Entonces, para el ejemplo dato anteriormente, el archivo de backup fue creado el 17 de octubre del 2008 a las 11:24:30,38.
NOTA 1: Con este script hay que tener cuidado en la sección set FECHA=%FECHA:/=% ya que si el separador de fecha del sistema es '-' y no '/' el script no funcionará, sólo se debe de corregir por el caracter adecuado para su correcto funcionamiento.
NOTA 2: Si se olvidaron de los alias que han creado en la base de datos, lo pueden ver a través de la siguiente sentencia SQL:
SELECT * FROM dba_directories
NOTA 3: Para importar un backup a la base de datos, se debe de realizar a través de la siguiente instrucción en una consola de windows:
IMPDP usuario/contraseña DIRECTORY=nombre_del_directorio DUMPFILE=nombre_del_archivo
Saludos.

41 comentarios:

Anónimo dijo...

hola,muy bueno el blog,si lo deseas,ingresa a mi pagina, a publicar un comentario.saludos

Anónimo dijo...

buenisimo, pero no dices como hacer el proceso inverso. O sea que Oracle importe automáticamente por medio de un bat el dmp generado con la fecha más reciente

saludos

Anónimo dijo...

hay un bat que hace todo eso, sin necesidad de configurar Oracle. simplemente guarda el contenido siguiente en un .bat

@ECHO OFF
set FECHA=%date%_%time%
set FECHA=%FECHA:/=%
set FECHA=%FECHA: =%
set FECHA=%FECHA::=%
set FECHA=%FECHA:,=%
set FECHA=%FECHA: =%
EXP NOMBREDB/CLAVEDB@USUARIOSB BUFFER=120000 FILE=%FECHA%.DMP LOG=%FECHA%.LOG GRANTS=Y INDEXES=Y ROWS=Y FULL=N OWNER=USUARIODB
exit

Solo cambia donde dice NOMBREDB por el nombre de la base de datos, CLAVEDB - clave de la base de datos, USUARIODB - el usuario de esa base de datos (no el system o sys sino el usuario con privilegios), y en Owner el propietario de la DB, que es el mismo que el usuario, y listo, pero no se hacer el proceso inverso

Anónimo dijo...

copiar pegar no+ de otra publicación

Anónimo dijo...

I'm really impressed with your writing skills as neatly as with the structure on your weblog. Is this a paid theme or did you customize it your self? Either way stay up the excellent quality writing, it's rare to look a great blog like this one today.
.
My web-site - http://www.daily-wet-tshirt.com

Anónimo dijo...

Great blog! Is your theme custom made or did you download it from somewhere?
A design like yours with a few simple tweeks would really make my blog jump out.
Please let me know where you got your theme. Kudos
Take a look at my weblog : bhgalleries.com

Anónimo dijo...

Fantastic beat ! I wish to apprentice at the same time as
you amend your site, how can i subscribe for a blog
web site? The account aided me a acceptable deal.
I had been a little bit acquainted of this your broadcast provided shiny transparent idea
my site > http://amazingteenbabes.com/

Anónimo dijo...

Wonderful blog! I found it while surfing around on Yahoo News.

Do you have any suggestions on how to get listed
in Yahoo News? I've been trying for a while but I never seem to get there! Thank you
Here is my site Free Teen Porn

Anónimo dijo...

Hey There. I found your blog using msn. This is
a really well written article. I will make sure to bookmark
it and come back to read more of your useful info. Thanks
for the post. I'll definitely comeback.
Also visit my web-site ; Danielle FTV

Anónimo dijo...

What's up, yup this article is really nice and I have learned lot of things from it regarding blogging. thanks.
My web page - nude girls

Anónimo dijo...

Hi, i believe that i noticed you visited my weblog thus i came to return
the want?.I am attempting to to find things to enhance
my website!I assume its good enough to make use of some of your ideas!

!
Also see my web page: Smoking Hot Brunette Gets Really Wet

Anónimo dijo...

Wow, incredible blog layout! How lengthy have you ever been blogging for?
you made blogging glance easy. The full glance of
your website is wonderful, let alone the content!
My web page ; free porn

Anónimo dijo...

Thank you for sharing your thoughts. I really appreciate your efforts and I am waiting for your further post thanks once again.
Have a look at my web-site ; Stanley and his girlfriend (USA)

Anónimo dijo...

I know this site gives quality based articles or reviews and additional information,
is there any other web page which gives such things in quality?
Also visit my site : contractor in Orlando

Anónimo dijo...

Thanks for sharing such a pleasant thinking,
paragraph is pleasant, thats why i have read it entirely
My webpage: lanautica

Anónimo dijo...

Usually I don't read post on blogs, but I would like to say that this write-up very pressured me to take a look at and do so! Your writing taste has been surprised me. Thank you, very great post.
Review my weblog : live free chat

Anónimo dijo...

It's an remarkable paragraph for all the web viewers; they will take benefit from it I am sure.
My blog - Lexington Law

Anónimo dijo...

Malaysia & Singapore & brunei finest on-line blogshop for wholesale & supply korean add-ons, accessories, earstuds,
choker, rings, trinket, hair & bangle accessories.
Offer 35 % wholesale price cut. Ship Worldwide
my webpage: stitching kawat

Anónimo dijo...

Malaysia & Singapore & brunei greatest online blogshop for wholesale &
quantity korean add-ons, earrings, earstuds, locket, rings,
bracelet, bracelet & hair accessories. Promotion 35 % wholesale price cut. Ship Worldwide
Here is my web blog Vix etf

Anónimo dijo...

Malaysia & Singapore & brunei ultimate on the internet
blogshop for wholesale & supply korean accessories, accessories, earstuds, necklace, rings, bangle, bracelet
& hair accessories. Offer 35 % wholesale price cut. Ship Worldwide
Also visit my weblog ; iTunes promotion

Anónimo dijo...

I blog often and I genuinely thank you for your content.
This great article has truly peaked my interest. I'm going to bookmark your blog and keep checking for new details about once a week. I opted in for your Feed too.
Take a look at my web blog ... Trick Photography and Special Effects

Anónimo dijo...

Stunning story there. What happened after? Take care!
my page: Photography Tricks

Anónimo dijo...

Thanks for the auspicious writeup. It in fact used to be a amusement account it.
Glance complicated to more introduced agreeable from you!
By the way, how can we communicate?
Also visit my site ... Cheap YouTube Views

Anónimo dijo...

Wow, that's what I was seeking for, what a material! present here at this weblog, thanks admin of this website.

my web site cardella UFC

Anónimo dijo...

Its not my first time to pay a visit this web site, i am visiting this web page
dailly and get fastidious data from here every day.


my web page - weider adjustable dumbbells

Anónimo dijo...

What's up, its fastidious post about media print, we all know media is a wonderful source of data.

My web blog: adjustable dumbells

Anónimo dijo...

Hello, after reading this remarkable paragraph i am
also cheerful to share my knowledge here with mates.


Here is my web site; predictions that came true
my web site: Bizarre

Anónimo dijo...

ghd outlet A great toga party game idea is to have a grape stomping contest. ghd straighteners

Anónimo dijo...

Heya i am for the first time here. I came across this
board and I find It really useful & it helped me out a lot.
I hope to give something back and help others like you helped me.



Here is my blog - http://www.babesflick.com/category/296/reality-hardcore-fucking/

Anónimo dijo...

Saved as a favorite, I love your site!

my webpage; sex-xxx-erotica.com

Anónimo dijo...

Undeniably consider that which you said. Your favorite reason appeared to be at the
internet the easiest thing to take into accout
of. I say to you, I definitely get annoyed even as
other folks think about concerns that they just don't know about. You controlled to hit the nail upon the top and also defined out the entire thing with no need side-effects , people could take a signal. Will likely be back to get more. Thanks

My blog post - cuteteenporn.net

Anónimo dijo...

Marvelous, what a webpage it is! This webpage presents
valuable information to us, keep it up.

Feel free to visit my web page: sexy girls

Anónimo dijo...

Thank you for the good writeup. It actually was once a leisure account it.

Look advanced to more brought agreeable from you!
By the way, how could we keep up a correspondence?


my homepage; http://www.wildpartygirls.org/

Anónimo dijo...

Hey there! Do you know if they make any plugins to safeguard against hackers?
I'm kinda paranoid about losing everything I've worked hard on.

Any recommendations?

Feel free to surf to my weblog ... Cute Teen Porn

Anónimo dijo...

Hello there! Do you know if they make any plugins to assist with SEO?
I'm trying to get my blog to rank for some targeted keywords but I'm not seeing very good gains.
If you know of any please share. Thank you!

Stop by my weblog ... http://best-porn.org/

Anónimo dijo...

Howdy just wanted to give you a quick heads up and let you know a few of the images aren't loading properly. I'm not sure
why but I think its a linking issue. I've tried it in two different browsers and both show the same outcome.

Also visit my web site - http://www.orgiasex.com

Anónimo dijo...

You really make it seem so easy with your presentation but I
find this matter to be really something which
I think I would never understand. It seems too complex and very broad for me.

I am looking forward for your next post, I'll try to get the hang of it!

Feel free to surf to my web-site: sexy girls videos

Anónimo dijo...

I do not know whether it's just me or if everybody else encountering issues with your site. It appears as though some of the written text within your content are running off the screen. Can somebody else please comment and let me know if this is happening to them as well? This could be a problem with my internet browser because I've had this
happen previously. Thank you

Review my blog: porn star zafira

Anónimo dijo...

Hola! I've been reading your web site for a while now and finally got the bravery to go ahead and give you a shout out from New Caney Texas! Just wanted to mention keep up the fantastic work!

Also visit my web-site :: Ass porn videos

Anónimo dijo...

clicks here They also offer Continuing Care in levels 2 to 4 if your dog needs help with long-term or chronic conditions. So be honest and open and there should be no problems come claim time. You need to be ready, you need to be prepared and that's what a pet insurance can do for you.

Unknown dijo...

soy nuevo y como se que respalda la informacion