Home » RDBMS Server » Server Administration » Reduced Backup as tersting instance
Reduced Backup as tersting instance [message #202122] Wed, 08 November 2006 04:42 Go to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Hi,

I would like to know, how I can create a "backup" of a database (10g) which is reduced in size and volume. The cause is, that our test systems don't need all the datasets of our productive database, about 10 % are sufficient. Of course the new instance must provide a consistent view on the data, i.e. all direct and indirect relationships must be preserved.

Does anyone have an idea and can tell me where to look for a solution? Maybe Oracle has a built-in feature, that can help us with that?

Thank you very much,
Sebastian

Report message to a moderator

Re: Reduced Backup as tersting instance [message #202125 is a reply to message #202122] Wed, 08 November 2006 05:00 Go to previous messageGo to next message
n_de_fontenay
Messages: 33
Registered: October 2006
Location: Paris
Member
Hi,

it is possible to compress the backupsets.
It's a configuration parameter.

Connect to RMAN and type the following:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

The compression rate is pretty good.
One thing you should know is:

Don't use tape compression if you are already using RMAN compression. (as stated in RMAN backup and recovery guide)

hope this helps,

Nicolas

Report message to a moderator

Re: Reduced Backup as tersting instance [message #202132 is a reply to message #202122] Wed, 08 November 2006 05:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
So, you want a smaller working database for testing from a bigger production database?
What kind of testing is this? For performance, dataset matters a lot. Performance testing that data is unfair.
What you can do is
export the prod database with ROWS=n ( no data exported. only the structure tables/indexes/code) is exported.
import into your target/test database.
Then you can use methods discussed here
http://www.orafaq.com/forum/m/14258/42800/?srch=Random+sample#msg_14258
to pull random sample data from prod to test.
Search the forum for more examples.

But there are known pains in this method.
1. Pulling random data may make the integrity constraints very unhappy.
2. You have to manually do this for all tables. If there are too many tables, it may be a pain. Ofcourse, you can script this and make your life much easier.

I was told that some tools like TOAD have the above said functionality as a built-in feature and will resolve all the dependencies by itself. Probably worth to have a look.

I once did the same with RMAN.
Just did a duplicate/clone using TSPITR. Only thing is, i duplicated the database as of nearly 24 months early. The source database by that time was pristine (with just the default entries the 'application' has created and probably 1 weeks worth data) Smile

Report message to a moderator

Re: Reduced Backup as tersting instance [message #202159 is a reply to message #202132] Wed, 08 November 2006 07:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
And if you are planning to do this frequently, make use of materialized views.

Report message to a moderator

Re: Reduced Backup as tersting instance [message #202171 is a reply to message #202125] Wed, 08 November 2006 08:19 Go to previous messageGo to next message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Hi Nicolas,

thanks for the hint, but we explicitly want to reduce the amount of data sets. Compression of the backup would mean decompression before use and that means performance overhead for our developers.

But thanks for your efforts, anyway.
Sebastian

Report message to a moderator

Re: Reduced Backup as tersting instance [message #202174 is a reply to message #202132] Wed, 08 November 2006 08:34 Go to previous message
s-bolz
Messages: 10
Registered: September 2006
Junior Member
Hi Mahesh,

we want the smaller database for our Software developers. There are several teams and the team work on different features. That means, we need several databases for them, to code against. The point here is, that we don't need different copies of our productive data that are based on different times in history. Instead we need different copies in terms of the DDL information of the databases. But every copy needs consistent data, of course.

If I understood your reply correctly, you suggest the following:

  1. Extract the schema information
  2. Rebuild the schema in an empty database
  3. Extract a percentage (e.g. with the SAMPLE clause) from the "origin" tables and insert them into the new database
  4. Extract the data sets from the other tables that reference the extracted data from the origin tables and insert them into the new database as well


Thanks for the link, I have not had the time to look into it right now, but I definitely will. Thanks for the hint on TOAD's built-in feature as well. Maybe this will help, too.

Sebastian

Report message to a moderator

Previous Topic: Restoring from cold backup
Next Topic: OEM issue
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 16:35:37 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.