Thursday, January 30, 2014

ORA-15032: not all alterations performed, ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)


##########################
## Error
##########################

ORA-15032: not all alterations performed

### Full Error

ASMCMD [+data] > rm RACDB01
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

##########################
#  Error Occurred
##########################

Error occured while removing a database directory in ASMCMD Prompt of 11.2.0.3.0 version

##########################
## Command Executed
##########################

rm RACDB01

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Folder Overview
#########################################

We have created new database with name RACDB01. But later on we need to create the same database with Different name. So We have used DBCA in silent mode to remove the database.
RAC Database and its instances was removed from OCR, but the database file which are present in ASM didn't get removed. so we are manually removing the directories as its not needed anymore.

=====================================================================================================================

#########################################
# 2) Before Dropping
#########################################

ASMCMD [+data] > ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    DB_UNKNOWN/
                                                   Y    RACDB02/
                                                   Y    RACDB01/
                                                   Y    RACDB07/

ASMCMD [+data] >

=====================================================================================================================

#########################################
# 3) Reproduce the Error
#########################################

Removing RACDB01 directory with rm command produces the error.

ASMCMD [+data] > rm RACDB01
ORA-15032: not all alterations performed
ORA-15177: cannot operate on system aliases (DBD ERROR: OCIStmtExecute)

=====================================================================================================================

#########################################
# Solution
#########################################

Just use Force option while removing.

rm -rf RACDB01

ASMCMD [+data] > rm -rf RACDB01
ASMCMD [+data] > ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    DB_UNKNOWN/
                                                   Y    RACDB02/
                                                   Y    RACDB07/
ASMCMD [+data] >


=====================================================================================================================
 RACDB01 folder is removed now.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================



Sunday, January 26, 2014

v$Flash_Recovery_Area_Usage Shows Wrong Values of NUMBER_OF_FILES


We have received an alert that FRA usaage has crossed 85%. Upon looking found that at OS level only 15 files but it shows as 220 files in v$flash_recovery_area_usage.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check Flashback Usage
#########################################

select * from v$flash_recovery_area_usage;
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               1
REDO LOG                              0                         0               0
ARCHIVED LOG                        2.9                         0              10
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                     83.62                     71.62             220
FOREIGN ARCHIVED LOG                  0                         0               0

=====================================================================================================================

#########################################
# 2) Check Recovery File Dest
#########################################

Select name,space_limit/1024/1024/1024 "Total Size",space_used/1024/1024/1024 "GB Used",round((space_used/space_limit)*100,2) "Percentage_Used",
(space_limit-space_used)/1024/1024/1024 "GB Free", space_reclaimable/1024/1024/1024 "Reclaimable",number_of_files from v$recovery_file_dest;
NAME                      Total Size    GB Used Percentage_Used    GB Free Reclaimable NUMBER_OF_FILES
------------------------- ---------- ---------- --------------- ---------- ----------- ---------------
+RECO                           1000 865.613281           86.56 134.386719  720.175781             220

=====================================================================================================================

#########################################
# 3) Database Parameters
#########################################

SQL> sho parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +RECO
db_recovery_file_dest_size           big integer 1000G

SQL> sho parameter retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     43200

=====================================================================================================================

#########################################
# 4) Flashback Logs
#########################################

Let us see the Flashback logs which is present at the OS level.

ASMCMD [+RECO/db01/flashback] > ls -l
Type       Redund  Striped  Time             Sys  Name
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_173.4531.837219837
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_199.41359.837295107
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_200.22599.837298645
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_201.32376.837302495
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_202.24492.837306331
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_203.25970.837309665
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_204.6831.837313373
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_205.7999.837317745
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_206.47642.837322305
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_207.75437.837325047
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_208.65550.837326775
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_209.21280.837328813
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_210.84547.837330497
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_211.72511.837332819
FLASHBACK  MIRROR  COARSE   JAN 20 09:00:00  Y    log_37.21464.834689357
ASMCMD [+RECO/db01/flashback] >

