- Some scripts are a compilation of several other scripts I've created.
- Some scripts are based on and adapted from existing scripts. Those that I found reference to the author I obviously identify in the script.
- 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.
- 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.
- Feel free to make changes, but please keep the reference to the authors.
- If you find any mistakes, please let me know.
terça-feira, 4 de fevereiro de 2025
[ORACLE] Useful scripts for the day-to-day life of a DBA (Part 1)
quinta-feira, 30 de janeiro de 2025
[ORACLE] Create OCI DB System using the console.
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.
Select the storage management software, and configure the storage performance and size.
- Standard Edition
- Enterprise Edition
- Enterprise Edition High Performance
- Enterprise Edition Extreme Performance.
11) Configure the SYS and TDE passwords, as well as the backup service.
After 90 minutes (average), you will have a DB system ready to use.
And that's it. I hope it helps you.
quinta-feira, 9 de janeiro de 2025
[ORACLE] Alter database open resetlogs failed after restore. And the problem was my Redo logs.
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
set pages 120 lines 10000;
SELECT
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;
I can't drop the log file group because the status “CLEARING_CURRENT”, of course.
Database altered.
SQL> ALTER DATABASE RENAME FILE '+REDO/FOLHAHOM/ONLINELOG/group_11a.rdo' TO '+REDO1/FOLHAHOM/ONLINELOG/group_11a.rdo';
Database altered.
SELECT
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;
Yes, but I preferred to recreate all the log file groups.
Database altered.
SQL> alter database add logfile thread 2 ('+REDO1','+REDO2') size 512M;
Database altered.
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.
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.
System altered.
SELECT
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;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 11;
Database altered.
segunda-feira, 18 de novembro de 2024
[ORACLE] Embedding ONNX model for Vector Search tests with Oracle Database 23ai
alter session set container=freepdb1;
DBMS_VECTOR.LOAD_ONNX_MODEL (
directory => 'DATA_DIR',
file_name => 'all_MiniLM_L12_v2.onnx',
model_name => 'ALL_MINILM_L12_V2');
END;
/
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
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
vector_coord VECTOR
);
(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.
Mario
sábado, 27 de julho de 2024
[ORACLE] Quick tips: Autonomous Health Framework (AHF) files cleanup.
tfactl purge -older 25d
I could run it using hours, for example:
tfactl purge -older 200h
The full syntax and parameters are:
segunda-feira, 15 de julho de 2024
[ORACLE] Increasing the number of cores in a virtualized Oracle Database Appliance.
- 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.
su -
ubiosconfig export all -x bios.xml
su -
ubiosconfig import config -f --expert -y -x bios.xml
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"
}
quinta-feira, 11 de julho de 2024
[ORACLE] How to create a test environment with Oracle 23ai running on MacBook Pro M3 Pro.
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.
# Check if Colima is using the new profile
docker context ls
Finally, create a docker container running Oracle 23ai:
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...
