Home » RDBMS Server » Backup & Recovery » TSPITR via RMAN in a Data Guard Environment (10.2.0.4 on HP-UX)
TSPITR via RMAN in a Data Guard Environment [message #392128] Mon, 16 March 2009 09:37 Go to next message
mastro
Messages: 5
Registered: October 2008
Location: Hamburg (Germany)
Junior Member
I am trying to do a Tablespace-Point-In-Time-Recovery in a Data Guard Environment.

On the Primary server I backup the control files, spfile and archivelogs with RMAN (catalog). On the Physical Standby I dump additionaly to the mentioned files the datafiles via following commands:


RECOVER COPY OF DATABASE WITH TAG 'ORA\$OEM_LEVEL_0';
BACKUP DEVICE TYPE DISK INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'ORA\$OEM_LEVEL_0' DATABASE;


When I try to recover a test tablespace on the Primary point-in-time I get a ORA-01180 Error. Here comes the whole output from RMAN:

RMAN> recover tablespace mstroech_test until time "to_date('16.03.2009 14:25:30', 'DD.MM.YYYY HH24:MI:SS')" auxiliary destination '/oracle/adm/dbflash/tspitr';

Starting recover at 16-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=137 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=158 devtype=DISK

Creating automatic instance, with SID='ysop'

initialization parameters used for automatic instance:
db_name=ADM
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_ADM_ysop
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/oracle/adm/dbflash/tspitr
control_files=/oracle/adm/dbflash/tspitr/cntrl_tspitr_ADM_ysop.f


starting up automatic instance ADM

Oracle instance started

Total System Global Area 205520896 bytes

Fixed Size 2082752 bytes
Variable Size 146802752 bytes
Database Buffers 50331648 bytes
Redo Buffers 6303744 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time "to_date('16.03.2009 14:25:30', 'DD.MM.YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 16-MAR-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=36 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oracle/adm/dbflash/ADMPRIM/autobackup/2009_03_16/o1_mf_s_681661239_4vwnk7jx_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oracle/adm/dbflash/ADMPRIM/autobackup/2009_03_16/o1_mf_s_681661239_4vwnk7jx_.bkp tag=TAG20090316T142039
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/oracle/adm/dbflash/tspitr/cntrl_tspitr_ADM_ysop.f
Finished restore at 16-MAR-09

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

starting full resync of recovery catalog
full resync complete
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "to_date('16.03.2009 14:25:30', 'DD.MM.YYYY HH24:MI:SS')";
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 5 to
"/oracle/adm/data/mstroech_test.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 5;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 5 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "MSTROECH_TEST", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /oracle/adm/dbflash/tspitr/TSPITR_ADM_YSOP/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 16-MAR-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=39 devtype=DISK

creating datafile fno=1 name=/oracle/adm/dbflash/tspitr/TSPITR_ADM_YSOP/datafile/o1_mf_system_%u_.dbf

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 03/16/2009 14:59:57
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/oracle/adm/data/system01.dbf'

Any idea what is wrong with the backup or the TSPITR?
Thank you in advance!
Re: TSPITR via RMAN in a Data Guard Environment [message #392132 is a reply to message #392128] Mon, 16 March 2009 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Any idea what is wrong with the backup or the TSPITR?
RMAN-06136: ORACLE error from auxiliary database: ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/oracle/adm/data/system01.dbf'

RMAN can not create '/oracle/adm/data/system01.dbf'


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: TSPITR via RMAN in a Data Guard Environment [message #392133 is a reply to message #392128] Mon, 16 March 2009 09:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>ORA-01110: data file 1: '/oracle/adm/data/system01.dbf'

Where is it getting this path from?
Are you using a mixture of OMF and regular files?

Did you also check the filesystem permissions for the said path?
Re: TSPITR via RMAN in a Data Guard Environment [message #392138 is a reply to message #392133] Mon, 16 March 2009 10:09 Go to previous messageGo to next message
mastro
Messages: 5
Registered: October 2008
Location: Hamburg (Germany)
Junior Member
Thank you for your fast replies!

>>ORA-01110: data file 1: '/oracle/adm/data/system01.dbf'

This is the path and file of the system tablespace of the primary database. I am wondering why the TSPITR process wants to re-create this system datafile. The permissions are 640 like every other datafile.

I guess the error could be caused by my incremental backup on the Standby server. When I backup the database on the Primary via RMAN command "backup database" and automatic controlfile backup I get following error message:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/16/2009 15:47:25
RMAN-03015: error occurred in stored script Memory Script
ORA-19571: datafilecopy recid 97 stamp 681661326 not found in control file
ORA-19600: input file is datafile-copy 97 ()
ORA-19601: output file is datafile-copy 0 (/oracle/adm/data/mstroech_test.dbf)


The curious thing is that right after I set up the Data Guard environment and run a full backup, a TSPITR worked successfully. After that I cleared all backup files, did a new full backup and tried a second TSPITR test which failed with the error in my first posting.

Three situations - thre different results.


>> You need to help us by following the Posting Guidelines as stated below.
>> http://www.orafaq.com/forum/t/88153/0/

Which exactly?
Re: TSPITR via RMAN in a Data Guard Environment [message #392139 is a reply to message #392133] Mon, 16 March 2009 10:12 Go to previous messageGo to next message
mastro
Messages: 5
Registered: October 2008
Location: Hamburg (Germany)
Junior Member
Mahesh Rajendran wrote on Mon, 16 March 2009 09:50
>>Are you using a mixture of OMF and regular files?


Only regular files.
Re: TSPITR via RMAN in a Data Guard Environment [message #392295 is a reply to message #392139] Tue, 17 March 2009 05:58 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You may want to check Note: 727655.1.
Re: TSPITR via RMAN in a Data Guard Environment [message #392316 is a reply to message #392295] Tue, 17 March 2009 06:53 Go to previous message
mastro
Messages: 5
Registered: October 2008
Location: Hamburg (Germany)
Junior Member
Thank you.

I already found that Note and think that it does not reflect my problem.

I do not have duplicate incarnation entries in the control file:

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 2 ADM 929905411 CURRENT 1 12-NOV-08
1 131236 UNKNOWN 929905411 ORPHAN 8273831 12-MAR-09
1 133826 UNKNOWN 929905411 ORPHAN 8510721 16-MAR-09
Previous Topic: Regarding Cold Backup
Next Topic: RMAN backup error
Goto Forum:
  


Current Time: Thu Dec 08 16:05:06 CST 2016

Total time taken to generate the page: 0.10577 seconds