Recovering From Loss Of Datafile

Recovering From Loss of Datafile We know that in Oracle, certain things are and can be done at certain times. One of those operations pertains to adding or creating datafiles. One operation where adding datafiles to a database is common is within or during the CREATE DATABASE-related statements. Even if using Oracle managed files and accepting defaults, datafiles will be created. Creating tablespaces also includes a provision for adding datafiles. Yet another add/create datafile event takes place when altering a tablespace for growth-related purposes (altering a tablespace by adding a datafile). In all of these scenarios, there is one thing in common: when the datafile is added, it is associated with a tablespace you have named or identified in the DDL statement. To categorize the commands or operations being used, we can identify them as CREATE or ALTER operations. Is it possible to add (by creating) a datafile to the database without specifying the tablespace to which it belongs? That is, can you issue an ALTER DATABASE CREATE DATAFILE ‘<path/name>’; and expect Oracle to know what to do with this file? If so, does this work for any datafile? And, why would you do this in the first place? After you alter the structure of a database (e.g., add a new datafile for whatever reason, add a new tablespace, or add another file to an existing tablespace), what is a best practice to follow? That’s right, take a backup. The Administrator’s Guide (10gR2) states the following no less than five times: After making any structural changes to a database, always perform an immediate and complete backup. Let’s suppose that sometime after having added a datafile (or before having a backup) and data manipulation operations have been applied to objects whose tablespace owns the datafile of interest, you lose the datafile. Media recovery is now required. Can the data be recovered? Don’t you need a backup of the datafile to which archived redo logs are applied in order to perform media recovery? Almost every example of media recovery seems to include that part – restore a backed up copy of the datafile and then apply archived redo logs to bring the tablespace to a more current point in time. This scenario is different – there is no backed up copy of the datafile to start with, so how can recovery be used here? The file existed once and now it does not. The control file still knows about the file, which is why you may or may not be able to open the database, or keep the database in an open state. This is where the ALTER DATABASE CREATE DATAFILE statement comes into play. You do not explicitly state the tablespace to which the datafile belongs because Oracle already knows this bit of information. Your task (one of two) in this scenario is to create a replacement file (same name or rename it, to include using a different path). Do you have to specify the size of the file? No, again, Oracle already knows this. Your other mission (two of two) is to apply archived redo logs against this filler/placeholder file. To reiterate what must be done: create a new datafile and apply archived redo logs (using RECOVER DATAFILE). Does this work for any datafile? It does not, specifically; you cannot use this technique to recover SYSTEM tablespace datafiles. Does this work for any DML-without-a-backed-up-copy scenario? No, it does not. If the DML was not logged, then there is nothing to recover from the archived redo logs. When is, or when can it be, DML not logged? That’s a different

topic (covered here), so for the point of this scenario, we assume that normal logging has taken place. Seeing is believing, so let’s prove that recovery can take place with an example. Create a test database (one you can afford to trash) and make the files as small as practical (we don’t care about the size; it’s just the fact that they exist). The database will need to be in archivelog mode. Once the database is open for business, create a new tablespace or add a datafile to an existing tablespace AND have the file location in a place where you can replicate media failure. A flash drive is handy for this; just pull the drive when ready to simulate loss of the datafile. Another way is to shutdown the database and rename the target/now missing file. After adding the datafile, create a table, add some data to it, ensure the redo logs rotate through at least once, and then pull the drive. In the recovery scenario, pretending that the flash drive location is no longer available, the CREATE DATAFILE statement will use syntax like so: ALTER DATABASE CREATE DATAFILE ‘the old path/name’ AS ‘use a new path/name’; The media recovery step is then applied against the new datafile via: RECOVER DATAFILE ‘the new path/name’; Assuming this all goes well, what should you do when recovery is complete? That’s right – take a backup. If you can recover (so easily?) this way, then why is a backup after adding a new file such a big deal? Going back to the NOLOGGING option, what if a table had been created via NOLOGGING? Or an index? Or lots and lots of each? At least with the backed up datafile, you will have captured the structure of those objects. Without the file, you would have to re-create them, which, in a recovery scenario, could add extra time you don’t really want to be spending given the possible/potential high degree of visibility or scrutiny you may be experiencing (ignoring the obvious question about why there wasn’t a backed up copy of the file in the first place). The figures and pictures below show the scenario of losing a datafile (I did a shutdown and deleted the file after the service was stopped). Starting Files FILE_NAME MB ----------------------------------------------------- ----C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS01.DBF 5 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\SYSAUX01.DBF 240 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\UNDOTBS01.DBF 25 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\SYSTEM01.DBF 470 Add a datafile SQL> alter tablespace users 2 add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF' 3 size 5M; Tablespace altered.

User DML SQL> alter tablespace users SQL> conn scott/tiger@demo Connected. SQL> create table lost 2 as select * from all_objects; Table created. SQL> delete from lost; 40768 rows deleted. SQL> commit; Commit complete. SQL> insert into lost select * from all_objects; 40768 rows created. SQL> commit; Commit complete. Switch logfiles SQL> conn system/oracle@demo Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> /

System altered.

Shutdown, and startup. Check the alert log.

Alert Log - file is missing Errors in file c:\oracle\product\10.2.0\admin\demo\bdump\demo_dbw0_3512.trc: ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF' ORA-27041: unable to open file OSD-04002: unable to open file O/S-Error: (OS 2) The system cannot find the file specified. Perform Recovery SQL> conn / as sysdba Connected. SQL> select status from v$instance; STATUS -----------MOUNTED SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF'; Database altered. SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF'; ORA-00279: change 548985 generated at 07/01/2008 21:52:32 needed for thread 1 ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DEMO\ARCHIVELOG\2008_07 _01\O1_MF_1_ 2_%U_.ARC ORA-00280: change 548985 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL} Log applied. Media recovery complete. SQL> alter database open; Database altered. Back to normal SQL> conn scott/tiger@demo Connected. SQL> select count(*) from lost; COUNT(*) ---------40768

In Closing This is actually pretty easy to practice and demonstrate, and it offers a little twist on the usual “how and when” you add datafiles to the database operation. As an alternative demonstration, create a table using the NOLOGGING option (can you identify one way how?) and then apply DML, rotate the logs, and induce media failure. Without re-creating the table, can you recover now?