Creating Oracle instance on a snapclone..

[Kenneth Platz]

Hey everyone,

I've got a fairly interesting question here, but I don't think it's exactly *new* territory. I mean, someone else has to have done this before.

We currently have 3 rp7400 servers which are part of an MC/ServiceGuard cluster and connected to our EVA. Nodes 1 and 2 run our production instances of Oracle Parallel Server (OPS, version 8i), and node 3 acts as our test/development/training server, and also acts as our Veritas Media Server.

Our production servers use raw partitions on the volume groups /dev/vgp_ops1 and /dev/vgp_ops2, which are presented (identically) on all 3 nodes. During our backup each night, we create a set of snapclones of these volume groups (or, I should properly say, the LUNs associated with these volume groups) and present these over to our media server (again, node 3). We then perform our backup of these snap clones and keep the snap clones around until just prior to the next night's backup. After a number of headaches and calls to HP's Response Center, this setup is now working very well for us.

However, we now have a new need -- apparently one of our business analysts periodically runs a number of Crystal Reports against the production data, and on occasion, these reports get so intensive that they have consumed all the resources on our production instances and caused those instances to crash pretty hard. What we'd like to do is to create an oracle instance that uses the snap clones as a duplicate of production... that way Mr. Business Analyst can go about and flail around with that copy all he wants without affecting production. Among other things, we would need to change the name of the instance in those datafiles and do some other magic to get this to work.

Does anyone have some example scripts set up for this? It would have to be scripted, obviously, since we'd want to be able to shut down and start up this instance each night prior to and following the evening's backups.

Thanks in advance!

[Brian Crabtree]

Hmmm. Well, it can be done. You would want to wait until your backup has completed, because to change the DB name, you are going to have to perform a recovery on the database to be able to open it.

The way that I do it is, we sync up the data and mount it on to the second system, take a copy of the controlfile trace, change the db name in the trace, get a set of the archivelogs that have been generated, bring up instance, recover the database forward, and open it.

While it can be scripted, it isn't easy. I have one, but it is heavily modified for my enviroment.

Thanks,

Brian

[malay boy]

Hi,
Hope this help.We have MC SG and mirror.what we do is every night we down the MC sg and split the mirror.After split the mirror,we mount the mirror copy and start the database ( 2 database , one in mirror and one existing lv) .So now we have two database running.
We run a backup on mirror copy database and asked the developers to run all the consuming report on mirror copy database.
When backup and report are done, we merge back the mirror copy.

Hope this make sense.

regards
mB

[Sunil K Shetty]

Hi Kenneth,

Even my site had an similar requirement, our data analysis team were generating reports for reporting which at times was consuming all the resources, almost grinding the Production DB to halt. We opted for snapshot rather than going for DB cloning everyday, we identified set of high volume transaction tables and created a snapshot for these tables in a secondary database(different server) and refreshed these snapshots every 2 hours, for small master tables we gave select privilege to production DB through a database link. All we do now is to monitor the jobs in the secondary database. This method has reduced the load on the Production DB significantly.

I guess unless your Business analyst need to access all the objects, this method should work for you.