Friday, February 26, 2021

Pooled Connection Request timeout

There is probably some code that you have out there that is not properly disposing of some objects. This will cause Oracle to hold on to connections that are not actually in use causing you to run out of available connections in the pool. Restarting IIS solves it because it kills all those connections.

Keep seperate pool for every hosted application and monitor which application is taking resources and report the same to respective product programmer.

Tuesday, February 23, 2021

request channel timed out while waiting for a reply after

Check proxy setting on that machine.


Sometimes dns is not able to resolve address so try IP instead of using name in local ldaddonmodules config file.

Thursday, February 11, 2021

External Table vs Global Temporary Table GTT

You want to read big size text file or any file from OS level in one go then use external table.

Global temp tables is used when you want to store data on fly in temporary table from your plsql cursor or vice versa and later insert into main tables.

GTT is faster in comparison to External table.
You can create index on GTT not on external table.

All depends on usage.

Sunday, February 7, 2021

Web Mobile Login page security | Penetration Testing | VAPT

Don't allow special character in user ID box.

Never display Ora- or any .net message on error. It should be user defined.

Also Sensitive information like server details should not be transferred in plain text between client and server.

JSON format

JSON is a format to store and exchange data between client and server. It is very simple,light weight, compatible with all popular programming languages.

How third party web or mobile app connect with your backoffice database?

For that we develop an API and host at an application server IIS. Which will communicate (request and response) between client(third party mobile, web) and server(Backoffice database) through JSON format.

Friday, February 5, 2021

Change Oracle Profile idle Time | Application Will disconnect if it is IDLE | Sniped Session

select * from dba_profiles;


select profile, limit from DBA_PROFILES

where profile = 'DEFAULT'

and resource_name = 'IDLE_TIME';


select profile, limit from DBA_PROFILES

where profile <> 'DEFAULT'

and resource_name = 'IDLE_TIME';




alter profile BOPOLICY LIMIT IDLE_TIME 30;



alter profile DEFAULT LIMIT IDLE_TIME 30;



ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;



It is changed to 30 min of idle time. The application will disconnect if it is idle for 30.

Status of session will be marked as sniped.



TO clean sniped session, need to schedule job to kill sniped session. 



CREATE OR REPLACE PROCEDURE "FLUSH_SNIPED_SESSION" 

AS

BEGIN

    FOR X IN (

            SELECT inst_id,SID, SERIAL#

            FROM GV$SESSION

            WHERE USERNAME IS NOT NULL

             AND STATUS='SNIPED'

        ) LOOP

        EXECUTE IMMEDIATE 'alter system disconnect session '''|| X.SID

                     || ',' || X.SERIAL# || ',@' || X.inst_id || ''' immediate';

    END LOOP;

END;

/




BEGIN

DBMS_SCHEDULER.CREATE_JOB(job_name        => 'FLUSH$SNIPED$SESSION',

                          job_type        => 'STORED_PROCEDURE',

                          job_action      => 'FLUSH_SNIPED_SESSION',

          start_date      => '10-MAR-13 10:00.00.00 AM ASIA/CALCUTTA',

                          repeat_interval => 'freq=minutely; interval=5;byhour=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23',

                          end_date        => NULL,

                          enabled         => TRUE,

                          comments        => 'Flush Sniped Session Kshitij Agarwal');

END;

/



exec dbms_scheduler.run_job('FLUSH$SNIPED$SESSION');




Tuesday, February 2, 2021

convert date to number

select to_number(to_char(sysdate, 'ddmmyyyy')) from dual;

Thursday, January 28, 2021

ora-27452 ora-00972

More than 30 character not allowed for job name.
So please keep smaller job name.

Thursday, January 14, 2021

ORA-29273 ORA-06512 ORA-29263 HTTP protocol error

 ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1722

ORA-29263: HTTP protocol error



following run

select utl_http.request('http://192.168.100.85/hii1111111111111111111111111111111111111111111111111111111111111111111188888888888888888888888888888888888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii7777766666iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuiuu') from dual;



following give error - ORA-29273 ORA-06512 ORA-29263 HTTP protocol error because of enter line character


