Rotating Header Image

Oracle for the SQL DBA… Part 1 of a bunch LOL

So i’ve been a bit quiet lately as things have been very crazy in my world… which is good cause that means I’m staying busy and out of trouble but I thought I would drop a quick horribly long blog about some of the stuff I’m working on right now.  One thing that seems to of landed on my plate is doing some Oracle DBA type stuff. 

Now I am NOT an Oracle DBA and this post will prove that so if you are an Oracle DBA and have something easier then my final “solution” please drop me some comments cause this seems way to painful to be the “right” way LOL.  As much as I’m an evangelist for SQL Server I’m wanting to learn more and more Oracle because you never know when something will get thrown over the fence right?

The following “use-case” came my way… “We’d like to have a fresh copy of our PROD database over to a new DEV database leaving the original DEV database intact”.

Seems easy enough… in my SQL Server environment I would restore from last nights backup of production and specify a new database name and filenames, paths, etc during the restore and bippidy-boppidy-bo we’re done like dinner.

This is where my journey of pain begins LOL (this is a long journey so feel free to skip to the end for my final solution or laugh at me along the way for your cruel fun and enjoyment hahaha)

I applied the logic I would use in SQL Server and thought about doing a “Database Recovery” using the Oracle Enterprise Manager 10G.  Hmmmm in order to use Enterprise Manager you need to be logged into a database instance though… I can’t log into an instance because I’m trying to restore one… I can’t restore one cause I can’t log into an instance… I can’t log into an instance because I’m trying to restore one… I can’t restore one cause I can’t log into an instance… *sigh*

Next stop… Database Configuration Assistant.  I thought I might find something useful there…. and I kind of did with “Create a Database”.  So I created a general purpose database cause I thought it’ll just be a place holder for my restore anyways so next, next, next, next, finish.  BOOYEAH! New database created.  For the sake of this post we’ll call this MYDBDEV2.

OK back to Enterprise Manager… and I start down the road of recovery

(Guilty Pleasure = Eminem)

So I go to perform a Whole Database Recovery and Enterprise Manager gives me this…

“The database will be shut down and brought to the NOMOUNT (STARTED) state to restore the control file first. Are you sure you want to shut down the database now?”

Sure… that sounds good.

OK so back to “Perform a Whole Database Recovery”… Can I just take a moment to complain about the “save as preferred” checkbox that NEVER saves… I digress lol

OK so I go to restore “Directly from a specified backup” and enter in the path to my backup file and my next stop is: “The operation for restoring the controlfile may take some time to complete. Are you sure you want to execute it now?”

Sure… that sounds good.  (All the Oracle folks reading this at this point I’m sure are laughing… but that’s fine LOL I can laugh now too about it)

Hmmmm so I viewed the details and it tells me… “ORA-01103: database name ‘MYDBPROD’ in control file is not ‘MYDBDEV2′”

*sigh* well yes I know that… so we’ll fast forward through the rest a bit here and give the quick run down of the rest of my pain before what I finally came up with that worked… so here we go:

  • Deleted MYDBDEV2 and created a new database named the same name as the production database MYDBPROD
  • That failed because I didn’t have the same file structure
  • I used a “database template” of MYDBPROD to create a database called MYDBPROD on the DEV server
  • I restored overtop of the MYDBPROD on the DEV server with a backup but that wouldn’t start cause my REDOLOGs were wrong
  • Deleted the REDOLOGs and it started up
  • Then the TEMP01.DBF file was giving me problems
  • Added a new TEMP.DBF file, set it to default, dropped the old TEMP01.DBF file
  • Went through a bunch of pain with NID command to rename the database
  • I mean a BUNCH of pain
  • MEGA, MEGA PAIN
  • Eventually got it to work

WOW…. ouch.

I then started playing around with the “Clone Database” tool in Enterprise Manager and here is what my final solution/steps look like: (This is where I want Oracle DBAs to comment on an easier/better solution LOL)

MY FINAL SOLUTION (for now LOL)

  • Create a new database (from a template of PROD) on the DEV server named the same as the database on the PROD server
  • Perform a recovery and restore the PROD backup over top of the DEV databases (that’s named the same as the PROD database)
  • Clone the DEV database that’s named the same as the PROD database and give it the name you want MYDBDEV2
  • Delete the DEV database that’s named the same as the PROD database

 This CAN’T be the best way to do this… I just don’t believe it.  So all you Oracle DBAs out there… you’ve had a good laugh at my expense LOL drop me some comments and help me help you (well I’m not actually helping you so that doesn’t really apply…but your help would be greatly appreciated 🙂 )

In all honesty this was a really fun exercise for me.  It’s nice to go into something totally blind and plug your way through it, get to a solution, find a better one, and keep on refining the process.  I learned a tonne of stuff and am wanting to do and learn more and more! 🙂  Oracle is like how PowerShell is/was for me… it’s fun to learn stuff that you don’t have a background in and after 13 years working with SQL Server I still learn new things but it’s a total different feeling with something so foreign to me.

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

3 Comments

  1. Allen Kinsel says:

    as with all things DBA “it depends” but heres a small tip depending on how big of a db your moving. look into the imp/exp tools. as with many things SQL Server, the GUI is not always the easiest way, not to mention IMHO the Oracle GUI tools are 100% worse than SQL’s HTH and if you need more, you have my email

  2. Chris Peirson says:

    Its funny reading your pain. I have gone through this before.
    I concur with Allen. You’d think the enterprise manager would be much like SQL 2000’s enterprise manager and be able to do much of this stuff easier. It is good for some things, but for a database copy like this you really want to look at setting up import/export scripts for the more easily manageable databases. Once you have scripts setup, it becomes much faster and easier.

    -CP

  3. In recent years, Adobe has introduced more lightweight — and free — software that makes it easy for users to create multimedia projects on mobile devices. Of course, Adobe is also hoping this approach lures more people, beyond just professional photographers or video editors, into its Creative Cloud.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.