ORA DBA

INS-06006 GI RunInstaller Fails If OpenSSH Is Upgraded to 8.x

2020-08-24T21:15:00.000+05:00

When attempting to configure 19c grid infrastructure by running <gridSetup.sh>, the following error occurs in SSH connectivity step:

[INS-06006] Passwordless SSH connectivity not set up between the following node(s): [<racnode2>]

The error can't be ignored so CRS installation fails.

However, SSH setup shows successful and ssh <node> date command works fine for all nodes, CVU user equivalence check also shows passed status.

OpenSSH is upgraded to 8.x. Please note OpenSSH's behavior might be different on any other platforms/OS, for example on AIX, OpenSSH 7.5 has this problem, and on SLES Linux 12 SP4, OpenSSH_7.2p2 has this problem.

# ssh -V

OpenSSH_8.0p1, OpenSSL 1.0.2r 24 Aug 2020

The below command might also give the above error on OpenSSH 8.0.

# scp -p <racnode2>:"'/tmp/test.txt'" /tmp/test.txt

protocol error: filename does not match request

And the error can be avoided by adding "-T" option in the command:

# scp -T -p <racnode2>:"'/tmp/test.txt'" /tmp/test.txt

test.txt 100% 2 0.1KB/s 00:00

To mitigate the risk of (CVE-2019-6111), OpenSSH 8.0 adds client-side checking that the filenames sent from the server match the command-line request, if there is a difference between client and server wildcard expansion, the client may refuse files from the server. For this reason, OpenSSH 8.0 provids a new "-T" flag to scp that disables these client-side checks. for details, see https://www.openssh.com/txt/release-8.0

Workaround :

Before installation, as root user: (please change the path if the location of your "scp" is not the same with below)

# Rename the original scp.

mv /usr/bin/scp /usr/bin/scp.orig

# Create a new file </usr/bin/scp>.

vi /usr/bin/scp

# Add the below line to the new created file </usr/bin/scp>.

/usr/bin/scp.orig -T $*

# Change the file permission.

chmod 555 /usr/bin/scp

After installation:

mv /usr/bin/scp.orig /usr/bin/scp

WUT-121: The file transfer has been forbidden by the Administrator

2020-08-24T00:39:00.007+05:00

Problem: Users were not able to download files.

Solution: Edit these files and add below parameters:

$DOMAIN_HOME/config/fmwconfig/components/FORMS/forms1/server/webutil.cfg

$DOMAIN_HOME/config/fmwconfig/components/FORMS/instances/forms1/server/webutil.cfg

transfer.database.enabled=TRUE

transfer.appsrv.enabled=TRUE

transfer.appsrv.workAreadRoot=TRUE

transfer.appsrv.accessControl=TRUE

#List transfer.appsrv.read.<n> directories

transfer.appsrv.read.1=/temp

#transfer.appsrv.read2.=<locatation>

#List transfer.appsrv.write.<n> directories

transfer.appsrv.write.1=/temp

Database Open Access Vulnerability

2020-08-24T00:29:00.004+05:00

Vulnerability Title: Database Open Access

Service Port: Database Listener Port

Service Name: Oracle TNS Listener

Service Protocol: tcp

Vulnerability Description:

The database allows any remote system the ability to connect to it. It is recommended to limit direct access to trusted systems because databases may contain sensitive data, and new vulnerabilities and exploits are discovered routinely for them. For this reason, it is a violation of PCI DSS section 1.3.6 to have databases listening on ports accessible from the Internet, even when protected with secure authentication mechanisms.

Solution:

Configure the database server to only allow access to trusted systems. For example, the PCI DSS standard requires you to place the database in an internal network zone, segregated from the DMZ

Add VALIDNODE_CHECKING in sqlnet.ora file and add IPs to restrict database access and then reload the listener and check:

$ORACLE_HOME/network/admin/sqlnet.ora

tcp.validnode_checking = yes

tcp.invited_nodes =(*.*.*.*)

Self-signed TLS/SSL certificate Vulnerability - Weblogic 12.2.1.4

2020-08-24T00:18:00.006+05:00

Vulnerability Title: Self-signed TLS/SSL certificate

Service Port:4443

Service Name: HTTPS

Service Protocol: tcp

Vulnerability Description:

The server's TLS/SSL certificate is self-signed. Self-signed certificates cannot be trusted by default, especially because TLS/SSL man-in-the-middle attacks typically use self-signed certificates to eavesdrop on TLS/SSL connections.

