Lesson 2	Recovering a lost Datafile with no Backup
Objective	Demonstrate how to recover a Lost Datafile with no backup

Recovering a Lost Datafile with no Backup

Suppose one of your junior DBAs created a tablespace named STUDENT two days ago and forgot to include this tablespace in your backup strategy. This tablespace contains important user data. A media failure occurs and the disk that holds the datafile belonging to the tablespace STUDENT is permanently damaged. After some investigation, you find that all the archived logs for the past 2 days are intact and that the datafile is not a system or rollback segment datafile.

Question: What should you do next?
Solution:

After some research, you determine that since you have all the archive logs generated since the lost datafile was created, it is possible to recover the datafile, even though it has no backup. The technique involves creating a replacement in an empty datafile using information stored in the control file. From the control file you get the size and name of the lost datafile using the command

ALTER DATABASE CREATE DATAFILE <name stored in controlfile>  
AS <name of replacement file>

Note that the size is not specified--this information is taken from the control file. The CREATE command produces an empty datafile of the appropriate size, which can then be recovered from the archive logs. This method allows you to recover the datafile just prior to the time of the media failure. If your database is still open when the damage occurs, you must act quickly to bring the damaged datafile offline to prevent any new checkpoint from attempting to write to a file that does not exist. The simulation below walks you through the steps of performing a datafile recovery with no backup on the damaged STUDENT tablespace. You may also preview all steps.

Performing a datafile recovery with no backup in Oracle

Take the corrupted datafile or the whole affected tablespace offline. If the database is closed, start the database in the mount mode. Take the damaged datafile offline and open the database. This will keep your database accessible to the users who do not need to use the corrupted tablespace.
Use the
```
ALTER TABLESPACE <tablespace_name> offline immediate;
```
statement if your database is open; use
```
ALTER DATABASE DATAFILE <datafile_name> offline immediate;
```
statement if your database can only be mounted.

Use the data dictionary view V$RECOVER_FILE to confirm the recovery status.
Recreate the damaged datafile to an alternative location using the
```
ALTER DATABASE DATAFILE '<datafile_name>' AS '<new datafile_name>';
```
statement.
Recover the datafile or the affected tablespace by applying all the archived logs and online redo logs required via the RECOVER TABLESPACE or RECOVER DATAFILE command.
Bring the recovered datafile or tablespace back online.
Include the tablespace in your backup strategy and inform the user that the tablespace is available.

Recover Datafile with no backup(Oracle)

As the DBA, you must first set the ORACLE_SID in order to point to the correct database, ORCL. Type SET ORACLE_SID=ORCL at the prompt and press Enter.
To connect to an Oracle database using SQLPlus, open the command prompt or terminal and type “sqlplus” followed by the username, password, and service name. For example:
```
sqlplus username/password@servicename.
```
To connect to ORCL as sysdba, type CONNECT INTERNAL at the prompt and press Enter. The password ORACLE is entered for you.
Since the database was closed due to a media failure, you must open it in the mount mode. Type STARTUP MOUNT at the prompt and press Enter.
To take the damaged datafile offline, type ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE; and press Enter.
Now you can open the database and make it available to the users who do not need the STUDENT tablespace. Type ALTER DATABASE OPEN at the prompt and press Enter.
To confirm the recovery status of the corrupt datafile, you query the data dictionary view V$RECOVER_FILE. Type SELECT * FROM V$RECOVER_FILE; at the prompt and press Enter.
The result shows that file #5 needs recovery. If no files needed recovery, this query would return no records. The CHANGE# column indicates the SCN where recovery must start. The value of zero shows that the recovery of this datafile in its original location is impossible. To recreate the datafile in an alternate location, type ALTER DATABASE CREATE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' AS 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA'; at the prompt and press Enter.
You must check the recovery status of the newly created datafile again by querying the V$RECOVER_FILE view. Type SELECT * FROM V$RECOVER_FILE; at the prompt and press Enter.
The query result shows that the recovery for this file should start with SCN 562019. To start the recovery process, type RECOVER DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' UNTIL TIME '2000-01-18:12:00:00'; at the prompt and press Enter.
To automatically apply all the archived and online redo logs, type AUTO at the cursor, and then press Enter.
All the archived and online redo logs will be applied to bring the data in the datafile back to the time the media failure occurred. In this simulation, only the beginning and the end of the recovery process are displayed. Click anywhere on the screen to go onto the next step.
To bring the recovered datafile back online, type ALTER DATABASE DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' ONLINE; at the prompt and press Enter.
You have successfully recovered the tablespace STUDENT. The important lesson learned: Do not forget to include the tablespace in the backup strategy to avoid this kind of recovery in future. This completes the Simulation.

