Enterprise Manager, Errors and Bugs

OEM 13C Patching Agent: [ERROR- Failed to Update Target Type Metadata]

 , ,

While applying Patch to OEM13c, specifically to OMS Agent, I got this error when trying to start it back:

Collection Status                            : Collections enabled
Heartbeat Status       : OMS responded illegally [ERROR- Failed to Update Target Type Metadata]
Last attempted heartbeat to OMS              : 2018-04-17 12:12:51
Last successful heartbeat to OMS             : (none)
Next scheduled heartbeat to OMS              : 2018-04-17 12:13:21
---------------------------------------------------------------
Agent is Running and Ready
[oracle@oem13c oms]$ ./emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors. (OMS_DOWN)
[oracle@oem13c oms]$ ./emctl pingOMS
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: OMS sent an invalid response: “ERROR- Failed to Update Target Type Metadata”

Nice hãn?
I Found to MOS EM 13c Agent : pingOMS error: OMS sent an invalid response: “ERROR- Failed to Update Target Type Metadata” (Doc ID 2318564.1) saying:

“This particular issue is caused when any Agent Plugin is upgraded to a higher level than the OMS plugin.”

The solution according to MOS Doc is to rollback the Agent Plugins ahead to the OMS version. Checking it:
More“OEM 13C Patching Agent: [ERROR- Failed to Update Target Type Metadata]”

Fusion Middleware, Shell Scripts, Weblogic

Disk Space is not Released After Deleting Files

 , , , , ,

When deleting a large files, the file is deleted successfully but the size of the filesystem does not reflect the change.
The files was be deleted, but only restarting the jvm or java process released the disk space.
Usually occurs with log files.

The lsof command show the files opened in system.
For example: lsof |grep deleted

java 15138 oracle 2959r REG 253,3 5875027 131163 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1.out03422 (deleted)
java 15138 oracle 3054r REG 253,3 10480928 131166 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1-diagnostic-81.log (deleted)
java 15138 oracle 3062r REG 253,3 10479417 131200 /logs/soa_domain/WLS1_SOA1/WLS1_SOA1-diagnostic-82.log (deleted)

The command output shows pid, owner, file descriptor (fd), size and file.

Workaround:
If can’t restart the process, it is possible to force the system to de-allocate the space consumed by an in-use file by forcing the system to truncate the file.
$ echo > /proc/pid/fd/fd_number

Be careful not to truncate unwanted files.

In my case:
$ echo > /proc/15138/fd/2959
$ echo > /proc/15138/fd/3054
$ echo > /proc/15138/fd/3062

That’s all for today.

Database, Enterprise Manager

Monitoring Your Oracle Database With Grafana

 , ,

Hi everybody,

Let’s talk about Dashboarding Oracle Databases with Grafana.

I always felt the need of a graphical monitoring tool for basic database things such as volume of archives, back-up archives, state of services, offline disks, space of diskgroup, consum of UNDO, consum of TEMP, space of filesystem, space of every diskgroups in all clusters. OEM seems just too much complicated to give a simple online graphical dashboard for this.

So I developed a “collector” of data that sends the data to Influxdb and generate these graphs. Simple like that.

Have a look on how it looks like:

grafana1

grafana2

Ok, but I how did it?
Here it goes a piece of code:
More“Monitoring Your Oracle Database With Grafana”

Database, Shell Scripts

Shellscript & Oracle: Run Script for a List of Databases

 , , ,

Hey all!
Quick tip/script for today: How to run same script for a list of databases quickly?

In my case I have same password/user in all databases, but in case you haven’t you can make a similar awk command to retrieve users and passwords from a file.

The list:

$: cat /tmp/dbs.cfg
db01
db02
db03
testdb

The script:

for DBSID in ${*-$(awk -F: '!/^#/ {print $1}' /tmp/dbs.cfg}
do
    print "
        connect user/password@${DBSID}
        @script_to_run.sql
        exit " |
    sqlplus /nolog >> /tmp/output_test.log
done

Hope it helps!
Cheers!

Database, Errors and Bugs

Opening New PDB 12.1.0.2 – Warning: PDB altered with errors

Hi,
Some time ago, after creating a pluggable database from seed, it was simply not open, check it out:

SQL> alter pluggable database mypdb open;

Warning: PDB altered with errors.

When checking for the pdb status on v$pdb, pdb, I found it was in restricted mode. Checking the alert.log, not a single error to help to solving the issue, as you can see:

CREATE PLUGGABLE DATABASE mypdb ADMIN USER PDBADMIN identified by *
Fri Feb 02 16:06:05 2018
APEX_040200.WWV_FLOW_TEMPLATES (JAVASCRIPT_CODE_ONLOAD) - CLOB populated
Fri Feb 02 16:06:37 2018
****************************************************************
Pluggable Database mypdb with pdb id - 7 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
This instance was first to open pluggable database mypdb (container=7)
Database Characterset for mypdb is WE8MSWIN1252
Deleting old file#2 from file$
Deleting old file#4 from file$
Adding new file#108 to file$(old file#2)
Adding new file#109 to file$(old file#4)
Successfully created internal service mypdb at open
ALTER SYSTEM: Flushing buffer cache inst=1 container=7 local
****************************************************************
Post plug operations are now complete.
Pluggable database mypdb with pdb id - 7 is now marked as NEW.
****************************************************************
Completed: CREATE PLUGGABLE DATABASE mypdb ADMIN USER PDBADMIN identified by *
alter pluggable database mypdb open
Fri Feb 02 16:06:52 2018
This instance was first to open pluggable database mypdb (container=7)
Pluggable database mypdb dictionary check beginning
Pluggable Database mypdb Dictionary check complete
Database Characterset for mypdb is WE8MSWIN1252
Fri Feb 02 16:07:07 2018
Opening pdb mypdb (7) with no Resource Manager plan active
Fri Feb 02 16:07:08 2018
Logminer Bld: Build started
Resize operation completed for file# 108, old size 296960K, new size 307200K
Resize operation completed for file# 108, old size 307200K, new size 317440K
Fri Feb 02 16:07:15 2018
Logminer Bld: Done
Pluggable database mypdb opened read write
Completed: alter pluggable database mypdb open

In the end of the day, discovered this is a match to MOS Bug 19174942 – After upgrade from 12.1.0.2 new cloned PDB opens with warnings in restricted mode (Doc ID 19174942.8).

To fix the problem you need create the missing default tablespaces for each common user (you can see that from DBA_USERS view in ROOT). In my case, it was the tablespace USERS (didn’t existing by looking up on dba_data_files view on the pdb). Seems it was not created correctly during create of the pluggable. So, to fix it:

create tablespace users datafile '+DGDATA/MYCDB/DATAFILE/user_01.dbf' size 100m;

Then you just have to restart your PDB and it will open without problems:

SYS@MYCDB>alter session set container=CDB$ROOT;

SQL> alter pluggable database mypdb close;

Pluggable database altered.

SQL> alter pluggable database mypdb open;

Pluggable database altered.

See you in the next post!

Database, GoldenGate, Tools

GGATE ABBENDED: ORA-00308: Cannot Open Archived Log

 , , , , ,

Hi all!
Ok, so this is one of the most common issues for GGate administration. How to solve it? Easy thing.

First let’s understand what it means: It means the redologs don’t have the required information (assuming integrated) and you have already deleted archivelogs the extract needs. Why? Probably because you already backed up those archivelogs and they were not needed for the database anymore.

Unfortunately we don’t have any kind of ARCHIVELOG DELETION POLICY to guarantee extracts had already read it, like we have for Dataguard. So, what can we do?

Restore the missing archivelogs.

But first let’s confirm on the errors. Some examples:

ERROR OGG-00446 Oracle GoldenGate Capture for Oracle, ext1.prm: Opening ASM file +ARCH/2_11055_742755632.dbf in DBLOGREADER mode: (308) ORA-00308: cannot open archived log '+ARCH/2_11055_742755632.dbf' ORA-17503.

or

ERROR OGG-01028 Oracle GoldenGate Capture for Oracle,ext1.prm: Getting attributes for ASM file +ARCH/2_86720_716466928.dbf,

SQL : (15056)

ORA-15056: additional error message ORA-15173: entry '2_86720_716466928.dbf' does not exist in directory '/...


SOLUTION:

Restore all archive logs starting from recovery checkpoint until the current checkpoint and restart the extract:
More“GGATE ABBENDED: ORA-00308: Cannot Open Archived Log”

Fusion Middleware, Weblogic

Error JPS-06516: Failed to get credential store in Webcenter Portal

 , ,

After startup NodeManager wrongly started from the root account, the following error occurs:

<Apr 9, 2018 8:34:41 AM BRT> <Warning> <oracle.jps.credstore> <JPS-01050> <Opening of wallet based credential store failed. Reason java.io.IOException: PKI-02002: Unable to open the wallet. Check password. >
<Apr 9, 2018 8:34:41 AM BRT> <Warning> <oracle.webcenter.framework.service.WebCenterConfig> <WCS-43054> <An error occured while trying to lookup connection Pagelet Producer.
javax.naming.NamingException [Root exception is oracle.adf.share.jndi.ConnectionException: java.lang.IllegalArgumentException: oracle.security.jps.service.keystore.KeyStoreServiceException: JPS-06516: Failed to get credential store.

Verify that the user owns the managed server has access to all files and directories in the temporary directory, specifically oracle-dfw* directories.

arquivos-oracle-dfw-webcenter

If there are files and / or directories with different owners of the managed server owner that presents the problem, back up the existing files and delete or move them.

That’s all for today.

Database, Errors and Bugs

Scheduler Job Start Time Changed After DST Change

 ,

Hi all,
Some time ago a client asked me to check why a Job started running in a different time. This was a week after the DST change. Easy guess, right?

The job was scheduled using timezone offset (PM-04:00) instead of timezone region. Considering the DST change, the offset changed as well (-5:00), but not the job schedule.
To fix the job time definitely, I changed schedule to use timezone region that database is in. Check below the investigation and solution:

SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB';

OWNER			 JOB_NAME    REPEAT_INTERVAL	LAST_START_DATE                      NEXT_RUN_DATE
---------- ---------- ----------------- -----------------------------------  --------------------
GREPORA		 MYJOB      FREQ=DAILY;			   08-NOV-17 09.00.00.330739 PM -04:00 08-NOV-17 09.00.00.300000 PM -04:00
							  
												  
SQL> SELECT * FROM   dba_scheduler_global_attribute  WHERE  attribute_name = 'DEFAULT_TIMEZONE';

ATTRIBUTE_NAME	  VALUE
----------------- ---
DEFAULT_TIMEZONE  EST5EDT


SQL> SELECT tzabbrev, TZ_OFFSET(tzname), tzname FROM V$TIMEZONE_NAMES 
WHERE tzname IN ('EST', 'EDT') OR tzabbrev IN ('EST', 'EDT') ORDER BY 1,2;

TZABBREV	TZ_OFFS TZNAME
--------- ------- ---------------------------
EDT       -04:00  America/Santo_Domingo
EDT       -05:00  America/Fort_Wayne
EDT       -05:00  America/Grand_Turk
EDT       -05:00  America/Indiana/Indianapolis
EDT       -05:00  America/Indiana/Marengo
EDT       -05:00  America/Indiana/Petersburg
EDT       -05:00  US/Michigan
EDT       -05:00  America/Detroit
EDT       -05:00  US/Eastern
EDT       -05:00  US/East-Indiana
EDT       -05:00  Jamaica
EDT       -05:00  EST5EDT
EDT       -05:00  Canada/Eastern
EDT       -05:00  America/Toronto
EDT       -05:00  America/Thunder_Bay
EDT       -05:00  America/Port-au-Prince
EDT       -05:00  America/Pangnirtung
EDT       -05:00  America/Nipigon
EDT       -05:00  America/New_York
EDT       -05:00  America/Nassau
EDT       -05:00  America/Montreal
EDT       -05:00  America/Louisville
EDT       -05:00  America/Kentucky/Monticello
EDT       -05:00  America/Kentucky/Louisville
EDT       -05:00  America/Jamaica
EDT       -05:00  America/Iqaluit
EDT       -05:00  America/Indiana/Vevay
EDT       -05:00  America/Indiana/Vincennes
EDT       -05:00  America/Indiana/Winamac
EDT       -05:00  America/Indianapolis
EDT       -06:00  America/Indiana/Tell_City
EDT       -06:00  America/Cancun
EST       +10:00  Australia/Queensland
EST       +10:00  Australia/Lindeman
EST       +10:00  Australia/Brisbane
EST       +10:30  Australia/Broken_Hill
EST       +10:30  Australia/Yancowinna
EST       +11:00  Australia/Currie
EST       +11:00  Australia/Canberra
EST       +11:00  Australia/ACT
EST       +11:00  Antarctica/Macquarie
EST       +11:00  Australia/Hobart
EST       +11:00  Australia/LHI
EST       +11:00  Australia/Lord_Howe
EST       +11:00  Australia/Melbourne
EST       +11:00  Australia/NSW
EST       +11:00  Australia/Sydney
EST       +11:00  Australia/Tasmania
EST       +11:00  Australia/Victoria
EST       -04:00  America/Antigua
EST       -04:00  America/Moncton
EST       -04:00  America/Santo_Domingo
EST       -05:00  US/Eastern
EST       -05:00  America/Grand_Turk
EST       -05:00  America/Atikokan
EST       -05:00  Jamaica
EST       -05:00  EST5EDT
EST       -05:00  EST
EST       -05:00  America/Indiana/Indianapolis
EST       -05:00  Canada/Eastern
EST       -05:00  America/Toronto
EST       -05:00  America/Thunder_Bay
EST       -05:00  America/Resolute
EST       -05:00  America/Indiana/Marengo
EST       -05:00  America/Indiana/Petersburg
EST       -05:00  America/Cayman
EST       -05:00  America/Indiana/Vevay
EST       -05:00  America/Indiana/Vincennes
EST       -05:00  America/Indiana/Winamac
EST       -05:00  America/Indianapolis
EST       -05:00  America/Iqaluit
EST       -05:00  America/Jamaica
EST       -05:00  America/Kentucky/Louisville
EST       -05:00  America/Kentucky/Monticello
EST       -05:00  US/Michigan
EST       -05:00  America/Louisville
EST       -05:00  America/Coral_Harbour
EST       -05:00  America/Detroit
EST       -05:00  America/Fort_Wayne
EST       -05:00  America/Montreal
EST       -05:00  America/Nassau
EST       -05:00  America/New_York
EST       -05:00  America/Nipigon
EST       -05:00  America/Panama
EST       -05:00  America/Pangnirtung
EST       -05:00  America/Port-au-Prince
EST       -05:00  US/East-Indiana
EST       -06:00  US/Central
EST       -06:00  CST
EST       -06:00  America/Rankin_Inlet
EST       -06:00  America/Merida
EST       -06:00  America/Menominee
EST       -06:00  America/Managua
EST       -06:00  America/Knox_IN
EST       -06:00  America/Indiana/Tell_City
EST       -06:00  America/Indiana/Knox
EST       -06:00  America/Chicago
EST       -06:00  America/Cancun
EST       -06:00  US/Indiana-Starke
EST       -07:00  America/Cambridge_Bay

100 rows selected.


SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name                 => 'GREPORA.MYJOB',
   attribute            => 'start_date',
   value                => TO_TIMESTAMP_TZ('09-11-2017 21:00:00 EST5EDT' ,'DD-MM-YYYY HH24:MI:SS TZR'));
END;
/

PL/SQL procedure successfully completed.

SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB';

OWNER			 JOB_NAME    REPEAT_INTERVAL	LAST_START_DATE                  NEXT_RUN_DATE
---------- ---------- ----------------- -------------------------------  --------------------
GREPORA		 MYJOB      FREQ=DAILY;			  08-NOV-17 09.00.00.3 09-NOV-17   09-NOV-17 09.00.00.0

Hope this helps you!
Cheers!

Java | DevOps | APEX | Scripts, PostgreSQL

Managing database changes with Sqitch

 , , , , , , , ,

Nowadays everybody talks about continuous integration, automated test tools, and stuff like that. But almost every time it’s about testing applications. Let’s talk a little about Sqitch, “a database change manager” as they use to call themselves.

Let’s say you’re starting a new project and want to ensure every database change are managed and secured with tests? Just like with an app, you can write tests to ensure everything works and you can have a “revert” method for the changes too. Suppose you are changing a field type and start creating a backup column (fallback ;)), but something goes wrong on the meantime and for some reason, you can’t do a simple rollback… revert it and be happy.

