Thursday, June 25, 2015

ORA-39202: Data cannot be filtered or selected in ESTIMATE_ONLY jobs

I have a huge table and I want to find the size of the rows which are 6 months old out of historical data in the table using datapump

$ expdp directory=EXP_DIR nologfile=y query=table:\"where CRT_TS\>'01-JAN-15 06.07.03.799000000 AM'\" tables=SCHEMA.TABLE  estimate_only=y

Export: Release 11.2.0.4.0 - Production on Thu Jun 25 15:48:46 2015

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39202: Data cannot be filtered or selected in ESTIMATE_ONLY jobs.


According to oracle support this is reported as BUG in 11gr2, we cannot filter the data rows when doing ESTIMATES at the same time

“Bug 9536364 : DOC: DATA_FILTER AND ESTIMATE OPTIONS CANNOT BE USED TOGETHER IN DBMS_DATAPUMP”


Action: Do not restrict data handling on jobs that cannot support data filtering.





Thursday, April 23, 2015

Oracle Cluster Health Monitor (CHM) using large amount of space (crfclust.bdb)

Last night my rac 2 node server went down for OS patcing and rebooted but all CRS resources not coming up on both the node after node reboots:

[root@oradev11 bin]# ./crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  OFFLINE
ora.cluster_interconnect.haip
      1        ONLINE  OFFLINE
ora.crf
      1        ONLINE  OFFLINE
ora.crsd
      1        ONLINE  OFFLINE
ora.cssd
      1        ONLINE  OFFLINE
ora.cssdmonitor
      1        ONLINE  ONLINE       oradev11
ora.ctssd
      1        ONLINE  OFFLINE
ora.diskmon
      1        ONLINE  OFFLINE
ora.drivers.acfs
      1        ONLINE  ONLINE       oradev11
ora.evmd
      1        ONLINE  OFFLINE
ora.gipcd
      1        ONLINE  OFFLINE
ora.gpnpd
      1        ONLINE  OFFLINE
ora.mdnsd
      1        ONLINE  OFFLINE                               STARTING


CRS alert log says:

[root@oradev11 ] # cd $GRID_HOME/log/hostname
[root@oradev11 oradev11]# tail -50f alertoradev11.log

o/p trimmed………

2015-04-22 20:06:01.173:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(24990)]CRS-5818:Aborted
2015-04-22 20:06:05.177:
[ohasd(12696)]CRS-2757:Command 'Start' timed out waiting for response from the resource 'ora.mdnsd'. Details at (:CRSPE00111:) {0:0:2} in /u01/app/11.2.0.4/grid/log/sl73vmhasd/ohasd.log.
2015-04-22 20:06:05.658:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(25614)]CRS-0037:An error occurred while attempting to write to file "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagenagent_grid.log". Additional diagnostics: LFI-00004: Call to lfibwrt() failed.
LFI-01518: write() failed(OSD return value = 28) in slfiwl.

2015-04-22 20:06:05.659:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(25614)]CRS-0004:logging terminated for the process. log file: "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagent_gridgrid.log"
2015-04-22 20:06:06.176:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(25631)]CRS-0037:An error occurred while attempting to write to file "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagenagent_grid.log". Additional diagnostics: LFI-00004: Call to lfibwrt() failed.
LFI-01518: write() failed(OSD return value = 28) in slfiwl.

2015-04-22 20:06:06.176:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(25631)]CRS-0004:logging terminated for the process. log file: "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagent_gridgrid.log"
2015-04-22 20:06:06.272:
[gpnpd(25644)]CRS-0037:An error occurred while attempting to write to file "/u01/app/11.2.0.4/grid/log/oradev11/gpnpd/gpnpd.log". Additional diagnostics: LFI-00004: ibwrt() failed.
LFI-01518: write() failed(OSD return value = 28) in slfiwl.