=====================================================================================================================
 If we can see the number of flashback logs present at OS level is only 15, but v$flash_recovery_AreA_usage & v$recovery_file_dest shows as 220
=====================================================================================================================

##########################
## Solution
##########################

Flashback Retention Target set at database level is 43200 minutes, which is 30 days.

Changing the database retention target from 30 days to 1 days

SQL> Alter system set db_flashback_retention_target=1440 scope=both sid='*';

System altered.

After changing this parameter, we have to wait for atleast 30 minutes to get this info updated in v$flash_recovery_Area_usage.

In case if v$flash_recovery_area_usage is still not updated then this seems to be a Bug 4911954.

A Workaround is to manually refresh v$flash_recovery_area_usage by running the below procedure.

SQL> execute dbms_backup_restore.refreshagedfiles;

PL/SQL procedure successfully completed.

Note :

Please monitor the alert log file for the changes happening after flashback retention target.

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Saturday, January 25, 2014

The OUI version is not applicable for current OPatch version. OPatch failed with error code 1


##########################
## Error
##########################

The OUI version is not applicable for current OPatch version.

### Full Error

[oracle@host01 OPatch]$ ./opatch lsinventory
OPatch Version : 11.1.0.9.2
  OUI  Version : 11.2.0.3.0
The OUI version is not applicable for current OPatch version.
OPatch failed with error code 1
[oracle@host01 OPatch]$

##########################
#  Error Occurred
##########################

Error occured while we run a opatch command from 12.1.0.1.0 version of 12c Cloud Management Agent

##########################
## Command Executed
##########################

./opatch lsinventory

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the ORACLE_HOME set
#########################################

[oracle@host01 OPatch]$ pwd
/u01/app/oracle/product/agent12c/core/12.1.0.1.0/OPatch

[oracle@host01 OPatch]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0.3/dbhome_1

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

ORACLE_HOME environment variable is set to 11g database home, but we are executing Opatch from 12c Cloud Agent Home.

=====================================================================================================================

##########################
## Solution
##########################

1) If We want to check the Inventory Details of Agent Home then Set 12c ORACLE AGENT HOME and then execute

[oracle@host01 OPatch]$ export ORACLE_HOME=/u01/app/oracle/product/agent12c/core/12.1.0.1.0
[oracle@host01 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.9.2
Copyright (c) 2011, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/agent12c/core/12.1.0.1.0
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/agent12c/core/12.1.0.1.0/oraInst.loc
OPatch version    : 11.1.0.9.2
OUI version       : 11.1.0.9.0
Log file location : /u01/app/oracle/product/agent12c/core/12.1.0.1.0/cfgtoollogs/opatch/opatch2014-01-23_11-19-48AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/agent12c/core/12.1.0.1.0/cfgtoollogs/opatch/lsinv/lsinventory2014-01-23_11-19-48AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
EM Platform (Agent)                                                  12.1.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :
Patch  6895422      : applied on Sat Jan 11 03:15:36 UTC 2014
Unique Patch ID:  14696320
   Created on 6 Apr 2012, 00:13:27 hrs PST8PDT
   Bugs fixed:
     6895422


--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@host01 OPatch]$

2) If we want to Check the Inventory Details of 11g Database Home then Set 11g database ORACLE HOME and then execute Opatch lsinventory command from 11g Database Home Binaries.

3) If we want to check the Inventory Details of 11g Database Home,

   OUI version and OPatch Version needs to be matched.

   Since OUI version starts with 11.1, please go to My Oracle Support and get the right OPatch 11.1.*.*.* using Patch Number 6880880, release version 11.1.0.0.0 on appropriate platform.

   [oracle@host01 OPatch]$ pwd
/u01/app/oracle/product/agent12c/core/12.1.0.1.0/OPatch
[oracle@host01 OPatch]$ mv p6880880_112000_Linux-x86-64.zip /u01/app/oracle/product/agent12c/core/12.1.0.1.0/
[oracle@host01 OPatch]$


