Wednesday, December 23, 2009

Point in Time Recovery fails with ORA-01841 on Oracle 10.2 SE

Yesterday I've decided to do point in time recovery for Oracle 10.2.0.4 SE database.

I've used the same script that was working before on other databases - but my operation failed with ORA-01841 error.

After spending some time on Google I found that this error was mostly related to illegal year entered.


ORA-01841:
(full) year must be between -4713 and +9999, and not be 0
Cause: Illegal year entered
Action: Input year in the specified range

To test and fix script I've decided to execute it on test Oracle 10.2.0.4 EE database but there everything worked without any error.



Then I've searched little more and found Metalink note 370500.1.

So I was encountering this error because NLS_LANG was not specified.

Little test case:
$ uname -a
Linux testhost2 2.6.18-164.el5 #1 SMP Thu Sep 3 02:16:47 EDT 2009 i686 i686 i386 GNU/Linux

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')
2  from dual;

TO_CHAR(SYSDATE,'DD
-------------------
23.12.2009 08:51:03



First script execution:

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1342177280 bytes

Fixed Size                     1267380 bytes
Variable Size               1191184716 bytes
Database Buffers             134217728 bytes
Redo Buffers                  15507456 bytes

RMAN> run
2> {
3> sql 'alter session set NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"';
4> set until time='23.12.2009 08:51:03';
5> restore database;
6> recover database;
7> }

sql statement: alter session set NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"

executing command: SET until clause

Starting restore at 23.12.2009 08:56:19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oratest/oradata/rasapp/datafiles/system01.dbf
restoring datafile 00002 to /oratest/oradata/rasapp/datafiles/undotbs101.dbf
restoring datafile 00003 to /oratest/oradata/rasapp/datafiles/sysaux01.dbf
restoring datafile 00004 to /oratest/oradata/rasapp/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oratest/rmanbkp/hsl1iqaa_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oratest/rmanbkp/hsl1iqaa_1_1 tag=TAG20091222T204706
channel ORA_DISK_1: restore complete, elapsed time: 00:07:27
Finished restore at 23.12.2009 09:03:48

Starting recover at 23.12.2009 09:03:49
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2009 09:03:50
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'DEC 23 2009 08:51:03'
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



For second execution I've changed script a little:

RMAN> run
2> {
3> set until time="to_date('23.12.2009 08:51:03','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
...
...
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2009 09:23:01
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'DEC 23 2009 08:51:03'
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



Then I've tried with setting NLS_LANG to .UTF8.
$ export NLS_LANG=.UTF8
$ echo $NLS_LANG
.UTF8
$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Dec 23 09:24:33 2009

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

connected to target database: RASAPP (DBID=1650089695, not open)

RMAN> run
2> {
3> sql 'alter session set NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"';
4> set until time='23.12.2009 08:51:03';
5> restore database;
6> recover database;
7> }

using target database control file instead of recovery catalog
sql statement: alter session set NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"

executing command: SET until clause

Starting restore at 23.12.2009 09:25:09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oratest/oradata/rasapp/datafiles/system01.dbf
restoring datafile 00002 to /oratest/oradata/rasapp/datafiles/undotbs101.dbf
restoring datafile 00003 to /oratest/oradata/rasapp/datafiles/sysaux01.dbf
restoring datafile 00004 to /oratest/oradata/rasapp/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oratest/rmanbkp/hsl1iqaa_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oratest/rmanbkp/hsl1iqaa_1_1 tag=TAG20091222T204706
channel ORA_DISK_1: restore complete, elapsed time: 00:07:56
Finished restore at 23.12.2009 09:33:07

Starting recover at 23.12.2009 09:33:08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /oratest/oradata/archlogs/1_1_706308053.dbf
archive log thread 1 sequence 2 is already on disk as file /oratest/oradata/archlogs/1_2_706308053.dbf
archive log thread 1 sequence 3 is already on disk as file /oratest/oradata/archlogs/1_3_706308053.dbf
archive log thread 1 sequence 1 is already on disk as file /oratest/oradata/archlogs/1_1_706312614.dbf
archive log filename=/oratest/oradata/archlogs/1_1_706308053.dbf thread=1 sequence=1
archive log filename=/oratest/oradata/archlogs/1_2_706308053.dbf thread=1 sequence=2
archive log filename=/oratest/oradata/archlogs/1_3_706308053.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:34
Finished recover at 23.12.2009 09:33:45


Now everything worked without error.

I must remember that NLS_LANG affects only 10.2 Standard Edition - on Enterprise Edition everything works fine.


After database point-in-time recovery, I must open the database with the RESETLOGS option.

$ sqlplus "/as sysdba"

SQL> alter database open resetlogs;

Database altered.

Written December 23, 2009 by Marko Sutic

   8 comments

8 comments:

  1. Marcin PrzepiorowskiDecember 23, 2009 at 1:08 PM

    Hi,

    In times of Oracle 8i and early 9i
    NLS_LANG has to be set to american_america to make RMAN running ;)

    As I can see there is still some old hard coded date setting.

    ReplyDelete
  2. Marko SuticDecember 23, 2009 at 2:17 PM

    Hi Marcin,

    thanks for your comment. It is very interesting that you had to set NLS_LANG to run RMAN in earlier versions :)

    I've tested this on Oracle 9.2.0.6 EE, which is the earliest version we have, and NLS_LANG can be empty.

    Maybe I should try with 9.2 SE :)

    ReplyDelete
  3. Marcin PrzepiorowskiDecember 23, 2009 at 2:46 PM

    Hi,

    For sure I have to set NLS_LANG in 8.1.7 with catalog and for some operations in nocatalog mode.

    This same was for 9.0.x - I don't remember exactly versions and I was fixed in next releases. I assume
    that for 9.2 should be OK but check it with catalog too if you have a chance.

    regards,
    Marcin

    ReplyDelete
  4. Marko SuticDecember 23, 2009 at 3:51 PM

    Hi,

    I don't use catalog for managing backups so I can't test this right now - but maybe later I will...

    For me it is important that I've seen this error and next time when I encounter it I will know what to do :)

    Enjoy,
    Marko

    ReplyDelete
  5. UnknownMarch 17, 2010 at 8:40 PM

    Encountered this same error, but had a stable environment (no recent changes) so I knew it was an actual date problem.

    With this query, this is how I found the problem.

    SELECT SUBSTR (date_field, 8, 11) AS year_txt
    , COUNT (*) AS cnt
    from table
    where to_char(date_field,'YYYY') = 0
    or to_char(date_field,'YYYY') <= -4713
    or to_char(date_field,'YYYY') >= 9999
    group by SUBSTR (date_field, 8, 11)

    Then I just found the row by searching the table with one of the where statements above based on the corrupted date data.

    ReplyDelete
  6. Damir VadasMay 7, 2010 at 1:05 PM

    Nice topic-very handful!

    ReplyDelete
  7. Marko SuticMay 8, 2010 at 8:53 PM

    Thanks Damir ;)

    ReplyDelete
  8. Marlon CostaApril 15, 2019 at 3:22 PM

    Worked with 11.2.0.4

    Thanks

    ReplyDelete

Subscribe to: Post Comments (Atom)