2015-04-22 20:06:06.272:
[gpnpd(25644)]CRS-0004:logging terminated for the process. log file: "/u01/app/11.2.0.4/grid/log/oradev11/gpnpd/gpnpd.log"
2015-04-22 20:06:09.314:
[gpnpd(25644)]CRS-2329:GPNPD on node oradev11 shutdown.
2015-04-22 20:08:06.226:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(25631)]CRS-5818:Aborted command 'start' for resource 'ora.gpnpd'. Details at (:CRSAGF00113:) {0:0:2} in /u01/app/11.2.0.4/grid/logbd001/agent/ohasd/oraagent_grid/oraagent_grid.log.
2015-04-22 20:08:10.229:
[ohasd(12696)]CRS-2757:Command 'Start' timed out waiting for response from the resource 'ora.gpnpd'. Details at (:CRSPE00111:) {0:0:2} in /u01/app/11.2.0.4/grid/log/sl73vmhasd/ohasd.log.
2015-04-22 20:08:10.710:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(26582)]CRS-0037:An error occurred while attempting to write to file "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagenagent_grid.log". Additional diagnostics: LFI-00004: Call to lfibwrt() failed.
LFI-01518: write() failed(OSD return value = 28) in slfiwl.

2015-04-22 20:08:10.710:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(26582)]CRS-0004:logging terminated for the process. log file: "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagent_gridgrid.log"
2015-04-22 20:08:11.280:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(26604)]CRS-0037:An error occurred while attempting to write to file "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagenagent_grid.log". Additional diagnostics: LFI-00004: Call to lfibwrt() failed.
LFI-01518: write() failed(OSD return value = 28) in slfiwl.

2015-04-22 20:08:11.280:
[/u01/app/11.2.0.4/grid/bin/oraagent.bin(26604)]CRS-0004:logging terminated for the process. log file: "/u01/app/11.2.0.4/grid/log/oradev11/agent/ohasd/oraagent_gridgrid.log"
2015-04-22 20:08:11.347:
[mdnsd(26617)]CRS-0037:An error occurred while attempting to write to file "/u01/app/11.2.0.4/grid/log/oradev11/mdnsd/mdnsd.log". Additional diagnostics: LFI-00004: ibwrt() failed.
LFI-01518: write() failed(OSD return value = 28) in slfiwl.

2015-04-22 20:08:11.347:
[mdnsd(26617)]CRS-0004:logging terminated for the process. log file: "/u01/app/11.2.0.4/grid/log/oradev11/mdnsd/mdnsd.log"
2015-04-22 20:08:11.351:
[mdnsd(26617)]CRS-5602:mDNS service stopping by request.

After so much of time spending on troubleshooting I checked the space on server mounts and then released it is because of space issue services are not coming up

[root@oradev11 bin]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/rootvg-rootlv
                      5.8G  1.8G  3.8G  33% /
tmpfs                 3.0G     0  3.0G   0% /dev/shm
/dev/sda1             190M   86M   95M  48% /boot
/dev/mapper/rootvg-homelv
                      2.0G  9.2M  1.8G   1% /home
/dev/mapper/rootvg-optlv
                      9.8G  2.0G  7.3G  22% /opt
/dev/mapper/rootvg-securlv
                      1.5G  211M  1.2G  16% /opt/security
/dev/mapper/rootvg-tmplv
                      2.0G  375M  1.5G  21% /tmp
/dev/mapper/rootvg-varlv
                      9.8G  1.1G  8.2G  12% /var
/dev/mapper/datavg-gridbaselv
                       50G   49G     0 100% /u01/app
/dev/mapper/datavg-rdbmsbaselv
                       50G  4.8G   42G  11% /u01/app/oracle
/dev/mapper/datavg-adrrepolv
                       50G  2.6G   45G   6% /oratrace
/dev/mapper/datavg-oemagentlv
                       20G  651M   18G   4% /u01/app/emagent
/dev/mapper/datavg-gglv
                       50G   52M   47G   1% /gg