What if your CI says that the application it’s fine and can be released to the production server but “a wild bug appears” and you NEED to revert to a previous version of your service, easy huh? With Docker and one command line, the service is up and running again. But what about the database changes your team did? Just call the revert and be happy :).

Have I mentioned that you can have multiples connection URI at the project and have different environments to apply the changes with a single command?!. Let’s see a few commands to create a simple MySQL database…

Create a folder, initialize GIT in it and initialize the Sqitch project.

mkdir awesomeapp
cd awesomeapp
git init .
sqitch init flipr --uri https://github.com/theory/sqitch-mysql-intro/ --engine mysql

Now you have a ready to go sqitch structure. Now let’s do some basic creates.

sqitch add createcustomeruser -n 'Creation of Customer table'

Add the create table script to the file inside the “deploy” dir.

CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name VARCHAR(50)
);

Add the verify script, can be a simple select. Add the revert script, guess what’s the command, and after all, you can run the commands below and see the result.

sqitch deploy db:mysql://root@/awesomeapp
sqitch verify db:mysql://root@/awesomeapp
sqitch revert db:mysql://root@/awesomeapp

This was a quick explanation about Sqitch so go ahead and read more at their docs and tutorials, they are pretty good.

Cloud Services | Exadata | Linux, Oracle Engineered Systems | Exadata

Verifying topology of Exadata

 ,

Hey all!
Some time ago I needed to check topology of a client’s Exadata due a network issue and made a very useful note. Sharing with you now. 😀

This and other cool commands can be found here: Network Diagnostics information for Oracle Database Machine Environments (Doc ID 1053498.1)

# /opt/oracle.SupportTools/ibdiagtools/verify-topology -t quarterrack

Newer versions don’t require -t option.
In case of halfrack, -t halfrack should be used in my case.

Ok, but how to know it? You can have it from here:

[root@greporaexa onecommand]# grep -i MACHINETYPES databasemachine.xml
[MACHINETYPES]X4-2 Eighth Rack HC 4TB[/MACHINETYPES]

Hope it helps! 🙂