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 *******