/dev/mapper/datavg-dbawslv
                       99G   16G   79G  17% /oraworkspace
/dev/mapper/datavg-auditfslv
                       50G  230M   47G   1% /oradbaudit
/dev/mapper/datavg-dbtoolslv
                      9.8G   86M  9.2G   1% /oratools



[root@oradev11 bin]# cd ../crf/db
[root@oradev11 db]# ls -lrht
total 4.0K
drwxr-x--- 2 root oinstall 4.0K Apr 22 20:45 oradev11
[root@oradev11 db]# cd oradev11

[root@oradev11 oradev11]# ls -lrth
total 38G
-rw-r--r-- 1 root root 1.1M Sep  8  2014 08-SEP-2014-09:24:06.txt
-rw-r--r-- 1 root root 1.9M Sep  8  2014 08-SEP-2014-10:07:28.txt
-rw-r--r-- 1 root root 1.2M Sep  8  2014 08-SEP-2014-10:20:00.txt
-rw-r----- 1 root root 8.0K Nov 20 09:44 repdhosts.bdb
-rw-r--r-- 1 root root  74K Mar  9 10:53 09-MAR-2015-10:53:37.txt
-rw-r--r-- 1 root root 856K Mar  9 10:56 09-MAR-2015-10:56:42.txt
-rw-r--r-- 1 root root  77K Mar 13 19:21 13-MAR-2015-19:21:26.txt
-rw-r--r-- 1 root root 218K Mar 13 19:21 13-MAR-2015-19:21:44.txt
-rw-r----- 1 root root  16M Apr 22 12:19 log.0000007983
-rw-r----- 1 root root  24K Apr 22 20:42 __db.001
-rw-r--r-- 1 root root 115M Apr 22 20:42 oradev11.ldb
-rw-r----- 1 root root 8.0K Apr 22 20:43 crfconn.bdb
-rw-r--r-- 1 root root 777K Apr 22 20:45 22-APR-2015-20:45:53.txt
-rw-r----- 1 root root  56K Apr 22 20:56 __db.006
-rw-r----- 1 root root 392K Apr 22 20:56 __db.002
-rw-r----- 1 root root 812M Apr 22 20:56 crfloclts.bdb
-rw-r----- 1 root root 668M Apr 22 20:56 crfcpu.bdb
-rw-r----- 1 root root 743M Apr 22 20:56 crfalert.bdb
-rw-r----- 1 root root 526M Apr 22 20:56 crfts.bdb
-rw-r----- 1 root root 607M Apr 22 20:56 crfhosts.bdb
-rw-r----- 1 root root  34G Apr 22 20:56 crfclust.bdb
-rw-r----- 1 root root  16M Apr 22 20:56 log.0000007984
-rw-r----- 1 root root 1.2M Apr 22 20:56 __db.005
-rw-r----- 1 root root 2.1M Apr 22 20:56 __db.004
-rw-r----- 1 root root 2.6M Apr 22 20:56 __db.003

From the above output I see only “crfclust.bdb” is consuming lot of space, then I followed the steps given in the oracle doc to free up the space on the server


Stop ora.crf ……….

[root@oradev11 bin]# ./crsctl stop res ora.crf -init
CRS-2673: Attempting to stop 'ora.crf' on 'oradev11'
CRS-2677: Stop of 'ora.crf' on 'oradev11' succeeded

[root@oradev11 oradev11]# rm crfclust.bdb

[root@oradev11 oradev11]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/rootvg-rootlv
                      5.8G  1.8G  3.8G  33% /
tmpfs                 3.0G  854M  2.2G  28% /dev/shm
/dev/sda1             190M   86M   95M  48% /boot
/dev/mapper/rootvg-homelv
                      2.0G  9.2M  1.8G   1% /home
/dev/mapper/rootvg-optlv
                      9.8G  2.0G  7.3G  22% /opt
