Home » RDBMS Server » Backup & Recovery » how to back up and restore the tables
how to back up and restore the tables [message #187116] Thu, 10 August 2006 23:05 Go to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi buddies

how can i do this in fast manner.

thanks a lot in advance.
Re: how to back up and restore the tables [message #187129 is a reply to message #187116] Fri, 11 August 2006 01:04 Go to previous messageGo to next message
Frank Naude
Messages: 4502
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
Re: how to back up and restore the tables [message #187373 is a reply to message #187116] Sat, 12 August 2006 22:51 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi frank nude.

i am newbie to oracle. so pls help me.

i am expecting your reply.
Re: how to back up and restore the tables [message #187383 is a reply to message #187373] Sun, 13 August 2006 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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?
Re: how to back up and restore the tables [message #187389 is a reply to message #187116] Sun, 13 August 2006 04:18 Go to previous messageGo to next message
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.


Re: how to back up and restore the tables [message #187410 is a reply to message #187389] Sun, 13 August 2006 14:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
Re: how to back up and restore the tables [message #187529 is a reply to message #187116] Mon, 14 August 2006 06:29 Go to previous messageGo to next message
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.
Re: how to back up and restore the tables [message #187537 is a reply to message #187529] Mon, 14 August 2006 06:48 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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).
Re: how to back up and restore the tables [message #187540 is a reply to message #187116] Mon, 14 August 2006 06:57 Go to previous messageGo to next message
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.
Re: how to back up and restore the tables [message #187544 is a reply to message #187540] Mon, 14 August 2006 07:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: how to back up and restore the tables [message #187552 is a reply to message #187116] Mon, 14 August 2006 07:33 Go to previous messageGo to next message
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.
Re: how to back up and restore the tables [message #187554 is a reply to message #187552] Mon, 14 August 2006 07:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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?
Re: how to back up and restore the tables [message #187595 is a reply to message #187116] Mon, 14 August 2006 10:39 Go to previous messageGo to next message
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
Re: how to back up and restore the tables [message #187641 is a reply to message #187595] Mon, 14 August 2006 15:28 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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).
Re: how to back up and restore the tables [message #187847 is a reply to message #187116] Wed, 16 August 2006 00:23 Go to previous messageGo to next message
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 199 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 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi littelfoot

i have attached another file with this.

  • Attachment: Tnsnames.ora
    (Size: 1.60KB, Downloaded 235 times)
Re: how to back up and restore the tables [message #187944 is a reply to message #187858] Wed, 16 August 2006 07:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: how to back up and restore the tables [message #188445 is a reply to message #187116] Fri, 18 August 2006 10:36 Go to previous messageGo to next message
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
Re: how to back up and restore the tables [message #188915 is a reply to message #188445] Tue, 22 August 2006 06:46 Go to previous messageGo to next message
dinu_dba
Messages: 8
Registered: August 2006
Location: Hyderabad
Junior Member

if u hav only one database no need to give database name ok
Re: how to back up and restore the tables [message #188936 is a reply to message #187116] Tue, 22 August 2006 08:42 Go to previous message
youthsen
Messages: 45
Registered: August 2006
Member
hi dinu_dba

how to create database in sql?

pls reply fast.

thanks a lot in advance.

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


Current Time: Sun Dec 11 00:43:27 CST 2016

Total time taken to generate the page: 0.21081 seconds