Transcript Data archival using Partitioning and Partition Maintenance

Data archival using Partitioning
and Partition Maintenance
O/o the A.G.(A&E)-II
Nagpur
Maharashtra
Addressing Key problems in
VLC:




Storage
Backup (Time involved)
Performance enhancement
Corruption of data
Approach:
Two Ways:

Delete Old data

Partition and split partition
Advantages/Need of Partition


Do we really need partition?
If we need, what Advantages we get?
Advantages






To reduce the size of the files that needs to be
backup
To reduce the load on Backup
To avoid data Corruption due to multiple tables.
Partitions can be altered, dropped, rebuild,
merged and truncated.
Partitions are held & managed independently.
The partitions tables can be queried and
updated
What is partition :


“Decomposing the very large tables and
indexes into smaller and more
manageable pieces are called partitions”
“A single logical table can be split into
number of physically separate pieces
based on range of key values, each of the
parts of the table is called partition”
In what type of organization it
is useful:
Partitions are especially useful in data
warehouse applications, which commonly
Store and analyze large amounts of
historical data.
i.e, VLC/GPF/Pension

Pre_requisits




Identify the tables to be partitioned on the basis
of size.
All Partitions of a table or index have the same
logical attributes. (the table share the same
column and constraints definitions.
All Partitions in an index share the same index
columns.
The Physical attributes can be different (storage
in different segments or different table space.
Partition and Split Partition:

“Partition “ refers initial partition of
tables, indexes .

“Split partition” refers to subsequent
partitions to be done in frequent
intervals as decided by the user.
Partition implementation in
VLC





It has been seen that in VLC software developed
by TCS for our zone, more than 50 % of the
data is associated with tables and indexes
pertaining to four Tables, viz
VCHR ( Voucher table )
VCHR_AMNT_DTL (Table storing additional
attributes of voucher, amounts etc.)
TRSRY_ACNT_DTL (Table storing LOP/Cash
Account, and the Major Head wise abstract of
Compiled accounts, like PWD/IRD/FRD)
PYMNT_CLSFD_ABSTRCT. (Table storing the
posted data )
Frequency of Splitting Partition

Time based:

Data Volume base:
Steps involved in initial Partition(for Non-Partitioned table):
1. Create Two Table spaces ex:
Create table space<tablespace name1>
datafile ‘ datafile name’ size 600M
default storage(initial 10M
Minextents 1 maxextents unlimited
pctincrease 0);
2. Create temp table as select * from target table
3. Drop old table cascade constraints.
4. Create new table with partition clause.


Option 1: Now , one must put in place the constraints
for the table.
then insert in the data from temp table, and enable all
constraints for the other table, basically FKs pointing to
the target table.
Initial Partition

Option 2 : instead of option 1 , to avoid
fragmentation, we can export the user
(i.e, nagmain) , and re importing it into the database.

If we go for option 2 , the modified steps will be:
5. after step 4, do nothing, ie do not enable any
of the constraints. after re importing the data,
enable the constraints this will also ensure that
the index on PK is also partitioned.
6. insert the data from temp table to original table
7. Create the indexes
Creatition of partitioned table









Create table<tablename>
(colm1,2,3)
Pctfree 10
Pctused 40
Partition by range<rangeid>
(partition<partition table name1>
Values less than <range> tablespace<tablespace name1>,
(partition<partition table name2>
Values less than < maxvalue > tablespace<tablespace name2>,
Steps involved in Split
Partition:
1. Take a cold backup (complete) including read only files.
2. Set the tablespace containing old partition data as
read-write;
Syntax: Login as a dba user, and issue command:
SQL> alter tablespace <table space name>
read write;
3. Allocate sufficient additional space in the above
tablespace.
4 Resizing the data file ‘ or ‘
Adding a new data file.
Split Partition





Syntax: Login as a dba user, and issue the
commands:
For resizing the file:
SQL> alter database datafile ‘/path/datefile
name.dbf’ resize n M;
ex: alter database datafile
‘/home2/agnag2/txn_data01.dbf’ resize 500M;
For adding datafile:


SQL> alter tablespace VLC_TXN_DATA01 add
datafile ‘path/ datefile name.dbf ’ size n M;
ex: alter tablespace VLC_TXN_DATA01 add datafile
‘/home2/agnag2/txn_data02.dbf’size 500M;
5. The subsequent steps depend on the table being
partitioned.

(a) For VCHR (assuming split at gnrtd id of 60000 and spitting
at higher end of partition vchr_P2):
(i) Coalesce the free space in both relevant
tablespaces:

Split Partition




Syntax:
SQL> alter tablespace VLC_TXN_DATA01 coalesce;
SQL> alter tablespace VLC_TXN_DATA02 coalesce;
(ii)
Split the partition using following
command :
SQL> alter table vchr split partition
vchr_p2 at (60000) into (
Partition vchr_p2 tablespace vlc_txn_data01 ,
Partition vchr_p3 tablespace vlc_txn_data02 );
split Partition
Coalesce the free space in both relevant
tablespaces:
(iii)
SQL> alter tablespace VLC_TXN_DATA01 coalesce;
SQL> alter tablespace VLC_TXN_DATA02 coalesce;
(iv) Rebuild the indexes pertaining to the table, which
are shown with status unusable in the dba view:
dba_ind_partitions. The command for this can be
generated by firing the query given below and spooling
the output:
Split Partition




select 'alter index '||index_name||'
rebuild partition '||
partition_name||' ;'
from dba_ind_partitions
where index_owner=’owner
name’ ( ex: 'NAGMAIN')
and status='UNUSABLE';
Split Partition
6. Similar steps mentioned in Sr.No.5 above
should be followed for other tables
7. Finally, after the partitions have been split, and
data moved from tablespace current
tablespace to old tablespace
(ex:VLC_TXN_DATA02 to
VLC_TXN_DATA01,)
8. try to resize the <old tablespace>
(ie.,VLC_TXN_DATA01) datafiles to the
minimum possible.
Split Partition
9. Then set the old tablespace
(VLC_TXN_DATA01 ) once again as readonly, and take a complete backup.
Thank
You