13 Replies Latest reply: Apr 19, 2012 10:16 AM by Mark Malakanov (user11181920) RSS

    Data Archive and Resue

    899401
      Hi,

      I waned to know some examples of data archiving and reuse it once again. I heard about fragmentation,shirnk and other methods but just wanted to know how to use that
      example is apperciated


      thanks
        • 1. Re: Data Archive and Resue
          713555
          this question is confusing.

          what are you trying to do and what do you want to do with what youve done when you done it?
          • 2. Re: Data Archive and Resue
            Aman....
            896398 wrote:
            Hi,

            I waned to know some examples of data archiving and reuse it once again. I heard about fragmentation,shirnk and other methods but just wanted to know how to use that
            example is apperciated
            Apples, oranges and coco is what you are saying. It would be better if you start with telling us your db version in 4 digits and exactly what you want to do, why and what you have planned for that so far?

            For teh data archive, you are already told a good reply on another thread of yours for the same topic. Don't create too many threads on the similar topic. Not only it would be confusing for the people who would attempt to reply but also for you to track.
            Re: Data Archive_help

            HTH
            Aman....
            • 3. Re: Data Archive and Resue
              899401
              Hi,

              Database version is 11g.can i get some practical examples on this?

              thanks
              • 4. Re: Data Archive and Resue
                Aman....
                896398 wrote:
                Hi,

                Database version is 11g.can i get some practical examples on this?
                How about this that you try something, post it here and we shall comment (and if required, correct) on it?

                Aman....
                • 5. Re: Data Archive and Resue
                  713555
                  you have old data going back a few years yes? only you know your data but lets say you want archive by year

                  If you current table is prod_tb
                  create table prod_tb_2011 as select * from prod_tb where <datecol> = 2011;

                  delete from prod_tb_2011 where <date_col> = 2011

                  repeat for other years. only you know your data and how you want to archive.

                  why do you want to archive? have you fully researched why you would archive? unless youre going into VERY large tables that you might want to take out of a nightly backup can you give a good reason why you want to archive assuming youve been looking after the table right.

                  Edited by: deebee_eh on 18-Apr-2012 05:41

                  Edited by: deebee_eh on 18-Apr-2012 05:41
                  • 6. Re: Data Archive and Resue
                    899401
                    Hi,

                    actually i wanted to archive the data rsuse it again as when it is required and this is the first time i am doing this and i do not know what is the correct methods
                    Please suggest some best methods as it is client requirement and with steps to follow because i new to this steps


                    thanks
                    • 7. Re: Data Archive and Resue
                      713555
                      if they want to use it again for querying join the archive table to any query either in a join statement or in a master view, ie

                      select * from
                      (prod_tb
                      union
                      prod_tb_2011
                      union
                      prod_tb_2010
                      ..
                      etc)

                      or create a view which does above and give the view to business.

                      Again I ask why do you want to archive? Querying old data is a business requirement which shouldnt be your concern, archiving old data is your concern and you must have a valid reason for doing it.
                      • 8. Re: Data Archive and Resue
                        899401
                        Actually client wants to reduce the database size while archiving the data fr 2009 year so he wanted to know if the records gets archived\deleted for 2009 year how much space will be released and additional to that he wants to archive or back up it and use it once again as when it is required

                        pls help with steps


                        thanks

                        Edited by: 896398 on Apr 18, 2012 8:38 AM

                        Edited by: 896398 on Apr 18, 2012 8:39 AM
                        • 9. Re: Data Archive and Resue
                          713555
                          he has a couple of ways, assuming theres no chaining in the table,

                          create table prod_tb_2009
                          as select * from prod_tb
                          where some_col = 2009

                          select bytes/1024/1024 from dba_segments
                          where segment_name = 'PROD_TB_2009"

                          or doa expdp with tables= and a query and look at the file size., either way , it doesnt matter. If you create the archive table then you leave the data in the database so youre not reducing size of the database, if you export it and keep it in a dump file, you have the data on a dump file so your file system holds it instead = overall file system usage much the same.

                          ask client why does he want to archive yet still query whenever he wants? databases were invented to hold data. sometimes they do it quite well.
                          • 10. Re: Data Archive and Resue
                            Mark Malakanov (user11181920)
                            he wants to archive or back up it and use it once again as when it is required
                            Hmm. How do you or he imagines that reuse process?

                            When he asks about archival he wants more free space, right?
                            Because he needs some free space to be filled with some new data, right?
                            Imagine, after one year he will fill all free space on the server.
                            And then suddenly he will need to reuse the data archived.
                            Where he will place it?

                            Adding to what debeeeh said: you can also create a separate tablespace and create archive table in it. Then for archival you make it read only, take it offline, backup its files to tape and remove them from HDD.
                            For restore you bring files back from tape, and take TS online.

                            Also in addition to it you can use partitions to swap the archived table with a partition of the working table so the archived rows will be logically added to working table rows.

                            Edited by: user11181920 on Apr 18, 2012 2:10 PM
                            • 11. Re: Data Archive and Resue
                              899401
                              Also in addition to it you can use partitions to swap the archived table with a partition of the working table so the archived rows will be logically added to working table rows.

                              Hi,

                              can u Please give a simple example on above statement


                              and now it is very clear that after archiving the data table space will not be reduced but can get one simple example for data archive

                              thanks
                              • 12. Re: Data Archive and Resue
                                713555
                                have you enterpise edition with partioning? if so, you need to start reading up on partitioning and all that entails. If you dont, you cant use it.

                                You see my table example above to archive 2009 data, create that table in its own tablespace, move the table space off when your not using it, back it up somewhere else. but it doesnt matter how you do it. youre moving the data from place to another. either a table or a partition. then putting that data somewhere else. if you keep it on the same file system either in the DB or Out you have not saved any space. And it doesnt matter whether its an export or its own tablespace

                                So lets say you back it up to tape off disk, customer has to tell you to restore (either tablespace or export, doesnt matter as long as you can get to it) when he wants it. can you do this? Have you the space to do this.

                                I know sometimes its easy to get annoyed when we ask about higher level stuff but can you please answer why they want to archive data?
                                if its storage then your path is a little clearer you got to move to tape with whatever archival method you use, and then you have to asnwer do you have space to move it back in if you needed.
                                If its archiving for performance then you need to look at performance first, not archiving. prove that it cant be tuned. Youll have a number of guys come back to you on here about that though.
                                • 13. Re: Data Archive and Resue
                                  Mark Malakanov (user11181920)
                                  Also in addition to it you can use partitions to swap the archived table with a partition of the working table so the archived rows will be logically added to working table rows.
                                  can u Please give a simple example on above statement
                                  Example for just one table. What you can use as a partition key may depend on your existing table structure and row data. If data allows it can be optimized by swapping empty partition with existing table (which is very fast) (not covered here).

                                  1. create tablespace ARCHIVE1900 datafile '/replaceable_disk/data/archive1900.dbf'

                                  2. create table WORKING_PARTITIONED (YEAR number/* it will be the partition key*/, OTHER_COL number, YETANOTHER_COL varchar .....) partition by range(YEAR)
                                  ( partition ARCHIVE1900 values less than (2000) tablespace ARCHIVE1900,
                                  partition DATA2000 values less than (2010) tablespace DATA2000,
                                  partition DATA2010 values less than (2020) tablespace DATA2020 )

                                  3. move data into WORKING_PARTITIONED from WORKING. Drop WORKING. Rename WORKING_PARTITIONED to WORKING.

                                  4. Archiving
                                  4.1 create table WORKING_ARCHIVE1900 tablespace ARCHIVE1900 as select * from WORKING where 1=0;
                                  4.2 alter table WORKING exchange partition ARCHIVE1900 with table WORKING_ARCHIVE1900 UPDATE GLOBAL INDEXES;
                                  Now all data from WORKING partition ARCHIVE1900 is in WORKING_ARCHIVE1900 table.
                                  WORKING partition ARCHIVE1900 is empty.
                                  4.3 alter tablespace ARCHIVE1900 read only;
                                  4.4 alter tablespace ARCHIVE1900 offline;
                                  4.5 on OS lever unmount or eject /replaceable_disk

                                  5. Restoring from archive
                                  5.1 mount /replaceable_disk so you have '/replaceable_disk/data/archive1900.dbf' available
                                  5.2 alter tablespace ARCHIVE1900 online;
                                  5.3 alter table WORKING exchange partition ARCHIVE1900 with table WORKING_ARCHIVE1900 UPDATE GLOBAL INDEXES;
                                  Now all data from WORKING_ARCHIVE1900 table is in WORKING partition ARCHIVE1900. WORKING_ARCHIVE1900 table is empty.

                                  enjoy.

                                  The example is approximate. Here may be syntax errors. so test the approach thoroughly before implementing.