Oracle in World: 9/19/10

Friday, September 24, 2010

ORA-16086: Redo data cannot be written to the standby redo log

Problem Description
Oracle physical standby database is not able to synchronize with the primary database. From the standby database alert log we see the following logs.

Thu Sep 23 14:57:47 2010
RFS[2237]: Assigned to RFS process 8174
RFS[2237]: Identified database type as 'physical standby': Client is ARCH pid 25089
RFS[2236]: Opened log for thread 2 sequence 1532 dbid 1323895516 branch 728508318
RFS[2237]: Opened log for thread 2 sequence 1533 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:32 2010
RFS[2226]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_1_seq_2044.9918.730479031
Thu Sep 23 14:58:33 2010
RFS[2176]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_2_seq_1466.9896.730477033
Thu Sep 23 14:58:35 2010
RFS[2168]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_2_seq_1545.9817.730476973
Thu Sep 23 14:58:40 2010
RFS[2238]: Assigned to RFS process 8319
RFS[2238]: Identified database type as 'physical standby': Client is ARCH pid 2094
RFS[2238]: Opened log for thread 2 sequence 1466 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:41 2010
RFS[2239]: Assigned to RFS process 8321
RFS[2239]: Identified database type as 'physical standby': Client is ARCH pid 25230
Thu Sep 23 14:58:43 2010
RFS[2240]: Assigned to RFS process 8323
RFS[2240]: Identified database type as 'physical standby': Client is ARCH pid 31899
RFS[2240]: Opened log for thread 1 sequence 2044 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:51 2010
RFS[2241]: Assigned to RFS process 8334
RFS[2241]: Identified database type as 'physical standby': Client is LGWR SYNC pid 22086
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Standby controlfile consistent with primary
RFS[2241]: No standby redo logfiles of size 102400 blocks available
RFS[2241]: No standby redo logfiles selected (reason:7)
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc:
ORA-16086: Redo data cannot be written to the standby redo log
Thu Sep 23 14:58:59 2010
RFS[2242]: Assigned to RFS process 8341
RFS[2242]: Identified database type as 'physical standby': Client is LGWR SYNC pid 6586
Thu Sep 23 14:58:59 2010
RFS[2178]: Possible network disconnect with primary database

From the trace file it is logged following entry.

[oracle@DRS-DB-01 ~]$ cat /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc
Trace file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: DRS-DB-01
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine: x86_64
Instance name: bdafisdc1
Redo thread mounted by this instance: 1
Oracle process number: 124
Unix process pid: 8334, image: oracle@DRS-DB-01

*** 2010-09-23 14:58:52.578
*** SESSION ID:(88.91) 2010-09-23 14:58:52.578
*** CLIENT ID:() 2010-09-23 14:58:52.578
*** SERVICE NAME:() 2010-09-23 14:58:52.578
*** MODULE NAME:(oracle@DC-DB-01 (TNS V1-V3)) 2010-09-23 14:58:52.578
*** ACTION NAME:() 2010-09-23 14:58:52.578

The primary database is operating in MAXIMUM PROTECTION
or MAXIMUM AVAILABILITY mode, and the standby database does
not contain any viable standby redo logfiles.
ORA-16086: Redo data cannot be written to the standby redo log

Cause of the Problem
The problem happened due to recovery area of standby database becomes full and standby redo logs have no place for archival. As the standby redo logs can't be archived, so they can't free space in order to accommodate/write new logs.

Solution of the Problem
Increase the recovery area size of standby database. You can check the location of archived redo log file by,
SQL> connect / as sysdba
SQL> archive log list

If you see that archive destination is USE_DB_RECOVERY_FILE_DEST then the location will be specified by parameter DB_RECOVERY_FILE_DEST.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
+RECOVERY

Note that you have sufficient storage under +RECOVERY and also you have set proper size of the parameter db_recovery_file_dest_size.

To check the size of parameter db_recovery_file_dest_size issue,
SQL> show parameter db_recovery_file_dest_size

In order to increase the size of db_recovery_file_dest_size parameter issue,
SQL> alter system set db_recovery_file_dest_size = 1000G scope=both sid='*';

Wednesday, September 22, 2010

New Features in Oracle Data Guard Broker 11g

