| point-in-time-recovery question [message #318363] |
Tue, 06 May 2008 10:18  |
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  |
 |
BlackSwan
Messages: 26766
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
