Oracle Flashback technology offers significant benefits for database management and recovery.
Monday, August 14, 2023
Article: Mastering Data History using Oracle's Flashback Data Archive Feature
Oracle Flashback technology offers significant benefits for database management and recovery.
Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature
Thursday, August 10, 2023
SELECT without FROM Clause in Oracle 23c
You will no longer receive error “ORA-00923: FROM keyword not found where expected” when running expressions to get results in Oracle 23c.
Here are few examples
Example 1: Run mathematical operations with or without using FROM clause and you will get the result
SQL> select 2+3 from dual;
SQL> select 2+3 ;
Example 2: Select current date with and without using FROM clause and you will get the result.
SQL> Select current_date from dual;
SQL> Select current_date;
Example 3: Select NEXTVAL with and without using FROM clause and you will get the result
SQL> Create sequence empno_seq;
SQL> select empno_seq.nextval from dual;
SQL> select empno_seq.nextval ;
Example 4: Pl/SQL block with and without using FROM clause and you will get the result
delcare
v1 number;
begin
select empno_seq.nextval into v1 from dual;
dbms_output.put_line ('v1= '||v1);
end;
/
delcare
v1 number;
begin
select empno_seq.nextval into v1;
dbms_output.put_line ('v1= '||v1);
end;
/
Friday, July 21, 2023
Flashback Data Archive enhancements in Oracle 12c
Oracle 12c (12.1.0.1) has below enhancements in FDA
- Data Hardening
- User context tracking
You can create new application using REGISTER_APPLICATION
SQL> Begin
DBMS_FLASHBACK_ARCHIVE.register_application(
application_name => 'ORACLERACEXPERT',
flashback_archive_name => 'FDA1');
end;
/
You can add tables to the application using ADD_TABLE_TO_APPLICATION procedure
SQL> Begin
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (
end;
/
SQL> Begin
DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (
end;
/
You can remove the tables using REMOVE_TABLE_FROM_APPLICATION procedure from
SQL> Begin
DBMS_FLASHBACK_ARCHIVE.enable_application(
end;
/
SQL> Begin
DBMS_FLASHBACK_ARCHIVE.disable_application(
application_name => 'ORACLERACEXPERT');
end;
/
User context tracking - By enabling this feature it is easy to track which user made what changes to the table.
Use DMBS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure to Set the user content level and procedure DMBS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXT procedure To Access the context
There are 3 options depending upon how much user context needs to save
ALL – The entire SYS_CONTEXT is stored
TYPICAL – The user context is stored
NONE- Nothing
For ex: - To set context level to ALL
SQL> DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL ( level=>‘ALL’);
You can get the XID from the archive table
XID
----------------
05000A0B7040000
Now with XID you can get the context information using dbms_flashback_archive.get_sys_context procedure
SQL> begin
dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'SESSION_USER'));
dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'HOST'));
dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'MODULE'));
end;
/
USER1
SRVHOST
SQL*Plus
You can get all Transactions ID using below query
SQL> select empno, empname, VERSIONS_XID
from EMP order by empno;
EMPNO EMPNAME VERSIONS_XID
----------- --------------- ---------------------
1 ORARAC 05000A0B7040000
You can use SYS_FBA_CONTEXT_AUD to get context information for each transaction.
There are many Flashback data archive view available and to get the list of all views run below query
SQL> SET LINESIZE 600
SQL> COLUMN owner FORMAT A10
SQL> COLUMN table_name FORMAT A25
SQL> SELECT owner, table_name FROM dba_tables WHERE table_name LIKE '%FBA%';
OWNER TABLE_NAME
-------------- -------------------------
SYS SYS_FBA_FA
SYS SYS_FBA_TSFA
SYS SYS_FBA_TRACKEDTABLES
SYS SYS_FBA_PARTITIONS
SYS SYS_FBA_USERS
SYS SYS_FBA_BARRIERSCN
SYS SYS_FBA_DL
SYS SYS_FBA_CONTEXT
SYS SYS_FBA_CONTEXT_AUD
SYS SYS_FBA_CONTEXT_LIST
SYS SYS_FBA_APP
SYS SYS_FBA_APP_TABLES
SYS SYS_FBA_COLS
SYS SYS_FBA_PERIOD
SYS SYS_MFBA_STAGE_RID
SYS SYS_MFBA_TRACKED_TXN
SYS SYS_MFBA_NROW
SYS SYS_MFBA_NCHANGE
SYS SYS_MFBA_NTCRV
You can refer below Oracle Doc for best practices
FDA - Flashback Data Archive Usage and Best Practices (Doc ID 2370465.1)
Flashback Data Archive provides many benefits for maintaining historic data against tracked tables. The FDA helps to perform undo-based flashback operations for an extended period and take advantage of this feature.
Monday, June 19, 2023
Webinar: Oracle Database 23c Security new features
Date and time: July 3rd 2023, 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)
This Webinar covers following Topics.
- SQL Firewall
- Audit
- Authentication
- Authorization
- Encryption
- Autonomous Database
- Other
You will receive an email confirmation with meeting link or Webinar link will be posted here.
Note that registrations are limited and first come and first serve basis.
For presentation link Click here
Thanks & Regards,
http://www.oracleracexpert.com
Thursday, June 8, 2023
Oracle 19c import issues ORA-31693, ORA-02354, ORA-39002, ORA-39405
ORA-31693: Table data object "ORCL"."ITEMCG" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39840: A data load operation has detected data stream format error .
ORA-39844: Bad stream format detected: [klaprs_62] [139751105749101] [139751105749012] [4] [2] [2065583] [] []
User may encounter this issue when new column added to a table with cokumn optimization enabled and the same column was modified.
Below are the workarounds
- Use access_method=EXTERNAL_TABLE during export
- Prior export add and drop a dummy column to the problematic table using
SQL> ALTER TABLE <table_name> DROP dummy number;
- Take export of failed table and import using CONTENT=DATA_ONLY as tableau structure already imported.
The fix for this bug was initially available on 19.13 and above see if it helps.
Also, I come across below issues when importing data
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 33 into a target database with TSTZ version 32.
User may encounter this issue when export from higher time zone version and importing into lower time zone version.
- Patch the target database to higher or equal to source time zone patch or DST TZ version
- Create a database with same time zone and perform export/import
- Oracle RDBMS and OJVM DST-related notes
- Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)
Active Sessions, Proxy Sessions and Locked objects in Oracle
--------------------------------------------------------
--Script : ACTIVE SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600
COLUMN username FORMAT A10
COLUMN osuser FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A40
COLUMN module FORMAT A35
COLUMN action FORMAT A15
COLUMN logon_time FORMAT A20
SELECT s.username,
s.osuser,s.sid,s.serial#,p.spid,s.lockwait,s.status,s.machine,s.program,s.module,s.action,
TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,s.blocking_session_status AS BlockStatus
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;
--Script : PROXY SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600
COLUMN username FORMAT A10
COLUMN osuser FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A40
COLUMN module FORMAT A35
COLUMN action FORMAT A15
COLUMN logon_time FORMAT A20
SELECT s.username,
s.osuser,s.sid,s.serial#,p.spid,s.lockwait,s.status,s.machine,s.program,s.module,s.action,TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,s.blocking_session_status AS BlockStatus
WHERE s.paddr = p.addr
AND s.sid = sci.sid
AND s.serial# = sci.serial#
AND sci.authentication_type = 'PROXY'
ORDER BY s.username, s.osuser;
--Script : LOCKED OBJECT SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600
COLUMN owner FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN oracle_username FORMAT A15
COLUMN locked_mode FORMAT A15
COLUMN os_user_name FORMAT A15
SELECT s.sid,
s.serial#,s.status,do.owner,do.object_name,do.object_type,lo.oracle_username,Decode(lo.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',lo.locked_mode) locked_mode,lo.os_user_name
WHERE lo.session_id = s.sid
AND do.object_id = lo.object_id
ORDER BY 1, 2, 3, 4;
Tuesday, May 30, 2023
Errors ORA-12154, ORA-29003 when connecting to Autonomous Data Warehouse using Django
File "C:\django\db\backends\oracle\base.py", line 254, in get_new_connection return Database.connect( cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified
First copy the tnsnames.ora into local system and test tnsping.
C:\> tnsping <service Name>
If the tnsping is working fine, then below are the passible reason for ORA-12154 error
- TNS_ADMIN might not be configured or set the right path
- There might be a typo in service name
- Connection details in settings.py might not be correct
ORA-29003: SSL transport detected mismatched server certificate
Django supports Oracle Database 19c or higher versions but cx_oracle python driver required. We verified that required driver is installed. We try to connect using SQL*PLUS but still receiving same error. That means there is no issue with the settings.
After research we found that 12c client is not supported for new mTLS authentication as per document “ALERT: Action Required for Autonomous Databases (Doc ID 2911553.1)”
We have installed Oracle 19c client and were able to connect without any issues.
Below are the options to resolve the issue
1. Install certified oracle client versions 11.2.0.4.220719 (or later), 18.19 (or later), 19.2 (or later), 21 (base release or later)
2. Update Autonomous database instance to allow both TLS and mTLS authentication.
- Goto Autonomous Database Details page --> Network --> click Edit in the Mutual TLS (mTLS) Authentication field.
- Change the value to allow TLS authentication, deselect Require mutual TLS (mTLS) authentication
- Click Update
I hope this helps.
Satishbabu G, Oracle ACE Pro
Oracle "Hyperion EPMA Server" service is not coming online
Here are the Errors from event.log
EPMA_Server
[EPMA Server Startup] ERROR SVR_ERR_PROCESSMGR_CANT_INIT_SESSIONMGR:Cannot initialize the Session Manager.Hyperion.DimensionServer.Interface.Exceptions.EPMAServiceException: Cannot initialize the Session Manager. ---> Hyperion.CommonServices.Exceptions.SessionManagerException: An error was encountered by the CAS Security Provider: Class: java.lang.NullPointerException
at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl)
at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString()
at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching)
--- End of inner exception stack trace ---
Hyperion EPMA DimmensionServer
Service cannot be started. Hyperion.DimensionServer.Interface.Exceptions.EPMAServiceException: Cannot initialize the Session Manager. ---> Hyperion.CommonServices.Exceptions.SessionManagerException: An error was encountered by the CAS Security Provider: Class: java.lang.NullPointerException
StackTrace:
at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl)
at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString()
at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching)
--- End of inner exception stack trace ---
The event log is no much helpful. But note that the Hyperion EPMA Service will not come up if you have any issues with database. Check the logs to see any “ORA- “errors .
I found below error in EssbaseAdminServices0.log
#### <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1329242417925> ORA-28001: the password has expired
Now it’s clear that Database user password expired.
SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘SHARED_SERVICES’;
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SHARED_SERVICES EXPIRED(GRACE)
Do not change the password. If you change the password then you need to re-configure the Hyperion service in order to use the new password.
If you know the password then set the using below command.
SQL> Alter user SHARED_SERVICES identified by ‘xxxxxx’;
If you don’t know the password then reset the password following below steps
SQL> select password from sys.user$ where name='SHARED_SERVICES';
PASSWORD
------------------------------
61266722B44D5BG418
SQL> alter user QWERTY identified by values '61266722B44D5BG418’;
User altered.
Also make sure to modify PASSWORD_LIFE_TIME to “UNLIMITED” for DEFAULT or User profile that you are using
SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
Profile altered.
Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com
Sunday, May 14, 2023
UN World Innovation Day: Igniting Change through Creativity and Collaboration
The United Nations (UN) has set forth 17 Sustainable Development Goals (SDGs) to tackle wide range of global challenges by 2030 which includes climate change, poverty, eradication and beyond. We will explore how Generative Artificial Intelligence (AI) can be used to achieve of these goals for better future.

