Hot Backup

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence 9

SQL> create table online_back(name varchar(100)) tablespace users;

Table created.

SQL> insert into online_back values('*******BEFORE BACKUP *******');

1 row created.

SQL> commit;

Commit complete.

SQL> column segment_name format a20;

SQL> column tablespace_name format a20;

SQL> SELECT owner,segment_name,segment_type FROM DBA_SEGMENTS

WHERE TABLESPACE_NAME ='USERS';

OWNER SEGMENT_NAME SEGMENT_TYPE

------------------------------ -----------------------------------------------------------------

SCOTT PK_DEPT INDEX

SCOTT DEPT TABLE

SCOTT EMP TABLE

SCOTT PK_EMP INDEX

SCOTT BONUS TABLE

SCOTT SALGRADE TABLE

SYS ONLINE_BACK TABLE

7 rows selected.

SQL> select file_id,tablespace_name,file_name from dba_data_files;

FILE_ID TABLESPACE_NAME FILE_NAME

------------------------------------------------------------------------------------------

4 USERS C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF

3 SYSAUX C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSAUX01.DBF

2 UNDOTBS1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\UNDOTBS01.DBF

1 SYSTEM C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSTEM01.DBF

5 TEST C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

868641

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

---------- ------------------ ---------- ---------

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 ACTIVE 868643 18-JUN-10

5 NOT ACTIVE 0

C:\>mkdir Hot_Backup

C:\>copy c:\oracle\product\10.2.0\oradata\dba20\USERS01.DBF c:\Hot_Backup

1 file(s) copied.

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> insert into online_back values('*******AFTER BACKUP *******');

1 row created.

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Delete USERS01.DBF

SQL> startup;

ORACLE instance started.

Total System Global Area 167772160 bytes

Fixed Size 1247876 bytes

Variable Size 75498876 bytes

Database Buffers 83886080 bytes

Redo Buffers 7139328 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF'

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF' offline;

Database altered.

SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF';

Media recovery complete.

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF' online;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from online_back;

NAME

---------------------------------------------

*******BEFORE BACKUP *******

*******AFTER BACKUP *******