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.

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.

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.

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.

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');

convert date to number

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

ora-27452 ora-00972

More than 30 character not allowed for job name.

So please keep smaller job name.

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;

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;

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

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;

/

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') ;

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/

Oracle 19c Security parameters Cyber security

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

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"/>

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