The UN World Innovation Day Hack 2023 is not just another hackathon; it's a unique opportunity to collaborate with encouraging individuals to creatively utilize their skills to generate new ideas and solutions for various critical areas. These include health and wellbeing, economic growth, decent work, and quality education. Hackathon brings together creative minds from around the world to generate innovative solutions for pressing issues. By bringing together a diverse group of talented individuals, the hackathon helps to provide meaningful solutions to pressing challenges.
Hackathon gives an opportunity to connect with new people from various backgrounds, experiences and expertise come together to collaborate, share ideas, and push the boundaries of innovation. You will have the opportunity to learn from each other, explore, generate innovative solutions, and network with inspiring individual beyond the event.
This year's hackathon introduces two brand new challenges:
- GPT4Good
- Data4Good
In the "GPT4Good" challenge, participants will explore the potential of GPT (Generative Pre-trained Transformer) models to address social, environmental, and humanitarian issues. The GPT is a cutting-edge artificial intelligence technology, has the power to generate human-like text, making it a valuable tool for various applications. From enhancing accessibility to fostering sustainable practices, participants will harness the power of GPT to create innovative solutions that contribute to the greater good.
The UN World Innovation Day Hack 2023 event schedule, spanning from May12th to May 15th, promises an exhilarating journey filled with creativity, collaboration, and problem-solving.
The Hackathons bring over 1000+ participants and mentors around the world. In this blog post, I express my deep gratitude to all the mentors who generously contribute their time, expertise, and support to guide teams and projects during these hackathons as they play essential role in making these events successful.
To find answers to common queries and gain a better understanding of the hackathon logistics visit the official hackathon FAQ page at https://www.worldinnovationday.com/faq .
To access valuable resources and guides visit resources page at https://www.worldinnovationday.com/resources. It will provide necessary tools and knowledge to overcome challenges and push the boundaries of innovation.
To access Challenge and Sponsored resources details, visit the challenge resource page
https://www.worldinnovationday.com/challenge-resources.
Join on this transformative journey, meet incredible individuals, test your skills, and deliver meaningful impacts. Together, let's unleash the potential of innovation and create a world that thrives on collaboration, inclusivity, and positive change.
Conclusion: The UN World Innovation Day Hack 2023 is a global celebration of creativity, collaboration, and innovation. As an official Mentor for this event, I am honored to be part of a community to address pressing global challenges through technology and creative thinking. As we work together, let us continue to inspire, guide, and empower the next generation of innovators for better future.
#WorldInnovationDay #Hackathon #GptForGood #DataForGood #Innovation #Collaboration
Thursday, March 2, 2023
Oracle AutoUpgrade with source and Target Database on different servers
- create config file
- Source Database should be running in original oracle home
- The Database server should be registered in DNS
- JAVA8 required for Autoupgrade to run. Oracle 12c R2 (12.2.0.1) or newer oracle homes have a java version by default.
- If you run AutoUpgrade in batch or script mode use “noconsole” parameter
There are four AutoUpgrade modes and each mode performs different steps
- Analyze Mode: Setup, Pre-checks.
- Fixups Mode: Setup, Pre-checks, and Pre-fixups.
- Deploy Mode: Setup, Guaranteed Restore Point, Pre-upgrade, Pre-checks, Pre-fixups, Drain, Database Upgrade, Post-checks, Post-fixups, and Post-upgrade.
- Upgrade Mode: Setup, Database Upgrade, Post-checks, and Post-fixups.
You can run this step on source database before you setup target.
Run below command to start AutoUpgrade Analyze
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze
Make sure you analyze output files for errors and run fixup on Source database server. You should see SID.html and SID_preupgrade.log files
Note that this mode also generates status files such as status.json, progress.json which can be located under cfgtoollogs/upgrade/auto/status.
Status.json – This file contains High level status of upgrade.
Progress.json – This file has contains status of progress of all upgrades
In case if target database is not on the same server then you must set the source home path in the configuration file, so that Autoupgrade analyze can run on source oracle home.
For example,
upgrade1.source_home=/home/oracle/product/12.2.0.1/dbhome_1 # Source ORACLE_HOME Path
upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1 # Target ORACLE_HOME Path
Fixups Mode : This mode performs all required automated fix up that are required before you start an upgrade. In this mode it creates guaranteed restore point.
Run below command on source Database server to run fixups.
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode fixups
Deploy Mode: This mode performs all required actions for an upgrade, which include analyze, fixups, upgrade and post upgrade steps. You will use this mode when source and target Oracle Home’s are on the same server. This mode also creates guaranteed restore point.
Run below command on source Database server to run fixups.
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode Deploy
Move the database from source to Target Database server and perform Upgrade mode.
Upgrade Mode: You will run this Autoupgrade mode when you are moving database to Target server or don’t have source Oracle home access. This mode is used only when you are moving to new server. But note that this mode doesn’t create guaranteed restore point and it doesn’t perform the post upgrade steps.
Before you perform upgrade make sure you copy the during_upgrade_pfile_dbname.ora from source to target Oracle Home with default name init<SID>.ora. You can also create the spfile using during_upgrade_pfile_dbname.ora which can be found under temp directory.
SQL> create spfile from pfile=' /home/oracle/autoupgrade/au21/TESTDB/temp/during_upgrade_pfile_testdb.ora';
On Target database server start the database in upgrade mode and run below command to upgrade
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode upgrade
After upgrade make sure you perform post upgrade steps manually
· Copy network files sql.net ora, listener,ora, tnsnames.ora files..etc
· Restart of database in case of RAC
· Remove guaranteed restore point
Thanks & Regards,
https://oracleracexpert.com, Oracle ACE
Wednesday, February 22, 2023
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class
No protocol specified
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:264)
at java.awt.Toolkit$2.run(Toolkit.java:860)
at java.awt.Toolkit$2.run(Toolkit.java:855)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)
at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)
at com.jgoodies.looks.LookUtils.<clinit>(LookUtils.java:249)
at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAndFeel.java:135)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:348)
at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1879)
at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)
at oracle.install.commons.util.Application.startup(Application.java:976)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)
at oracle.assistants.common.base.driver.AssistantApplication.startup(AssistantApplication.java:336)
at oracle.assistants.dbca.driver.DBConfigurator.startup(DBConfigurator.java:378)
at oracle.assistants.dbca.driver.DBConfigurator.main(DBConfigurator.java:513)
The main causes of the issue is
1. The DISPLAY variable is NOT set or value is not correct.
2. X Windows is not installed
3. Oracle user not added to ACL (Access Control List)
If launching from server make sure you have any X Windows software installed and install org-x11-apps.x86_64 package
If you are launching from a local workstation, make sure you have XGraphics software such as Exceed, VLC…etc tools installed. Then You can set DISPLAY by using below command
CSH
$ setenv DISPLAY <IP Address of workstation or server>:0
SSH or KSH
$ export DISPLAY=<IP Address of workstation or server>:0
In Some cases, users may face issue when installing Oracle software on Ubuntu or Linux environment.
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation,
Continue? (y/n) [n] Y
>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-01_12-17-01PM. Please wait ....oracle$:~$ No protocol specified
Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit
In this scenario, verify weather JDK installed on the system or not using below command
$ java -version
If not installed then installing required JDK will resolve the issue. If the problem persists, then run below command by logging as root
$ xhost +
Switch back to Oracle user and run the installation.
Thanks,
Thursday, February 16, 2023
Upgrade Oracle Database using AutoUpgrade Utility
It is always recommending that you run AutoUpgrade in Analyze mode before running in Fixup mode. Note that fixup mode can make changes to the source database. The Autoupgrade includes automatic retry and fallback, schedule upgrades and modify or remove the initialization parameters which are deprecated.
AutoUpgrade utility can be used on upgrading databases from 12c R2 release (12.2 + DBJAN2019RU and newer)
You need to download AutoUpgrade utility for Databases 12c R2 and 18c. From 19.3 (19c) and later the autoupgrade.jar file exists by default.
Oracle home must contain JAVA to use AutoUpgrade Tool. Always download latest AutoUpgrade.jar file
Create a sample config.txt file for database ugrade
upgrade1.run_utlrp=no # Optional. To run utlrp after upgrade select YES
upgrade1.timezone_upg=no # Optional. To run the timezone upgrade select YES
upgrade1.start_time=now # Optional Use “NOW” or specify future upgrade time and date [+XhYm (X hours, Y minutes) | dd/mm/yyyy hh:mm:ss]
upgrade1.upgrade_node=localhost # Optional. Default is 'localhost'
upgrade1.target_version=19 # Only required if target database version is 12.2
upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1 # Target ORACLE_HOME Path
AutoUpgrade with source and Target Database on same server
- Run below command to start AutoUpgrade Analyze
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze
AutoUpgrade utility launched with default options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 databases will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases [ 1 ]
Jobs finished [1]
Jobs failed [0]
Jobs pending [0]
------------------- JOBS FINISHED SUCCESSFULLY --------------------
Job 100 for TESTDB
All the logs are under the job id . Pls review all the logs for any errors and information.
You can see the warnings and errors in html file.
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode deploy
AutoUpgrade utility launched with default optionsProcessing config file ...+--------------------------------+| Starting AutoUpgrade execution |+--------------------------------+1 databases will be processedType 'help' to list console commandsupg>
upg> lsj+----+-------+---------+---------+-------+--------------+--------+---------------+|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|+----+-------+---------+---------+-------+--------------+--------+---------------+| 102| TESTDB|DBUPGRADE|EXECUTING|RUNNING|21/05/12 15:03|15:35:22|95% Upgraded |+----+-------+---------+---------+-------+--------------+--------+---------------+Total jobs 1
Once upgrade is completed verify upg_summary.log for any errors
/u01/home/oracle:DBA>cat upg_summary.logOracle Database Release 19 Post-Upgrade Status Utility 04-30-2021 11:07:0Database Name: TESTDBComponent Current Full Elapsed TimeName Status Version HH:MM:SSOracle Server UPGRADED 19.10.0.0.0 00:09:54JServer JAVA Virtual Machine VALID 19.10.0.0.0 00:01:12Oracle XDK UPGRADED 19.10.0.0.0 00:00:38Oracle Database Java Packages UPGRADED 19.10.0.0.0 00:00:05Oracle Text UPGRADED 19.10.0.0.0 00:00:23Oracle Workspace Manager UPGRADED 19.10.0.0.0 00:00:22Oracle Real Application Clusters OPTION OFF 19.10.0.0.0 00:00:00Oracle XML Database UPGRADED 19.10.0.0.0 00:00:51Oracle Multimedia UPGRADED 19.10.0.0.0 00:01:47Datapatch 00:01:41Final Actions 00:01:44Post Upgrade 00:00:16
Total Upgrade Time: 00:17:31
Database time zone version is 26. It is older than current release time
Grand Total Upgrade Time: [0d:0h:20m:20s]
If you select upgrade1.timezone_upg=YES then Time zone will be upgraded as part of the Database upgrade.
AutoUpgrade Stages
AutoUpgrade utility go through series of steps called stages but the actions performed during every stage defined by the “processing mode”. For AutoUpgrade we have Analyze, Fixups, Deploy, and Upgrade processing modes.
AutoUpgrade has the following stages:
- SETUP: The initial stage in which it starts the job.
- PREUPGRADE: The stage in which it performs readiness for upgrade such as sufficient space
- PRECHECKS: The stage in which it performs pre-checks on source Oracle home to meet requirements for upgrade.
- GRP: Backup database using guaranteed restore point (GRP) before upgrade, this option only available in Enterprise Edition only
- PREFIXUPS: The stage in which it performs preupgrade fixups before upgrade
- DRAIN: The stage during which it shuts down the database to release resources.
- DBUPGRADE: The stage in which it performs the actual upgrade.
- POSTCHECKS: The stage in which it performs post upgrade checks on the target Oracle home before executing any postupgrade fixups.
- POSTFIXUPS: The stage in which it performs processing of postupgrade fixups, timezone upgrade is part of this step.
- POSTUPGRADE: The stage in which in copies or merges the required files from to the target Oracle home for ex:- listener, tns files
There are 4 state messages
- RUNNING – AutoUpgrade is still running
- FINISHED - AutoUpgrade is successfully completed
- ERROR – AutoUpgrade has some Errors
- ABORTED - AutoUpgrade aborted response to user request.
- Database Server Upgrade/Downgrade Compatibility Matrix (Doc ID 551141.1)
- Using AutoUpgrade for Oracle Database Upgrades
https://oracleracexpert.com, Oracle ACE
Create, Drop, Alter Blockchain tables in Oracle 21c
Create and Drop Blockchain table
When creating block chain you can specify retention period for Blockchain table using “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period and to specify retention period for rows use “NO DELETE” Clause .
Ex:- In below example creates Blockchain_T1 table the table can be dropped until 10 days and rows can be deleted until 15 days that they were inserted.
SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1";
Table level clauses
- NO DROP – The table cannot be dropped.
- NO DROP UNTIL x DAYS IDLE – Table cannot be dropped when the table is IDLE and no new rows created for specified X number of days or retention period
Row level clauses
- NO DELETE or NO DELETE LOCKED – The Rows cannot be deleted.
- NO DELETE UNTIL x DAYS AFTER INSERT – the rows cannot be deleted until x number of days they were inserted, the retention setting can be changed using ALTER TABLE command.
- NO DELETE UNTIL x DAYS AFTER INSERT LOCKED – the rows cannot be deleted until x number of days they were inserted , also retention setting cannot be changed until x number of days
In below example creates Blockchain_T1 table with partitions. The table cannot be dropped until 10 days and rows cannot be deleted until 15 days that they were inserted.
SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE)
NO DROP UNTIL 10 DAYS IDLE
NO DELETE UNTIL 15 DAYS AFTER INSERT
HASHING USING "SHA2_512" VERSION "v1"
PARTITION BY RANGE(Col3)
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-31-2022','mm-dd-yyyy')),
PARTITION p2 VALUES LESS THAN (TO_DATE('02-28-2022','mm-dd-yyyy')),
PARTITION p3 VALUES LESS THAN (TO_DATE('03-31-2022','mm-dd-yyyy'))
);
You can query USER_TAB_COLS for Blockchain Table details
SQL> SELECT internal_column_id as colid
column_name
data_type,
data_length,
FROM user_tab_cols
WHERE table_name = 'BLOCKCHAIN_T1'
ORDER BY colid;
COLID COLUMN_NAME DATA_TYPE DATA_LENGTH
---------- ------------------------ ------------------------------ -----------
1 Col1 NUMBER 22
2 Col2_ VARCHAR2(48) 48
3 Col3T DATE 7
4 ORABCTAB_INST_ID$ NUMBER 22
5 ORABCTAB_CHAIN_ID$ NUMBER 22
6 ORABCTAB_SEQ_NUM$ NUMBER 22
7 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13
8 ORABCTAB_USER_NUMBER$ NUMBER 22
9 ORABCTAB_HASH$ RAW 2000
10 ORABCTAB_SIGNATURE$ RAW 2000
11 ORABCTAB_SIGNATURE_ALG$ NUMBER 22
12 ORABCTAB_SIGNATURE_CERT$ RAW 16
13 ORABCTAB_SPARE$ RAW 2000
You can query {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views to get information about Blockchain Tables
DROP - The below example drops the table if the table has not modified for retention period defined in the Blockchain creation.
It is recommended to use PURGE option when dropping a Blockchain table.
Note that Blockchain tables cannot be create the root container and application root container.
NO DROP UNTIL 10 DAYS IDLE
NO DELETE LOCKED
HASHING USING "SHA2_512" VERSION "v1";
ORA-05729: blockchain table cannot be created in root container
ALTER Blockchain Tables : The Blockchain table retention can be modified using ALTER TABLE command
In below example we increased retention for Blockchain_T1 table that it cannot be dropped until table IDLE and no new rows created for 21 days.
SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 21 DAYS IDLE;
In below example trying to lower retention for Blockchain_T1 table to 16 and the operation failed as retention value cannot be lowered.
SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 16 DAYS IDLE;
ORA-05732: retention value cannot be lowered
You can also increase column length but cannot add or drop column in Blockchain tables.
SQL> ALTER TABLE Blockchain_T1 MODIFY (COL2 VARCHAR2(58));
Table BLOCKCHAIN_T1 altered.
ADD column
Error report -
ORA-05715: operation not allowed on the blockchain table
DROP column
SQL> ALTER TABLE Blockchain_T1 DROP column Col2;
Error report -
ORA-05715: operation not allowed on the blockchain table
DDL and DML on Block chain
In below example we are trying to DELETE, TRUNCATE, UPDATE and MOVE the operation not allowed
DELETE Table
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table
Error report -
ORA-05715: operation not allowed on the blockchain table
Error report -
SQL Error: ORA-05715: operation not allowed on the blockchain table
DROP TABLE
ORA-05723: drop blockchain table BLOCKCHAIN_T1 not allowed
MOVE Table
SQL> ALTER TABLE Blockchain_T1 move tablespace Blockchain_TBS2 ;
Error report -
ORA-05715: operation not allowed on the blockchain table
Pls refer Restrictions for Blockchain tables for more details.
https://oracleracexpert.com, Oracle ACE
Wednesday, February 15, 2023
BlockChain Tables in Oracle 21c
Oracle 19c introduced Immutable tables concept, that provides protection against unauthorized data modifications. The blockchain tables concept introduced in Oracle 21c and can be backported to 19c using a patch 32431413, but COMPATIBLE parameter must be set to 19.10.0 or late.
These tables are useful to implement Blockchain applications to handle tamper-resistant blockchain transactions with verifiable crypto-secure data management practices. The blockchain tables prevent unauthorized changes or deletion by criminals, hackers and fraud and protect critical company data. Blockchain tables has hidden columns as well and these values are managed by the database.
Blockchain tables and regular tables can be used in queries and transactions, also you can create indexes and partitions.
Column Name |
Data Type |
Description |
ORABCTAB_INST_ID$ |
NUMBER (22) |
Instance ID of the
database instance into which the row is inserted. |
ORABCTAB_CHAIN_ID$ |
NUMBER (22) |
Chain ID of the
chain, in the database instance, into which the row is inserted. 0 through 31 are valid values. |
ORABCTAB_SEQ_NUM$ |
NUMBER(22) |
Sequence number of
the row on the chain |
ORABCTAB_CREATION_TIME$ |
TIMESTAMP WITH TIME
ZONE |
Row created time in
UTC format |
ORABCTAB_USER_NUMBER$ |
NUMBER (22) |
Database User ID who
inserted the row. |
ORABCTAB_HASH$ |
RAW(2000) |
Hash value of the
row |
ORABCTAB_SIGNATURE$ |
RAW(2000) |
User signature of
the row |
ORABCTAB_SIGNATURE_ALG$ |
NUMBER(22) |
Signature algorithm
used to produce the user signature of a signed row. |
ORABCTAB_SIGNATURE_CERT$ |
RAW(16) |
GUID of the
certificate associated with the signature on a signed row. |
ORABCTAB_SPARE$ |
RAW(2000) |
Reserved for future
use. |
- In case of Oracle RAC instance, a block chain table contains 32 chains and chain will have unique combination of instance ID and chain ID. It is recommended to create index on the combination of Instance ID, chain ID and sequence number.
- The SHA2-512 hashing algorithm used to handle hash value
- In case Oracle Data Guard, to avoid data loss consider using Maximum availability or Maximum protection mode
- To specify retention period for Blockchain table use “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period.
- To specify retention period for rows in Blockchain table use “NO DELETE” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period for rows
Restrictions
for Blockchain tables
- There are many restrictions when using blockchain tables
- Many datatypes are not supported such as nested table , varray, REF , ROWID, UROWID, LONG, object type, BFILE, XMLType , , , TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
- Point-in-time recovery or flashback database will undo changes on all database objects including blockchain tables.
- Blockchain Retention policies depends on system time and any changes to systems time must be audited.
- Max number of user column allowed are 980
- Blockchain tables doesn’t allow or support below operations
Column level restrictionso Adding, dropping, and renaming columnso Dropping partitions
Row level restrictionso Update or merge rowso Defining BEFORE ROW triggers that fire for update operations are not allowed.
Table level restrictionso Truncate tableo Inserting data using parallel DMLo Sharded tableso During distributed transactions, Inserting data into a blockchain table using Active Data Guard DML redirection is not supportedo Direct-path loadingo Flashback tableo Cannot convert a regular table to a blockchain table or vice versa.o XA transactions
Database level restrictionso Export and Import can be done as regular tables, without the system-generated hidden columns.o Creating blockchain tables in CDB or application rooto Creating Oracle Label Security (OLS) policieso Using the DBMS_REDEFINITION package for Online redefinitiono Creating Oracle Virtual Private Database (VPD) policieso When using Transient Logical Standby and rolling upgrades, the DDL and DML on blockchain tables are not replicated and supportedo when using Logical Standby and Oracle GoldenGate, the DDL and DML on blockchain tables succeed on the primary database but are not replicated to standby databaseso Creating Automatic Data Optimization (ADO) policies
Monday, January 30, 2023
DBMS_BLOCKCHAIN_TABLE package in Oracle 21c
- Sing a row that is added into the table
- Verify the integrity of rows and hash column.
- Generate signature and signed digest
- Delete rows in a blockchain table that are beyond retention defined.
- DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS By using this procedure you can verify the integrity of rows and hash column. You can also verify the Signatures as well
DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
low_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
high_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
instance_id IN NUMBER DEFAULT NULL,
chain_id IN NUMBER DEFAULT NULL,
number_of_rows_verified OUT NUMBER,
verify_signature IN BOOLEAN DEFAULT TRUE);
For ex:-
DECLARE
Rows_Verify NUMBER;
DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(
schema_name => 'BCUSER',
table_name => 'BLOCKCHAIN_T1',
number_of_rows_verified => Rows_Verify);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(Rows_Verify) || ' rows deleted');
END;
/
- DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST – By using this procedure you can generate a signature and signed digest.
DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
signed_bytes IN OUT BLOB,
signed_rows_indexes OUT ORABCTAB_ROW_ARRAY_T,
schema_certificate_guid OUT RAW,
signature_algo IN NUMBER default SIGN_ALGO_DEFAULT)
RETURN RAW;
- DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN - By using this procedure you can verify the integrity of rows created between specified time period X1 and X2
DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN(signed_bytes_latest IN BLOB,signed_bytes_previous IN BLOB,number_of_rows_verified OUT NUMBER);
- DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS - By using this procedure you can delete rows that are beyond retention period
For ex:-DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(schema_name IN VARCHAR2,table_name IN VARCHAR2,before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,number_of_rows_deleted OUT NUMBER);
Ex:- The below example deletes the rows beyond the retention period, you can limit the number of rows deletion by using before_timestamp
DECLAREDel_Rows NUMBER;DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS(schema_name => 'BCUSER',table_name => 'BLOCKCHAIN_T1',before_timestamp => NULL,number_of_rows_deleted => Del_Rows);DBMS_OUTPUT.PUT_LINE(TO_CHAR(Del_Rows) || ' rows deleted');END;/
- DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE - By using this procedure you can determine the data format for row content to compute the signature.
DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE(schema_name IN VARCHAR2,table_name IN VARCHAR2,instance_id IN NUMBER,chain_id IN NUMBER,sequence_id IN NUMBER,data_format IN NUMBER,row_data IN OUT BLOB);
- DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH - By using this procedure you can determine the data format for row content to compute hash value
DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH(schema_name IN VARCHAR2,table_name IN VARCHAR2,instance_id IN NUMBER,chain_id IN NUMBER,sequence_id IN NUMBER,data_format IN NUMBER,row_data IN OUT BLOB);
- DBMS_BLOCKCHAIN_TABLE.SIGN_ROW – By using this procedure you can add a signature to an existing row.
DBMS_BLOCKCHAIN_TABLE.SIGN_ROW(schema_name IN VARCHAR2,table_name IN VARCHAR2,instance_id IN NUMBER,chain_id IN NUMBER,sequence_id IN NUMBER,hash IN RAW DEFAULT NULL,signature IN RAW,certificate_guid IN RAW,signature_algo IN NUMBER);
- DBMS_BLOCKCHAIN_TABLE.SIGN_ALGO_RSA_SHA2_512 - – By using this procedure you can validate a signature using specific digital certificate and signature algorithm. Note that Blockchain tables support only DER encoding or X.509 certificates
- Note that DBMS_CRYPTO.HASH function can be used to compute hash value
By using DBMS_TABLE_DATEA package you can get the byte values of row, column
- DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMN - By using this procedure column data in bytes
- DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMNS By using this procedure column data in bytes for set of columns
- DBMS_TABLE_DATA.GET_BYTES_FOR_ROW By using this procedure row data in bytes.
https://oracleracexpert.com, Oracle ACE