Data archival using Partitioning and Partition Maintenance

1. Data archival using Partitioning and Partition Maintenance O/o the A.G.(A&E)-II Nagpur Maharashtra

2. Addressing Key problems in VLC: • Storage • Backup (Time involved) • Performance enhancement • Corruption of data

3. Approach: Two Ways: • Delete Old data • Partition and split partition

4. Advantages/Need of Partition • Do we really need partition? • If we need, what Advantages we get?

5. 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

6. 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”

7. 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

8. 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.

9. 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.

10. 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 )

11. Frequency of Splitting Partition • Time based: • Data Volume base:

12. 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.

13. 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

14. 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>,

15. 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.

16. 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;

17. For adding datafile: • SQL> alter tablespaceVLC_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:

18. 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 );

19. split Partition (iii) Coalesce the free space in both relevant tablespaces: 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:

20. 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';

21. 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.

22. Split Partition 9. Then set the old tablespace (VLC_TXN_DATA01 ) once again as read-only, and take a complete backup.

23. Thank You