Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 9, 2008

Hands-on Oracle: Backup and Recovery Games - Creating Datafiles

By Steve Callan

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: {<RET>=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?

» See All Articles by Columnist Steve Callan



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM