terça-feira, 4 de fevereiro de 2025

[ORACLE] Useful scripts for the day-to-day life of a DBA (Part 1)

Hello all 
 
Hope you're doing well!

I've created repository at GITHUB and I'm going to start sharing some scripts that I like to make and that help me in my day-to-day life as a DBA.
 

But some considerations are important:
  1. Some scripts are a compilation of several other scripts I've created.
  2. Some scripts are based on and adapted from existing scripts. Those that I found reference to the author I obviously identify in the script.
  3. So, if there's a script that you're the author of and I've adapted to my reality and it's not mentioned, please let me know and I'll be happy to reference it.
  4. Because they're my scripts, they have my logic and help me in my day-to-day work, it doesn't mean that they're the best or that they're always the most refined and concerned with good practices. They can help you, but that's all.
  5.  Feel free to make changes, but please keep the reference to the authors.
  6.  If you find any mistakes, please let me know.
The first scripts are for monitoring RMAN and one that combines various Advisors' results to suggest SGA tuning.

Remember that these are scripts that you can use and change at your own risk. They help me a lot, but may not be as functional for you.
 
 
Feel free to download and use.

Regards
Mario Barduchi

quinta-feira, 30 de janeiro de 2025

[ORACLE] Create OCI DB System using the console.

Hello everyone!

I hope you're doing well.

Today, I going to show you how to create a new DB system in OCI.

Let's get started.

 
1) Select Oracle Database -> Oracle Base Database Service in the OCI console and click on "Create DB system".

 
 
2) Enter the compartment and DB system names.

 
3) Click on "Change Shape" and select your preferred shape.
 
 
4) Configure the storage. To do this, click on "Change Storage".
 

Select the storage management software, and configure the storage performance and size.


5) Configure the Oracle Database software edition and the DB system node count.

 
We have 4 database software edition options: 
  • Standard Edition
  • Enterprise Edition
  • Enterprise Edition High Performance
  • Enterprise Edition Extreme Performance.
 
6) Add the ssh key and select whether to use your own licences or the license included model.


 
7) Now, specify the VCN and subnet to want to use.
 
 
The private IP will be created automatically, if you don't configure it.
 
 
8) Click "Next".
 
 


9) Now let's configure the database information.

 
Click "Change database image".


10) Select the database image.
 

 

11) Configure the SYS and TDE passwords, as well as the backup service.


Finally, click on “Create DB system”.

After 90 minutes (average), you will have a DB system ready to use.

And that's it. I hope it helps you.
 
Regards
Mario Barduchi

quinta-feira, 9 de janeiro de 2025

[ORACLE] Alter database open resetlogs failed after restore. And the problem was my Redo logs.

Hello all
 
How are you doing?
 
I was creating a new test environment today and I tried to open a database that I had restored. 
 
Simple. Of course not.
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '+REDO/FOLHAHOM/ONLINELOG/group_4a.rdo'
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted
ORA-15001: diskgroup "REDO" does not exist or is not mounted
 
I checked the redo log files. They were wrong and I have no idea why.

set pages 120 lines 10000;
SELECT 
    l.INST_ID, l.THREAD#, l.GROUP#, 
    substr(lf.MEMBER,1,75) as MEMBER, 
    l.SEQUENCE#, l.first_change#, 
    l.BYTES/(1024 * 1024) AS "Size MB", 
    l.MEMBERS, l.ARCHIVED, l.STATUS, 
    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;


Maybe I forgot to set log_file_name_convert. 
 
I don't know and It doesn't matter. I didn't want to do the whole process again.

I can't drop the log file group because the status “CLEARING_CURRENT”, of course.


Kids, don't try it at home? 😜

I'm just kidding, but this solution is a workaround and should only be used in an emergency.

And remember: This is a test environment. Production can and probably will be different, so use at your own risk.

Let's go to the party:
 
-- Thread 1
SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_6a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_6a.rdo';
Database altered.

-- Thread 2
SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_11a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_11a.rdo';
Database altered.

set pages 120 lines 10000;
SELECT 
    l.INST_ID, l.THREAD#, l.GROUP#, 
    substr(lf.MEMBER,1,75) as MEMBER, 
    l.SEQUENCE#, l.first_change#, 
    l.BYTES/(1024 * 1024) AS "Size MB", 
    l.MEMBERS, l.ARCHIVED, l.STATUS, 
    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;


 
Can I rename all the members of the log file?

Yes, but I preferred to recreate all the log file groups.
 
 
 
SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.
 
SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL>  alter database add logfile thread 1 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.

 
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 5;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 7;
Database altered.

SQL>
ALTER DATABASE DROP LOGFILE GROUP 8;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 13;
Database altered.

SQL> alter system checkpoint;
System altered.

I checked that the old groups were inactive and dropped the last two groups.

set pages 120 lines 10000;
SELECT 
    l.INST_ID, l.THREAD#, l.GROUP#, 
    substr(lf.MEMBER,1,75) as MEMBER, 
    l.SEQUENCE#, l.first_change#, 
    l.BYTES/(1024 * 1024) AS "Size MB", 
    l.MEMBERS, l.ARCHIVED, l.STATUS, 
    lf.IS_RECOVERY_DEST_FILE, l.CON_ID
FROM gv$log l
JOIN gv$logfile lf ON
    l.INST_ID = lf.INST_ID AND
    l.GROUP# = lf.GROUP#
ORDER BY 1,2,3;


SQL> ALTER DATABASE DROP LOGFILE GROUP 6;
Database altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 11;
Database altered.
 
SQL> ALTER DATABASE ENABLE thread 2;
 
 
And that's it. I hope I've helped you.

Regards
Mario


 
 


segunda-feira, 18 de novembro de 2024

[ORACLE] Embedding ONNX model for Vector Search tests with Oracle Database 23ai

Hello, everyone.

How's it going?

Today, I'm going to show you how to load an ONNX model into the Oracle 23ai database for your vector search tests. 

But first, what is ONNX?1

Open Neural Network Exchange (ONNX) is an open format built to represent machine learning models. ONNX defines a common set of operators - the building blocks of machine learning and deep learning models - and a common file format to enable AI developers to use models with a variety of frameworks, tools, runtimes, and compilers.

You can find more information about ONNX here.

To begin with, we'll need to download an ONNX model. We can do this here.
  
In my example, I'm use a docker environment to tests and I need to copy the ONNX zip to this environment.

For copy:
docker cp ONNX/all_MiniLM_L12_v2_augmented.zip Orcl23aiORDS:/opt/oracle/DATA_DIR
 

Unzip the file:
 
 

Execute the grants bellow.

sqlplus / as sysdba
alter session set container=freepdb1;
    GRANT DB_DEVELOPER_ROLE, CREATE MINING MODEL TO LAOUC24;
    CREATE OR REPLACE DIRECTORY DATA_DIR AS '/opt/oracle/DATA_DIR';
    GRANT READ,WRITE ON DIRECTORY DATA_DIR TO LAOUC24;
  


Finally, load the model into the database.

sqlplus laouc24/Password123##@FREEPDB1
 
exec DBMS_VECTOR.DROP_ONNX_MODEL (
        model_name => 'ALL_MINILM_L12_V2', 
        force => true
);
 
BEGIN
        DBMS_VECTOR.LOAD_ONNX_MODEL (
                directory => 'DATA_DIR',
                file_name => 'all_MiniLM_L12_v2.onnx',
                model_name => 'ALL_MINILM_L12_V2');
END;
/  
 

 
Check that the model is loaded into the database and run the first test.

SELECT 
    model_name, 
    algorithm, 
    mining_function 
FROM user_mining_models 
WHERE 
     model_name = 'ALL_MINILM_L12_V2';


SELECT 
    VECTOR_EMBEDDING ( 
 ALL_MINILM_L12_V2 USING 'It is a pleasure to be here at LAOUC Tour 2024!' as DATA
    ) AS embedding;


Now you're ready to create your first table, load the data and vectors and start using Vector Search

CREATE TABLE laouc24.vector1 (
        id                      NUMBER PRIMARY KEY,
        name                VARCHAR2(50),
        vector_coord    VECTOR
);

INSERT INTO laouc24.vector1 (id, name, vector_coord) VALUES
        (1, 'Mr. Morcego'           , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Bat' as data)),
        (2, 'Mr. 7 Mountains'      , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. 7 Mountains' as data)),
        (3, 'Mr. Martin'               , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Martin' as data)),
        (4, 'Mrs. Maria Padilha' , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mrs. Maria Padilha' as data)),
        (5, 'Mr. Litle Fire'            , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Litle Fire' as data)),
        (6, 'Mrs. Maria Quiteria' , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mrs. Maria Quiteria' as data)),
        (7, 'Mr. Seven'               , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Seven' as data)),
        (8, 'Mr. World Spinner'   , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. World Spinner' as data)),
        (9, 'Mr. White Stone'      , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. White Stone' as data)),
        (10, 'Mr. Litle Darkness' , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Litle Darkness' as data)),
        (11, 'Mr. Marian'             , VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Mr. Marian' as data));

        
COMMIT;

SELECT * FROM  laouc24.vector1 where id = 4;


And that's it. Your database is ready for testing using Vector Search.

For more examples and information, you can access this posts here and here.

I hope this tip has helped you.


Regards
Mario




sábado, 27 de julho de 2024

[ORACLE] Quick tips: Autonomous Health Framework (AHF) files cleanup.

Hello everyone.
 
How are you doing?
 
Today, I going to show you a quick tip about the AHF - Autonomous Health Framework.
 
In case your autopurge is off, and your disk space is slowing, you can do a manual purge.
 
And it's a simple operation.
 
1) Check the space
 
df -h
 

2) Lists all the files that would be deleted without deletion - as root.
 
For example, I'm only going to clear the last 10 days (my retention is 30 days). 
  
tfactl purge -older 20d -dryrun
 
 
 
3) Delete the files
 
tfactl purge -older 25d
 
I could run it using hours, for example:
 
tfactl purge -older 200h
 

The full syntax and parameters are:
 

 
 
4) Check the space
 
 
5) To enable autopurge:
 
tfactl set autopurge=on
 
 
You can manage the options using tfactl menu:
 
tfactl menu
 
 
For more information: tfactl purge
 
And, there you go. The space has been cleared.
 
It`s as simple as that!
 
I hope this tip helps you.
 
Regards
Mario
 
 

segunda-feira, 15 de julho de 2024

[ORACLE] Increasing the number of cores in a virtualized Oracle Database Appliance.

Hello everyone.

How are you going?

Today, I'll show you the process to increase the number of cores in a virtualized ODA HA X7-2.

Important: This process must be validated with Oracle Support, always.
 
Furthermore, remember that:  
  • This step-by-step worked for me, but it may not work for you. 
  • It's a test environment. The real life will be different, for sure.
  • This post is for study and testing as well and has no concern for performance and security best practices.

So, let's get started!
 
The first step is create a new BIOS xml file to change the number of the cores.

su -

ubiosconfig export all -x bios.xml



We need to change te parameter below:

vi bios.xml

From: <Active_Processor_Cores>3</Active_Processor_Cores>

To:      <Active_Processor_Cores>4</Active_Processor_Cores>

In my example, I'll change to 3 cores for 4 cores.

Now, we need to import the new parameter.

su - 

ubiosconfig import config -f --expert -y -x bios.xml




Reboot the node and we have 4 cores on node 1. Repeat the process for node 2.

Before:


After:





After this, it was easy!

odacli update-cpucore --cores 8

{

  "jobId" : "388ac288-abaf-4304-8a3b-b033e4ca7dde",

  "status" : "Created",

  "message" : null,

  "reports" : [ ],

  "createTimestamp" : "July 11, 2024 02:49:22 AM BRT",

  "resourceList" : [ ],

  "description" : "CPU cores  service update",

  "updatedTime" : "July 11, 2024 02:49:22 AM BRT"

}






And, there you go!

I hope it helps!

Regards
Mario

quinta-feira, 11 de julho de 2024

[ORACLE] How to create a test environment with Oracle 23ai running on MacBook Pro M3 Pro.

Hello everyone.

How are you going?

I've finally surrendered to the Macbook's allure for work. 

And my first challange was to run the Oracle 23ai in my Mac.

So, let's get started!

First, we need to install the homebrew.

/bin/bash -c 
"$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)" 

We don't have an installer to Oracle 23ai for the Mac M3. 


So, I'm use Colima + Docker + QEMU to emulate x86_64 arch and run the Oracle 23ai. 


Colima is an open source container runtime for macOS with minimal configuration. In addition,

QEMU was an open source software virtualization tool that performs hardware emulation. 


Now, we need to install Docker, Docker-compose, Colima and reinstall QEMU.

   

   brew update


   brew install docker


   brew install docker-compose

 

   brew install colima


   brew reinstall qemu


* Yes, I forgot to take the screenshots 


We are to install Rosetta, as well.


   oftwareupdate --install-rosetta


According Apple, "Rosetta is not an app that you open or interact with. Rosetta works

automatically in the background whenever you use an app that was built only for Mac

computers with an Intel processor. It translates the app for use with Apple silicon."


Check the colima status.


   colima help




Stop and delete the old profile.


   colima stop


   colima delete


And start Colima with the parameters bellow:


   colima start --cpu 4 --memory 10 --arch x86_64 --vm-type vz --vz-rosetta


   Where:

      --vm-type=vz             ==> Virtual machine type.

      --vz-rosetta                ==> Enable Rosetta for amd64 emulation.

      --mount-type=virtiofs ==> Volume Mount Driver for vz.

      --arch x86_64            ==> Architecture (x86_64).


   colima status




The default VM created by Colima has 2 CPUs, 60GB storage and 2GB memory runing

Ubuntu.




Access the Colima console:

   colima ssh


   # Check if Colima is using the new profile

   docker context ls




Finally, create a docker container running Oracle 23ai:


 docker run -d -it --name Orcl23ai /
    -p 1527:1521 /
    -p 5507:5500 /
    -p 8087:8080 /
    -p 8447:8443 
    -e ORACLE_PWD=E 
    container-registry.oracle.com/database/free:latest



Check the container and database.

   docker ps --format "table {{.ID}}\t{{.Status}}\t{{.Names}}\t{{.Ports}}"

   docker exec -it Orcl23ai /bin/bash


And thats it. As simple as that.

If you can watch this installation, you can see these videos from my friend Eduardo Claro:

You can also consult the documentation here - it's for Oracle Autonomous Database Free Container Image, but the process is the same.

I hope it helps you.

Regards
Mario

Postagem em destaque

[ORACLE] Useful scripts for the day-to-day life of a DBA (Part 1)

Hello all    Hope you're doing well! I've created repository at GITHUB and I'm going to start sharing some scripts that I like t...