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

Comments