/dev/mapper/rootvg-securlv
                      1.5G  211M  1.2G  16% /opt/security
/dev/mapper/rootvg-tmplv
                      2.0G  376M  1.5G  21% /tmp
/dev/mapper/rootvg-varlv
                      9.8G  1.1G  8.2G  12% /var
/dev/mapper/datavg-gridbaselv
                       50G   13G   34G  28% /u01/app
/dev/mapper/datavg-rdbmsbaselv
                       50G  4.8G   42G  11% /u01/app/oracle
/dev/mapper/datavg-adrrepolv
                       50G  2.6G   45G   6% /oratrace
/dev/mapper/datavg-oemagentlv
                       20G  651M   18G   4% /u01/app/emagent
/dev/mapper/datavg-gglv
                       50G   52M   47G   1% /gg
/dev/mapper/datavg-dbawslv
                       99G   16G   79G  17% /oraworkspace
/dev/mapper/datavg-auditfslv
                       50G  231M   47G   1% /oradbaudit
/dev/mapper/datavg-dbtoolslv
                      9.8G   86M  9.2G   1% /oratools
/dev/asm/ggatevol-387
                       20G  562M   20G   3% /gg/GG11
                                                           
Start again………..

[root@oradev11 bin]# ./crsctl start res ora.crf -init
CRS-2672: Attempting to start 'ora.crf' on 'oradev11'

CRS-2676: Start of 'ora.crf' on 'oradev11' succeeded

[root@oradev11 bin]# ./crsctl status res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       oradev11           Started
ora.cluster_interconnect.haip
      1        ONLINE  ONLINE       oradev11
ora.crf
      1        ONLINE  ONLINE       oradev11
ora.crsd
      1        ONLINE  ONLINE       oradev11
ora.cssd
      1        ONLINE  ONLINE       oradev11
ora.cssdmonitor
      1        ONLINE  ONLINE       oradev11
ora.ctssd
      1        ONLINE  ONLINE       oradev11           OBSERVER
ora.diskmon
      1        OFFLINE OFFLINE
ora.drivers.acfs
      1        ONLINE  ONLINE       oradev11
ora.evmd
      1        ONLINE  ONLINE       oradev11
ora.gipcd
      1        ONLINE  ONLINE       oradev11
ora.gpnpd
      1        ONLINE  ONLINE       oradev11
ora.mdnsd
      1        ONLINE  ONLINE       oradev11


Now I see all the resources are up and running

Refer:
Oracle Cluster Health Monitor (CHM) using large amount of space (more than default) (Doc ID 1343105.1)



Sunday, March 1, 2015

ORA-31613: Master process DM00 failed during startup

DB: 11.2.0.4 RAC database
OS: RHEL 6

While performing  schema export using datapump, expdp job is failing to start and throwing above error.

 [oracle@oracle002 /oracle]$ expdp parfile=exp_MY_schema.par

Export: Release 11.2.0.4.0 - Production on Tue Feb 24 18:31:59 2015

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1587
ORA-39062: error creating master process DM00
ORA-31613: Master process DM00 failed during startup.

If we check the Alert log:

ORA-00020: maximum number of processes (400) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.

According to oracle doc (Doc ID 1368180.1) the issue is with oracle process reached maximum limit and hence DM00 failed to start