The command "SET ORACLE_SID=ORCL" is still used in the Oracle RDBMS, but it is no longer necessary in most cases. Oracle has introduced a new feature called Oracle Easy Connect, which automatically identifies the Oracle instance to connect to. As a result, you no longer need to set the ORACLE_SID environment variable explicitly.
However, there are still a few cases where you may need to set the ORACLE_SID environment variable. For example, if you are using a tool that does not support Oracle Easy Connect, or if you are connecting to a remote instance of Oracle, you will need to set the ORACLE_SID environment variable manually.
Here are some of the cases where you may need to set the ORACLE_SID environment variable:

If you are using a tool that does not support Oracle Easy Connect, such as the SQL*Plus command-line tool.
If you are connecting to a remote instance of Oracle.
If you are using a non-Oracle application that needs to connect to Oracle, such as a Java application.

If you are not sure whether or not you need to set the ORACLE_SID environment variable, it is always best to consult the documentation for the tool or application that you are using. Here are some examples of how to set the ORACLE_SID environment variable:
On Windows:

SET ORACLE_SID=ORCL

On Linux or macOS:

export ORACLE_SID=ORCL

Once you have set the ORACLE_SID environment variable, you can connect to the Oracle instance using the following command:

sqlplus

You will then be prompted to enter your Oracle username and password.

C:\ORANT> SET ORACLE_SID=ORCL
sqlplus / as sysdba -sid ORCL

(c) Copyright 2017, Oracle Corporation. All Rights Reserved.

SQL> CONNECT INTERNAL
Password:
Connected.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area                         11710464 bytes
Fixed Size                                          49152 bytes
Variable Size                                    11177984 bytes
Database Buffers                                   409600 bytes
Redo Buffers                                        73728 bytes
Database mounted.
SQL> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE;
Statement processed.
SQL> ALTER DATABASE OPEN;
Statement processed.
SQL> SELECT * FROM V$RECOVER_FILE;
FILE#   ONLINE  ERROR              CHANGE#    TIME
-----   ------- ------------------ -------    ---------
    5   OFFLINE FILE NOT FOUND           0
1 row selected.
SQL> ALTER DATABASE CREATE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' 
AS 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA';
Statement processed. 
SQL> SELECT * FROM V$RECOVER_FILE;
FILE#   ONLINE  ERROR              CHANGE#    TIME
-----   ------- ------------------ --------   ------------
    5   OFFLINE        5621029    11-JAN-2000
1 row selected.

SQL> RECOVER DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' 
UNTIL TIME '2000-01-18:12:00:00';
ORA-00279: change 5621029 generated at 01/11/00 16:03:17 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC442.1
ORA-00280: change 5621029 for thread 1 is in sequence #442
 Specify log: {
 <RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
ORA-00279: change 5641036 generated at 01/12/00 11:45:17 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC443.1
ORA-00280: change 5641036 for thread 1 is in sequence #443
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC442.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5661054 generated at 01/12/00 19:31:49 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC444.1
ORA-00280: change 5661054 for thread 1 is in sequence #444
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC443.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5681062 generated at 01/13/00 19:24:40 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC445.1
ORA-00280: change 5681062 for thread 1 is in sequence #445
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC444.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5701070 generated at 01/17/00 10:46:41 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC446.1
ORA-00280: change 5701070 for thread 1 is in sequence #446
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC445.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5721078 generated at 01/17/00 12:26:16 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC447.1
ORA-00280: change 5721078 for thread 1 is in sequence #447
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC446.1' no longer needed for this recovery
 Log applied.
ORA-00279: change 5741086 generated at 01/18/00 11:04:32 needed for thread 1
ORA-00289: suggestion : C:\ORANT\RDBMS80\ARC448.1
ORA-00280: change 5741086 for thread 1 is in sequence #448
ORA-00278: log file 'C:\ORANT\RDBMS80\ARC447.1' no longer needed for this recovery
 Log applied.
Media recovery complete.
SQL> ALTER DATABASE DATAFILE 'D:\NEW_LOCATION\DATABASE\STUDENT.ORA' ONLINE;
Statement processed.
SQL>

The next lesson demonstrates how to handle recovery of a file in backup mode.