Tablespace restore / recovery with RMAN
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Tablespace restore / recovery with RMAN

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    807

    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 03:43 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The baseline should be in the level 1 backup.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2002
    Posts
    807
    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 06:11 PM.

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  5. #5
    Join Date
    Oct 2002
    Posts
    807
    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

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Oct 2002
    Posts
    807
    "
    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?)

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Oct 2002
    Posts
    807
    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.

  10. #10
    Join Date
    Oct 2002
    Posts
    807
    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
  •  


Click Here to Expand Forum to Full Width