Wednesday, November 27, 2019

Changing SGA & PGA memory for Oracle

The following can give good info on target memory advice after your system has been running for some time. It is cleared during restart
 select * from V$MEMORY_TARGET_ADVICE ;

BACKUP your SPFILE before doing any radical changes.
Example location ~ D:\oracle\product\12.1.0\dbhome_1\database

To set pga to 1 gb and sga to 3 g, enter the following commands:

ALTER SYSTEM SET pga_aggregate_target=1G SCOPE = SPFILE;

You will need to restart your Oracle database.

And, Bob's your uncle, you should have your PGA and SGA set to the new target values.

Tuesday, August 06, 2019

WebSphere Application Server Network Deployment 7.0 security.xml corrupted.

On one of our internal WebSphere application server network deployment v7 environments, I hit the following error.

The error was: Cannot get security object from WCCM

Security.xml appeared to have been corrupted. The security.xml file was blank.

To resolve this, I had to copy a security.xml file from another server, and then edit it to 
  1. replace the original server name with the name of my corrupted server 
  2. change it to disable security altogether as instructed here.
    1. NOTE: I had to use the 2nd method mentioned, since my Dmgr was stopped. 
Thanks so much to Chandan Kumar of GeekFlare for posting this very useful procedure :-) 

In case something should happen to GeekFlare,  I am copying it below. Please attribute all credit to Chandan for this! 

Tuesday, July 23, 2019

SQL Server View Definition Truncating

Recently I was trying to query a view definition.
I tried a couple different methods, including sys.sql_modulesOBJECT_DEFINITION (). Both were, for reasons not immediately apparent, truncating the definition of one particular view.

I tried the obvious things like piping the output directly to a file using sqlcmd and checking my output length in SSMS.

I noticed, however, that the view in question was truncating right after the first union key word.

On a hunch, I decided to search for unprintable characters.

Stack Exchange presented me with this (unaccepted) answer from Ilya Shamuratov to a question about removing non-printable ascii characters.

CREATE FUNCTION [dbo].REPLACE_UNPRINT_CHARS(@VarString nvarchar(256))  
RETURNS nvarchar(256) 
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@VarString, CHAR(0), ''), CHAR(1), ''), CHAR(2), ''), CHAR(3), ''), CHAR(4), ''), CHAR(5), ''), CHAR(6), ''), CHAR(7), ''), CHAR(8), ''), CHAR(9), ''), CHAR(10), ''), CHAR(11), ''), CHAR(12), ''), CHAR(13), ''), CHAR(14), ''), CHAR(15), ''), CHAR(16), ''), CHAR(17), ''), CHAR(18), ''), CHAR(19), ''), CHAR(20), ''), CHAR(21), ''), CHAR(22), ''), CHAR(23), ''), CHAR(24), ''), CHAR(25), ''), CHAR(26), ''), CHAR(27), ''), CHAR(28), ''), CHAR(29), ''), CHAR(30), ''), CHAR(31), ''), CHAR(127), ''), CHAR(128), ''), CHAR(129), ''), CHAR(130), ''), CHAR(131), ''), CHAR(132), ''), CHAR(133), ''), CHAR(134), ''), CHAR(135), ''), CHAR(136), ''), CHAR(137), ''), CHAR(138), ''), CHAR(139), ''), CHAR(140), ''), CHAR(141), ''), CHAR(142), ''), CHAR(143), ''), CHAR(144), ''), CHAR(145), ''), CHAR(146), ''), CHAR(147), ''), CHAR(148), ''), CHAR(149), ''), CHAR(150), ''), CHAR(151), ''), CHAR(152), ''), CHAR(153), ''), CHAR(154), ''), CHAR(155), ''), CHAR(156), ''), CHAR(157), ''), CHAR(158), ''), CHAR(159), ''), CHAR(160), ''); 
 I used this directly in my query, and voila! I get the entire definition back.
NOTE that, for my implementation to work, I needed to replace with spaces ( ' ' ) rather than just using the replace function to delete (replacing with '')

I think his idea of a function that can be used is a good idea, and I may implement something like this in my toolkit.

Thanks, Ilya :-)

Changing SGA & PGA memory for Oracle

The following can give good info on target memory advice after your system has been running for some time. It is cleared during restart  s...