Backup & Recovery :: Restore And Recover Database Excluding Tablespace
Jul 27, 2011
I am doing some test, seeking your expert opinion.I have a database and have the following backup strategy.
1) Database running in archive log mode.
2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS';
RMAN> BACKUP DATABASE;
3) I take a separate Backup of DP_TS_LOB tablespace.
RMAN> BACKUP TABLESPACE DP_TS_LOB;
4) I backup all the archived redo logs.
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> backup archivelog all delete input;
Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.I tried the following but unsuccessful.
RMAN> restore controlfile to 'g:ctl_bckctl_01' from autobackup;
RMAN> restore spfile to 'g:ctl_bckspfile' from autobackup;
RMAN> sql "create PFILE = ''G:ctl_bckPFILE'' from SPFILE = ''G:ctl_bckSPFILE''";
Update the new parameter with new control file name. Copied the control file and parameter file to corresponding location.
First I assumed "Recover Database" will only recover online database files. But got the following error.
RMAN> recover database;
Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:23:03
RMAN-06094: datafile 8 must be restored
Then I tried recovering data file separately, but same error.
RMAN> recover datafile 1;
Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:24:30
RMAN-06067: RECOVER DATABASE required with a backup or created control file
I am trying to create my database on a new server using rman incremental hot backups.
I have already restored and recovered my level 0 database on new server, but i am not able to restore/recover the level 1 backup on the new server. I have transported level 1 backup irom the old server to the new one. Do i need to recover lvl 1 bkp only or restore it also.
I'm using Oracle Standard edition 11gR2, actually that's why I'm posting this thread because I can't use TRANSPORT TABLESPACE feature.
I need to restore and recover two tablespaces, backed up in source database in another database. The destination db has been duplicated from the source db a couple of days ago so the structure is identical.
I cannot use Data Pump on this specific tablespace, because it causes some strange behavior in our application. So I took a backup from tablespaces and now I'm looking for a solution to restore and recover it in another db.
Is there any command to restore a tablespace from a specific file? I googled a lot and no luck so far..
P.S: I use controlfile instead of recovery catalog.
How many times i can do recover (TSPITR) a tablespace ? While trying to recover tspitr tablespace on 2nd time i got error message ORA-01178 file 13 created before last create controlfile,but first time it was success.
I have to perform the TSPITR of a tablespace to recover it to a time 03-apr-2011 5 pm.Now I have done that.
Now I find that I need to actually recover it ti 04-apr-2011.Can I again recover the tablespace to 04-Apr after recovering it to 03-Apr by mistake I am not usng recovery catalog;
i want to clone the test db to dev db but i dont want default users and tablespaces(SYS,SYSAUX,...) in the test db datapump and exp are not working, then contacted ORACLE support and they concluded that test db sysaux was corrupted.i took RMAN all tablespace bkup except default tablespaces. then created new database using DBCA and now i want to restore and recover all those test db tablesapces but while i'm doing restore its saying
RMAN> restore tablespace MAIN_DATA;
Starting restore at 07-SEP-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=129 device type=DISK creating datafile file number=20 name=/opt/oracle/oradata/DEV71/main_data01.dbf restore not done; all files read only, offline, or already restored Finished restore at 07-SEP-12
I have the following situation: a customer sent me a group of files, that he said to be a "backup" of an old database, that is no longer online (he doesn´t use our software anymore). However, he needs to recover the data in this database. The person at the company who generate these files doesn´t work there anymore, so we don´t have any of the following information:
- Database version, connection details, service names, user names neither tablespace names.
We were just told that these files were recorded in a DVD, and is que only source remaining. The files he sent are the following:
Currently my DB is running on 10g Archivelog mode. All the archive logs being generated at /u04/dbarch/.The server crashed yesterday. Now when I want to start my instance it throwing the error ORA-01110 and ORA-01113-- system01.dbf need more recovery.The below are the command I fired from sql prompt:-
SQL>STARTUP MOUNT; SQL>RECOVER DATABASE UNTIL CANCEL; But still oracle suggesting that system01.dbf need more recovery.
multiple disk failure, the database was open at the time.We have managed to recover the data from the disk and have put it on another disk.We have got a new server with oracle 9 running.
A blank database has been created.I have copied the control files and redo files onto the new server and left the datafiles , (.dbf,.ora) on a seperate external disk F drive (this is because the original database resided on F drive)I can mount the database , however when I try to open it I get the following
ORACLE instance started.
Total System Global Area 135338868 bytes Fixed Size 453492 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: 'F:ORACLEORADATALCMPROSYSTEM01.DBF' ORA-01207: file is more recent than controlfile - old controlfile
We have faced database(10.2.0) issue cause incomplete recovery and have performed open resetlogs. This DB is of 12Tb. In 10g opening database with reset logs do not invalidate previous backups. we have another replica(no reset state)of this database which we sync using archives.how we can apply those archives(reset database)to previous database?
I need to restore a backup of database A from into Database B. Both have the same names. Database B is already up and running.
I have a full rman backup of Database A ( it was taken with a recovery catalog which I don't have access to now as it was deleted ). I just have the full backup pieces including the control files. Is it possible to recover this database into Database B from this stand point.
I was thinking
2. Shutdown database B 3. Mount database B. It has same name as Database A. 4. With RMAN restore controlfile. Will a new controlfile be created from the backup directory I have the rman pieces for the full backup in ?
I have oracle 10g installed on my system and name of the database is "ORCL" for which I have schedule the incremental backup everyday. Mentioned below are the steps followed
*************PARAMETERS TO BE CHANGED****************** configure channel 1 device type disk format '\192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incrementalstd_%U'; configure channel 2 device type disk format '\192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incrementalstd_%U'; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '\192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incrementalcntrl_%U'; CONFIGURE RETENTION POLICY TO REDUNDANCY 7; CONFIGURE CONTROLFILE AUTOBACKUP ON; *******************************************************
*******COMMAND FOR THE CONNECTING TO RMAN************** rman LOG = \192.16.17.140dbbackups192.16.17.152oracle_rman_backup_incremental rmanlog_%date:~4,2%-%date:~7,2%-%date:~10%.txt APPEND CONNECT TARGET SYS/ORACLE@ORCL *******************************************************
********INCREMENTAL BACKUP COMMAND********************* RUN { BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE; BACKUP ARCHIVELOG ALL DELETE INPUT; } ********************************************************************
Now I want to restore this backup to some other system with new database. How to do this recovery to some other database on new system.
Can we restore a database if we have only the datafiles available? The database has been dropped but we have just the datafiles with NO system, sysaux,users and undo datafiles that were saved after taking the database offline and then dropping the database. I know this is strange but if possible, I want to see if we can retrieve some of the data from those datafiles to another database. BTW, There is no backup available for this database except the dbf files on OS.
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.
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?
We have a database server running oracle 12G on linux which is the production then we have a DR using the same version of DB and linux, I want to restore from tape into the DR from the production backup. the UNIX admin has restored already from tape the rman backup, i would like to know what are the steps to restore the backup from tape, also the DR database server was created identical like production using the same spfile etc.
were do i copy restored file into on the DR server? also then in RMAN do i just start the DR database and run the command restore database? what else do i need?
I have taken cold back-up of a complete database using below rcv from a server.
RMAN> connect target / run{ set command id to 'bkp_EMCPROD_db'; allocate channel c1 type 'sbt_tape' parms="BLKSIZE=1048576, ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)"; backup current controlfile format 'ct_t%t_T%T_d%d_s%s.ctl'; backup database format 'db_t%t_T%T_d%d_s%s_p%p.db'; backup current controlfile format 'ct_t%t_T%T_d%d_s%s.ctl'; release channel c1; }
I need to restore this back-up on another server of the same Database Name. Below is the rcv which my sr. dba asked me to look into for the restore.
/* From the new server to restore the back-up */ RMAN> connect target "rman/xxxxx@emcprod" connect auxiliary / run { set until time "to_date('Nov 03 2011 19:00:00','Mon DD YYYY HH24:MI:SS')"; allocate auxiliary channel c1 type 'sbt_tape' parms="ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.sacsun8.opt)"; restore database; }
I am new in oracle. I want to restore my database in oracle 10g enterprise. Actually i have a backup file(.bkp file) from oracle 10g xe and now i wanna restore in oracle 10g enterprise.
Question: does oracle recover past the scn mentioned in the control file or do you need recover using backup control file for it? No specific scn,time specified.
Scenario: Restore database on new server + copy archivelogs from original to minimize data loss. Backup : 5 AM Archivelogs: 7 AM
New server: Configure dummy instance Restore spfile from tape Restore controlfile from tape: SCN 5AM Restore database (no scn specified) from tape: : SCN 5AM Copy archivelogs from old server to archivelog new location: SCN 7AM. Recover database open database -> SCN 5AM or 7AM?
Does it recover till SCN 5AM (controlfile) or SCN 7AM (latest scn in copied archivelog)
restored from tape a backup then issued rman command
RMAN> restore database;
Starting restore at 21-MAY-13 Finished restore at 21-MAY-13
RMAN> alter database open;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 05/21/2013 15:36:15 ORA-01190: control file or data file 1 is from before the last RESETLOGS ORA-01110: data file 1: '/u02/oradata/system01.dbf' [code]....
I create a test database which is duplicate of my production server and after every 15 or 20 days I need to synchronize test db with production db.So I clone it and it takes about a day.I have to remove all datafiles from test and move from production to there on test server and I applied restore and recover.So it is very time consuming process.
I want to know is there any process which makes my work less like I just need to apply all rest of the logs which are new after synchronize last time from production server that need restore and recover.
I'm in the process of testing a restore/recover from a simulated full system and media loss. A level 0 backup was taken from the server, and I'm trying to restore/recover to a point in time on a second server. I have created the database (with the same name) and have been able to successfully restore both the controlfile and spfile from the autobackup.
how does RMAN treat the "set until time"? The level 0 was taken on a database/server that is in CST while the database I'm trying to do a restore/ recover to is in EST. So when trying to do a point in time recovery, should I specify the time in EST or CST?
I run DLY cold backups including controlfile & archivelogs and keep 3 gens of backups and archivelogs to 3 days also. What is the correct procedure to restore back to an old cold backup. Database is backed up Mon,Tue,Wed on Thur we require to bring database back to Tuesday DLY security.
I have tried: startup mount restore database from tag 'DLYTUE' recover database
( this just rolls forward on all archive logs once it restores datafiles, not getting to point to alter database open resetlogs.)
Next tried to :
recover database archivelog tag 'DLYTUE' ( same outcome )
What is the correct rman procedure including should I remove old archive logs from working OS directory ? Also need to know once I restore back and open database to TUE security how do you tidy up the archive logs and dumps which are no longer require i.e. : weds..
I have read about using the set until time option but how to I get absolute time from when cold backup completed if this is the correct procedure.
