Solved

making periodically backup

Posted on 2014-02-08
20
327 Views
Last Modified: 2014-02-14
hi
i'm a client on the server
i would like to have periodically backup for my user
what is the best way to perform the backup with a smallest amount of data size ?
0
Comment
Question by:NiceMan331
  • 8
  • 6
  • 5
  • +1
20 Comments
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 39845248
If you are not a privileged user, then you are going to be very limited on the methods you can use.

The 2 that I can think of are the original export (doc here -> http://docs.oracle.com/cd/E16655_01/server.121/e17639/original_export.htm#SUTIL3634) or using a tool such as SQL Developer to dump all the data to CSV files (which could be time consuming and a pain, but it should be possible).
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 39845726
Hi!

To be able to export and import from your schema you will need to ask your DBA to grant to your user the  EXP_FULL_DATABASE and  IMP_FULL_DATABASE privileges.

Regards,
    Tomas Helgi
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39845973
The 'best' thing to do is to not make live changes to the system.

I create SQL scripts for everything I do.  Once things are 'tested' and ready to move into production, just run a known/tested script.  It helps reduce the chance of making a mistake.

Once things are done, merge the current SQL script in to a master script.

You then have a script that can recreate ALL your database objects any time you want.
0
 

Author Comment

by:NiceMan331
ID: 39846548
sligtw
it seemed good idea
could you pls explain what the sql script will contain ?
is will contain the current data ?
could you post an example
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39847028
You do not need the EXP_FULL_DATABASE role to export data in your own schema.  As the privilege implies, you need it to do a FULL export.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39847931
>>could you pls explain what the sql script will contain ?

It will be the DDL commands that you will be applying to the database.

As far as an example:  If you wanted to add a column to a table, don't do it through a GUI.  save the statement in a .sql file:
alter table some_table add mynewcolumn char(1);

Then edit your 'master' script to add the new column to the create table statement.

Then if something happens and you need to rebuild that table, you have the DDL in the master script file.

If you are also after the data in the tables then you will need a different approach.  I would agree that exp/imp will probably be the best choice for remote backups unless you want the backup on the database server itself.
0
 

Author Comment

by:NiceMan331
ID: 39849268
i got it
actually i want to backup the data itself , not the structure of the tables
is exp/imp will works from my pc to the server ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39849869
Yes, exp will work for that.  It will capture structure and data.
0
 

Author Comment

by:NiceMan331
ID: 39849925
i remeber one database admin adviced me before to copy the datafile itself , not the reg exp/imp
what is the best advice here
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39849970
If all your data is stored in one tablespace, and only your data, then you could do that but you still need to use copy it properly by making it into a transportable tablespace set.  That still requires exp and access to the server to copy the file(s).

Doc for transportable tablespace is here -> http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#SUTIL2784
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:NiceMan331
ID: 39854054
ok
what is the correct script for exp / imp
please note that the database contain many oracle_users
i want to dmp one user only
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39854337
Documentation link was posted in my original comment.

The simplest form of what you want is:

exp <user>/<pass>@<DB> FILE=backup.dmp

<user> is the user you want to export
<pass> is the password for that user
<DB> is the tnsnames entry for the database
backup.dmp will be the file that contains the export.
0
 

Author Comment

by:NiceMan331
ID: 39859020
backup done , exp
1- if i add full = yes , it will export all users in the same database ?
2-how to scedule that exp weekly basis ?
3-what is the correct script for imp
4-how to make it from sql developer ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39859067
1:  full=y is EVERYTHING.
2: OS scheduling.  Windows: Task Scheduler.  Unix: cron
3: Set up the Oracle environment variables like ORACLE_HOME and OS PATH then it is the single exp command.  There are tons of export script examples on the web.  Just search for them.
4: You don't.

All that said:  You do realize that exp really isn't a true backup.  You are limited on the types of 'recovery' you can do with it.  The main thing you will be missing is Point-In-Time recovery.

You can only 'recover' what is in the export file.  Depending on what happened that causes you to need to recover, you might lose data.
0
 

Author Comment

by:NiceMan331
ID: 39859204
slightw

4: You don't.

while johnos said
or using a tool such as SQL Developer to dump all the data to CSV files (which could be time consuming and a pain, but it should be possible).

is it possible or not ?


All that said:  You do realize that exp really isn't a true backup.  You are limited on the types of 'recovery' you can do with it.  The main thing you will be missing is Point-In-Time recovery.

could you do more explanation
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39859214
>>is it possible or not ?

A CSV isn't the same as an Oracle export using exp.  Two different things.

>>could you do more explanation

There are many blogs/papers that talk about Point in Time recovery as does the online documentation.

For the detailed information about it, I'll let you read.

The high-level:
You take a full export at 8:00 AM.
You insert 100 rows at 9:00 AM.
Your database disk crashes and you lose your data file that contains the table.

You can only recover the database back to what it looked like at 8:00 AM.  You have lost the 100 rows you inserted at 9:00AM.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 39859216
If you add full=y, then you need to have the appropriate privileges to do a full export as well.  You asked about exporting a single user.  That is the information you received.

SQL Developer is capable of dumping a single table to a CSV file.  It is not exp.  It is simply selecting from a table and formatting the output into a file.  Exp cannot be scheduled through SQL Developer, it is a separate command line.
0
 

Author Comment

by:NiceMan331
ID: 39859310
You can only recover the database back to what it looked like at 8:00 AM.  You have lost the 100 rows you inserted at 9:00AM.

i got it , so is there any way to have all post after the exp ?
i mean , is there any tool to perform  Point-In-Time recovery.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39859328
>>i mean , is there any tool to perform  Point-In-Time recovery.

Yes, take a 'real' backup.

There are several tools/ways to do this.  I suggest RMAN or a third-party developed one (there are several, BackupExec for one).

You can perform your own hot backup but why when Oracle provides RMAN?

There are tons of examples of Oracle Hot Backup scripts out there if you look around.

NOTE: To do a point in time, you need a backup of the WHOLE database.  Basically file copies of your data files.  This is not 'small' which is what you asked for in the original question.
0
 

Author Comment

by:NiceMan331
ID: 39859369
NOTE: To do a point in time, you need a backup of the WHOLE database.  Basically file copies of your data files.  This is not 'small' which is what you asked for in the original question.

any how it is additional info for my knowledge
thans
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now