PerformanceWiki [the blog]

Backup and Restore Oracle Databases From Command Line

Below shows simple steps in Oracle 9i and 10g to backup and restore Oracle databases; the goal is for the tasks to be called remotely so they can be automated as part of a performance test execution (i.e., always restores a known baseline before start a test!)

1) Shuts down the listener:

F:\_backup>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 23-MAY-2007 09:49:20

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pw213)(PORT=1521)))
The command completed successfully

2) Shuts down the instance:

F:\_backup>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 23 09:49:27
2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

3) Use operating system “copy” command to copy the entire “oradata” directory to your backup location:

F:\_backup>xcopy /Y/E D:\oradata oradata_2007_05_21

Copies all oradata to the
“oradata_2007_05_21″ directory.

4) To restore, repeat steps 1) and 2), then copy back the files from “oradata_2007_05_21″ to your run-time “oradata” directory.

5) Start up listener:

F:\_backup>lsnrctl start

6) Start up instance:

F:\_backup>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 23 09:49:27 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup