Mirroring production data onto reporting data

[Henrique Silva_3]

Application needs a reporting tool that needs to access same structure of production database ( they are working on a data warehouse model, but that will take time ).
So, for now, we will implement oracle or some 3rd party replication OR, the easiest right now, Business Copy for the XP ( SAN ). Business copy will mirror the database files into a different set of disks that can be mounted on another system for reporting purposes, thus, taking the load off the production box.

question.

how do I bring up the mirrored database up so that there is no confusion to which database it is being accessed, since the global instance name will be the same ? Or, can I simply setup two different global instance names based upon two different init.ora files ? Or public links ? Anyone has done this in the past ?

thanks,

Henrique Silva

[John Poff]

Hello Henrique,

We do something like that here. I'm not an Oracle DBA, but the way I understand it is that on the reporting side you would use a different oracle home, with an init.ora file that gives the mirrored copy a different instance name. We have a test/development system where we update the database every couple of weeks with a copy of the production side. I split off the copy and mount it up on the test system, and our DBAs run some scripts and make it come up with a different instance name.

JP

[Graham Cameron_1 · Accepted solution]

Henrique
This is something we do here every day - we use a business copy (similar to a mirror split) on our XP to generate 2 or 3 copies of the production database for data mining, testing etc.
To make thos work we use a couple of tricks.
On the prod database, before the split, invoke (from SQL or svrmgrl) "alter database backup control file to trace".
This produces, in the user_dump_dest directory, an ascii dump of the control file.
Then we use some scripts to edit this file to reflect the file system after the split. The key here is that the first line of the file becomes
"CREATE CONTROLFILE REUSE SET DATABASE "" RESETLOGS NOARCHIVELOG".
Then you can start up the copy database using the modified create control file sstatement (you then need to do "ALTER DATABASE OPEN RESETLOGS") and away you go.
BTW - don't forget to update the GLOBAL_NAME table.

[Henrique Silva_3]

Thanks guys.

so, by doing getting the trace file and doing the reuse database and reset logs, you can basically change the instance name ? I was under the impression that with oracle, there were two things you could not do once the database had been created. Change its name and change the block size !!!

I will try it out !!!

thanks a lot

Henrique Silva