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 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 |
John Watson
Messages: 8964 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 #548352 is a reply to message #548345] |
Wed, 21 March 2012 09:26 |
John Watson
Messages: 8964 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 #549823 is a reply to message #548355] |
Wed, 04 April 2012 02:24 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
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.
- Create a basic init.ora file with only one parameter "db_name"
- 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;
- Memorize the undo tablespace(s) and modify the parameters accordingly to the new server (*dest, memory parameter, cluster->stand alone db...)
- 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;
- 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}) )
/
- 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'
/
- 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.
- 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;
- For all tablespace in $TBSNORESTO drop it with option INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
- 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>;
- 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
|
|
|
Goto Forum:
Current Time: Sat Feb 01 20:31:17 CST 2025
|