[oracle@host01 12.1.0.1.0]$ pwd
/u01/app/oracle/product/agent12c/core/12.1.0.1.0
[oracle@host01 12.1.0.1.0]$ mv OPatch OPatch_Old
[oracle@host01 12.1.0.1.0]$ unzip p6880880_112000_Linux-x86-64.zip
Archive:  p6880880_112000_Linux-x86-64.zip
   creating: OPatch/


[oracle@host01 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0.3/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0.3/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/opatch2014-01-23_11-15-06AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.3/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-01-23_11-15-06AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g                                                  11.2.0.3.0
There are 1 product(s) installed in this Oracle Home.


=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================



12c Agent has stopped monitoring. The following errors are reported : TOO_MANY_OPEN_FILES.



One of our Oracle 12c Cloud Control Management Agent has started producing Errors like below.

##########################
## Error
##########################


##########################
#  Error Occurred
##########################

Oracle Cloud Control Managment Agent of 12.1.0.1.0 version has stopped monitoring the targets and started producing the alerts.

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Agent Status
#########################################

cd /u01/app/oracle/product/agent12c/core/12.1.0.1.0/bin

./emctl status agent

[oracle@host01 bin]$ ./emctl status agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.1.0
OMS Version       : 12.1.0.1.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /u01/app/oracle/product/agent12c/agent_inst
Agent Binaries    : /u01/app/oracle/product/agent12c/core/12.1.0.1.0
Agent Process ID  : 34527
Parent Process ID : 34343
Agent URL         : https://host01:3872/emd/main/
Repository URL    : https://oem:4901/empbs/upload
Started at        : 2014-01-10 21:42:00
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2014-01-23 11:55:26
Last attempted upload                        : 2014-01-23 11:55:26
Total Megabytes of XML files uploaded so far : 359.89
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 98.90%
Collection Status                            : Collections enabled
Last attempted heartbeat to OMS              : 2014-01-23 11:55:24
Last successful heartbeat to OMS             : 2014-01-23 11:55:24
---------------------------------------------------------------
Agent is Running and Ready


./emctl upload agent


[oracle@host01 bin]$ ./emctl upload agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
[oracle@host01 bin]$

=====================================================================================================================
 Agent is Running good and upload of Agent is also working good but it has stopped Monitoring the Targets.
=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

This error seems to be a Bug 14083191 for which a Patch 6895422 is Available and needs to be applied to Management Agent ORACLE_HOME

=====================================================================================================================

#########################################
# 3) Check Patch Details
#########################################

./opatch lsinventory

[oracle@host01 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.9.2
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/agent12c/core/12.1.0.1.0
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/agent12c/core/12.1.0.1.0/oraInst.loc
OPatch version    : 11.1.0.9.2
OUI version       : 11.1.0.9.0
Log file location : /u01/app/oracle/product/agent12c/core/12.1.0.1.0/cfgtoollogs/opatch/opatch2014-01-23_11-19-48AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/agent12c/core/12.1.0.1.0/cfgtoollogs/opatch/lsinv/lsinventory2014-01-23_11-19-48AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
EM Platform (Agent)                                                  12.1.0.1.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch  6895422      : applied on Sat Jan 11 03:15:36 UTC 2014
Unique Patch ID:  14696320
   Created on 6 Apr 2012, 00:13:27 hrs PST8PDT
   Bugs fixed:
     6895422
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@host01 OPatch]$

=====================================================================================================================
 From Above output we can see like Patch 6895422 is already applied but still we are getting Errors and Agent stopped Monitoring.
=====================================================================================================================

#########################################
# 4) Root Cause
#########################################

From 3) Output we can see that Patch 6895422 applied on 11-Jan-2014.

Patch  6895422      : applied on Sat Jan 11 03:15:36 UTC 2014

