Today we are going to have look on the steps which we are going to use in Oracle for ” Recover From Redo Log File (After Lost Data File)”. The loss is loss being DBA we well understand the cost of loss “CDR”
Check archive list
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
check the tablespaces and datafile
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\DBA01\USERS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\SYSAUX01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\UNDOTBS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\SYSTEM01.DBF
Create new tablespace and table with some data
SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\TEST01.DBF' SIZE 1M; Tablespace created. SQL> create table test_1(id number) tablespace test; Table created. SQL> insert into test_1 values(2); 1 row created. SQL> insert into test_1 values(5); 1 row created. SQL> commit; Commit complete.
Check records in table and recheck the tablespace
SQL> select * from test_1; 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\DBA01\USERS01.DBF SYSAUX D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\SYSAUX01.DBF UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\UNDOTBS01.DBF SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\SYSTEM01.DBF TEST D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\TEST01.DBF
Shutdown the database and delete datafile
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Delete D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\TEST01.DBF
Try to start the database
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\DBA01\TEST01.DBF'
Create the data file and recover it
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF'; Database altered. SQL> recover datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA01\TEST01.DBF'; Media recovery complete. SQL> alter database open; Database altered. SQL> select * from test; ID ---------- 2 5
Today’s thought If people like you, they’ll listen to you, but if they trust you, they’ll do business with you.– By Zig Ziglar
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:
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
