Oracle-Log Recovery-015

The tablespace is not backed up and deleted. Logs need to be restored, but logs cannot have breakpoints.
Check whether logs are in archive Mode Under sys user
SQL> archive log list
If you are in non-archive mode, you need to change the sys user to archive mode in the database mount state.
SQL> alter database archive
 
 
1. Create a user
SQL> create tablespace lits datafile 'e: \ dbf \ li01.dbf' size 50 m;
 
Tablespace created.
 
SQL> create user li identified by li default tablespace lits;
 
User created.
 
SQL> grant connect to li;
 
Grant succeeded.
-- Allows the user li to use the lits tablespace at will
SQL> alter user li quota unlimited on lits;
 
User altered.
 
SQL>
 
 
2. Close the database, delete the dbf file, and simulate data file loss or damage
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-- Delete li01.dbf
 
 
3. Restart
SQL> startup
ORACLE instance started.
 
Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11-see DBWR trace file
ORA-01110: data file 11: 'e: \ DBF \ li01.dbf'
-- Data file loss can only be started to mount mode. If the system needs to be started properly, you can first offline the lost file and other normal data files to start the database normally.
SQL> select file #, ts #, name from v $ datafile;
 
FILE # TS # NAME
------------------------------------------------------------------------------
1 0 E: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ SYSTEM01.DBF
2 1 E: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ UNDOTBS01.DBF
3 2 E: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ SYSAUX01.DBF
4 4 E: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ USERS01.DBF
5 6 E: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ EXAMPLE01.DBF
6 7 E: \ DBF \ MYTABLESPACE. DBF
7 7 E: \ DBF \ MYTABLESPACE01.DBF
8 7 E: \ DBF \ MY02
9 8 E: \ DBF \ MYTS03
10 9 E: \ ORACLE \ RMAN \ RMANTS01.DBF
11 10 E: \ DBF \ LI01.DBF
 
11 rows selected.
 
 
4. datafile offline start the system
 
SQL> alter database datafile 11 offline;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL>
SQL>
 
 
 
5. Delete the damaged new dbf. li01.dbf must be of the same name.
-- Another restoration method is to copy the backup to a data file with the same name as datafile for restoration.
SQL> alter database create datafile 'e: \ dbf \ li01.dbf ';
 
Database altered.
 
 
 
6. Synchronize logs to the new dbf
SQL> recover datafile 'e: \ dbf \ li01.dbf ';
Media recovery complete.
 
-- The table cannot be found.
SQL> select * from li. li01;
Select * from li. li01
*
ERROR at line 1:
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: 'e: \ DBF \ li01.dbf'
SQL>
 
 
7. datafile online
SQL> alter database datafile 11 online;
 
Database altered.
 
SQL> select * from li. li01;
 
LID
----------
1
2
3
 
SQL>
-- The query result is the result before commit.
SQL>

Author: "fly @ lwy"