We need sleep function many a times in our code, may be for application logic or even sometimes for monitoring purpose. We always had this available with sleep() function that resides within DBMS_LOCK ==> DBMS_LOCK.SLEEP
This was always a big security risk as granting access to DBMS_LOCK will make a way to get access to other functions/procedures within which is not always necessary.
So from 18C, Oracle comes with SLEEP function within a publicly granted package ==> DBMS_SESSION.SLEEP.
From 18C DBMS_LOCK.SLEEP is deprecated, but it is still present for backwards compatibility.
I would suggest, whoever is planning to upgrade their databases to 19C in near future, should upgrade their PL/SQL codes to use DBMS_SESSION.SLEEP instead of DBMS_LOCK.SLEEP. Also one should make sure to revoke any grants to the DBMS_LOCK package where they were intended to give access to only SLEEP procedure.
One of the best feature I came across recently. Oracle SQL*Plus is now support command line history similar to UNIX “history” command. This feature is available from Oracle 12.2.
I was chatting to one of my dear friend yesterday during Sangam19 and he turned to me and asked “Why should people attend any AIOUG events”. It made me pause to think for a moment. Any type of such events is going to take up your time and will usually cost a decent amount of money. In addition, you’re taking time away from work & its not for vacation.
This post is to share my personal top reasons as to why people should attend AIOUG events:
Expand your professional network:
These events provide great opportunity to network with like minded people and industry peers.It is always helpful to have a healthy professional network. These events bring together people from all different geographical areas under single roof. Also this helps you meeting with people in your field that you haven’t connected in a while.
Expand your knowledge:
No matter how experienced you are at your field, everyone can learn. These is the best opportunity to hear from all Industry experts in person. You will meet all Oracle Gurus wherein they will share their experiences, their learning on the latest technologies. Also you can get guidance from them, also you can clear your doubts.
Learn beyond your skills:
When you are working for the same company for the years, your work & knowledge can become monotonous. Most of the times due to busy work schedule, its very difficult keep yourself up with the technology changes and advancements. These events can help you to know about newest technologies in the market also you can plan your careers accordingly.
To present yourself:
These events are the best opportunities wherein you can market yourself, share your ideas, knowledge. Here you can meet someone who can influence your professional career dramatically. You can develop a reputation as an expert to your peers.
Get certified:
All AIOUG events, provide your considerable discounts on all Oracle certifications. Especially in during Sangam19 Test Fest, people completed their certifications almost in free of cost.
Have Fun:
Last but of course not the least is the fun part. These events are fully filled with fun activities. These events can be a much needed break from your day to day life.
I am happy share some of the best pics from Sangam19:
Few days ago, I was installing OEM agent version 13.3.0.0.0 on my DEV box with OS AIX version 7.2. I was using silent method using agentDeploy.sh.
Previously I had installed it successfully on multiple machines without any issues but this one failed with below error:
java.io.IOException: OUI-10039:Unable to access the inventory /u01/app/oraInventory on this system. Please ensure you have the proper permissions to read/write/search the inventory.
I tried to see if there are any permissions issue on folder /u01/app/oraInventory, I found mentioned directory was not present. So this error is expected one. But why Oracle is searching inventory at incorrect location???
When I dug into associated log file, I found more details about these errors.
2019-10-07 12:20:48,465 WARNING [34] oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader - The inventory pointer location /var/opt/oracle/oraInst.loc is either not readable or does not exist2019-10-07 12:20:48,475 INFO [34] oracle.sysman.nextgen.utils.NextGenInventoryUtil - Setting default inventory location to: '/u01/app/oraInventory'2019-10-07 12:20:48,475 WARNING [34] oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader - The inventory pointer location /var/opt/oracle/oraInst.loc is either not readable or does not exist2019-10-07 12:20:48,475 WARNING [34] oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader - The inventory pointer location /var/opt/oracle/oraInst.loc is either not readable or does not exist2019-10-07 12:20:48,477 SEVERE [34] oracle.sysman.oii.oiii.OiiiInstallAreaControl - OUI-10039:Unable to access the inventory /u01/app/oraInventory on this system. Please ensure you have the proper permissions to read/write/search the inventory.2019-10-07 12:20:48,477 SEVERE [34] oracle.sysman.nextgen.impl.NextGenInstallerImpl - java.io.IOException: OUI-10039:Unable to access the inventory /u01/app/oraInventory on this system. Please ensure you have the proper permissions to read/write/search the inventory.
So basically, Oracle tries to check oraInst.loc under folder /var/opt/oracle & if it doesn’t find any, then it sets default inventory location to ‘/u01/app/oraInventory’.
I feel, This is somewhat agent software BUG, as loaction of oraInst.loc in AIX is ‘/etc’ not ‘/var/opt/oracle’.
Now there are two questions. First one, how to resolve this & second one, why my other installations went successful.
Reason behind other installation to be successful was Oracle indeed find oraInventory at its default location. So whenever oraInventory is located at ‘/u01/app/oraInventory’, you wont face this issue.
Now how to resolve this. You can always create softlink oraInst.loc to “/var/opt/oracle”.
Steps are given below:
Login using root:
1. mkdir -p /var/opt/oracle/
2. cd /var/opt/oracle/
3. ln -s /etc/oraInst.loc oraInst.loc
4. ls -lrt oraInst.loc
Once you perform above steps, you will be install OEM agent successfully.
Recently I was doing fresh Grid Infrastructure(GI) 12.2 install on one of our UAT boxes, wherein I was facing strange issue.
Both “root.sh” & “./roothas.sh -postpatch” exiting with below error/warning:
CLSRSC-400: A system reboot is required to continue installing.
test-server01:/u01/app/12.2.0.1/grid/bin # cd $ORACLE_HOME/crs/install
test-server01:/u01/app/12.2.0.1/grid/crs/install # ./roothas.sh -postpatch
Using configuration parameter file: /u01/app/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/crsdata/test-server01/crsconfig/hapatch_2019-05-27_02-22-25PM.log
2019/05/27 14:22:30 CLSRSC-329: Replacing Clusterware entries in file '/etc/inittab'
2019/05/27 14:23:18 CLSRSC-400: A system reboot is required to continue installing.
A simple instruction given by above warning was to reboot machine & retry. I did ask server admin to reboot machine but subsequent rerun of command failed with the same error.
test-server01:/u01/app/12.2.0.1/grid/crs/install # ./roothas.sh -postpatch
Using configuration parameter file: /u01/app/12.2.0.1/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/crsdata/test-server01/crsconfig/hapatch_2019-05-27_02-37-02PM.log
2019/05/27 14:37:07 CLSRSC-329: Replacing Clusterware entries in file '/etc/inittab'
2019/05/27 14:37:52 CLSRSC-400: A system reboot is required to continue installing.
I tried checking associated log files to get more details: /u01/app/crsdata/test-server01/crsconfig/hapatch_2019-05-27_02-37-02PM.log
> ACFS-9428: Failed to load ADVM/ACFS drivers. A system reboot is recommended.
> ACFS-9310: ADVM/ACFS installation failed.
> ACFS-9178: Return code = USM_REBOOT_RECOMMENDED
2019-05-27 14:37:41: ACFS drivers cannot be installed, and reboot may resolve this
2019-05-27 14:37:52: Command output:
> CLSRSC-400: A system reboot is required to continue installing.
>End Command output
2019-05-27 14:37:52: CLSRSC-400: A system reboot is required to continue installing.
So this was definitely due to issue with ACFS drivers.
I found below MOS documents related to my issues but nothing was exactly matching with my situation or operating system.
In our environment, we don’t use ACFS file system, so it was not the real problem in my case & we can always get ACFS drivers explicity if we need it in the future.
After reading all the logs I found /u01/app/12.2.0.1/grid/lib/acfstoolsdriver.sh is being called for ACFS driver installation.
I changed following code from
# Now run command with all arguments! exec ${RUNTHIS} $@
to
# Now run command with all arguments! #exec ${RUNTHIS} $@ exit 0
After changing above code, “./roothas.sh -postpatch” completed without errors/warning & I was able to complete GI installation successfully.
Note: This workaround is only applicable when ACFS is not being used in the environment so it can be implemented with the forewarning that there is implied risk which one must accept 😉
Yesterday, I was busy upgrading my UAT database from 12.1.0.2to 12.2.0.1. As a prerequisite when I ran preupgrade.jar into 12.1 RDBMS home it gave me below warning:
-- CHECK/FIXUP name: pending_dst_session
--
-- The call to run_fixup below will test whether
-- the following issue originally identified by
-- the preupgrade tool is still present
-- and if so, it will attempt to perform the action
-- necessary to resolve it.
--
-- ORIGINAL PREUPGRADE ISSUE:
-- + Complete any pending DST update operation before starting the database
-- upgrade.
--
-- There is an unfinished DST update operation in the database. It's
-- current state is: DATAPUMP(1)
--
-- There must not be any Daylight Savings Time (DST) update operations
-- pending in the database before starting the upgrade process.
-- Refer to My Oracle Support Note 1509653.1 for more information.
--
fixup_result := dbms_preup.run_fixup('pending_dst_session');
I tried querying DATABASE_PROPERTIES to get current DST_UPGRADE_STATE output, it was shown as below:
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE DATAPUMP(1)
I followed below MOS notes to resolve the issues, but all ended without any luck.
Then I thought of ignoring this error & proceeded with DB upgrade. DB was successfully upgraded. But I once again stuck during step of DST upgrade from 18 to 26.
It was not allowing me to upgrade DST version from 18 to 26 as DST_UPGRADE_STATE was not NONE.
Then I googled it & found below steps to resolve it:
1. ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’; 2. exec dbms_dst.unload_secondary; 3. ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, OFF’;
I did check DST_UPGRADE_STATE post implementing it.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
-------------------------------------------------------------------------------------------------------------------------------- ---------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 14
DST_UPGRADE_STATE NONE
Now my database was ready for DST upgrade as DST_UPGRADE_STATE is NONE. 🙂
As a prerequisite of upgrade, I had to rectify this before attempting upgrade.
As a first step, I tried running UTLRP.sql, but still component was in INVALID state.
I even checked, status of all objects in the database with object_type like JAVA%.
SYS@TESTDB:TESTDB> select owner, status, count(*) from all_objects where object_type like '%JAVA%' group by owner, status;
OWNER STATUS COUNT(*)
-------------------------------------------------------------------------------------------------------------------------------- ------- ----------
SYS VALID 29238
MDSYS VALID 650
ORDSYS VALID 2589
So now only option, I had left is to reinstall JAVA package inside database.
PFB steps for the same:
alter system set java_jit_enabled = FALSE;
alter system set “_system_trig_enabled”=FALSE;
alter system set job_queue_processes=0;
create or replace java system;
alter system set java_jit_enabled = true;
alter system set “_system_trig_enabled”=TRUE;
alter system set JOB_QUEUE_PROCESSES=1000;
@?/rdbms/admin/utlrp.sql
After applying above steps, JServer JAVA Virtual Machine became VALID. 🙂
