Recover From Redo Log File

This post explains about the steps are used by DBA for recovery From Redo Log File.

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     2
Current log sequence              4

List the tablespaces

SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP

SQL> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\SYSTEM01.DBF

Create new tablespace and create a table with some dummy data

SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\TEST01.DBF' SIZE 1M;
Tablespace created.

SQL> create table test(id number) tablespace test;
Table created.

SQL> insert into test values(2);
1 row created.
SQL> insert into test values(5);
1 row created.
SQL> commit;
Commit complete.

SQL> select * from test;
ID
----------
2
5

SQL> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME           FILE_NAME
---------------------------------------------------------------------------------------
USERS            D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\USERS01.DBF
SYSAUX        D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\SYSAUX01.DBF
UNDOTBS1   D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\UNDOTBS01.DBF
SYSTEM        D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\SYSTEM01.DBF
TEST              D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\TEST01.DBF

Shutdown the database

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

Delete the datafile and try to start the database

Delete D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\TEST01.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 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\TEST01.DBF'

Recreate the datafile and do the recovery

SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA02\TEST01.DBF';
Database altered.

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

SQL> alter database open;
Database altered.

SQL> select * from test;
ID
----------
2
5

Today’s thought

“You have to act and act now.” 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