EM Grid Control Agent - The Culprit of Renewing Weblogic Server SSL Certificates

We were using VeriSign SSL certificates for our Weblogic admin and managed servers. It seemed easy to enable the SSL: create the keystore -> generate then send the SSL certificate requests of the servers -> receive the SSL certificates -> import the Verisign CA, issuing as well as the server certificates into the keystore -> import the Verisign CA and issuing certificates to update the keystore configuration from the Weblogic admin console to point to the keystore for the servers -> restart the servers

Then it was time to renew the SSL certificates. However my company decided to switch to Microsoft certificates using internal CA and issuing certificates, since these servers are only used inside the company. Following above steps I renewed the certificates, but on starting the Weblogic servers, I got CERTIFICATE UNKNOWN warning messages in the logs.

 

It turned out that I also need to import the Microsoft CA and issuing certificates to EM Grid Control agent keystore, which is under $AGENT_HOME/sysman/config/montrust/AgentTrust.jks and the keystore password is welcome by default. It is because the EM Grid Control agent keystore does have the VeriSign CA certificate by default, but doesn’t have Microsoft CA certificate.

EM Grid Control - The Culprit of Changing Weblogic Admin Password

I am sure different people have different experience on changing Weblogic Admin Password – it depends on your Weblogic server and domain configuration. There is a very good step by step note on how to change Weblogic Admin Password, which lists all possible issues and gives solutions:


http://weblogicserveradministration.blogspot.in/2012/10/how-to-change-reset-weblogic-admin-user.html

However in my case, I faced an issue that no matter what I tried, I got an error: 5 invalid login attempts, locking account for 30 minutes. Eventually I figured out there is a preferred credential configured in EM Grid Control that uses Weblogic admin account to monitor the Weblogic domain. After also changing the password in the EM Grid Control preferred credential, the issue was resolved.

Another note is to make sure you take a cold backup before trying to change the Weblogic admin password, so you can also restore back if things go wrong. Cheers!

Issue of Concatenating DATE Variables into a Single String in the Dynamic SQL

When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.


You do need to specify bind variables with dynamic SQL, in clauses like WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements.

Although the above is the best practice from Oracle, it seems an issue of concatenating DATE variables into a single string in the dynamic SQL - NOT an issue of concatenating NUMBER variables though. Using a bind variable to pass in the DATE variable worked.

The following illustrates the issue, although there is no need to use dynamic SQL there:

SQL> CREATE OR REPLACE PROCEDURE TEST_PROC(This_customer_id IN NUMBER, This_date IN DATE) IS
2 num_rows NUMBER(9);
3 my_date DATE;
4 BEGIN
5 EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM ORDERS WHERE CUSTOMER_ID
= ' || This_customer_id || ' AND ORDER_DATE >= :my_date' INTO num_rows USING This_date;
6 DBMS_OUTPUT.PUT_LINE(num_rows);
7 END TEST_PROC;
8 /

Procedure created.

SQL> EXEC TEST_PROC(101,SYSDATE-1000);
81005
PL/SQL procedure successfully completed.

SQL> EXEC TEST_PROC(101,'24-SEP-09');
81005
PL/SQL procedure successfully completed.


SQL> CREATE OR REPLACE PROCEDURE TEST_PROC(This_customer_id IN NUMBER, This_date IN DATE) IS
2 num_rows NUMBER(9);
3 --my_date DATE;
4 BEGIN
5 EXECUTE IMMEDIATE 'SELECT COUNT(0) FROM ORDERS WHERE CUSTOMER_ID = ' ||
This_customer_id || ' AND ORDER_DATE >= ' || This_date INTO num_rows;
6 DBMS_OUTPUT.PUT_LINE(num_rows);
7 END TEST_PROC;
8 /

Procedure created.

SQL> EXEC TEST_PROC(101,SYSDATE-1000);

BEGIN TEST_PROC(101,SYSDATE-1000); END;
*
ERROR at line 1:
ORA-00904: "SEP": invalid identifier
ORA-06512: at "ABC.TEST_PROC", line 5
ORA-06512: at line 1

The Correct ISNUMERIC Function in Oracle

1. In the function, I use REPLACE(UPPER()) to replace the 'E' with 'A', which fixes the issue with strings like '1234e2', otherwise Oracle considers it as a number, i.e. 123400
2. I prefer to return NUMBER instead of BOOLEAN, which allows me to use the function in DML SQL statements directly.
-----------------------------------------------------------------------------
FUNCTION ISNUMERIC (This_str IN VARCHAR2) RETURN NUMBER
IS
  v_dummy NUMBER;
BEGIN
  v_dummy := TO_NUMBER(REPLACE(UPPER(This_str),'E','A'));
  RETURN 0;
  EXCEPTION WHEN OTHERS THEN
    RETURN 1;
END;
/

However are Numbers and Characters Stored in Oracle

1. For integers others than those in tens (i.e. 10, 100...), 1-99 is stored using 2 bytes, 101~999 using 3 bytes...
2. For integers in tens, their trailing 0s are not used any extra bytes. E.g. 100000000 still uses 2 bytes. Not sure how does Oracle achieve it?
3. For characters, each character is stored using 1 bytes.
4. So if you need to store single digit like '0' or '1' indicator, better to store it as VARCHAR2(1), not NUMBER(1).

You can use vsize to verify.

How does DataStage Project Level Auto-Purge Job Logs Work

1. The job has to be run. I.e. if the job isn't run, its old logs will not be purged.
2. The job has to run successfully. E.g. if the job is aborted, its old logs will not be purged.

Someone mentioned to me that the project level auto-purge job logs only affects those jobs imported after auto-purge change. This doens't appear to be the behaviour with the version 8.5 I am working with.

What is It Doing - Select Count(*) on a Table

If there are indexes on the table, it will do an index full scan (small index) or index fast full scan (large index).

But which index will be used by the optimizer? The primary key index, or the index on the column that in the COUNT()?

The answer is it will use the smallest index on the table. Take a look the execution plan and then verify the index segment is the smallest one.