Re: single table restore w/out exports

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1995/04/05
Message-ID: <Pine.SUN.3.91.950405090216.27399B-100000_at_seatimes>#1/1

> >We are running cold backups as our backup. Shut down database, backup
> >system files, restart db. Our users want to be able to restore single
> >tables as they did before when we we using exports. Is there a way to
> >use the datafiles and a temp db to trick oracle into loading the table,
> >so we could export it from a copy of the datafile?
 

> You could do an export before the backup...
> Or put together a procedure to select every column from every table into
> a ascii file(for each table), then you can restore single rows.

Preliminary work to get ready:

1. Be sure the system admin is using a backup that will allow redirection.
2. Use the ORACLE_BASE organization expecially for the areas the database files will reside.
3. Have a second SID and area for a second copy of your database already defined with an initSID.ora all set up to go.
4. Use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command (note that TRACE is not quoted) to dump a CREATE CONTROLFILE to the ???.trc file.
5. Edit said trc file to extract the SQL statements needed to create a set of CONTROL FILES for the database using the new location paths. Save this in your $ORACLE_BASE/admin/SID/pfile directory, where SID is your alternate database SID.

When the need arrises to pick up a single table or a set of rows from said table:

1. Restore the approprite cold backup using redirection to put the files in the locations specified in the CREATE CONTROLFILE sql statements.
2. Remove all occurances of CONTROL.CTL that were just restored.
3. Use the SQL statements in $ORACLE_BASE/admin/SID/pfile to build the new CONTROL.CTL files with the revised data file names. You can even change the name of the database (as long as the new name agrees with the new initSID.ora).
4. The SQL statements should have started up the 'new' database. You are now free to export or use SQL to copy tables/rows from one database to another.

Good luck. I'm trying to encourage my management to purchase SQL*TRAX so I can restore 'goofs' directly from the archived redo log files. But, the above method does work (unless I forgot a step) as I've replicated databases (changing both the SID and the database name) using this method (OK, so I skipped writing to tape and simply copied (cp) the appropriate files while the source instance was shut down).

Hope this helps.

--Steve

PS. Everybody else, just for the safety factor, go ahead and build that CREATE CONTROLFILE set of sql statements using the above command and a good editor. It may save your companies bacon and make you a hero.

+----------------------------------------------------+
| Steve Butler          Voice:  206-464-2998         |
| The Seattle Times       Fax:  206-382-8898         |
| PO Box 70          Internet:  sbut-is_at_seatimes.com |
| Seattle, WA 98111    Packet:  KG7JE_at_N6EQZ.WA       |
+----------------------------------------------------+

All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Wed Apr 05 1995 - 00:00:00 CEST