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

Hello everyone.

 

Hope you're doing well!

As I said here, I've created a repository on GITHUB to share some scripts that I like to make and that help me in my day-to-day life as a DBA.

 

Today's script is showmetrics.sql and is in the ENVIRONMENT directory.     
 

This script is used to check all system metric values of the long-duration and their details. If the standard deviation in the metrics is high, it marks the metric for verification.

 

It's the first version, so I'm sure the Advisor needs some 'tuning' 😆. The attempt here is to mark the metrics with a standard deviation above 50% for verification.

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

And more important, my script, my rules for advice, ok? 😆

If you think you have a better metric, and want to suggest it, I'd be happy to listen and analyze it.

And always remember:

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.

Feel free to download here.

Regards

Mario Barduchi

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

Hello all 

 

Hope you're doing well!

As I said here, I've created a repository on GITHUB to share some scripts that I like to make and that help me in my day-to-day life as a DBA.

 

 

Today's script is showplan.sql and is in the SQLPLAN directory.     
 
The script serves to help monitor and check active execution plans and try to create an advisor for sql plans with problems. 

 

It's the first version, so I'm sure the Advisor needs some 'tuning' 😆. The attempt here is to flag potentially problematic plans, just a starting point for further analysis.

You can use this script in combination with Carlos Sierra's coe_xfr_sql_profile.sql to define a better plan, for example.

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

My script, my rules for advice and tuning, ok?

 

The repository is https://github.com/MarioBarduchi/Scripts2DBA.

 

The script is: https://github.com/MarioBarduchi/Scripts2DBA/blob/main/SQLPLAN/showplan.sql

 

And always remember:

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.

Feel free to download and use.

Regards

Mario Barduchi

[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

 

 

[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:

Installing Colima and Docker in Apple Silicon ARM based machines

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

[ORACLE] Simple trick about DBMS_JOBS.

Hello everyone!!!

Hope you're doing well!

Today, I'm going to show you a simple trick about dbms_job. 

Ok, Ok!!! I know we should use the dbms_scheduler, but sometimes the customer uses an "old school" schedule, and they don't want to change.

In this case, if you run a creation with the SYS user, for example, the job will be created under the SYS User.

But you can change this using SYS.DBMS_IJOB.CHANGE_ENV.

For example:

exec dbms_job.submit (

   job       => jobno,
   what      => 'begin USER_DEST.proc; end;',
   next_date => SYSDATE+1,
   interval  => 'trunc(sysdate + 1) + 10/1440');   
  

SELECT 

JOB, 
LOG_USER,
PRIV_USER,
SCHEMA_USER,
LAST_DATE,
LAST_SEC,
WHAT 
FROM dba_jobs
WHERE JOB = 18068;

    JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC  WHAT
------- -------- --------- ----------- --------- --------- -------  

  18068 SYS     SYS    SYS        17-JUN-24 09:00:00 USER_DEST.proc;

If I need to change the owner:

EXEC SYS.DBMS_IJOB.CHANGE_ENV(
JOB    => 18068, 
LUSER  => 'USER_DEST', 
PUSER  => 'USER_DEST', 
CUSER  => 'USER_DEST', 
NLSENV => NULL);

Where:
JOB    => Job number 
LUSER  => Log user
PUSER  => Priv user
CUSER  => Schema User
NLSENV => NLS Language

Example:
-- Change only PRIV_USER
EXEC SYS.DBMS_IJOB.CHANGE_ENV(18068,NULL,'USER_DEST',NULL,NULL);
COMMIT;

-- Change all
EXEC SYS.DBMS_IJOB.CHANGE_ENV(18068,'USER_DEST','USER_DEST','USER_DEST',NULL);
COMMIT;

SELECT 

JOB, 
LOG_USER,
PRIV_USER,
SCHEMA_USER,
LAST_DATE,
LAST_SEC,
WHAT 
FROM dba_jobs
WHERE JOB = 18068;

    JOB LOG_USER   PRIV_USER  SCHEMA_USER LAST_DATE LAST_SEC  WHAT
------- ---------  ---------  ----------- --------- --------- -------

  18068 USER_DEST  USER_DEST  USER_DEST   17-JUN-24 09:00:00 USER_DEST.proc;

As simple as that!

I hope this helps you!

Regards

Mario

[ORACLE] Cleanup Repository Area - Oracle Database Appliance (ODA)

Hello everyone.

How are you doing?

Today, I'll share with you a simple tip about ODA.

When I need to clean the repository area, I use these steps:

But always remember:  

• 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.

1) List your dbhomes.

# odacli list-dbhomes

2) Check the directory size (/opt).

df -h
Filesystem                          Size  Used Avail Use% Mounted on
devtmpfs                            189G  176K  189G   1% /dev
tmpfs                               189G  845M  188G   1% /dev/shm
tmpfs                               189G  4.1G  185G   3% /run
tmpfs                               189G     0  189G   0% /sys/fs/cgroup
/dev/mapper/VolGroupSys-LogVolRoot   30G  9.6G   19G  35% /
/dev/md0                            476M  123M  329M  28% /boot
/dev/sda1                           500M  8.6M  492M   2% /boot/efi

/dev/mapper/VolGroupSys-LogVolOpt    59G   45G   11G  81% /opt

/dev/mapper/VolGroupSys-LogVolU01    99G   47G   47G  51% /u01
/dev/asm/datprds-31                 370G  291G   80G  79% /u02/app/oracle/oradata/prd
/dev/asm/commonstore-31             5.0G  631M  4.4G  13% /opt/oracle/dcs/commonstore
/dev/asm/datprodp-31                2.2T  1.6T  673G  70% /u02/app/oracle/oradata/mcg
/dev/asm/redo-365                   236G  184G   53G  78% /u04/app/oracle/redo
/dev/asm/reco-8                     1.8T  865G  936G  49% /u03/app/oracle
/dev/asm/datprodmv-31                17T   17T   16G 100% /u02/app/oracle/oradata/prodmvx7hmcg
tmpfs                                38G     0   38G   0% /run/user/54325
tmpfs                                38G     0   38G   0% /run/user/1000
tmpfs                                38G     0   38G   0% /run/user/0


3) Check the repository area.

cd /opt/oracle/oak/pkgrepos

ll -lrht orapkgs/clones/
total 9.0G
-rw-r--r-- 1 root root 2.9G Jan 15  2021 db112.210119.tar.gz
-rw-r--r-- 1 root root 6.1G Feb  8  2021 grid19.tar.gz
-rw-r--r-- 1 root root  11K Feb 12  2021 clonemetadata.xml

du -sh *
38M     asr
16G     db
44M     dcsadmin
609M    dcsagent
46M     dcscli
175M    dcscontroller
32M     firmwarecontroller
48M     firmwaredisk
12M     firmwareexpander
8.6M    hmp
216M    ilom
238M    java
981M    mysql
79M     oak
585M    opatch
9.7G    orapkgs
527M    os
91M     oss
3.4M    System
302M    thirdpartypkgs
8.4M    zookeeper

4) Cleanup the repo area

# odacli cleanup-patchrepo -cl

# odacli describe-job -i "5793e96c-159e-46cc-999e-938e339e8773"

5) Check the directory size (/opt).

df -h

Filesystem                          Size  Used Avail Use% Mounted on
devtmpfs                            189G  176K  189G   1% /dev
tmpfs                               189G  845M  188G   1% /dev/shm
tmpfs                               189G  4.1G  185G   3% /run
tmpfs                               189G     0  189G   0% /sys/fs/cgroup
/dev/mapper/VolGroupSys-LogVolRoot   30G  9.6G   19G  35% /
/dev/md0                            476M  123M  329M  28% /boot
/dev/sda1                           500M  8.6M  492M   2% /boot/efi

/dev/mapper/VolGroupSys-LogVolOpt    59G   39G   18G  70% /opt

/dev/mapper/VolGroupSys-LogVolU01    99G   47G   47G  51% /u01
/dev/asm/datprds-31                 370G  291G   80G  79% /u02/app/oracle/oradata/prd
/dev/asm/commonstore-31             5.0G  631M  4.4G  13% /opt/oracle/dcs/commonstore
/dev/asm/datprodp-31                2.2T  1.6T  673G  70% /u02/app/oracle/oradata/mcg
/dev/asm/datprodp-31                2.2T  1.6T  673G  70% /u02/app/oracle/oradata/mcg
/dev/asm/redo-365                   236G  184G   53G  78% /u04/app/oracle/redo
/dev/asm/reco-8                     1.8T  866G  935G  49% /u03/app/oracle
/dev/asm/datprodmv-31                17T   17T   16G 100% /u02/app/oracle/oradata/prodmvx7hmcg
tmpfs                                38G     0   38G   0% /run/user/54325
tmpfs                                38G     0   38G   0% /run/user/1000
tmpfs                                38G     0   38G   0% /run/user/0

6) Check the repository area.

cd /opt/oracle/oak/pkgrepos

ll -lrht orapkgs/clones

total 2.9G
-rw-r--r-- 1 root root 2.9G Jan 15  2021 db112.210119.tar.gz
-rw-r--r-- 1 root root  11K Feb 12  2021 clonemetadata.xml

du -sh *

38M     asr
16G     db
44M     dcsadmin
609M    dcsagent
46M     dcscli
175M    dcscontroller
32M     firmwarecontroller
48M     firmwaredisk
12M     firmwareexpander
8.6M    hmp
216M    ilom
238M    java
981M    mysql
79M     oak
585M    opatch
3.6G    orapkgs
527M    os
91M     oss
3.4M    System
302M    thirdpartypkgs
8.4M    zookeeper

That's it. As simple as that.

I hope that this tip help you.

Regards

Mario