OraFAQ Forum: Backup & Recovery » Incomplete Recover - Srt Until time

Home » RDBMS Server » Backup & Recovery » Incomplete Recover - Srt Until time - Date format (Oracle 9.2.0.6.0 on HP-UX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Incomplete Recover - Srt Until time - Date format [message #427000]

Tue, 20 October 2009 09:36

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

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427003 is a reply to message #427000]

Tue, 20 October 2009 09:44

ebrian
Messages: 2794
Registered: April 2006

Senior Member

No, setting TO_DATE while in RMAN will override setting NLS_DATE_FORMAT.

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427004 is a reply to message #427000]

Tue, 20 October 2009 09:46

Mahesh Rajendran
Messages: 10708
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

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

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427017 is a reply to message #427000]

Tue, 20 October 2009 10:44

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

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427024 is a reply to message #427017]

Tue, 20 October 2009 11:37

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Check Performing Time-Based Incomplete Recovery.

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427027 is a reply to message #427000]

Tue, 20 October 2009 11:43

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

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427237 is a reply to message #427000]

Wed, 21 October 2009 10:53

chetanaZ
Messages: 132
Registered: October 2009
Location: UK

Senior Member

Hi Ebrian/ Mahesh

finally the following worked

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Incomplete Recover - Srt Until time - Date format [message #427245 is a reply to message #427237]

Wed, 21 October 2009 11:29

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Thanks for responding back with your results. This may help others in the future !

Report message to a moderator

Previous Topic:	Restore backup from another (non-default) location
Next Topic:	recover database error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 21 11:35:22 CDT 2025