[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

making periodically backup

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
NiceMan331
Asked:
NiceMan331
  • 8
  • 6
  • 5
  • +1
2 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
Tomas Helgi JohannssonCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
NiceMan331Author Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
NiceMan331Author Commented:
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
 
johnsoneSenior Oracle DBACommented:
Yes, exp will work for that.  It will capture structure and data.
0
 
NiceMan331Author Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
NiceMan331Author Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
johnsoneSenior Oracle DBACommented:
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
 
NiceMan331Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
NiceMan331Author Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now