From 2) Output we can see that Agent has Last Started on 10-Jan-2014

Started at        : 2014-01-10 21:42:00

=====================================================================================================================

##########################
## Solution
##########################

To Fix the Error, the proposed solution is to Apply Patch 6895422.

In our case its already applied, but we found out that Agent is not Re-started after patch was applied to its HOME.

Agent needs to be bounced after Patch has been applied to it. A Bounce of Agent fixed the Issue.

./emctl stop agent

./emctl start agent

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Wednesday, January 22, 2014

ADRCI - DIA-48448: This command does not support multiple ADR homes



##########################
## Error
##########################

DIA-48448: This command does not support multiple ADR homes

### Full Error


adrci> ips pack incident 384130 in /tmp
DIA-48448: This command does not support multiple ADR homes

##########################
#  Error Occurred
##########################

Error occured while i tried to pack an incident in 11.2.0.3.0 version using adrci utility

##########################
## Command Executed
##########################

In ADRCI,

ips pack incident 384130 in /tmp

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Homes present in ADR
#########################################

show home

adrci> show home
ADR Homes:
diag/rdbms/db1/db1
diag/asm/+asm/+ASM
diag/asm/+asm/+ASM1
diag/clients/user_oracle/host_1249968719_11
diag/tnslsnr/host01p/listener
diag/tnslsnr/host01p/listener_scan2
diag/tnslsnr/host01p/listener_scan1
diag/tnslsnr/host01p/listeber
diag/tnslsnr/host01p/listener_scan3
diag/tnslsnr/host02/listener
adrci>

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

From above we can see that we have multiple homes present in ADR.

=====================================================================================================================

##########################
## Solution
##########################

set the corresponding database home to pack the incident generated for that database.

set home diag/rdbms/db1/db1

adrci> set home diag/rdbms/db1/db1
adrci>
adrci> show home
ADR Homes:
diag/rdbms/db1/db1
adrci>

adrci> ips pack incident 384130 in /tmp
Generated package 3 in file /tmp/ORA600250_20140122114957_COM_1.zip, mode complete
adrci>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Tuesday, January 21, 2014

Recovery Catalog update gives Error - java.sql.SQLException: ORA-00942: table or view does not exist


Error - java.sql.SQLException: ORA-00942: table or view does not exist

##########################
## Error
##########################

Error - java.sql.SQLException: ORA-00942: table or view does not exist

### Full Error



##########################
#  Error Occurred
##########################

Error occured while trying to update the recovery catalog information for the database in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Update Catalog information in Recovery Catalog Settings.

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Reproduce the Error
#########################################

Go To Database home --> Availability --> Recovery Catalog Settings ( In Backup / Recovery Section )

Once Recovery Catalog Settings page comes, click Recovery Catalog ( in Related Links Section ) and Edit the Recovery Catalog information

When we try to update recovery catalog database credentials we are getting the error mentioned in "Full Error"

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

When the screen shows like below to specify the credentials of Recovery catalog database, by Default we provide Recovery Catalog owner credentials.


That's where the problem lies, We should Provide a User with DBA privileges

=====================================================================================================================

#########################################
#  Solution
#########################################

So We have to provide the user credentials who has DBA Privileges like below,



Once We click Next, we will be taken to " Edit Recovery Catalog : User " page, where we will have to give Recovery catalog user credentials.


When we click next, a page will show like below.

Bug 16203642 also raised for this Error.

Seems like captions used in the Editing Recovery Catalog is little confusing. :)

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Sunday, January 19, 2014

12c Database Status pending ( Unknown )



We have recently moved from 11g Grid control to 12c cloud services. The way we resolve the targets which were in pending status from 11g to 12c is different. Please take a look into the simple steps.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check 12c Agent Status
#########################################

Set 12c Agent environment or go to the $AGENT_HOME and check the status of Agent.

[oracle@host01 bin]$ pwd
/u01/app/oracle/product/agent12c/core/12.1.0.1.0/bin

