Database Size and Free Space

Check your database size and free space:

col 'Database Size' for a15
col 'Free Space' for a15
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",round(free.p / 1024 / 1024/1024) || ' GB' "Free Space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used, (select sum(bytes) as p from dba_free_space) free group by free.p;

Disable Firewall on OCI Linux Servers

How to disable firewall on OCI Linux Servers / regular on-premise servers?

Below commands will work for OCI Linux OS Version 7, so please check your O.S Version before execute these commands.

Check OS Version:

For Oracle Linux:
 $ cat /etc/oracle-release (OR)  just type "hostnamectl" on the command line.

For Redhat Linux:
$ cat /etc/redhat-release

1) Check if firewall is running or not.

$ firewall-cmd --state

2) Stop firewall.

$ systemctl stop firewalld.service

Note: If you simply stop the firewall, once server rebooted, your firewall be started running again.  If you don't want to enable firewall rules after reboot permanently disable the firewall.

3) Disable firewall.

$ systemctl disable firewalld.service

4) Cross check if firewall is running or not.

$ firewall-cmd --state


If your OS Version is 6, to stop firewall you need to use iptables service.  Remember all commands needs to be run from root user, if not at least you should have sudo access to run these commands.

1) Status of firewalls

$ service iptables status

2) Stop firewalls.

$ service iptables stop

3) Disable firewalls.

$ chkconfig iptables off

Timezone Change on OCI Linux Hosts

How to change Timezone on Oracle Cloud Infrastructure (OCI) Linux Servers:

All our OCI servers by default they are in GMT timezone, but we wanted them to be in US CST time.
So we have changed timezone as shown below.

1) Login to the host as root user and change directory to /etc and do the following.

    cd /etc/

2) Check localtime file.

    $ls -ltr localtime    ## It is softlink and pointed to GMT time.
  lrwxrwxrwx. 1 root root 37 Jan  24  2020 localtime -> /usr/share/zoneinfo/GMT
   
3) Remove the softlink that pointed to GMT.

  unlink localtime

4) Create a new softlink pointing to US CST time.

    ln -s /usr/share/zoneinfo/America/Chicago localtime  

DBA Directory

1) How to check existing DBA Directories in a database:

col OWNER for a10
col DIRECTORY_NAME for a35
col DIRECTORY_PATH for a75
set lines 200
select * from dba_directories;

2) Create a new DBA Directory or modify existing directory path:

create or replace directory DMPDIR as '/u01/datapump';

3) Grant required privileges to the DBA Directory to access by a user.

grant read,write on directory DMPDIR to <username>;

4) Grant required privileges to the DBA Directory to access by every user.

grant read,write on directory DMPDIR to public;

Cancel Concurrent Request From Backend

Run this as 'APPS' user:

1) Concurrent Request Status:

select status_code,phase_code from fnd_concurrent_requests where  request_id=&1;  

2) Cancel Concurrent Request:

update fnd_concurrent_requests set status_code='D', phase_code='C' where request_id=&1; 
commit; 

3)  Update Concurrent request as 'Complete Normal'

update fnd_concurrent_requests set status_code='C',phase_code='C' where request_id=&1;

commit;

4)  Cancel All Pending Concurrent Requests

Updating base tables directly is not supported or recommended by Oracle – this update would be OK to execute in a non-production instance. In a production instance, use APIs or cancel the pending requests through the forms interface.

update FND_CONCURRENT_REQUESTS set phase_code='C',status_code='D' where phase_code='P' ;

commit;