Home » RDBMS Server » Backup & Recovery » rman tablespace restore (oracle 11g ,linux machine)
rman tablespace restore [message #456906] Thu, 20 May 2010 02:03 Go to next message
saravanan82kumar
Messages: 5
Registered: September 2007
Location: Bnagalore
Junior Member
dear all,

Please find the root cause if the error while restoring tablespace

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jun 27 15:34:11 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: CPM10 (DBID=2860680927)

RMAN> #run
2> #{
3> #SQL 'ALTER TABLESPACE TSNAME OFFLINE';
4> #RESTORE TABLESPACE TSNAME;
5> #RECOVER TABLESPACE TSNAME;
6> #SQL 'ALTER TABLESPACE TSNAME ONLINE';
7> #}
8> run
9> {
10> SQL 'ALTER TABLESPACE TSNAME OFFLINE';
11> set until time "to_date('2010-05-27:14:57:50','yyyy-mm-dd:hh24:mi:ss')";
12> RESTORE TABLESPACE TSNAME;
13> RECOVER TABLESPACE TSNAME until time "to_date('2010-05-27:14:57:50','yyyy-mm-dd:hh24:mi:ss')" AUXILIARY DESTINATION '/opt/oracle/base/flash_recovery_area';
14> SQL 'ALTER TABLESPACE TSNAME ONLINE';
15> }
16>
17>
using target database control file instead of recovery catalog
sql statement: ALTER TABLESPACE TSNAME OFFLINE

executing command: SET until clause

Starting restore at 27-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK

creating datafile file number=7 name=/opt/oracle/base/oradata/cpm10/mnt01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 27-JUN-10

Starting recover at 27-JUN-10
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='gbCf'

initialization parameters used for automatic instance:
db_name=CPM10
db_unique_name=gbCf_tspitr_CPM10
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/opt/oracle/base/flash_recovery_area
log_archive_dest_1='location=/opt/oracle/base/flash_recovery_area'
#No auxiliary parameter file used


starting up automatic instance CPM10

Oracle instance started

Total System Global Area 292933632 bytes

Fixed Size 1336092 bytes
Variable Size 100666596 bytes
Database Buffers 184549376 bytes
Redo Buffers 6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2010-05-27:14:57:50','yyyy-mm-dd:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 27-JUN-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK


Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2010 15:40:59
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

Recovery Manager complete.

Report message to a moderator

Re: rman tablespace restore [message #456918 is a reply to message #456906] Thu, 20 May 2010 03:00 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
You seem to be working in the future!

[Updated on: Thu, 20 May 2010 03:01]

Report message to a moderator

Re: rman tablespace restore [message #456933 is a reply to message #456906] Thu, 20 May 2010 04:22 Go to previous messageGo to next message
saravanan82kumar
Messages: 5
Registered: September 2007
Location: Bnagalore
Junior Member
Reg:TSPITR i inserted set until time as just before the backup .
kindly help me on that to solve this issue.

Removing automatic instance
shutting down automatic instance
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /tmp/CPM10/controlfile/o1_mf_62n2f3l3_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/29/2010 14:56:55
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

[Updated on: Thu, 20 May 2010 04:48]

Report message to a moderator

Re: rman tablespace restore [message #456937 is a reply to message #456933] Thu, 20 May 2010 05:10 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Reg:TSPITR i inserted set until time as just before the backup .
What John meant is
>>RMAN-03002: failure of recover command at 06/29/2010 14:56:55

First fix your system date.
It is pointing to JUNE 2010.

Report message to a moderator

Re: rman tablespace restore [message #456938 is a reply to message #456906] Thu, 20 May 2010 05:32 Go to previous messageGo to next message
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
Senior Member
Quote:
i inserted set until time as just before the backup
Does this mean that you are trying to restore to a point earlier than your backup?

Report message to a moderator

Re: rman tablespace restore [message #456940 is a reply to message #456938] Thu, 20 May 2010 06:47 Go to previous messageGo to next message
saravanan82kumar
Messages: 5
Registered: September 2007
Location: Bnagalore
Junior Member
yes ,i apply before or after the backup time it wont work .it will work fine without TSPITR .But if applying TSPITR i got this error .please give solution to me

Report message to a moderator

Re: rman tablespace restore [message #456941 is a reply to message #456940] Thu, 20 May 2010 06:50 Go to previous messageGo to next message
saravanan82kumar
Messages: 5
Registered: September 2007
Location: Bnagalore
Junior Member
Following things are applied after change the date and time in UNIX machine.then also i got the same error.
Physical backup are available,i will cross check all those things.

RMAN> run
2> {
3> SQL 'ALTER TABLESPACE mntns OFFLINE';
4> set until time "to_date('2010-05-20:16:53:52','yyyy-mm-dd:hh24:mi:ss')";
RESTORE TABLESPACE MNTNS;
5> 6> RECOVER TABLESPACE MNTNS until time "to_date('2010-05-20:16:53:52','yyyy-mm-dd:hh24:mi:ss')" AUXILIARY DESTINATION '/tmp';
7> SQL 'ALTER TABLESPACE MNTNS ONLINE';
8> }

using target database control file instead of recovery catalog
sql statement: ALTER TABLESPACE mntns OFFLINE

executing command: SET until clause

Starting restore at 20-MAY-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=61 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/base/oradata/cpm10/mnt01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/base/flash_recovery_area/CPM10/backupset/2010_05_20/o1_mf_nnndf_TAG20100520T165326_5zbt86tv_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/base/flash_recovery_area/CPM10/backupset/2010_05_20/o1_mf_nnndf_TAG20100520T165326_5zbt86tv_.bkp tag=TAG20100520T165326
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:48
Finished restore at 20-MAY-10

Starting recover at 20-MAY-10
using channel ORA_DISK_1
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='igxz'

initialization parameters used for automatic instance:
db_name=CPM10
db_unique_name=igxz_tspitr_CPM10
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/tmp
log_archive_dest_1='location=/tmp'
#No auxiliary parameter file used


starting up automatic instance CPM10

Oracle instance started

Total System Global Area 292933632 bytes

Fixed Size 1336092 bytes
Variable Size 100666596 bytes
Database Buffers 184549376 bytes
Redo Buffers 6381568 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2010-05-20:16:53:52','yyyy-mm-dd:hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=17 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/oracle/base/flash_recovery_area/CPM10/autobackup/2010_05_20/o1_mf_s_719513608_5zbt88k4_.bkp
channel ORA_AUX_DISK_1: piece handle=/opt/oracle/base/flash_recovery_area/CPM10/autobackup/2010_05_20/o1_mf_s_719513608_5zbt88k4_.bkp tag=TAG20100520T165328
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/tmp/CPM10/controlfile/o1_mf_5zbv10hv_.ctl
Finished restore at 20-MAY-10

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until time "to_date('2010-05-20:16:53:52','yyyy-mm-dd:hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 7 to
"/opt/oracle/base/oradata/cpm10/mnt01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 7;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/CPM10/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 20-MAY-10
using channel ORA_AUX_DISK_1

One or more auxiliary set datafiles could not be removed

Removing automatic instance
shutting down automatic instance
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /tmp/CPM10/controlfile/o1_mf_5zbv10hv_.ctl deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/20/2010 17:06:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Report message to a moderator

Re: rman tablespace restore [message #457209 is a reply to message #456941] Fri, 21 May 2010 21:14 Go to previous messageGo to next message
evawanling
Messages: 1
Registered: May 2010
Junior Member
You seem to be working in the future!

Report message to a moderator

Re: rman tablespace restore [message #457214 is a reply to message #457209] Fri, 21 May 2010 22:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
evawanling wrote on Fri, 21 May 2010 22:14
You seem to be working in the future!

You seem to not pay attention to what was already said!

Report message to a moderator

Re: rman tablespace restore [message #457221 is a reply to message #457209] Sat, 22 May 2010 01:14 Go to previous message
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good! now we know you can copy and paste, let's see what will be your next step.

Regards
Michel

Report message to a moderator

Previous Topic: oracle
Next Topic: RMAN BACKUP from SNAPSHOT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jan 03 05:15:07 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.