tag:blogger.com,1999:blog-68508172719411977982024-03-19T04:20:24.049-07:00Oracle DBA and RAC DBA ExpertA Knowledge Sharing SiteSatishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.comBlogger336125tag:blogger.com,1999:blog-6850817271941197798.post-89416648867658596942024-03-05T16:25:00.000-08:002024-03-05T16:25:00.185-08:00Bugs in Oracle Apex 23.2 and ORDS 23.4 I have recently installed Apex 23.2, ORDS 23.4 for POC purpose and come across below issues when working with APEX and ORDS. <br /><br /><u><b>Bug: Oracle Apex 23.2 with Interactive Grid <br /></b></u><br />When working with Interactive Grid, the filters saved in the Interactive Grid not displaying correct values. The issue happens intermittently, and filter values change from date to string or filter operators may drop out of the list. <br /><br />When users working with operators they see inconsistency in the values, which is a big concern. <br /><br />After research I found that its known issue ang filed bug #36200437 <br /><br />For more detailed info please check below Oracle forum link <br /><br /><a href="https://forums.oracle.com/ords/apexds/post/bug-in-apex-23-2-interactive-grid-multiple-criteria-filter-9238">BUG in APEX 23.2 Interactive Grid: Multiple Criteria Filter Issue - Oracle Forums</a> <br /><br /><u><b>Bug: ORDS 23.4 with PL/SQL <br /></b></u><br />Please see the cause and action for error PLS-00306: wrong number or types of arguments in call to 'string” <br /><br />Cause: <br />This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The subprogram name might be misspelled, a parameter might have the wrong datatype, the declaration might be faulty, or the declaration might be placed incorrectly in the block structure. For example, this error occurs if the built-in square root function SQRT is called with a misspelled name or with a parameter of the wrong datatype. <br /><br />Action: <br />Check the spelling and declaration of the subprogram name. Also confirm that its call is correct, its parameters are of the right datatype, and, if it is not a built-in function, that its declaration is placed correctly in the block structure. <br /><br />But in my case when working with PL/SQL code users receiving “PLS-00306: wrong number or types of arguments in call”. The same code works after some time, after research found that it’s a known bug. <br /><br />For more details, please see the below Oracle forums link. <br /><br /><a href="https://forums.oracle.com/ords/apexds/post/ords-23-4-with-pl-sql-pls-00306-wrong-number-or-types-of-ar-5527">ORDS 23.4 with PL/SQL. PLS-00306: wrong number or types of arguments in call to - Oracle Forums</a> <br /><br />Hope this helps.<div><br />Thanks & Regards.<div>Satishbabu Gunukula, Oracle ACE Pro<br /><div><a href="https://oracleracexpert.com">https://oracleracexpert.com</a> <br /><br /> </div></div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-55233173335418503372024-02-07T20:09:00.000-08:002024-02-17T20:13:25.462-08:00SQL Firewall in Oracle 23cSQL Firewall inspects all incoming statements and ensures only authorized SQL is run and it is embedded in the Oracle Database and unauthorized SQL Statements will be logged and blocked.<br /><br />SQL firewall provides real-time protection from attacks and mitigate risks from SQL injection attacks, anomalous access, credential abuse or theft. SQL Firewall supports all commands except transaction control commands such as SAVEPOINT, COMMIT, ROLLBACK. <br /><br />To administer SQL Firewall user must have SQL_FIREWALL_ADMIN role. To query DBA_SQL_FIREWALL* data dictionary the user must have SQL_FIREWALL_VIEWER role <br /><br />You can Configure SQL Firewall using DBMS_SQL_FIREWALL package or Oracle Data Safe. SQL Firewall can be used in both root and Pluggable Database (PDB) <br /><br /><br />You can enable SQL Firewall using below command. <br /><i>SQL> EXEC DBMS_SQL_FIREWALL.ENABLE; <br /></i><br />Create and enable SQL Firewall capture for a user using below command <br /><br /><div><i>SQL> BEGIN <br />DBMS_SQL_FIREWALL.CAPTURE_CAPTURE ( <br />Username => ‘SCOTT’ <br />top_level_only => TRUE, <br />Start_capture => TRUE <br />); <br />END; </i><br /><br />Enable SQL Firewall Allow List <br /><br /><i> SQL>BEGIN <br />DBMS_SQL_FIREWALL.ENABLE_ALLOW_LIST ( <br />username => ‘SCOTT’, <br />enforce => DBMS_SQL_FIREWALL.ENFORCE_SQL, <br />block => TRUE ); <br />END; <br /></i><br />You can use below commands to START and STOP capture. <br /><br /><i>SQL> EXEC DBMS_SQL_FIREWALL.START_CAPTURE (‘SCOTT’); <br />SQL> EXEC DBMS_SQL_FIREWALL.STOP_CAPTURE (‘SCOTT’); <br /></i><br />You can generate an allow list using below procedures. <br /><br /></div><div><i>DBMS_SQL_FIREWALL.ADD_ALLOWED_CONTEXT <br />DBMS_SQL_FIREWALL.DELETE_ALLOWED_CONTEXT <br />DBMS_SQL_FIREWALL.DELETE_ALLOWED_SQL <br /></i><br />The SQL Firewall may generate large volume of capture logs and to minimize performance impact the database memory needs to be sized to handle the load. It is advised to add additional 2GB to LARGE_POOL_SIZE parameter and also it advised to have SGA_TARGET to 8GB or more. <br /><br />To purge logs you can use below procedure <br /><br /><i>BEGIN <br />DBMS_SQL_FIREWALL.PURGE_LOG ( <br />username => ‘SCOTT’, <br />purge_time => '2024-01-10 12:00:00.00 -08:00', <br />log_type => 'DBMS_SQL_FIREWALL.ALL_LOGS' <br />); <br />END; <br /></i>/ <br /><br />You can also enable and disable SQL Firewall Trace using below commands. The trace level value should be LOW, HIGH, HIGHEST based upon how much detail tracing you want to have. <br /><br />-Session level Tracing Enable and Disable <br /><i>ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level <br />ALTER SESSION SET EVENTS 'TRACE SQL_FIREWALL OFF <br /></i><br />-System Level Tracing Enable and Disable <br /><i>ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL DISK=trace_level <br />ALTER SYSTEM SET EVENTS 'TRACE SQL_FIREWALL OFF </i><br /><br />You can query below Data Dictionary Views for SQL Firewall protections <br /><i>DBA_SQL_FIREWALL_ALLOWED_SQL - View shows allowed SQL and Accessed objects </i><br /><i>DBA_SQL_FIREWALL_ALLOWED_IP_ADDR – View shows the Users allowed IP address </i><br /><i>DBA_SQL_FIREWALL_CAPTURE_LOGS – View shows the Capture log entries </i><br /><i>DBA_SQL_FIREWALL_VIOLATIONS – View shows the SQL Firewall Violations </i><br /><br />Thanks & Regards</div><div>https://oracleracexpert.com</div><div>Oracle ACE Pro<br /> <br /><br /> </div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-51572151719442764312024-01-05T23:03:00.000-08:002024-01-05T23:08:13.244-08:00Oracle Agile Application or Quick Search performance issue and how to resolve?When you come across any performance issue on agile first, you need to verify schema integrity by running agile9_check.sql. <br /><br />The agile9_check.sql it verifies Database Schema version with Agile Schema version, and it MUST match. Also, it validates schema objects against Agile schema version and report any errors or warnings. <br /><br />You may see ERRORS/WARNINGS <br /><b><i>ERROR: Missing or INVALID index (columns) XXXXXXXXX on table XXXXX. <br />ERROR: Missing or INVALID FTS CTX indexes XXXXXXX. <br />WARNING: ACTIVITY_BASELINE_XXXXX does not belong to AGILE DB. Please Drop the Table if not required. <br /></i></b><br />In case of any Errors/Warnings you need we need to fix the issue. If you have created any custom objects you will see WARNING that object doesn't below to Agile DB and you can ignore. <br /><br />Once user fix the schema integrity download listFTSInfo.sql and run the script as Agile user and it will generate listFTSInfo_xxxxxxxxxx.log file. You need to search for "029", where it says "List Index Percent of Fragmentation" where you can see the indexes that has fragmentation. <br /><br />If you see any fragmentation, it will effect Quick search and stuck threads will be generated. <br /><br /><i><span style="color: red;"><b><[STUCK] ExecuteThread: '19' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "1,247" seconds working on the request "Http Request Information: weblogic.servlet.internal.ServletRequestImpl@2f2fb94[GET /Agile/PCMServlet]<br /> ", which is more than the configured time (StuckThreadMaxTime) of "1,200" seconds in "server-failure-trigger". Stack trace:<br /> java.net.SocketInputStream.socketRead0(Native Method)</b><br /></span></i> <br />To resolve the issue, Log into sqlplus as agile database user, and run <b>agile_ctx_recreate.sql</b> found under %Oracle_base%\admin\{SID}\create\{agile_schema_username} to rebuild CTX indexes. <br /><br />Oracle support recommends stopping Agile application, run agile_ctx_recreate.sql and Start the Application. <br /><br />It is highly advisable to run <b>complie_Invalid_objects.sql</b>, <b>agile9stats.sql</b> to recompile invalid objects and collect stats to improve the performance. <br /><br />Refer Oracle Agile support doc for Full Text search (FTS) Enablement, synchronization, Indexing and optimization. <br /><br /><b>Frequently Asked Questions on Agile Product Lifecycle Management (PLM) Full Text Search (FTS) Enablement, Synchronization, Indexing, and Optimization (Doc ID 1503311.1) <br /></b><br />For Agile Stuck thread issue, refer below Oracle support Doc ID <br /><br /><b>Agile Managed Servers Goes to Warning State, Having Stuck Thread at com.agile.cs.query.QuerySessionBean (Doc ID 2919154.1) <br /></b><br /> Thanks & Regards<div>https://oracleracexpert.com, Oracle ACE<br /><br /> </div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com2tag:blogger.com,1999:blog-6850817271941197798.post-44034601104679359552023-12-31T19:09:00.000-08:002024-03-12T17:03:12.663-07:00Webinar: Oracle Database 23c Security new featuresThis Webinar helps you to understand Oracle Database 23c new Security features and make use of these cutting edge functionalities.<br /><br /><div>Date and time: Jan 8th 2024, 8:00am-9:00am<br />Pacific Daylight Time (San Francisco, GMT-07:00)<br /><br />This Webinar covers following Topics.<br /><ul style="text-align: left;"><li><b> SQL Firewall</b></li><li><b> Audit</b></li><li><b> Authentication</b></li><li><b> Authorization</b></li><li><b> Encryption</b></li><li><b> Autonomous Database</b></li><li><b>Other</b></li></ul></div><div>To register for this Webinar, please send an email to <a href="mailto:SatishbabuGunukula@gmail.com">SatishbabuGunukula@gmail.com</a><br />You will receive an email confirmation with meeting link or Webinar link will be posted here.<br /><br />Note that registrations are limited and first come and first serve basis.<br /><br />For presentation link<a href="https://www.slideshare.net/slideshows/oracle-database-23c-security-new-featurespptx/266701588"> Click here</a><br /><br />Thanks & Regards,<br />http://www.oracleracexpert.com</div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-10273754558890057602023-11-09T11:42:00.010-08:002023-11-09T11:45:56.734-08:00ORA-29861: domain index is marked LOADING/FAILED/UNUSABLEWhen users performing the transactions on tables where DOMAIN,SPATIAL indexes are part of it they may receive below error and unable to proceed. <br /><br />SQL> DELETE FROM USER_URL where CXT_ID=8484884; <br /><b><span style="color: red;">ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE<br /></span></b><br />The main reason for this error is user might have copied the table/index using export/import or different method but the CREATE INDEX and failed. <br /><br />Find out what caused the failure of CREATE INDEX. In many cases not having enough space on the Tablespace where you are creating the index is the culprit. <br /><br />SQL> SELECT INDEX_NAME, TABLE_NAME STATUS,DOMIDX_STATUS,DOMIDX_OPSTATUS FROM DBA_INDEXES WHERE DOMIDX_OPSTATUS='FAILED';<br /><br />INDEX_NAME TABLE_NAME STATUS DOMIDX_STATUS DOMIDX_OPSTATUS <br />------------------ --------------- ---------- --------------------- ------------------------- <br />USER_URL_IDX USER_URL VALID VALID <b><span style="color: red;">FAILED </span></b><br /><br /> <br />When verified I found that schema refreshed recently, and index not created successfully. <br /><br /><span style="color: red;">ORA-31693: Table data object "FIN"."USER_URL" failed to load/unload and is being skipped due to error: <br />ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE <br /></span><br />If you need workaround to proceed with transactions immediately, then drop the index. <br />The permanent fix will be drop and recreate index successfully. <br /><br />To avoid this type of issues, make sure you perform following checks. <br /><br />1. Before refresh verify the tablespace or file systems has enough space and add required space <br />2. After import check the data pump or import log file and fix all failures <br />3. Run utlrp.sql to recompile all invalid objects. <br /><br /> <div>Thanks & Regards</div><div><a href="https://oracleracexpert.com">https://oracleracexpert.com</a></div><div><br /></div><div><br /></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-40626021428639646322023-10-20T18:51:00.001-07:002023-11-09T11:36:43.435-08:00Oracle "Hyperion EPMA Server" service is not coming onlineRecently I have encountered this issue with Oracle Hyperion. When we restarted Hyperion server, the “Hyperion EPMA Server” service is not coming online and application is down.
<br />
<br /><strong>Here are the Errors from event.log</strong>
<br />
<br /><strong>EPMA_Server</strong>
<br />[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
<br /> StackTrace:
<br />
<br /> at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl)
<br /> at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString()
<br /> at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching)
<br /> --- End of inner exception stack trace ---
<br />
<br />
<br /><strong>Hyperion EPMA DimmensionServer</strong>
<br />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
<br />StackTrace:
<br />at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl)
<br />at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString()
<br />at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching)
<br />--- End of inner exception stack trace ---
<br />
<br />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 .
<br />
<br />I found below error in <strong>EssbaseAdminServices0.log</strong>
<br />####<Feb 14, 2012 10:00:17 AM PST> <Warning> <JDBC> <HQ-HYPPRD> <EssbaseAdminServices0> <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1329242417925> <BEA-001129> <Received exception while creating connection for pool "EPMSystemRegistry": <strong>ORA-28001: the password has expired</strong>
<br />
<br />Now it’s clear that Database user password expired.
<br />
<br />SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘SHARED_SERVICES’;
<br />
<br />USERNAME ACCOUNT_STATUS
<br />------------------------------ --------------------------------
<br />SHARED_SERVICES EXPIRED(GRACE)
<br />
<br />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.
<br />
<br />If you know the password then set the using below command.
<br />SQL> Alter user SHARED_SERVICES identified by ‘xxxxxx’;
<br />
<br />If you don’t know the password then reset the password following below steps
<br />SQL> select password from sys.user$ where name='SHARED_SERVICES';
<br />
<br />PASSWORD
<br />------------------------------
<br />61266722B44D5BG418
<br />
<br />SQL> alter user QWERTY identified by values '61266722B44D5BG418’;
<br />User altered.
<br />
<br />Also make sure to modify PASSWORD_LIFE_TIME to “UNLIMITED” for DEFAULT or User profile that you are using
<br />
<br />SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
<br />Profile altered.
<br />
<br />Regards,
<br />Satishbabu Gunukula
<br />http://www.oracleracexpert.comSatishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com5tag:blogger.com,1999:blog-6850817271941197798.post-14270515290787178222023-10-20T18:46:00.004-07:002023-10-20T18:53:25.921-07:00ORA-02304: invalid object identifier literal and ORA-39083 errors during impdpUsers might receive below error during the import. <br /><br /><b><i><span style="color: red;">ORA-39083: Object type TYPE:"ORCL"."OID_TAB1" failed to create with error: <br />ORA-02304: invalid object identifier literal </span><br /><br />Failing sql is: <br />CREATE EDITIONABLE TYPE "ORCL"."OID_TAB1" OID 'FB3CF41KK327B011D053F114ABBC878C' AS OBJECT ( <br />className1 VARCHAR2(200), <br />id1 NUMBER) </i></b><br /><br />Run below query in the database and check for TPYE_ODI exists or not<div> <br />SQL> select OWNER, TYPE_NAME from DBA_TYPES where TYPE_OID='FB3CF41KK327B011D053F114ABBC878C '; <br /><br /><div>OWNER TYPE_OID <br />------------------------------------------ -------------------------------- <br />ORCL FB3CF41KK327B011D053F114ABBC878C <br /><br />In most cases you will see the ODI is already exists in the database. The ODI already exists, and you cannot create ODI with same name that’s why it failed. You need to follow one of the options. <br /><br />1. Create the object with new ODI – You can remove the ODI clause from the statement and run again. The database will generate new ODI for the object. <br /><br />2. Drop the existing TYPE_ODI and reimport - DROP the object and reimport so that object will create with same ODI <br /><br />3. In “impdp” use the parameter transform=oid:n - Using the parameter during the impdp will import TYPES with new ODI. <br /><br />I have used all 3 methods based upon different scenarios. <br /><br />Thanks & Regards,</div><div>http://oracleracexpert.com</div><div><br /><br /> <br /><br /> </div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-22123297033415213412023-10-16T19:50:00.001-07:002023-10-20T19:55:56.592-07:00WARNING: inbound connection timed out (ORA-3136)The ORA-3136 will be written into alert.log when users fails to provide credentials and cannot authenticate within the set timeout value. <br /><br />The default value set for below parameters in sqlnet.ora is 60 seconds. <br /><br /><b>SQLNET.INBOUND_CONNECT_TIMEOUT <br />INBOUND_CONNECT_TIMEOUT_listener_name <br /></b><br />You will see below error in the alert.log file <br /><br /><span style="color: red;">Fatal NI connect error 12170. <br />VERSION INFORMATION: <br />TNS for Linux: Version 19.0.0.0.0 - Production <br />Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production <br />TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production <br />Version 19.5.0.0.0 <br />Time: 14-OCT-2023 18:06:02 <br />Tracing not turned on. <br />Tns error struct: <br />ns main err code: 12535 <br /><br />TNS-12535: TNS:operation timed out <br />ns secondary err code: 12606 <br />nt main err code: 0 <br />nt secondary err code: 0 <br />nt OS err code: 0 <br />Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xx.xxx.xx)(PORT=51290)) <br />2023-10-12T16:06:02.222415-07:00 <br />WARNING: inbound connection timed out (ORA-3136) <br />2023-10-12T16:15:18.866821-07:00 </span><br /><br />In the above log you can see the client IP and port, you can able to check corresponding entry in the listener log as well. Also, you can see the time stamp of user initiated the connection and time stamp when user got error, it will be more than 60 sec. <br /><br />The main reason for the error is … <br /><br />1. When user or application trying to connect using wrong credentials or no attend made under 1 min threshold default value of the instance. <br /><br />You can able to reproduce the issue by entering wrong credentials<div> <br />SQL> sqlplus HR/xxxx@orcl <br />SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 13 19:31:30 2023 <br />Version 19.18.0.0.0 <br />Copyright (c) 1982, 2022, Oracle. All rights reserved. <br /><br />ERROR: <br />ORA-01017: invalid username/password; logon denied <br /><br />$ tail -f orcl_alert.log <br />Fri Oct 13 19:32:31 2023 <br />WARNING: inbound connection timed out (ORA-3136) <br /><br />2. If the database has some performance issues or any network delay can cause long time and cannot authenticate user within default time out i.e. 60 seconds <br /><br />If the database has some load causing the slowness, then increasing SQLNET.INBOUND_CONNECT_TIMEOUT to higher value will help to reduce the errors. <br /><br />No DB restart required when you make the change in sqlnet.ora but note that it will be applicable to next server process. <br /><br />3. The server receives the request but cannot be able to authenticate with in default time out i.e. 60 seconds <br /><br />Find out what causing the delay in authentication if required increase the time out value. <br /><br />If you are receiving error frequently you can enable tracing using below command <br />SQL> alter system set events '3136 trace name errorstack level 3';<div> <br />You can trun off tracing using <br />SQL> alter system set events '3136 trace name context off'; <br /><br />Note that tracing will be generated under USER_DUMP_DEST or BACKGROUND_DUMP_DEST <br /><br />If you still seeing these warnings I would suggest to raise a ticket with oracle support <br /><br />Thanks,<br /><a href="https://oracleracexpert.com">https://oracleracexpert.com</a></div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-6893184653444967362023-08-14T10:53:00.013-07:002023-08-14T10:57:03.032-07:00Article: Mastering Data History using Oracle's Flashback Data Archive FeatureThe Historical data is key for business decisions and having reliable data is very important for organizations. There are frequent implications for both financial and legal data for organizations and having a secure and accurate history of change of data is crucial for business. <br /><br />Oracle Flashback technology offers significant benefits for database management and recovery. <div><br /><div>Please see the below article how it will help to meet the organizational needs.<div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><h3><span style="font-weight: 700; text-align: left;"><span style="color: red;">Mastering Data History : A Guide using Oracle's Flashback Data Archive Feature</span></span><br /><a href="https://www.linkedin.com/pulse/mastering-data-history-guide-using-oracles-flashback-archive?trk=public_profile_article_view">https://www.linkedin.com/pulse/mastering-data-history-guide-using-oracles-flashback-archive?trk=public_profile_article_view</a></h3></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNNCTkvj2AkU_s9rCPFgzvK-gi4K7aG1MFmFtODvPduYa96ZfroaIYgr7joyz_FPOb28O4yoKNtcUMDNIWPa20H0hQg4goLnVf_hlqTU2VJgBNuK73AG770L2oE5zZ7A0cDUXsPNMcY8O8FdL-n8EptMBW4NR0m0RhuAv83bRRcvVEc6iOgD0Kwf17vA0/s590/FDA%20Small.PNG" style="margin-left: 1em; margin-right: 1em;"><span style="color: black;"><br /></span><img border="0" data-original-height="445" data-original-width="590" height="301" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNNCTkvj2AkU_s9rCPFgzvK-gi4K7aG1MFmFtODvPduYa96ZfroaIYgr7joyz_FPOb28O4yoKNtcUMDNIWPa20H0hQg4goLnVf_hlqTU2VJgBNuK73AG770L2oE5zZ7A0cDUXsPNMcY8O8FdL-n8EptMBW4NR0m0RhuAv83bRRcvVEc6iOgD0Kwf17vA0/w400-h301/FDA%20Small.PNG" width="400" /></a></div><br /><div><div>Thanks,</div><div>https://oracleracexpert.com</div></div></div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com6tag:blogger.com,1999:blog-6850817271941197798.post-87719164076779758332023-08-10T18:51:00.001-07:002023-08-16T18:57:56.330-07:00SELECT without FROM Clause in Oracle 23cOracle 23c has many new features and “SELECT without FROM” is one of the features. By using this feature, you can run queries without using FROM clause and specifying table name for testing expressions to get the results which can be easy of use for developers. <br /><br />You will no longer receive error “ORA-00923: FROM keyword not found where expected” when running expressions to get results in Oracle 23c. <br /><br />Here are few examples<div><br />Example 1: Run mathematical operations with or without using FROM clause and you will get the result<br /><br />SQL> select 2+3 from dual; <br />SQL> select 2+3 ; <br /><br />Example 2: Select current date with and without using FROM clause and you will get the result.</div><div><br />SQL> Select current_date from dual; <br />SQL> Select current_date; <br /><br />Example 3: Select NEXTVAL with and without using FROM clause and you will get the result<br /><br />SQL> Create sequence empno_seq;</div><div> <br />SQL> select empno_seq.nextval from dual; <br />SQL> select empno_seq.nextval ; <br /><br />Example 4: Pl/SQL block with and without using FROM clause and you will get the result<br />delcare <br />v1 number; <br />begin <br />select empno_seq.nextval into v1 from dual; <br />dbms_output.put_line ('v1= '||v1); <br />end; <br />/ <br /><br />delcare <br />v1 number; <br />begin <br />select empno_seq.nextval into v1; <br />dbms_output.put_line ('v1= '||v1); <br />end; <br />/ <br /><br /></div><div>Many other databases such as MS SQL Server, MYSQL support without FROM clause, this will help improve SQL Code portability.</div><div><br /></div><div>Thanks</div><div><a href="https://oracleracexpert.com">https://oracleracexpert.com</a></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com3tag:blogger.com,1999:blog-6850817271941197798.post-57975823533796179942023-07-21T19:01:00.000-07:002023-08-16T08:12:30.383-07:00Flashback Data Archive enhancements in Oracle 12cFlashback feature uses Automatic Undo Management for historical and metadata transactions. Flashback Data archive (FDA) feature introduced in Oracle 11g for undo-based flashback operations, and it is configured using retention time. Flashback data archive supported for multitenant (12.1.0.2 and above versions) as well using local or shared undo configuration.<br /><br />Oracle 12c (12.1.0.1) has below enhancements in FDA <br /><br /><ul style="text-align: left;"><li>Data Hardening</li><li>User context tracking</li></ul><b><u><span style="font-size: medium;">Data Hardening</span> </u>–</b> This feature helps to associate set of tables for a specific application, so that you can enable Flashback Data archive for all those tables in a single command. Use DMBS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION to register an application<br /><br />You can create new application using REGISTER_APPLICATION <br /><br /><b><i>SQL> Begin</i></b><b><i><br />DBMS_FLASHBACK_ARCHIVE.register_application( <br />application_name => 'ORACLERACEXPERT', <br />flashback_archive_name => 'FDA1'); <br />end; <br /></i>/ </b><br /><br />You can add tables to the application using ADD_TABLE_TO_APPLICATION procedure <br /><br /><b><i>SQL> Begin</i></b><i><b><br />DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (</b></i><div><i><b>application_name=> 'ORACLERACEXPERT', </b></i></div><div><i><b>table_name=> 'EMP' , </b></i></div><div><b><i>schema_name -> 'USER1'); <br />end; <br />/ </i><br /><br /></b><b><i>SQL> Begin</i></b><b><i><br />DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION (</i></b><div><i><b>application_name=> 'ORACLERACEXPERT', </b></i></div><div><i><b>table_name=> 'DEPT' , </b></i></div><div><i><b>schema_name -> 'USER1'); <br />end; <br />/ </b></i><br /><br />You can remove the tables using REMOVE_TABLE_FROM_APPLICATION procedure from <br /><br /><div>The application will not enable automatically, use ENABLE_APPLICATION procedure to enable Flashback Data Archive for all tables in the specified application. <br /><br /><b><i>SQL> Begin</i></b><i><b><br />DBMS_FLASHBACK_ARCHIVE.enable_application(</b></i><div><i><b>application_name => 'ORACLERACEXPERT'); <br />end; <br />/ </b><br /></i><br /><div style="text-align: left;">To disable the application use disable_application procedure <br /><br /><b><i>SQL> Begin</i></b><b><i><br />DBMS_FLASHBACK_ARCHIVE.disable_application( <br />application_name => 'ORACLERACEXPERT'); <br />end; <br />/ </i><br /></b><br /><b><u><span style="font-size: medium;">User context tracking</span></u> -</b> By enabling this feature it is easy to track which user made what changes to the table.<br /><br />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 <br /><br />There are 3 options depending upon how much user context needs to save <br />ALL – The entire SYS_CONTEXT is stored <br />TYPICAL – The user context is stored <br />NONE- Nothing <br /><br />For ex: - To set context level to ALL <br /><i><b>SQL> DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL ( level=>‘ALL’); </b><br /></i><br />You can get the XID from the archive table <br /><br /></div><div style="text-align: left;"><i><b>SQL> select XID from SYS_FBA_HIST_93222; <br />XID <br />---------------- <br />05000A0B7040000 </b><br /></i><br />Now with XID you can get the context information using dbms_flashback_archive.get_sys_context procedure <br /><br /> <br /><b><i>SQL> begin <br />dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'SESSION_USER')); <br />dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'HOST')); <br />dbms_output.put_line(dbms_flashback_archive.get_sys_context ('05000A0B7040000', 'USERENV', 'MODULE')); <br />end; </i></b><br />/ <br />USER1 <br />SRVHOST <br />SQL*Plus <br /><br />You can get all Transactions ID using below query <br /><br /><b><i>SQL> select empno, empname, VERSIONS_XID <br />from EMP order by empno; <br /><br /> EMPNO EMPNAME VERSIONS_XID <br />----------- --------------- --------------------- <br />1 ORARAC 05000A0B7040000 </i></b><br /><br />You can use SYS_FBA_CONTEXT_AUD to get context information for each transaction. <br /><br />There are many Flashback data archive view available and to get the list of all views run below query</div><div style="text-align: left;"><br /></div><div style="text-align: left;"><b><i>SQL> SET PAGESIZE 1000 <br />SQL> SET LINESIZE 600 <br />SQL> COLUMN owner FORMAT A10 <br />SQL> COLUMN table_name FORMAT A25 <br /><br />SQL> SELECT owner, table_name FROM dba_tables WHERE table_name LIKE '%FBA%';</i></b></div><div style="text-align: left;"><b><i> <br />OWNER TABLE_NAME <br />-------------- ------------------------- <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_FA <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_TSFA <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_TRACKEDTABLES <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_PARTITIONS <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_USERS <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_BARRIERSCN <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_DL <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_CONTEXT <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_CONTEXT_AUD <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_CONTEXT_LIST <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_APP <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_APP_TABLES <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_COLS <br />SYS <span> </span><span> </span><span> </span> SYS_FBA_PERIOD <br />SYS <span> </span><span> </span><span> </span> SYS_MFBA_STAGE_RID <br />SYS <span> </span><span> </span><span> </span> SYS_MFBA_TRACKED_TXN <br />SYS <span> </span><span> </span><span> </span> SYS_MFBA_NROW <br />SYS <span> </span><span> </span><span> </span> SYS_MFBA_NCHANGE <br />SYS <span> </span><span> </span><span> </span> SYS_MFBA_NTCRV </i></b><br /><br />You can refer below Oracle Doc for best practices <br /><br />FDA - Flashback Data Archive Usage and Best Practices (Doc ID 2370465.1) <br /><br />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.<br /> </div></div></div></div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com9tag:blogger.com,1999:blog-6850817271941197798.post-62180483024838475282023-06-08T12:18:00.002-07:002023-06-08T12:18:13.643-07:00Oracle 19c import issues ORA-31693, ORA-02354, ORA-39002, ORA-39405When performing import come across the following issue <br /><br /><i><span style="color: red;">ORA-31693: Table data object "ORCL"."ITEMCG" failed to load/unload and is being skipped due to error: <br />ORA-02354: error in exporting/importing data <br />ORA-39840: A data load operation has detected data stream format error . <br />ORA-39844: Bad stream format detected: [klaprs_62] [139751105749101] [139751105749012] [4] [2] [2065583] [] [] <br /></span></i><br />User may encounter this issue when new column added to a table with cokumn optimization enabled and the same column was modified. <br /><br />Below are the workarounds <br /><br /><ul style="text-align: left;"><li>Use access_method=EXTERNAL_TABLE during export </li></ul><div><ul style="text-align: left;"><li>Prior export add and drop a dummy column to the problematic table using </li></ul>SQL> ALTER TABLE <table_name> ADD dummy number; <br />SQL> ALTER TABLE <table_name> DROP dummy number;<br /></div><div><ul style="text-align: left;"><li>Take export of failed table and import using CONTENT=DATA_ONLY as tableau structure already imported. </li></ul><br />The fix for this bug was initially available on 19.13 and above see if it helps. <br /><br />Also, I come across below issues when importing data <br /><br /><i><span style="color: red;">ORA-39002: invalid operation <br />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. <br /></span></i><br />User may encounter this issue when export from higher time zone version and importing into lower time zone version.<br /></div><div><ul style="text-align: left;"><li>Patch the target database to higher or equal to source time zone patch or DST TZ version </li><li>Create a database with same time zone and perform export/import </li></ul>For latest DST patches refer,<br /><ul style="text-align: left;"><li>Oracle RDBMS and OJVM DST-related notes </li><li>Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)</li></ul><br /></div><div>Hope this helps,</div><div><br /></div><div>Thanks & Regards</div><div><a href="https://oracleracexpert.com">https://oracleracexpert.com</a></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com3tag:blogger.com,1999:blog-6850817271941197798.post-11494502582548898672023-06-08T11:12:00.006-07:002023-06-12T17:10:54.557-07:00Active Sessions, Proxy Sessions and Locked objects in OracleI have received few requests from Oracle User community to provide SQL queries related to active sessions, proxy sessions and locked objects in a session. Here are few SQL queries which you can use in day to day monitoring.<div> <br /><b>--------------------------------------------------------</b><b><br />--Script : ACTIVE SESSIONS<br />--Author : oracleracexpert.com<br /></b><b>--------------------------------------------------------</b><br />SET PAGESIZE 1000<br />SET LINESIZE 600<br /><br />COLUMN username FORMAT A10<br />COLUMN osuser FORMAT A10<br /><div>COLUMN sid FORMAT 9999</div><div>COLUMN serial# FORMAT 999999999</div>COLUMN status FORMAT A10<br />COLUMN machine FORMAT A20<br />COLUMN program FORMAT A40<br />COLUMN module FORMAT A35<br />COLUMN action FORMAT A15<br />COLUMN logon_time FORMAT A20<br /><br />SELECT s.username,<br /></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div>s.osuser,</div><div>s.sid,</div><div>s.serial#,</div><div>p.spid,</div><div>s.lockwait,</div><div>s.status,</div><div>s.machine,</div><div>s.program,</div><div>s.module,</div><div>s.action,</div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div>TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,</div><div>s.blocking_session_status AS BlockStatus</div></blockquote><div>FROM v$session s, v$process p<br />WHERE s.paddr = p.addr<br />AND s.status = 'ACTIVE'<br />ORDER BY s.username, s.osuser;<br /><br /><div>------------------------------------</div><div><b>--Script : Active Session waits</b></div><div><b>--Author : oracleracexpert.com</b></div><div>------------------------------------</div><div>SET PAGESIZE 1000</div><div>SET LINESIZE 600</div><div><br /></div><div>COLUMN username FORMAT A10</div><div>COLUMN osuser FORMAT A10</div><div>COLUMN sid FORMAT 9999</div><div>COLUMN serial# FORMAT 999999999</div><div>COLUMN spid FORMAT A10</div><div>COLUMN state FORMAT A10</div><div>COLUMN wait_class FORMAT A20</div><div>COLUMN seconds_in_wait FORMAT 999999999</div><div>COLUMN module FORMAT A35</div><div>COLUMN blocking_session FORMAT A20</div><div>COLUMN blocking_session_status FORMAT A20</div><div><br /></div><div>SELECT s.username,</div><div> s.osuser,</div><div> s.sid,</div><div> s.serial#,</div><div><span style="white-space: normal;"><span style="white-space: pre;"> </span> p.spid, </span></div><div> s.state,</div><div><span style="white-space: normal;"><span style="white-space: pre;"> </span> s.wait_class,</span></div><div> s.seconds_in_wait,</div><div> s.module,</div><div> TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,</div><div><span style="white-space: normal;"><span style="white-space: pre;"> </span> s.blocking_session,</span></div><div> s.blocking_session_status AS BlockStatus</div><div>FROM v$session s, v$process p</div><div>WHERE s.paddr = p.addr</div><div>AND s.status = 'ACTIVE'</div><div>ORDER BY 1,2;</div><div><br /></div><div><br /></div><b>--------------------------------------------------------<br />--Script : PROXY SESSIONS<br />--Author : oracleracexpert.com<br />--------------------------------------------------------</b><br />SET PAGESIZE 1000<br />SET LINESIZE 600<br /><br />COLUMN username FORMAT A10<br />COLUMN osuser FORMAT A10<br /><div>COLUMN sid FORMAT 9999</div><div>COLUMN serial# FORMAT 999999999</div>COLUMN status FORMAT A10<br />COLUMN machine FORMAT A20<br />COLUMN program FORMAT A40<br />COLUMN module FORMAT A35<br />COLUMN action FORMAT A15<br />COLUMN logon_time FORMAT A20<br /><br />SELECT s.username,<br /></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div>s.osuser,</div><div>s.sid,</div><div>s.serial#,</div><div>p.spid,</div><div>s.lockwait,</div><div>s.status,</div><div>s.machine,</div><div>s.program,</div><div>s.module,</div><div>s.action,</div><div>TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,</div><div>s.blocking_session_status AS BlockStatus</div></blockquote><div>FROM v$session s, v$process p, v$session_connect_info sci<br />WHERE s.paddr = p.addr<br />AND s.sid = sci.sid<br />AND s.serial# = sci.serial#<br />AND sci.authentication_type = 'PROXY'<br />ORDER BY s.username, s.osuser;<br /><br /><div><b>--------------------------------------------------------<br />--Script : LOCKED OBJECT SESSIONS<br />--Author : oracleracexpert.com<br />--------------------------------------------------------</b><br />SET PAGESIZE 1000<br />SET LINESIZE 600<br /><br /><div>COLUMN sid FORMAT 9999</div><div>COLUMN serial# FORMAT 999999999</div>COLUMN status FORMAT A10<br />COLUMN owner FORMAT A20<br />COLUMN object_owner FORMAT A20<br />COLUMN object_name FORMAT A30<br />COLUMN object_type FORMAT A15<br />COLUMN oracle_username FORMAT A15<br />COLUMN locked_mode FORMAT A15<br />COLUMN os_user_name FORMAT A15<br /><br />SELECT s.sid,<br /></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>s.serial#,</div></div><div><div>s.status, </div></div><div><div>do.owner, </div></div><div><div>do.object_name,</div></div><div><div>do.object_type,</div></div><div><div>lo.oracle_username,</div></div><div><div>Decode(lo.locked_mode, 0, 'None',</div></div><div><div>1, 'Null (NULL)',</div></div><div><div>2, 'Row-S (SS)',</div></div><div><div>3, 'Row-X (SX)',</div></div><div><div>4, 'Share (S)',</div></div><div><div>5, 'S/Row-X (SSX)',</div></div><div><div>6, 'Exclusive (X)',</div></div><div><div>lo.locked_mode) locked_mode,</div></div><div><div>lo.os_user_name</div></div></blockquote><div><div>FROM v$locked_object lo, dba_objects do, v$session s <br />WHERE lo.session_id = s.sid<br />AND do.object_id = lo.object_id<br />ORDER BY 1, 2, 3, 4;<br /><br /></div><div>Thanks & Regards,</div><div>https://oracleracexpert.com</div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-92020183103414900112023-05-30T10:18:00.001-07:002023-05-30T10:18:05.879-07:00Errors ORA-12154, ORA-29003 when connecting to Autonomous Data Warehouse using DjangoWhen connection to Autonomous Data Warehouse Using Django web framework first we come across “ORA-12154” error. That means Django cannot be able to connect to the Oracle Database service name with the settings in settings.py file. <br /><br /><span style="color: red;"><i>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 </i></span><br /><br />First copy the tnsnames.ora into local system and test tnsping. <br /><br />C:\> tnsping <service Name> <br /><br />If the tnsping is working fine, then below are the passible reason for ORA-12154 error <br /><ul style="text-align: left;"><li>TNS_ADMIN might not be configured or set the right path </li><li>There might be a typo in service name </li><li>Connection details in settings.py might not be correct </li></ul>We found that TNS_ADMIN is not set to the right path and updating the correct path resolved the issue. But we have received new error <br /><br /><i><span style="color: red;">ORA-29003: SSL transport detected mismatched server certificate <br /></span></i><br />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. <br /><br />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)” <br /><br />We have installed Oracle 19c client and were able to connect without any issues. <br /><br />Below are the options to resolve the issue <br /><br />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) <br /><br />2. Update Autonomous database instance to allow both TLS and mTLS authentication.<br /><ul style="text-align: left;"><li>Goto Autonomous Database Details page --> Network --> click Edit in the Mutual TLS (mTLS) Authentication field. </li><li>Change the value to allow TLS authentication, deselect Require mutual TLS (mTLS) authentication </li><li>Click Update </li></ul>3. Use ssl_server_dn_match=no in the connect string (when using 12c client updating the value to “no” didn’t help ) <br /><br />I hope this helps. <br /><br /><div>Thanks <br />Satishbabu G, Oracle ACE Pro</div><div><a href="https://oracleracexpert.com">https://oracleracexpert.com</a></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-18356525174910800242023-05-30T09:40:00.003-07:002023-05-30T10:43:26.186-07:00Oracle "Hyperion EPMA Server" service is not coming onlineI have come across below issue wtih Oracle Hyperion. When we restarted Hyperion server, the “Hyperion EPMA Server” service is not coming online and application is down. <br /><br /><b>Here are the Errors from event.log </b><br /><br /><b>EPMA_Server <br /></b><i><br />[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 <br /></i><br /><div>StackTrace: <br /><br />at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl) <br /><br />at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString() <br /><br />at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching) <br /><br />--- End of inner exception stack trace --- <br /><br /> <br /><b>Hyperion EPMA DimmensionServer <br /></b><br />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 <br />StackTrace: <br />at Hyperion.DimensionServer.SessionManager.CasSecurityProvider.GetDataBaseDetails(String& vendor, String& serverName, Int32& dbPort, String& dbName, String& username, String& password, String& jdbcUrl) <br />at Hyperion.DimensionServer.SessionManager.SessionManager.InitializeSqlConnectionString() <br />at Hyperion.DimensionServer.SessionManager.SessionManager.Initialize(String configFileName, Boolean restorePastInstanceSessions, Boolean enableCaching) <br />--- End of inner exception stack trace --- <br /><br /><br />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 . <br /><br />I found below error in EssbaseAdminServices0.log <br /><br />#### <[ACTIVE] ExecuteThread: '0' for queue: 'weblogic.kernel.Default (self-tuning)'> <> <> <> <1329242417925> ORA-28001: the password has expired <br /><br />Now it’s clear that Database user password expired. <br /><br />SQL> select USERNAME, ACCOUNT_STATUS from dba_users where USERNAME = ‘SHARED_SERVICES’; <br />USERNAME ACCOUNT_STATUS <br />------------------------------ -------------------------------- <br />SHARED_SERVICES EXPIRED(GRACE) <br /><br />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. <br /><br />If you know the password then set the using below command. <br />SQL> Alter user SHARED_SERVICES identified by ‘xxxxxx’; <br /><br />If you don’t know the password then reset the password following below steps <br />SQL> select password from sys.user$ where name='SHARED_SERVICES'; <br /><br />PASSWORD <br />------------------------------ <br />61266722B44D5BG418 <br /><br />SQL> alter user QWERTY identified by values '61266722B44D5BG418’; <br />User altered. <br /><br />Also make sure to modify PASSWORD_LIFE_TIME to “UNLIMITED” for DEFAULT or User profile that you are using <br /><br />SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited; <br />Profile altered. <br /><br />Regards, <br />Satishbabu Gunukula <br />http://www.oracleracexpert.com</div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-57019171780592036552023-05-14T19:05:00.008-07:002023-05-30T20:56:49.441-07:00UN World Innovation Day: Igniting Change through Creativity and CollaborationWhenever there is a global challenge, the Innovation has always played an important role to solving challenges. Every year the United Nations celebrates Innovation Day on the 21st of April to recognizes the importance of innovation in harnessing the economic potential of nations. As world faces unprecedented crises in health, education, the future of work and economy, it is important for us to find innovative solutions together <br /><br />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. <br /><br /> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYMLnmRK3HgGGVheycn-4VgZXy6vUjyQV9_F6yh5Hk168Fds3dPlAF6u54Xelueh_WPDpRyp1vTz4vaSSrvBISfKfZrnbqH5vY9gRHfBC2FduPSc7Oy2PJzSKaUbk8ZjRjkqlYoT9IoWad0byockyHSVbTY-9UtRn6Q1S1H77m3klnBufM4Fhr8KBn/s624/hack%20pic1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="387" data-original-width="624" height="271" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYMLnmRK3HgGGVheycn-4VgZXy6vUjyQV9_F6yh5Hk168Fds3dPlAF6u54Xelueh_WPDpRyp1vTz4vaSSrvBISfKfZrnbqH5vY9gRHfBC2FduPSc7Oy2PJzSKaUbk8ZjRjkqlYoT9IoWad0byockyHSVbTY-9UtRn6Q1S1H77m3klnBufM4Fhr8KBn/w437-h271/hack%20pic1.png" width="437" /></a><br /><br />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. <br /><br />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. <br /><br />This year's hackathon introduces two brand new challenges: <br /><ul style="text-align: left;"><li>GPT4Good </li><li>Data4Good </li></ul>These challenges can address crucial areas where innovation can make a significant impact. <br /><br />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.<div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoCdkiZGdcusJ_23dkta17-yJpQBTuQO4d8ajxXK-4Kt4cpBLac0FKj3lt_6QXfCXUPeLwNuXG9grYPwfozNnX-A3L0_Y6Qe-lZPlHsB8E3i9WAWQyvOtsDLwcN4polTpKhIjIVp2I3obBVdQAHPhjM19P0b8bmg6pnWzFz36FVglw7PnWD7lBu2Nr/s355/hack%20pic%202.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="354" data-original-width="355" height="319" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoCdkiZGdcusJ_23dkta17-yJpQBTuQO4d8ajxXK-4Kt4cpBLac0FKj3lt_6QXfCXUPeLwNuXG9grYPwfozNnX-A3L0_Y6Qe-lZPlHsB8E3i9WAWQyvOtsDLwcN4polTpKhIjIVp2I3obBVdQAHPhjM19P0b8bmg6pnWzFz36FVglw7PnWD7lBu2Nr/w320-h319/hack%20pic%202.png" width="320" /></a></div><br /><div><div><br /></div><div>The "Data4Good" challenge focuses on leveraging the power of data to drive positive change. With the proliferation of data in our digital age, there is a tremendous opportunity to extract insights and make informed decisions that benefit society. To address critical issues such as participants will work with diverse datasets, apply data analytics, visualization, and machine learning techniques to address critical issues. Participants will be encouraged to explore how data can be ethically collected, analyzed, and utilized to address pressing global issues. <br /><br />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.</div><div><br /></div><div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgugeaikWy5l9RbeqDqLU8XWqSlpdEjsZb_JRUFq5qeajf6q4XvGuifC7YyThlz2lRJNNorG8XuNJQ6B3N1Njh2NoWutM1MSo5GexONCEdhbz_WugBhbkZof3jPfoceYEoNE5TO739bujKgsQlghIe24dAgTWze9jrYj0XlFI0vcw1Jig8g6QcG_t_I" style="margin-left: 1em; margin-right: 1em;"><img data-original-height="682" data-original-width="965" height="299" src="https://blogger.googleusercontent.com/img/a/AVvXsEgugeaikWy5l9RbeqDqLU8XWqSlpdEjsZb_JRUFq5qeajf6q4XvGuifC7YyThlz2lRJNNorG8XuNJQ6B3N1Njh2NoWutM1MSo5GexONCEdhbz_WugBhbkZof3jPfoceYEoNE5TO739bujKgsQlghIe24dAgTWze9jrYj0XlFI0vcw1Jig8g6QcG_t_I=w423-h299" width="423" /></a></div><br /><br /></div>The UN World Innovation Day Hack 2023 is made possible through collaboration with Hackmakers, a global community of changemakers and innovators, joins hands with sponsors such as Oracle, AI4Diversity, Polygon, Unstoppable Domains, Buzzy and Wand to support this event. These organizations bring their expertise, resources, and mentorship to provide participants with the necessary tools to transform their ideas into tangible solutions. <br /><br />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. <br /><br />To find answers to common queries and gain a better understanding of the hackathon logistics visit the official hackathon FAQ page at <a href="https://www.worldinnovationday.com/faq">https://www.worldinnovationday.com/faq</a> . <br /><br />To access valuable resources and guides visit resources page at <a href="https://www.worldinnovationday.com/resources">https://www.worldinnovationday.com/resources</a>. It will provide necessary tools and knowledge to overcome challenges and push the boundaries of innovation. <br /><br />To access Challenge and Sponsored resources details, visit the challenge resource page <br /><a href="https://www.worldinnovationday.com/challenge-resources">https://www.worldinnovationday.com/challenge-resources</a>. <br /><br />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. <br /><br /><b>Conclusion:</b> 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.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjq0W5h76nCvCn_R-2ZSBaU_fV-InVGd0StDS69rktHckIPsfSc73hgS3PENp4tq5Y7KBn7m_BvfjfxLMW_0J6kW6rdYEBpykWqWnACOiX96mO2yqBJjxsD4bz3ui1JOcgtC8R7wenLSClUiL8Jz6SZ10Kjt1pxLHXB94rIvlmf1a84PjRJiI-IjD-1/s624/hack%20pick%20-mentor%204.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="353" data-original-width="624" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjq0W5h76nCvCn_R-2ZSBaU_fV-InVGd0StDS69rktHckIPsfSc73hgS3PENp4tq5Y7KBn7m_BvfjfxLMW_0J6kW6rdYEBpykWqWnACOiX96mO2yqBJjxsD4bz3ui1JOcgtC8R7wenLSClUiL8Jz6SZ10Kjt1pxLHXB94rIvlmf1a84PjRJiI-IjD-1/w475-h268/hack%20pick%20-mentor%204.png" width="475" /></a></div><div><br />#WorldInnovationDay #Hackathon #GptForGood #DataForGood #Innovation #Collaboration<br /> </div><div><br /></div><div>Regards</div><div>Satishbabu G, Oracle ACE Pro</div><div><a href="https://oracleracexpert.com">https://oracleracexpert.com</a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><br /></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-90745711872412124962023-03-02T19:22:00.002-08:002023-03-02T19:23:13.298-08:00Oracle AutoUpgrade with source and Target Database on different servers AutoUpgrade utility can be used on upgrading databases from 12c R2 release (12.2 + DBJAN2019RU and newer) and it has Analyze, Fixup, Deploy and Upgrade processing modes. <div><br /></div><div>Before you proceed make sure you meet following requirements. <br /><ul style="text-align: left;"><li>create config file</li><li>Source Database should be running in original oracle home</li><li>The Database server should be registered in DNS</li><li>JAVA8 required for Autoupgrade to run. Oracle 12c R2 (12.2.0.1) or newer oracle homes have a java version by default.</li><li>If you run AutoUpgrade in batch or script mode use “noconsole” parameter</li></ul>From Oracle 19c (10.3) autoupgrade.jar file exists by default, for earlier releases you must download latest autoupgrade.jar file from Oracle support. <br /><br />There are four AutoUpgrade modes and each mode performs different steps<div><ul style="text-align: left;"><li> Analyze Mode: Setup, Pre-checks.</li><li>Fixups Mode: Setup, Pre-checks, and Pre-fixups.</li><li>Deploy Mode: Setup, Guaranteed Restore Point, Pre-upgrade, Pre-checks, Pre-fixups, Drain, Database Upgrade, Post-checks, Post-fixups, and Post-upgrade.</li><li>Upgrade Mode: Setup, Database Upgrade, Post-checks, and Post-fixups.</li></ul><b>Analyze Mode:</b> This mode runs few checks to see weather database is ready for upgrade. In this mode it only reads data from database to analyze and does not perform any changes. <br /><br />You can run this step on source database before you setup target. <br /><br />Run below command to start AutoUpgrade Analyze <br /><br /><i>$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze <br /></i><br />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 <br /><br />Note that this mode also generates status files such as status.json, progress.json which can be located under cfgtoollogs/upgrade/auto/status. <br /><br />Status.json – This file contains High level status of upgrade. <br />Progress.json – This file has contains status of progress of all upgrades <br /><br />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. <br /><br />For example, <br />upgrade1.source_home=/home/oracle/product/12.2.0.1/dbhome_1 # Source ORACLE_HOME Path <br /> upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1 # Target ORACLE_HOME Path <br /><br /><b>Fixups Mode :</b> This mode performs all required automated fix up that are required before you start an upgrade. In this mode it creates guaranteed restore point. <br /><br />Run below command on source Database server to run fixups. <br /><br /><i>$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode fixups <br /></i><br /><b>Deploy Mode:</b> This mode performs all required actions for an upgrade, which include analyze, fixups, upgrade and post upgrade steps. You will use this mode <u>when source and target Oracle Home’s are on the same server. </u> This mode also creates guaranteed restore point. <br /><br />Run below command on source Database server to run fixups. <br /><br /><i>$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode Deploy <br /></i><br /><u>Move the database from source to Target Database server and perform Upgrade mode. </u><br /><br /><b>Upgrade Mode</b>: 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. <br /><br />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. <br /><br />SQL> create spfile from pfile=' /home/oracle/autoupgrade/au21/TESTDB/temp/during_upgrade_pfile_testdb.ora'; <br /><br />On Target database server start the database in upgrade mode and run below command to upgrade <br /><br /><i>$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode upgrade <br /></i><br />After upgrade make sure you perform post upgrade steps manually <br /><br />· Copy network files sql.net ora, listener,ora, tnsnames.ora files..etc <br />· Restart of database in case of RAC <br />· Remove guaranteed restore point <br /></div><br />Thanks & Regards,<br />https://oracleracexpert.com, Oracle ACE</div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-14882116562191259752023-02-22T14:54:00.003-08:002023-02-22T14:54:51.189-08:00Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize classWhen launching Database configuration assistant (DBCA) you may encounter this error. The “java.lang.NoClassDefFoundError” error encounter when it could not initialize class “sun.awt.X11.XToolkit”. <div><br /></div><div>This normally occur when launching Graphic user interface (GUI) <br /><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>No protocol specified</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.lang.Class.forName0(Native Method)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.lang.Class.forName(Class.java:264)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.awt.Toolkit$2.run(Toolkit.java:860)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.awt.Toolkit$2.run(Toolkit.java:855)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.security.AccessController.doPrivileged(Native Method)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:854)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at com.jgoodies.looks.LookUtils.<clinit>(LookUtils.java:249)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAndFeel.java:135)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.lang.Class.forName0(Native Method)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at java.lang.Class.forName(Class.java:348)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1879)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at oracle.install.commons.util.Application.startup(Application.java:976)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at oracle.assistants.common.base.driver.AssistantApplication.startup(AssistantApplication.java:336)</i></b><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"></blockquote><b><i>at oracle.assistants.dbca.driver.DBConfigurator.startup(DBConfigurator.java:378)</i></b><br /><b><i>at oracle.assistants.dbca.driver.DBConfigurator.main(DBConfigurator.java:513) <br /></i></b><br />The main causes of the issue is <br />1. The DISPLAY variable is NOT set or value is not correct. <br />2. X Windows is not installed <br />3. Oracle user not added to ACL (Access Control List) <br /><br />If launching from server make sure you have any X Windows software installed and install org-x11-apps.x86_64 package <br /><br />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 <br /><br />CSH <br />$ setenv DISPLAY <IP Address of workstation or server>:0 <br /><br />SSH or KSH <br />$ export DISPLAY=<IP Address of workstation or server>:0 <br /><br />In Some cases, users may face issue when installing Oracle software on Ubuntu or Linux environment. <br /> <br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><b><i>>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<</i></b></blockquote><b><i><br /></i></b><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><b><i>Some requirement checks failed. You must fulfill these requirements before</i></b></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><b><i>continuing with the installation,</i></b></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><b><i>Continue? (y/n) [n] Y</i></b></blockquote><b><i><br /></i></b><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><b><i>>>> Ignoring required pre-requisite failures. Continuing...<br /></i></b><b><i>Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-01_12-17-01PM. Please wait ....oracle$:~$ No protocol specified<br /></i></b><b><i>Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit</i></b></blockquote> <br />In this scenario, verify weather JDK installed on the system or not using below command <br />$ java -version<br /> <br />If not installed then installing required JDK will resolve the issue. If the problem persists, then run below command by logging as root<br /> <br />$ xhost +<br /> <br />Switch back to Oracle user and run the installation.<br /> <br />Thanks,<div>https://oracleracexpert.com, Oracle ACE Pro<br /> </div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-61122028502533460622023-02-16T20:27:00.014-08:002023-02-16T20:39:06.735-08:00Upgrade Oracle Database using AutoUpgrade Utility Autoupgrade is used to upgrade one or more databases using command line. Using the tool, you can run pre-upgrade tasks that will provide the fixups, all recommended fixups must be executed before upgrading the database and finish the upgrade by running the post-upgrade tasks. By using this utility you can upgrade hundreds of databases with one command. <br /><br />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. <br /><br />AutoUpgrade utility can be used on upgrading databases from 12c R2 release (12.2 + DBJAN2019RU and newer) <br /><br />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. <br /><br />Oracle home must contain JAVA to use AutoUpgrade Tool. Always download latest AutoUpgrade.jar file <br /><br />Create a sample config.txt file for database ugrade <br /><br /><div><i><span style="font-size: x-small;"><b>upgrade1.sid=TESTDB # ORACLE_SID of source DB <br />upgrade1.run_utlrp=no # Optional. To run utlrp after upgrade select YES <br />upgrade1.timezone_upg=no # Optional. To run the timezone upgrade select YES <br />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] <br />upgrade1.upgrade_node=localhost # Optional. Default is 'localhost' <br />upgrade1.target_version=19 # Only required if target database version is 12.2</b></span></i></div><div><i><span style="font-size: x-small;"><b></b></span></i><i><span style="font-size: x-small;"><b>upgrade1.log_dir= /home/oracle/cfgtoollogs/autoupgrade/TESTDB # Log dir path for the upgrade job</b></span></i><div><i><span style="font-size: x-small;"><b>upgrade1.source_home=/home/oracle/product/12.2.0.1/dbhome_1 # Source ORACLE_HOME Path <br />upgrade1.target_home=/home/oracle/product/19.0.0.0/dbhome_1 # Target ORACLE_HOME Path </b></span></i></div><br />AutoUpgrade with source and Target Database on same server <br /><ul style="text-align: left;"><li>Run below command to start AutoUpgrade Analyze </li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><i><b>$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode analyze </b></i><i><br /></i><i>AutoUpgrade utility launched with default options<br /></i><i>Processing config file ...<br /></i><i>+--------------------------------+<br /></i><i>| Starting AutoUpgrade execution |<br /></i><i>+--------------------------------+<br /></i><i>1 databases will be analyzed<br /></i><i>Type 'help' to list console commands<br /></i><i>upg> Job 100 completed<br /></i><i>------------------- Final Summary --------------------<br /></i><i>Number of databases [ 1 ]</i><i><br /></i><i>Jobs finished [1]<br /></i><i>Jobs failed [0]<br /></i><i>Jobs pending [0]<br /></i><i>------------------- JOBS FINISHED SUCCESSFULLY --------------------<br /></i><i>Job 100 for TESTDB </i></blockquote> <br />All the logs are under the job id . Pls review all the logs for any errors and information. <br /><br />You can see the warnings and errors in html file.<div><br /></div><div> <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3rUIKFqs4Yt589GdmClfvLIpeUEXLKf34UZA4CsBUGVK1oZa1dSnn15QyCTAetoamMFiKphilJCtmdtpPSBuadUc1KmgegUPh_WdOBAOF6nfxccB118oP9tz7KoypS8MPLMzYFy4mDD9hoOgNCxfZOhuaPfEtSAaoedouYUzu2L9Nb2BC3pOJeVeL/s624/pic1.png" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="279" data-original-width="624" height="286" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3rUIKFqs4Yt589GdmClfvLIpeUEXLKf34UZA4CsBUGVK1oZa1dSnn15QyCTAetoamMFiKphilJCtmdtpPSBuadUc1KmgegUPh_WdOBAOF6nfxccB118oP9tz7KoypS8MPLMzYFy4mDD9hoOgNCxfZOhuaPfEtSAaoedouYUzu2L9Nb2BC3pOJeVeL/w640-h286/pic1.png" width="640" /></a><br /><br /><div><ul style="text-align: left;"><li>Run below command to start deployment of upgrade on same server</li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div style="text-align: left;"><b>$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar – config /home/oracle/autoupgrade/config.txt -mode deploy</b></div></div></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div style="text-align: left;"><div><br /></div></div><div style="text-align: left;"><div>AutoUpgrade utility launched with default options</div></div><div style="text-align: left;"><div>Processing config file ...</div></div><div style="text-align: left;"><div>+--------------------------------+</div></div><div style="text-align: left;"><div>| Starting AutoUpgrade execution |</div></div><div style="text-align: left;"><div>+--------------------------------+</div></div><div style="text-align: left;"><div>1 databases will be processed</div></div><div style="text-align: left;"><div>Type 'help' to list console commands</div></div><div style="text-align: left;"><div>upg></div></div></blockquote><div style="text-align: left;"><div><br /> <br /></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div style="text-align: left;"><div><b>upg> lsj</b></div></div><div style="text-align: left;"><div><i>+----+-------+---------+---------+-------+--------------+--------+---------------+</i></div></div><div style="text-align: left;"><div><i>|Job#|DB_NAME| STAGE|OPERATION| STATUS| START_TIME| UPDATED| MESSAGE|</i></div></div><div style="text-align: left;"><div><i>+----+-------+---------+---------+-------+--------------+--------+---------------+</i></div></div><div style="text-align: left;"><div><i>| 102| TESTDB|DBUPGRADE|EXECUTING|RUNNING|21/05/12 15:03|15:35:22|95% Upgraded |</i></div></div><div style="text-align: left;"><div><i>+----+-------+---------+---------+-------+--------------+--------+---------------+</i></div></div><div style="text-align: left;"><div><i>Total jobs 1</i></div></div></blockquote><div style="text-align: left;"><div><br />Once upgrade is completed verify upg_summary.log for any errors <br /><br /></div></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div style="text-align: left;"><div><i>/u01/home/oracle:DBA>cat upg_summary.log </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Database Release 19 Post-Upgrade Status Utility 04-30-2021 11:07:0 </i></div></div></div><div><div style="text-align: left;"><div><i>Database Name: TESTDB </i></div></div></div><div><div style="text-align: left;"><div><i>Component Current Full Elapsed Time </i></div></div></div><div><div style="text-align: left;"><div><i>Name Status Version HH:MM:SS </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Server UPGRADED 19.10.0.0.0 00:09:54 </i></div></div></div><div><div style="text-align: left;"><div><i>JServer JAVA Virtual Machine VALID 19.10.0.0.0 00:01:12 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle XDK UPGRADED 19.10.0.0.0 00:00:38 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Database Java Packages UPGRADED 19.10.0.0.0 00:00:05 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Text UPGRADED 19.10.0.0.0 00:00:23 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Workspace Manager UPGRADED 19.10.0.0.0 00:00:22 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Real Application Clusters OPTION OFF 19.10.0.0.0 00:00:00 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle XML Database UPGRADED 19.10.0.0.0 00:00:51 </i></div></div></div><div><div style="text-align: left;"><div><i>Oracle Multimedia UPGRADED 19.10.0.0.0 00:01:47 </i></div></div></div><div><div style="text-align: left;"><div><i>Datapatch 00:01:41 </i></div></div></div><div><div style="text-align: left;"><div><i>Final Actions 00:01:44 </i></div></div></div><div><div style="text-align: left;"><div><i>Post Upgrade 00:00:16 </i></div></div></div></blockquote><div><div style="text-align: left;"><div style="text-align: left;"><i><br /></i></div></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div style="text-align: left;"><div style="text-align: left;"><i>Total Upgrade Time: 00:17:31 </i></div></div></div></blockquote><div><div style="text-align: left;"><div style="text-align: left;"><i><br /></i></div></div></div><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div><div style="text-align: left;"><div style="text-align: left;"><i>Database time zone version is 26. It is older than current release time </i></div></div></div></blockquote><div><div style="text-align: left;"><div style="text-align: left;"><i> zone version 32. Time zone upgrade is needed using the DBMS_DST package. <br /><br /> Grand Total Upgrade Time: [0d:0h:20m:20s] </i><br /><br />If you select upgrade1.timezone_upg=YES then Time zone will be upgraded as part of the Database upgrade. <br /> <br /><u>AutoUpgrade Stages </u><br /><br />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. <br /><br />AutoUpgrade has the following stages: </div><div><ul style="text-align: left;"><li>SETUP: The initial stage in which it starts the job. </li><li>PREUPGRADE: The stage in which it performs readiness for upgrade such as sufficient space </li><li>PRECHECKS: The stage in which it performs pre-checks on source Oracle home to meet requirements for upgrade. </li><li>GRP: Backup database using guaranteed restore point (GRP) before upgrade, this option only available in Enterprise Edition only </li><li>PREFIXUPS: The stage in which it performs preupgrade fixups before upgrade </li><li>DRAIN: The stage during which it shuts down the database to release resources. </li><li>DBUPGRADE: The stage in which it performs the actual upgrade. </li><li>POSTCHECKS: The stage in which it performs post upgrade checks on the target Oracle home before executing any postupgrade fixups. </li><li>POSTFIXUPS: The stage in which it performs processing of postupgrade fixups, timezone upgrade is part of this step. </li><li>POSTUPGRADE: The stage in which in copies or merges the required files from to the target Oracle home for ex:- listener, tns files </li></ul>There are 2 operational modes during Autoupgrade, i.e. PREPARING and EXECUTING <br /><br />There are 4 state messages<br /></div><div><ul style="text-align: left;"><li>RUNNING – AutoUpgrade is still running </li><li>FINISHED - AutoUpgrade is successfully completed </li><li>ERROR – AutoUpgrade has some Errors </li><li>ABORTED - AutoUpgrade aborted response to user request.</li></ul><u>Refer useful oracle support notes </u><br /><ul style="text-align: left;"><li>Database Server Upgrade/Downgrade Compatibility Matrix (Doc ID 551141.1) </li><li><a href="file:///C:/Users/SatishbabuG/Box/01.%20My%20Personal%20Folder/SatishbabuG/Intuitive/Personal/MyBL/2023/Feb/%E2%80%A2%09https:/docs.oracle.com/en/database/oracle/oracle-database/21/upgrd/using-autoupgrade-oracle-database-upgrades.html#GUID-71883C8C-7A34-4E93-8955-040CB04F2109">Using AutoUpgrade for Oracle Database Upgrades</a></li></ul>Thanks & Regards,<br />https://oracleracexpert.com, Oracle ACE</div></div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-22723096533495824202023-02-16T14:10:00.005-08:002023-02-16T20:31:57.408-08:00Create, Drop, Alter Blockchain tables in Oracle 21cBlockchain tables are designed to allow insert operations only, updates and any modifications are not allowed, and delete operations are restricted. The rows are organized into chains by storing previous row’s hash values in the current row and chain of rows is verifiable by all participants. 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 <br /><br /><b><u>Create and Drop Blockchain table <br /></u></b><br />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 . <br /><br />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. <br /><br /><i>SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE) <br />NO DROP UNTIL 10 DAYS IDLE <br />NO DELETE UNTIL 15 DAYS AFTER INSERT <br />HASHING USING "SHA2_512" VERSION "v1"; </i><br /><br /><u>Table level clauses</u><br /><ul style="text-align: left;"><li>NO DROP – The table cannot be dropped. </li></ul><div><ul style="text-align: left;"><li>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</li></ul>Where X is the number of days. <br /><br /><u>Row level clauses <br /></u><ul style="text-align: left;"><li>NO DELETE or NO DELETE LOCKED – The Rows cannot be deleted. </li></ul><div><ul style="text-align: left;"><li>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. </li></ul><div><ul style="text-align: left;"><li>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</li></ul><u>Create partition on Blockchain table <br /></u><br />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. <br /><br /><i>SQL> CREATE BLOCKCHAIN TABLE Blockchain_T1 (Col1 NUMBER, Col2 VARCHAR2(48), Col3 DATE) <br />NO DROP UNTIL 10 DAYS IDLE <br />NO DELETE UNTIL 15 DAYS AFTER INSERT <br />HASHING USING "SHA2_512" VERSION "v1" <br />PARTITION BY RANGE(Col3) <br />(PARTITION p1 VALUES LESS THAN (TO_DATE('01-31-2022','mm-dd-yyyy')), <br />PARTITION p2 VALUES LESS THAN (TO_DATE('02-28-2022','mm-dd-yyyy')), <br />PARTITION p3 VALUES LESS THAN (TO_DATE('03-31-2022','mm-dd-yyyy')) <br />); </i><br /><br />You can query USER_TAB_COLS for Blockchain Table details</div><div><br /><i>SQL> SELECT internal_column_id as colid<br /> column_name<br /> data_type,<br /> data_length,<br />FROM user_tab_cols <br />WHERE table_name = 'BLOCKCHAIN_T1'<br />ORDER BY colid;<br /><br />COLID COLUMN_NAME DATA_TYPE DATA_LENGTH <br />---------- ------------------------ ------------------------------ ----------- <br /><br /> 1 Col1 NUMBER 22 <br />2 Col2_ VARCHAR2(48) 48 <br />3 Col3T DATE 7 <br />4 ORABCTAB_INST_ID$ NUMBER 22 <br />5 ORABCTAB_CHAIN_ID$ NUMBER 22 <br />6 ORABCTAB_SEQ_NUM$ NUMBER 22 <br />7 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13 <br />8 ORABCTAB_USER_NUMBER$ NUMBER 22 <br />9 ORABCTAB_HASH$ RAW 2000 <br />10 ORABCTAB_SIGNATURE$ RAW 2000 <br />11 ORABCTAB_SIGNATURE_ALG$ NUMBER 22 <br />12 ORABCTAB_SIGNATURE_CERT$ RAW 16 <br />13 ORABCTAB_SPARE$ RAW 2000 </i><br /><br /></div><div>13 rows selected. <br /><br />You can query {CDB|DBA|ALL|USER}_BLOCKCHAIN_TABLES views to get information about Blockchain Tables <br /><br /><u>DROP </u>- The below example drops the table if the table has not modified for retention period defined in the Blockchain creation. <br /><br /></div><div>SQL> DROP TABLE Blockchain_T1 PURGE;<br /> <br />It is recommended to use PURGE option when dropping a Blockchain table.</div><div> <br />Note that Blockchain tables cannot be create the root container and application root container.</div><div><br /></div><div> In below example Blockchain_T2 table creation failed as it cannot be created in root container</div><div><br /></div><div><i>SQL> CREATE BLOCKCHAIN TABLE Blockchain_T2 (Col4 NUMBER, Col2 VARCHAR2(48), Col3 DATE) <br />NO DROP UNTIL 10 DAYS IDLE <br />NO DELETE LOCKED <br />HASHING USING "SHA2_512" VERSION "v1"; </i><br /><br /></div><div>Error report - <br /><span style="color: red;">ORA-05729: blockchain table cannot be created in root container </span><br /><br /><b><u>ALTER Blockchain Tables : </u></b>The Blockchain table retention can be modified using ALTER TABLE command <br /><br />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. <br /><br />SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 21 DAYS IDLE; <br /><br />In below example trying to lower retention for Blockchain_T1 table to 16 and the operation failed as retention value cannot be lowered. <br /><br />SQL> ALTER TABLE Blockchain_T1 NO DROP UNTIL 16 DAYS IDLE; <br /><br /></div><div>Error report - <br /><span style="color: red;">ORA-05732: retention value cannot be lowered <br /></span><br />You can also increase column length but cannot add or drop column in Blockchain tables. <br />SQL> ALTER TABLE Blockchain_T1 MODIFY (COL2 VARCHAR2(58));</div><div><br />Table BLOCKCHAIN_T1 altered.<br /> <br /><b>ADD column</b> </div><div>SQL> ALTER TABLE Blockchain_T1 ADD (Col4 varchar2(32));<br /> <br />Error report -<br /><span style="color: red;">ORA-05715: operation not allowed on the blockchain table</span><br /> <br /><b>DROP column</b> <br />SQL> ALTER TABLE Blockchain_T1 DROP column Col2;<br /> <br />Error report -<br /><span style="color: red;">ORA-05715: operation not allowed on the blockchain table</span><br /> <br /><b><u>DDL and DML on Block chain <br /></u></b><br />In below example we are trying to DELETE, TRUNCATE, UPDATE and MOVE the operation not allowed<br /> <br /><b>DELETE Table</b></div><div>SQL> DELETE FROM Blockchain_T1 where Col1 = 1;<br /> <br />Error report -<br /><span style="color: red;">SQL Error: ORA-05715: operation not allowed on the blockchain table</span><br /><br /></div><div><b>TRUNCATE Table</b></div><div>SQL> TRUNCATE TABLE Blockchain_T1;<br /> <br />Error report -<br /><span style="color: red;">ORA-05715: operation not allowed on the blockchain table<br /></span><br /></div><div><b>UPDATE Table</b></div><div>SQL> UPDATE Blockchain_T1 SET Col2=“Test” WHERE id = 1;<br /> <br />Error report -<br /><span style="color: red;">SQL Error: ORA-05715: operation not allowed on the blockchain table</span><br /> <br /><b>DROP TABLE</b></div><div>SQL> DROP TABLE Blockchain_T1; <br /><br /></div><div>Error report - <br /><span style="color: red;">ORA-05723: drop blockchain table BLOCKCHAIN_T1 not allowed <br /></span><br /><b>MOVE Table<br /></b>SQL> ALTER TABLE Blockchain_T1 move tablespace Blockchain_TBS2 ; <br /><br />Error report - <br /><span style="color: red;">ORA-05715: operation not allowed on the blockchain table <br /></span><br />Pls refer <a href="https://www.oracleracexpert.com/2023/02/blockchain-tables-in-oracle-21c.html">Restrictions for Blockchain tables</a> for more details.</div><div><br /></div>Thanks & Regards<br />https://oracleracexpert.com, Oracle ACE</div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-26887501615581517832023-02-15T19:56:00.009-08:002023-02-15T20:04:11.333-08:00BlockChain Tables in Oracle 21cBlockchain tables are designed to allow insert operations only, updates and any modifications are not allowed, and delete operations are restricted. The rows are organized into chains by storing previous row’s hash values in the current row and chain of rows is verifiable by all participants.<br /><br />Oracle 19c introduced <a href="https://www.oracleracexpert.com/2021/04/immutable-tables-in-oracle-19c-or-21c.html">Immutable tables</a> 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. <br /><br />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. <br /><br />Blockchain tables and regular tables can be used in queries and transactions, also you can create indexes and partitions.<div><br /></div><div><b><u>Blockchain tables hidden columns</u></b></div><div><u><b><br /></b></u></div><div><table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="background: #FCFBFA; border-collapse: collapse; border: none; margin-left: 26.25pt; mso-border-alt: solid #ECEAE5 1.5pt; mso-padding-alt: 3.0pt 3.0pt 3.0pt 3.0pt; mso-yfti-tbllook: 1184; width: 102%px;">
<thead>
<tr style="height: 28.5pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="background: #F8F7F4; border: solid #ECEAE5 1.5pt; height: 28.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="bottom" width="44%">
<p class="MsoNoSpacing"><b><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Column Name<o:p></o:p></span></b></p>
</td>
<td style="background: #F8F7F4; border-left: none; border: solid #ECEAE5 1.5pt; height: 28.5pt; mso-border-left-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="bottom" width="21%">
<p class="MsoNoSpacing"><b><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Data Type<o:p></o:p></span></b></p>
</td>
<td style="background: #F8F7F4; border-left: none; border: solid #ECEAE5 1.5pt; height: 28.5pt; mso-border-left-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="bottom" width="33%">
<p class="MsoNoSpacing"><b><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Description<o:p></o:p></span></b></p>
</td>
</tr>
</thead>
<tbody><tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_INST_ID$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">NUMBER (22)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Instance ID of the
database instance into which the row is inserted.<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_CHAIN_ID$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">NUMBER (22)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Chain ID of the
chain, in the database instance, into which the row is inserted. 0 through 31 are valid values. <o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_SEQ_NUM$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">NUMBER(22)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Sequence number of
the row on the chain<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_CREATION_TIME$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">TIMESTAMP WITH TIME
ZONE</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Row created time in
UTC format<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_USER_NUMBER$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">NUMBER (22)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Database User ID who
inserted the row.<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_HASH$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">RAW(2000)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Hash value of the
row<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_SIGNATURE$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">RAW(2000)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">User signature of
the row<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_SIGNATURE_ALG$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">NUMBER(22)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Signature algorithm
used to produce the user signature of a signed row.<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_SIGNATURE_CERT$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">RAW(16)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">GUID of the
certificate associated with the signature on a signed row.<o:p></o:p></span></p>
</td>
</tr>
<tr>
<td style="background: white; border-top: none; border: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 44.34%;" valign="top" width="44%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">ORABCTAB_SPARE$</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 21.7%;" valign="top" width="21%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Courier New"; font-size: 10.0pt; mso-fareast-font-family: "Times New Roman";">RAW(2000)</span><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";"><o:p></o:p></span></p>
</td>
<td style="background: white; border-bottom: solid #ECEAE5 1.5pt; border-left: none; border-right: solid #ECEAE5 1.5pt; border-top: none; mso-border-left-alt: solid #ECEAE5 1.5pt; mso-border-top-alt: solid #ECEAE5 1.5pt; padding: 7.5pt 7.5pt 7.5pt 7.5pt; width: 33.96%;" valign="top" width="33%">
<p class="MsoNoSpacing"><span style="color: #1a1816; font-family: "Segoe UI",sans-serif; font-size: 10.5pt; mso-fareast-font-family: "Times New Roman";">Reserved for future
use.<o:p></o:p></span></p>
</td>
</tr>
</tbody></table><br /></div><div><u><b>Important Guidelines for Blockchain Tables</b></u></div><div>
<p class="MsoListParagraphCxSpFirst"></p><ul style="text-align: left;"><li>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.</li></ul></div><div><ul style="text-align: left;"><li>The SHA2-512 hashing algorithm used to handle hash value</li></ul></div><div><ul style="text-align: left;"><li>In case Oracle Data Guard, to avoid data loss consider using Maximum availability or Maximum protection mode</li></ul></div><div><ul style="text-align: left;"><li>To specify retention period for Blockchain table use “NO DROP” Clause in the CREATE BLOCKCHAIN TABLE statement to specify retention period.</li></ul></div><div><ul style="text-align: left;"><li>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</li></ul><p></p>
<p class="MsoNormal"><a name="OLE_LINK21"><u><b>Restrictions
for Blockchain tables</b><o:p></o:p></u></a></p>
<ul style="text-align: left;"><li>There are many restrictions when using blockchain tables </li></ul><ul style="text-align: left;"><li>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 </li></ul><ul style="text-align: left;"><li>Point-in-time recovery or flashback database will undo changes on all database objects including blockchain tables. </li></ul><ul style="text-align: left;"><li>Blockchain Retention policies depends on system time and any changes to systems time must be audited. </li></ul><ul style="text-align: left;"><li>Max number of user column allowed are 980 </li></ul></div><div><ul style="text-align: left;"><li>Blockchain tables doesn’t allow or support below operations </li></ul></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><u>Column level restrictions </u></div><div>o Adding, dropping, and renaming columns </div><div>o Dropping partitions </div></blockquote><div><br /></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><u>Row level restrictions </u></div><div>o Update or merge rows </div><div>o Defining BEFORE ROW triggers that fire for update operations are not allowed. </div></blockquote><div><br /></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><u>Table level restrictions </u></div><div>o Truncate table </div><div>o Inserting data using parallel DML </div><div>o Sharded tables </div><div>o During distributed transactions, Inserting data into a blockchain table using Active Data Guard DML redirection is not supported </div><div>o Direct-path loading </div><div>o Flashback table </div><div>o Cannot convert a regular table to a blockchain table or vice versa. </div><div>o XA transactions </div></blockquote><div><u><br /></u></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><u>Database level restrictions </u></div><div>o Export and Import can be done as regular tables, without the system-generated hidden columns. </div><div>o Creating blockchain tables in CDB or application root </div><div>o Creating Oracle Label Security (OLS) policies </div><div>o Using the DBMS_REDEFINITION package for Online redefinition </div><div>o Creating Oracle Virtual Private Database (VPD) policies </div><div>o When using Transient Logical Standby and rolling upgrades, the DDL and DML on blockchain tables are not replicated and supported </div><div>o 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 databases </div><div>o Creating Automatic Data Optimization (ADO) policies </div></blockquote><div><br /> </div><div>Thanks & Regards</div><div>https://oracleracexpert.com, Oracle ACE</div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-12640444433316684162023-01-30T13:59:00.000-08:002023-02-16T15:36:36.181-08:00DBMS_BLOCKCHAIN_TABLE package in Oracle 21cYou can use DBMS_BLOCKCHAIN_TABLE package for maintenance of blockchain table. Using this package you can perform the below tasks. <br /><ul style="text-align: left;"><li>Sing a row that is added into the table </li><li>Verify the integrity of rows and hash column. </li><li>Generate signature and signed digest </li><li>Delete rows in a blockchain table that are beyond retention defined. </li></ul>Here are the few subprograms (procedures and functions) used with DBMS_BLOCKCHAIN_TABLE package<br /><ul style="text-align: left;"><li>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 </li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;">DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(<br /> schema_name IN VARCHAR2,<br /> table_name IN VARCHAR2,<br /> low_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,<br /> high_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,<br /> instance_id IN NUMBER DEFAULT NULL,<br /> chain_id IN NUMBER DEFAULT NULL,<br /> number_of_rows_verified OUT NUMBER,<br /> verify_signature IN BOOLEAN DEFAULT TRUE); </blockquote><br /><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;">For ex:-</blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;">DECLARE</blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"> Rows_Verify NUMBER;<br /> DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS(<br /> schema_name => 'BCUSER',<br /> table_name => 'BLOCKCHAIN_T1',<br /> number_of_rows_verified => Rows_Verify);<br /> DBMS_OUTPUT.PUT_LINE(TO_CHAR(Rows_Verify) || ' rows deleted');<br /> END;<br /> / </blockquote><ul style="text-align: left;"><li>DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST – By using this procedure you can generate a signature and signed digest. </li></ul><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;">DBMS_BLOCKCHAIN_TABLE.GET_SIGNED_BLOCKCHAIN_DIGEST(<br /> schema_name IN VARCHAR2,<br /> table_name IN VARCHAR2,<br /> signed_bytes IN OUT BLOB,<br /> signed_rows_indexes OUT ORABCTAB_ROW_ARRAY_T,<br /> schema_certificate_guid OUT RAW,<br /> signature_algo IN NUMBER default SIGN_ALGO_DEFAULT)<br /> RETURN RAW;</blockquote><div><ul style="text-align: left;"><li>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</li></ul></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>DBMS_BLOCKCHAIN_TABLE.VERIFY_TABLE_BLOCKCHAIN( </div></div><div><div>signed_bytes_latest IN BLOB, </div></div><div><div>signed_bytes_previous IN BLOB, </div></div><div><div>number_of_rows_verified OUT NUMBER); </div></div></blockquote><div><div><br /><ul style="text-align: left;"><li>DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS - By using this procedure you can delete rows that are beyond retention period </li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>For ex:- </div></div><div><div>DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS( </div></div><div><div>schema_name IN VARCHAR2, </div></div><div><div>table_name IN VARCHAR2, </div></div><div><div>before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, </div></div><div><div>number_of_rows_deleted OUT NUMBER); </div></div></blockquote><div><div><br /></div></div><blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;"><div><div style="text-align: left;">Ex:- The below example deletes the rows beyond the retention period, you can limit the number of rows deletion by using before_timestamp </div></div></blockquote><div><div><br /></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>DECLARE</div></div><div><div> Del_Rows NUMBER; </div></div><div><div>DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS( </div></div><div><div>schema_name => 'BCUSER', </div></div><div><div>table_name => 'BLOCKCHAIN_T1', </div></div><div><div>before_timestamp => NULL, </div></div><div><div>number_of_rows_deleted => Del_Rows); </div></div><div><div>DBMS_OUTPUT.PUT_LINE(TO_CHAR(Del_Rows) || ' rows deleted');</div></div><div><div> END;</div></div><div><div> / </div></div></blockquote><div><div><ul style="text-align: left;"><li>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. </li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_SIGNATURE( </div></div><div><div>schema_name IN VARCHAR2, </div></div><div><div>table_name IN VARCHAR2, </div></div><div><div>instance_id IN NUMBER, </div></div><div><div>chain_id IN NUMBER, </div></div><div><div>sequence_id IN NUMBER, </div></div><div><div>data_format IN NUMBER, </div></div><div><div>row_data IN OUT BLOB);</div></div></blockquote><div><div><ul style="text-align: left;"><li>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 </li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>DBMS_BLOCKCHAIN_TABLE.GET_BYTES_FOR_ROW_HASH( </div></div><div><div>schema_name IN VARCHAR2, </div></div><div><div>table_name IN VARCHAR2, </div></div><div><div>instance_id IN NUMBER, </div></div><div><div>chain_id IN NUMBER, </div></div><div><div>sequence_id IN NUMBER, </div></div><div><div>data_format IN NUMBER, </div></div><div><div>row_data IN OUT BLOB); </div></div></blockquote><div><div><ul style="text-align: left;"><li>DBMS_BLOCKCHAIN_TABLE.SIGN_ROW – By using this procedure you can add a signature to an existing row. </li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div><div>DBMS_BLOCKCHAIN_TABLE.SIGN_ROW( </div></div><div><div>schema_name IN VARCHAR2, </div></div><div><div>table_name IN VARCHAR2, </div></div><div><div>instance_id IN NUMBER, </div></div><div><div>chain_id IN NUMBER, </div></div><div><div>sequence_id IN NUMBER, </div></div><div><div>hash IN RAW DEFAULT NULL, </div></div><div><div>signature IN RAW, </div></div><div><div>certificate_guid IN RAW, </div></div><div><div>signature_algo IN NUMBER); </div></div></blockquote><div><div><ul style="text-align: left;"><li>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 </li></ul><ul style="text-align: left;"><li>Note that DBMS_CRYPTO.HASH function can be used to compute hash value </li></ul><u>Blockchain Data byte values:</u></div><div><u></u><u><br /></u>By using DBMS_TABLE_DATEA package you can get the byte values of row, column <br /></div><div><ul style="text-align: left;"><li>DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMN - By using this procedure column data in bytes</li><li>DBMS_TABLE_DATA.GET_BYTES_FOR_COLUMNS By using this procedure column data in bytes for set of columns</li><li>DBMS_TABLE_DATA.GET_BYTES_FOR_ROW By using this procedure row data in bytes.</li></ul>Thanks & Regards<br />https://oracleracexpert.com, Oracle ACE<br /> </div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-36200796282136385452022-12-12T19:33:00.016-08:002022-12-21T09:39:49.158-08:00Webinar: Oracle Data pump enhancements in Oracle 21cThis Webinar helps you to understand Oracle data pump new features in Oracle 21c and take an advantage of these features. <br /><br />Date and time: Dec 21st 2022 8:00am-9:00am<br /> Pacific Daylight Time (San Francisco, GMT-07:00)<br /> <br /> This Webinar covers following Topics.<ul style="text-align: left;"><li>Introduction to Oracle Data Pump </li><li>CHECKSUM, CHECKSUM_ALGORITHM </li><li>VERIFY_ONLY and VERIFY_CHECKSUM </li><li>INCLUDE and EXCLUDE in the Same Operation </li><li>Index Compression </li><li>Transportable Tablespace Enhancements </li><li>JSON Data Type Support </li><li>Export/Import from Cloud Object Store </li><li>Q&A </li></ul>To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com<div>Please use <a href="https://zoom.us/j/7892015022?pwd=NnRzMFIzdmVWUEFUMitpWlVxNU1jUT09">Zoom Link</a> to join the Webinar. <br /> <br /> Note that registrations are limited and first come and first serve basis.<br /> You will receive an email confirmation with meeting session link.<br /> <br /> For presentation link<a href="https://www.slideshare.net/satishbabugunukula/oracle-data-pump-enhancements-in-oracle-21cpptx"> Click here</a> <br /> <br /> Thanks & Regards,<br /> http://www.oracleracexpert.com</div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com1tag:blogger.com,1999:blog-6850817271941197798.post-36420226744880043212022-11-08T11:30:00.012-08:002022-12-15T11:45:57.679-08:00Physical Standby Recovery or Sync Using Recover from Service from 18cThe <b>RECOVER FROM SERVIC</b>E is used to recover data guard database which is out of sync or lagging standby. Before 12c the user needs to determine the current SCN, take a backup and apply, start managed recovery of standby and but in 12c they automated these steps but user still needs to perform few manual steps such as control file update. <div><br /></div><div>This feature has been improved in Oracle 18c and covers all the steps that DBA used to perform manually <br /><br />Primary : DBPRI <br />Standby: DBSTB <br /><br /><u>Steps if the database is Oracle 18c or above <br /></u><br />1. Check the Data Guard Broker configuration. <br /><br />DGMGRL> show configuration <br />Configuration – dg_config <br />Protection Mode: MaxPerformance <br /><br /></div><div>Members: <br />dbpri - Primary database <br /><b>Error: ORA-16724: cannot resolve gap for one or more members </b><br /><br /> dbstb - Physical standby database <br /><b> Warning: ORA-16809: multiple warnings detected for the member </b><br /><br /> Fast-Start Failover: Disabled <br /><br /> Configuration Status: <br /><b>ERROR (status updated 5 seconds ago) </b><br /><br />We can see that both Primary and Physical standby are out of sync <br /><br />2. Stop Recovery Managed process on the standby <br /><br />DGMGRL> edit database DBSTB set state=APPLY-OFF; <br /><br />Or <br /><br />RMAN> alter database recover managed standby database cancel; <br /><br />3. Run roll forward command <br /><br />$rman target / <br /><br />Recovery Manager: Release 19.0.0.0.0 - Production on Thu Mar 11 12:12:48 202 <br />Version 19.5.0.0.0 <br /><br />Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. <br /><br />connected to target database: DBSTB (DBID=xxxxxxxx) <br />RMAN> recover standby database from service DBPRI <br />Starting recover at 11-MAR-22 <br />using target database control file instead of recovery catalog <br />Oracle instance started……. <br /><br />media recovery complete, elapsed time: 00:00:00 <br />Finished recover at 05-MAR-22 <br />Executing: alter system set standby_file_management=auto <br />Finished recover at 05-MAR-22 <br /><br />4. Enable the Recovery Managed process on the standby <br /><br />DGMGRL> edit database DBSTB set state=APPLY-ON; <br /><br /></div><div>Or <br /><br /></div><div>RMAN> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; <br /><br /><br />5. Verify the Data Guard configuration status <br /><br />DGMGRL> show configuration <br /><br /></div><div>Configuration - dg_config <br />Protection Mode: MaxPerformance <br /><br /> Members: <br />dbpri - Primary database <br /> dbstb - Physical standby database <br /><br />Fast-Start Failover: Disabled<br /><br />Configuration Status: <br /><b>SUCCESS (status updated 56 seconds ago) </b><br /><br />Now standby Database has been recovered and in sync with the primary. <br /><br /> </div><div>Thanks & Regards</div><div><a href="http://oracleracexpert.com">http://oracleracexpert.com</a>, Oracle ACE</div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com0tag:blogger.com,1999:blog-6850817271941197798.post-33854427549790247962022-09-19T13:06:00.005-07:002022-09-19T13:06:00.236-07:00BEA-002936 maximum thread constraint or BEA-000337 [STUCK] ExecuteThread in WebLogicI come across an issue and found below error message in server log file. As per oracle note Doc ID 1356278.1 this message is only informational and can be safely ignored. But our planning application crashed and we had to restart the services.<br /><br /><span style="color: red;"><i>####<Sep 8, 2022 11:06:22 AM PDT> <Info> <WorkManager> <SCLWPA2188> <Planning0> <[ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1662660382282> <BEA-002936> <maximum thread constraint ASYNC_REP_FLUSH_WM is reached> <br /></i><i>####<Sep 8, 2022 11:08:39 AM PDT> <Info> <WorkManager> <SCLWPA2188> <Planning0> <[ACTIVE] ExecuteThread: '31' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <1662660519981> <BEA-002936> <<b>maximum thread constraint</b> ASYNC_REP_FLUSH_WM is reached> </i></span><br /><br />In many cases the error may clear after some time. After research I found that WebLogic Server associates some internal work managers whenever a JMS server is created and below are the parameters <br /><br />weblogic.jms.<JMSServer_NAME>.AsyncPush <br />Min threads = 1, max threads = unlimited, high fair share <br /><br />weblogic.jms.<JMSServer_NAME>.Limited <br />Min threads = 1, max threads = 8, normal fair share. <br /><br />weblogic.jms.<JMSServer_NAME>.System <br />Min threads = 1, max threads = unlimited, high fair share. <br /><br />The work managers will expand up to the max limit and will shrink back when the load returns to normal, however shrinking doesn’t start unless there are few idle state threads.<div><br /></div><div>As you see the work managers have a limited thread count of 8, but this limit is internal and cannot be changed without contacting Oracle support. <br /><br />Users may also see STUCK thread issues <br /><br /><span style="color: red;">Sep 3, 2022 11:23:31 AM EDT> <Error> <WebLogicServer> <abcsystems.orass.com> <server-cc113sp> <[ACTIVE] ExecuteThread: '33' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <> <45443435326649> <BEA-000337> <<b>[STUCK] </b>ExecuteThread: 45 for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for “550" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 678511 ms <br /></span><br />The root cause Thread gets stuck if it is continually working (not idle) for a set period of time. This time is called the StuckThreadMaxTime, and has a default value of 600 seconds. The stuck thread cannot complete its current work or accept any new work the server logs the message. Note that if all threads gets stuck then server health state will change to either “warning” or “critical” <br /><br />The stuck thread errors may disappear after some time if you see these errors frequently then you should start tuning to avoid issues. Note that restart will clear these errors. <br /><br />I would recommend you go to WebLogic configuration and finetune your parameters based upon the current settings such as “Stuck thread max time” and “Stuck Thread Timer Interval” <br /><br />To fine tune the parameter go to weblogic console à Domain à Environment à Server à <Managed Server> à Configuration > Tuning <br /><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto;"><tbody><tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHiPquEz_1SvZsaqyWXFyHJ3fZfwf3jCBixDPaFAPJl9QOn04AbAazJDftZbvzrF1TPx-a0TS7Wm1B_3dukEMPl7OnNp1TxTuSfkWHrLF4NcC06Quurc8Oh7GEZ2lO80_dT68lQ77fityZJShcePGVe6Fu4prtAlvbmC6iphN5nheWNTrwoG-STHbd/s1525/TUNING.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" data-original-height="385" data-original-width="1525" height="162" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHiPquEz_1SvZsaqyWXFyHJ3fZfwf3jCBixDPaFAPJl9QOn04AbAazJDftZbvzrF1TPx-a0TS7Wm1B_3dukEMPl7OnNp1TxTuSfkWHrLF4NcC06Quurc8Oh7GEZ2lO80_dT68lQ77fityZJShcePGVe6Fu4prtAlvbmC6iphN5nheWNTrwoG-STHbd/w640-h162/TUNING.PNG" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"></td></tr></tbody></table><br /><br />Make sure you apply changes and reboot the server for new changes to take into effect. You can also refer below WLS documentation and Metalink note for more details.<div><br /><div>WLS documentation : <a href="https://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/tuning/TuningExecuteThreads.html">https://docs.oracle.com/cd/E23943_01/apirefs.1111/e13952/taskhelp/tuning/TuningExecuteThreads.html</a><br />Metalink Note: <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1584393.1&id=1302472.1">Note 1302472.1</a> - Information about STUCK Threads Condition and Tuning in Weblogic Server</div><div><br /></div><div>Thanks & Regards</div><div>http://oracleracexpert.com, Oracle ACE Pro<br /><br /></div></div>Satishbabu Gunukulahttp://www.blogger.com/profile/02630966486825083489noreply@blogger.com14