RE: about rman, recovery and date format

From: <krish.hariharan_at_quasardb.com>
Date: Thu, 22 Nov 2007 11:41:23 -0700
Message-ID: <002f01c82d37$491f0500$6401a8c0@BHAIRAVIPC01>

I don't think you can use the sql command since it applies to the target connection and not to the catalog connection. The errors are likely because the setting is ignored or is not in effect and your last two characters are being converted to year based on the (target/catalog) datbase's default data format setting.

You have to use NLS_DATE_FORMAT and NLS_LANG env variables (see Note:164521.1, a bit dated) to make that work. I am not sure if this is relevant in the more recent releases of Oracle/RMAN or not. In any event, I prefer the use the to_date format, to remove any ambiguity, in the set until time clause, as follows.

set until time="to_date('20-NOV-2007:23:00:00', 'DD-MON-YYYY:HH24:MI:SS')";

-Krish

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Remigiusz Sokolowski
Sent: Thursday, November 22, 2007 1:31 AM To: 'ORACLE-L'
Subject: about rman, recovery and date format

lastly I've had to recover one of databases. I use script below

run {
allocate channel d1 device type disk format '<path>/%d/%d_%T_%U.bkp' sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''"; set until time '2007-11-20 23:00:00';
restore database;
recover database;
}

During recover operation I 've got error

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

The solution was to delete lines 3 and 4 (i.e. not to set date format) - as in this I have could done because proper time was not so important and I have operated on chosen fraction of archivelogs.

Any thoughts what is wrong?

TIA
Remigiusz

Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x

Zastrzezenia:
1. Wylaczenie danej funkcjonalnosci oznacza, ze niezwlocznie przystapimy

lub juz pracujemy nad jej uruchomieniem 2. Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora

i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa Wirtualna Polska S.A.

WIRTUALNA POLSKA SA, ul. Traugutta 115c, 80-226 Gdansk; NIP: 957-07-51-216;
Sad Rejonowy Gdansk-Polnoc KRS 0000068548, kapital zakladowy 62.880.024 zlotych (w calosci wplacony)
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 22 2007 - 12:41:23 CST