Home » RDBMS Server » Backup & Recovery » problem in TSPITR (11.2 ,windows 2008)
problem in TSPITR [message #609577] Mon, 10 March 2014 02:28 Go to next message
kakakoko
Messages: 22
Registered: March 2014
Junior Member
Hi,

When I want to performing fully automated TSPITR by following command:

run {
sql 'alter system archive log current';
recover tablespace 'TESTTABLESPACE' until time "to_date('2014-03-10 09:44:09', 'YYYY-MM-DD HH24:MI:SS')" auxiliary destination = 'E:\AX';
backup tablespace 'TESTTABLESPACE';
sql 'alter tablespace "TESTTABLESPACE" online';
}

the following errors occurred:

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: '+DATA/orcl/datafile/system.260.826913305'


What is the problem?
Re: problem in TSPITR [message #609582 is a reply to message #609577] Mon, 10 March 2014 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Database Backup and Recovery User's Guide
Chapter 21 Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)

Re: problem in TSPITR [message #609583 is a reply to message #609582] Mon, 10 March 2014 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are you sure Oracle has the necessary privileges on 'E:\AX'?
Is 'E:' a local or a network drive?

Re: problem in TSPITR [message #609584 is a reply to message #609583] Mon, 10 March 2014 02:44 Go to previous messageGo to next message
kakakoko
Messages: 22
Registered: March 2014
Junior Member
>>Are you sure Oracle has the necessary privileges on 'E:\AX'?
How can I check?
>> Is 'E:' a local or a network drive?
E:\AX is local drive.
Re: problem in TSPITR [message #609588 is a reply to message #609584] Mon, 10 March 2014 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How can I check?


1/ Execute "Services", scroll down to the instance service "OracleServiceXXXX", right click on "Properties", click on "Connection" tab, note the account that started the service (maybe SYSTEM)
2/ Right click on E:, click on "Security" tab, check the privileges of the previous account
3/ Do the same thing with E:\AX

Note: "local" meant local to the database server.

Re: problem in TSPITR [message #609593 is a reply to message #609588] Mon, 10 March 2014 03:17 Go to previous messageGo to next message
kakakoko
Messages: 22
Registered: March 2014
Junior Member

the account that started the service is system, and it has full permission on 'E:' and 'E:\AX'.

>> Note: "local" meant local to the database server.
yes.
Re: problem in TSPITR [message #609594 is a reply to message #609593] Mon, 10 March 2014 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you make local connections with RMAN (that is without @...), check you own account has also the privileges.

Re: problem in TSPITR [message #609597 is a reply to message #609577] Mon, 10 March 2014 03:48 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Your RMAN command should have generated a lot more output than the three lines you have given, please can you post the complete output?
Re: problem in TSPITR [message #609602 is a reply to message #609597] Mon, 10 March 2014 03:56 Go to previous messageGo to next message
kakakoko
Messages: 22
Registered: March 2014
Junior Member
the command output is:


using target database control file instead of recovery catalog
sql statement: alter system archive log current

Starting recover at 10-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK
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='EFen'

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=EFen_tspitr_ORCL
compatible=11.2.0.0.0
db_block_size=4096
db_files=200
sga_target=280M
processes=50
db_create_file_dest=E:\AX
log_archive_dest_1='location=E:\AX'
#No auxiliary parameter file used


starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2175128 bytes
Variable Size                100667240 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4886528 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('2014-03-10 09:44:09', '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 10-MAR-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=42 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 E:\ARCHIVE LOGS\ORCL\AUTOBACKU
P\2014_03_10\O1_MF_S_841830125_9KVTTXSH_.BKP
channel ORA_AUX_DISK_1: piece handle=E:\ARCHIVE LOGS\ORCL\AUTOBACKUP\2014_03_10\
O1_MF_S_841830125_9KVTTXSH_.BKP tag=TAG20140310T094205
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\AX\ORCL\CONTROLFILE\O1_MF_9KW43ZXX_.CTL
Finished restore at 10-MAR-14

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('2014-03-10 09:44:09', 'YYYY-MM-DD HH24:MI:SS')";
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  '"TESTTABLESPACE"' ||' offline immediat
e';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  6 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 clone tempfile  2 to new;
set newname for datafile  5 to
 "+DATA/orcl/datafile/testdatafile1";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 6, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace "TESTTABLESPACE" offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to E:\AX\ORCL\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
renamed tempfile 2 to E:\AX\ORCL\DATAFILE\O1_MF_TEMPTEST_%U_.TMP in control file


Starting restore at 10-MAR-14
using channel ORA_AUX_DISK_1

creating datafile file number=1 name=E:\AX\ORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF
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 E:\AX\ORCL\CONTROLFILE\O1_MF_9KW43ZXX_.CTL deleted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/10/2014 12:20:48
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01180: can not create data
file 1
ORA-01110: data file 1: '+DATA/orcl/datafile/system.260.826913305'

--moderator update: added code tags. jw.

[Updated on: Mon, 10 March 2014 04:04] by Moderator

Report message to a moderator

Re: problem in TSPITR [message #609603 is a reply to message #609602] Mon, 10 March 2014 04:02 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
And now one problem is clear:
creating datafile file number=1 name=E:\AX\ORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF
One or more auxiliary set datafiles could not be removed


Before your next post, please read our OraFAQ Forum Guide and read How to use [code] tags and make your code easier to read I'll add the [code] tags to your previous post.



[Updated on: Mon, 10 March 2014 04:06]

Report message to a moderator

Re: problem in TSPITR [message #609605 is a reply to message #609603] Mon, 10 March 2014 04:37 Go to previous message
kakakoko
Messages: 22
Registered: March 2014
Junior Member
Quote:
Before your next post, please read our OraFAQ Forum Guide and read How to use [code] tags and make your code easier to read I'll add the [code] tags to your previous post.

thanks for your notification.


Quote:
And now one problem is clear:

creating datafile file number=1 name=E:\AX\ORCL\DATAFILE\O1_MF_SYSTEM_%U_.DBF
One or more auxiliary set datafiles could not be removed


How can I resolve the problem?

Previous Topic: Oracle 11g Backup
Next Topic: Duplicate DB Issue with Set until time (merged)
Goto Forum:
  


Current Time: Sat Oct 25 09:38:50 CDT 2014

Total time taken to generate the page: 0.08843 seconds