Home » RDBMS Server » Backup & Recovery » point-in-time-recovery question (oracle 10, solaris)
point-in-time-recovery question [message #318363] Tue, 06 May 2008 10:18 Go to next message
dynamometer
Messages: 2
Registered: May 2008
Junior Member
Does anyone know if it's possible to script in sqlplus a pitr backup with a variable for the time?

I have tried and get an error every time.

It seems that with the statement

recover database using backup controlfile until time 'YYYY-MM-DD:HH:MM:SS';

you cannot replace the date time stamp with a variable. Is that true? Or am I just doing it incorrectly, wrong syntax?

I've tried various things such as 'to_char(SYSDATE,'YYYY-MM-DD' || ':08:00:00')'
I've tried just SYSDATE with the formatting....

I either get an error about the string not being a constant or an error about the year having to be between a certain number. I've also tried it with defining a Variable in a script right before the statement.

Any help would be appreciated.
Re: point-in-time-recovery question [message #318366 is a reply to message #318363] Tue, 06 May 2008 10:30 Go to previous message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
below is sample code that should show you how to do what you want.

cat sample.sh 
YESTERDAY=05-MAY-08
sqlplus << EOF
/ as sysdba
set term on echo on time on
select count(*) from user_objects where trunc(created) = to_date('${YESTERDAY}','DD-MON-YY');
EXIT
EOF
 sh -x sample.sh
+ YESTERDAY=05-MAY-08
+ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 6 08:28:24 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> 08:28:25 SQL> 
  COUNT(*)
----------
        57

08:28:25 SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Previous Topic: Re-install Oracle software, retaining same ASM database.
Next Topic: How to discover DBID having RMAN backup files
Goto Forum:
  


Current Time: Sat Dec 03 14:26:26 CST 2016

Total time taken to generate the page: 0.15656 seconds