Duplicate Sequence# in both primary and standby database in v$archived_log

[Nett]

Please guys i need help here i have two databases one is primary the other is standby database, i stopped the standby database and i executed the following command

alter database commit to switchover to primary

the after a seconds i descovered that i did a mistake and i executed the command
alter database commit to switchover to physical standby;

now i have a problem in both databases , I have duplicate sequence number in both databases,

Look at sequence number 10468
-----------------------------------------------------------------------
1- primary

SQL> select sequence#, dest_id, archived, applied, completion_time from v$archived_log where dest_id = 2 and sequence# > 10399 order by sequence#

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10400 2 YES YES 10-MAR-10
10401 2 YES NO 11-MAR-10
10402 2 YES YES 11-MAR-10
10403 2 YES YES 11-MAR-10
10404 2 YES YES 11-MAR-10
10405 2 YES YES 11-MAR-10
10406 2 YES YES 11-MAR-10
10407 2 YES YES 11-MAR-10
10408 2 YES YES 11-MAR-10
10409 2 YES YES 11-MAR-10
10410 2 YES NO 11-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10411 2 YES NO 11-MAR-10
10412 2 YES NO 11-MAR-10
10413 2 YES NO 11-MAR-10
10414 2 YES NO 11-MAR-10
10415 2 YES YES 11-MAR-10
10416 2 YES NO 11-MAR-10
10417 2 YES NO 11-MAR-10
10418 2 YES NO 11-MAR-10
10419 2 YES NO 11-MAR-10
10420 2 YES NO 11-MAR-10
10421 2 YES YES 11-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10422 2 YES YES 11-MAR-10
10423 2 YES YES 11-MAR-10
10424 2 YES YES 11-MAR-10
10425 2 YES YES 11-MAR-10
10426 2 YES YES 11-MAR-10
10427 2 YES NO 11-MAR-10
10428 2 YES YES 11-MAR-10
10429 2 YES NO 12-MAR-10
10430 2 YES NO 12-MAR-10
10431 2 YES NO 12-MAR-10
10432 2 YES YES 12-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10433 2 YES NO 12-MAR-10
10434 2 YES NO 12-MAR-10
10435 2 YES NO 12-MAR-10
10436 2 YES NO 12-MAR-10
10437 2 YES NO 12-MAR-10
10438 2 YES NO 12-MAR-10
10439 2 YES NO 12-MAR-10
10440 2 YES NO 12-MAR-10
10441 2 YES NO 12-MAR-10
10442 2 YES NO 12-MAR-10
10443 2 YES NO 12-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10444 2 YES NO 12-MAR-10
10445 2 YES NO 13-MAR-10
10446 2 YES NO 13-MAR-10
10447 2 YES NO 13-MAR-10
10448 2 YES NO 13-MAR-10
10449 2 YES NO 13-MAR-10
10450 2 YES NO 13-MAR-10
10451 2 YES NO 13-MAR-10
10452 2 YES NO 13-MAR-10
10453 2 YES YES 13-MAR-10
10454 2 YES NO 13-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10455 2 YES NO 13-MAR-10
10456 2 YES NO 13-MAR-10
10457 2 YES NO 14-MAR-10
10458 2 YES NO 14-MAR-10
10459 2 YES NO 14-MAR-10
10460 2 YES NO 14-MAR-10
10461 2 YES NO 14-MAR-10
10462 2 YES YES 14-MAR-10
10463 2 YES NO 14-MAR-10
10464 2 YES YES 14-MAR-10
10465 2 YES NO 14-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10466 2 YES YES 14-MAR-10
10468 2 YES NO 14-MAR-10
10468 2 YES NO 14-MAR-10
10469 2 YES NO 14-MAR-10
10470 2 YES NO 14-MAR-10
10471 2 YES NO 14-MAR-10
10472 2 YES NO 14-MAR-10
10473 2 YES NO 14-MAR-10
10474 2 YES NO 14-MAR-10
10475 2 YES NO 14-MAR-10
10476 2 YES NO 14-MAR-10

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10477 2 YES NO 14-MAR-10
10478 2 YES NO 14-MAR-10
10479 2 YES NO 14-MAR-10
10480 2 YES NO 14-MAR-10
10481 2 YES NO 15-MAR-10
10482 2 YES NO 15-MAR-10
10483 2 YES NO 15-MAR-10
10484 2 YES NO 15-MAR-10
10485 2 YES NO 15-MAR-10
10486 2 YES NO 15-MAR-10

186 rows selected.

SQL>
-----------------------------------------------------

in Standby database

SQL> select sequence#, dest_id, archived, applied, completion_time from v$archived_log where dest_id = 2 and sequence# > 10300 order by sequence#

SEQUENCE# DEST_ID ARC APP COMPLETIO
---------- ---------- --- --- ---------
10427 2 YES YES 11-MAR-10
10428 2 YES NO 11-MAR-10
10468 2 YES YES 14-MAR-10
10468 2 YES NO 14-MAR-10
10469 2 YES NO 14-MAR-10
10477 2 YES NO 14-MAR-10

6 rows selected.

SQL>
-----------------------------------------------------------------

can any one help me please to solve this problem;

i restarted the standby database many times and tried with

Alter database recover managed standby database;

it doesn't work;

look at these too
---------------------------------------------------
1- primary
SQL> select max(sequence#) from v$archived_log where applied='YES' group by thread#;

MAX(SEQUENCE#)
--------------
10466

SQL>archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /pcard15/oradata/pcard/arch
Oldest online log sequence 10478
Next log sequence to archive 10487
Current log sequence 10487

----------------------------------
2- Standby
SQL>select max(sequence#) from v$archived_log where applied='YES' group by thread#;

MAX(SEQUENCE#)
--------------
10468

SQL>archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /pcard15/oradata/pcard/arch
Oldest online log sequence 10472
Next log sequence to archive 0
Current log sequence 10487

-----------------------------------------

can anyone help me please i'm afraid to shutdown the primary database caus it may not start again.

[Nett]

i need your help please .. does anyone have any suggestions

[vnktummala]

hope you have open SR to oracle while waiting for some help in the forum.

Thanks,

[Nett]

the primary is working fine but i need at least steps to recreate it again

[vnktummala]

please consult your DBA who built the existing primary and standby sites. If you want to try your self there are bunches of documents available on the net. Google it and update the forum if you didn't find any thing.

Thanks,