| Next log sequence to archive shows 0 [message #617439] |
Mon, 30 June 2014 03:57  |
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 #617458 is a reply to message #617453] |
Mon, 30 June 2014 06:15  |
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 #617516 is a reply to message #617460] |
Tue, 01 July 2014 04:09 |
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 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
