Oracle Database Discussions

Hi,

I am starting to administer this database which was already setup by the previous dba. It is a 10g Primary setup for a Physical standby using Data Guard.

The problem is that the logs are not being shipped and applied correctly. Here is the overview of the entire setup, please let me know what looks wrong here...


Primary Configuration:
---------------------
initSRPROD.ora
--------------

*.audit_file_dest='/apps/oracle/admin/srprod/adump'
*.background_dump_dest='/apps/oracle/admin/srprod/bdump'
*.compatible='10.2.0.3.0'
*.core_dump_dest='/apps/oracle/admin/srprod/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOLOG'
*.db_file_multiblock_read_count=16
*.db_name='srprod'
*.db_recovery_file_dest='+ARCHIVE'
*.db_recovery_file_dest_size=2147483648
*.db_file_name_convert='+DATA/srprodb/','+DATA/srprod/','+ARCHIVE/srprodb','+ARCHIVE/srprod'
*.db_unique_name=srprod
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srprodbXDB)'
*.fal_server='srprodb'
*.fal_client='srprod'
*.instance_name=srprod
*.job_queue_processes=10
*.log_archive_config='dg_config=(srprodb,srprod)'
*.log_archive_dest_1='LOCATION=+ARCHIVE/srprod/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=srprod'
*.log_archive_dest_2='service=srprodb valid_for=(online_logfiles,primary_role) db_unique_name=srprodb'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.ARC'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='+DATA/srprod/','+DATA/srprodb/','+ARCHIVE/srprod','+ARCHIVE/srprodb'
*.open_cursors=300
*.pga_aggregate_target=1669332992
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_max_size=12884901888
*.sga_target=9663676416
*.standby_file_management=auto
*.service_names='srprod'
*.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/apps/oracle/admin/srprod/udump'

listener.ora
------------
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/DB102)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb101)(PORT = 1521))
)
)


tnsnames.ora
------------
## Standby
SRPRODB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srprodb)
(UR = A)
)
)

## Primary
SRPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb101)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srprod)
)
)



-----------------------------------------------------------------------------------------------------------
Standby Configuration:
----------------------
initSRPRODB.ora

*.audit_file_dest='/apps/oracle/admin/srprodb/adump'
*.background_dump_dest='/apps/oracle/admin/srprodb/bdump'
*.compatible='10.2.0.3.0'
*.core_dump_dest='/apps/oracle/admin/srprodb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOLOG'
*.db_file_multiblock_read_count=16
*.db_name='srprod'
*.db_recovery_file_dest='+ARCHIVE'
*.db_recovery_file_dest_size=2147483648
*.db_file_name_convert='+DATA/srprod/','+DATA/srprodb/','+ARCHIVE/srprod','+ARCHIVE/srprodb'
*.db_unique_name=srprodb
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srprodbXDB)'
*.fal_server='srprod'
*.fal_client='srprodb'
*.instance_name=srprodb
*.job_queue_processes=10
*.log_archive_config='dg_config=(srprodb,srprod)'
*.log_archive_dest_1='LOCATION=+ARCHIVE/srprodb/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=srprodb'
*.log_archive_dest_2='service=srprod valid_for=(online_logfiles,primary_role) db_unique_name=srprod'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='%t_%s_%r.ARC'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='+DATA/srprod/','+DATA/srprodb/','+ARCHIVE/srprod','+ARCHIVE/srprodb'
*.open_cursors=300
*.pga_aggregate_target=1669332992
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_max_size=12884901888
*.sga_target=9663676416
*.standby_file_management=auto
*.service_names='srprodb'
*.thread=1
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/apps/oracle/admin/srprodb/udump'

listener.ora
------------

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/DB102)
(PROGRAM = extproc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb102)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

tnsnames.ora
------------
## Standby
SRPRODB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srprodb)
(UR = A)
)
)

## Primary
SRPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = tenprdsrdb101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srprod)
)
)

-----------------------------------------------------------------------------------------------------------
Monitoring the Physical Standby Database:
-----------------------------------------