Solution:

Obtain a new TLS/SSL server certificate that is NOT self-signed and install it on the server or remove the demo certificates.

remove these demo.cert and DemoTrust.jks certificates and check:

$MWH/wlserver/server/lib/DemoTrust.jks

$MWH/wlserver/server/lib/demo.cert

Click Jacking Vulnerability - Weblogic 12.2.1.4

2020-08-24T00:08:00.004+05:00

Vulnerability Title: Click Jacking

Service Port:4443

Service Name: HTTPS

Service Protocol: tcp

Vulnerability Description:

Clickjacking, also known as a UI redress attack, is a method in which an attacker uses multiple transparent or opaque layers to trick a user into clicking a button or link on a page other than the one they believe they are clicking. Thus, the attacker is "hijacking" clicks meant for one page and routing the user to an illegitimate page.

Vulnerability Solution:Use HTTP X-Frame-Options. Send the HTTP response headers with X-Frame-Options that instruct the browser to restrict framing where it is not allowed.

1. Apply the Patch 30418565

Patch 30418565 FORMS LISTENER SERVLET NOT GENERATING X-FRAME-OPTIONS HTTP HEADER

Add SAMEORIGIN option with X-Frame-Options optin in Run-time and Staging httpd.conf

-- X-Frame-Options is available at line 1033

Header always append X-Frame-Options SAMEORIGIN

2.Run-time directory:

$DOMAIN_HOME/config/fmwconfig/components/OHS/instances/<componentName>/httpd.conf

3.Staging directory:

$DOMAIN_HOME/config/fmwconfig/components/OHS/<componentName>/httpd.conf

Restart the service and check.

Browsable Web Directory Vulnerability - Weblogic 12.2.1.4

2020-08-23T23:54:00.000+05:00

Vulnerability Title:Browsable web directory

Service Port:4443

Service Name:HTTPS

Service Protocol:tcp

Vulnerability Description: A web directory was found to be browsable, which means that anyone can see the contents of the directory. These directories can be found:

* via page spidering (following hyperlinks), or

* as part of a parent path (checking each directory along the path and searching for ""Directory Listing"" or similar strings), or

* by brute forcing a list of common directories.

Browsable directories could allow an attacker to perform a directory traversal attack by viewing ""hidden"" files in the web root, including CGI scripts, data files, or backup pages."

Vulnerability Proof: https://<*.*,*.*>:4443/OracleHTTPServer12c_files/

Solution:

Remove the "Indexs" option from Run-time & Staging httpd.conf

--check @ line # 241

From: Options Indexes FollowSymLinks

To: Options FollowSymLinks

1.Run-time directory:

$DOMAIN_HOME/config/fmwconfig/components/OHS/instances/<componentName>/httpd.conf

2.Staging directory:

$DOMAIN_HOME/config/fmwconfig/components/OHS/<componentName>/httpd.conf

You should add the following value in [default] section of the formsweb.cfg

3.$DOMAIN_HOME/config/fmwconfig/servers/WLS_FORMS/applications/formsapp_12.2.1/config/formsweb.cfg

#X-Frame-Options to Resolve Click-Jacking

Set_X_Frame_Options=true

If you are not planing for https then comment out these lines in both Run-time and Staging httpd.conf files:

# Include the SSL definitions and Virtual Host container

include "ssl.conf"------------------------------->Comment out this.

....

IncludeOptional "moduleconf/*.conf" ------------------------------->Comment out this.

Restart the services and then test.

Weblogic 12.2.1.4 Admin Server Default Port Change

2020-08-23T23:15:00.002+05:00

Please make the below changes to update the port in FMW 12.2.1.4:

Step 1: Take the backup of the below files:

$DOMAIN_HOME/config/fmwconfig/servers/AdminServer/applications/em/META-INF/emoms.properties

$DOMAIN_HOME/sysman/state/targets.xml

$DOMAIN_HOME/bin/stopManagedWebLogic.sh

$DOMAIN_HOME/bin/startManagedWebLogic.sh

$DOMAIN_HOME/bin/stopWebLogic.sh

Step 2: Navigate to the Weblogic Admin Console > Environments > Servers > AdminServer > Configuration Tab > General Sub Tab > now change the listen port from 7001 to 7010 and save the changes

