tag:blogger.com,1999:blog-39666373125482033392024-03-18T00:27:10.689-07:00Principal Performance Engineering, Database, SQL, PLSQL and Fusion Middleware Learnings15 years 3 months + of experience in database administration, performance engineering and software cost optimization. Expert in architecture of large scale product, service features in product developments and several POCs executions. Specialty in Database administration, Database Performance, SQL, PLSQL, Shell, Simple solutions for Designing Performance engineering solutions, actively working on Software Cost Optimization in CLOUD. Always ready for new challenges with simple solutionsmdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.comBlogger128125tag:blogger.com,1999:blog-3966637312548203339.post-83077404343565271972021-08-04T06:17:00.002-07:002021-08-10T19:21:54.654-07:00Trace scheduler job execution in oracle Scheduler jobs are executed with service name:SYS$USERSbegindbms_scheduler.drop_job ('TEST_JOB1');end;/BEGIN dbms_scheduler.create_job ('TEST_JOB1', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE l_sql VARCHAR2(3999) := ''''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => ''DBMS_SCHEDULER'',action_name => ''CE_STREAM''); mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-76407886465696760322021-07-30T01:31:00.003-07:002021-07-30T01:32:03.557-07:00TOP N PARTITION FROM EACH PARTITION TABLE TOP N PARTITION NAMES FROM EACH PARTITION TABLE IN SCHEMA:SELECT table_name, LISTAGG(partition_name, ' ') WITHIN GROUP( ORDER BY partition_position ) AS partition_namesFROM ( WITH rws AS ( mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-5639636835498927652021-07-19T20:23:00.003-07:002021-08-04T04:45:25.487-07:00How to Enable Trace service name and module, another session in oracleHow to Enable Trace servicename and module, another session:Note:Test case 1 and Test case 2 : Enable /Disable tracing of sessions on combination of Service name and Module nameTest 3: Enable/Disable trace on a particular user based on SID,SERIAL# .Test case 1:======================Session 1:----------------sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1BEGIN mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-1372926434401972652021-02-06T02:12:00.008-08:002021-02-06T02:26:47.997-08:00How to run query with bind variables same as it run from application in oracle databaseHow to run query with bind variables same as it run from application in oracle database
We see queries run from application , we get different sql_id, plan hash value when we take that sql text and rum from command line, sql worksheet or sql developer for those queries that get values at run time in the form of bind variables.
the thing that I am going explain here how can we run a query mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-69877793539351161192020-12-20T20:46:00.004-08:002020-12-20T20:50:13.885-08:00How to change max_string_size in oracle RAC container database and pluggable databases Change max_string_size in oracle RAC container database and pluggable databases:Steps: 1: create a pfile from spfile.2. set max_string_size=EXTENDED 3. set cluster_database=FALSE 4. startup upgrade5. @?/rdbms/admin/utl32k.sql - run it in CDB.6. set cluster_database=TRUE7. Restart CDB with srvctl commands and ensure all the instances are up and running.8. Now mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-26991352882874882322020-12-20T16:59:00.003-08:002020-12-20T17:04:10.010-08:00ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32. and ORA-30094: failed to find the time zone data file for version 34 in $ORACLE_HOME/oracore/zoneinfo Error:ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32.SQL> SELECT version FROM v$timezone_file; VERSION---------- 32conn sys as sysdba SQL> ALTER SESSION SET CONTAINER=mypdb;ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE INSTANCES=ALL;Session mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-31596467273340259742020-12-20T16:43:00.006-08:002020-12-20T16:53:20.986-08:00UPGRADE 19c 2 node RAC cluster from 19.3 to 19.8 with July 2020 RUEnvironment: 19.3 2 node RAC cluster.Latest OPatch: : p6880880_190000_Linux-x86-64.zip -- to get OPatch Version: 12.2.0.1.23GI Release Update (July 2020) : p31305339_190000_Linux-x86-64.zipDatabase Release Update (July 2020) : p31281355_190000_Linux-x86-64.zipRU: Release Updatesfollowed exactly what is given mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-19410917923692160202020-12-03T03:24:00.010-08:002020-12-03T03:28:50.691-08:00Oracle Database schema tables rows count We do come across a requirement or want to know count of each and every table in a particular schema or group of schemas. what is simplest way of doing this?. do we need query each and every table ?.. not required as tables are used for some other DMLs .. . we can create a job or run whenever we want to know the count of each and every table in a particular schema. The code givenmdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-65965563044489943642020-11-28T17:13:00.001-08:002020-11-28T17:14:55.570-08:00ORA-22275: invalid LOB locator specified , do we really have LOB columns in table ORA-22275: invalid LOB locator specified , do we really have LOB columns in table:I came across a customer problem that query returning ORA-22275: invalid LOB locator specified error from application logs when they ran a query that has join conditions with just two tables . none of the tables in the query have LOB columns, it all number, char , varchar2 data type columns.the mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-11575473941124896372020-11-28T16:48:00.008-08:002020-11-28T16:55:43.965-08:00Configuring Local Undo AND Configuring Shared UndoConfiguring Local Undo:Shtudown the instance:SQL> shutdown immediate;SQL> startup upgrade;SQL> show con_nameCON_NAME-----------------------------CDB$ROOTChange the Undo mode to “Local Undo”:SQL> alter database local undo on;Reboot the instance:SQL> shutdown immediate;SQL> startup;Verify that the Local Undo is now used:SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-73234275093772128052020-11-12T16:29:00.006-08:002020-11-12T16:36:19.631-08:00How to export and import schema stats from one database to another database in oracleHow to import schema stats from one database to another database in oracle:It is not just importing schema stats from one database to another database, the one of the problem I have come across is export delay or import delay when export or importing whole schema stats with including statistics. I found this is useful when making my export and imports faster however the solution is FYI only,mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-85806849063964033012020-03-03T01:04:00.001-08:002020-06-07T17:39:18.436-07:00How to truncate reference partitioning table in oracle database
How to truncate reference partitioning table in oracle database
there is no option to truncate reference partitioning tables in oracle database unless FK tables delete rule is set CASCADE. it is tested in 12.2
how do we get rid of PK table while keeping FK table exists , It depends on the way the tables are got created.
Possible ways:
1. use delete
2. drop FK tables, truncate PK tables sincemdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-47528892836411738652020-02-17T22:45:00.002-08:002020-02-17T22:51:51.646-08:00Errors in 19c rac cluster *root.sh script execution during GRID installation.
Errors in 19c rac cluster *root.sh script execution during GRID installation.
1. run(before gird install) runcluvfy.sh stage -pre crsinst -n node1, node2 and ensure output is endup with all r in PASSED state.
Error seen in log file:
2020-02-13 20:12:33: Invoking "/u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -chkckpt -name mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-29225477908408680962020-02-17T21:36:00.001-08:002020-02-17T21:36:35.580-08:00How to connect database using JDBC connect string
How to connect database using JDBC connect string:
As these days, databases are on cloud and at times the only way seems to connect database using JDBC url format.
it is very simple:
scan name:
sqlplus 'user1/user1@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost.mydomain.myurl)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDB19C1)))'
these days scan mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-52925403425856939422020-02-14T21:16:00.000-08:002020-02-14T21:44:05.437-08:00How to Calculate OS CPU Utilization From Oracle Database AWR report.
How to Calculate OS CPU Utilization From Oracle Database AWR report.:
please note: the calculation formulas given here are the formulas i got from various sources on reading AWRs (in process of learning AWR myself).. the credit goes to them who could find time to share their knowledge.. many authors ...many blogs on awr analysis.
TOTAL CPU= NUM_CPUS*ELAPSED_TIME*60
TOTAL CPU= 8*60*60 = 28800 mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-72144489814219637012020-02-13T03:01:00.003-08:002020-02-13T03:01:41.441-08:00[INS-40724] No locally defined network interface matches the SCAN subnet.
Errors while installing oracle 19c cluster - grid software installation..
[INS-40724] No locally defined network interface matches the SCAN subnet.
Issue: none of the locally defined network interfaces has a subnet matching the SCAN subnet.
Fixes:
1. error entry in /etc/hosts file on RAC nodes.
2. defined network interfaces has a subnet matching the SCAN subnet i.e. the Public mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-40248212125097264242020-01-25T07:57:00.001-08:002020-01-25T07:57:54.619-08:00How to check Oracle Flex cluster and Flex ASM
Oracle FLEX ASM: The number of ASM instances which will be running in the cluster based on cardinality defined during the cluster configuration.
Flex Cluster need to be enabled for flex ASM.
The default cardinality is 3, can be changed with srvctl modify asm commands
Clients can connect to remote ASM instance either shared network or separate dedicated asm network.
Useful commands:
mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-32984666618096363722020-01-12T17:01:00.001-08:002020-02-14T21:46:48.803-08:00Plug Single Instance PDB from non ASM CDB to ASM RAC CDB with wallet configuration
Plug Single Instance PDB to RAC CDB
Objective : clone pdb from one cdb to another cdb. DB version : 12.2
our source is non RAC file system CDB, our target is ASM RAC CDB.
One may wonder why did we do unplug and plug in database when we have a simple way of doing pdb clone with database link. yes, doing pdb clone copy with database link is simple but we could not bring PDB across all 4 nodes mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-29601548723248954612018-04-02T18:47:00.002-07:002018-04-02T19:03:49.463-07:00how to fix when a query performed well earlier but suddenly started giving slow response time
how to fix when a query performed well earlier but suddenly started giving slow response time :
how to fix when a query performed well earlier but suddenly started giving slow response time ??
There could be several direct ,indirect causes but most likely change in explain plan.
the first fix one should try is to understand change in explain plan and see if that is the problem or any mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com1tag:blogger.com,1999:blog-3966637312548203339.post-13996793997966939962017-09-14T09:19:00.003-07:002017-09-14T19:09:59.709-07:00Table resize, Index Resize, partition or sub partition table space resize :
Table resize, Index Resize, partition or sub partition table space resize :
Please Note: Take backup of either schema and/or table before doing any changes. No backup means no recovery as simple as that..
Backup is must, backup is must.. Test this in non-prod first - no direct use in prod db.
------------------------- Table Resize --------------
execute mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-58680738187074297422017-09-14T09:16:00.000-07:002017-09-14T09:16:55.865-07:00How to find only nfs shares or nfs drives in linux
How to find only nfs shares or nfs drives in linux
df -Ph -t nfs
mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-80608142108276499382017-08-10T05:26:00.000-07:002017-08-10T05:35:39.065-07:00
How to restore oracle database with no errors:
Note: please take great care before you do backup and restore as incorrect executions are catastrophic. You are responsible for your own actions and no one else.
We all like to restore database when it is required to restore however at times we found difficult to restore due to non availability of backup files, or missing backups etc.
howmdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-1482607255556512472016-12-20T01:22:00.000-08:002016-12-20T01:22:31.079-08:00ORA-12801: error signaled in parallel query server , ORA-27090: Unable to reserve kernel resources
ORA-12801: error signaled in parallel query server , ORA-27090: Unable to reserve kernel resources
ORA-12801: error signaled in parallel query server , ORA-27090: Unable to reserve kernel resources
Problem:
-------------------
ORA-12801: error signaled in parallel query server P006, instance
mdvreddy.markmotels.com:mdvreddy (1)
ORA-27090: Unable to reserve kernel resources for asynchronousmdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0tag:blogger.com,1999:blog-3966637312548203339.post-74680379862628518712016-12-18T23:17:00.000-08:002016-12-19T17:19:15.383-08:00OHS not starting after giving opmnctl startall with error make_sock: could not bind to address xx.x.x:80
OHS not starting after giving opmnctl startall with error make_sock: could not bind to address xx.x.x:80
[2016-12-19T05:51:07.1888+00:00] [OHS] [INCIDENT_ERROR:32] [OHS-9999] [core.c] [host_id: mdvreddy.markmotels.com] [host_addr: xx.100.100.100] [pid: 8256] [tid: 140065001035584] [user: mdvreddy] [VirtualHost: main] (13)Permission denied: make_sock: could not bind to address xx.mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com1tag:blogger.com,1999:blog-3966637312548203339.post-68572569597698399222016-10-13T08:18:00.002-07:002016-10-13T08:31:18.557-07:00Create password verification function in oracle 12c databases
Create password verification function in oracle 12c databases:
Password verification function can be created both in CDB and in PDB level. They are independent from one another.
Here it is the demo.
@?/rdbms/admin/utlpwdmg.sql
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Function created.
Grant succeeded.
Profile altered.
select limit from dba_profiles where mdvreddyhttp://www.blogger.com/profile/06534743339394362524noreply@blogger.com0