./emctl status agent


[oracle@host01 bin]$ ./emctl status agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.1.0
OMS Version       : 12.1.0.1.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /u01/app/oracle/product/agent12c/agent_inst
Agent Binaries    : /u01/app/oracle/product/agent12c/core/12.1.0.1.0
Agent Process ID  : 6790
Parent Process ID : 6688
Agent URL         : https://host01.dt.stepintooracledba.com:3873/emd/main/
Repository URL    : https://host02.stepintooracledba.com:4900/empbs/upload
Started at        : 2013-12-16 12:06:35
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2014-01-15 21:51:45
Last attempted upload                        : 2014-01-15 21:51:45
Total Megabytes of XML files uploaded so far : 158.69
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 35.93%
Collection Status                            : Collections enabled
Last attempted heartbeat to OMS              : 2014-01-15 21:51:49
Last successful heartbeat to OMS             : 2014-01-15 21:51:49
---------------------------------------------------------------
Agent is Running and Ready

=====================================================================================================================
 From above output check the below lines,

Last successful upload                       : This should be the latest timeframe
Number of XML files pending upload           : Check Any Files needs to be Uploaded
Collection Status                            : Make Sure Collection is enabled
Last successful heartbeat to OMS             : This should be the latest timeframe

=====================================================================================================================
 If Agent is not Running then start the agent
=====================================================================================================================

#########################################
# 2) Upload Agent
#########################################

Upload Agent and check if its uploading without any issues.

./emctl upload agent

[oracle@host01 bin]$ ./emctl upload agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

./emctl status agent

[oracle@host01 bin]$ ./emctl status agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.1.0
OMS Version       : 12.1.0.1.0
Protocol Version  : 12.1.0.1.0
Agent Home        : /u01/app/oracle/product/agent12c/agent_inst
Agent Binaries    : /u01/app/oracle/product/agent12c/core/12.1.0.1.0
Agent Process ID  : 6790
Parent Process ID : 6688
Agent URL         : https://host01.dt.stepintooracledba.com:3873/emd/main/
Repository URL    : https://host02.stepintooracledba.com:4900/empbs/upload
Started at        : 2013-12-16 12:06:35
Started by user   : oracle
Last Reload       : (none)
Last successful upload                       : 2014-01-15 21:53:32
Last attempted upload                        : 2014-01-15 21:53:32
Total Megabytes of XML files uploaded so far : 158.7
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 35.93%
Collection Status                            : Collections enabled
Last attempted heartbeat to OMS              : 2014-01-15 21:52:49
Last successful heartbeat to OMS             : 2014-01-15 21:52:49
---------------------------------------------------------------
Agent is Running and Ready

=====================================================================================================================

#########################################
# 3) Check Agent can Evaluate Response Metric
#########################################

cd /u01/orabase/12cagent/core/12.1.0.1.0/bin

./emctl getmetric agent (database_name_as_shown_in_console),oracle_database,Response

./emctl getmetric agent db01.stepintooracledba.com,oracle_database,Response

[oracle@host01 bin]$ ./emctl getmetric agent db01.stepintooracledba.com,oracle_database,Response
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Status,State,oraerr,Archiver,DatabaseStatus,ActiveState
1,OPEN,,STOPPED,ACTIVE,NORMAL
 1 = database is up

=====================================================================================================================

#########################################
# 4) Check Targets in Suspension State
#########################################

First we will check get the Agent Name using below command.

./emctl config agent listtargets | grep oracle_emd

[oracle@host01 bin]$ ./emctl config agent listtargets | grep oracle_emd
[host01.dt.stepintooracledba.com:3873, oracle_emd]

Now we will see the targets which are in Suspension State in this Host.

./emctl getmetric agent host01.dt.stepintooracledba.com:3873,oracle_emd,z#Tasks_in_Suspension

[oracle@host01 bin]$ ./emctl getmetric agent host01.dt.stepintooracledba.com:3873,oracle_emd,z#Tasks_in_Suspension
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
TaskName,SuspendedUntil,Points
oracle_listener:LISTENER_host01.dt.stepintooracledba.com:Response,292278994-08-17 02:12:55,2147483647
oracle_emd:host01.dt.stepintooracledba.com:3873:z#AgentStat_PingStats,292278994-08-17 02:12:55,2147483647
oracle_emd:host01.dt.stepintooracledba.com:3873:z#CurrentRequests,292278994-08-17 02:12:55,2147483647
oracle_emd:host01.dt.stepintooracledba.com:3873:z#AgentStat_OutStandingSeverities,292278994-08-17 02:12:55,2147483647
oracle_database:db01.stepintooracledba.com:activity_pending,292278994-08-17 02:12:55,2147483647
oracle_database:db01.stepintooracledba.com:dataguard_11gR2,292278994-08-17 02:12:55,2147483647
oracle_listener:LISdb01_host01.dt.stepintooracledba.com:Response,292278994-08-17 02:12:55,2147483647
oracle_emd:host01.dt.stepintooracledba.com:3873:z#LastPing,292278994-08-17 02:12:55,2147483647
oracle_database:db01.stepintooracledba.com:observer_11g,292278994-08-17 02:12:55,2147483647
oracle_emd:host01.dt.stepintooracledba.com:3873:z#AgentStat_UploadSendingQueueStatistics,292278994-08-17 02:12:55,2147483647
oracle_database:db01.stepintooracledba.com:Response,292278994-08-17 02:12:55,2147483647
oracle_emd:host01.dt.stepintooracledba.com:3873:z#UploadSystemStats,292278994-08-17 02:12:55,2147483647
[oracle@host01 bin]$


=====================================================================================================================

#########################################
# 5) Check Current Status of the Database Target
#########################################

./emctl status agent target db01.stepintooracledba.com,oracle_database | grep Response

[oracle@host01 bin]$ ./emctl status agent target db01.stepintooracledba.com,oracle_database | grep Response
Response      State                   n/a             UNDEFINED       Mon Dec 16 12:06:40 EST 2013
Response      Status                  n/a             UNDEFINED       Mon Dec 16 12:06:40 EST 2013
[oracle@host01 bin]$

=====================================================================================================================
 Response Status Shows as UNDEFINED
=====================================================================================================================

#########################################
# 6) Check Response Metric
#########################################

We will check whether response metric is scheduled for this database or not.

./emctl status agent scheduler | grep Response

[oracle@host01 bin]$ ./emctl status agent scheduler | grep Response
2014-01-15 22:07:30.370 : osm_instance:+ASM_host01.dt.stepintooracledba.com:Response
[oracle@host01 bin]$

=====================================================================================================================

#########################################
# 7) Collect the Response Metric Manually
#########################################

From Step 6, we can see that Response Metric is not scheduled for the database.

Running the response metric manually, causes the agent to perfom an immediate collection of the metric:-


./emctl control agent runCollection db01.stepintooracledba.com:oracle_database Response

[oracle@host01 bin]$ ./emctl control agent runCollection db01.stepintooracledba.com:oracle_database Response
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

[oracle@host01 bin]$

If no errors are returned by running the metric manually, refresh the console (by clicking on the 'refresh data' arrow at the right hand side of the top of

the console).

Now the Database Status is showing as Up & Running....

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Saturday, January 18, 2014

OGG-01668 PROCESS ABENDING, OGG-01930 Datastore error in 'dirbdb', OGG-01172 Discard file


One of our Oracle Golden Gate Replicat Process Got Abended when we tried to start Replicat Process got OGG-01930 error. We have done lot of search in google for the error and found out the actual error is different and its very simple.

Take a look into below steps for a simple fix.

##########################
## Error
##########################

2014-01-15 22:16:13  ERROR   OGG-01668  PROCESS ABENDING.
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.

### Full Error