Step3: Update the Admin Port(7001 to 7010) in the below files:

$DOMAIN_HOME/config/fmwconfig/servers/AdminServer/applications/em/META-INF/emoms.properties

$DOMAIN_HOME/sysman/state/targets.xml

$DOMAIN_HOME/bin/stopManagedWebLogic.sh

$DOMAIN_HOME/bin/startManagedWebLogic.sh

$DOMAIN_HOME/bin/stopWebLogic.sh

Step 4: Run $DOMAIN_HOME/bin/setDomainEnv.sh

Step 5: Restart your Admin Server and test the Weblogic Console URL and EM Console URL.

Admin Server Failure To Start With Error BEA-000362 Server failed

2020-08-23T22:56:00.003+05:00

There are 1 nested errors:

weblogic.management.DeploymentException: java.io.IOException: Error from fcntl() for file locking, Resource temporarily unavailable, errno=11

Solution:

Remove these *.lok file and then try to start

$DOMAIN_HOME/edit.lok

$DOMAIN_HOME/config/lifecycle-config.xml.lok

$DOMAIN_HOME/config/ovd/default/ovd.lok

$DOMAIN_HOME/servers/AdminServer/tmp/AdminServer.lok

$DOMAIN_HOME/servers/WLS_FORMS/tmp/WLS_FORMS.lok

$DOMAIN_HOME/servers/WLS_REPORTS/tmp/WLS_REPORTS.lok

$DOMAIN_HOME/tmp/<filename>.lok

find . -name "*.lok" -exec rm -f {} \;

$DOMAIN_HOME/servers/AdminServer/data/store/default/_WLS_ADMINSERVER000000.DAT

$DOMAIN_HOME/servers/AdminServer/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT

$DOMAIN_HOME/servers/WLS_FORMS/data/store/default/_WLS_WLS_FORMS000000.DAT

$DOMAIN_HOME/servers/WLS_FORMS/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT

$DOMAIN_HOME/servers/WLS_REPORTS/data/store/default/_WLS_WLS_REPORTS000000.DAT

$DOMAIN_HOME/servers/WLS_REPORTS/data/store/diagnostics/WLS_DIAGNOSTICS000000.DAT

find . -name "*.DAT" -exec rename '.DAT' '.DAT_OLD' {} \;

Rolling Upgrade Error in Script

2020-06-20T00:39:00.001+05:00

Few months back we were upgrading our AIX based database from 11gR2 [11.2.0.4] to 12c [12.2.0.1] by rolling upgrade. When we run physru_v3.sh script it is giving error:

WARN: The last execution of this script either exited in error or at the

-e user's request. At this point, there are three available options:

-e 1) resume the rolling upgrade where the last execution left off

-e 2) restart the script from scratch

-e 3) exit the script

-e

Option (2) assumes the user has restored the primary and physical

-e standby back to the original configuration as required by this script.

-e Enter your selection (1/2/3):

-e Sep 24 11:17:59 2019 [0-1] not a valid option - ''

-e Enter your selection (1/2/3):

-e Sep 24 11:17:59 2019 [0-1] not a valid option - ''

-e Enter your selection (1/2/3):

-e Sep 24 11:17:59 2019 [0-1] not a valid option - ''

Solution:

The issue is caused by the following setup:

in physru_v3.sh script change first line from below and then rerun the script

#!/bin/sh

#!/bin/bash <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Reports Server Component Status in REPORTS 12C

2020-06-20T00:08:00.004+05:00

How to check report server component status as we previously check by opmnctl?

Due to decommissioning of opmn from 12c, that option is not available. There are two ways to check, unfortunately none of them as simple as opmn:

1. Run the command $DOMAIN_HOME/reports/bin/rwdiag.sh -findall

If the reports server appears in the output, then it is up and running. If it does not appear, it is down

2. Check the REPORT Server components logs.

AVDF Console and Listener Port Change

2020-06-19T23:49:00.002+05:00

Is it possible to change Audit Vault and Database Firewall listener and https console ports?

Answer:

It is not supported to change the AVDF database default listener port, it is also not supported to register the AV repository to a listener other than the one started by the AV installation. Doing either of these things it might "break" AVDF functionality.

The same is applicable for the default console https port change to 443.

For the moment there is no such possibility for AVDF hopefully feature will be available next release which is 19.1.

ORA-28041: Authentication protocol internal error

