1 2 Previous Next 21 Replies Latest reply: Jan 24, 2013 2:05 PM by Bernardo RSS

    Backup plan for apex

    Bernardo
      I would like to know if someone of you guys can give me some advices or recommend me some backup plan for my workspace.

      The biggest table have more than 80.000 records and we have daily records and our team need to backup our information. So I need an advice for doing that and how... I tried to export the table that have 80.000 records and I got a time out error. Then I downloaded the infomation to xml but I could not upload it... so please, could you recommend me some simple way for doing it

      Thanks!

      Regards, Bernardo
        • 1. Re: Backup plan for apex
          Matthew Morris
          I tried to export the table that have 80.000 records and I got a time out error.
          You don't say what utility you are using to do the export. I'm guessing the data unload in SQL Workshop. Not really a tool I'd use for a robust recovery scheme. For what you indicate, I'd create a script to export the table(s) you need using Datapump (expdp). Google will locate you a dozen sites telling you the specifics of exporting tables and/or schemas. One you have a script that exports the data that you need to have, then set up a job to run the script in the early AM each day. Keep the most recent 5-10 dump files just in case you lose some data and don't notice for a few days.
          • 2. Re: Backup plan for apex
            Bernardo
            Thanks Matthew, I'm going to try with your advice. I hope that in APEX I have the permissions for doing it...

            Regards, Bernardo
            • 3. Re: Backup plan for apex
              Bernardo
              CREATE directory data_dump_dir AS 'Dumps'
              I got this error:

              ORA-01031: insufficient privileges

              I think that in APEX I don't have the privileges... Could you please telle another way to make a plan backup...
              • 4. Re: Backup plan for apex
                Vivek Ananda
                Bernado,
                Is apex running local in you organization or are you trying to export from apex.oracle.com ?

                Vivek
                • 5. Re: Backup plan for apex
                  Bernardo
                  From apex.oracle.com
                  • 6. Re: Backup plan for apex
                    Vivek Ananda
                    Bernardo,
                    Try to export to text file. This would generate a .csv file, which can be imported back.

                    Vivek
                    • 7. Re: Backup plan for apex
                      Bernardo
                      Like I said that are the first things that I did... but it's not enough... I need a better backup plan.... I got time out errors when I'm using the sql worshop utilities...
                      • 8. Re: Backup plan for apex
                        Bernardo
                        It's not answered
                        • 9. Re: Backup plan for apex
                          Vivek Ananda
                          Apex website does not give an other way to load the data back. I would suggest you split the files to load the data. Future loads can be incremental which takes only data that changed and that would back it smaller files.

                          Vivek
                          • 10. Re: Backup plan for apex
                            Bernardo
                            aaah... Could please tell me how to do that?? Can I set up a job to do that?? for do that daily or weekly....
                            • 11. Re: Backup plan for apex
                              Matthew Morris
                              Could please tell me how to do that??
                              Didn't realize you weren't using your own instance. First rule is that if this is really critical data, it shouldn't be on apex.com at all. From there, you have no direct access to save data to a directory.

                              The most automatable way in which to get your data that I can think of is to create a CSV and then email it to a given address. Once it's working, schedule the procedure to run periodically. A more production version would probably take a parameter like 'Daily/Weekly/Monthly' and use this to set the filename (i.e. 'data_backup_wed.csv' or 'data_backup_week' or 'data_backup_month') to assist in grandfathering your exports.

                              A (very) rough example follows:
                              CREATE PROCEDURE rough_backup_example
                              AS
                                  v_csvdata    CLOB;
                                  v_email_id   NUMBER;
                                  CURSOR C1 IS 
                                    SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME 
                                    FROM   EMP E, DEPT D 
                                    WHERE E.DEPTNO = D.DEPTNO 
                                    ORDER BY EMPNO;
                              
                              BEGIN
                                  FOR v_Lp IN C1 LOOP
                                      v_csvdata := c_csvdata ||
                                         C1_R.EMPNO || ',' ||
                                         C1_R.ENAME || ',' ||
                                         C1_R.SAL || ',' ||
                                         C1_R.DEPTNO || ',' ||
                                         C1_R.DNAME || CHR(13) || CHR(10);
                                  END LOOP;
                              
                                v_email_id := APEX_MAIL.SEND(
                                      p_to        => 'destaddress@company.com',
                                      p_from      => 'destaddress@company.com',
                                      p_subj      => 'Backup file for ' || TO_CHAR(SYSDATE, 'DD-MON-YY'),
                                      p_body      => 'Backup file for ' || TO_CHAR(SYSDATE, 'DD-MON-YY'));
                              
                                APEX_MAIL.ADD_ATTACHMENT(
                                          p_mail_id    => v_email_id,
                                          p_attachment => v_csvdata,
                                          p_filename   => 'Data_Backup_' || TO_CHAR(SYSDATE, 'MON_DD') || '.csv',
                                          p_mime_type  => 'application/csv');
                              
                              END;
                              /
                              • 12. Re: Backup plan for apex
                                Bernardo
                                Thank you very much Matthew.... Ook, now if I woud like to import that if I lost some information from my database... Can I also create a procedure to import the csv into the tables that I lost the information? If so, should I truncate the table and import the backup file? What is the best what to do it?

                                PS.- Is there another way to export the data?
                                • 13. Re: Backup plan for apex
                                  mobra
                                  The biggest table have more than 80.000 records and we have daily records and our team need to backup our information.
                                  You do know that apex.oracle.com is not intended for production applications, right?

                                  +"Oracle provides the apex.oracle.com service for demonstration purposes only. Storing of production or sensitive data is prohibited. For production applications you can install Application Express on-premise or sign up for the Oracle Database Cloud Service."+

                                  - Morten

                                  http://ora-00001.blogspot.com
                                  • 14. Re: Backup plan for apex
                                    Matthew Morris
                                    If so, should I truncate the table and import the backup file? What is the best what to do it?
                                    In my book, the easiest way to import from a CSV file in a controlled fashion is to create an external table against the file. Then you can issue SQL against it and access just the data that you need in whatever way that you need it. For example, say you had an employee table and some dummy meant to update one employee's last name to 'Smith', but forgot the WHERE clause and ended up setting every employee's last name to Smith. If you had a CSV backup of the table as the target of an external table, you could use it to grab all employees last names and employee IDs and fix the data without having to re-import everything.
                                    PS.- Is there another way to export the data?
                                    Without more details on what you're looking for to be different, I'm not going to try to think of an alternate export method. The method as supplied gives you a file that is very portable and a process that can be automated. It would be nice to automate further in such a way that instead of being emailed to someone that the file was placed directly on a filesystem. However, apex.com has no means of accessing 'your' filesystem, so that's not practical.
                                    1 2 Previous Next