2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
2014-01-15 22:16:13  ERROR   OGG-01172  Discard file (./dirrpt/DB2.dsc) exceeded max bytes (1000000000).
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
2014-01-15 22:16:13  ERROR   OGG-01668  PROCESS ABENDING.
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.

##########################
#  Error Occurred
##########################

Replicat Process got ABENDED in Golden Gate Version 11.2.1.0.1

##########################
## Command Executed
##########################

start replicat DB2

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Current Status of the Replicat
#########################################

GGSCI (host01) 9> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
ABE      RUNNING
REPLICAT    ABENDED     GG2         00:00:03      00:35:42
REPLICAT    RUNNING     GG3         00:00:00      00:00:01
REPLICAT    RUNNING     GGSE        00:00:00      00:00:08



=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

GGSCI (host01) 10> view report DB2

2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
2014-01-15 22:16:13  ERROR   OGG-01172  Discard file (./dirrpt/DB2.dsc) exceeded max bytes (1000000000).
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
2014-01-15 22:16:13  ERROR   OGG-01668  PROCESS ABENDING.
2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.

#########################################
# 3) Reproduce the Error
#########################################

Restarting the Abended replicat, throws below error and again goes back to ABENDED state.

2014-01-15 22:16:13  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.

=====================================================================================================================
If we can see from Step 2, We have received Error OGG-01172 that discard file has reached its max bytes
=====================================================================================================================

#########################################
# 4) Check Replicat Parameter Details
#########################################

GGSCI (host01) 11> view param DB2

DISCARDFILE ./dirrpt/DB2.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER on friday

GGSCI (host01) 12>

=====================================================================================================================
 From above we can see that Discard file Limit is set to 1 GB and its also set to Rollover on Every Friday
=====================================================================================================================

#########################################
# 5) Discard File Size
#########################################

cd /u01/app/goldgate/11.2/dirrpt/

-rw-rw-rw- 1 oracle oinstall 1000000103 Jan 15 22:16 DB2.dsc

[oracle@host01 dirrpt]$ du -sh DB2.dsc
955M    DB2.dsc
[oracle@host01 dirrpt]$

=====================================================================================================================
 Maximum Size of Discard File set is 1000 MB and discard file has reached 1000000103 bytes..
=====================================================================================================================

##########################
## Solution
##########################

Discard file has reached its maximum size set. So,

1) We can increase size of the discardfile to a higher value.

2) If above option is not feasible due to disk space shortage, best option is to delete the existing discard file as it contains all discarded records

since start of replicat process and all of them are not needed. You'll need only latest discarded records if you want to perform RCA for these records.

=====================================================================================================================
 Increasing Discard File Limit
=====================================================================================================================

edit param DB2

GGSCI (host01) 13> edit param DB2

2014-01-15 22:25:06  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.



GGSCI (host01) 14> view param DB2

DISCARDFILE ./dirrpt/DB2.dsc, APPEND, MEGABYTES 1500
DISCARDROLLOVER on friday

GGSCI (host01) 16> info DB2
REPLICAT   DB2       Last Started 2014-01-10 13:37   Status ABENDED
Checkpoint Lag       00:00:03 (updated 00:39:33 ago)
Log Read Checkpoint  File ./dirdat/pg000952
                     2014-01-15 21:46:32.000864  RBA 55114491


GGSCI (host01) 17>

GGSCI (host01) 17> start DB2
2014-01-15 22:26:18  WARNING OGG-01930  Datastore error in 'dirbdb': BDB0113 Thread/process 23555/1125325120 failed: BDB1507 Thread died in Berkeley DB library.
Sending START request to MANAGER ...
REPLICAT DB2 starting

GGSCI (host01) 18> info DB2
REPLICAT   DB2       Last Started 2014-01-15 22:26   Status RUNNING
Checkpoint Lag       00:00:03 (updated 00:39:47 ago)
Log Read Checkpoint  File ./dirdat/pg000952


=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================