This post explains about the steps are used by DBA for recovery From Redo Log File. Check the archive log status
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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
|
1 2 3 4 |
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. |
Delete the datafile and try to start the database
|
1 2 3 4 5 6 7 8 9 10 11 |
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 […]