Cold Backup and Recovery From Archivelog

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     6

Current log sequence           8

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount;

ORACLE instance started.

 Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              83887484 bytes

Database Buffers           75497472 bytes

Redo Buffers                7139328 bytes

Database mounted.

 

SQL> alter database archivelog;

 Database altered.

 

SQL> alter database open;

 Database altered.

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     6

Next log sequence to archive   8

Current log sequence           8

 

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

 Table created.

 

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

 1 row created.

 

SQL> commit;

 Commit complete.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

C:\>mkdir c:\c_backup

 

C:\>copy c:\oracle\product\10.2.0\oradata\dba20\* c:\c_backup\

c:\oracle\product\10.2.0\oradata\dba20\SYSAUX01.DBF

c:\oracle\product\10.2.0\oradata\dba20\UNDOTBS01.DBF

c:\oracle\product\10.2.0\oradata\dba20\CONTROL01.CTL

c:\oracle\product\10.2.0\oradata\dba20\CONTROL02.CTL

c:\oracle\product\10.2.0\oradata\dba20\CONTROL03.CTL

c:\oracle\product\10.2.0\oradata\dba20\REDO01.LOG

c:\oracle\product\10.2.0\oradata\dba20\REDO02.LOG

c:\oracle\product\10.2.0\oradata\dba20\REDO03.LOG

c:\oracle\product\10.2.0\oradata\dba20\TEMP01.DBF

c:\oracle\product\10.2.0\oradata\dba20\SYSTEM01.DBF

c:\oracle\product\10.2.0\oradata\dba20\TEST01.DBF

c:\oracle\product\10.2.0\oradata\dba20\USERS01.DBF

12 file(s) copied.

 

SQL> startup;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              83887484 bytes

Database Buffers           75497472 bytes

Redo Buffers                7139328 bytes

Database mounted.

Database opened.

 

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

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

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

 

SQL> startup;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              83887484 bytes

Database Buffers           75497472 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> alter database open;

 Database altered.

 

SQL> select * from c_backup;

select * from c_backup


ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

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

 

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

        1 file(s) copied.

 

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> select * from c_backup;

 NAME

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

 ****** INSERTED AFTER BACKUP *******

****** INSERTED BEFORE BACKUP *******

 

 

Comments