1

new to oracle, I am having a oracle DB which we don't use anymore(version : 8.1.7.4).

I want to take a backup and keep it for few more years(we don't know if we may need something later on)

1 >> What is the best way to take a backup ? 2 >> Can I take the backup and restore it to a newer version of oracle on my local box ?

Thanks, Jack

Jack
  • 101
  • 1
  • 6

2 Answers2

4

if you make a physical backup using rman or file copies, you also need to keep a copy of ORACLE_HOME and your operating system. If you want to make sure that you can access your data, an export might be a better choice, if your database is not very big. (more than a few hundred gig). If it is larger, upgrade it to the latest possible release and save that one. Doing so will give you a better chance to start your database again, after a year, without having to restore your operating system. This is enough if you only want sql access. If you also want the application to be able to use the database, the upgrade might not be the best choice.

I hope this helps. Ronald.

2

When people talk about an Oracle backup, they almost always mean a physical backup-- a physical copy of your data files, your control files, redo logs, etc. It's probably easiest to use Recovery Manager (renamed RMAN in recent versions) to do this if you've never done a user-managed backup before. If you take a physical backup, however, you can only restore it to a machine running the exact version of Oracle from the backup (and a similar operating system). Given how difficult it would be to find 8.1.7 installation media let alone a machine running a supported operating system, that may not be the best approach here.

You can also take a logical backup using the export utility. This generates a single binary file that has the DDL and data for either the entire database or for whatever schema(s) you actually want to preserve. This binary dump file can be imported into later Oracle versions but it will not contain information about things like the database configuration and it would not be possible to roll forward from a backup if later changes are made (thought that doesn't sound like a concern in this case).

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I took the export from Linux box(11 Gig), when I try to restore it on windows it complains about path and users. – Jack Feb 23 '11 at 19:52
  • What sort of export did you do? A full export? A user export? A tablespace export? Can you post the exact export command you used, the exact import command, and the exact error? – Justin Cave Feb 23 '11 at 19:56
  • exp sa_new/oracle PARFILE=params.dat FILE=/oradata/dba.dmp GRANTS=y FULL=y ROWS=y LOG=/oradata/dba.log – Jack Feb 23 '11 at 20:10
  • @Jack - OK. What is in params.dat? – Justin Cave Feb 23 '11 at 20:10
  • FILE=/oradata/dba.dmp GRANTS=y FULL=y ROWS=y LOG=/oradata/dba.log – Jack Feb 23 '11 at 20:11
  • imp system/password file c:\dba.dmp full=y – Jack Feb 23 '11 at 20:12
  • Error :IMP-00017: following statement failed with ORACLE error 2494: "CREATE TABLESPACE "OEM_REPOSITORY" DATAFILE '/oradata/VPRD1/oemrep01.dbf' " "SIZE 104857600 AUTOEXTEND ON NEXT 26214400 MAXSIZE 83886080 DEFAULT" " STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCT" "INCREASE 0) ONLINE PERMANENT " IMP-00003: ORACLE error 2494 encountered ORA-02494: invalid or missing maximum file size in MAXSIZE clause IMP-00015: following statement failed because the object already exists: "CREATE TABLESPACE "USERS" DATAFILE '/oradata/VPRD1/users01.dbf' SIZE 10485" – Jack Feb 23 '11 at 20:12
  • IMP-00017: following statement failed with ORACLE error 2494: "CREATE TABLESPACE "OEM_REPOSITORY" DATAFILE '/oradata/VPRD1/oemrep01.dbf' " "SIZE 104857600 AUTOEXTEND ON NEXT 26214400 MAXSIZE 83886080 DEFAULT" " STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCT" "INCREASE 0) ONLINE PERMANENT " IMP-00003: ORACLE error 2494 encountered ORA-02494: invalid or missing maximum file size in MAXSIZE clause IMP-00015: following statement failed because the object already exists: "CREATE TABLESPACE "USERS" DATAFILE '/oradata/VPRD1/users01.dbf' SIZE 10485" – Jack Feb 23 '11 at 20:13
  • @Jack - OK, so you're doing a full export. Do you really want a full export? Or do you just need the export for a schema or two? If the intention is to have something just in case you need to restore it years in the future, having the DDL to create tablespaces and users and to grant privileges to those users strikes me on first blush as counterproductive. – Justin Cave Feb 23 '11 at 20:14
  • The errors you're getting doing the import are the result of the import process trying to create the tablespaces that existed in the old database. Depending on how the data files on the new database are laid out, you may need to manually create the tablespaces before running the import (using the ignore=y option) so that the new data files are in the correct place for the new database. – Justin Cave Feb 23 '11 at 20:17
  • so, I need to create the table spaces and users before i run the import ? is there a way or sql command that dumps all tables spaces and users on old DB and what would be the command to run on new Db to create these. – Jack Feb 23 '11 at 20:19
  • That's what import is doing. The problem is that you appear to already have a USERS tablespace on the destination system, so the command to create the USERS tablespace fails. Unless the destination database has the same name as the source, it's unlikely that you'd want the data files created in the same directory path on the destination as they were on the source. And depending on the version of the destination database, some of the options in the DDL may no longer be valid. That's why I would tend to thing that you want a schema-level export not a full export. – Justin Cave Feb 23 '11 at 20:40
  • Thanks Justin, let me try that. – Jack Feb 23 '11 at 21:20