[ORACLE] ORA-46362: Could not translate variable ORACLE_BASE.

Hello evceryone!

I hope you're doing well!

Today, when I tried to start a database using SRVCTL, I made the error below.

[GTP1.ora-rac01 dbs]$ srvctl start database -d GTP

When I started the database using SQLPLUS, it was normal.

I checked the ORACLE_BASE variable.

    [GTP1.ora-rac01 dbs]$ echo $ORACLE_BASE/

    /u01/app/oracle/

And I check the database configuration, as well.

    [GTP1.ora-rac01 dbs]$ srvctl config database -d GTP

Everything seemed normal.

When I check on "Database Error Messages", the error became clearer to me.

This database was restored and its environment variables were wrong. 

Why? I have no idea! 

The solution was to change the ORACLE_HOME variable using SRVCTL SETENV. 

[GTP1.ora-rac01 dbs]$ srvctl setenv database -db GTP -env ORACLE_BASE=$ORACLE_BASE

And there you go! As simple as that!

After that, the database can be started normally.

I hope that 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

[ORACLE] Batch change EDITIONABLE property.

Hello everyone.

Hope you're doing well!

Today, I have a simple case.

 

A test database had many database objects with the EDITIONABLE property set to "N".

Why did this happen? I have no idea!

Of course, you don't have any possibility to making the change manually, object by object.

This isn't a normal thing to happen, so... let's get to the solution.

A simple script solves the problem. 😆

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) First, count the objects that will be changed.

SELECT

    substr(OWNER,1,20) AS OWNER,

    count(1) AS TOTAL,

    EDITIONABLE

FROM

    DBA_OBJECTS

WHERE

OWNER IN (

         'AUDIT_DBAMV','DBAADV','DBACP','DBADW','DBAMV',          

         'DBAPORTAL','DBAPS','DBASGU','DBATUALIZA','EDITOR','IDCE', 

         'MPACS','MVAPI','MVBIKE','MVINTEGRA','MVMOBILE','SITEDS')

AND 

OBJECT_TYPE IN (

         'FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE',

         'PROCEDURE', 'TRIGGER','TYPE','TYPE BODY',

         'SYNONYM','VIEW')

AND 

EDITIONABLE = 'N'

GROUP BY OWNER, EDITIONABLE

ORDER BY OWNER, EDITIONABLE;

2) Generate the script for change.

SET SERVEROUTPUT ON;

SET PAGES 0;

SET VERIFY OFF;

SET FEEDBACK OFF;

SET HEADING  OFF;

     

SELECT 

   'ALTER '||object_type || ' ' || owner || '.' || object_name || ' EDITIONABLE;'

FROM

DBA_OBJECTS 

WHERE

OWNER IN (

         'AUDIT_DBAMV','DBAADV','DBACP','DBADW','DBAMV',          

         'DBAPORTAL','DBAPS','DBASGU','DBATUALIZA','EDITOR','IDCE', 

         'MPACS','MVAPI','MVBIKE','MVINTEGRA','MVMOBILE','SITEDS')

AND 

OBJECT_TYPE IN (

         'FUNCTION','LIBRARY','PACKAGE BODY','PACKAGE',

         'PROCEDURE', 'TRIGGER','TYPE','TYPE BODY',

         'SYNONYM','VIEW')

AND 

EDITIONABLE = 'N'

ORDER BY OWNER;

As simple as that.

But be careful. Some objects may be in heavy use at the moment, and because of this we can generate Library Cache Pin or Library Cache Lock waits - as in the image below. 

Therefore, it's important to run the script at a time when the database workload is low, monitoring the objects in use and the wait events.  

You can check the objects in the library cache using this SQL. This won't give you 100% certainty that the object is currently in use, but it's a good indicator.

SELECT 

     substr(OWNER,1,15) AS OWNER,

     substr(NAME,1,30)  AS NAME, 

     count(1) 

FROM

     GV$DB_OBJECT_CACHE 

