[StandbyDB] shows logs in double, one applied, one not.

[pivert]

I have multiple old logs that are always showed as non applied. (My actual sequence# is 11796.) The standby db is running fine.

If I look closer, in fact I have all theses logs in double, with the same name. One applied, and one not. (See the second query.)

Theses logs sequence# should match when I had to rebuild the standby database. (copy datafiles from primary, create standby controlfile,...)

How can I solve this problem ?

Thanks !

SQL> select name from v$archived_log where applied='NO';

NAME
--------------------------------------------------------------------------------
/db/app/oracle/oradata/kosmodb/archive/1_9976.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9975.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9978.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9977.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9980.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9979.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9982.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9981.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9983.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9984.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9985.dbf

NAME
--------------------------------------------------------------------------------
/db/app/oracle/oradata/kosmodb/archive/1_9986.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9987.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9988.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9989.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9990.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9992.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9991.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9994.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9993.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9996.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9995.dbf

NAME
--------------------------------------------------------------------------------
/db/app/oracle/oradata/kosmodb/archive/1_9998.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9997.dbf
/db/app/oracle/oradata/kosmodb/archive/1_10000.dbf
/db/app/oracle/oradata/kosmodb/archive/1_9999.dbf

26 rows selected.

SQL>


SQL> select name,applied from v$archived_log where name like '%1_999_.dbf';

NAME APP
-------------------------------------------------- ---
/db/app/oracle/oradata/kosmodb/archive/1_9990.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9992.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9991.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9994.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9993.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9996.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9995.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9998.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9997.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9999.dbf NO
/db/app/oracle/oradata/kosmodb/archive/1_9990.dbf YES

NAME APP
-------------------------------------------------- ---
/db/app/oracle/oradata/kosmodb/archive/1_9991.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9992.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9994.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9993.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9995.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9996.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9997.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9998.dbf YES
/db/app/oracle/oradata/kosmodb/archive/1_9999.dbf YES

20 rows selected.

SQL>

[pivert]

While surfing Metalink, I found this :

How to Clean-Up Entries in V$LOG_HISTORY and/or V$ARCHIVE_LOG:
==============================================================

The number of archived redo log files maintained in the controlfile and available through V$LOG_HISTORY is determined by the MAXLOGHISTORY parameter of the create database command. The archived redo log records are maintained up to the value of MAXLOGHISTORY, then wrap and overwritey previous entries starting from the beginning.

Eventually, all entries in V$LOG_HISTORY will be overwritten after the number of archived logs exceeds the MAXLOGHISTORY value.

One way to force the removal of archived log entries from the controlfile and V$LOG_HISTORY would be to recreate the controlfile.

For instructions on how to recreate the controlfile, please see the following
note:

How to Recreate the Controlfile

So, I should have a solution to my problem. And as it's a standby database, the standby controlfile is easy to recreate from the primary.

The question is : why does it happen ??

[pivert]

So OK... I think I understand why this happen.

Probably that my standby database was stopped. The primary was running and made some switch logfiles...

I then created the standby controlfile, copied it to the standby db and started the standby db in recovery mode.

The standby db had to download the logfiles from the primary, but theses logfiles were already recorded in the controlfile because they have been created by the primary before the standby control file creation.

That's why theses logs were in double, one non applied (because the record is from the primary) and one applied (the record created by the standby.

To solve the problem :
- alter database switch logfile
- wait until the log is applied on the standby then immediately stop the standby db.
- create the standby controlfile
- replace the controlfiles on the standby db by the new standby control file.

Bye