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

ORA-22992 cannot use LOB locators selected from remote tables

SELECT * FROM TBLHTMLSCANNEDIMAGE@LNK_PREVIOUSYEARBALANCE;

ORA-22992: cannot use LOB locators selected from remote tables

both 1920 and 2021 in single db. but through link Oracle is not suppporting this

select * from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm

Workaround

SELECT ( select cmodifymatter from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm where nrequestno=4)   FROM DUAL;

select pscannedimage From tblscannedimage@lnk_digital;

Workaround

SELECT (select pscannedimage From tblscannedimage@lnk_digital) as pscannedimage  FROM DUAL

SELECT * FROM TBLHTMLSCANNEDIMAGE@LNK_PREVIOUSYEARBALANCE;

Workaround

CREATE MATERIALIZED VIEW MV_TBLHTMLSCANNEDIMAGE AS SELECT * FROM TBLHTMLSCANNEDIMAGE@LNK_PREVIOUSYEARBALANCE;

SELECT * FROM MV_TBLHTMLSCANNEDIMAGE;

Workaround

create temp table in database and insert into it then select

select *

from test@dblink;

 create global temporary table testglobal

on commit delete rows

as select * from test@dblink;

select * from testglobal;

 insert into testglobal

select y from test@dblink;

select * from testglobal ;

ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.

Summary

from LD , I m trying to save Blob image in ccm's table with link

select * from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm;

Error

01~Error Description : ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.

&#xD;

Method : UpdateEkycdataLine : 46406~0~

Investigation

select * from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm;

check db version of both database 

lnk_ccm(Distrubted DB) is of 11g

source db in 19c

Reason

Distributed LOB operations are not supported on pre-12.2 databases(11.2.0.3).

19c db should be connected through 19c db to support this.

Solution

upgrade DB.

ORA-30926 unable to get a stable set of rows in the source tables

ORA-30926: unable to get a stable set of rows in the source tables

 

Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.

 

getting more than one records to update single row

***there is a restriction that multiple updates to the same row in the destination table is not allowed and will raise ORA-30926.

Action: Remove any non-deterministic where clauses and reissue the dml.

Solution 

use distinct in select query

check duplicate record

Another Issue Cause

Merge Parallel is not supporting when query is using index.

Check explain plan. It is not using Parallel hint as using index.

If use full hint with paralel then it start using Parallel.

that time getting this error  ORA-30926 unable to get a stable set of rows in the source tables

Solution

Remove Parallel hint

ORA-00600 internal error code, arguments [13013] [5001]

ORA-600 [13013] [a] [b] [c] [d] [e] [f]

This format relates to Oracle Server 8.0.3 to 10.1

Arg [a] Passcount

Arg [b] Data Object number

Arg [c] Tablespace Relative DBA of block containing the row to be updated

Arg [d] Row Slot number

Arg [e] Relative DBA of block being updated (should be same as [c])

Arg [f] Code

ORA-00600: internal error code, arguments: [13013], [5001], [268], [8447338], [5], [8447338], [17], [], [], [], [], []

RnD

Select object_name,object_type,owner from dba_objects where data_object_id=268;

Analyze table <owner>.<table name> validate structure cascade online ;

Rebuild Table and Index

**Index is created as column desc 

Issue

getting this issue in Merge Parallel 

Workaround

after removing parallel hint it is working fine.

Issue 

Table issue. May be bug

Solution 

Drop and recreate table

Disable trigger based on some condition

/* following command will disable the RK Trigger at NON RK Client only */

declare lchost varchar2(100); 

begin

select host into lchost from dba_db_links where db_link='LNK_RAKSHAK'; 

if ( trim(upper(lchost)) like '%RAKSHAK%' ) 

THEN 

  BEGIN

  FOR cur_rec IN (SELECT trigger_name 

                  FROM   dba_triggers WHERE trigger_name like 'TG_RK%') LOOP

    BEGIN

      EXECUTE IMMEDIATE 'alter trigger ldbo.' || cur_rec.trigger_name || ' DISABLE' ;

    END;

  END LOOP;

END;

END IF ;

END;

/

Merge is not using Parallel hint

if you are using table alias then use parallel(alias) not parallel(table_name)

also if query is using index in plan then it will not use parallelism

if you want to use parallel forcefully then use full(alias) along with parallel hint.

DBCA Ora-03113

Check OS level

Resource

RAM

copy column from same table

Table 1

Col1

Col2

Col3

New column Col4 added

Col4 has same data col2

Method

Update table1 set Col4=col2

Create table as 

Insert into table2( col1,col2,col3,col2)

Rename

oracle communication: failed to connect to server or failed to parse connect string

Tnsping

Antivirus and firewall exclusion

Oracle database and application driver version compatibility

ODAC

ODP.NET

oracle client

oracle.manageddataaccess.dll

Oracle bug