This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Jan 24, 2013 12:05 PM by Bernardo RSS

Backup plan for apex

Bernardo Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    VivekAnanda Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    From apex.oracle.com
  • 6. Re: Backup plan for apex
    VivekAnanda Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    It's not answered
  • 9. Re: Backup plan for apex
    VivekAnanda Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points