2020-06-19T21:45:00.001+05:00

Expired user accounts can't change password and connect from 11.1.0.7 client to 12.2.0.1 DB+April2018PSU, failing with error

ORA-28041: Authentication protocol internal error.

There is no issue with open user accounts connecting from 11.1.0.7 client to 12.2.0.1 DB+April2018PSU.

Also the expired user account connections from 11.1.0.7 client to 12.2.0.1DB+Jan2018PSU works fine. The issue started only after applying April 2018 PSU patch on DB server.

From Forms 11.1.2.2 and the SQLPLUS 11.1.0.7 that comes with Forms 11.1.2.2, expired user connections fail with below message:

ERROR:

ORA-28041: Authentication protocol internal error

In 12.2.0.1 databases, 11.1 client support has been removed. The following table shows which client and which database version are supported.

Cluster verification failed with PRVG-4574

2020-06-11T17:06:00.001+05:00

Grid Infrastructure Upgrade from 12c to 19c

Error:

"Verifying Verify that the ASM instance was configured using an existing ASM parameter file. ...FAILED (PRVG-4574)"

Solution:

Copy ASM password file from local directory to ASM disk group.

1. Check the Path of ASM Password File

srvctl config asm -a

ASM home: <CRS home>

Password file: /oracle/product/12.2.0/grid/dbs/orapw+ASM

Backup of Password file:

ASM listener: LISTENER

ASM is enabled.

ASM is individually enabled on nodes:

ASM is individually disabled on nodes:

ASM instance count: ALL

Cluster ASM listener: ASMNET1LSNR_ASM

2. Move ASM Passowrd File to ASM Disk Group

When tried to move ASM password file from local directory to ASM disk group faced this error:

ASMCMD> pwcopy /oracle/product/12.2.0/grid/dbs/orapw+ASM +OCR_DG/orapwASM

OPW-00010: Could not create the password file.

ORA-15056: additional error message

ORA-15221: ASM operation requires compatible.asm of 12.1.0.0.0 or higher

ORA-06512: at line 4

ASMCMD-9454: could not create new password file

error is visible now that our ASM compatible parameter is not set accordingly.

select group_number, name,compatibility from v$asm_diskgroup;

 
GROUP_NUMBER NAME                           COMPATIBILITY      
------------ ------------------------------ ----------------  
  1 		 OCR_DG                         11.2.0.2.0

Change compatibility:

alter diskgroup OCR_DG SET ATTRIBUTE 'compatible.asm' = '12.1';

ASMCMD> pwcopy /oracle/product/12.2.0/grid/dbs/orapw+ASM +OCR_DG/orapwASM

srvctl modify asm -pwfile +OCR_DG/orapwASM

srvctl config asm -a

ASM home: <CRS home>

Password file: +OCR_DG/orapwASM

Backup of Password file:

ASM listener: LISTENER

ASM is enabled.

ASM is individually enabled on nodes:

ASM is individually disabled on nodes:

ASM instance count: ALL

Cluster ASM listener: ASMNET1LSNR_ASM

Now rerun runcluvfy.sh and it will be passed.

ORA-19571: archived log RECID '' STAMP '' not found in control file

2020-06-04T10:10:00.006+05:00

It seems that the Controlfile records are over-written.

1. check control_file_record_keep_time

show parameter control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7

The value is very low. So please increase.

alter system set control_file_record_keep_time=20 scope=both;

System altered.

SQL> show parameter control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 20

2) Next you need to catalog the archivelogs to make the backup run without errors

RMAN> catalog start with '/u01/archive';

3. Now start the backup

Password same as Login Name

2020-06-01T15:40:00.004+05:00

select
name as [LoginName]
,'Password is same as Login Name' [Description]
from sys.syslogins
WHERE PWDCOMPARE (name,password) = 1

Password same as Username

2020-06-01T14:57:00.007+05:00

If You want to check username have passwords same as login name. e.g. username 'abc123' has password= 'abc123'

Solution:

create or replace function samepwd(username in varchar2, password in varchar2)

return char

authid current_user

raw_key raw(128):= hextoraw('0123456789ABCDEF');

raw_ip raw(128);

pwd_hash varchar2(16);

cursor c_user (cp_name in varchar2) is

select password

from sys.user$

where password is not null

and name=cp_name;

procedure unicode_str(userpwd in varchar2, unistr out raw)

