How RMAN Restores/Rebuilds Tables

All posts relating to Oracle database administration.

Moderator: Tim...

How RMAN Restores/Rebuilds Tables

Postby dnunknown » Tue Sep 11, 2012 4:47 pm

Hey Tim,

10.2
We have a process that does bulk inserts into a table in our production enviroment to a table, this table is also subject to mass deletes, we will call it table X

Now for development we take the RMAN backup and restore it into a different enviroment, we run the same bulk inserts and the time it takes to run the job is about 1/3 the time. The enviroments are identical. So I am thinking that table X in production when doing the insert is being scanned to try to utilize the empty space given back to the table by the mass deletes. And if this is true then the only thing that would be logical is that when RMAN restores the entire DB the rows are written without these 'holes' of empty space so it simply just appends it to the end of the table causing a much quicker insert

Am I right in my logic here?

Thanks,
Steve
dnunknown
Advisor
 
Posts: 287
Joined: Wed Aug 01, 2007 5:35 pm
Location: Boulder, CO USA

Re: How RMAN Restores/Rebuilds Tables

Postby Tim... » Tue Sep 11, 2012 5:50 pm

Hi.

1) Is the hardware any different? If you have better hardware in the second environment, you would expect a different result.
2) What type of backups are you doing? Image copies are literally a complete image copy, so no "extra" changes can happen. If you are doing backupsets, then RMAN uses unused block compression so it only backs up blocks that are listed as being used in the tablespace bitmap. This is effectively compacting the backup and in some ways compacting the recovered data file. It doesn;t compact data within blocks though.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: How RMAN Restores/Rebuilds Tables

Postby dnunknown » Tue Sep 11, 2012 6:03 pm

1) HW is the same, if not a bit less powerfull than production since it is for development
2) The backup is the standard RMAN backup performed via;
Code: Select all
run {
backup database;
backup archivelog all;
}

Then in development enviroment it is restored pretty much in the same manner;
Code: Select all
run {
restore database;
recover database;
}
dnunknown
Advisor
 
Posts: 287
Joined: Wed Aug 01, 2007 5:35 pm
Location: Boulder, CO USA

Re: How RMAN Restores/Rebuilds Tables

Postby Tim... » Wed Sep 12, 2012 8:08 am

Hi.

The you will have empty block compression and null compression, as they are the default in 10gR2 and above. You should check the extent map on both installation to confirm this has resulted in compression and reduction of the HWM. That would explain quicker FTS.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17962
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 9 guests

cron