How to create new PDB service?

Some situations, you need different service name/ database connection name for your pluggable database ( RAC & Non-RAC). You can follow below steps to complete the task.

Steps for Creating a new Service for a PDB

Step 1. Change to pdb container;

alter session set container=pdb1;

Step 2. Create service for current pdb using name and new network service name.

ALTER SESSION SET CONTAINER=PAUENO;

exec dbms_service.create_service('<service_name>','<network_name>');

exec dbms_service.create_service('pdb1svc','pdb1svc');

Step 3. Start the new service.

exec dbms_service.start_service('pdb1svc');
alter system register;

To Verify:

COLUMN name FORMAT A30
COLUMN network_name FORMAT A30

SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;

Step 4. Check the listener services and try to make a new connection to the newly created services. 

lsnrctl services 

Step 5. Save the state for PDB. 

 Need to save the state for pluggable for the new pdb service. Otherwise, you need to manually start the service after you restart the pdb or bounce the CDB ROOT database.

alter pluggable database save state;

RAG (Retrieval-Augmented Generation) explained

RAG (Retrieval-Augmented Generation) is a framework in Artificial Intelligence that combines retrieval-based methods with generative models (like GPT or BERT) to produce more accurate, informative, and context-aware responses.

RAG by by NVIDIA:
Retrieval-augmented generation is a technique for enhancing the accuracy and reliability of generative AI models with information from specific and relevant data sources. In other words, it fills a gap in how LLMs work. Under the hood, LLMs are neural networks, typically measured by how many parameters they contain. An LLM’s parameters essentially represent the general patterns of how humans use words to form sentences.

RAG by G-Cloud:
RAG (Retrieval-Augmented Generation) is an AI framework that combines the strengths of traditional information retrieval systems (such as search and databases) with the capabilities of generative large language models (LLMs). By combining your data and world knowledge with LLM language skills, grounded generation is more accurate, up-to-date, and relevant to your specific needs.

RAG by AWS:
Retrieval-Augmented Generation (RAG) is the process of optimizing the output of a large language model, so it references an authoritative knowledge base outside of its training data sources before generating a response. Large Language Models (LLMs) are trained on vast volumes of data and use billions of parameters to generate original output for tasks like answering questions, translating languages, and completing sentences. RAG extends the already powerful capabilities of LLMs to specific domains or an organization's internal knowledge base, all without the need to retrain the model. It is a cost-effective approach to improving LLM output so it remains relevant, accurate, and useful in various contexts.

May be I can explain RAG as ...
Retrieval-Augmented Generation (RAG) is a hybrid AI architecture.
It augments a language model with an external knowledge base (retrieved at runtime).
This helps the model access up-to-date or domain-specific information that may not be in its training data.

How it Works?
Query → User asks a question.
Retrieval → A retriever (like FAISS, Elasticsearch, etc.) fetches relevant documents from a knowledge base.
Augmentation → Retrieved documents are appended to the prompt for a generative model.
Generation → The generative model (e.g., GPT, T5) produces an answer using both the query and the retrieved context.

Real-World Use Case Example
Use Case: Customer Support Bot for an Insurance Company

Problem:
The chatbot should answer policy-related questions like:
“What does my health insurance plan cover for cancer?”
Traditional model might say:
“Health insurance may or may not cover cancer based on the plan.”

RAG-based chatbot does:
Retrieves actual policy documents from the company’s internal knowledge base.
Generates answer like:
“According to your Health Secure Plan (Policy ID: XXZZ2025), cancer benefits cover up to ₹500,000, including pre and post-natal expenses.”

• Personalized
• Accurate
• Trustworthy

When to Use RAG?

When information is frequently changing (e.g., news, prices, health guidelines).
When custom domain knowledge is critical.
When you want to combine accuracy with natural language fluency.

Agentic RAG
Agentic AI and LLM agents have been around for a few months, and the idea is simple: AI systems that don’t just give answers but actively assist with tasks, adapt to new information, and work independently when needed. The challenge, though, is making these systems reliable and up-to-date, especially in areas where the information changes all the time.

RAG was one of the first approaches to tackle this problem. It combines two things: the ability to fetch real-time, relevant data (retrieval) and the power to generate responses using that data (generation). As soon as agentic AI became a focus, RAG quickly stood out as a natural fit. It gave AI systems the ability to stay current and respond with information that made sense for the situation.

What are RAG agents?
RAG agents are AI tools designed to do more than retrieve and generate—they’re built for doing specific tasks. Think of them as goal-oriented assistants that know where to find the information they need and how to use it. Instead of generic answers, they’re tailored for real-world situations.

For example:
A RAG agent in customer support doesn’t only tell you the refund policy; it finds the exact details for your specific order.
In healthcare, a RAG agent doesn’t only summarize medical studies; it pulls the most relevant research based on a patient’s case.
So, if an LLM-based RAG would only answer questions, RAG agents fit into workflows and make decisions based on fresh, relevant data.

RAG agent frameworks (Most popular in 2025)
Some of the famous agentic RAG frameworks are LangChain, LlamaIndex Agents, DB GPT, Quadrant Rag Eval, MetaGPT, Ragapp, GPT RAG by Azure, IBM Granite, Langflow, AgentGPT, CrewAI etc.

RAG vs. Semantic Search

Feature	RAG (Retrieval-Augmented Generation)	Semantic Search
Definition	Combines document retrieval with a generative LLM to answer queries in natural language	Retrieves documents or chunks that semantically match a query (no generation)
Output	Synthesized, human-like response using retrieved content	List of relevant documents or passages
Use Case	Question answering, summarization, chatbots	Search engines, document finders, internal knowledge lookup
Model Role	Uses both retriever + generator (e.g., vector search + GPT)	Only uses retriever (e.g., vector embeddings + similarity search)
Latency	Higher (retrieval + generation step)	Lower (just retrieval)
Explainability	Moderately explainable (shows retrieved docs and generation)	High explainability (results are directly retrieved)
Example Prompt	"Summarize all recent changes in our HR policy."	"Show me documents related to recent HR policy updates."

Important Update as on 2nd October 2024:

Cohere.command, cohere.command-light, meta.llama-2-70b-chat (and few more) AI models got retired (and in the last few months), so any application using these AI models will not work and give a response error message “Entity with key <model-name> not found” or “Error 400 <model-name> does not support TextGeneration”, Please see the list of models already retired or sooner to retire.

