Tablespace restore / recovery with RMAN

Hi,
I've got a question on tablespace restore / recovery with RMAN. Background : I am currently backing up a database (in archivelog mode) using rman scripts. I use incremental backups to backup the database every night (level 0 on sun, level 1 on other days). Archive logs are backed up every hour.
I'd like to get your opinion on ways to restore the tablespace in the below scenario.

1) I create a NEW tablespace (tablespace 'TEST_TS') at 1pm today and add a bunch of tables to it.
2) I then rename the underlying datafile '/u12/oradata/TESTRMAN/test_ts01.dbf' to test_ts01.dbf.old or something.

Now how do I recover this tablespace (TEST_TS)? I tried offlining it, and restoring it from rman..but that doesn't seem to work since it doesn't have a baseline.

Any ideas? Do I have to do a point in time recovery or something?

Thanks for your time.

PS : Yes, I realize that it is a good practise to backup the database immediately after creating a new tablespace..but lets keep that aside for now.

The baseline should be in the level 1 backup.

Jeff / moderators,
May be I didn't phrase my question right. I still continue to get a "RMAN-06023: no backup or copy of datafile 3 found to restore" message while trying to restore the new tablespace.

I've simulated and pasted all the steps again:
1) I first performed a level 0 incremental backup.
2) THen created a tablespace.
3) Then renamed the underlying d/f.
4) Then tried restoring the tablespace.

I've "cut n paste" all the steps that I performed. By the way, one other issue cropped up while simulating the below restore..I was able to issue checkpoints (for a while) even after the underlying datafile was renamed at the OS level! Pretty bizzare..please see the below, you'll probably understand this a lil better.

I first kicked off the backup. It was successful. If you'd like I can paste the logfile later.

oracle[/export/home/oracle/rman] rman @testrmanincr0.rman log='/export/home/oracle/rman/testrmanincr0.log'


oracle[/export/home/oracle/rman] cat testrmanincr0.rman
connect target sys/blah@TESTRMAN
connect catalog rman/rmanblah@DWDEV
run { execute script testrman_incr0; }
===================
Here is the rman backup script being called by testrmanincr0.rman :

CREATE SCRIPT testrman_incr0
{
allocate channel t1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
allocate channel t2 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
BACKUP INCREMENTAL LEVEL = 0 CUMULATIVE
format 'testrman_incr0_%t_%s_%p'
filesperset 5
DATABASE;
sql 'alter system archive log current';
backup
format 'testrman_incr0_archlog_%t_%s_%p'
archivelog all;
release channel t1;
release channel t2;
}
==================
Now to creating the new tablespace

SQL> create tablespace test_ts datafile '/u12/oradata/TESTRMAN/test_ts.dbf' size 1M;

Tablespace created.

SQL> !ls -l /u12/oradata/TESTRMAN/test_ts.dbf
-rw-r----- 1 oracle oinstall 1064960 Apr 13 13:39 /u12/oradata/TESTRMAN/test_ts.dbf

SQL> create table junk_on_test_ts (a varchar2(20)) tablespace test_ts;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> !mv /u12/oradata/TESTRMAN/test_ts.dbf /u12/oradata/TESTRMAN/test_ts.dbf.o

SQL> alter system checkpoint;

System altered.

SQL> !ls -l /u12/oradata/TESTRMAN/test_ts.dbf*
-rw-r----- 1 oracle oinstall 1064960 Apr 13 13:41 /u12/oradata/TESTRMAN/test_ts.dbf.o

SQL> alter system checkpoint;

System altered.

HOW IN THE WORLD DOES IT ISSUE CHECKPOINTS WHEN THE D/F HAS BEEN RENAMED?!! (Not yelling..just making this prominent)

No error messages in alertlog either!

oracle[/u01/app/oracle/admin/TESTRMAN/bdump] tail alert_TESTRMAN.log
Tue Apr 13 13:35:13 2004
ARC0: Evaluating archive log 1 thread 1 sequence 23
ARC0: Unable to archive log 1 thread 1 sequence 23
Log actively being archived by another process
Tue Apr 13 13:35:13 2004
ARCH: Completed archiving log 1 thread 1 sequence 23
Tue Apr 13 13:39:25 2004
create tablespace test_ts datafile '/u12/oradata/TESTRMAN/test_ts.dbf' size 1M
Tue Apr 13 13:39:25 2004
Completed: create tablespace test_ts datafile '/u12/oradata/T

