Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Unanswered: To recover dropped Tablespace from Backupset

    Hi,
    1. I have taken a hot backup of database with RMAN on 28.04.2004 at 2030 hrs. It is KR.dmp. It includes system, undo, tmp & TEST tablespaces.
    2. Now i dropped the tablespace 'TEST' from the database.
    3. I am trying to restore/recover my dropped tablesapce using RMAN only and executing this script, but it is giving some error during restoring the tablespace from backupset KR.dmp. I have queries recovery catalog & confirm that tablespace TEST is there in backupset.

    RMAN> run{
    2> allocate channel ch1 type disk format '/mnt43/db/KR.dmp';
    3> restore tablespace 'TEST';
    4> }

    starting full resync of recovery catalog
    full resync complete
    allocated channel: ch1
    channel ch1: sid=17 devtype=DISK

    Starting restore at 29-APR-04

    released channel: ch1
    RMAN-00571: ================================================== =========
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ================================================== =========
    RMAN-03002: failure of restore command at 04/29/2004 14:47:20
    RMAN-06004: ORACLE error from recovery catalog database: RMAN-20202: tablespace not found in the recovery catalog
    RMAN-06019: could not translate tablespace name "TEST"

    RMAN>

    How can it restore my this dropped tablespace..?? Please help & suggest some way around.

    Regards,
    Kamesh Rastogi
    - KR

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Have you tried outside of RMAN?

    Stop the database,
    Put the test tablespace file from the backup back in the correct location...
    startup mount pfile=xxx
    alter database recover tablespace test

    I'm assuming that you are in ARCHIVELOG mode since you say you have a
    "HOT" backup... See if Oracle can find the archivelogs and apply them to
    get the tablespace back online...

    HTH
    Gregg

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Why do I get the feeling he used the EXPORT utility?
    Why would you name the backup file the same extension as an export file?

    to verify, please issue this in RMAN:
    list backup;

    My feeling is that once you log into the catalog all you should need to enter is:
    sql "alter tablespace TEST offline";
    restore tablespace TEST;
    sql "alter tablespace TEST online";

    then you are done.

    BTW - You don't mention what version you are running so I am basing everything off of 9.2.0.4
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Mar 2004
    Location
    Colorado
    Posts
    49
    If you have droped the test tablespace from the target database you will need to do a point in time recovery back to a time before you droped the tablespace. Trying to recover test will fail because it does not exist in your current enviroment (You dropped it and commited) Then you will need to export out all the objects in the tablespace and recover you database back to present and import you test data.

  5. #5
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Why do I get the feeling he used the EXPORT utility?
    Why would you name the backup file the same extension as an export file?

    to verify, please issue this in RMAN:
    list backup;


    Hi Duke,
    This is RMAN backup..Yes..the file was given like export dump i.e. .dmp. I have checked my catalog [list backup] & my this backup set is there. And the verison is 9.2.0.4.0. I can connect to my recovery catalog, but i hope this might not work. The tablespace TEST is not there NOW in production database so i can make it offline & possibly cannot recover it. After sometime, i will test out your solution & see if that works.

    Also, I tried finding some workaround on Metalink which says if u have dropped the tablespace then RMAN cannot recover it. We need some Tablespace Point in Time Recovery (TSPITR) to do the same. I am on the job to get this base & let see if i can recover it back.

    Thanks & Regards,
    Kamesh Rastogi
    - KR

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    restore tablespace TEST should work in RMAN when a tablespace has been dropped.

    If the datafile was also dropped then be sure to issue a restore datafile datafilename after your restore tablespace command.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi,
    Thanks Duke for all those info. I tried and tested...but seems nothing is coming in my way...I am at the same place. I have doubt that if i hv dropped the tablespace (using drop tablespace TEST), then how can i make that offline: I am giving complete steps & also will give what i hv tried. This will be useful for u to understand my problem in much better way:

    1. First i tried like this from my Backup KR.dmp (this is RMAN hot backup...) and getting ORA-00959.
    RMAN> run{
    2> allocate channel ch1 type disk format '/mnt43/db/KR.dmp';
    3> sql "alter tablespace TEST offline";
    4> restore tablespace "TEST";
    5> sql "alter tablespace TEST online";
    6> }

    allocated channel: ch1
    channel ch1: sid=11 devtype=DISK

    sql statement: alter tablespace TEST offline
    released channel: ch1
    RMAN-00571: ================================================== ========
    RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS ==========
    RMAN-00571: ================================================== ========
    RMAN-03009: failure of sql command on default channel at 05/01/2004 09:23:24
    RMAN-11003: failure during parse/execution of SQL statement: alter tablespace TEST offline
    ORA-00959: tablespace 'TEST' does not exist

    2. Then i tried just to restore tablespace from my backup...No break-through
    RMAN> run{
    2> allocate channel ch1 type disk format '/mnt43/db/KR.dmp';
    3> restore tablespace "TEST";
    4> }

    allocated channel: ch1
    channel ch1: sid=11 devtype=DISK

    Starting restore at 01-MAY-04

    released channel: ch1
    RMAN-00571: ================================================== ========
    RMAN-00569: ========== ERROR MESSAGE STACK FOLLOWS ===========
    RMAN-00571: ================================================== ========
    RMAN-03002: failure of restore command at 05/01/2004 09:25:23
    RMAN-06004: ORACLE error from recovery catalog database: RMAN-20202: tablespace
    not found in the recovery catalog
    RMAN-06019: could not translate tablespace name "TEST"

    3. Then i tried to restore my deleted datafile of this dropped tablespace. (the datafile ws removed by command rm /mnt11/db/higfl/higfl_test_data1.dbf (in Unix). This is giving me some surprise. It is saying that this datafile is not available in Recover Catalog whereas it is having a valid backup set.

    ==> The List of backupset KR.dmp is listed..... <==
    BS Key Type LV Size Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    91 Full 160M DISK 00:00:39 28-APR-04
    BP Key: 92 Status: AVAILABLE Tag: TAG20040428T202242
    Piece Name: /mnt43/db/KR.dmp
    Controlfile Included: Ckp SCN: 866653 Ckp time: 28-APR-04
    List of Datafiles in backup set 91
    File LV Type Ckp SCN Ckp Time Name
    ---- -- ---- ---------- --------- ----
    1 Full 866654 28-APR-04 /mnt43/db/higfl/sys/higfl_sys1.dbf
    2 Full 866654 28-APR-04 /mnt43/db/higfl/sys/higfl_undo1.dbf
    3 Full 866654 28-APR-04 /mnt11/db/higfl/hindiapps/higfl_hindiapps_data1.dbf
    4 Full 866654 28-APR-04 /mnt21/db/higfl/hindiapps/higfl_hindiapps_indx1.dbf
    5 Full 866654 28-APR-04 /user11/db/higfl/rmancat/higfl_rmancat_data1.dbf
    6 Full 866654 28-APR-04 /mnt11/db/higfl/test/higfl_test_data1.dbf

    ==> Please note file No. 6 is listed there......<==

    ==> The command used for restoring datafile <==
    RMAN> run{
    2> allocate channel ch1 type disk format '/mnt43/db/KR.dmp';
    3> restore datafile '/mnt11/db/higfl/test/higfl_test_data1.dbf';
    4> }

    allocated channel: ch1
    channel ch1: sid=16 devtype=DISK

    Starting restore at 01-MAY-04

    released channel: ch1
    RMAN-00571: ================================================== ========
    RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS ==========
    RMAN-00571: ================================================== ========
    RMAN-03002: failure of restore command at 05/01/2004 09:33:30
    RMAN-20201: datafile not found in the recovery catalog
    RMAN-06010: error while looking up datafile: /mnt11/db/higfl/test/higfl_test_data1.dbf



    So Sir, basically i am not getting any break-through. I am still trying. Some guys say that it has to be TSPITR making clone database (another instance) and then recover till the time before tablespace was dropped and then export TS from clone database & import it to production database and then switch datafile etc. etc. Just a breif summary...TSPITR is altogether another theory. Require more care & attention...!! some risk is also involved here !!!

    Rest all is fine. Thanks.

    Regards,
    Kamesh Rastogi
    - KR

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    I think there is something going on with your recovery catalog.

    Why are you specifying your backupfile? RMAN already knows what file to use because of the recovery catalog. What happens when you do not specify the backup file and just connect target, connect catalog and then issue the recover command?

    also, I assume your catalog db is not the same as the db you are attempting to recover I hope.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi,
    Thanks.
    The reason why i was specifying backupfile because i wanted to load a tablespace TEST from a specified backupset. Anyhow, i tried restore without specifying backupset, but this is of no good. Here is the error i list for u:

    RMAN> run{
    2> restore tablespace 'TEST';
    3> }

    Starting restore at 04-MAY-04

    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=11 devtype=DISK
    RMAN-00571: ================================================== =========
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ================================================== =========
    RMAN-03002: failure of restore command at 05/04/2004 08:27:22
    RMAN-06004: ORACLE error from recovery catalog database: RMAN-20202: tablespace
    not found in the recovery catalog
    RMAN-06019: could not translate tablespace name "TEST"

    Also, the recovery catalog is in the same database where i am restoring my tablespace....Will it make any difference during restore / reload operation..??

    Regards,
    Kamesh Rastogi
    - KR

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by RastogiKamesh
    Also, the recovery catalog is in the same database where i am restoring my tablespace....Will it make any difference during restore / reload operation..??

    Regards,
    Kamesh Rastogi
    It makes tons of difference. Especially depending on what tablespace your catalog is listed on. Think of it this way: You cannot shut down a db and recover if your catalog is located on the shutdown db since you would not be able to read the catalog.

    Granted, it looks like your datafiles and tablespaces for your catalog are seperate, but this is still not a correct backup method.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    have you tried these steps?
    NOTICE! do not pute tablspace or datafile in quotes!!

    PHP Code:
    sql "alter tablespace test offline";
    sql "alter datafile 6 offline";

    restore tablespace test;
    restore datafile 6;

    recover tablespace test;
    recover datafile 6;

    sql "alter tablespace test online";
    sql "alter datafile 6 online"
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Hi,
    I tried as u suggested & it has not worked. And i am sure that this way it is not going to help us. May be TSPITR is useful. Thanks.

    Regards,
    Kamesh Rastogi
    - KR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •