Priming Oracle Data Files before Migrating to Thinly Provisioned Storage
If there was ever a Golden Rule within database administration I'd venture to say that it would be: "Don't ever let your database run out of disk space". Doing so is a sure fire way to render a database inoperable, increase costs for staff fire-fighting, and, if you're the administrator, lose your job. For these reasons, DBAs and storage administrators have ventured on the side of extreme caution and over provisioned storage for applications--rendering valuable storage unavailable for other systems while inflating capital costs.
Data centers venturing to remedy improper use of storage have turned to thin provisioning as a mechanism to easily allocate space on a just-enough and just-in-time basis. Under thin provisioning, all applications can make use of spare storage, simplify storage administration, and reduce capital budgets.
But because of the internal nature of databases, migrating to thin provisioning can pose some interesting dilemmas if the free space is not dealt with before or during migration--resulting in a thin provisioning system that contains the same wasted space and consumes as much storage as the original system.
By their very nature, databases tend to leave empty space within data blocks through the improper setting of parameters such as block size, PCTFREE, and PCTUSED (in Oracle).Normal application processing such as INSERT, UPDATE, and DELETE operations that chain rows or exasperate the amount of free space within data blocks can also leave empty spaces. While migration and copy utilities for thin provisioning are good at removing free space within data files, some are unable to penetrate and see all free space within Oracle data files. For this reason it is a good idea to prep Oracle data files before hand to reclaim as much free space as possible.
What needs to happen is the compression of data and elimination of free space within the data blocks--basically a reorganization of each and every database structure that consumes storage. If you have an Oracle database, Oracle (10g and above) supplies a nice tool to perform in-place reorganization of data while keeping database objects online and available for application processing.
The Oracle SHRINK (ALTER TABLE...SHRINK SPACE) command will compact space used in a segment and return unused segments back to the table space which effectively packs data blocks as efficiently as possible. It is this shrinking of segments that makes it easier for the migration of data files to thinly provisioned volumes while completely removing wasted free space in them.
Recognize that the SHRINK command only compresses database objects and does not compress within table spaces or data files. As such, it may be beneficial, depending on how table spaces are being managed within Oracle, to look at the ALTER TABLESPACE...COALESCE and ALTER DATABASE DATAFILE...RESIZE commands for table spaces and data files respectively to eliminate all free space within Oracle data files before a migration.
As a member of the Oracle Partner Network and Oracle Unbreakable Linux Program, 3PAR and Oracle work closely together to bring a solution for managing complex database environments while maintaining optimal performance and controlling the increase cost of database storage. Powerful joint solutions such as combining Oracle's 11g database with ASM and 3PAR InServ Storage Servers with Thin Provisioning not only simplifies storage management but enables resource optimization through automated storage provisioning--resulting in a 30% reduction in storage related tasks and a projected 65% increase in productivity for database administrators.
Thin provisioning in a database environment is critical to the proper allocation and usage of storage. Working with a leader in thin provisioning and then properly evaluating your environment requires skill not only from vendors but within each company's data center as well. 3PAR's commitment to research, development, performance, and taking thin provisioning to new limits is well documented and can take care of the vendor side. Just remember, if a database has been improperly configured and maintained there may be additional steps to perform before migration. Shrinking, coalescing, and resizing Oracle data files should be considered the first step in preparing a database for migration to thin provisioning.
Data centers venturing to remedy improper use of storage have turned to thin provisioning as a mechanism to easily allocate space on a just-enough and just-in-time basis. Under thin provisioning, all applications can make use of spare storage, simplify storage administration, and reduce capital budgets.
But because of the internal nature of databases, migrating to thin provisioning can pose some interesting dilemmas if the free space is not dealt with before or during migration--resulting in a thin provisioning system that contains the same wasted space and consumes as much storage as the original system.
By their very nature, databases tend to leave empty space within data blocks through the improper setting of parameters such as block size, PCTFREE, and PCTUSED (in Oracle).Normal application processing such as INSERT, UPDATE, and DELETE operations that chain rows or exasperate the amount of free space within data blocks can also leave empty spaces. While migration and copy utilities for thin provisioning are good at removing free space within data files, some are unable to penetrate and see all free space within Oracle data files. For this reason it is a good idea to prep Oracle data files before hand to reclaim as much free space as possible.
What needs to happen is the compression of data and elimination of free space within the data blocks--basically a reorganization of each and every database structure that consumes storage. If you have an Oracle database, Oracle (10g and above) supplies a nice tool to perform in-place reorganization of data while keeping database objects online and available for application processing.
The Oracle SHRINK (ALTER TABLE...SHRINK SPACE) command will compact space used in a segment and return unused segments back to the table space which effectively packs data blocks as efficiently as possible. It is this shrinking of segments that makes it easier for the migration of data files to thinly provisioned volumes while completely removing wasted free space in them.
Recognize that the SHRINK command only compresses database objects and does not compress within table spaces or data files. As such, it may be beneficial, depending on how table spaces are being managed within Oracle, to look at the ALTER TABLESPACE...COALESCE and ALTER DATABASE DATAFILE...RESIZE commands for table spaces and data files respectively to eliminate all free space within Oracle data files before a migration.
As a member of the Oracle Partner Network and Oracle Unbreakable Linux Program, 3PAR and Oracle work closely together to bring a solution for managing complex database environments while maintaining optimal performance and controlling the increase cost of database storage. Powerful joint solutions such as combining Oracle's 11g database with ASM and 3PAR InServ Storage Servers with Thin Provisioning not only simplifies storage management but enables resource optimization through automated storage provisioning--resulting in a 30% reduction in storage related tasks and a projected 65% increase in productivity for database administrators.
Thin provisioning in a database environment is critical to the proper allocation and usage of storage. Working with a leader in thin provisioning and then properly evaluating your environment requires skill not only from vendors but within each company's data center as well. 3PAR's commitment to research, development, performance, and taking thin provisioning to new limits is well documented and can take care of the vendor side. Just remember, if a database has been improperly configured and maintained there may be additional steps to perform before migration. Shrinking, coalescing, and resizing Oracle data files should be considered the first step in preparing a database for migration to thin provisioning.
Newsletter Signup
Leave a comment