enc_str varchar2(124):='';

tot_len number;

curr_char char(1);

padd_len number;

ch char(1);

mod_len number;

debugp varchar2(256);

begin

tot_len:=length(userpwd);

for i in 1..tot_len loop

curr_char:=substr(userpwd,i,1);

enc_str:=enc_str||chr(0)||curr_char;

end loop;

mod_len:= mod((tot_len*2),8);

if (mod_len = 0) then

padd_len:= 0;

else

padd_len:=8 - mod_len;

end if;

for i in 1..padd_len loop

enc_str:=enc_str||chr(0);

end loop;

unistr:=utl_raw.cast_to_raw(enc_str);

end;

function crack (userpwd in raw) return varchar2

enc_raw raw(2048);

raw_key2 raw(128);

pwd_hash raw(2048);

hexstr varchar2(2048);

len number;

password_hash varchar2(16);

begin

dbms_obfuscation_toolkit.DESEncrypt(input => userpwd,

key => raw_key, encrypted_data => enc_raw );

hexstr:=rawtohex(enc_raw);

len:=length(hexstr);

raw_key2:=hextoraw(substr(hexstr,(len-16+1),16));

dbms_obfuscation_toolkit.DESEncrypt(input => userpwd,

key => raw_key2, encrypted_data => pwd_hash );

hexstr:=hextoraw(pwd_hash);

len:=length(hexstr);

password_hash:=substr(hexstr,(len-16+1),16);

return(password_hash);

end;

begin

open c_user(upper(username));

fetch c_user into pwd_hash;

close c_user;

unicode_str(upper(username)||upper(password),raw_ip);

if( pwd_hash = crack(raw_ip)) then

return ('Y');

else

return ('N');

end if;

end;

set lines 1000

set pages 1000

COLUMN username format A25

COLUMN password format A25

COLUMN account_status format A30

select username, username password,account_status from dba_users where samepwd(username, username) = 'Y';

Monitoring Long Running Operations

2020-06-01T14:36:00.006+05:00

set lines 1000
set pages 1000
COLUMN OP_NAME FORMAT a40
select * from (
SELECT
SID,
OPNAME OP_Name,
TO_CHAR(start_time,'DD-MON-YYYY HH:MI:SS') START_TIME,
TO_CHAR(start_time + (round(TIME_REMAINING/60)/1440 +round(ELAPSED_SECONDS/60)/1440),'DD-MON-YYYY HH:MI:SS') "Expected_End_Time",
round(TIME_REMAINING/60) "Remaining_Minuts",
round(ELAPSED_SECONDS/60) "Elapsed_Minuts",
round(((TIME_REMAINING/(TIME_REMAINING+ELAPSED_SECONDS))*100),2) "% Remaining",
round(((ELAPSED_SECONDS/(TIME_REMAINING+ELAPSED_SECONDS))*100),2) "% Completed"
FROM
V$SESSION_LONGOPS
WHERE
TIME_REMAINING>0) order by 4 desc;

Toad Error - Error downloading resources XML file

2020-06-01T14:23:00.004+05:00

Error downloading resources XML file:Error downloading URL:

….//community-dowloads.quest.com/toadsoft/ORA…

Solution:

1. Launch Toad

2. Go to the menu View | Toad Options

3. Go to Startup. Under Automatic Updates, uncheck this option and select "Don't automatically check for updates".

4. Go to General. Under Toad Improvement Program, uncheck the option "Participate in the Toad Improvement program".

5. Go to Network Utilities. Under Hosts, uncheck the option "Override Proxy for Toad sites".

6. Hit Apply and OK, and then restart Toad.

REP-110: Unable to open file

2020-06-01T13:26:00.010+05:00

REP-110: File km_ram_0003.rdf cannot be opened.
REP-1070: An error occurred while opening or saving a document.
REP-0110: File km_ram_0003.rdf cannot be opened.

Solution:

Add reports path in following files and then restart report services:

$ORACLE_HOME/bin/reports.sh

$DOMAIN_HOME/reports/bin/reports.sh

REPORTS_PATH=/u01/reports:${ORACLE_HOME}/reports/templates:${ORACLE_HOME}/reports/samples/demo:${ORACLE_HOME}/reports/printers:${DOMAIN_HOME}/reports/fonts:${REPORTS_PATH}; export REPORTS_PATH

Collect Diagnostic Logs in AVDF

