Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Flashback Logs

578585Sep 12 2008 — edited Sep 13 2008
{color:#0000ff}I pointed this out one year back when I was on 10.2.0.1 but it seems this issue is still here today with 10.2.0.4:
If I flashback much back in time, almost as back as the OLDEST_FLASHBACK_SCN I often fall in the scenario where the OLDEST_FLASHBACK_SCN > CHECKPOINT_CHANGE# .
{color}
h6. sys@DSSPRE> select (select oldest_flashback_scn from v$flashback_database_log) OLDEST_FLASHBACK_SCN, checkpoint_change#, controlfile_change#, current_scn from v$database;
h6. OLDEST_FLASHBACK_SCN CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CURRENT_SCN
-------------------- ------------------ ------------------- --------------
{color:#ff0000}7118887956839{color} 7118887954672 7118887955984 7118887956216
h6.
{color:#0000ff}In such cases upon attempting to flashback further, based on the OLDEST_FLASHBACK_SCN I get the obvious error:
{color}
sys@DSSPRE> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[sys@DSSPRE|mailto:sys@DSSPRE]> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 2029008 bytes
Variable Size 121637424 bytes
Database Buffers 83886080 bytes
Redo Buffers 2162688 bytes
Database mounted.
[sys@DSSPRE|mailto:sys@DSSPRE]> flashback database to scn 7118887956840;
flashback database to scn 7118887956840
*
ERROR at line 1:
ORA-38743: Time/SCN is in the future of the database.


{color:#0000ff}Of course the SCN I want to flashback to must be older than CURRENT_SCN but then the OLDESTFLASHBACK_SCN appears to be no longer reliable._
{color}
sys@DSSPRE> flashback database to scn 7118887956200;
Flashback complete.
[sys@DSSPRE|mailto:sys@DSSPRE]> alter database open resetlogs;
Database altered.
[sys@DSSPRE|mailto:sys@DSSPRE]> select (select oldest_flashback_scn from v$flashback_database_log) OLDEST_FLASHBACK_SCN, checkpoint_change#, controlfile_change#, current_scn from v$database;
OLDEST_FLASHBACK_SCN CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CURRENT_SCN
-------------------- ------------------ ------------------- --------------
7118887957768 7118887956231 7118887956399 7118887956426

Any thoughts?

{color:#0000ff}Enrico
{color}

Comments

Laurent Schneider
It works by me, and french is my mother tongue, but I cannot stand reading messages in anything else then english!
SQL> select x;
select x
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SCOTT@LSC62/10.1.0.2
SQL> alter session set nls_language=french;

Session modifiée.

SQL> select x;
select x
       *
ERREUR à la ligne 1 :
ORA-00923: mot-clé FROM absent à l'emplacement prévu
natlangs
Hi Salman,

What did you see when NLS_LANGUAGE is set to FRENCH? Is it only the accented characters that are not appearing correctly?

Nat
Salman Qureshi
i did the following by connectiung scott
remember that i m working in the SQLPLUS where my SERVER is installed

SQL> select x;
select x
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


now i set the NLS_LANGUAGE

SQL> alter session set NLS_LANGUAGE=FRENCH;

Session altered.


now i again run the same statement but error message is still in english

SQL> select x;
select x
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


so any solution
Regards
JustinCave
Did you install the non-English languages when you installed Oracle?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
358102
Here is some information from Oracle's Globalization Support Guide.

The language-specific binary message files that are actually installed depend on the languages that the user specifies during product installation. Only the English binary message file and the language-specific binary message files specified by the user are installed.
Salman Qureshi
one thing more to tell that when i query EMP...the data formate is according to french..so the problem is just that error messages are not being displayed in french
Regards
358102
Salman,

I still think that you may not have all the 'nlb' files
installed. Here is what I get on my machine. 
SQL> select x;
select x
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> select sysdate from dual;

SYSDATE
---------
08-JUL-04

SQL> alter session set nls_language = french nls_date_language=french;

Session altered.

SQL> select * from nls_session_parameters
  2  ;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   FRENCH
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              FRENCH
NLS_SORT                       FRENCH
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

SQL> select sysdate from dual;

SYSDATE
-----------
08-JUIL.-04

SQL> select x;
select x
       *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
Salman Qureshi
well i have total 530 .nlb files in my
ORACLE_HOME\ocommon\nls\ADMIN\DATA directory


i also set the session parameters as u did in ur example and when queried NLS_SESSION_PARAMETERS...i got the same result as u got

when i queried v$version again i got the same result as u got
so whats the problem....

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Laurent Schneider 
find $ORACLE_HOME -name "sp1*.msb"
/app/oracle/product/9.2.0.5/sqlplus/mesg/sp1us.msb
/app/oracle/product/9.2.0.5/sqlplus/mesg/sp1d.msb
/app/oracle/product/9.2.0.5/sqlplus/mesg/sp1f.msb
/app/oracle/product/9.2.0.5/sqlplus/mesg/sp1i.msb
and I installed english (us), german (d), french (f) and italian (i).

Regards
Laurent
358102
Laurent is right. You should be looking for the '.msb' (message binaries) files.
Here is what I get on my unix installation. 
find $ORACLE_HOME -name "sp1*.msb"
/tech/oracle/product/v920/sqlplus/mesg/sp1us.msb
Salman Qureshi
well i m working on Windows 2000 and when i searched for .msb files, i found many on different locations but non starts with SPL..i mean spl*.msb...so if anyone who has done this on windows plz tell me and another thing that now if i try to do a custome installation for this NLS support then whgich feature should i install?
Regards
Salman Qureshi
another thing that no file is on the path that u have specified according to ur unix environment...i mean to say that there is no "app" directory in my ORACLE_HOME directory
Regards
Laurent Schneider
do you have some files in $ORACLE_HOME/sqlplus/mesg ?
1 - 13
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 11 2008
Added on Sep 12 2008
#general-database-discussions
9 comments
775 views