As 11g has two releases 11.2g and 11.1g so I will include new features into two sections.
New features in Oracle Data Guard Broker 11.1g
1. Fast start failover can be performed from a Data Guard broker if the database configuration is operating in maximum performance mode.

2. The new DBMS_DG PL/SQL package allows applications to notify the observer process to initiate a fast-start failover to the target standby database.

3. We can configure data guard broker to perform fast start failover based errors raised by the Oracle server.

4. After a failover, the observer automatically attempts to reinstate bystander standby databases.

5. Through DGMGRL CONVERT DATABASE command, you can convert physical standby database to a snapshot standby database, or reverts the snapshot standby database back to a physical standby database.

6. You can specify a connect identifier for Redo Transport Services as you want.

7. No need to shutdown database when changing the protection mode to and from maximum availability and maximum performance.

8. Support for Redo Apply on physical standby databases opened for read.

9. Allow a primary or logical standby database in a Data Guard Broker configuration to be mounted without having to be opened automatically.

10. The DGMGRL client ADD DATABASE command can import a pre-configured service attribute from a LOG_ARCHIVE_DEST_n parameter when adding a standby database to the configuration. So we can only issue "ADD DATABASE {DATABASE_NAME}" command instead of specifying any connect identifier.

11. A primary database can be in either the TRANSPORT-OFF or TRANSPORT-ON state. A standby database can be in either the APPLY-OFF or APPLY-ON state. In this way, database states become simpler.

12. In 11.1g the network bandwidth utilization in a Data Guard configuration is done efficiently by compressing archived redo logs as they are transmitted over the network to standby databases, when the log is sent to satisfy a gap at a standby database.

13. You can now specify USE_DB_RECOVERY_FILE_DEST as the value for the AlternateLocation and StandbyArchiveLocation instance-specific properties.

14. The following new Data Guard command-line interface (DGMGRL) commands are available:

CONVERT DATABASE TO SNAPSHOT STANDBY

CONVERT DATABASE TO PHYSICAL STANDBY

DISABLE FAST_START FAILOVER CONDITION

ENABLE FAST_START FAILOVER CONDITION

SHOW FAST_START FAILOVER

15. The following new database properties are available:

DGConnectIdentifier (replaces InitialConnectIdentifier)

ObserverConnectIdentifier

RedoCompression

16. The following new configuration properties are available:

FastStartFailoverAutoReinstate

FastStartFailoverLagLimit

FastStartFailoverPmyShutdown

17. The LsbyTxnConsistency database property has been renamed to LsbyPreserveCommitOrder.

18. New default of ASYNC for the LogXptMode database property.

New features in Oracle Data Guard Broker 11.2g
1. Now the broker configuration consist of 1 primary database and up to 30 standby databases.

2. The value of an instance-specific property can be changed for all instances in one command.

3. In 11.1g redo transport compression was limited to compressing redo data only when a redo gap is being resolved. In 11.2g when compression is enabled for a destination, all redo data sent to that destination is compressed.

4. Improved status and error reporting for the SHOW CONFIGURATION and SHOW DATABASE commands.

5. The SHOW DATABASE command displays the apply lag and transport lag of a standby database.

6. Shutting down all instances but one on a target physical standby for role changes is no longer required.

7. FAN/ONS (Fast Application Notification/Oracle Notification Services) notification is sent after a failover for databases configured with Oracle Clusterware and for single-instance databases registered with Oracle Restart.

8. FAN/AQ (Fast Application Notification/Advanced Queueing) notification is sent after a failover to a logical standby database.

9. Integration with Active Data Guard (automatic stop and restart of apply) when an apply instance is opened.

10. Reinstatement of the old (original) primary database after an immediate failover.

11. Improved hang detection and resolution for broker network communications. In addition, a new configuration property, CommunicationTimeout, enables the user to select an appropriate timeout value for their environment.

12. New database property, StaticConnectIdentifier, which allows the user to specify a static connect identifier that the DGMGRL client will use to start database instances.

13. Protection mode upgrades from maximum availability to maximum protection no longer require a restart of the primary database. This means that when upgrading from maximum performance mode to maximum protection mode, you can avoid a restart of the primary database by first upgrading to maximum availability. Once in maximum availability mode, you can then upgrade to maximum protection mode.