select utl_http.request('http://192.168.100.85/hii111111111111111111111111111111111111111111111111111111111111111111118888888888888888888888888888888888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889999999999999999999999999999999999999999999999999999999999999999999999999999999988888888888888888888888888888888888888888888999999999999999999999999999999999999999999999999999999999999999999999999999999998888888888888888888888888888888888888888888899999999999999999999999999999999999999999999999999999999999999999999999999999999888888888888888888888888888888888888888888889

9999999999999999999999999999999999999999999999999999999999999999999999999999999iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii7777766666iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuiuu') from dual;


Wednesday, September 23, 2020

private dblink

DB ldccm
User ldccm
Private Dblink cmldlink connect to LD database

Now how user will access ldfibs table of other database using private link.


Create view vwldfibs as select * from ldfibs@cmldlimk;

Grant view vwldfibs to user1;


Friday, September 18, 2020

Calculating ages in years

Calculating ages in years isn't as easy as

( current date - birth date ) / 365

@connor_mc_d has a solution - convert the dates to numbers:

trunc ((
 to_number (to_char ( sysdate, 'YYYYMMDD' )) - 
 to_number (to_char ( birth, 'YYYYMMDD' ))
 ) / 10000
)

https://t.co/gVoEijf874 https://t.co/6iSyzBXdUh

Great thanks Connor Sir

Monday, September 14, 2020

Gather Stats of Table Partition Latest/Current Month Partition

 



BEGIN

 FOR CUR_REC IN (select * from (select table_owner,table_name,partition_name,partition_position,last_analyzed from dba_tab_partitions where table_name  ='POWEROFATTORNEYSTOCKS' order by partition_position desc) where rownum=1) LOOP

    BEGIN

 EXECUTE IMMEDIATE 'begin dbms_stats.gather_table_stats(ownname =>''LDBO'' ,Tabname =>''' || cur_rec.table_name || ''',Partname =>''' || cur_rec.partition_Name || ''',cascade => true, DEGREE=>DBMS_STATS.DEFAULT_DEGREE); end;';

    EXCEPTION

      WHEN OTHERS THEN

        NULL;

    END;

  END LOOP;

END;

/



Kill Third Party Tool Like PLSQL Developer Session



BEGIN

 FOR CUR_REC IN (select ss.username,ss.terminal,ss.sid,ss.serial#,ss.inst_id from gv$session ss where module='PL/SQL Developer') LOOP

  BEGIN

 EXECUTE IMMEDIATE 'alter system disconnect session''' || CUR_REC.sid || ',' || CUR_REC.serial# || ',@' || CUR_REC.inst_id || ''' immediate';

 EXCEPTION

      WHEN OTHERS THEN

        NULL; 

    END;

  END LOOP;

END;

/


Thursday, August 27, 2020

ORA-12018: following error encountered during code generation for ..ORA-00979: not a GROUP BY expression

 ORA-12018 ORA-00979 ORA-06512

When we create Materialized report view it is successfully created and reports are coming properly.

But when we do complete refresh using below mentioned command it is showing an error

SQL> exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;

begin dbms_mview.refresh('mv_rkcapitaldashboard','C'); end;

ORA-12018: following error encountered during code generation for "LDBO"."MV_RKCAPITALDASHBOARD"

ORA-00979: not a GROUP BY expression

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994

 

Bug

Oracle Version 11.2.0.4

Solution

alter session set "_complex_view_merging"=false;

or add following in query

/*+ opt_param('_complex_view_merging','false') */

or try following in SQL

/*+ NO_QUERY_TRANSFORMATION */ 

exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;



Friday, August 21, 2020

The message could not be dispatched because the service at the endpoint address 'net.pipe://localhost/ServiceName' is unavailable for the protocol of the address

Getting the error, The message could not be dispatched because the service at the endpoint address 'net.pipe://localhost/ServiceName' is unavailable for the protocol of the address.


If you use net.pipe protocol, you should check if "Net.Pipe Listener Adapter" service is running in Services management console.

Then, try using ServiceModelReg.exe -r to repair all WCF components.

And Check whether you enable net.pipe protocol in IIS.
give all permissions for the service files and folders to the account under which the app pool for the website is configured to run.

https://www.google.com/amp/s/rohitguptablog.wordpress.com/2011/06/16/configuring-wcf-service-with-nettcpbinding/amp/

Thursday, August 20, 2020

Oracle 19c Security parameters Cyber security

Wednesday, August 12, 2020

IIS Web Security | Cyber Security | VAPT | Best Practices


1) Don't use default location c: to host application
2) disable default page
3) disable directory browsing
4) Disable the Http OPTIONS Method
5) Enable Dynamic IP Address Restrictions
The Dynamic IP to prevent DoS attacks.
6) Ensure that you keep up to date with the latest updates and security patches. The majority of hacks affecting the web server occur on unpatched servers.
7) Disabling-IIS-Web-Banner-And-Other-IIS-Headers




Please make sure following IIS security settings should be configured at your public hosting customer facing application.

Remove the default page or stop/disable from the IIS server

Open IIS Manager
Click the server name
Double click on Default Document
On the right side, click “Disable”

 Or redirect default page to application login page