2020-06-01T10:41:00.004+05:00

login audit vault system by 'support' user and then switch to 'root' user.

cd /usr/local/dbfw/bin/priv

and run diagnostic package

./dbfw-diagnostics-package.rb

this command will generate the diagnostics zipfile and write its name to stdout. This may then be copied from the system using any normal method.

chmod 775 /usr/local/dbfw/tmp/diagnostics_avs_2020_06_01_05_35_22_0A6239CD75E1EE716B5428EF358F893D.zip

Diagnostic file path: /usr/local/dbfw/tmp/

Download the diagnostic file and share with oracle support engineer.

Enable Diagnostics Collection and Packaging Utility in AVDF

2020-06-01T10:13:00.007+05:00

The diagnostic packager is disabled by default and must be installed

manually in order to enable log collection and packaging.

Please run the following command from this appliance as 'root':

/usr/local/dbfw/bin/priv/dbfw-diagnostics-package.rb --install

After the package has been installed, the diagnostics configuration

must be modified to allow the utility to collect information about the

appliance. Collection of all elements and files can be enabled with:

/usr/local/dbfw/bin/priv/dbfw-diagnostics-package.rb --enable ALL

Edit this file to select the diagnostics to include:

/usr/local/dbfw/etc/dbfw-diagnostics-package.yml

Further options are available by viewing the utiity help:

/usr/local/dbfw/bin/priv/dbfw-diagnostics-package.rb --help

When you have collected the diagnostics, remove the package with the following

command (as 'root'):

/usr/local/dbfw/bin/priv/dbfw-diagnostics-package.rb --remove

Find Client IP from SQL Server ERRORLOG

2020-05-29T11:30:00.008+05:00

sp_configure 'show advanced options', 1

RECONFIGURE;

sp_configure 'Ole Automation Procedures', 1

RECONFIGURE;

sp_configure 'show advanced options', 1

RECONFIGURE;

CREATE TABLE errorLog (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX));

INSERT INTO errorLog

EXEC sp_readerrorlog -- specify the log number or use nothing for active error log

CREATE FUNCTION RegexBysp_OA (@subject varchar(max), @pattern varchar(4000))

RETURNS varchar(4000)

BEGIN

DECLARE @objRegexExp INT,

@objMatch INT,

@Result VARCHAR(8000) --Creating COM object

EXEC sp_OACreate 'VBScript.RegExp', @objRegexExp OUT--Assigning Properties to COM object

EXEC sp_OASetProperty @objRegexExp, 'Pattern', @pattern

EXEC sp_OASetProperty @objRegexExp, 'IgnoreCase', 1

EXEC sp_OASetProperty @objRegexExp, 'MultiLine', 1

EXEC sp_OASetProperty @objRegexExp, 'Global', false

EXEC sp_OASetProperty @objRegexExp, 'CultureInvariant', true --Executing the COM object

EXEC sp_OAMethod @objRegexExp, 'execute', @objMatch OUT, @subject--Fetching the first matching value

EXEC sp_OAGetProperty @objmatch, 'item(0).Value' , @Result OUT--Releasing COM object after use

EXEC sp_OADestroy @objMatch

EXEC sp_OADestroy @objRegexExp RETURN @Result

END

--To Check the function

select *, master.dbo.RegexBysp_OA(Text,'(?:[0-9]{1,3}\.){3}[0-9]{1,3}') as [IP_Address] from

CREATE TABLE IP_Address (IP_Address VARCHAR(64));

INSERT INTO IP_Address

SELECT master.dbo.RegexBysp_OA(Text,'(?:[0-9]{1,3}\.){3}[0-9]{1,3}') as [IP_Address]

FROM errorLog

select count(*) "Connection Count", IP_Address from IP_Address where IP_Address is not null group by IP_Address order by 1;

Based on above result you can implement firewall to block specific IP to made connection to SQL Server.

Find Client IP from listener log

2020-05-29T10:35:00.006+05:00

Some time we need to know who has made a connection attempt (successful/ unsuccessful ) because listener log is capturing all the connection attempts.

cd $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/<listener name>/trace

ls -ltr

-rw-rw----. 1 oracle oracle 3733 May 29 10:22 listener.log

cat listener.log | grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' | sort | uniq -c | sort -n

if you want to save the screen out to a file:

grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' listener.log | sort | uniq -c | sort -n > listener_unique_ip.txt

