Home » Server Options » Data Guard » Sequence of archive log list show zero at physical standby (11g, 11.2.0.1, window7)
Sequence of archive log list show zero at physical standby [message #634873] Tue, 17 March 2015 05:45 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

My primary database name is - Tagitest
Standby name is - Ratultst


Sequence number at standby is showing zero after execution of duplicate command. Below is my duplicate command execution steps -

D:\app\product\11.2.0\db_1\database>rman target sys/oracle@tagitest auxiliary sys/oracle@ratultst

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 17 15:28:22 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TAGITEST (DBID=2799061182)
connected to auxiliary database: TAGITEST (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 17-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=160 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  'D:\app\product\11.2.0\db_1\DATABASE\PWDtagitest.ORA' auxiliary format
 'D:\app\product\11.2.0\db_1\DATABASE\PWDratultst.ORA'   ;
}
executing Memory Script

Starting backup at 17-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
Finished backup at 17-MAR-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  'D:\APP\ORADATA\RATULTST\CONTROL01.CTL';
   restore clone controlfile to  'D:\APP\ORADATA\RATULTST\CONTROL02.CTL' from
 'D:\APP\ORADATA\RATULTST\CONTROL01.CTL';
}
executing Memory Script

Starting backup at 17-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=D:\APP\PRODUCT\11.2.0\DB_1\DATABASE\SNCFTAGITEST.ORA tag=TAG20150317T152832 RECID=4 STAMP=87459651
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 17-MAR-15

Starting restore at 17-MAR-15
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-MAR-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "D:\APP\ORADATA\RATULTST\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
 "D:\APP\ORADATA\RATULTST\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\APP\ORADATA\RATULTST\SYSAUX01.DBF";
   set newname for datafile  3 to
 "D:\APP\ORADATA\RATULTST\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "D:\APP\ORADATA\RATULTST\USERS01.DBF";
   backup as copy reuse
   datafile  1 auxiliary format
 "D:\APP\ORADATA\RATULTST\SYSTEM01.DBF"   datafile
 2 auxiliary format
 "D:\APP\ORADATA\RATULTST\SYSAUX01.DBF"   datafile
 3 auxiliary format
 "D:\APP\ORADATA\RATULTST\UNDOTBS01.DBF"   datafile
 4 auxiliary format
 "D:\APP\ORADATA\RATULTST\USERS01.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\APP\ORADATA\RATULTST\TEMP01.DBF in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 17-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\ORADATA\TAGITEST\SYSTEM01.DBF
output file name=D:\APP\ORADATA\RATULTST\SYSTEM01.DBF tag=TAG20150317T152843
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\APP\ORADATA\TAGITEST\SYSAUX01.DBF
output file name=D:\APP\ORADATA\RATULTST\SYSAUX01.DBF tag=TAG20150317T152843
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\APP\ORADATA\TAGITEST\UNDOTBS01.DBF
output file name=D:\APP\ORADATA\RATULTST\UNDOTBS01.DBF tag=TAG20150317T152843
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\APP\ORADATA\TAGITEST\USERS01.DBF
output file name=D:\APP\ORADATA\RATULTST\USERS01.DBF tag=TAG20150317T152843
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=874596654 file name=D:\APP\ORADATA\RATULTST\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=874596654 file name=D:\APP\ORADATA\RATULTST\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=874596655 file name=D:\APP\ORADATA\RATULTST\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=874596655 file name=D:\APP\ORADATA\RATULTST\USERS01.DBF
Finished Duplicate Db at 17-MAR-15

RMAN> exit


Recovery Manager complete.

D:\app\product\11.2.0\db_1\database>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 17 15:35:34 2015

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


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

