Simple oracle backup without using exp or expdp

Question

I have Oracle 10g installed on Windows in C: \ oracle. If I stop all Oracle services, is backup safe by simply copying the entire directory (for example, to C: \ oracle_bak), or am I much better off using expdp?

Pointers to documents / websites are very welcome, I could not do something interesting for Google.

Answer 1

If your database is not running in archived log mode, the answer is yes. Here are some scripts that I use to back up and restore my database.

--- backup.bat ---

sqlplus "sys/ passwd@database as sysdba" @shutdown.sql xcopy C:\oracle\oradata\database\*.* C:\oracle\oradata\backup_database\*.* /Y sqlplus "sys/ passwd@database as sysdba" @startup.sql 

---- shutdown.sql

 shutdown immediate exit; 

---- startup.sql

 startup exit; 

The recovery script is similar. Just copies files in the other direction.

Answer 2

You can simply copy the data files (make sure that you also get the management files and make sure that you test your backups). You should probably use RMAN.

A quick guide to backing up and restoring Oracle® databases would be a good start.

Answer 3

A very simple backup method is to export the appropriate schema using the exp tool. If, for example, all your tables exist in the MY_APP schema (reading the database for mysql users), you can send all your tables to a single file.

 exp userid=MY_APP file=dumpfile.dmp log=logfile.txt consistent=y statistics=none buffer=1024000 

Restoring dumpfile to a second database works as follows

 imp userid=system fromuser=MY_APP touser=MY_APP file=dumpfile.dmp commit=y buffer=102400 

Or you can restore tables from MY_APP to another schema in the same database

 imp userid=system fromuser=MY_APP touser=MY_BACKUP file=dumpfile.dmp commit=y buffer=102400 

Just create a new MY_BACKUP schema before importing

 create user MY_BACKUP identified by SECRET default tablespace USERS temporary tablespace temp; grant connect, resource to MY_BACKUP; 

Answer 4

Copy / Paste works, but you should not just copy / paste the whole Oracle house. This is a lot more effort than required.

First you need to do a log switch, i.e.

 SET ORACLE_SID=mydb sqlplus /nolog Connect / as sysdba Alter system switch logfile; 

Put all your table spaces in backup mode, i.e.

 CONNECT / AS SYSDBA ALTER TABLESPACE mytablespace BEGIN BACKUP; 

(You can get your tablespaces by querying the DBA_TABLESPACES view)

Then copy all your data files and repeat the log files to the backup folder.

Whether this method is safe or not depends on how you save the data files and the log files. Of course, I have to mention that RMAN is Oracle Verified and recommended backup mode.