WHERE

     NAME IN (

      'TRG_NOTA_FISCAL_CONV_PART','TRG_NOTA_FISCAL_FNFI',

      'TRG_NOTA_FISCAL_VALIDA_CNPJ','TRG_NOTA_FISCAL_PROIBE_CANC_NF',

      'TRG_NOTA_FISCAL_PARTICULAR','TRG_NOTA_FISCAL_AJUSTES',

      'TRG_NOTA_FISCAL_DADOS_NFE','TRG_NOTA_FISCAL_SN_CONTABILIZA',

      'TRG_EVOLUCAO_NOTA_FISCAL','TRG_FNFI_NF_INTEGRA_BENNER',

      'TRG_ATUALIZA_HIST_SIT_NF','TRG_STATUS_NFE_NOTA_FISCAL',

      'TRG_IMVW_OUT_NF_SUNAT')

GROUP BY OWNER, NAME;

3) Once again, count the objects with EDITIONABLE = N.

4) And count the objects with EDITIONABLE = Y.

I hope this simple tip helps you.

Regards

Mario

[ORACLE] How to move a datafile to a new location in ASM.

Hello everyone!

I hope you're doing well!

Today, I needed to move a datafile to another ASM Disk Group.

The process was simple, but remember that this step-by-step worked for me, but it may not work for you.

So do it in a test environment if you're not sure.

Important:

• For SYS and SYSAUX, the procedure will be slightly different, so don't use this step-by-step in these two cases. 
• There are also other ways to do this process, today I chose to do this one.

So, let's go to the party!

1) Check the datafile path

    SQL> SELECT file#, name FROM v$datafile where name like '%zab%';

2) Create a backup as copy with RMAN
    RMAN> BACKUP AS COPY DATAFILE 69 format '+DGFRA';

3) I put the datafile offline

    SQL> ALTER DATABASE DATAFILE 69 offline;

4) I switch & recover the datafile

    RMAN> switch datafile 69 to copy;
    RMAN> recover datafile 69;

5) I put the datafile online

    SQL> ALTER DATABASE DATAFILE 69 online;

6) Check the datafile in the new location 

    SQL> SELECT file#, name FROM v$datafile where name like '%zab%';

7) Delete the old datafile

    RMAN> delete datafilecopy '+DGDATA/PRD/DATAFILE/tbs_zabbix.334.1144323691';

As simple as that!

Regards

Mario

[ORACLE] ORA-16597: Oracle Data Guard Broker detects two or more primary databases. #JoelKallmanDay

Hello everyone

I hope you're doing weel.

Last week, I was doing a test using Data Guard Broker and at the end of the switchover I got the error bellow:

    

       switchover was not sucessful - Timeout

These problems in my alert.log were the main cause of the error, propably:   

LAD:2 network reconnect abandoned
<error barrier> at 0x7ffcb2af9bf0 placed krsl.c@6774
ORA-03135: connection lost contact
*** 2023-10-04 09:57:23.145275 [krsh.c:6348]

When I checked my databases - Primary and DG, the switchover was completed with success.

        select 

            inst_id, database_role, open_mode, log_mode, flashback_on, force_logging        

       from 

            gv$database;

Everything was Ok, but when I checked my DG Broker, the error bellow occurred.

        show configuration

Okay, I admit that I thought: 

I'm a smart guy, a smart DBA, an Oracle ACE, so I'm just remove the configuration and that's it! 😄

Well done!!! Emotional damage!!! I failed, miserably!!! 😅😅😅 

         remove configuration;

The solution to this was, but always remember:

• This step-by-step worked for me, but it may not work for you.
• It's a basic and limited 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)  Check the location of the DG Broker config files

show parameter dg_broker_config_file 

2) Change the dg_broker_start parameter in both environments

     

       alter system set dg_broker_start=false scope=both sid='*';

3) Remove DG Broker config files 

rm -f /u01/app/oracle/product/19.3.0.0/db_1/dbs/dr1IMANDG.dat
rm -f /u01/app/oracle/product/19.3.0.0/db_1/dbs/dr2IMANDG.dat