SQL> alter database add standby logfile group 4 ('D:\app\oradata\RATULTST\stndby04.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 5 ('D:\app\oradata\RATULTST\stndby05.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 6 ('D:\app\oradata\RATULTST\stndby06.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 7 ('D:\app\oradata\RATULTST\stndby07.log') size 50m;

Database altered.


SQL> select * from v$standby_log;

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
     BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- ------------- ---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------
         4 UNASSIGNED                                        0          0
  52428800        512        512 YES UNASSIGNED             0
           0                      0

         5 UNASSIGNED                                        0          0
  52428800        512        512 YES UNASSIGNED             0
           0                      0

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
     BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- ------------- ---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------

         6 UNASSIGNED                                        0          0
  52428800        512        512 YES UNASSIGNED             0
           0                      0

         7 UNASSIGNED                                        0          0
  52428800        512        512 YES UNASSIGNED             0

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
     BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- ------------- ---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------
           0                      0


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\archive\RATULTST
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           0
SQL>



Pfile of primary database => TAGITEST
tagitest.__db_cache_size=666894336
tagitest.__java_pool_size=4194304
tagitest.__large_pool_size=4194304
tagitest.__oracle_base='D:\app'#ORACLE_BASE set from environment
tagitest.__pga_aggregate_target=310378496
tagitest.__sga_target=931135488
tagitest.__shared_io_pool_size=0
tagitest.__shared_pool_size=243269632
tagitest.__streams_pool_size=0
*.audit_file_dest='D:\app\admin\tagitest\adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oradata\tagitest\control01.ctl','D:\app\oradata\tagitest\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.DB_FILE_NAME_CONVERT='D:\app\oradata\RATULTST','D:\app\oradata\tagitest'
*.db_name='TAGITEST'
*.db_unique_name='TAGITEST'
*.diagnostic_dest='D:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tagitestXDB)'
*.FAL_SERVER='RATULTST'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(TAGITEST,RATULTST)'
*.log_archive_dest_1='location=D:\app\archive\tagitest VALID_FOR=(all_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=TAGITEST'
*.LOG_ARCHIVE_DEST_2='SERVICE=RATULTST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RATULTST'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='Arc_%d_%s_%t_%r'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='D:\app\oradata\RATULTST','D:\app\oradata\tagitest'
*.nls_date_format='dd/mm/yyyy'
*.open_cursors=300
*.pga_aggregate_target=309329920
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=929038336
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'


pfile of standby

ratultst.__db_cache_size=486539264
ratultst.__java_pool_size=16777216
ratultst.__large_pool_size=16777216
ratultst.__oracle_base='D:\app'#ORACLE_BASE set from environment
ratultst.__pga_aggregate_target=503316480
ratultst.__sga_target=738197504
ratultst.__shared_io_pool_size=0
ratultst.__shared_pool_size=201326592
ratultst.__streams_pool_size=0
*.audit_file_dest='D:\app\admin\RATULTST\adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\oradata\RATULTST\control01.ctl','D:\app\oradata\RATULTST\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.diagnostic_dest='D:\app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RATULTSTXDB)'
*.memory_target=1239416832
*.nls_date_format='dd/mm/yyyy'
*.open_cursors=1500
*.processes=400
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
###############################################################################
*.DB_FILE_NAME_CONVERT='D:\app\oradata\tagitest','D:\app\oradata\RATULTST'
*.db_name='TAGITEST'
*.db_unique_name='RATULTST'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(TAGITEST,RATULTST)'
*.log_archive_dest_1='location=D:\app\archive\RATULTST VALID_FOR=(all_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RATULTST'
*.LOG_ARCHIVE_DEST_2='SERVICE=TAGITEST LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TAGITEST'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='Arc_%d_%s_%t_%r'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_FILE_NAME_CONVERT='D:\app\oradata\tagitest','D:\app\oradata\RATULTST'
*.nls_date_format='dd/mm/yyyy'
*.sec_case_sensitive_logon=FALSE
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.FAL_SERVER='TAGITEST'


Archive log list of Primary database

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\archive\tagitest
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence           13


Please assist, why "archive log list" is not getting update at standby even after switching the log file at primary?

Regards,
Ashish Kumar Mahanta
Re: Sequence of archive log list show zero at physical standby [message #634875 is a reply to message #634873] Tue, 17 March 2015 05:57 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
alert log at primary database => TAGITEST

	
Alert log at Primary database-

Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: D:\APP\ORADATA\TAGITEST\REDO01.LOG
Archived Log entry 11 added for thread 1 sequence 12 ID 0xa6d711be dest 1:
Tue Mar 17 15:35:40 2015
Error 3113 received logging on to the standby
Errors in file d:\app\diag\rdbms\tagitest\tagitest\trace\tagitest_arc1_5144.trc:
ORA-03113: end-of-file on communication channel
PING[ARC1]: Heartbeat failed to connect to standby 'RATULTST'. Error is 3113.
Tue Mar 17 15:40:42 2015
Error 3113 received logging on to the standby
Errors in file d:\app\diag\rdbms\tagitest\tagitest\trace\tagitest_arc1_5144.trc:
ORA-03113: end-of-file on communication channel
PING[ARC1]: Heartbeat failed to connect to standby 'RATULTST'. Error is 3113.
Tue Mar 17 15:46:44 2015
Error 3113 received logging on to the standby
Errors in file d:\app\diag\rdbms\tagitest\tagitest\trace\tagitest_arc1_5144.trc:
ORA-03113: end-of-file on communication channel
PING[ARC1]: Heartbeat failed to connect to standby 'RATULTST'. Error is 3113.



Alert log at standby database-
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x7FFFFFFF8] [PC:0xA311986, 000000000A311986]
Errors in file d:\app\diag\rdbms\ratultst\ratultst\trace\ratultst_ora_4128.trc (incident=35414):
ORA-07445: exception encountered: core dump [PC:0xA311986] [ACCESS_VIOLATION] [ADDR:0x7FFFFFFF8] [PC:0xA311986] [UNABLE_TO_READ] []
Tue Mar 17 15:52:44 2015
Errors in file d:\app\diag\rdbms\ratultst\ratultst\cdump\ratultstcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [000000000A311986] [0x000000000A311986]
Tue Mar 17 15:52:45 2015
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x7FFFFFFF8] [PC:0xA311986, 000000000A311986]
Errors in file d:\app\diag\rdbms\ratultst\ratultst\trace\ratultst_ora_3824.trc (incident=35422):
ORA-07445: exception encountered: core dump [PC:0xA311986] [ACCESS_VIOLATION] [ADDR:0x7FFFFFFF8] [PC:0xA311986] [UNABLE_TO_READ] []
Tue Mar 17 15:52:45 2015
Errors in file d:\app\diag\rdbms\ratultst\ratultst\cdump\ratultstcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [000000000A311986] [0x000000000A311986]
Tue Mar 17 15:52:46 2015
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x7FFFFFFF8] [PC:0xA311986, 000000000A311986]
Errors in file d:\app\diag\rdbms\ratultst\ratultst\trace\ratultst_ora_4296.trc (incident=35677):
ORA-07445: exception encountered: core dump [PC:0xA311986] [ACCESS_VIOLATION] [ADDR:0x7FFFFFFF8] [PC:0xA311986] [UNABLE_TO_READ] []
Tue Mar 17 15:52:46 2015
Errors in file d:\app\diag\rdbms\ratultst\ratultst\cdump\ratultstcore.log
ORA-07445: caught exception [ACCESS_VIOLATION] at [000000000A311986] [0x000000000A311986]
Tue Mar 17 15:52:46 2015
Sweep [inc][35422]: completed
Sweep [inc][35414]: completed
Tue Mar 17 15:53:46 2015
Sweep [inc][35677]: completed
Re: Sequence of archive log list show zero at physical standby [message #634877 is a reply to message #634875] Tue, 17 March 2015 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink/MOS and/or call Oracle support
Have a look at alert.log and trace files.
You can also read this article: Troubleshooting Internal Errors.
Re: Sequence of archive log list show zero at physical standby [message #634880 is a reply to message #634877] Tue, 17 March 2015 07:11 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

Trying to get suggestion from MOS. Purpose to log a call is to know the suggestions from you end also.

Thanks,
Re: Sequence of archive log list show zero at physical standby [message #634881 is a reply to message #634880] Tue, 17 March 2015 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Unfortunately to debug such error (ACCESS_VIOLATION) you have to have access to Oracle code which is only possible for Oracle developer.

Can you post the following files, maybe we can see some clues in them:
d:\app\diag\rdbms\tagitest\tagitest\trace\tagitest_arc1_5144.trc
d:\app\diag\rdbms\ratultst\ratultst\trace\ratultst_ora_4128.trc

What kind of file is "d:\app\diag\rdbms\ratultst\ratultst\cdump\ratultstcore.log"?

Re: Sequence of archive log list show zero at physical standby [message #634923 is a reply to message #634881] Wed, 18 March 2015 02:13 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michecl,

Please find the attached requested files.
Re: Sequence of archive log list show zero at physical standby [message #634924 is a reply to message #634923] Wed, 18 March 2015 02:14 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
another requested file

[Updated on: Wed, 18 March 2015 02:14]

Report message to a moderator

Re: Sequence of archive log list show zero at physical standby [message #634925 is a reply to message #634924] Wed, 18 March 2015 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The first file may indicate your standby server, as defined in your log_archive_dest entry, is unreachable for the primary one.
Post the result of "tnsping TAGITEST".
Have you any firewall on these servers?

For the second one I need you answer my question "What kind of file is "d:\app\diag\rdbms\ratultst\ratultst\cdump\ratultstcore.log"?"
Can you read it?

Re: Sequence of archive log list show zero at physical standby [message #634939 is a reply to message #634925] Wed, 18 March 2015 03:52 Go to previous message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

D:\app\product\11.2.0\db_1\database>tnsping tagitest

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-MAR-2015 14:18:51

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

Used parameter files:
D:\app\product\11.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.39.71)(PORT =
1521))) (CONNECT_DATA = (SERVICE_NAME = TAGITEST) (UR=A)))
OK (10 msec)

D:\app\product\11.2.0\db_1\database>tnsping ratultst

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-MAR-2015 14:18:57

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

Used parameter files:
D:\app\product\11.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.39.52)(PORT =
1521))) (CONNECT_DATA = (SERVICE_NAME = RATULTST) (UR=A)))
OK (0 msec)

D:\app\product\11.2.0\db_1\database>tnsping tagitest

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 18-MAR-2015 14:19:03

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

Used parameter files:
D:\app\product\11.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.39.71)(PORT =
1521))) (CONNECT_DATA = (SERVICE_NAME = TAGITEST) (UR=A)))
OK (0 msec)



ratultstcore.log file is too big. Unable to post it here. Tried several times. Its readable only.

Firewall is off on both sides.

[Updated on: Wed, 18 March 2015 03:59]

Report message to a moderator

Previous Topic: Logical dataguard - No-logging in force logging
Next Topic: data guard is very slow.why?
Goto Forum:
  


Current Time: Sat Nov 18 13:38:09 CST 2017

Total time taken to generate the page: 0.00939 seconds