[oracle@oracle002 /oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 24 18:38:23 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options

SQL> select resource_name, current_utilization/limit_value*100,current_utilization, max_utilization,limit_value from v$resource_limit where resource_name in ('processes','sessions');

Output for the above sql shows Max utilization and max limit for the process and session parameters
(or)
SQL> select name, value from v$spparameter where name in ('processes','sessions');

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
processes
400

sessions
400

Check the current processes used:

SQL> select count(*) from v$process;

  COUNT(*)
----------
       393


Solution:

Increase the value of PROCESSES initialization parameter and restart the database. Please follow below link to resolve the issue



Reference:
Data Pump Export Fails With ORA-39062 and ORA-31613: Master Process DM00 Failed During Startup (Doc ID 1368180.1)


Saturday, February 14, 2015

ERROR: Could not delete DB checkpoint for REPLICAT

DB version:  11.2.0.2.0
OGG version:  11.2.1.0.3
OS Version: RHEL 6

GGSCI (oracledev10) 10> delete Replicat REP
ERROR: Could not delete DB checkpoint for REPLICAT REP (OCI Error ORA-00942: table or view does not exist (status = 942). Deleting from checkpoint table ggs_admin.ggchkpt, group 'REP', key 2292316344 (0x88a1f8b8), SQL <DELETE FROM ggs_admin.ggchkpt  WHERE group_name = 'REP' AND        group_key  = 2292316344>).

The  above error was due to REPLICAT process added without specifying NODBCHECKPOINT argument when it got created initially.

Solution:  Try deleting with "!" option

GGSCI (oracledev10) 12> delete replicat REP !
WARNING: Could not delete DB checkpoint for REPLICAT REP (OCI Error ORA-00942: table or view does not exist (status = 942). Deleting from checkpoint table ggs_admin.ggchkpt, group 'REP', key 2292316344 (0x88a1f8b8), SQL <DELETE FROM ggs_admin.ggchkpt  WHERE group_name = 'REP' AND        group_key  = 2292316344>).

Replicat REP Deleted now

Here, The “!” tells GoldenGate to ignore the checkpoint table

If we wish to create Replicat without checkpoint then use below command to create

ADD REPLICAT <group>, EXTTRAIL <trail>, NODBCHECKPOINT


If we want to create Replicat WITH checkpoint then use below command to create
ADD CHECKPOINTTABLE ggs_admin.ggchkpt          (create if not exists)

ADD REPLICAT <group>, EXTTRAIL <trail>,  CHECKPOINTTABLE ggs_admin.ggchkpt


Refer:
How Can I Delete A REPLICAT Created Without A Checkpoint Table? (Doc ID 965689.1)

Saturday, February 7, 2015

ORA-00257: archiver error. Connect internal only, until freed.


DB: 11gR2 RAC 2 NODE on LINUX

SQL> select name from v$database;

NAME
---------
PERFDB

This Error will occur for various reasons, let see what is causing this error in my database

Alert log message:  (Always check Alert log for this error)

ARC3: Error 19504 Creating archive log file to '+FRA'
Fri Oct 10 19:04:18 2014
Errors in file /u01/app/oracle/diag/rdbms/perfdb/PERFDB2/trace/PERFDB2_arc0_15765.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted
*************************************************************
WARNING: A file of type ARCHIVED LOG may exist in
db_recovery_file_dest that is not known to the database.
Use the RMAN command CATALOG RECOVERY AREA to re-catalog
any such files. If files cannot be cataloged, then manually
delete them using OS command. This is most likely the
result of a crash during file creation.

From my alert log it is clear that my FRA disk group got exhausted


SQL> show parameter recovery

NAME                                         TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string             +FRA
db_recovery_file_dest_size         big integer   300G
recovery_parallelism                    integer         0

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

     MAXGB     USEDGB
----------        ----------
       300       3.42773438       (have enough space)

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim", NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

Type                                  % Used     % Reclaim    # Files
--------------------                ----------     ---------- ----------
CONTROL FILE                           0          0          0
REDO LOG                                  0          0          0
ARCHIVED LOG                      1.13        0         21
BACKUP PIECE                        .01          0          1
IMAGE COPY                              0          0          0
FLASHBACK LOG                       0          0          0
FOREIGN ARCHIVED LOG        0          0          0

7 rows selected.


Database PERFDB is absolutely fine and recovery dest has space but +FRA is full because of archivelogs of other database which are sharing same ASM diskgroup
Here in this cluster 4 database are running and sharing same FRA diskgroup

ASMCMD> lsdg
State     Type     Rebal  Sector Block   AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N      512   4096  1048576   1535979    16725                0           16725              0             N       DATA/
MOUNTED  EXTERN  N  512   4096  1048576   1535979    539       0         539          0         N       FRA/
MOUNTED  EXTERN  N      512   4096  1048576       494      130                0             130              0             N      OCR/
MOUNTED  EXTERN  N      512   4096  1048576       494      131                0             131              0             N       OCRM/
MOUNTED  NORMAL  N      512   4096  1048576      1482     1194              494             120              0             Y       VOTE/



ASMCMD> pwd
+FRA/****DB/ARCHIVELOG
ASMCMD> rm -rf 2012_05*
ASMCMD> rm -rf 2012_06*
...   ……….

(OR)

If you want to delete archive logs older than ‘x’ days, then

RMAN> delete archivelog until time 'SYSDATE-X';       (deleting older than X days)

Freed some space in FRA by removing old archive log files(Take backup if this is critical db), then problem resolved for PERFDB database.

SQL> select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER NAME          TOTAL_MB    FREE_MB       USABLE_FILE_MB
------------ -------------------          ---------- -------     ----------      ---------------
           1 DATA                              1535979      16725          16725
           2 FRA                                 1535979     462046         462046
           3 OCRM                                    494          131            131
           4 OCR                                        494          130            130
           5 VOTE                                   1482         1194            120


If your Alert log says

Alert log message:

ARC2: Error 19809 Creating archive log file to '+FRADG'
Errors in file /oratrace/diag/rdbms/DB/DBSID2/trace/DBSID_arc3_29957.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 6005194752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************

Then check the below link:


Friday, January 30, 2015

OGG-01028 unexpected LOB record or record fragment

My replicat REP1 abended with the below error for SCHEMA.TEST table

ERROR   OGG-01028  unexpected LOB record or record fragment in /gg/dirdat/VC/VC000079, rba 1367.

CauseReplicat altered to start on trail where the first record is a LOB and NOT the base record.

Solution:  Start the Replicat Manually from the beginning of the LOB record.

Start logdump utility to find out the root cause

 [oracle@oracledev11 gg]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.



Logdump 411 >ghdr on
Logdump 412 >detail on
Logdump 413 >open /gg/dirdat/VC/VC000079
Current LogTrail is /gg/dirdat/VC/VC000079
Logdump 414 >pos o
Error: Invalid file position (O)
Logdump 415 >DETAIL DATA
Logdump 416 >pos 0
Reading forward from RBA 0
Logdump 417 >n

2015/01/25 17:52:55.283.720 FileHeader           Len  1359 RBA 0
Name: *FileHeader*
 3000 01a2 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...
 0003 3200 0004 2000 0000 3300 0008 02f2 33b1 25be | ..2... ...3.....3.%.
 3008 3400 001c 001a 7572 693a 736c 3733 7670 7064 | 0.4.....uri:oraclepro
 6270 3030 393a 3a67 673a 686f 6d65 3600 001d 001b | d009::gg:home6.....
 2f67 672f 4747 3131 2f64 6972 6461 742f 5643 2f56 | /gg/dirdat/VC/V
 4330 3030 3037 3937 0000 0101 3800 0004 0000 004f | C0000797....8......O
 3900 0008 0000 0000 05f5 db9a 3a00 0081 0b31 3038 | 9...........:....108

Logdump 418 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  2020  (x07e4)   IO Time    : 2015/01/25 17:52:52.996.472
IOType     :   116  (x74)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      25679       AuditPos   : 159347796
Continued  :     Y  (x01)     RecCount   :     3  (x03)

2015/01/25 17:52:52.996.472 LargeObject          Len  2020 RBA 1367
Name: SCHEMA.MYTEST
After  Image:                                             Partition 4      m
 0000 0003 0000 0005 0000 0002 0000 07d0 0000 07d0 | ....................
 204d 6f62 696c 652f 3131 4235 3534 6120 5361 6661 |  Mobile/3222F Safa
 7269 2f39 3533 372e 3533 222c 2254 484d 5f62 726f | ri/1234.53","THM_bro
 7773 6572 5f73 7472 696e 675f 6861 7368 3a34 6563 | wser_string_hash:4ec
 6134 3532 3034 3731 3166 3265 3634 3764 3030 3161 | xxxxxxxxxxxxxxxxxxx
 3133 3438 3734 3632 3322 2c22 5448 4d5f 6373 735f | 12344322","THM_css_
 696d 6167 655f 6c6f 6164 6564 3a79 6573 222c 2254 | image_loaded:yes","T
Column 3, LOB fragment 2 of 5
Size          2000 (x07d0)
Offset        2000 (x07d0)

Logdump 419 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    86  (x0056)   IO Time    : 2015/01/25 17:52:52.996.472
IOType     :   116  (x74)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      25679       AuditPos   : 159347796
Continued  :     Y  (x01)     RecCount   :     4  (x04)

2015/01/25 17:52:52.996.472 LargeObject          Len    86 RBA 3463
Name: SCHEMA.MYTEST
After  Image:                                             Partition 4      m
 0000 0003 0000 0005 0000 0003 0000 0fa0 0000 0042 | ...................B
 3022 2c22 5448 4d5f 7072 6f66 696c 6564 5f64 6f6d | 0","THM_profiled_dom
 6169 6e3a 7365 6375 7265 2e63 6865 636b 6f75 742e | ain:xxxxx.xxxx.
 7669 7361 2e63 6f6d 222c 2254 484d 5f70 726f 6669 | corp.com","THM_profi
 6c65 645f 646f                                    | led_do
Column 3, LOB fragment 3 of 5
Size            66 (x0042)
Offset        4000 (x0fa0)

………………..

By reading the above current trail file it is clear that it is trying to apply a LOB segment (RBA 1367) which is fragmented meaning(applying 2 of 5) it is not starting from 1 which is missing in 'VC000079' trail file



Lets go to previous trail file(VC000078) and search for the LOB:

Logdump 439 >DETAIL DATA
Logdump 440 >GHDR ON
Logdump 441 >detail on
Logdump 442 >open /gg/dirdat/VC/VC000078    (going one trail file back)
Current LogTrail is /gg/dirdat/VC/VC000078
Logdump 443 >pos eof                             (going End of file)
Reading forward from RBA 99998989
Logdump 444 >pos reverse                     (Reading from reverse)
Reading in reverse from RBA 99998989
Logdump 445 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  2020  (x07e4)   IO Time    : 2015/01/25 17:52:52.993.564
IOType     :   116  (x74)     OrigNode   :   255  (xff)
TransInd   :     .  (x01)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      25679       AuditPos   : 159347796
Continued  :     Y  (x01)     RecCount   :     2  (x02)

2015/01/25 17:52:52.993.564 LargeObject          Len  2020 RBA 99996893
Name: SCHEMA.MYTEST
After  Image:                                             Partition 4      m
 0000 0003 0000 0005 0000 0001 0000 0000 0000 07d0 | ....................
 7b22 616d 6d56 646e 6153 6572 7669 6365 5265 7175 | {"oracleserviceRequ
 6573 7422 3a7b 2250 726f 6475 6374 496e 766f 6b65 | est":{"ProductInvoke
 7254 7970 6522 3a22 565f 4d45 222c 2243 6f6e 7465 | rType":"XXXX","Conte
 7874 5479 7065 223a 2250 7572 6368 6173 6549 6e74 | xtType":"PurchaseInt
 656e 7422 2c22 5265 7175 6573 7449 6422 3a22 3331 | ent","RequestId":"31
 6136 3032 3566 2d37 3664 622d 3431 6133 2d62 3338 | a1234f-76db-xxxx-b38
Column 3, LOB fragment 1 of 5
Size          2000 (x07d0)
Offset           0 (x0000)

Logdump 446 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   116  (x0074)   IO Time    : 2015/01/25 17:52:52.995.853
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      25679       AuditPos   : 159347796
Continued  :     Y  (x01)     RecCount   :     1  (x01)

2015/01/25 17:52:52.995.853 Insert               Len   116 RBA 99996643       ß LOB starts from here
Name: SCHEMA.MYTEST
After  Image:                                             Partition 4   G  b
 0000 000c 0000 0008 3833 3731 3035 3036 0001 0028 | ........83710506...(
 0000 0024 3965 6463 3564 6530 2d30 6665 612d 3431 | ...$gddgg5w4-0fea-41
 3361 2d61 3938 332d 6666 3362 3834 6465 6561 3039 | 3a-a324-ff3b84feea09
 0002 0008 0000 0004 5644 4e41 0004 0005 0000 0001 | ........BSDA........
 3500 0500 1f00 0032 3031 352d 3031 2d32 353a 3137 | 5......2015-01-25:17
 3a35 323a 3533 2e37 3031 3030 3030 3030           | :52:53.701000000
Column     0 (x0000), Len    12 (x000c)
Column     1 (x0001), Len    40 (x0028)
Column     2 (x0002), Len     8 (x0008)
Column     4 (x0004), Len     5 (x0005)
Column     5 (x0005), Len    31 (x001f)

Logdump 447 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :  1805  (x070d)   IO Time    : 2015/01/25 17:52:52.995.853
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :      25466       AuditPos   : 55964348
Continued  :     N  (x00)     RecCount   :     1  (x01)

2015/01/25 17:52:52.995.853 Insert               Len  1805 RBA 99994708
Name: SCHEMA2.LOCATION
After  Image:                                             Partition 4   G  s
 0000 000c 0000 0008 3632 3839 3432 3433 0001 0041 | ........62894243...A
 0000 003d 6874 7470 733a 2f2f 766d 6564 666d 2e76 | ...=https://oracle.dom
 6973 612e 636f 6d3a 3834 3433 2f64 666d 7365 7276 | ain.com:8443/oracleserv
 6963 6573 2f76 312f 636f 6e73 756d 6572 2f64 6563 | ices/v1/consumer/dec
 6973 696f 6e00 0200 2f00 0000 2b31 3432 3232 3038 | ision.../...+12345678
 3230 335f 3536 375f 3532 335f 6c37 3370 3132 336d | 203_567_523_l73p123m
 5f43 4845 434b 4f55 542d 5749 4447 4554 0003 000e | _xxxxxx....
Column     0 (x0000), Len    12 (x000c)
Column     1 (x0001), Len    65 (x0041)
Column     2 (x0002), Len    47 (x002f)
Column     3 (x0003), Len    14 (x000e)
Column     4 (x0004), Len     5 (x0005)
Column     5 (x0005), Len   184 (x00b8)
Column     6 (x0006), Len     4 (x0004)
Column     7 (x0007), Len     6 (x0006)
Column     8 (x0008), Len  1295 (x050f)
Column     9 (x0009), Len    31 (x001f)
Column    10 (x000a), Len     8 (x0008)
Column    11 (x000b), Len     4 (x0004)
Column    12 (x000c), Len     8 (x0008)
Column    13 (x000d), Len    31 (x001f)
Column    14 (x000e), Len    31 (x001f)


Here we found the LOB segment in '78' trail file so lets start the replicat using the above highlighted rba number:

Syntax:   ggsci> alter <replicat>, extseqno ### ,extrba <RBA of record from above output>
  
ggsci> alter REP1, extseqno 78 ,extrba 99996643

ggsci> start REP1

For more info:
OGG Replicat Abends On LOB Data. OGG Error: OGG-01028: Unexpected LOB Record Or Record Fragment In ... (Trail File) (Doc ID 1903819.1)