FAQ
Here is the scenario

HP UX, 10G R1, DW, noarchivelog, cold backup only

Attempt partial clone to recover data from one tablespace
Tablespace X contains datafiles 1-6

Tablespace X was put in readonly mode on April 2 - files have timestamp
of April 2
Cold backup was taken on April 7 system & sysaux have April 7 as time stamp.

We want to recover from April 2

I request the datafiles from April 7 tape, and attempt the partial clone
by creating a new control file pointing to the restored system, sysaux
and datafiles 1-6

Create the control file, attempt to open with resetlogs, I get the message

"Datafile 1 not restored from a sufficiently old backup"
(FWIW... datafile 1 *is* older the system/sysaux datafiles)

Looking at v$recover_file (as pointed out by Jeremiah Wilton last week)
for discrepancies for the scn, and of course, datafile 1-6 are one scn
-- and system and sysaux are a different scn.

So, can readonly tablespaces be restored via partial clone? Im thinking
the scn (or similar identifier) is kept in the binary control, which
wold "glue" the 2 different groups of datafiles together. Which is
"missing" from the system sysaux datafiles.

??

Openfor suggestions

Thanks !

Bob

--
"Oracle error messages being what they are, do not
highlight the correct cause of fault, but will identify
some other error located close to where the real fault lies."

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Andrey Kriushin at May 3, 2006 at 12:16 pm
    Hi,
    the easiest way to learn what to do about READ ONLY tablespaces and
    their files is to ask RBDMS itself.

    Mount the database using binary controlfile from April 7 backup. Issue
    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    ORADEBUG SETMYPID

    ORADEBUG TRACEFILE_NAME

    Check the trace file named in the last command. Carefully check where
    the names of READ ONLY tablespace datafiles are placed. Do they appear
    in CREATE CONTROLFILE statement? So by now you'll have a working example.

    BTW, I've not noticed any UNDO tablespace. Did you just forget about it?
    You'll need SYSTEM, SYSAUX and **UNDO** when recreating controfile.

    HTH

    - Andrey
  • Bob at May 3, 2006 at 12:28 pm
    Hi, Im running behind, but my experience is, including undo and opening
    the db via partial clone, will corrupt the datafiles . Just system and
    sysaux.
    bob

    Andrey Kriushin wrote:
    Hi,
    the easiest way to learn what to do about READ ONLY tablespaces and
    their files is to ask RBDMS itself.

    Mount the database using binary controlfile from April 7 backup. Issue
    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
    ORADEBUG SETMYPID
    ORADEBUG TRACEFILE_NAME
    Check the trace file named in the last command. Carefully check where
    the names of READ ONLY tablespace datafiles are placed. Do they appear
    in CREATE CONTROLFILE statement? So by now you'll have a working example.

    BTW, I've not noticed any UNDO tablespace. Did you just forget about
    it? You'll need SYSTEM, SYSAUX and **UNDO** when recreating controfile.

    HTH
    - Andrey
    --
    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."

    --
    http://www.freelists.org/webpage/oracle-l
  • Andrey Kriushin at May 3, 2006 at 4:55 pm
    Bob wrote:

    >
    Hi, Im running behind, but my experience is, including undo and
    opening the db via partial clone, will corrupt the datafiles . Just
    system and sysaux.
    bob
    As far as I know, *undo* doesn't corrupt datafiles, but instead
    participates in recovering them;-). Of course, in your particular case
    with cold backup after clean (it that true?) shutdown there are no dead
    transactions which need to be recovered.

    BTW, probably I missed something: what do you mean by "partial clone"?
    Is that some new procedure in 10g which I'm not aware of? Some automated
    step in TSPITR? Or is it just good old "take some datafiles, switch the
    others offline or not mention them when creating controlfile"?

    Andrey
  • Bob at May 4, 2006 at 12:08 am
    Hi - my partial clone is to recover 1 table from a multi terabyte
    database when all I have is a cold backup:

    Shutdown cleanly
    Perform cold backup
    (weeks pass by....)
    Request datafiles for system, sysaux, and all datafiles from the needed
    tablespace
    Create init.ora
    Start instance
    Create control file below
    alter database open resetlogs;
    export the table

    This method always works- but I need a different technique for the
    readonly tablespace

    I've done a test today on a dev db by creating a tablespace and making
    it readonly, then run backup control file to trace. The output is saying
    (see below) that I can open the database and rename the "MISSINGXXX"
    file then put the tablespace online. Interesting note the MISSING000NN
    NN is the file# of the datafile.

    But doing controlfile technique below corrupts the system and sysaux,
    like so....
    create control file from below syntax, then

    alter database open resetlogs
    .... waiting
    "disconnection forced, host def doesnt't exist"

    Which, from my experience is Oracles way of saying "your database is toast"


    Tonight I plan to test this on a tiny windows db at home and see what I
    can do. I want to do a clone with just system and sysaux and see if the
    database will open.
    Previously, bringing in undo is a no-no as I get the dreaded
    "disconnection forced, host def doesnt't exist" which indicates "failed
    recovery""

    I'm open for any suggestions, and will post my findings

    Thanks

    Bob

    STARTUP NOMOUNT

    CREATE CONTROLFILE SET DATABASE "newt" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32

    MAXLOGMEMBERS 3

    MAXDATAFILES 254

    MAXINSTANCES 1

    MAXLOGHISTORY 909

    LOGFILE

    GROUP 1 ('/sourcedb001/oradata/sourcedb/redo101.log') SIZE 100M,
    GROUP 2 ('/sourcedb001/oradata/sourcedb/redo201.log') SIZE 100M
    DATAFILE

    '/sourcedb001/oradata/sourcedb/system01.dbf',
    '/sourcedb001/oradata/sourcedb/sysaux01.dbf'
    *readonly files do not appear in this list*
    CHARACTER SET US7ASCII;

    # Database can now be opened zeroing the online logs.
    ALTER DATABASE OPEN RESETLOGS;

    # Files in read-only tablespaces are now named.
    ALTER DATABASE RENAME FILE 'MISSING00017' TO

    '/sourcedb001/oradata/sourcedb/readonly_01.dbf';

    # Online the files in read-only tablespaces.
    ALTER TABLESPACE "READONLY" ONLINE;

    Andrey Kriushin wrote:
    Bob wrote:
    Hi, Im running behind, but my experience is, including undo and
    opening the db via partial clone, will corrupt the datafiles . Just
    system and sysaux.
    bob
    As far as I know, *undo* doesn't corrupt datafiles, but instead
    participates in recovering them;-). Of course, in your particular
    case with cold backup after clean (it that true?) shutdown there are
    no dead transactions which need to be recovered.

    BTW, probably I missed something: what do you mean by "partial clone"?
    Is that some new procedure in 10g which I'm not aware of? Some
    automated step in TSPITR? Or is it just good old "take some datafiles,
    switch the others offline or not mention them when creating controlfile"?

    - Andrey
    --
    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."

    --
    http://www.freelists.org/webpage/oracle-l
  • Ken Naim at May 4, 2006 at 3:14 am
    You are missing the undo tablespace, oracle relies heavily on it and will
    behave oddly if you do not restore it.
    Ken Naim


    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Bob
    Sent: Wednesday, May 03, 2006 7:09 PM
    Cc: oracle-l
    Subject: Re: Restore Readonly Tablespace Via Partial Clone


    Hi - my partial clone is to recover 1 table from a multi terabyte database
    when all I have is a cold backup:

    Shutdown cleanly
    Perform cold backup
    (weeks pass by....)
    Request datafiles for system, sysaux, and all datafiles from the needed
    tablespace
    Create init.ora
    Start instance
    Create control file below
    alter database open resetlogs;
    export the table

    This method always works- but I need a different technique for the readonly
    tablespace

    I've done a test today on a dev db by creating a tablespace and making it
    readonly, then run backup control file to trace. The output is saying (see
    below) that I can open the database and rename the "MISSINGXXX" file then
    put the tablespace online. Interesting note the MISSING000NN NN is the
    file# of the datafile.

    But doing controlfile technique below corrupts the system and sysaux, like
    so....
    create control file from below syntax, then

    alter database open resetlogs
    .... waiting
    "disconnection forced, host def doesnt't exist"

    Which, from my experience is Oracles way of saying "your database is toast"


    Tonight I plan to test this on a tiny windows db at home and see what I can
    do. I want to do a clone with just system and sysaux and see if the database
    will open.
    Previously, bringing in undo is a no-no as I get the dreaded "disconnection
    forced, host def doesnt't exist" which indicates "failed recovery""

    I'm open for any suggestions, and will post my findings

    Thanks

    Bob

    STARTUP NOMOUNT

    CREATE CONTROLFILE SET DATABASE "newt" RESETLOGS NOARCHIVELOG
    MAXLOGFILES 32

    MAXLOGMEMBERS 3

    MAXDATAFILES 254

    MAXINSTANCES 1

    MAXLOGHISTORY 909

    LOGFILE

    GROUP 1 ('/sourcedb001/oradata/sourcedb/redo101.log') SIZE 100M,
    GROUP 2 ('/sourcedb001/oradata/sourcedb/redo201.log') SIZE 100M
    DATAFILE

    '/sourcedb001/oradata/sourcedb/system01.dbf',
    '/sourcedb001/oradata/sourcedb/sysaux01.dbf'
    *readonly files do not appear in this list*
    CHARACTER SET US7ASCII;

    # Database can now be opened zeroing the online logs.
    ALTER DATABASE OPEN RESETLOGS;

    # Files in read-only tablespaces are now named.
    ALTER DATABASE RENAME FILE 'MISSING00017' TO

    '/sourcedb001/oradata/sourcedb/readonly_01.dbf';

    # Online the files in read-only tablespaces.
    ALTER TABLESPACE "READONLY" ONLINE;

    Andrey Kriushin wrote:
    Bob wrote:

    Hi, Im running behind, but my experience is, including undo and opening the
    db via partial clone, will corrupt the datafiles . Just system and sysaux.
    bob
    As far as I know, undo doesn't corrupt datafiles, but instead participates
    in recovering them;-). Of course, in your particular case with cold backup
    after clean (it that true?) shutdown there are no dead transactions which
    need to be recovered.

    BTW, probably I missed something: what do you mean by "partial clone"? Is
    that some new procedure in 10g which I'm not aware of? Some automated step
    in TSPITR? Or is it just good old "take some datafiles, switch the others
    offline or not mention them when creating controlfile"?

    Andrey

    --
    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."

    --
    http://www.freelists.org/webpage/oracle-l
  • Andrey Kriushin at May 4, 2006 at 1:12 pm
    Hi,
    I�ve done a test today on a dev db by creating a tablespace and making
    it readonly, then run backup control file to trace. The output is
    saying (see below) that I can open the database and rename the
    "MISSINGXXX" file then put the tablespace online. Interesting note the
    MISSING000NN NN is the file# of the datafile.
    Right, up to this point everything is as expected. Your cf creation
    script is now much better. Might not be perfect yet...
    alter database open resetlogs
    .... waiting
    "disconnection forced, host def doesnt�t exist"
    Which, from my experience is Oracles way of saying "your database is
    toast"
    No, no, no. It is the info from your server process, not from the
    instance. It just seeks your sympathy because something went wrong in
    its relations with the instance;-).

    Did Oracle instance say something more specific in its alert.log?
    Probably someting about undo or just missed file with a name similar to
    MISSING...? Some datafile number which magically coincides with that of
    undo tablespace datafile?

    BTW, what are parameter settings for UNDO_MANAGEMENT, UNDO_TABLESPACE?

    Andrey
  • Hemant K Chitale at May 4, 2006 at 4:34 pm
    You need the UNDO tablespace datafile as well.

    (experience with 8.1.7, when the RBS file was missing [inadvertently] and
    Oracle could not
    complete the rollback of the instance recovery).

    Check the alert.log file for messages.

    Hemant
    At 08:08 AM Thursday, Bob wrote:
    Hi - my partial clone is to recover 1 table from a multi terabyte database
    when all I have is a cold backup:


    This method always works- but I need a different technique forš the
    readonly tablespace

    But doing controlfile technique below corrupts the system and sysaux, like
    so....
    create control file from below syntax, then

    alter database open resetlogs
    .... waiting
    "disconnection forced, host def doesnt’t exist"

    Which, from my experience is Oracles way of saying "your database is toast"
    Å¡


    Å¡STARTUP NOMOUNT
    CREATE CONTROLFILE SET DATABASE "newt" RESETLOGSÅ¡ NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 1
    MAXLOGHISTORY 909
    LOGFILE
    Å¡ GROUP 1 ('/sourcedb001/oradata/sourcedb/redo101.log') SIZE 100M,
    Å¡ GROUP 2 ('/sourcedb001/oradata/sourcedb/redo201.log') SIZE 100M
    DATAFILE
    Å¡ '/sourcedb001/oradata/sourcedb/system01.dbf',
    Å¡ '/sourcedb001/oradata/sourcedb/sysaux01.dbf'
    *readonly files do not appear in this list*
    CHARACTER SET US7ASCII
    ;
    Hemant K Chitale
    http://web.singnet.com.sg/~hkchital
  • Bob at May 5, 2006 at 2:00 am
    Hi - here is the output of my research. undo is *NOT* needed for this
    type of "partial clone".

    Keep in mind my reason for a "partial" clone is that I'm only interested
    in 1 table, as soon as I get the table exported, the clone is deleted.
    So, for example- my undo can be up to 60GB and my datafiles for the
    tablespace 200GB and if undo is not needed why try to find space for it.?

    However, my claim that bringing in undo will "corrupt" the datafiles was
    incorrect. What *will* cause trouble is if you leave the reference to
    the undo tablespace in the init.ora file. My test case below worked
    perfectly with the offline tablespace. The output shows what was done.
    We can see undo and users tablespace are "missing" but that doesnt�t
    stop me from getting the table.

    Why it failed in another environment? - I�m not sure. I wasn�t doing the
    job.
    So, here is a technique to deal with read-only tablespaces and restores.
    HTH

    bob

    INIT.ORA

    db_name=partial
    db_block_size=8192
    compatible='10.2.0.1.0'
    control_files='J:\thrash\clone\control01.ctl'
    db_recovery_file_dest_size=2147483648
    sga_target=289406976
    user_dump_dest=J:\thrash\clone\logs
    audit_file_dest=J:\thrash\clone\logs
    background_dump_dest=J:\thrash\clone\logs
    core_dump_dest=J:\thrash\clone\logs
    db_recovery_file_dest=J:\thrash\clone\logs

    #############################################

    startup nomount pfile=J:\thrash\clone\initTHRASH.ora

    CREATE CONTROLFILE SET DATABASE "PARTIAL" RESETLOGS NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

    LOGFILE

    GROUP 1 'J:\thrash\clone\REDO01.LOG' SIZE 5M,
    GROUP 2 'J:\thrash\clone\REDO02.LOG' SIZE 5M
    DATAFILE

    'J:\thrash\clone\SYSTEM01.DBF',
    'J:\thrash\clone\SYSAUX01.DBF'
    CHARACTER SET WE8MSWIN1252;
    controlfile created
    alter database open resetlogs;
    database open
    ########################################
    select file#, name from v$datafile;
    1 J:\THRASH\CLONE\SYSTEM01.DBF
    2 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00002 <-- undo
    3 J:\THRASH\CLONE\SYSAUX01.DBF
    4 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00004 <-- users
    5 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00005
    6 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00006
    7 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\MISSING00007

    NOTE the BOB datafiles are actually in the J:\thrash\clone\ directory
    ALTER DATABASE RENAME FILE 'MISSING00005' TO
    'J:\thrash\clone\BOB_01.DBF';
    Database altered.
    ALTER DATABASE RENAME FILE 'MISSING00006' TO
    'J:\thrash\clone\BOB_02.DBF';
    Database altered.
    ALTER DATABASE RENAME FILE 'MISSING00007' TO
    'J:\thrash\clone\BOB_03.DBF';
    Database altered.
    ALTER TABLESPACE "BOB" ONLINE;
    Tablespace altered.
    select count(*) from bob.mytab;
    COUNT(*)

    147543

    --

    "Oracle error messages being what they are, do not
    highlight the correct cause of fault, but will identify
    some other error located close to where the real fault lies."

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 2, '06 at 11:46p
activeMay 5, '06 at 2:00a
posts9
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase