This forum is currently locked. You can't register or post questions at this time. (read more)

How RMAN Restores/Rebuilds Tables

All posts relating to Oracle database administration.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

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

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;
}

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron