JD Edwards CNC

  How to find Session ID/ SQL ID/ SQL Statement Using OS Process ID 1. It will show you the basic Session Information. SELECT P.SPID OS_PROCESS_ID,       S.SID SESSION_ID,       S.SERIAL#,       S.USERNAME,       S.STATUS,       S.LAST_CALL_ET,       P.PROGRAM,       P.TERMINAL,       LOGON_TIME,       MODULE,       S.OSUSER  FROM GV$PROCESS P, GV$SESSION S WHERE S.PADDR = P.ADDR AND S.INST_ID=P.INST_ID AND P.SPID=&Process_ID ORDER BY S.INST_ID; 2. It Will Show You the SQL ID which SQL Statements currently Executing.  SELECT S.SQL_ID   FROM GV$PROCESS P, GV$SESSION S  WHERE S.PADDR = P.ADDR  AND S.INST_ID=P.INST_ID  AND  P.SPID=&Process_ID  ORDER BY S.INST_ID; 3. It Will Show You the SQL Statements Details which SQL Statements are currently Running. ...

We can use the power of ansible to automate the oracle client installation task on multiple servers.In this article, we will explain how we will achieve this by executing an ansible playbook from ansible control node. Ansible control node  is any machine where ansible tool is installed.    IMPORTANT POINTS: oracle 19c client will be installed on all nodes. Make sure  the servers where  client need to be installed has connectivity from ansible control server   Below are the steps : 1. Copy the oracle 19c client software ( zip file ) to ansible control server. [ansible_node] ls -ltr /oracle/LINUX.X64_193000_client.zip 2. Prepare the inventory file: The list of servers where oracle 19c client will be installed. [ansiblel_node] $ cat /home/ansible/ansible-scipts/host_inv [appserver] linux_host22 ansible_host=10.20.86.60 ansible_connection=ssh ansible_user=root ansible_ssh_pass=dbaclass@123 linux_host29 ansible_host=10.20.86.61 ansible_connection=ssh ansible_...

  Oracle database : Basics about pfile and spfile What is a parameter file Parameter file is a text or binary to store the database initialization parameters. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance and many other aspects as well. Such as : memory allocation (SGA and PGA), startup of optional background processes, Setting of NLS parameters etc. There are 2 types of parameter files, namely : 1. pfile (parameter file) – older way [ not recommended by oracle ] 2. spfile (server parameter file) – newer way [ recommended by oracle ] spfile was introduced starting from oracle 9i, untill that time text based pfile was used to store database initialization parameters. pfile V/s spfile pfile spfile Text file Binary file Parameters in pfile can be edited using any text editor spfile can not be edited using a text editor. Instead it can only be altered using the “”ALTER SYSTEM”” comm...

 Oracle has renamed the tools release naming convention and new release 22 was released recently. Below are some of the highlights Enhanced Browser Support for Grid Import and Export   This quick tour describes the functionality of importing and exporting EnterpriseOne grid data on all JD Edwards supported browsers   Rich Formatting of Orchestration and Notification Messages   Rich formatting carries information beyond the words and numbers in the message. With rich formatting, not only are your messages accurate and timely, they are also easily understood by their recipients. They communicate your company's brand to your customers and partners. This quick tour shows how   easy it is to create well-formatted messages from the intuitive user interface within Orchestrator Studio. Please refer below link for more information on this release: https://docs.oracle.com/cd/E84502_01/learnjde/automate-optimize-transform.html#april-2022      

  SQL Script to find sessions consuming lot of CPU in the DB col program form a30 heading "Program" col CPUMins form 99990 heading "CPU in Mins" select rownum as rank, a.* from ( SELECT v.sid, program, v.value / (100 * 60) CPUMins FROM v$statname s , v$sesstat v, v$session sess WHERE s.name = 'CPU used by this session' and sess.sid = v.sid and v.statistic#=s.statistic# and v.value>0 ORDER BY v.value DESC) a where rownum < 11;

  OCI announced their Bastion service which is great addon! Now you don’t need that server provisioned anymore, or you don’t need a public subnet. You have possibility to use either managed session or port forwarding session to connect to servers. Managed sessions requires Agent enabled on the compute and ssh port forwarding requires IP on your subnet where you’re connecting to and port. OCI documentation says on new compute instances Agent should be enabled by default but you’ll see further down that I had to enable the agent. How it works? In short everything is controlled through OCI and you can manage who can do what via normal IAM policies. What you’ll need first is an Bastion “resource”. Resource defines name, VCN and subnet (where you’re connecting to) and who can login from which IP addresses to Bastion.    After you have Bastion created you need to create a session. If you’re using managed SSH session then you need to define SSH key (for the session!), usern...

  Use below query to get the size of a database. col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , 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 / Database Size Used space Free space -------------------- -------------------- -------------------- 8 GB 5 GB 3 GB  To Check the database size physical consume on disk. select sum(bytes)/1024/1024/1024 size_in_gb from dba_data_files;   SIZE_IN_GB ---------- 32.1853638   Check the total space used by the data. select sum(bytes)/1024/10...

  Scheduling tasks on Linux with cron Cron is a daemon used to execute scheduled commands automatically. Learning how to use cron required some reading and experimenting, but soon I was using cron to shut down our email server, back up the data in a compressed tar file, then restart the email service at 3AM. The commands for a cron job are stored in the crontab file on a Linux system, which is usually found in /etc/crontab. Display the contents of your crontab file with   $ crontab -l . Edit the crontab file with   $ crontab -e . Some systems default to the   Vi editor   for cron editing. You can override this setting using   environment variables : $ EDITOR = nano crontab -e This allows you to use the   nano editor   to edit your personal crontab (if you don't have one yet, one is created automatically for you). All crontab commands have parameters denoted by an asterisk until you insert an integer value. The first represents minutes, then hours...

The undo tablespace, has undo segments. The each undo segment is composed by undo extents. When transactions hit a database and they need undo space the allocation happens in the following sequence: 1. Allocate an extent in an undo segment which has no active transaction. Oracle tries to distribute transactions over all undo segments. 2. If no undo segment found then oracle tries to online an off-line undo segment and use it. 3. If no undo segments to online, then we create a new undo segment and use it. 4. If space does not permit creation of undo segment, then we try to reuse an expired extent from the existing undo segments. For a running transaction associated with undo segment/ extent, if it needs more undo space then: 1. If the current extent has more free blocks then use the next free block that is all ready allocated to the extent. 2. If the current extent does not have free blocks and if the next extent of the segment has expired then wrap in the the next extent and return the...