4) Check if the configuration was removed

       

        show configuration

5) At the end, I'll recreate the DG Broker configs

        create configuration 'DR_IM' as primary database is imandg connect identifier is ironmandg;

 

        add database ironman as connect identifier is ironman maintained as physical;

        enable configuration;

        show configuration verbose;

            Configuration - DR_IM
            Protection Mode: MaxPerformance
              Members:
              imandg  - Primary database
              ironman - Physical standby database 
              Properties:
                FastStartFailoverThreshold      = '30'
                OperationTimeout                = '30'
                TraceLevel                      = 'USER'
                FastStartFailoverLagLimit       = '30'
                CommunicationTimeout            = '180'
                ObserverReconnect               = '0'
                FastStartFailoverAutoReinstate  = 'TRUE'
                FastStartFailoverPmyShutdown    = 'TRUE'
                BystandersFollowRoleChange      = 'ALL'
                ObserverOverride                = 'FALSE'
                ExternalDestination1            = ''
                ExternalDestination2            = ''
                PrimaryLostWriteAction          = 'CONTINUE'
                ConfigurationWideServiceName    = 'ironman_CFG'
      

      Fast-Start Failover:  Disabled
      

      Configuration Status:

          SUCCESS

Yes, it's simple and work's very well!

I'm hope this post helps you.

Regards

Mario

[ORACLE] How do I upgrade the AHF.

Hello everyone!!!

I hope you're doing well!

In my last post here, I've showed how to install the Oracle Autonomous Health Framework (AHF).  

Today, I'll show how you can upgrade the AHF and how to use the auto upgrade as well.  

But always remember:

1. This step-by-step worked for me, but it may not work for you.
2. It's a basic and limited environment. The real life will be different, for sure.
3. This post is for study and testing as well, and has no concern for performance and security best practices.

So, let's get started!!! 

1) Download the AHF zip file

I'll download the last version, "AHF 23.8.0 for Linux". 

For download: Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)

Important: I'll execute all opertations with user ROOT.

2) Check the zip file

There is no need to unzip the file.

3) Check the AHF services 

tfactl print status

4) Set to stage for upgrade (if you don't run auto upgrade process)

ahfctl setupgrade -swstage /u01/AHF

Important: If you don't run the setup upgrade, you'll receive the error below:

5) Finally, I'll run the upgrade

ahfctl upgrade

6) And now, I'll check the services

tfactl print status

The upgrade process also removes the zip file from the staging area.

You can make the process easier if you configure the auto upgrade process.

If you run this setup before upgrade, you don't need to run the stage setup (step 4):

ahfctl setupgrade -all

Enter autoupgrade flag <on/off> : on

Enter software stage location : /u01/AHF

Enter auto upgrade frequency : 30

AHF autoupgrade parameters successfully updated

Successfully synced AHF configuration

refreshConfig() completed successfully.

More information about auto upgrade here.

And that's it my friends. AHF has been updated and is ready to use!!!

So, I hope this has helped.

Regards

Mario

[ORACLE] How do I install the Oracle Autonomous Health Framework (AHF)?

Hello everyone!!!

I hope you're doing well!

Today, I'll show you how to install the Oracle Autonomous Health Framework (AHF). 

I don't have an Oracle RAC installed here on my laptop, but I'll provide it. Today I'll show you a single-node environment.

I decided to write and learn a bit more about AHF after having the privilege of attending Sandesh Rao's live session at Oracle Cloudworld 2023 in Las Vegas.

First, what is Oracle Autonomous Health Framework?

In Oracle's words, it's a "Machine learning-based solution for maintaining database availability and performance".

In Mario's words, it's a "very cool tool that collects and groups your system health information and logs and uses Machine Learning to provide you insights and predictions so that you can act preventively, avoiding problems in your environment".

For more information, feel free to click.

So, let's get started!!!

But always remember:

1. This step-by-step worked for me, but it may not work for you.
2. It's a basic and limited environment. The real life will be different, for sure.
3. This post is for study and testing as well, and has no concern for performance and security best practices.

1) Download the AHF zip file

In my post, I'll download version "AHF 23.7.0 for Linux". 

Calm down, calm down, buddy!!! I know we already have version 23.8 at the time of writing this post (Sep/23), but I'll show you how to upgrade the version on next post. 

For download: Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)

2) Unzip the zip file

Important: I'll execute all installation with user ROOT.

[root@Mariao ~]# cd /u01/AHF

[root@Mariao ~]# unzip AHF-LINUX-v23.7.0.zip

3) Create the data directory.

[root@Mariao ~]# mkdir -p /AHF/Data

3) Run install.

In red, my changes and interactions.

[root@Mariao ~]# cd /u01/AHF/

[root@Mariao AHF]# ./ahf_setup 

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_237000_306422_2023_09_29-09_29_16.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 23.7.0 Build Date: 202307281326

Default AHF Location : /opt/oracle.ahf

Do you want to install AHF at [/opt/oracle.ahf] ? [Y]|N : N

Please Enter new AHF Location : /AHF/Data

AHF Location : /AHF/Data/oracle.ahf

AHF Data Directory stores diagnostic collections and metadata.

AHF Data Directory requires at least 5GB (Recommended 10GB) of free space.

Choose Data Directory from below options : 

1. /AHF/Data/oracle.ahf [Free Space : 12525 MB]

2. Enter a different Location

Choose Option [1 - 2] : 1

AHF Data Directory : /AHF/Data/oracle.ahf/data

Do you want to add AHF Notification Email IDs ? [Y]|N : N

Extracting AHF to /AHF/Data/oracle.ahf

Configuring TFA Services

Discovering Nodes and Oracle Resources

Not generating certificates as GI discovered

Starting TFA Services

Created symlink /etc/systemd/system/multi-user.target.wants/oracle-tfa.service -> /etc/systemd/system/oracle-tfa.service.

Created symlink /etc/systemd/system/graphical.target.wants/oracle-tfa.service -> /etc/systemd/system/oracle-tfa.service.

.----------------------------------------------------------------------------.

| Host   | Status of TFA | PID    | Port | Version    | Build ID             |

+--------+---------------+--------+------+------------+----------------------+

| mariao | RUNNING       | 308232 | 5000 | 23.7.0.0.0 | 23700020230728132609 |

'--------+---------------+--------+------+------------+----------------------'

Running TFA Inventory...

Adding default users to TFA Access list...

.---------------------------------------------------------.

|               Summary of AHF Configuration              |

+-----------------+---------------------------------------+

| Parameter       | Value                                 |

+-----------------+---------------------------------------+

| AHF Location    | /AHF/Data/oracle.ahf                  |

| TFA Location    | /AHF/Data/oracle.ahf/tfa              |

| Orachk Location | /AHF/Data/oracle.ahf/orachk           |

| Data Directory  | /AHF/Data/oracle.ahf/data             |

| Repository      | /AHF/Data/oracle.ahf/data/repository  |

| Diag Directory  | /AHF/Data/oracle.ahf/data/mariao/diag |

'-----------------+---------------------------------------'

Starting ORAchk Scheduler from AHF

Setting up AHF CLI and SDK

AHF binaries are available in /AHF/Data/oracle.ahf/bin

AHF is successfully Installed

Do you want AHF to store your My Oracle Support Credentials for Automatic Upload ? Y|[N] : N

Moving /tmp/ahf_install_237000_306422_2023_09_29-09_29_16.log to /AHF/Data/oracle.ahf/data/mariao/diag/ahf/

4) And finally, check the installation.

[root@Mariao ~]# tfactl print status

[root@Mariao ~]#  tfactl toolstatus

[root@Mariao ~]# ahfctl statusahf

 

It's realy easy!

In the next post, I'll show you how to upgrade the AHF version.

So, I hope this has helped.

Regards

Mario