After about 2-3 minutes of continuously issuing checkpoints, I eventually see the following errors in the alertlog. Why did it take so long for the smon to wake up and figure the checkpointing error?! So I could potentially have inserted rows into this table although the underlying datafile was gone! So would these rows have been written to?! This is bizzare.

oracle[/u01/app/oracle/admin/TESTRMAN/bdump] tail alert_TESTRMAN.log
Errors in file /u01/app/oracle/admin/TESTRMAN/bdump/testrman_smon_13050.trc:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u12/oradata/TESTRMAN/test_ts.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
Tue Apr 13 13:46:13 2004

Anyway, back to the restore issue at hand :

oracle[/u12/oradata/TESTRMAN] rman target / catalog rman/rmanblah@DWDEV

Recovery Manager: Release 9.2.0.4.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: TESTRMAN (DBID=2651072106)
connected to recovery catalog database

RMAN> run { allocate channel t1 type 'sbt_tape' parms
2> 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
3> sql "alter tablespace TEST_TS offline immediate";
4> restore tablespace TEST_TS;
5> recover tablespace TEST_TS;
6> sql "alter tablespace TEST_TS online";
7> release channel t1;
8> }

starting full resync of recovery catalog
full resync complete
allocated channel: t1
channel t1: sid=11 devtype=SBT_TAPE
channel t1: Tivoli Data Protection for Oracle: version 5.2.0.0

sql statement: alter tablespace TEST_TS offline immediate

Starting restore at 13-APR-04

released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/13/2004 13:56:48
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore

Quote:

---

Originally posted by Axr2
HOW IN THE WORLD DOES IT ISSUE CHECKPOINTS WHEN THE D/F HAS BEEN RENAMED?!! (Not yelling..just making this prominent)

No error messages in alertlog either!

---

riddle me this, what does the CHECKPOINT Clause do?

What version of oracle are you using? did you sync up the catalog after you added the new files if you're using 8i?

steve

Moderators / Advisors - I would greatly appreciate it if you will please throw some light on this tablespace recovery issue.

Thanks, Anand

Steve, to answer your qns :
1) 'alter system checkpoint;' is the 'manual' way to issue a checkpoing (versus doing it automatically by setting the log_checkpoint_interval parm..or the checkpoint thats automatically issued when the log switches)

2) No, I didn't sync the catalogs after adding the new tablespace. But if you notice the RMAN recovery automatically resynced the catalogs as the first step.

3) Am using 9.2.0.4

I'm not sure you can recover without a baseline. I'll be interested if any other RMAN users can provide some feedback.

"
I'm not sure you can recover without a baseline. I'll be interested if any other RMAN users can provide some feedback."

Jeff -
So RMAN expects that one issue a "Level 0" backup immediately after creating a new tablespace? That's not very 'user friendly' for lack of a better term.

I would think everyone using RMAN would've encountered this restore issue or atleast have tested/simulated it before implementation..It'll be great if they could share their thoughts.


- Anand
PS : Any thoughts on the checkpoint process (although the underlying d/f had dissappered?)

Quote:

---

Originally posted by Axr2
So RMAN expects that one issue a "Level 0" backup immediately after creating a new tablespace? That's not very 'user friendly' for lack of a better term.

I would think everyone using RMAN would've encountered this restore issue or atleast have tested/simulated it before implementation

---

I didn't say a "Level 0", I said a baseline. When you do an incremental "Level 1" backup, the baseline of the datafile gets backed up in your "Level 1" backup. You then have a place to recover from. I know this works. I've tested it. I've done 6 production recoveries like this. I'm just not sure how RMAN would recover when he doesn't have that initial file to apply changes to.

Jeff,
Yeah, sorry..I meant to say "level 0" or "level 1" or any level for that matter. So Oracle expects a baseline or a backup to be able to restore a tablespace! That effectively translates to - "Although you might create a new tablespace during the day (regular work hours), please refrain from using the new tablespace (till the next day) until a baseline backup has been issued. Lest you run the risk of not being able to recover the tablespace and underlying data, should the need arise."

