[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] Redo log file status are CLEARING or CLEARING_CURRENT after Restore.

Hello everyone.

Did you have this problem after restore?

Ohhh my good! WTF?

The first command "alter database open resetlogs" is abnormally abrupted leaving the redo log file status as CLEARING or CLEARING_CURRENT in controlfile.

I'll show you:

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;

The solution is simple, but always remember:

• This step-by-step worked for me, but it may not work for you.
• This post is for help you, but never take my word for it, test it well before use.

Execute the clear unarchived command:

    alter database clear unarchived logfile group 1;

    alter database clear unarchived logfile group 2;

    alter database clear unarchived logfile group 3;

    alter database open resetlogs;

Here you done!

Or, you can recreate a controlfile. If you want to know more about this solution, check the note ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (Doc ID 1352133.1).

A quick and simple tip, but one that helps a lot.

Regards

Mario