Thursday 22 November 2007

Oracle tablespace Backup/Restore (OS backup)

I tested tablespace recovery.

Be sure your database in archive mode
SQL> archive log list;
SQL> select count(*) from hr.employees;

Begin backup.
SQL> alter tablespace example begin backup;
Check the tablespace in backup state
SQL> select * from v$backup;
Prepare hr account
SQL> alter user hr account unlock;
SQL> alter user hr identified by hr;
# sqlplus hr/hr
Make some changes
SQL> create table employees2 as select * from employees;

Backup your file (This means that if you put your database in backup state you can do filesystem backup for oracle)
# cp example01.dbf example01.dbf.backup
End backup mode
# sqlplus / as sysdba
SQL> alter tablespace example end backup;

Shutdown database, delete table and restore table
SQL> shutdown abort;
# rm example01.dbf
# sqlplus / as sysdba
SQL> startup
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

You want database to be open while you are restoring tablespace.
SQL> alter database datafile '/path/example01.dbf' offline;
SQL> alter database open;

Restore tablespace and recover it
# mv example01.dbf.backup example01.dbf
SQL> recover tablespace example;
SQL> alter tablespace example online;

Verify recovery
# sqlplus hr/hr
SQL> select count(*) from employees2;

No comments: