Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archivelog Error in standby database ORA-00270, ORA-19809, ORA-19804

Posted by Hendry chinnapparaj on June 11, 2010

Problem

———–

The archive destination / FRA is full causing archive gap in the Data Guard Env.

Errors

——-

Creating archive destination file : +DATA (18727 blocks)

Fri Jun 11 15:07:29 2010

Errors in file /u01/oracle/product/10.2.0/db_1/admin/cpstest/udump/cpstest_rfs_344110.trc:

ORA-00270: error creating archive log +DATA

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 10485760 bytes disk space from 16106127360 limit

Fri Jun 11 15:07:29 2010

Errors in file /u01/oracle/product/10.2.0/db_1/admin/cpstest/udump/cpstest_rfs_344110.trc:

ORA-00270: error creating archive log +DATA

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 10485760 bytes disk space from 16106127360 limit

Query the Archive Gap

—————————-

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”

2    FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

3    4  WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference

———- ———————- ——————— ———-

1                    423                   259        164

Query the FRA Space

————————–

SQL> SELECT file_type, space_used * percent_space_used / 100 / 1024 / 1024 used,

space_reclaimable

* percent_space_reclaimable

/ 100

/ 1024

/ 1024 reclaimable,

frau.number_of_files

FROM v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

2    3    4    5    6    7    8

FILE_TYPE          USED RECLAIMABLE NUMBER_OF_FILES

———— ———- ———– —————

CONTROLFILE           0           0               0

ONLINELOG      909.1344           0               9

ARCHIVELOG   14415.6159           0             350

BACKUPPIECE     21.4998           0               2

IMAGECOPY             0           0               0

FLASHBACKLOG     7.6785           0               2

6 rows selected.

Fix the issue / delete the archivelog files which are already applied and reclaim disk space

—————————————————————————————————————

Rman target /

RMAN> delete archivelog from sequence 1 until sequence 258;

deleted archive log

archive log filename=+DATA/cpstest_j/archivelog/2010_06_08/thread_1_seq_257.1686.721138949 recid=466 stamp=721138948

deleted archive log

archive log filename=+DATA/cpstest_j/archivelog/2010_06_08/thread_1_seq_258.1809.721139309 recid=467 stamp=721139309

Deleted 258 objects

Query FRA Space after deleting archive logs

——————————————————–

SQL> SELECT file_type, space_used * percent_space_used / 100 / 1024 / 1024 used,

space_reclaimable

* percent_space_reclaimable

/ 100

/ 1024

/ 1024 reclaimable,

frau.number_of_files

FROM v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;

2    3    4    5    6    7    8

FILE_TYPE          USED RECLAIMABLE NUMBER_OF_FILES

———— ———- ———– —————

CONTROLFILE           0           0               0

ONLINELOG      311.8656           0               9

ARCHIVELOG    1465.5576           0             224

BACKUPPIECE      7.3752       .0308               2

IMAGECOPY             0           0               0

FLASHBACKLOG    22.1256           0              16

6 rows selected.

Query for archive Gap

—————————-

SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”

2    FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

3    (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

4  WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference

———- ———————- ——————— ———-

1                    423                   423          0

devu009n4 $ dgmgrl /

DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Error:

ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL

SQL> show parameter broker

NAME                                 TYPE        VALUE

———————————— ———– ——————————

dg_broker_config_file1               string      /u01/oracle/product/10.2.0/db_

1/dbs/dr1cpstest_f.dat

dg_broker_config_file2               string      /u01/oracle/product/10.2.0/db_

1/dbs/dr2cpstest_f.dat

dg_broker_start                      boolean     TRUE

primary

————

select synchronization_status, synchronized from v$archive_dest_Status;

SYNCHRONIZATION_STATUS SYNCHRONIZED

———————- ————

CHECK CONFIGURATION    NO

OK                     YES

CHECK CONFIGURATION    NO

CHECK CONFIGURATION    NO

CHECK CONFIGURATION    NO

CHECK CONFIGURATION    NO

CHECK CONFIGURATION    NO

CHECK CONFIGURATION    NO

evu009n3 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL>

devu009n4 $ . oraenv

ORACLE_SID = [+ASM] ? cpstest

The Oracle base for ORACLE_HOME=/u01/oracle/product/10.2.0/db_1 is /u01/oracle/product/10.2.0/db_1

devu009n4 $

devu009n4 $

devu009n4 $ dgmgrl /

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: