Home » RDBMS Server » Backup & Recovery » Incomplete Recover - Srt Until time - Date format (Oracle 9.2.0.6.0 on HP-UX)
Incomplete Recover - Srt Until time - Date format [message #427000] Tue, 20 October 2009 09:36 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

I want to do incomplete recovery of database using 'UNTIL TIME' as follows

rman> recover database until time "to_date('.........')" using backup controlfile;

Since i am using to_date command here, do I need to set NLS_DATE_FORMAT prior to it?

Also if I am needed to set NLS_DATE_FORMAT but if i haven't set it explicitly which format will be picked by RMAN?

please note that I am not using 'run' for rman.

Thanks and Regards,
Chetana
Re: Incomplete Recover - Srt Until time - Date format [message #427003 is a reply to message #427000] Tue, 20 October 2009 09:44 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
No, setting TO_DATE while in RMAN will override setting NLS_DATE_FORMAT.
Re: Incomplete Recover - Srt Until time - Date format [message #427004 is a reply to message #427000] Tue, 20 October 2009 09:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Never Mind.
Ebrian answered it better.

Edit:
Changed.

[Updated on: Tue, 20 October 2009 09:48]

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427010 is a reply to message #427000] Tue, 20 October 2009 10:11 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Ebrian and Mahesh

Thanks for quick help

My original script is like as following

cp /u05/oradata/db01/backup/controlfile_backup.ctl /opt/oracle/control01.ctl
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwdb011 password=oracle entries=10
$ORACLE_HOME/bin/sqlplus "/  as sysdba" <<EOF>>/u04/scripts/logs/DB01_clone.log
startup  mount pfile=/u04/scripts/clone_from_db01/initdb01.ora
exit
$ORACLE_HOME/bin/rman target / <<EOF>>/u04/scripts/logs/DB01_clone.log
restore database force;
exit
$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF>> /u04/scripts/logs/DB01_clone.log
RECOVER automatic DATABASE  USING BACKUP CONTROLFILE;
RECOVER  DATABASE  USING BACKUP CONTROLFILE until CANCEL;
CANCEL
ALTER database open RESETLOGS;
shutdown immediate;
exit


We are not restoring controlfile, In fact we are using backedup copy, must be 'alter database backup controlfile to /u05..'
Thats' why we are recovering using backup controlfile
I hope this will be the same even in case of incomplete recovery. Right?

We are copying this backup from one rac database to another on the same location, recover one node and subsequntlt clone it further for another db by recreating controlfile

Now my question is if the syntax I put in earlier mail will work?
as follows:

cp /u05/oradata/db01/backup/controlfile_backup.ctl /opt/oracle/control01.ctl
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwdb011 password=oracle entries=10
$ORACLE_HOME/bin/sqlplus "/  as sysdba" <<EOF>>/u04/scripts/logs/DB01_clone.log
startup  mount pfile=/u04/scripts/clone_from_db01/initdb01.ora
exit
$ORACLE_HOME/bin/rman target / <<EOF>>/u04/scripts/logs/DB01_clone.log
restore database force;
exit
$ORACLE_HOME/bin/sqlplus "/ as sysdba" <<EOF>> /u04/scripts/logs/DB01_clone.log
--RECOVER automatic DATABASE  USING BACKUP CONTROLFILE;
--RECOVER  DATABASE  USING BACKUP CONTROLFILE until CANCEL;
--CANCEL
recover database until time "to_date('dd-mm-yyyy hh24:mi:ss')" using backup controlfile; --SQL
ALTER database open RESETLOGS;
shutdown immediate;
exit


Also if backup time is beyond the expected point in time of recovery, can i issue
rman>restore database until time "to_date('dd-mm-yyyy hh24:mi:ss')" 


instead of 'restore database force' on RMAN before the recover using until time?


because of urgency i am unable to write script using 'run(set until time)'

Thanks and Regards,
Chetana
Re: Incomplete Recover - Srt Until time - Date format [message #427017 is a reply to message #427000] Tue, 20 October 2009 10:44 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Ebrian/Mahesh

Can I use "to_date('dd-mm-yy... syntax during recovery on SQL

I read that it can be used in RMAN> but not sure if it can be used on SQL>

Could you please suggest on this

Regards,
Chetana
Re: Incomplete Recover - Srt Until time - Date format [message #427024 is a reply to message #427017] Tue, 20 October 2009 11:37 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check Performing Time-Based Incomplete Recovery.
Re: Incomplete Recover - Srt Until time - Date format [message #427027 is a reply to message #427000] Tue, 20 October 2009 11:43 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Ebrian,

The link you sent seems the exact one, thanks for it

Just before sometime I scheduled recover referring metalink Doc ID : 249164.1

I shall post the results tomorrow

Many Thanks to you and Mahesh for quick help

Thanks and Regards,
Chetana

Re: Incomplete Recover - Srt Until time - Date format [message #427237 is a reply to message #427000] Wed, 21 October 2009 10:53 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Ebrian/ Mahesh

finally the following worked

SQL> RECOVER automatic DATABASE UNTIL TIME '2009-10-21:06:00:00' USING BACKUP CONTROLFILE;
Media recovery complete.
SQL>
SQL> ALTER database open RESETLOGS;
Database altered.



Many Thanks for your help

Thanks and Regards,
Chetana

Re: Incomplete Recover - Srt Until time - Date format [message #427245 is a reply to message #427237] Wed, 21 October 2009 11:29 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks for responding back with your results. This may help others in the future !
Previous Topic: Restore backup from another (non-default) location
Next Topic: recover database error
Goto Forum:
  


Current Time: Mon Sep 26 11:10:11 CDT 2016

Total time taken to generate the page: 0.06304 seconds