I have Oracle 10g installed on Windows in C:\oracle. If I stop all Oracle services, is it safe to backup by just copying the entire directory (e.g., to C:\oracle_bak), or am I significantly better off using expdp?
Pointers to docs/websites very welcome, I wasn't able to Google up anything relevant.
You can just copy the data files ( make sure you get the control files as well, and make sure you TEST your backups ), however. You should probably be using RMAN.
The Oracle® Database Backup and Recovery Quick Start Guide would be a good place to start.
A very simple backup method is to export the relevant schema using the exp tool. If e.g. all your tables exist in the schema MY_APP (read database for mysql users), you can dump all its tables to one file.
exp userid=MY_APP file=dumpfile.dmp log=logfile.txt consistent=y statistics=none buffer=1024000
Restoring the dumpfile to a second database works like this
imp userid=system fromuser=MY_APP touser=MY_APP file=dumpfile.dmp commit=y buffer=102400
Or you can restore the 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 schema MY_BACKUP before the import
create user MY_BACKUP identified by SECRET default tablespace USERS temporary tablespace temp;
grant connect, resource to MY_BACKUP;
If your database is not running in archive log mode the answer is yes. Here are some scripts I use to backup 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;
Restore script is similar. Just copies the files in the other direction.
Copy/ Paste does work, but you shouldn't simply copy/ paste the entire Oracle home. This is a lot more effort than what is required.
You will firstly need to perform a log switch, i.e.
SET ORACLE_SID=mydb
sqlplus /nolog
Connect / as sysdba
Alter system switch logfile;
Place all your tablespaces into 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 redo log files to your backup location.
In regards to whether this method is safe or not, It depends on how you are retaining the data files and log files. Of course, I should mention that RMAN is Oracle's proven and recommended mode of backup.