On Primary
----------
SQL> SELECT max(sequence#) FROM V$ARCHIVED_LOG where ARCHIVED = 'YES';

MAX(SEQUENCE#)
--------------
4150


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCHIVE/srprod/
Oldest online log sequence 4149
Next log sequence to archive 4151
Current log sequence 4151

alter_srprod.log
----------------

Sun Jul 27 22:24:51 2008
Thread 1 advanced to log sequence 4150
Current log# 3 seq# 4150 mem# 0: +REDOLOG/srprod/onlinelog/group_3.261.660754259
Current log# 3 seq# 4150 mem# 1: +REDOLOG/srprod/onlinelog/group_3.260.660754261
Sun Jul 27 22:24:51 2008
Errors in file /apps/oracle/admin/srprod/bdump/srprod_arch_18301.trc:
ORA-00254: error in archive control string ''
Sun Jul 27 22:24:51 2008
FAL[server, ARCh]: FAL archive failed, see trace file.
Sun Jul 27 22:24:51 2008
Errors in file /apps/oracle/admin/srprod/bdump/srprod_arch_18301.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Sun Jul 27 22:24:51 2008
ORACLE Instance srprod - Archival Error. Archiver continuing.
Mon Jul 28 01:38:24 2008
Starting control autobackup
Mon Jul 28 01:39:09 2008
Control autobackup written to SBT_TAPE device
comment 'API Version 2.0,MMS Version 5.0.0.0',
media '0167L3'
handle 'c-4266459051-20080728-00'
LNS1 started with pid=45, OS id=27134
Mon Jul 28 01:39:22 2008
Thread 1 advanced to log sequence 4151
Current log# 1 seq# 4151 mem# 0: +REDOLOG/srprod/onlinelog/group_1.259.660751999
Current log# 1 seq# 4151 mem# 1: +REDOLOG/srprod/onlinelog/group_1.257.660752009
Mon Jul 28 01:39:23 2008
Errors in file /apps/oracle/admin/srprod/bdump/srprod_lns1_27134.trc:
ORA-00254: error in archive control string ''
Mon Jul 28 01:39:23 2008
LNS: Failed to archive log 1 thread 1 sequence 4151 (254)
Mon Jul 28 01:40:08 2008
Errors in file /apps/oracle/admin/srprod/bdump/srprod_arcj_18329.trc:
ORA-00254: error in archive control string ''
Mon Jul 28 01:40:08 2008
FAL[server, ARCj]: FAL archive failed, see trace file.
Mon Jul 28 01:40:08 2008
Errors in file /apps/oracle/admin/srprod/bdump/srprod_arcj_18329.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Mon Jul 28 01:40:08 2008
ORACLE Instance srprod - Archival Error. Archiver continuing.
Mon Jul 28 01:42:27 2008
Starting control autobackup
Mon Jul 28 01:45:04 2008
Control autobackup written to SBT_TAPE device
comment 'API Version 2.0,MMS Version 5.0.0.0',
media '0167L3'
handle 'c-4266459051-20080728-01'


/apps/oracle/admin/srprod/bdump/srprod_arcj_18329.trc
------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /apps/oracle/product/DB102
System name: Linux
Node name: tenprdsrdb101
Release: 2.6.9-42.ELsmp
Version: #1 SMP Wed Jul 12 23:32:02 EDT 2006
Machine: x86_64
Instance name: srprod
Redo thread mounted by this instance: 1
Oracle process number: 241
Unix process pid: 18329, image: oracle@tenprdsrdb101 (ARCj)

Detected dead process 30340; subsuming V$MANAGED_STANDBY slot
*** 2008-07-28 01:40:08.019
*** SERVICE NAME:(SYS$BACKGROUND) 2008-07-28 01:40:08.019
*** SESSION ID:(733.183) 2008-07-28 01:40:08.019
FAL[server]: Selected inactive destination; resetting temporarily
Redo shipping client performing standby login
*** 2008-07-28 01:40:08.051 65194 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 254 creating standby archive log file at host 'srprodb'
*** 2008-07-28 01:40:08.071 61287 kcrr.c
ARCj: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (254)
*** 2008-07-28 01:40:08.071 61287 kcrr.c
ARCj: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
ORA-00254: error in archive control string ''
*** 2008-07-28 01:40:08.071 59530 kcrr.c
kcrrfail: dest:2 err:254 force:0 blast:1
kcrrwkx: unknown error:254
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...


On Standby
----------

SQL> SELECT max(sequence#) FROM V$ARCHIVED_LOG where ARCHIVED = 'YES';

MAX(SEQUENCE#)
--------------
4098


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCHIVE/srprodb/
Oldest online log sequence 4146
Next log sequence to archive 0
Current log sequence 4151