Unique Attempt Counts	IP
1	10.1.**.39
4	10.1.**.77
33	10.1.**.76

Based on above result you can implement ACL in sqlnet.ora by tcp.invited_nodes.

Adding Datafile in Oracle Tablespace by Dynamic SQL

2020-05-28T11:15:00.032+05:00

1. Check OS Free Space by Toad / SQL Developer

2. Add Datafile based on particular tablespace datafile number

3. Add Datafile based on overall database datafile number

Add Datafile based on particular tablespace datafile number

define v_path = '';

select 'ALTER TABLESPACE '|| TBS_NAME ||' ADD DATAFILE ''&v_path'||lower(TBS_NAME)||'_'||max(df#+1)||'.dbf'''||

' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;' as cmd

FROM (

select df.file# as df#,df.ts#,df.name,tbs.name as TBS_NAME from v$datafile df,v$tablespace tbs

where df.ts#=tbs.ts# and tbs.name=upper('&name')) group by TBS_NAME;

Enter the datafile path and tablespace name.

Generated SQL:

ALTER TABLESPACE FCAT ADD DATAFILE '/data1/oradata/fcat_73.dbf' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;

copy this generated SQL and run, datafile successfully added!

Add Datafile based on overall database datafile number

define v_path = '';

select 'ALTER TABLESPACE '|| TBS_NAME ||' ADD DATAFILE ''&v_path'||lower(TBS_NAME)||'_'||(select max(file#+1)from v$datafile)||'.dbf'''||

' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;' as cmd

FROM (

select df.file# as df#,df.name,tbs.name as TBS_NAME from v$datafile df,v$tablespace tbs

where df.ts#=tbs.ts# and tbs.name=upper('&name')) group by TBS_NAME;

Enter the datafile path and tablespace name.

Generated SQL:

ALTER TABLESPACE FCAT ADD DATAFILE '/data1/oradata/fcat_74.dbf' SIZE 5G AUTOEXTEND ON NEXT 512M MAXSIZE 31G;

copy this generated SQL and run, datafile successfully added!

Kill Blocking Sessions in RAC

2020-05-22T06:39:00.008+05:00

select decode(request,0,'Holder: ','Waiter: ') || gv$lock.sid ses, 'alter system kill session ''||gv$lock.sid||','||gvs.serial#||',@'||gvs.inst_id||''';' from gv$lock join gv$session gvs on gv$lock.sid=gvs.sid and gv$lock.inst_id=gvs.inst_id join gv$locked_object lo on gv$lock.sid = lo.session_id and gv$lock.inst_id=lo.inst_id join dba_objects do on lo.object_id = do.object_id where (id1, id2, gv$lock.type) in (select id1, id2, type from gv$lock where request>0) order by id1, request;

Command Syntax:

Alter system kill session 'sid,serial#,@inst_id' ;

copy the generated SQL command and execute.

alter system kill session '1523,3805,@1';

ORA DBA

INS-06006 GI RunInstaller Fails If OpenSSH Is Upgraded to 8.x

WUT-121: The file transfer has been forbidden by the Administrator

Database Open Access Vulnerability

Self-signed TLS/SSL certificate Vulnerability - Weblogic 12.2.1.4

Click Jacking Vulnerability - Weblogic 12.2.1.4

Browsable Web Directory Vulnerability - Weblogic 12.2.1.4

Weblogic 12.2.1.4 Admin Server Default Port Change

Admin Server Failure To Start With Error BEA-000362 Server failed

Rolling Upgrade Error in Script

Reports Server Component Status in REPORTS 12C

AVDF Console and Listener Port Change

ORA-28041: Authentication protocol internal error

Cluster verification failed with PRVG-4574

ORA-19571: archived log RECID '***' STAMP '***' not found in control file

Password same as Login Name

Password same as Username

Monitoring Long Running Operations

Toad Error - Error downloading resources XML file

REP-110: Unable to open file

Collect Diagnostic Logs in AVDF

Enable Diagnostics Collection and Packaging Utility in AVDF

Find Client IP from SQL Server ERRORLOG

Find Client IP from listener log

Adding Datafile in Oracle Tablespace by Dynamic SQL

Add Datafile based on particular tablespace datafile number

Add Datafile based on overall database datafile number

Kill Blocking Sessions in RAC

ORA-19571: archived log RECID '' STAMP '' not found in control file