That's hardly user-friendly. How does one go about implementing new projects/tools on an existing production system then? Basically the DBA is expected to create the new tablespaces on a certain day. Backup the database at night (or whenever appropriate). And then, wait until the next day (or until the 'baseline' has been established) to create objects on the new tablespace!

- Anand
PS : RMAN users - please comment on this issue.

I logged a TAR with Oracle about this. Here is the excerpt from the horse's mouth. I guess the new tablespace is not recoverable in the scenario I was trying to simulate.

"ANSWER:
==========
Called Anand
Explained that unfortunately, we cannot restore something that was not backed up. So even though there are archivelogs, we do not have a datafile to restore. As we need a baseline from which to restore, if it was never backed up, we do not have that baseline."

I don't mean to mix issues here..but what beats me is how does Data guard (physical standby) then create a a new tablespace on the standby when you create one on the primary? Obviously the standby is intelligent enough to extract DDL from the archive logs (if you use a ARCH log transport) and create the tablespace on the standby..so why is RMAN not able to do the same! Am I missing something obvious?

- Anand

Quote:

---

Originally posted by Axr2
I logged a TAR with Oracle about this. Here is the excerpt from the horse's mouth. I guess the new tablespace is not recoverable in the scenario I was trying to simulate.

"ANSWER:
==========
Called Anand
Explained that unfortunately, we cannot restore something that was not backed up. So even though there are archivelogs, we do not have a datafile to restore. As we need a baseline from which to restore, if it was never backed up, we do not have that baseline."

I don't mean to mix issues here..but what beats me is how does Data guard (physical standby) then create a a new tablespace on the standby when you create one on the primary? Obviously the standby is intelligent enough to extract DDL from the archive logs (if you use a ARCH log transport) and create the tablespace on the standby..so why is RMAN not able to do the same! Am I missing something obvious?

- Anand

---

that depends, if you have standby_management set to auto then yeah it will create the ts's and df's on the fly 4 u

http://metalink.oracle.com/metalink/...atabase_id=NOT

Jeff,
Thanks for the note. Yup, I've performed that (adding a d/f and 'fooling' it to perform recovery on the new file) with old traditional hot backups in the past. The process is somewhat akin to standby database maintenance in 7.3.4 days..

Anyway, that's exactly what I told the guy in Oracle support as well. But he claims that one can't do this directly through RMAN. (It is another matter that support person didn't know that such a recovery was even possible! hehe..).

RMAN still has some ways to go before it can be the 'plug n play' tool that Oracle claims it is.

- Anand

FYI. RMAN addresses tablespace recovery without a baseline backup.

Here's how. Eventually, asked Tom about it. Embarassingly for me, he pointed me to the user guide. :(
Recovery is identical to how it was performed with traditional Oracle hot backups.


------------

Recovering a Lost Datafile Without a Backup: Example
In this scenario, the following sequence of events occurs:

You make a whole database backup of your ARCHIVELOG mode database.
You create a tablespace containing a single datafile called ?/oradata/trgt/history01.dbf.
You populate the newly created datafile with data.
You archive all the active online redo logs.
Someone accidentally deletes ?/oradata/trgt/history01.dbf from the operating system before you have a chance to back it up.
Are you prevented from recovering the data in the lost datafile because you have no backup of the file? No. You can recover the data by creating a new datafile with the same filename as the lost datafile, then run the RECOVER command to apply the redo for this file.

For example, start RMAN, connect to the target database, and then run the following statements at the RMAN prompt:

# take the missing datafile offline
# note that SQL statement is bounded by double quotes, but the datafile name has two
# individual single quotes both before and after it
SQL "ALTER DATABASE DATAFILE
'' ?/oradata/trgt/history01.dbf '' OFFLINE";
# create a new datafile with the same name as the missing datafile
SQL "ALTER DATABASE CREATE DATAFILE
'' ?/oradata/trgt/history01.dbf '' ";
# recover the newly created datafile
RECOVER DATAFILE '?/oradata/trgt/history01.dbf';
# bring the recovered datafile back online
SQL "ALTER DATABASE DATAFILE
'' ?/oradata/trgt/history01.dbf '' ONLINE";