Using the Web site redirect function build in IIS Manager.
1. Start IIS Manager from Administrator Tools.
2. Expand the ServerName, and then expand the Sites
3. Double Click on the Default Web Site to open up the Default Web Site properties.
4. Under section"IIS", double click on "HTTP Redirect".
5. Check "Redirect requests to this destination", and fill in the Web Application link in the box. For example "Https://abc.com/ldclientlevelH"
6. Check Only redirect requests to content in this directory (not subdirectories)"
7. On the right side pan, click Apply.
8. Since the you Websites are under Default Web Sites, this change will be applied to any websites that's under Default Websites. Please go to each website and do the HTTP Redirect, make sure None of the box is checked.
9. Reset IIS.


Disabling IIS Directory Browsing or Listing

The attacker can display the whole list of files in the directories. These directories include sensitive files such as password files, database files, FTP logs etc. It is obvious that this information was not intended for public view.

1. Go to RUN
2. Type inetmgr and click Enter to open IIS console
3. Select Application Directory under Default website or other Website.
4. In the right hand side panel double click on the "Directory Browsing" option.
5. Click on the Disable button

How do you stop users directly accessing files on a website in IIS in that directory
Directory is not accessible now after disabling directory browsing but if hacker knows the file name then file can be accessed.
URL Rewrite
Or
If anyone directly come to any page it should be redirect to authentication page 

Disabling-IIS-Web-Banner-And-Other-IIS-Headers

For security purposes, it may be desirable to disable the X-ASPNET-VERSION and X-Powered-By HTTP Headers.
 
The HTTP header "X-Powered-By" reveals the version of IIS being used on the server. This can be disabled by:
1. Open the IIS Manager
2. Select the website that website Server is running under.
3. Select "HTTP Response Headers"
4. Select the "X-Powered-By" HTTP Header and select "Remove"
The Http Header "X-ASPNET-VERSION" reveals the version of ASP.NET being used by the website Server application pool. This can be disabled by:
 
1. Open the web.config file for website Server (located in the root directory for the website).
2. Just after the <system.web> tag add this: <httpRuntime enableVersionHeader="false" />
3. Save the file.
 
Note: The SERVER header variable should not be removed as it will cause certain functionality within website Server to break. 




Disable the OPTIONS Method
The OPTIONS method provides a list of methods that are supported by the web server. Although this might seem beneficial, it also provides useful information to the attacker at the reconnaissance stage. Therefore, we recommend that you disable the OPTIONS method completely. This can be done by denying the OPTIONS verb in HTTP verb request filtering rules in IIS.

Open the IIS Manager
Select the name of the machine to configure this globally (or change to the specific web site for which you need to configure this)
Double click on Request Filtering
Change to the HTTP Verbs tab
From the Actions pane, select Deny Verb
Insert OPTIONS in the Verb field and click on OK to save changes




Enable Dynamic IP Address Restrictions
The Dynamic IP Restrictions module helps to block access to IP addresses that exceed a specified number of requests and thus helps prevent denial-of-service (DoS) attacks. This module will inspect the IP address of each request sent to the web server and will filter these requests in order to temporarily deny IP addresses that follow a particular attack pattern.

The Dynamic IP Restrictions module can be configured to block IP addresses after a number of concurrent requests or to block IP addresses that perform a number of requests over a period of time. Depending on your IIS version you will need to enable either the IP Security feature or the IP and Domain Restrictions 

To set or modify dynamic IP restrictions:

Open the IIS Manager
Select the name of the machine to configure this globally (or change to the specific web site for which you need to configure this)
Double click on IP Address and Domain Restrictions
From the Actions pane, select Edit Dynamic Restriction Settings
Modify and set the dynamic IP restriction settings as needed and click on OK to save changes









Sunday, August 2, 2020

ora-01843 not valid month

ora-01843 not valid month

Date which is passing in query has wrong format.

Check server date format
Or
Web.config (.NET application)
Add following or do setting in IIS
<globalization culture="en-GB"/>

Wednesday, July 29, 2020

ORA-06502: PL/SQL: Numeric Or Value Error: Character String Buffer Too Small

ORA-06502: PL/SQL: Numeric Or Value Error: Character String Buffer Too Small

Sometimes you get this error if you insert a character/string in a numeric column or vice versa.

It happens when the field size is smaller than the length of the string you try to store.

Check the length of each column which is going to insert. There is a column having length less than the value you are going to insert.

ORA-00600 internal error code, arguments [qkebCreateConstantOpn1]

Query

UPDATE TBLNONPOASTOCKS SET NQUANTITY=0,NUSABLEPLEDGESTOCK=0 WHERE TBLNONPOASTOCKS.CFIRMNUMBER=ICFIRMNUMBER AND TBLNONPOASTOCKS.CPERSONALDPID=ICDPID AND 
TBLNONPOASTOCKS.DDTOFTRAN=IDTRANDATE ;


Error

ORA-00600 internal error code, arguments [qkebCreateConstantOpn1], [], [], [], [], [], [], [], [], [], [], []


Solution
Drop and recreate Table


Followers