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
