This content has been marked as final.
Show 21 replies
-
1. Re: Backup plan for apex
Matthew Morris Jan 10, 2013 9:21 PM (in response to Bernardo)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 Jan 10, 2013 10:15 PM (in response to Matthew Morris)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 Jan 11, 2013 2:05 PM (in response to Bernardo)
I got this error:CREATE directory data_dump_dir AS 'Dumps'
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 Jan 11, 2013 2:29 PM (in response to Bernardo)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 Jan 11, 2013 3:46 PM (in response to Vivek Ananda)From apex.oracle.com -
6. Re: Backup plan for apex
Vivek Ananda Jan 11, 2013 4:03 PM (in response to Bernardo)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 Jan 11, 2013 4:15 PM (in response to Vivek Ananda)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 Jan 11, 2013 4:19 PM (in response to Bernardo)It's not answered -
9. Re: Backup plan for apex
Vivek Ananda Jan 11, 2013 4:20 PM (in response to Bernardo)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 Jan 11, 2013 4:28 PM (in response to Vivek Ananda)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 Jan 11, 2013 6:33 PM (in response to Bernardo)1 person found this helpfulCould 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 Jan 11, 2013 7:01 PM (in response to Matthew Morris)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
MortenBraten Jan 11, 2013 8:38 PM (in response to Bernardo)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 Jan 12, 2013 12:30 AM (in response to Bernardo)1 person found this helpfulIf 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.