Let’s have look on the steps used for Cold Backup and Recovery From Archivelog.
Check the archive log status

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

Enable the archive logs

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

create a table for the testing purpose

SQL> create table Cold0_Backup(name varchar(100)) tablespace users;
Table created.

SQL> insert into Cold0_Backup values('****** INSERTED BEFORE BACKUP *******');
1 row created.

SQL> commit;
Commit complete.

Shutdown the database and make of necessary files

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

D:\>mkdir D:\Cold0_Backup
D:\>copy D:\oracle\product\10.2.0\oradata\OracleHelp\* D:\Cold0_Backup\
D:\oracle\product\10.2.0\oradata\OracleHelp\SYSAUX01.DBF
D:\oracle\product\10.2.0\oradata\OracleHelp\UNDOTBS01.DBF
D:\oracle\product\10.2.0\oradata\OracleHelp\CONTROL01.CTL
D:\oracle\product\10.2.0\oradata\OracleHelp\CONTROL02.CTL
D:\oracle\product\10.2.0\oradata\OracleHelp\CONTROL03.CTL
D:\oracle\product\10.2.0\oradata\OracleHelp\REDO01.LOG
D:\oracle\product\10.2.0\oradata\OracleHelp\REDO02.LOG
D:\oracle\product\10.2.0\oradata\OracleHelp\REDO03.LOG
D:\oracle\product\10.2.0\oradata\OracleHelp\TEMP01.DBF
D:\oracle\product\10.2.0\oradata\OracleHelp\SYSTEM01.DBF
D:\oracle\product\10.2.0\oradata\OracleHelp\TEST01.DBF
D:\oracle\product\10.2.0\oradata\OracleHelp\USERS01.DBF
12 file(s) copied.

Start the database and insert some records

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 Cold0_Backup values('****** INSERTED AFTER BACKUP *******');
1 row created.

SQL> commit;
Commit complete.

Shut down the database and remove user tablespace

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

D:\>del D:\oracle\product\10.2.0\oradata\OracleHelp\USERS01.DBF

Start the database and make particular datafile offline

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: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLEHELP\USERS01.DBF'

SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLEHELP\USERS01.DBF' offline;
Database altered.

SQL> alter database open;
Database altered.

SQL> select * from Cold0_Backup;
select * from Cold0_Backup
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLEHELP\USERS01.DBF'

D:\>copy D:\Cold0_Backup\USERS01.DBF D:\oracle\product\10.2.0\oradata\OracleHelp\
1 file(s) copied.

Recover the datafile

SQL> recover datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLEHELP\USERS01.DBF';
Media recovery complete.

SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORACLEHELP\USERS01.DBF' online;
Database altered.

SQL> select * from Cold0_Backup;
NAME
--------------------------------------------------------------------------------
****** INSERTED AFTER BACKUP *******
****** INSERTED BEFORE BACKUP *******

Today’s thought

Our goal is very simple to become the desktop for e-businesses. Larry Ellison

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.