Vector Search and Vector Index - working steps (demo)

 -- Setup Oracle 23ai 

Click Here : Oracle 23ai Database setup

Small Introduction about vector Database and Vector Search:

- Vector Database 

A vector database is a specialized type of database designed to store, index, and query data represented as high-dimensional vectors, also known as vector embeddings. These embeddings are numerical representations of various data types, such as text, images, audio, or other complex objects, generated by machine learning models like large language models (LLMs)

- Vector Search:

Vector search is a method of information retrieval that leverages these vector embeddings to find data points that are semantically similar to a given query. Instead of relying on exact keyword matches, vector search calculates the "distance" or "similarity" between the query vector and the vectors stored in the database. This allows for more intelligent and context-aware searches, enabling use cases like:

1) Semantic Search
2) Image and Audio Recognition
3) Retrieval-Augmented Generation (RAG)
4) Recommendation Systems

Vector databases and vector search are crucial components in modern AI applications, facilitating efficient and effective handling of unstructured and semi-structured data for various machine learning tasks.

Most Important part in AI is Retrieval-augmented generation (RAG). RAG is a technique that enhances the performance of large language models (LLMs) by combining them with an external knowledge base. Instead of relying solely on their pre-trained knowledge, RAG systems retrieve relevant information from external sources and incorporate it into the generation process, resulting in more accurate, informed, and up-to-date responses. 

Note: Create your own file system and directories to test the scenarios

Let us start the POC on loading LLM, Creating vector and generate test case on vector search.

Step#01 - create a directory to hold the model, download it, and unzip the model into that directory.

$ mkdir -p /u04/23ai_activity/models
$ cd /u04/23ai_activity/models
$ wget https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/VBRD9P8ZFWkKvnfhrWxkpPe8K03-JIoM5h_8EJyJcpE80c108fuUjg7R5L5O7mMZ/n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2_augmented.zip

unzip -oq all_MiniLM_L12_v2_augmented.zip

Step#02 - Connect to the database, create a test user, create a directory object pointing to the physical directory, and grant access to it for our test user.

SQL> alter session set container=FREEPDB1;
Session altered.

- create user to test scenarios ( use strong password for the user)

create user if not exists testuser1 identified by testuser1 quota unlimited on users;
grant create session, db_developer_role, create mining model to testuser1;
create or replace directory model_dir as '/u04/23ai_activity/models';
grant read, write on directory model_dir to testuser1;


Step#3 - Now load the model into the database using the DBMS_VECTOR package.

sqlplus /nolog
conn testuser1/testuser1@//localhost:1521/freepdb1

SQL>
begin
  dbms_vector.drop_onnx_model (
    model_name => 'ALL_MINILM_L12_V2',
    force => true);
  dbms_vector.load_onnx_model (
    directory  => 'model_dir',
    file_name  => 'all_MiniLM_L12_v2.onnx',
    model_name => 'ALL_MINILM_L12_V2');
end;
/
PL/SQL procedure successfully completed.

- Now  see the model information in the USER_MINING_MODELS view.

column model_name format a30
column algorithm format a10
column mining_function format a15
select model_name, algorithm, mining_function
from   user_mining_models
where  model_name = 'ALL_MINILM_L12_V2';
MODEL_NAME                     ALGORITHM  MINING_FUNCTION
------------------------------ ---------- ---------------
ALL_MINILM_L12_V2              ONNX       EMBEDDING

Step#4 - Generating Vectors (VECTOR Data Type)

Generate vectors using the VECTOR_EMBEDDING function. In the following example we generate a vector for the text "Quick test". As you can see, the resulting vector is really big considering the size of the text.

set lines 160
set long 1000000
select vector_embedding(all_minilm_l12_v2 using 'Quick test' as data) AS my_vector;
MY_VECTOR
----------------------------------------------------------------------------------------------------------------------------------------------------------------
[-3.86444256E-002,7.27762803E-002,-6.99377712E-003,-7.29618035E-003,8.81512091E-003,-6.36086613E-002,4.39666817E-003,-4.20215651E-002,-1.32307202E-001,-5.837616
05E-003,-1.3236966E-002,-1.62914731E-002,6.54898351E-003,-4.983522E-002,-1.98450536E-002,-4.69920225E-002,1.03937663E-001,-8.96753445E-002,-2.77861813E-003,4.13
947664E-002,-6.51626661E-002,-1.09901905E-001,-8.73053819E-003,2.533352E-002,-1.42030632E-002,-2.42071245E-002,1.91591978E-002,4.93748812E-003,6.30869251E-003,-
1.24127813E-001,-7.17297941E-003,3.7317384E-002,4.97635901E-002,4.52162437E-002,1.49683114E-002,-2.21795831E-002,-3.67936082E-002,-6.20233943E-004,7.16803819E-0
02,5.33913262E-003,1.92087106E-002,-9.91346017E-002,3.90679464E-002,2.22725421E-002,5.04363105E-002,1.81943253E-002,5.34031466E-002,1.44161871E-002,-1.99907795E
-002,-1.20323608E-002,-2.63888389E-002,-4.14667316E-002,6.2473774E-002,-4.68838662E-002,1.16748568E-002,-2.43180972E-002,-3.11982706E-002,-7.5750039E-003,2.2546
6359E-002,-4.17359956E-002,1.23237111E-002,4.31706831E-002,-7.83750787E-002,1.24918511E-002,5.42060807E-002,4.33742851E-002,2.52278382E-003,-1.15482137E-002,-9.
98658361E-004,-2.12613102E-002,1.00960173E-002,3.17986645E-002,-1.13146752E-002,-1.26893371E-002,2.66182758E-002,-7.50683714E-003,-3.70341949E-002,1.94851588E-0
02,-2.9213747E-002,-2.61210538E-002,2.86212545E-002,-9.15900841E-002,1.50552345E-002,-4.98168021E-002,2.29324233E-002,7.82517716E-003,4.22972552E-002,3.37974802
E-002,-4.2345725E-002,-6.32970557E-002,3.84949856E-002,-1.93851739E-002,1.96233811E-003,-3.91593436E-004,7.80334743E-003,5.63595518E-002,4.45814878E-002,-4.9701
6348E-002,1.36384079E-002,2.76547611E-001,6.3580215E-002,-1.69337653E-002,-3.25948671E-002,2.74621621E-002,-1.84809547E-002,-3.58916223E-002,3.18280957E-003,-3.

 
===========
EXAMPLE-2
===========
Step#1 - Download below csv file from hugging face

https://huggingface.co/datasets/ygorgeurts/movie-quotes/resolve/main/movie_quotes.csv?download=true -O movie_quotes.csv

Step#2 - Connect to the database and create a new table from the CSV file of movie quotes.

sqlplus /nolog
conn testuser1/testuser1@//localhost:1521/freepdb1
drop table if exists movie_quotes purge;
create table movie_quotes as
select movie_quote, movie, movie_type, movie_year
from   external (
         (
           movie_quote  varchar2(400),
           movie        varchar2(200),
           movie_type   varchar2(50),
           movie_year   number(4)
         )
         type oracle_loader
         default directory model_dir
         access parameters (
           records delimited by newline
           skip 1
           badfile model_dir
           logfile model_dir:'moview_quotes_ext_tab_%a_%p.log'
           discardfile model_dir
           fields csv with embedded terminated by ',' optionally enclosed by '"'
           missing field values are null
           (
             movie_quote char(400),
             movie,
             movie_type,
             movie_year
           )
        )
        location ('movie_quotes.csv')
        reject limit unlimited
      );
  
  
Table created.
SQL>

SQL> desc movie_quotes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MOVIE_QUOTE                                        VARCHAR2(400)
 MOVIE                                              VARCHAR2(200)
 MOVIE_TYPE                                         VARCHAR2(50)
 MOVIE_YEAR                                         NUMBER(4)
SQL>

Step#4 - add a new column to hold the vector data for each movie quote. we are using the new VECTOR data type.

SQL> alter table movie_quotes add (
  movie_quote_vector vector
);  
Table altered.
SQL> desc movie_quotes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MOVIE_QUOTE                                        VARCHAR2(400)
 MOVIE                                              VARCHAR2(200)
 MOVIE_TYPE                                         VARCHAR2(50)
 MOVIE_YEAR                                         NUMBER(4)
 MOVIE_QUOTE_VECTOR                                 VECTOR(*, *)
SQL>

Step#5 - populate the new column by generating vectors from the movie quotes.


SQL> update movie_quotes
set    movie_quote_vector = vector_embedding(ALL_MINILM_L12_V2 using movie_quote as data);
732 rows updated.
SQL> commit;
Commit complete.


Step#5 - Vector Search using VECTOR_DISTANCE

Now perform a search using the VECTOR_DISTANCE function. 
This accepts two vectors and as the name suggests, returns a distance between them. 
Since the model we used generates vectors for text data, 
we would expect the vector distance to be smaller for two vectors that are similar. 
In the following examples we create a vector from our search text and order the output 
of the query by the vector distance between our search text and the quote text.
First we ask for "Films with motivational speaking in them".


SQL> variable search_text varchar2(100);
exec :search_text := 'Films with motivational speaking in them';SQL>
PL/SQL procedure successfully completed.
SQL>
set pages 100
set linesize 200
column movie format a50
column movie_quote format a100
SELECT vector_distance(movie_quote_vector, (vector_embedding(all_minilm_l12_v2 using :search_text as data))) as distance,
       movie,
       movie_quote
FROM   movie_quotes
order by 1
fetch approximate first 5 rows only;

OUT-PUT:

  

DISTANCE MOVIE                                              MOVIE_QUOTE
---------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
6.786E-001 Once Upon a Time in Hollywood                      That was the best acting i've ever seen in my whole life.
6.979E-001 Dead Poets Society                                 You must strive to find your own voice because the longer you wait to begin, the less likely you are
                                                               going to find it at all.
7.169E-001 The Pursuit of Happyness                           Walk that walk and go forward all the time. Don't just talk that talk, walk it and go forward. Also,
                                                               the walk didn't have to be long strides; baby steps counted too. Go forward.
7.186E-001 Joker                                              My mother always tells me to smile and put on a happy face. She told me I had a purpose to bring lau
                                                              ghter and joy to the world.
7.234E-001 Blazing Saddles                                    Men, you are about to embark on a great crusade to stamp out runaway decency in the west. Now you me
                                                              n will only be risking your lives, whilst I will be risking an almost certain Academy Award nominati
                                                              on for Best Supporting Actor.



Next we ask for "Films about war".
-------------------------------

variable search_text varchar2(100);
exec :search_text := 'Films about war';
set linesize 200
column movie format a50
column movie_quote format a100
SELECT vector_distance(movie_quote_vector, (vector_embedding(all_minilm_l12_v2 using :search_text as data))) as distance,
       movie,
       movie_quote
FROM   movie_quotes
order by 1
fetch approximate first 5 rows only;

  DISTANCE MOVIE                                              MOVIE_QUOTE
---------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
5.682E-001 Dr. Strangelove                                    Gentlemen, you can't fight in here! This is the War Room!
6.346E-001 Blazing Saddles                                    Men, you are about to embark on a great crusade to stamp out runaway decency in the west. Now you me
                                                              n will only be risking your lives, whilst I will be risking an almost certain Academy Award nominati
                                                              on for Best Supporting Actor.
6.587E-001 Fury                                               Ideals are peaceful; history is violent.
7.243E-001 The Kill Team                                      You give me your loyalty, and I?ll guarantee that each and every one of you will have a chance to be
                                                               a warrior, to actually be a part of history.
7.253E-001 Dr. No                                             Bond. James Bond



Step#6 - (Optional) Create a Vector Index 

If we want to create a vector index, we must set the VECTOR_MEMORY_SIZE parameter for the root container. The amount of memory we need depends on the size and complexity of the data being indexed.

conn / as sysdba
SQL> show parameter vector_memory_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size                   big integer 0

alter system set vector_memory_size = 1G scope=spfile;
shutdown immediate;
startup;

-- There are two types of vector indexes.
-- Neighbor Partition Vector Indexes
-- In-Memory Neighbor Graph Vector Index

conn testuser1/testuser1@//localhost:1521/freepdb1
drop index if exists movie_quotes_vector_idx;

-- Neighbor Partition Vector Index

create vector index movie_quotes_vector_idx on movie_quotes(movie_quote_vector) organization neighbor partitions
distance cosine
with target accuracy 95;

drop index if exists movie_quotes_vector_idx;

-- In-Memory Neighbor Graph Vector Index

create vector index movie_quotes_vector_idx on movie_quotes(movie_quote_vector) organization inmemory neighbor graph
distance cosine
with target accuracy 95;

Note:  Vectors are big. This has to be taken into account from both a storage perspective, and a memory perspective, especially when creating vector indexes.

-----
-- Search the query again
variable search_text varchar2(100);
exec :search_text := 'Films about war';
set linesize 200
column movie format a50
column movie_quote format a100
SELECT vector_distance(movie_quote_vector, (vector_embedding(all_minilm_l12_v2 using :search_text as data))) as distance,
       movie,
       movie_quote
FROM   movie_quotes
order by 1
fetch approximate first 5 rows only;

Now see the difference when you have large amount of data.

Follow my blog and share with others.

Oracle Database 23ai Free version setup on Linux - rpm based (simplified)

 - rpms to download

oracle-database-free-23ai-1.0-1.el8.x86_64.rpm

oracle-database-preinstall-23ai-1.0-2.el8.x86_64.rpm

-- run the pre-install rpm

# dnf -y install oracle-database-preinstall-23ai-1.0-2.el8.x86_64.rpm

-- verify

# rpm -qa | grep -i database

oracle-database-preinstall-23ai-1.0-2.el8.x86_64

-- Install database via rpm

# dnf -y install oracle-database-free-23ai-1.0-1.el8.x86_64.rpm

Note: your last message must be like below:

Installed:

  oracle-database-free-23ai-1.0-1.x86_64

Complete!

-- Now verify:

# rpm -qa | grep -i database

oracle-database-free-23ai-1.0-1.x86_64

oracle-database-preinstall-23ai-1.0-2.el8.x86_64

-- Oracle Database 23ai Creation and Configuration

# cd /opt/oracle/product/23ai/dbhomeFree

# /etc/init.d/oracle-free-23ai configure

e.g.,

# /etc/init.d/oracle-free-23ai configure

Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Password cannot be null. Enter password:
The password you entered contains invalid characters. Enter password:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database FREE.
Enter SYS user password:
**********
Enter SYSTEM user password:
********
Enter PDBADMIN User Password:
************
Prepare for db operation
7% complete
Copying database files
29% complete
Creating and starting Oracle instance
30% complete
33% complete
36% complete
39% complete
43% complete
Completing Database Creation
47% complete
49% complete
50% complete
Creating Pluggable Databases
54% complete
71% complete
Executing Post Configuration Actions
93% complete
Running Custom Scripts
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/FREE.
Database Information:
Global Database Name:FREE
System Identifier(SID):FREE
Look at the log file "/opt/oracle/cfgtoollogs/dbca/FREE/FREE.log" for further details.
Connect to Oracle Database using one of the connect strings:
     Pluggable database: ugbu-mum-98/FREEPDB1
     Multitenant container database: ugbu-mum-98
#

-- Database status check
# /etc/init.d/oracle-free-23ai status
Status of the Oracle FREE 23ai service:
LISTENER status: RUNNING
FREE Database status:   RUNNING
#
-- verify pmon
$ ps -ef | grep pmon
oracle     39598       1  0 07:16 ?        00:00:00 db_pmon_FREE
oracle     44957   44346  0 07:36 pts/0    00:00:00 grep --color=auto pmon

-- Check the database

$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Thu Oct 24 07:22:07 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle.  All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07
SQL> 

Note: From 23ai onwards, non-container database option not available. Default is container based database

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select name,open_mode,database_role,log_mode from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    LOG_MODE
--------- -------------------- ---------------- ------------
FREE      READ WRITE           PRIMARY          NOARCHIVELOG
SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
SQL>
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 FREEPDB1                       READ WRITE NO
SQL> alter session set container=FREEPDB1;
Session altered.
SQL>

-- To connect from client / application / oracle developer, you can add an entry to tnsnames.ora file 
e.g.,

FREEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <your-server-ip/host>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

state is UNDEFINED IN impdp

MANAGE TERMINATED OR LOST DATAPUMP IMPDP JOB

Error from import log:

...

ORA-39029: worker 8 with process name "DW01" prematurely terminated

ORA-31671: Worker process DW01 had an unhandled exception.

ORA-01114: IO error writing block to file  (block # )

ORA-01114: IO error writing block to file 1039 (block # 896640)

ORA-15055: unable to connect to ASM instance

ORA-15055: unable to connect to ASM instance

ORA-00603: ORACLE server session terminated by fatal error

ORA-27504: IPC error creating OSD context

ORA-27300: OS system dependent operation:sendmsg failed with status: 105

ORA-27301: OS failure message: No buffer space available

ORA-27302: failure occurred at: sskgxpsnd2

ORA-03135: connection lost contact

UDI-31626: operation generated ORACLE error 31626

ORA-31626: job does not exist

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4756

ORA-39076: cannot delete job SYS_IMPORT_FULL_01 for user sys

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.DBMS_DATAPUMP", line 2114

ORA-06512: at "SYS.DBMS_DATAPUMP", line 4683

ORA-06512: at "SYS.DBMS_DATAPUMP", line 6137

ORA-06512: at line 1

-- when you attach the job, you can see below status also.

Resuming the impdp process:

$ impdp sys/********@DEMODB attach=SYS_IMPORT_FULL_01

...

State: UNDEFINED

...

Import>

-- find your job details from database

SELECT owner_name, job_name, operation, job_mode, state 

FROM dba_datapump_jobs

where JOB_NAME='SYS_IMPORT_FULL_01';

output:

OWNER_NAME JOB_NAME                  OPERATION    JOB_MODE        STATE                    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

---------- ------------------------------ ------------ --------------- -------------------- ---------- ----------------- -----------------

sys    SYS_IMPORT_FULL_01        IMPORT       FULL            NOT RUNNING                   0                 0        ##########

Solution/ Fix:

Restart the job using CONTINUE_CLIENT after attachinig the job. It ll restart the job where it was failed.

e.g.,

Import> CONTINUE_CLIENT

Job SYS_IMPORT_FULL_01 has been reopened at Sun Apr 14 22:59:09 2024

Restarting "sys"."SYS_IMPORT_FULL_01":  sys/********@DEMODB parfile=impdp_HCM_DEV.par

-- Verify the status of the job

OWNER_NAME   JOB_NAME                       OPERATION              JOB_MODE               STATE

------------ ------------------------------ ---------------------- ---------------------- ----------------------

sys          SYS_IMPORT_FULL_01             IMPORT                 FULL                   EXECUTING

-- from alert log it is confirmed, job restarted again.

2024-04-14T22:59:10.761862-05:00

Thread 1 advanced to log sequence 134694 (LGWR switch),  current SCN: 21163224110

  Current log# 2 seq# 134694 mem# 0: +RECO/DEMODB/ONLINELOG/group_2.337.1053949991

2024-04-14T22:59:37.902800-05:00

DEMODB(3):

DEMODB(3):DW00 started with pid=116, OS id=15549, wid=1, job sys.SYS_IMPORT_FULL_01

2024-04-14T22:59:40.229363-05:00

DEMODB(3):

DEMODB(3):DW01 started with pid=117, OS id=15580, wid=2, job sys.SYS_IMPORT_FULL_01

2024-04-14T22:59:40.432641-05:00

DEMODB(3):

DEMODB(3):DW02 started with pid=123, OS id=15593, wid=3, job sys.SYS_IMPORT_FULL_01

Update Weblogic domain to discover newly added managed servers in OEM 13c

 In most of cases you may add/ delete managed servers from your Weblogic domain as part of regular activity. but if your are monitoring these systems from OEM 13c, then you need to discover them to monitor and manage. 

You can follow below steps for this purpose:

Step# 1:  Run Auto Discover

OEM Console --> Setup --> Add Targets --> Configure Auto Discovery --> select your admin server host --> Discover Now

Step#2 : Update WebLogic Domain

OEM Console --> Targets --> Middleware --> Search with domain Name (Target Name) --> Click on Same domain Target --> Weblogic Domain -->  Refresh WebLogic Domain --> Add Update Targets --> Click on "Add Targets" to add newly joined managed servers

Now you can see all the changes will be reflected.

How to copy a BEST Execution Plan for any SQL ID from One Database to Another Database in Oracle

Scenario-1:

Assume, you have a SQL, which is using a better plan on one environment(assume pre-prod) and not-so-good plan in another environment. And you want to copy the better plan to the destination database and force the optimizer to use this plan.

Scenario-2:

Assume, you have a SQL, which is using a better plan on pre-prod after doing lots of tunning activities. May be you used SQL-Patch etc but when you want to move the same to production with new code deployment plan, then how you can achieve this?

Let us go with actual steps what is tested successfully.

Points to remember:

Your database code and data must be same with structure-wise but data volume may be similar may not be exact.

[From Source Database]

1. Get the SQL ID, Plan Hash Value of the good Plan. You can use any of the following SQL.

SQL> select sql_id, plan_hash_value from v$sqlarea 
     WHERE sql_id IN ('fkmu4ubzmdx629');
SQL> select sql_id, plan_hash_value from v$sql 
     WHERE sql_id IN ('fkmu4ubzmdx629');

2. Create a SQL Tuning Set 

Create a SQL Tuning Set for the above SQL Id and Plan Hash Value. This Tuning Set will be copied to the Destination database using a staging table and the datapump utility.

Step 1 : Create an Empty SQL Tuning Set

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'fkmu4ubzmdx629_STS'
, description => 'fkmu4ubzmdx629 STS to copy to Prod - better plan'
);
END;
/

Step 2 : Load the SQL Information for the SQL ID into this Tuning Set

DECLARE
 c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
  SELECT VALUE(p)
    FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''fkmu4ubzmdx629'' AND plan_hash_value = 367652949 ')) p;
   -- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'fkmu4ubzmdx629_STS', populate_cursor => c_sqlarea_cursor);
END;
/

** You can display the content of SQL Tuning Set using :

SELECT NAME, STATEMENT_COUNT, DESCRIPTION FROM DBA_SQLSET;

COLUMN SQL_TEXT FORMAT a30
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999
SELECT 

  SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
  ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM TABLE(

      DBMS_SQLTUNE.SELECT_SQLSET('fkmu4ubzmdx629_STS' ));

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('fkmu4ubzmdx629_STS') );

3. Create a Staging table to hold the exported SQL Tuning set

--> Table name and other parameters are case-sensitive  <--

BEGIN

DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'FKMU4UBZMDX629_STS_TBL', schema_name => 'SYSTEM');

END;

/

4. Load the SQL Tuning Set information to the Staging Table. (See Above on how to create and populate a SQL Tuning Set)

BEGIN

DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'fkmu4ubzmdx629_STS'
, sqlset_owner => 'SYS'
, staging_table_name => 'FKMU4UBZMDX629_STS_TBL'
, staging_schema_owner => 'SYSTEM'
);
END;

/

5. Export the table using datapump utility

expdp system DIRECTORY=data_pump_dir DUMPFILE=fkmu4ubzmdx629_STS_Plan.dmp TABLES=FKMU4UBZMDX629_STS_TBL

6. Transfer the dump file to the destination server. (Using scp or ftp command)

[On Target Server]

1. Import into The destination Database

impdp system DIRECTORY=data_pump_dir DUMPFILE=fkmu4ubzmdx629_STS.dmp TABLES=FKMU4UBZMDX629_STS_TBL

2. Unpack The SQL Tuning set from the staging table to the destination server

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
sqlset_name => '%'
, sqlset_owner => 'SYS'
, replace => true
, staging_table_name => 'FKMU4UBZMDX629_STS_TBL'
, staging_schema_owner => 'SYSTEM');
END;
/

3. Load the plan from SQL Tuning Set to SQL Plan Baseline.

begin

 VARIABLE v_plan_cnt NUMBER
 EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
 sqlset_name => 'fkmu4ubzmdx629_STS', -
 sqlset_owner => 'SYS', -
 basic_filter => 'sql_id = ''fkmu4ubzmdx629'' AND plan_hash_value = 367652949' );

end;

/

4. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan.

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');'
from V$SQLAREA where SQL_ID in ('fkmu4ubzmdx629');

exec DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');

Oracle Database HISTOGRAMS - Performance Review

Histograms tell the Optimizer about the distribution of data within a column. Without a histogram, the Optimizer assumes a uniform distribution of rows across the distinct values in a column.

A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into "buckets," as you might sort coins into buckets. Based on the NDV and the distribution of the data, the database chooses the type of histogram to create.

Example:

Assume there is a table named JET_ENGINE having 10,000 records and it has Column names SALES_COUNTRY. This column stores information about how many JET_ENGINEs are sold per country. Assume that JET_ENGINEs are sold only to three countries: India, US and UK.

Assume that 9000 JET_ENGINEs were sold to India, 900 to US and 100 to UK.

Table: JET_ENGINE
Column Name: SALES_COUNTRY

You ran a SQL query to find out how much JET_ENGINEs were sold to UK.

WITHOUT HISTOGRAM

Optimizer will calculates the cardinality (number of rows returned) by dividing the total number of rows in the table by the number of distinct values in the column.

Cardinality= Total_records/number_of_distinct _values= 10000/3= 3333

As the data distribution in SALES_COUNTRY column is not uniform so cardinality estimate is showing up as 3333 while for UK we have only 100 records. Keeping such high cardinality in mind, Oracle may try to use the full scan here and query cost will be high.

WITH HISTOGRAM

The presence of a histogram changes the formula used by the Optimizer to estimate the cardinality, and allows it to generate a more accurate execution plan. So mostly in above case Oracle will look at the histogram and choose a low-cost index range scan (if available).

TYPES OF HISTOGRAMS:

1. Height – balanced Histograms : A height-based histogram is used when there are more distinct values than the number of buckets. In this type, the column values are divided into bands so that each band contains approximately the same number of rows. In this histogram type values will be evenly spread through the buckets.

2. Frequency Histograms : This type of Histograms are more precise and are more widely used by Oracle. Each value of the column corresponds to a single bucket of the histogram. This is also called value based histogram. Each bucket contains the number of occurrences of that single value only. Frequency histograms are automatically created when the number of distinct values is less than or equal to the number of histogram buckets specified.

Oracle 12c also introduced hybrid histogram which combines characteristics of both height-based histograms and frequency histograms  and also Top-frequency histograms which are a variant on the frequency histogram, where the histogram only focuses on the popular values, ignoring the less popular values as statistically insignificant.

HOW TO VERIFY IF YOUR TABLE IS USING HISTOGRAM

You can query DBA_TAB_COL_STATISTICS or USER_TAB_COL_STATISTICS view to get this information easily.

e.g.,

select table_name,column_name,histogram
from DBA_TAB_COL_STATISTICS
where owner='TRANSACTION' and table_name='ACCOUNTS';

You ll have below values for your columns:

NONE

TOP-FREQUENCY 

FREQUENCY - ignores nonpopular values that are statistically insignificant.

HEIGHT BALANCED - Legacy one

KEY DATABASE PARAMETERS RELATED TO HISTOGRAMS

METHOD_OPT: This is the parameter which tells Oracle Engine to create histogram while collecting the statistics.

The default value of METHOD_OPT from 10g onwards is ‘FOR ALL COLUMNS SIZE AUTO’.

Below are possible values for the METHOD

AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

REPEAT: Collects histograms only on the columns that already have histograms.

SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns.

INTEGER VALUE: Number of histogram buckets. Any value between 1 -254. Note that setting 1 will disable the Histogram collection

This basically implies that Oracle will automatically create histograms on those columns which have skewed data distribution and there are SQL statements referencing those columns.

TO VERIFY YOUR CURRENT SETTING

SQL> select dbms_stats.get_prefs ('METHOD_OPT') from dual;
DBMS_STATS.GET_PREFS('METHOD_OPT')
---------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
 

TO CHANGE YOUR SETTING TO “REPEAT”

SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE REPEAT');

TO DISABLE THE HISTOGRAM COLLECTION

SQL> exec dbms_stats.set_global_prefs ('METHOD_OPT','FOR ALL COLUMNS SIZE 1');

HOW TO CREATE / DELETE HISTOGRAMS

If DBMS_STATS gathers statistics is used for a table, and if the queries have referenced the columns in this table, then Oracle Database creates histograms automatically as needed. Oracle engine updates the data dictionary table SYS.COL_USAGE$ regularly based on user queries running in the database.

You can simply let Oracle collect all the histograms it wants and then drop any of histograms that cause you performance problem and then set the preference to tell the optimizer not to gather it again.

The following command drops the histogram on the EMP_ID column:

BEGIN
 dbms_stats.Delete_column_stats(ownname => 'SCOTT', tabname => 'EMP',
 colname => 'EMP_ID', col_stat_type => 'HISTOGRAM');
END;
/

The following command prevents the Optimizer from creating a histogram further on the EMP_ID column in the EMP table:

BEGIN
dbms_stats.Set_table_prefs('SCOTT', 'EMP',
'METHOD_OPT'=>'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 EMP_ID');
END;
/

WHEN TO CREATE HISTOGRAMS

Create Histograms in below situation:

1) Create for the column which are included in the WHERE clause
2) AND also when the column is highly skewed.

Note that Histograms are not just for indexed column but also for the non-indexed columns

Do not create Histograms in below situation:

1) Data in column is evenly distributed.
2) Column is not used in a where clause.
3) Do not create on every column of the table as it will increase the stats collection time, parsing time and also can cause optimize to generate bad plans.
4) Do not create Histograms on primary key.
5) Do not create histogram when the column is unique and is used only with equality predicates

TIPS FOR HISTOGRAMS

1) Frequency histograms are more precise, than Height Balanced histograms.
2) Histograms are stored in the dictionary and maintenance and space cost is there. So create histograms only when required.
3) Histograms are not gathered on columns for which there is no predicate information in the col_usage$ table.
4) col_usage$ is only populated when columns referenced in predicates and hard parsing occurred.

Securing Your Logical Standby Database

A logical standby mines the primary database redo and reconstructs the higher level SQL operations that resulted in the database change, and then applies these SQL statements to maintain the standby database. The benefits to using a logical standby is that

• It can be open for reads
• Can support indexes and materialized views
• Can add tables, schemas and have read-write access to these tables and schemas (as long as they are not managed by redo apply)
• Perform rolling upgrades, for example the primary may be using 10g and the logical standby could be using 11g
• Idea for testing patch sets
• Idea as a staging system to propagate changes

The logical standby database is in Read-Write mode means a fully updatable mode. A database can be accessed and potential data may be changed. So Oracle data guard provides a lock. We can configure GUARD on the database at a different level according to our requirement.

A user access can be controlled with GUARD.

We can enable GUARD with ALTER DATABASE GUARD [ALL | STANDBY | NONE] command.

There are three level for GUARD.

ALL: We can use ALTER DATABASE GUARD ALL  statement. It prevents users from making changes to any data in the database.

STANDBY: It Prevents users from making changes to any data maintained by Data Guard SQL Apply

NONE: If we provide Guard none then it provides normal security a user which have access to objects can change data in that.

To check Guard_status use following query.

SQL> select guard_status from v$database;

GUARD_STATUS

------------------------------

STANDBY

Note: You can set your value based on requirement.

Monitor Docker metrics using Prometheus & Grafana

Prometheus and Grafana are great tools for both metric data collection and visualization. Using Prometheus and Grafana, here we shall create a dashboard to monitor Docker container. 

Assume, we have already a setup with Grafana & Prometheus  and a docker container.

To setup Prometheus & Grafana, Click here.

To setup Docker container, Click here

Step-1: Check docker service is active or not

[root@docker docker]# service docker status| grep active

Redirecting to /bin/systemctl status docker.service

     Active: active (running) since Thu 2023-03-30 06:03:24 UTC; 47s ago

[root@docker docker]#

Step-2 : Add below file in docker server/ machine

vi /etc/docker/daemon.json

{

 "metrics-addr" : "172.31.37.33:9393",

 "experimental" : true

}

[root@docker docker]# service docker restart

Step3: Check running containers if any

[root@docker docker]# docker ps -a

CONTAINER ID   IMAGE     COMMAND       CREATED         STATUS          PORTS     NAMES

deb6378efe5b   ubuntu    "/bin/bash"   2 minutes ago   Up 6 seconds              container5

e2967e62a390   ubuntu    "/bin/bash"   2 minutes ago   Up 14 seconds             container4

d044849ad38e   ubuntu    "/bin/bash"   2 minutes ago   Up 30 seconds             container3          container1

[root@docker docker]#

Step-4: Edit prometheus.yml file and restart Prometheus service

[root@grafana ~]# cd /home/ec2-user/prometheus/prometheus_setup/

# vi prometheus.yml

static_configs:

      - targets: ["localhost:9090"]

      - targets: ["172.31.37.33:9100"]

      - targets: ["172.31.37.33:9323"]   ## added for docker

!wq!

# systemctl restart prometheus.service

Step-5 : Now connect to Prometheus server and check this target status.

It should be "Up"

Step-6: Create new dashboard/ panel

use below PromoSQL

sum by(state)(engine_daemon_container_states_containers{})

Now you will have data in your panel:

Manage dashboards - Grafana Dashboard Exporter/Importer

 Importing monitoring template from Grafana lab to grafana dashboard

Add custom Grafana dashboards:

You must have a Grafana account with admin rights to perform the steps described in this section.

To add a custom Grafana dashboard:

• Login to Grafana and create a new dashboard.
• Export your dashboard to json file. See Grafana documentation to find out how to do this.
• Put the exported file under the grafana-dashboards/ folder.

Use below official website to download json file:

https://grafana.com/grafana/dashboards/1860-node-exporter-full/

    node-exporter-full_rev30.json

Grafana Home --> Dash board --> Import --> Import Json file --> Upload Json file (node-exporter-full_rev30.json) --> choose Data source as Prometheus --> Import

Manually I added load to one target machine which is configured here. You can use last 15 mins data and see the chnages.

-- cpu load

# while true; do /bin/true; done

-- memory load

# cat <( </dev/zero head -c 500m) <(sleep 120) | tail

So, the take away is, no need to create all dash boards manually. Many are already developed and available in grafana official website. Just use it with documentation.

Try to create your another dashboard using below json file:

https://grafana.com/grafana/dashboards/13978-node-exporter-quickstart-and-dashboard/

    node-exporter-quickstart-and-dashboard_rev2.json

Grafana & Prometheus - Setup (Part-2)

 Grfana Dashboard setup(Part-1)

Here we can continue to setup grafana. so that we can have stunning visualizations of Prometheus server data collection.

 For my lab setup, I shall use same Prometheus server for grafana setup. But you can use different server also in real case scenario.

Go to grafana official website 

https://grafana.com/ --> Downloads --> Self-managed --> select Grafana --> Download --> select (OSS) Open source softwae --> select Linux version

[root@grafana ec2-user]# mkdir grafana

[root@grafana ec2-user]# cd grafana/

[root@grafana grafana]# pwd

/home/ec2-user/grafana

# wget https://dl.grafana.com/oss/release/grafana-9.4.7-1.x86_64.rpm

# sudo yum install grafana-9.4.7-1.x86_64.rpm

[root@grafana grafana]# service grafana-server restart

Restarting grafana-server (via systemctl):                 [  OK  ]

-- Enable always on for both grafana and Prometheus

[root@grafana grafana]# chkconfig grafana-server on

[root@grafana grafana]# systemctl enable prometheus.service

-- in targets/ nodes also enable node exporter

[root@target node_exporter]# systemctl enable node_exporter.service

Note: Grafana runs on default 3000 port. make sure this port allowed in security group.

Now open grafana URL.

http://54.169.181.69:300

It ll ask you username and password. By default username and password will be "admin".

As soon as you logged in, it ll prompt you to change password:

Now you need to configure data source.. Grafana support lots of data sources:

select Configuration --> Data source

Now add prmotheus data souce like below:

URL: http://<private-ip>:9090/

Then "save & Test". You can see message like "Data souce is working". If error need to check whether IP address used wrongly etc.

Now create your dashboard:

Select Dash board --> New Dash board --> New Panel 

Now you need to choose data source. Here we have only Prometheus data source as of now.

then, use same PromoQL. for that select "Code" and use the query and click on "Run queries"

Here is my first dashboard. You can give name to this panel also. You can create many panels and dashboards as per your requirements.

Grafana & Prometheus - Setup (Part-1)

 About Gaafana:
Grafana is a multi-platform open source analytics and interactive visualization web application. It provides charts, graphs, and alerts for the web when connected to supported data sources. Most high lighted part is "Grafana provides you with tools to turn your time-series database (TSDB) data into insightful graphs and visualizations".

About Prometheus:
Prometheus is an open-source technology designed to provide monitoring and alerting functionality for cloud-native environments, including Kubernetes. It records real-time metrics in a time series database built using a HTTP pull model, with flexible queries and real-time alerting.. It can also collect and record labels, which are optional key-value pairs.
Let us proceed with a lab setup to configure Prometheus and Grafana. Here I am using free tier AWS account with EC2 instances to setup my lab environment.

My architecture diagram will be like below: (simple monitoring architecture)

Installation steps:

Installation of Prometheus on Linux Server:

# sudo hostname grafana

# sudo su

[root@grafana ~]# cd /home/ec2-user/

# mkdir prometheus

# cd prometheus

# 

Step#1 : Install Prometheus 

https://prometheus.io/  --> download --> prometheus-2.43.0.linux-amd64.tar.gz (copy this link)

[root@grafana prometheus]# wget https://github.com/prometheus/prometheus/releases/download/v2.43.0/prometheus-2.43.0.linux-amd64.tar.gz

...

prometheus-2.43.0.linux-amd64 100%[=================================================>]  86.87M  17.2MB/s    in 6.4s

[root@grafana prometheus]# tar -xzvf prometheus-2.43.0.linux-amd64.tar.gz

[root@grafana prometheus]# mv prometheus-2.43.0.linux-amd64 prometheus_setup

[root@grafana prometheus]# # cd prometheus_setup/

# pwd

/home/ec2-user/prometheus/prometheus_setup

Step#2 : Configure Prometheus

# cd /etc/systemd/system

# vi prometheus.service

[Unit]

Description=Prometheus Server

Documentation=https://prometheus.io/docs/introduction/overview/

After=network-online.target

[Service]

User=root

Restart=on-failure

ExecStart=/home/ec2-user/prometheus/prometheus_setup/prometheus --config.file=/home/ec2-user/prometheus/prometheus_setup/prometheus.yml

[Install]

WantedBy=multi-user.target

:wq

Restart Prometheus

[root@grafana prometheus_setup]# pwd

/home/ec2-user/prometheus/prometheus_setup

[root@grafana prometheus_setup]# systemctl restart prometheus.service

verify status

#  systemctl status prometheus.service

prometheus.service - Prometheus Server

     Loaded: loaded (/etc/systemd/system/prometheus.service; disabled; preset: disabled)

     Active: active (running) since Sat 2023-03-25 19:03:44 UTC; 57s ago

Step#3 : Allow default Prometheus port 9090 in security group

security Group -->  select your security group --> Inbound rules --> Edit inbound rules --> Add rule --> Custom TCP --> 9090 --> 0.0.0.0/0 (internet) --> save 

Step#4: Use Prometheus from browser

copy the public IP and used 9090 port

http://54.169.181.69/9090

Now you have Prometheus Home page

Setup Target

Now we can configure target machines to start monitoring. Here at least we can configure one target machine.

Go to Prometheus official website --> Click on node exporter 

https://prometheus.io/download/#node_exporter

copy node_exporter-1.5.0.linux-amd64.tar.gz link

[root@target ~]# cd /home/ec2-user/

[root@target ec2-user]# mkdir node_exporter

[root@target ec2-user]# cd node_exporter

[root@target node_exporter]# wget https://github.com/prometheus/node_exporter/releases/download/v1.5.0/node_exporter-1.5.0.linux-amd64.tar.gz

[root@target node_exporter]# tar xzvf node_exporter-1.5.0.linux-amd64.tar.gz

# mv node_exporter-1.5.0.linux-amd64 node_exporter

[root@target node_exporter]# pwd

/home/ec2-user/node_exporter/node_exporter

[root@target node_exporter]# ls -l

total 19336

-rw-r--r--. 1 3434 3434    11357 Nov 29 19:05 LICENSE

-rw-r--r--. 1 3434 3434      463 Nov 29 19:05 NOTICE

-rwxr-xr-x. 1 3434 3434 19779640 Nov 29 18:59 node_exporter

[root@target node_exporter]#

Now create node exporter service file like previous. Name of files are fixed. You can't use custom names.

# touch /etc/systemd/system/node_exporter.service

# vi /etc/systemd/system/node_exporter.service

[Unit]

Description=Node_exporter Server

Documentation=https://prometheus.io/docs/introduction/overview/

After=network-online.target

[Service]

User=root

Restart=on-failure

ExecStart=/home/ec2-user/node_exporter/node_exporter/node_exporter

[Install]

WantedBy=multi-user.target

:wq!

Now restart node exporter and check status:

[root@target node_exporter]# systemctl restart node_exporter.service

[root@target node_exporter]# systemctl status node_exporter.service

● node_exporter.service - Node_exporter Server

     Loaded: loaded (/etc/systemd/system/node_exporter.service; disabled; preset: disabled)

     Active: active (running) since Sat 2023-03-25 19:59:12 UTC; 15s ago

Now attach this target to prometheus server:

Go to prometheus server

Edit prometheus.yml and node/target machine details

[root@grafana prometheus_setup]# pwd

/home/ec2-user/prometheus/prometheus_setup

[root@grafana prometheus_setup]# ls prometheus.yml

prometheus.yml

Note: Use private IP address for the targets as our ab setup resides on same netwrok.

# vi prometheus.yml

 static_configs:

      - targets: ["localhost:9090"]

      - targets: ["172.31.37.33:9100"]

Note: Node exporter service runs on 9100 port and you need to allow this port as well.

Now again Prometheus service.

Now check whether you able to see node/ target details in Prometheus dashboard or not.

Yes we can see it.

Prometheus Home --> Status --> Targets 

Here we can see our target machine/ node machine.

You can monitor targets using PromoSQL (queries)

e.g.,

Use below query in search query box and click on execute and select graph

sum by(mode)(irate(node_cpu_seconds_total{mode!="idle"}[5m])) > 0

Here we can see this CPU load metric values with interval of 5 mins as per our PromoSQL query.

But, this graph is not so much user friendly and informative. For this w can setup and use grafana dash board .

Click on  below grafana dashboard setup link :

http://facedba.blogspot.com/2023/03/grafana-prometheus-setup-part-2.html

Grafana & Prometheus - Setup (part-2)