Home » Server Options » Data Guard » Next log sequence to archive shows 0 (11g, 11.2.0.1, windows 7)
Next log sequence to archive shows 0 [message #617439] Mon, 30 June 2014 03:57 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

I have created physical standby database. After duplicate command, switchover_status for the standby database was showing "TO_PRIMARY'. When executed the script
Finished Duplicate Db at 30-JUN-14

RMAN> exit


Recovery Manager complete.

E:\app\product\112~1.0\dbhome_1\database>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 30 12:43:36 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@secdb> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SYS@secdb> select open_mode, switchover_status from v$database;

OPEN_MODE            SWITCHOVER_STATUS
-------------------- --------------------
MOUNTED              TO PRIMARY

SYS@secdb> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\app\product\11.2.0\arch\secdb
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SYS@secdb> select * from v$logfile;

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
         3         ONLINE
E:\APP\ORADATA\SECDB\REDO03.LOG
NO

         2         ONLINE
E:\APP\ORADATA\SECDB\REDO02.LOG
NO

    GROUP# STATUS  TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---

         1         ONLINE
E:\APP\ORADATA\SECDB\REDO01.LOG
NO


SYS@secdb> alter database add standby logfile group 4 ('E:\APP\ORADATA\SECDB\REDO04.log') size 50M;

Database altered.

SYS@secdb> alter database add standby logfile group 5 ('E:\APP\ORADATA\SECDB\REDO05.log') size 50M;

Database altered.

SYS@secdb> alter database add standby logfile group 6 ('E:\APP\ORADATA\SECDB\REDO06.log') size 50M;

Database altered.

SYS@secdb> alter database add standby logfile group 7 ('E:\APP\ORADATA\SECDB\REDO07.log') size 50M;

Database altered.

SYS@secdb> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SYS@secdb> ARCHIVE LOG LIST;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\app\product\11.2.0\arch\secdb
Oldest online log sequence     9
Next log sequence to archive   0
Current log sequence           11
SYS@secdb> select open_mode, switchover_status from v$database;

OPEN_MODE            SWITCHOVER_STATUS
-------------------- --------------------
MOUNTED              NOT ALLOWED


Below are the primary pfile -

E:\app\product\112~1.0\dbhome_1\database>type INITprimdb.ORA
primdb.__db_cache_size=511705088
primdb.__java_pool_size=4194304
primdb.__large_pool_size=4194304
primdb.__oracle_base='E:\app'#ORACLE_BASE set from environment
primdb.__pga_aggregate_target=243269632
primdb.__sga_target=725614592
primdb.__shared_io_pool_size=0
primdb.__shared_pool_size=197132288
primdb.__streams_pool_size=0
*.audit_file_dest='E:\app\admin\primdb\adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='E:\app\oradata\primdb\control01.ctl','E:\app\oradata\primdb\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primdb'
*.db_unique_name='primdb'
*.diagnostic_dest='E:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primdbXDB)'
*.fal_server='SECDB'
*.log_archive_config='DG_CONFIG=(primdb,secdb)'
*.log_archive_dest_1='location=E:\app\product\11.2.0\arch\PRIMDB VALID_FOR=(all_LOGFILES,ALL_ROLES) DB_UNIQUE_
NAME=PRIMDB'
*.log_archive_dest_2='service=secdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=secdb'
*.log_archive_format='Arc_%d_%s_%t_%r.arc'
*.nls_date_format='dd/mm/yyyy'
*.open_cursors=300
*.pga_aggregate_target=241172480
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=723517440
*.undo_tablespace='UNDOTBS1'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='E:\app\oradata\secdb', 'E:\app\oradata\primdb'
*.DB_FILE_NAME_CONVERT='E:\app\oradata\secdb', 'E:\app\oradata\primdb'
*.job_queue_processes=10
*.nls_date_format='dd/MM/yyyy'
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.core_dump_dest='E:\app\diag\rdbms\secdb\secdb'


Belo is the pfile of standby -

E:\app\product\112~1.0\dbhome_1\database>type INITsecdb.ORA
secdb.__db_cache_size=511705088
secdb.__java_pool_size=4194304
secdb.__large_pool_size=4194304
secdb.__oracle_base='E:\app'#ORACLE_BASE set from environment
secdb.__pga_aggregate_target=243269632
secdb.__sga_target=725614592
secdb.__shared_io_pool_size=0
secdb.__shared_pool_size=197132288
secdb.__streams_pool_size=0
*.audit_file_dest='E:\app\admin\secdb\adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='E:\app\oradata\secdb\control01.ctl','E:\app\oradata\secdb\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='primdb'
*.db_unique_name='secdb'
*.diagnostic_dest='E:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=secdbXDB)'
*.fal_server='PRIMDB'
*.log_archive_config='DG_CONFIG=(primdb,secdb)'
*.log_archive_dest_1='location=E:\app\product\11.2.0\arch\secdb VALID_FOR=(all_LOGFILES,ALL_ROLES) DB_UNIQUE_N
AME=secdb'
*.log_archive_dest_2='service=primdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primdb
'
*.log_archive_format='Arc_%d_%s_%t_%r.arc'
*.nls_date_format='dd/mm/yyyy'
*.open_cursors=300
*.pga_aggregate_target=241172480
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=723517440
*.undo_tablespace='UNDOTBS1'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_FILE_NAME_CONVERT='E:\app\oradata\primdb', 'E:\app\oradata\secdb'
*.DB_FILE_NAME_CONVERT='E:\app\oradata\primdb', 'E:\app\oradata\secdb'
*.job_queue_processes=10
*.nls_date_format='dd/MM/yyyy'
*.remote_login_passwordfile='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.core_dump_dest='E:\app\diag\rdbms\secdb\secdb\cdump'


Please assist to know the exact problem, why switchover_status at standby shows "NOT_ALLOWED" ?

Waiting for your kind response...

Regards,
Ashish Kumar Mahanta
Re: Next log sequence to archive shows 0 [message #617443 is a reply to message #617439] Mon, 30 June 2014 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67294
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
why switchover_status at standby shows "NOT_ALLOWED" ?


What is the relation between this question and the title?

From the documentation on V$DATABASE:
Quote:
NOT ALLOWED - On a primary database, this status indicates that there are no valid and enabled standby databases. On a standby database, this status indicates that a switchover request has not been received from the primary database.


So this is the expected and normal status in normal days of the life of a standby database.

Re: Next log sequence to archive shows 0 [message #617446 is a reply to message #617443] Mon, 30 June 2014 04:37 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

Please let me know whats wrong at standby side? Why it is not receiving switchover request from primary?

Is there any parameters wrongly assigned at standby side?



Regards,
Ashish Kumar Mahanta
Re: Next log sequence to archive shows 0 [message #617453 is a reply to message #617446] Mon, 30 June 2014 05:13 Go to previous messageGo to next message
John Watson
Messages: 8319
Registered: January 2010
Location: Global Village
Senior Member
Switchover is of no relevance at this stage. Have you started the propagation and apply of redo? Run this at both sites:
select process,status,sequence#,block# from v$managed_standby;

[Updated on: Mon, 30 June 2014 05:13]

Report message to a moderator

Re: Next log sequence to archive shows 0 [message #617458 is a reply to message #617453] Mon, 30 June 2014 06:15 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear John,

Standby side -

SYS@secdb> select process,status,sequence#,block# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CLOSING              14          1
ARCH      CLOSING              16          1
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0
RFS       IDLE                  0          0

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
RFS       IDLE                  0          0
RFS       IDLE                 17         79
MRP0      APPLYING_LOG         17         78



primary side

SYS@primdb> select process,status,sequence#,block# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CLOSING              15          1
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CLOSING              14          1
ARCH      CLOSING              15          1
ARCH      CLOSING              16          1
ARCH      CLOSING              16          1
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
ARCH      CONNECTED             0          0
LNS       WRITING              17        188

31 rows selected.


Please help me out from this problem.

Regards,

Ashish Kumar Mahanta
Re: Next log sequence to archive shows 0 [message #617460 is a reply to message #617458] Mon, 30 June 2014 06:33 Go to previous messageGo to next message
John Watson
Messages: 8319
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Please help me out from this problem.
You do not have a problem (not a technical problem, anyway). Data Guard is working fine.


Re: Next log sequence to archive shows 0 [message #617516 is a reply to message #617460] Tue, 01 July 2014 04:09 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear John,

When i try to switch to primary, it asked for media recovery. Below are the steps -

SYS@secdb> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED


SYS@secdb> alter database recover managed standby database cancel;

Database altered.

SYS@secdb> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required



Waiting for your kind response...
Re: Next log sequence to archive shows 0 [message #617517 is a reply to message #617516] Tue, 01 July 2014 04:18 Go to previous message
Michel Cadot
Messages: 67294
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-16139: media recovery required
 *Cause:  An error has occurred while performing a switchover.
 *Action: If you were switching a primary database to the standby role, the
          conversion was successful, but one or more errors occurred after
          the conversion completed. See the alert log for more information.
          You can proceed with switching the target standby database to the
          primary role, but you must address the errors reported at the old
          primary database before it can protect the new primary database.

          If you encountered this error while switching a physical standby
          database to the primary role, the switchover did not succeed
          because additional redo must be applied. Verify that redo apply is
          active and wait until the SWITCHOVER_STATUS column of the
          V$DATABASE dynamic performance view reports TO_PRIMARY or
          SESSIONS_ACTIVE before retrying the switchover.
Previous Topic: commit mode and dataguard
Next Topic: RFS LogMiner: Registered logfile to LogMiner session id
Goto Forum:
  


Current Time: Mon Aug 10 02:59:52 CDT 2020