-
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.
Last edited by Axr2; 04-13-2004 at 02:43 PM.
-
The baseline should be in the level 1 backup.
Jeff Hunter
-
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
Last edited by Axr2; 04-13-2004 at 05:11 PM.
-
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
I'm stmontgo and I approve of this message
-
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.
Jeff Hunter
-
"
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?)
-
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 Hunter
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|