Hello,experts
My oracle environment is Data Guard 11.2.0.1.0 physical standby,when i query SQL statement below in SQL prompt,
such as,
SYS@standby> col current_scn for 99999999999999999
SYS@standby> select current_scn from v$database;
CURRENT_SCN
----------------------------
12220045296
SYS@standby> select scn_to_timestamp(current_scn) from v$database;
select scn_to_timestamp(current_scn) from v$database
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
At the same time,i also query SQL statement below,
SYS@standby> col min(scn) for 9999999999999999999999
SYS@standby> col max(scn) for 9999999999999999999999
SYS@standby> select min(scn),max(scn) from smon_scn_time;
MIN(SCN) MAX(SCN)
---------------------------- -------------------------
12161918151 12220038810
SYS@standby> select scn_to_timestamp(min(scn)),scn_to_timestamp(max(scn)) from smon_scn_time;
SCN_TO_TIMESTAMP(MIN(SCN))
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(MAX(SCN))
---------------------------------------------------------------------------
26-AUG-17 03.50.37.000000000 AM
31-AUG-17 09.47.56.000000000 AM
SYS@standby> select count(*) from smon_scn_time;
COUNT(*)
---------------
1559
About error of 'ORA-08181' is related to total value of 'SMON_SCN_TIME'(1559) ?
Because in my another Data Guard 11.2.0.4.0 physical standby,this query is normal and total value of 'SMON_SCN_TIME' is 1709.My query as follows,
SYS@standby29> col current_scn for 99999999999999999
SYS@standby29> col min(scn) for 9999999999999999999999
SYS@standby29> col max(scn) for 9999999999999999999999
SYS@standby29> select current_scn from v$database;
CURRENT_SCN
----------------------------
12220352180
SYS@standby29> select scn_to_timestamp(current_scn) from v$database;
SCN_TO_TIMESTAMP(CURRENT_SCN)
---------------------------------------------------------------------------
31-AUG-17 10.01.00.000000000 AM
SYS@standby29> select min(scn),max(scn) from smon_scn_time;
MIN(SCN) MAX(SCN)
---------------------------- ---------------------------
12162782291 12220349663
SYS@standby29> select scn_to_timestamp(min(scn)),scn_to_timestamp(max(scn)) from smon_scn_time;
SCN_TO_TIMESTAMP(MIN(SCN))
---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(MAX(SCN))
---------------------------------------------------------------------------
26-AUG-17 05.00.23.000000000 AM
31-AUG-17 09.59.51.000000000 AM
SYS@standby29> select count(*) from smon_scn_time;
COUNT(*)
---------------
1709
Now,i have doubt SCN time has expired of my query,
could i truncate table 'SMON_SCN_TIME' ? so how to solve this problem for me now ?
Please give some advices,thanks!