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