Recovery of Read-Only Tablespace

If a read-only tablespace is destroyed due to media failure, the recovery process is dependent on how current the tablespace backups are. The following image demonstrates three recovery scenarios for a read-only tablespace.

ALTER DATABASE RENAME FILE <old_filename> 
TO <new_filename>; command.

The command to start up an Oracle instance with a specific parameter file (PFILE) can be implemented using SQL*Plus or SQL Developer, though the approach differs slightly between the two.
1) SQL*Plus SQL*Plus is a command-line tool that allows you to issue SQL and PL/SQL commands to an Oracle Database. It's the direct successor to the older SVRMGR (Server Manager) utility and supports almost all commands that were available in SVRMGR, including startup commands.
To execute the startup command in SQL*Plus, you would do the following:

1. Open SQL*Plus.
2. Connect to the Oracle instance as SYSDBA, which is required for performing startup and shutdown operations. The command might look like this:
   

   CONNECT / AS SYSDBA
   

3. Run the startup command with the PFILE:
   

   STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA
   

This will start the Oracle instance using the specified initialization parameter file.
2) SQL Developer SQL Developer is a graphical tool for database development that includes an interface for managing databases, including starting up and shutting down the database. However, it does not provide a direct interface for issuing a startup command with a PFILE from the graphical interface as SQL*Plus does.
To use SQL Developer for this operation, you would typically ensure that the default parameter file is configured correctly or the database is already set up to start with the correct parameters. SQL Developer can be used to stop and start the database, but specifying a PFILE directly during startup would usually be handled outside of SQL Developer, unless using SQL Developer's command-line interface.
In conclusion:

• SQL*Plus is the appropriate tool for directly executing the `STARTUP OPEN PFILE` command as it supports command-line operations similar to SVRMGR.
• SQL Developer can manage database startup through its graphical interface but does not natively support specifying a PFILE in the startup command. For advanced operations like specifying a PFILE directly, you would revert to SQL*Plus or adjust the database configuration to use the correct PFILE automatically upon startup.

SVRMGR> STARTUP OPEN PFILE=C:\ORANT\DATABASE\INITORCL.ORA
ORACLE instance started.
Total System Global Area  49152 bytes
Fixed Size                 49152 bytes
Variable Size              49152 bytes
Database Buffers           409600 bytes
Redo Buffers               73728 bytes
Database mounted.
ORA-01157: cannot identify data file 5 - file not found
ORA-01110: data file 5: 'C:\ORANT\DATABASE\STUDENT.ORA'
SVRMGR>

Analysis: This output is from an Oracle Server Manager (`SVRMGR`) session attempting to start an Oracle database using a parameter file (`PFILE`). Here are the key points from the output:

1. Oracle Instance Started: Indicates the database instance has successfully initiated but hasn't opened yet.
2. ORA-01157: This error suggests that the Oracle database engine cannot find data file number 5.
3. ORA-01110: This provides additional information about the missing file, pointing to its expected location on the filesystem (`C:\ORANT\DATABASE\STUDENT.ORA`).

Troubleshooting Steps:

• Check File Path: Verify that the file `STUDENT.ORA` exists at the specified path.
• Permissions: Ensure the Oracle process has sufficient permissions to access the file.
• Disk Issues: Check the disk for errors that might prevent file access.
• Backup: If the file is corrupted or missing, consider restoring from a backup if available.

1. After a media failure, you try to start the system. Type STARTUP OPEN PFILE=C:\ORNAT\DATABASE\INITORCL.ORA at the prompt and press Enter.
2. The error message shows that the datafile C:\ORANT\DATABASE\STUDENT.ORA is damaged. You know this file belongs to the STUDENT tablespace, which was read-only, and you have a backup of the tablespace. However, you remember that you had one of your junior DBAs change the tablespace to read-write a couple of days ago and it is not included in your routine backup strategy. As a result, you have to use the backup when the tablespace STUDENT was read-only to perform the recovery. In order to make the database available, you must take the damaged tablespace STUDENT offline. Type
   

   ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE;
   

   
   at the prompt and press Enter.
3. To start the database, type ALTER DATABASE OPEN; at the prompt and press Enter.
4. Before restoring the backup STUDENT, you have to access the operating system. Type HOST at the prompt and press Enter.
5. To restore the backup STUDENT, type COPY C:\ORANT\DATABASEx\ORCLBACKUP\STUDENT_R.ORA C:\ORANT\DATABASE\STUDENT.ORA at the prompt and press Enter.
6. To recover the damaged datafile, type RECOVER AUTOMATIC DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' ; at the prompt and press Enter.
7. Now you need to bring the datafile back online. Type ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' ONLINE; at the prompt and press Enter.
8. You have successfully recovered the STUDENT tablespace from the backup when the tablespace was read-only. Don't forget to make a backup of this tablespace immediately. You should also include it in your routine backup strategy. This completes the Simulation.

SQL> STARTUP OPEN PFILE=C:\ORNAT\DATABASE\INITORCL.ORA 
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.
ORA-01157: cannot identify data file 5 - file not found
ORA-01110: data file 5: 'C:\ORANT\DATABASE\STUDENT.ORA'
SQL> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' OFFLINE; 
Statement processed.
SQL> ALTER DATABASE OPEN;
Statement processed.
SQL> HOST
Microsoft(R) Windows NT(TM)
(C) Copyright 1985-1996 Microsoft Corp.
C:\orant> COPY C:\ORANT\DATABASEx\ORCLBACKUP\STUDENT_R.ORA C:\ORANT\DATABASE\STUDENT.ORA
C:\ORANT\DATABASEx\ORCLBACKUP\STUDENT_R.ORA
1 file(s) copied.
C:\orant> EXIT
SQL> RECOVER AUTOMATIC DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA';
Statement processed.
SQL> ALTER DATABASE DATAFILE 'C:\ORANT\DATABASE\STUDENT.ORA' ONLINE; 
Statement processed.
SQL>

To view the lines for the whole process in the simulation, click the View Code button.
The next lesson investigates recovery issues related to the read-only tablespace.