14. Data Recovery Adviser now uses Data Guard to suggest viable repair options for a primary database that cannot be repaired in a timely manner.

Sunday, September 19, 2010

Diagnosis Oracle Data Guard Primary Site Configuration

-- NAME: DG_Primary_Database_Diagnosis.sql
-- ------------------------------------------------------------------------
-- AUTHOR: Mohammad Abdul Momin Arju
-- September 2010.
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is used to troubleshoot Oracle Data Guard issues for
-- Data Guard Primary database.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only.
-- Test it before you run in your production database.
-- ------------------------------------------------------------------------
-- Content of the Script

set echo off 
set feedback off 
column timecol new_value timestamp 
column spool_extension new_value suffix 
select to_char(sysdate,'Mondd_hhmi') timecol, 
'.out' spool_extension from sys.dual; 
column output new_value dbname 
select value || '_' output 
from v$parameter where name = 'db_name'; 
spool dg_prim_diag_&&dbname&×tamp&&suffix 
set linesize 79
set pagesize 35 
set trim on 
set trims on 
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS'; 
set feedback on 
select to_char(sysdate) time from dual; 
 
set echo on

-- Select from v$database will give us the generic information about how this standby is
-- setup. The database_role can be SNAPSHOT STANDBY/LOGICAL STANDBY/PHYSICAL STANDBY/PRIMARY
-- It should be standby for standby database. PROTECTION_MODE can be,
-- MAXIMUM PROTECTION - Database is running in maximized protection mode
-- MAXIMUM AVAILABILITY - Database is running in maximized availability mode
-- RESYNCHRONIZATION - Database is running in resynchronization mode
-- MAXIMUM PERFORMANCE - Database is running in maximized protection mode
-- UNPROTECTED - Database is unprotected (this normally occurs when the primary database is mounted and not open)

-- If protection_level is different than protection_mode then for some
-- reason the mode listed in protection_mode experienced a need to downgrade. Once the
-- error condition has been corrected the protection_level should match the protection_mode
-- after the next log switch.

column role format a7 tru 
column name format a10 wrap

select name,database_role role,log_mode,
       protection_mode,protection_level  
from v$database;

-- ARCHIVER can be STOPPED/STARTED/FAILED -
-- Archiver FAILED means archiver failed to archive a log last time but will try
-- again within 5 minutes
-- LOG_SWITCH_WAIT can be ARCHIVE LOG/CLEAR LOG/CHECKPOINT/NULL -
-- NULL means ALTER SYSTEM SWITCH LOGFILE is hung but there is room in the current online redo log

column host_name format a20 tru 
column version format a9 tru 

select instance_name,host_name,version,archiver,log_switch_wait 
from v$instance;

-- Force logging is not mandatory but is recommended. Supplemental
-- logging must be enabled if the standby associated with this primary is
-- a logical standby. During normal operations it is acceptable for
-- SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.

column force_logging format a13 tru 
column remote_archive format a14 tru 
column dataguard_broker format a16 tru 

select force_logging,remote_archive,
       supplemental_log_data_pk,supplemental_log_data_ui, 
       switchover_status,dataguard_broker 
from v$database;

-- This query produces a list of all archive destinations and shows if they are enabled,
-- what process is servicing that destination, if the destination is local or remote,
-- and if remote what the current mount ID is.
-- Status can be, VALID - Initialized and available
-- INACTIVE - No destination information
-- DEFERRED - Manually disabled by the user
-- ERROR - Error during open or copy
-- DISABLED - Disabled after error
-- BAD PARAM - Parameter has errors
-- ALTERNATE - Destination is in an alternate state
-- FULL - Exceeded quota size for the destination.
-- For a physical standby we should have at least one remote destination that points the primary set.

COLUMN destination FORMAT A35 WRAP 
column process format a10 
column archiver format a10 
column ID format 99 

select dest_id "ID",destination,status,target, 
archiver,schedule,process,mountid  
from v$archive_dest order by 1;

-- This select will give further detail on the destinations as to what
-- options have been set. Register indicates whether or not the archived
-- redo log is registered in the remote destination control file.

set numwidth 8
column ID format 99 

select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async,
       net_timeout net_time,delay_mins delay,reopen_secs reopen,
       register,binding 
from v$archive_dest order by dest_id;

