OraFAQ Forum: Backup & Recovery » how to back up and restore the tables

Home » RDBMS Server » Backup & Recovery » how to back up and restore the tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to back up and restore the tables [message #187116]

Thu, 10 August 2006 23:05

youthsen
Messages: 45
Registered: August 2006

Member

hi buddies

how can i do this in fast manner.

thanks a lot in advance.

Report message to a moderator

Re: how to back up and restore the tables [message #187129 is a reply to message #187116]

Fri, 11 August 2006 01:04

Frank Naude
Messages: 4589
Registered: April 1998

Senior Member

Quote:

How can i do this in fast manner?

This doesn't look like a technical question. Some tips:

Ensure you master the theory
Practice
Automate
Document all steps involved
Work faster and smarter

Report message to a moderator

Re: how to back up and restore the tables [message #187373 is a reply to message #187116]

Sat, 12 August 2006 22:51

youthsen
Messages: 45
Registered: August 2006

Member

hi frank nude.

i am newbie to oracle. so pls help me.

i am expecting your reply.

Report message to a moderator

Re: how to back up and restore the tables [message #187383 is a reply to message #187373]

Sun, 13 August 2006 03:25

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

As you've mentioned "fast", perhaps it is all about testing purposes? For example, you have a table which is full of useful data. Now you'd like to apply some UPDATEs which could be harmful to this table and mess up the data.

To backup this table, you could create the backup table - if it is not too large - in the current schema as

SQL> CREATE TABLE my_backup_table AS SELECT * FROM original_table;

If anything goes wrong with the original one, delete (truncate/drop - choose one which suits the most your needs) it and restore it using

SQL> INSERT INTO original_table SELECT * FROM my_backup_table;

Furthermore, save it into the export file:

OS> EXP username/password@database TABLES=original_table FILE=backup.dmp

To restore it, use IMP command:

OS> IMP username/password@database FILE=backup.dmp

This statement might require use of IGNORE=Y if the table still exists in a schema (because CREATE TABLE statement would fail; with IGNORE=Y, Import utility will just import data into the table). Also, be careful because if there's still data in the table, you'll append backup data into existing ones which will also fail if there's unique key constraint (or index) in the table. Therefore, perhaps the best scenario would be

SQL> DELETE original_table;
OS> IMP username/password@database FILE=backup.dmp IGNORE=Y

Does any of this help? Or did I miss the point?

Report message to a moderator

Re: how to back up and restore the tables [message #187389 is a reply to message #187116]

Sun, 13 August 2006 04:18

youthsen
Messages: 45
Registered: August 2006

Member

hi Littlefoot

thank you very much.

i have worked the first two command.

but i don't know how to enter in to "os>"

then only i can enter your commands.

so pls give me any on to enter into "os>" for export the table as file.

i am expecting your reply.

Report message to a moderator

Re: how to back up and restore the tables [message #187410 is a reply to message #187389]

Sun, 13 August 2006 14:55

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm sorry - my mistake - I should have explained that in my previous post.

SQL> is SQL*Plus prompt
OS> is operating system prompt

Report message to a moderator

Re: how to back up and restore the tables [message #187529 is a reply to message #187116]

Mon, 14 August 2006 06:29

youthsen
Messages: 45
Registered: August 2006

Member

hi Littlefoot

once again i am telling that i am new to oracle.

i dont know how to enter os> prompt.

is it with in oracle? or winows?

my table name is - sen
database name - oracle.world
username/password = scott/tiger

pls give the info to enter into "os>" prompt.

is it like "start-->run-->command then c:\windows>" ?

i am expecting your reply.

Report message to a moderator

Re: how to back up and restore the tables [message #187537 is a reply to message #187529]

Mon, 14 August 2006 06:48

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yes, on Microsoft Windows, you'd run "Command promt" in order to get to the operating system (MS Windows, right?) prompt. Choose Start -> Run -> CMD (and press <Return>).

And yes, it will look like "C:\WINDOWS>" (or similar).

Report message to a moderator

Re: how to back up and restore the tables [message #187540 is a reply to message #187116]

Mon, 14 August 2006 06:57

youthsen
Messages: 45
Registered: August 2006

Member

hi littelfoot

my table name = sen
database name = oracle.world
username/password = scott/tiger

i entered such as

start-->run--->command

now it opens like

c:\windows>exp scott/tiger@oracle.world tables=sen file=sen.dmp

bad command or file name.

so it shows like this. so what to do to solve this problem.

pls reply.

Report message to a moderator

Re: how to back up and restore the tables [message #187544 is a reply to message #187540]

Mon, 14 August 2006 07:07

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It means that Windows couldn't find a program or command whose name is "exp". Furthermore, it means that "exp" either isn't there (not installed), or it is in a directory which isn't the current one (c:\windows) or is not part of the PATH system variable.

If Oracle was properly installed, Universal Installer would add all necessary directories into the PATH. Therefore, I'd say that you didn't install Oracle server (database) but client (which normally doesn't come with export/import utilities).

What to do? First, make sure you (don't) have "exp.exe" on your hard disk. If you don't have it, install it.

Report message to a moderator

Re: how to back up and restore the tables [message #187552 is a reply to message #187116]

Mon, 14 August 2006 07:33

youthsen
Messages: 45
Registered: August 2006

Member

hi Littlefoot

i am using windows98 os and personal oracle for windows.

is this "personal oracle for windows" is a client version?

and i find the files "exp80.exe" and "imp80.exe" at follwoing location.

c:\orawin98\bin>

can i use this files to export and import?

i used exp80.exe instead of exp. but it shows the error msg.

how can i find the files "exp.exe" and "imp.exe" from source cd of personal oracle for windows.

pls help me.

Report message to a moderator

Re: how to back up and restore the tables [message #187554 is a reply to message #187552]

Mon, 14 August 2006 07:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It seems like an old Oracle version; but never mind, it should work anyway.

Could you post the whole output of this command sequence?

C:\> CD C:\ORAWIN98\BIN
C:\ORAWIN98\BIN\> EXP80 HELP=Y

By the way, what "error message" are you talking about?

Report message to a moderator

Re: how to back up and restore the tables [message #187595 is a reply to message #187116]

Mon, 14 August 2006 10:39

youthsen
Messages: 45
Registered: August 2006

Member

hi

i entered as follwos

c:\orawin95\bin>exp80 scott/tiger@oracle.world tables=sen file=senthil.dmp

Export release 8.0.3.0.0 production on Mon Aug 14 12:2:47 2006

(c) Copyright 1997 oracle corporation. All rights reserved.

EXP-00056: ORACEL error 12154 encountered
ORA-12154: TNS:could not resolve service name
EXP-00000: Export terminated unsuccessfully

how to solve this error

Report message to a moderator

Re: how to back up and restore the tables [message #187641 is a reply to message #187595]

Mon, 14 August 2006 15:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It means that there's no database named "oracle.world" on your system. A blind shot would be trying to do the export using "oracle" instead of "oracle.world".

It would help if you post your SQLNET.ORA and TNSNAMES.ORA files (can be found in \network\admin directory).

Report message to a moderator

Re: how to back up and restore the tables [message #187847 is a reply to message #187116]

Wed, 16 August 2006 00:23

youthsen
Messages: 45
Registered: August 2006

Member

hi littlefoot

my table name:sen
username/password : scott/tiger

i used the follwoing command to find my database name after logged in as scott/tiger.

SQL> select * from global_name;

GLOBAL_NAME
----------------------------------------
ORACLE.WORLD

i used the following command to find the database name by logged in as system/manage.


SQL> select name from v$database;

NAME
---------
ORACLE

so which database name i have to use that "oracle" or "oracle.world".

is there any other method to find the database name.

the two files are "Sqlnet.ora" and "Tnsnames.ora" are located in c:\orawin95\net80\admin.

i have attached a file "Sqlnet.ora". but i dont know how to attach more than one file. so i am attaching another file "Tnsnames.ora" in next post.

pls reply very fast.

Attachment: Sqlnet.ora
(Size: 0.18KB, Downloaded 1475 times)

[Updated on: Wed, 16 August 2006 00:46]

Report message to a moderator

Re: how to back up and restore the tables [message #187858 is a reply to message #187116]

Wed, 16 August 2006 00:59

youthsen
Messages: 45
Registered: August 2006

Member

hi littelfoot

i have attached another file with this.

Attachment: Tnsnames.ora
(Size: 1.60KB, Downloaded 1515 times)

Report message to a moderator

Re: how to back up and restore the tables [message #187944 is a reply to message #187858]

Wed, 16 August 2006 07:33

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

SQLNET.ORA seems to be OK.

TNSNAMES.ORA contains aliases to your database(s). You have:
- Beq-local.world
- Tcp-loopback.world
- TcpExample.world
- SpxExample.world
- NmpExample.world
- CMExample.world
- extproc_connection_data.world

In order to make it work, you'd have to use one of those. Which one is your database? It seems that SID is "ORCL".

This is an example of a "correct" TNSNAMES.ORA alias:

ORA8I =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oraserv)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

How do you connect to, for example, Scott's schema? SQLPLUS SCOTT/TIGER@what_do_you_put_here ? I'd use SQLPLUS SCOTT/TIGER@ORA8I.

Do you, perhaps, have ORACLE_SID environment variable set? Check it with
C:\> set oracle_sid

Finally, if none of your TNSNAMES.ORA aliases work, try with "EXP scott/tiger@orcl FILE=scott.dmp TABLES=sen"; maybe it'll work.

Report message to a moderator

Re: how to back up and restore the tables [message #188445 is a reply to message #187116]

Fri, 18 August 2006 10:36

youthsen
Messages: 45
Registered: August 2006

Member

hi little foot

i exported the table using the commands

c:\windows>exp80 scott/tiger tables=sen file=sen.dmp

i imported the table using the command

c:\windows>imp80 scott/tiger file=sen.dmp tables=sen

i did not mention the database name. but it is working fine.

why it doesn't need database name

Report message to a moderator

Re: how to back up and restore the tables [message #188915 is a reply to message #188445]

Tue, 22 August 2006 06:46

dinu_dba
Messages: 8
Registered: August 2006
Location: Hyderabad

Junior Member

if u hav only one database no need to give database name ok

Report message to a moderator

Re: how to back up and restore the tables [message #188936 is a reply to message #187116]

Tue, 22 August 2006 08:42

youthsen
Messages: 45
Registered: August 2006

Member

hi dinu_dba

how to create database in sql?

pls reply fast.

thanks a lot in advance.

Report message to a moderator

Previous Topic:	RMAN-Differential,Cumulative and Full Backup
Next Topic:	Import Issues

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 01:35:08 CST 2025