Home » RDBMS Server » Backup & Recovery » Partly restore a database (10.2.0.4 (but if you have an answer in 11.2 I take it) / AIX 5.3)
Partly restore a database [message #548333] Wed, 21 March 2012 07:59 Go to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have a task which is to get an export of an account.
Easy, you will say, use Data Pump but I have not the database only a complete RMAN backup of it (including control file and spfile).
Easy once again, restore the database and export but I have not the space to restore the complete database.

Hopefully, all segments of the account are in a couple of tablespaces I know the name (and I know I have space enough to restore them).

Unfortunately, I don't know the other tablespace names (but of course SYSTEM and SYSAUX), in particular I don't know the name of the undo tablespace and, of course, I have no connection with the source database and no way to know these tablespace names.

So here's the question, how can I restore part of the database (SYSTEM, SYSAUX, undo tablespace and a couple of other ones) from a complete backup without knowing the name of the undo tablespace and of the tablespaces I don't need?

Regards
Michel
Re: Partly restore a database [message #548336 is a reply to message #548333] Wed, 21 March 2012 08:17 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Would it help to restore and mount the controlfile? Then from the datafile names one might be able to guess at the tablespace names.

Also, in mount mode can you offline (or even offline-drop) all the datafiles that you don't want, then do the restore and recovery of the database? If I remember correctly, offline files are ignored by restore/recover.

Any good?

[update: and of course, you have access to v$tablespace in mount mode]

[Updated on: Wed, 21 March 2012 08:18]

Report message to a moderator

Re: Partly restore a database [message #548345 is a reply to message #548336] Wed, 21 March 2012 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Would it help to restore and mount the controlfile? Then from the datafile names one might be able to guess at the tablespace names.


I didn't check it but I was thinking it could be possible to query v$tablespace (x$kccts) and get the names. Unfortunately it does not give the type (dba_tablespaces.contents that comes from sys.ts$) and I think (but is not sure) that undo tablespace must be restored/recovered.

Quote:
Also, in mount mode can you offline (or even offline-drop) all the datafiles that you don't want,


Unfortunately I don't know the file names (OMF ones) only the tablespace names.
But it is a good idea to follow, I think I can make some join between x$kccts, x$kccfe and x$kccfn (v$tablespace and v$datafile, don't know if I have access to the view) to get these names.
Still remains the problem of undo tablespace if it is really mandatory.

Thanks
Michel
Re: Partly restore a database [message #548352 is a reply to message #548345] Wed, 21 March 2012 09:26 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
THe name of the undo tablespace will be in the spfile. And you'll have access to v$datafile and v$tablespace, they are populated from the controlfile. I'm certain Oracle will want the undo tablespace, unless you use the hidden parameter to take the undo segments offline (but somehow you would need to work out what they were called first).
Good luck.

Re: Partly restore a database [message #548355 is a reply to message #548352] Wed, 21 March 2012 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
THe name of the undo tablespace will be in the spfile.

Yes but how to find it (or them) if it is named something like XYZ among other XYZ tablespaces?

But wait... it is in the spfile... silly me (hoping they do not change it when some transactions were still running in a previous one... fingers crossed).

Regards
Michel
Re: Partly restore a database [message #549823 is a reply to message #548355] Wed, 04 April 2012 02:24 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, here's the definitive protocol I found.
I remind the issue: you have a (complete) set of backup from a database and you want to restore, in another server without no network with the original server, only some tablespaces to export a schema.


  1. Create a basic init.ora file with only one parameter "db_name"
  2. Start the instance in nomount mode, restore spfile to a pfile and shutdown the instance
    SET DBID=${DBID};
    STARTUP NOMOUNT pfile=${WORKDIR}/init.ora;
    RESTORE SPFILE TO PFILE '${WORKDIR}/init.ora' FROM '${SPFILE}';
    SHUTDOWN ABORT;

  3. Memorize the undo tablespace(s) and modify the parameters accordingly to the new server (*dest, memory parameter, cluster->stand alone db...)
  4. Start the instance in nomount mode, restore the control file, mount the instance, and catalog the backup files
    SET DBID=${DBID};
    STARTUP NOMOUNT PFILE=${RESTODIR}/init${ORACLE_SID}.ora;
    RESTORE CONTROLFILE FROM '${CTLFILE}';
    ALTER DATABASE MOUNT;
    CATALOG START WITH '${BACKUPDIR}' NOPROMPT;
    CROSSCHECK BACKUP;

  5. Retrieve the file names for the tablespaces you want to restore ($UNDOTBS contains the undo tablespaces you retrieved in step 3, $TABLESPACES the tablespaces you want to restore and $DATE the date to recover)
    select file#, name
    from v$datafile
    where creation_time <= to_date('${DATE}','YYYYMMDDHH24MISS')
      and ts# in ( select ts# from v$tablespace 
                   where name in ('SYSTEM','SYSAUX')
                      or name in (${UNDOTBS})
                      or name in (${TABLESPACES}) )
    /

  6. Retrieve the name of the tablespaces you don't want to restore (=> $TBSNORESTO)
    select name
    from v$tablespace
    where name not in ('SYSTEM','SYSAUX')
      and name not in (${UNDOTBS})
      and name not in (${TABLESPACES})
      and included_in_database_backup = 'YES'
    /

  7. Execute the "SET NEWNAME" command for all these files as well as temporary files (retrieved from v$tempfile) and the "ALTER DATABASE RENAME FILE" for the redo log files.
  8. Restore, recover the database and open the database
    SET UNTIL TIME "TO_DATE('${DATE}','YYYYMMDDHH24MISS')";
    RESTORE DATABASE SKIP FOREVER TABLESPACE ${TBSNORESTO};
    SWITCH DATAFILE ALL;
    RECOVER DATABASE SKIP FOREVER TABLESPACE ${TBSNORESTO} DELETE ARCHIVELOG;
    ALTER DATABASE OPEN RESETLOGS;

  9. For all tablespace in $TBSNORESTO drop it with option INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
  10. Recreate the temporary files: for all tablespaces and files retrieved by the following query:
    select ts.name, f.name
    from v$tempfile f, sys.ts$ ts
    where f.ts# = ts.ts#
    /

    Execute
    ALTER TABLESPACE <tbsname> ADD TEMPFILE <new tempfile name>;
    ALTER TABLESPACE <tbsname> DROP TEMPFILE <the original tempfile name>;

  11. Export the schema

If you have any comment, it is welcome.
Note this is not a complete script, some steps have to be adapted depending the case (ASM, ASM<->Non ASM...).

Regards
Michel

[Updated on: Wed, 04 April 2012 03:51]

Report message to a moderator

Previous Topic: how to recover before 15 minutes table data from flashback
Next Topic: RMAN-00554, RMAN-04005, ORA-0103
Goto Forum:
  


Current Time: Sat Feb 24 18:00:01 CST 2018

Total time taken to generate the page: 0.02327 seconds