-- The following select will show any errors that occured the last time an attempt to
-- archive to the destination was attempted. If ERROR is blank and status is VALID then
-- the archive completed correctly.

column error format a80 tru 
select dest_id,status,error from v$archive_dest;

-- Determine if any error conditions have been reached by querying the v$dataguard_status

column message format a100 
select message, timestamp 
from v$dataguard_status 
where severity in ('Error','Fatal') 
order by timestamp;

-- The following query will determine the current sequence number
-- and the last sequence archived. If you are remotely archiving
-- using the LGWR process then the archived sequence should be one
-- higher than the current sequence. If remotely archiving using the
-- ARCH process then the archived sequence should be equal to the
-- current sequence. The applied sequence information is updated at
-- log switch time.

select ads.dest_id,max(sequence#) "Current Sequence",
       max(log_sequence) "Last Archived"
from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads 
where ad.dest_id=al.dest_id 
and al.dest_id=ads.dest_id 
group by ads.dest_id;

-- The following select will attempt to gather as much information as
-- possible from the standby.

set numwidth 8
column ID format 99 
column "SRLs" format 99 
column Active format 99 

select dest_id id,database_mode db_mode,recovery_mode, 
       protection_mode,standby_logfile_count "SRLs",
       standby_logfile_active ACTIVE, 
       archived_seq# 
from v$archive_dest_status;

-- Query v$managed_standby to see the status of processes involved in
-- the shipping redo on this system. Does not include processes needed to
-- apply redo.

select process,status,client_process,sequence#
from v$managed_standby;

-- The following query is run on the primary to see if SRL's have been
-- created in preparation for switchover.

select group#,sequence#,bytes from v$standby_log;

-- The above SRL's should match in number and in size with the ORL's
-- returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

-- Verify all the Non-default init parameters.

set numwidth 5 
column name format a30 tru 
column value format a48 wra 
select name, value 
from v$parameter 
where isdefault = 'FALSE';
 
spool off

In RAC database alert log reports ORA-00202 ORA-17510

Problem Description
In RAC environment alert log of the database instance intermittently reports following message for a brief period of time.

ORA-00202: controlfile: '+DATA/database/control01.ctl'
ORA-17510: Attempt to do i/o beyond file size

However, after sometime errors are go away. Database functions normally since the errors are reported and no problem is seen from the front end.

Cause of the Errors Reported
If you have close look of the alert log file of the other instance in the database, you will notice following message.

kccrsz: expanded controlfile section < > from < > to < > records
requested to grow by < > record(s); added < > block(s) of records

The above message indicates that there was an expansion in the size of the control file observed on the other node at around the time the errors were reported.

The scenario here is that there is a small window where the resize of a control file took place on one node and before the new control file size got updated/refreshed on the other node, a select was issued against one of the fixed views that accessed the control file without this control file transaction.

If you look for the trace files generated due to the these errors, you will notice the v$ fixed views being accessed in the failing sql captured.

The errors ultimately being disappeared automatically because the new control file size would get refreshed across all the nodes.

Solution
Since errors are harmless so you can simply ignore the error message.

ORA-17510: Attempt to do i/o beyond file size ORA-00600 kmgs_parameter_update_timeout_1

Problem Description
While setting any parameter in the physical standby database spfile it fails with ORA-17510. For example, changing LOCAL_LISTENER parameter in spfile fails with following message.

SQL> ALTER SYSTEM SET LOCAL_LISTENER=LISTENER_LOCAL scope=both sid='*';
ORA-17510: Attempt to do i/o beyond file size

If you look for alert log entry you will notice error message like,

Thu Sep 16 17:13:17 2010
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc2/trace/bdafisdc2_mmon_12522.trc (incident=14620):
ORA-00600: internal error code, arguments: [kmgs_parameter_update_timeout_1], [17510], [], [], [], [], [], [], [], [], [], []
ORA-17510: Attempt to do i/o beyond file size
Incident details in: /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc2/incident/incdir_14620/bdafisdc2_mmon_12522_i14620.trc

Cause of the Problem
The error ORA-17510 and ORA-600 [kmgs_parameter_update_timeout_1] indicates a problem of extending the size of the spfile, or a possible corruption of the spfile. Oracle is not able to write entry in the spfile and hence error returned.

Solution of the Problem
In order to solve the problem you need to recreate the spfile.

1. First create a pfile from the spfile, e.g.:

SQL> connect / as sysdba
SQL> create pfile='/tmp/pfile.ora' from spfile;

2. Startup the instance using the pfile created, e.g.:

SQL> starup pfile='/tmp/pfile.ora';

3. Then recreate the spfile from the pfile, e.g.:

SQL> connect / as sysdba
SQL> create spfile from pfile='/tmp/pfile.ora';

4. Startup the instance with the spfile and modify the parameter.
SQL> startup
SQL> ALTER SYSTEM SET LOCAL_LISTENER=LISTENER_LOCAL scope=both sid='*';

Diagnosis Oracle Physical Standby Data Guard Configuration

-- NAME: DG_Physical_Standby_Diagnosis.sql
-- ------------------------------------------------------------------------
-- AUTHOR: Mohammad Abdul Momin Arju
-- September 2010.
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is used to troubleshoot Oracle Data Guard issues for
-- physical standby database.
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only.
-- Test it before you run in your production database.
-- ------------------------------------------------------------------------
-- Content of the Script

set echo off 
set feedback off 
column timecol new_value timestamp 
column spool_extension new_value suffix 
select to_char(sysdate,'Mondd_hhmi') timecol, 
'.out' spool_extension from sys.dual; 
column output new_value dbname 
select value || '_' output 
from v$parameter where name = 'db_name'; 
spool dgdiag_phystby_&&dbname&×tamp&&suffix 
set lines 200 
set pagesize 35 
set trim on 
set trims on 
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS'; 
set feedback on 
select to_char(sysdate) time from dual; 

set echo on

column host_name format a20 tru 
column version format a9 tru 
select instance_name,host_name,version,archiver,log_switch_wait from v$instance;

column ROLE format a7 tru 
select name,database_role,log_mode,controlfile_type,protection_mode,protection_level  
from v$database;

-- Force logging is not mandatory but is recommended. Supplemental logging should be enabled
-- on the standby if a logical standby is in the configuration. During normal
-- operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.

column force_logging format a13 tru 
column remote_archive format a14 tru 
column dataguard_broker format a16 tru 
select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui, 
switchover_status,dataguard_broker from v$database;

COLUMN destination FORMAT A35 WRAP 
column process format a10 
column archiver format a10 
column ID format 99 

select dest_id "ID",destination,status,target, 
archiver,schedule,process,mountid  
from v$archive_dest;

-- If the protection mode of the standby is set to anything higher than max performance
-- then we need to make sure the remote destination that points to the primary is set
-- with the correct options else we will have issues during switchover.

select dest_id,process,transmit_mode,async_blocks, 
net_timeout,delay_mins,reopen_secs,register,binding 
from v$archive_dest;

column error format a80 tru 
select dest_id,status,error from v$archive_dest;

-- Determine if any error conditions have been reached by querying the v$dataguard_status

column message format a100 
select message, timestamp 
from v$dataguard_status 
where severity in ('Error','Fatal') 
order by timestamp;

-- The following query is ran to get the status of the Standby Redo Log's on the standby. If the
-- primary is archiving with the LGWR process and SRL's are present (in the correct
-- number and size) then we should see a group# active.

select group#,sequence#,bytes,used,archived,status from v$standby_log;

-- The above SRL's should match in number and in size with the Online Redo Log's returned below:

select group#,thread#,sequence#,bytes,archived,status from v$log;

-- Query v$managed_standby to see the status of processes involved in the configuration.

select process,status,client_process,sequence#,block#,active_agents,known_agents
from v$managed_standby;

-- Verify that the last sequence# received and the last sequence# applied to standby
-- database.

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;

-- V$ARCHIVE_GAP displays information about archive gaps on a standby database.
-- This view can be used to find out the current archive gap that is blocking
-- recovery for the current recovery incarnation.

select * from v$archive_gap;

-- It is always better to verify all non-default init parameters.

set numwidth 5 
column name format a30 tru 
column value format a50 wra 
select name, value 
from v$parameter 
where isdefault = 'FALSE';

spool off

Oracle in World

Most Visited Recently

Categories

Popular Posts

Which OS you like best for your oracle