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)

Wednesday, November 19, 2014

ORA-12516, TNS: listener could not find available handler with matching protocol stack

My Application Team getting below error and unable to initiate connection to the oracle database:

error:  ORA-12516, TNS: listener could not find available handler with matching protocol stack

When I connected and checked on the database, (here my db is RAC server and using spfile)

On NODE 2:

Checking alert log file 

vi alert_DEVD2.log      

No errors found

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 18 19:35:36 2014

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, Oracle Database Vault and Real Application Testing options

SQL>
col name format a30
col value format a10

SQL> select name, value from v$spparameter where name in ('processes','sessions');

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

or, check the max and current utilization of these parameters

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

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

  COUNT(*)
----------
       166

On NODE 1 :

SQL> select * from v$resource_limit where resource_name in ('processes','sessions');



 vi alert_DEVD1.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.


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

  COUNT(*)
----------
       380


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

  COUNT(*)
----------
       546

From the above output I see my processes are exceeded the value set by oracle(400) and resulting the error in alert log. Then checked the connections to the database:

Set linesize 280;
select count(*),INST_ID,username,osuser,status,machine from gv$session group by INST_ID,username,osuser,status,machine order by count(*),INST_ID;

Check the output from the above query and kill if find any INACTIVE sessions (idle)

Solution:

According to oracle, PROCESSES parameter specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.


If the database is a RAC database then each NODE can have different values for PROCESS parameter.

SQL> sho parameter spfile;

NAME                                           TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATADG/DEVD/spfiledevd.ora

Increase the Process parameter (replace xxx with any higher value)

SQL> alter system set processes=XXX  scope=spfile;
System altered.

SQL> alter system set Sessions=XXX  scope=spfile;
System altered.

*** Bounce the database to take effect ***




If processes=x
sessions=x*1.1+5
transactions=sessions*1.1


If the database is running on pfile then follow below steps:

1. Edit the database init.ora
2. Locate and increase the processes parameter to a higher value.
3. Save the init.ora file
4. Stop and restart the database
5. Re-connect to the database instance


For more Refer:

Troubleshooting Guide - ORA-20: Maximum Number Of Processes (%S) Exceeded (Doc ID 1287854.1)