Tag Archives: oracle

Cursor Mutex S wait event and too many child cursors in Oracle

Cursor Mutex S wait event and too many child cursors in Oracle

Mutexes are a lighter-weight and more granular concurrency mechanism. If one session is changing a data structure in memory,
then another session must wait to acquire the mutex before it can make a similar change.

The wait event: “Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache. Library cache mutex – X is held in exclusive mode by a session
It is involved, as soon as two sessions try to (hard/soft) parse the same statement in sense of SQL_IDs.
Cursor: mutex S wait occurs when Oracle is serializing parsing of multiple SQL statements.

We upgraded the database on 12.2 Environment from 11g, we are start getting

Oracle Wait: “”Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache.

You can check the child cursor having mainly update or insert then select in version count in AWR report

Top 10 Foreground Events by Total Wait Time

Event             Waits       Total Wait Time (sec)   Avg Wait   % DB time   Wait Class
cursor: mutex X   5,698,887   512.1K                  89.86ms    79.6        Concurrency 
DB CPU                         22.1K                              3.4   
cursor: mutex S   1,616,430    10.7K                   6.62ms     1.7        Concurrency
log file sync     546,786     2030.3                   3.71ms      .3        Commit 

It may be caused of version SQL present in database: ( check awr report for following section)

SQL ordered by Version Count

Version Count  Executions  SQL Id         SQL Module     SQL Text
4,096                      crxk85q87pug9                 SELECT COUNT(:"SYS_B_0") FROM ... 
1,426                      9gm72hfpbn449                 SELECT entity_no, date_capture... 
1,086                      6rkf3uzawjtwk      
659            118         79msc2b6tcv7a  StmtCOM64.exe  SELECT status, page_count, phy... 
608                        9ktt2cdn2cndn                 SELECT COUNT(:"SYS_B_0") FROM ... 
261                        1y4f4wtu63797                 WITH MONITOR_DATA AS (SELECT I... 
161            33          c7v3v03hs7528  w3wp.exe       SELECT IC.Tran.*, rowidtochar(... 

With peak hours, lot of update statement run the child cursor is increasing and causing performance issue.
On checking with v$sql immediately showed an select statement that has more than 1000 child cursors for the same SQL_ID,
it was mostly the culprit sql because creating new child cursors means expensive hard parsing,
and searching the list of existing children means CPU load as well.

You can check the child cursor count from the following queries:

Check Number of child cursor present in database:

select a.cursors, a.sql_id,b.sql_text
from
(
select count(*) as cursors, ssc.sql_id
from v$sql_shared_cursor ssc
group by ssc.sql_id
order by cursors desc
) a,
(
select sa.sql_id, sa.sql_text, sa.parsing_schema_name from v$sqlarea sa
) b
where a.sql_id=b.sql_id and b.parsing_schema_name = 'SCOTT';

The number of child cursors and the appearance of the “Cursor: mutex S” wait event is closely related,
a stable and well-working workaround was to flush out the particular cursor or flush out the shared pool
but shared pool flush sometime is expensive in Production environment.

Solution

1. Set the following paremeter may fixed the issue:

alter system set "_cursor_obsolete_threshold"=1024

Temporary solution
Scheduled a job in dbms_scheduler, running every 1 minute, executing dbms_shared_pool.purge for flushing the sql id cursors:

DECLARE
SQ_ADD VARCHAR2(100) := '';
BEGIN
execute immediate 'select address from v$sqlarea where sql_id = ''someSQLIDfoo''' into SQ_ADD;
dbms_shared_pool.purge (SQ_ADD||',123454321','C');
END;

3. Flush the shared pool if you are having large no of sql having multiple version count.

alter system flush shared_pool;

In my case 12.2 oracle, optimizer parameter disable fixed the issue. It reduced the version count sqls in awr report from next day.
May be the issue with optimizer adaptive plans in 12c, when i updated this parameter to false, then my version count is going down.
adaptive cursor sharing is playing.

alter system set optimizer_adaptive_plans=false scope=both;

Explain for child cursor:
On tracing the sql queries using bind variable at level 10046 traces, level 12.

found that the bind varialbe is treated as different in some case NULL value, some as varchar2 or in some as number data type.
if you have 10 column in where clause using 10 bind variable then it may lead to lot of production of child plans.

Advertisements

SP2-0027: Input is too long (> 2499 characters)

SP2-0027: Input is too long (> 2499 characters)

When we tried to execute long SQL queries on the SQLPLUS prompt then we will get the bellow error:

SP2-0027: Input is too long (> 2499 characters)

Error occurred due to SQL queries is not break in different lines it is present in one line which will reached the upper limit of the single line input 2499.

Solution:
To resolve this issue, we will need to break the Single line SQL queries into different line.

In Notepad or Notepad++, we will see the single line SQL queries with wordwrap feature enabled in different line. So we donot know it is in single line or multiple line. Check with disable the wordwrap feature.

We will tried to break it in different line by pressing enter button in between query and break the Single line SQL query into multiple line. So that one line will not reached the upper limit of 2499 character. Then you simply copy paste the SQL queries and execute it directly on SQLPLUS prompt.

Unable to lock Central Inventory. OPatch will attempt to re-lock

Unable to lock Central Inventory. OPatch will attempt to re-lock

When we are going to apply or rollback the patch with Oracle opatch utility. Then some time we got the following message.

Error:

OPatch will sleep for few seconds, before re-trying to get the lock...
Unable to lock Central Inventory. OPatch will attempt to re-lock.
Do you want to proceed? [y|n]

Then we have to check the permission of the Oracle Inventory folder.

On Windows:
1. We are running from the command prompt, then check that command prompt is running with administrator right, Right click on cmd.exe and open it with administrator rights and again run the opatch command. It will fixed the issue surely.

2. If first point not able to solve the problem,We need to check permission of the folder C:\Program Files\Oracle\Inventory\ContentsXML and right click on directory–>properties–>Security–>select give full control for your windows users for contentsXML folder.

3. If not, then ADD the user with oradba priviliges group in windows.

On Linux:
1. Check the permission of the folder

ls -ltr /oracle/oraInventory/ContentsXML

2. Change the permission of the Inventory folder

chmod 777 /oracle/oraInventory/ContentsXML

High CPU SQL in AWR EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS

High CPU consumption in AWR Report EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS()

EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS dbms_job is used for Enterprise Manager (EM) Database Control in 10g and above versions of the database.
NOTE : The dbms job EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS is present in the database running EM Database Control and the note does not apply to EM Grid Control.

In AWR report it show large no of execution and consuming High CPU

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

On checking the dbms_jobs table:

select job, schema_user, last_date, next_date, interval, what from dba_jobs;

 JOB NEXT_DATE NEXT_SEC DESCRIPTION                                                                                                                                                  
---- --------- -------- ----------------------------------------                                                                                                                     
4001 29-AUG-17 18:43:01 wwv_flow_cache.purge_sessions(p_purge_se                                                                                                                     
4002 29-AUG-17 11:50:43 wwv_flow_mail.push_queue(wwv_flow_platfo                                                                                                                     
  23 29-AUG-17 11:41:43 EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROC 

JOB SCHEMA_USER WHAT                                         INTERVAL
--- ----------- -------------------------------------------- -----------------------
 23 SYSMAN      EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); sysdate + 1 / (24 * 60)

Solution:

Option 1: If enterprise manager database control is not needed.
If EM database control is not used, we did not configured it then we removed the job which caused performance issue.
For Remove the job:

execute dbms_job.remove(21);

OR

exec sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> commit;

Option 2nd: If Enterprise Manager database console is needed.
For improve the performance we need to rebuild index and reorganise the related tables as below steps:

1. Stop the DB Console service on all the nodes (if it’s running)
2. Stop the EM jobs by logging in as SYSMAN and run:

exec emd_maintenance.remove_em_dbms_jobs;
commit;

3. Reorganise the table as SYS or SYSMAN:

CREATE TABLE temp_dep AS SELECT * FROM MGMT_METRIC_DEPENDENCY_DETAILS WHERE 1=2;
exec dbms_redefinition.start_redef_table ('SYSMAN','MGMT_METRIC_DEPENDENCY_DETAILS','temp_dep');
DROP MATERIALIZED VIEW temp_dep;

CREATE TABLE temp_avail AS SELECT * FROM MGMT_CURRENT_AVAILABILITY WHERE 1=2;
exec dbms_redefinition.start_redef_table ('SYSMAN','MGMT_CURRENT_AVAILABILITY','temp_avail');
DROP MATERIALIZED VIEW temp_avail;

4. Rebuild the index used:

ALTER INDEX SEVERITY_PRIMARY_KEY REBUILD;
ALTER INDEX CUR_AVAIL_PRIMARY_KEY REBUILD;
ALTER INDEX PK_MGMT_METRIC_DEP_DETAILS REBUILD;

5. Resubmit the EM jobs:

exec emd_maintenance.submit_em_dbms_jobs;
commit;

Migrate Database from Oracle to MS SQL Server with SSMA

Migrate Database from Oracle to Microsoft SQL Server with SSMA Utility

Following are the steps performed from migrate database from oracle to sql server:

1. Download the SSMA software for migration from Oracle to SQL Server

https://www.microsoft.com/en-us/download/details.aspx?id=54255

2. Install the SSMA software and found SSMA icon on Desktop by simple next next button.

SSMAicon1.JPG

3. Open the SSMA software and start a new project.
Go to File–>click New then Pop Window ask for project name, location and into which sql server want to migrate included SQL Azure option. (selected sql server 2014 for example)

SSMAnewproject.JPG

4. Connect with oracle database have three options:
1. Oracle Client Provider – Need to install Oracle Client
2. OLEDB Provider – Direct connectivity for database if not using PDB of 12c
3. ODBC Driver – Create ODBC connection first
Note: you are on another server or pdb database of 12c version.Its better to install Oracle client for connectivity and create TNS entries in Client.

In my example, My Server is in 12c and Schema is in PDB database. So i installed Oracle Client and configured PDB1 for direct connectivity. Make Sure your pdb is in open state.

Created TNS entry for PDB database in tnsnames.ora file:
Go to Oracle Client location $ORACLE_CLIENT_HOME\network\admin

tnsping.JPG

5. Connect with PDB1 database with SSMA Module in Oracle Connectivity:

PDBconnectivity.JPG

Note: it will take time after message of connected, it will start load the object from Oracle to make its own repository.

LoadOracleObjects.JPG

Error:
Connection to Oracle failed.
ORA-01033: ORACLE initialization or shutdown in progress

I am using PDB1 database , it is not in open state. So, I connected with sysdba user and open the pdb1 database
sqlplus sus@pdb1 as sysdba
Password
SQL> Alter database open;

6. After loading the Oracle Object, it will enable the connectivity button for SQL Server.

ConnectwithSQLServer

7. Connect with the SQL Server database.
Note: Choosed TEST database as name which is not present in SQL Server. So it will pop window for creating TEST database.

SqlServerconnection.JPG

Error: Sql Server Agent is now working. You need to start the service of SQL Server Agent in services.msc, Then press continue button.

SqlServerAgentService.JPG
Note: You can also modify reporserver schema in TEST Database to default dbo

Modifytodba.JPG

8. Right click on the schema in Oracle tab which want to migrate and choose create report.

CreateReport.JPG
Note: Process of creating report is started in output window.

9. Warning occured during the create report and continue on it.

Prerequestnotmet.JPG

10. After continue, it start converting and creating report in html fromat which is present in location of project –> report –> report –M mainindex.html

creatingreport

11. Report open in internet explorer which will give you detail of objects and estimate of manual convertion time.

Reporthtml.JPG

12. Start the convert process by right click on schema of Oracle and choose the convert button using the SSMA tool.

Conversionfinish.JPG

13. On Right tab SQL SERVER Metadata Explorer, Right click for sync the sql server database with repository
It will create the tables, procedure, function into the sql server database when you choose to sync the database.

synchronisedatabase.JPG

14. Syncronization is completed for sturcture

syncforstructure.JPG

15. Start the migration data process
Note: Both oracle and SQL Server database is selected in migration process

Startmigratedata.JPG
Note: Will ask for password again for data migration.

16. Datamigration report is finished.
Error 58 table failed to migrated

Datamigrationreport.JPG
Save the report for future correction.

17. Verify the sql server by connecting the TEST database.

Change the DBNAME with NID utility in Oracle

Change the DBNAME with NID utility in Oracle

Nid utility is used to change the dbname if required.

Following are the step to change the database name in Oracle with NID utility

Suppose database is installed on windows Server we are nameing from IC11G to ORCL, let go one by one steps

1. Check the location of datafiles

set line 200 pages 200
column name for a50
column file_name for a50
column member for a50
select name from v$controlfile;
select file_name from dba_data_files;
select file_name from dba_temp_files;
select member from v$logfile;

SQL> select name from v$controlfile;

NAME
——————————————
E:\ORADATA\IC11G\CONTROL01.CTL
E:\ORADATA\IC11G\CONTROL02.CTL

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————
E:\ORADATA\IC11G\USERS01.DBF
E:\ORADATA\IC11G\UNDOTBS01.DBF
E:\ORADATA\IC11G\SYSAUX01.DBF
E:\ORADATA\IC11G\SYSTEM01.DBF

SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————
E:\ORADATA\IC11G\TEMP01.DBF

SQL> select member from v$logfile;

MEMBER
——————————————
E:\ORADATA\IC11G\REDO03.LOG
E:\ORADATA\IC11G\REDO02.LOG
E:\ORADATA\IC11G\REDO01.LOG

Note: In windows under oradata folder, we can alos change folder name IC11g to ORCL
 
2. Shutdown the database for change name with nid process.

Shutdown immediate;

 
3. Startup the database at mount state for using NID utility

startup mount

 
4. Use NID utility to change the dbname
Note: Change dbname from IC11g to ORCL

SET oracle_Sid=IC11G

nid target=sys/sys123 dbname=ORCL

Output:
C:\Users\e3019447>nid target=sys/sys123 dbname=ORCL

DBNEWID: Release 11.2.0.4.0 – Production on Mon Jun 5 01:04:32 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to database IC11G (DBID=231578491)

Connected to server version 11.2.0

Control Files in database:
E:\ORADATA\IC11G\CONTROL01.CTL
E:\ORADATA\IC11G\CONTROL02.CTL

Change database ID and database name IC11G to ORCL? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 231578491 to 1473611297
Changing database name from IC11G to ORCL
Control File E:\ORADATA\IC11G\CONTROL01.CTL – modified
Control File E:\ORADATA\IC11G\CONTROL02.CTL – modified
Datafile E:\ORADATA\IC11G\SYSTEM01.DB – dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\SYSAUX01.DB – dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\UNDOTBS01.DB – dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\USERS01.DB – dbid changed, wrote new name
Datafile E:\ORADATA\IC11G\TEMP01.DB – dbid changed, wrote new name
Control File E:\ORADATA\IC11G\CONTROL01.CTL – dbid changed, wrote new name
Control File E:\ORADATA\IC11G\CONTROL02.CTL – dbid changed, wrote new name
Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1473611297.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

 
5. After NID utility done, try to startup the database in open state

ALTER SYSTEM SET DB_NAME=ORCL SCOPE=spfile;
startup nomount;
alter database mount;
alter database open resetlogs;

 
Note: Following error may occurred after nid utility

ORA-01103: database name ‘ORCL’ in control file is not ‘IC11G’

Solution: Alter db_name parameter in SPFile
ALTER SYSTEM SET DB_NAME=ORCL SCOPE=spfile;

When you start database may error occurred so change your environment variable to ORACLE_SID to ORCL
Set ORACLE_SID=ORCL
OR
Change the services name on window platform
oradim -Delete -SID IC11g
oradim -NEW -SID ORCL

ORA-12560: TNS: Protocol adapter error

Go to folder %oracle_home%\database in windows:
Rename the password file from PWDIC11g.ora to PWDORCL.ora
Rename Spfile from SPFILEIC11g.ora to SPFILEORCL.ora

6. If you like to move database as new database name as default setting, it better to rename the folder associated with database for future better handling.
Note: We are renaming all folder associated with old oracle home like oradata has IC11g name and change parameter control_file to move in new destination.

— Creating Pfile for editing parameters
Create pfile=’E:\pfile.txt’ from spfile;

— Spooling rename file from IC11g to ORCL folder
set line 200 pages 200
spool E:\rename.txt
— rename datafile
select ‘Alter database rename file ”’||file_name||”’ TO ”’||replace(file_name,’IC11G’,’ORCL’)||”’;’ from dba_Data_files;
— rename temp files
select ‘Alter database rename file ”’||file_name||”’ TO ”’||replace(file_name,’IC11G’,’ORCL’)||”’;’ from dba_temp_files;
— rename redo log files
select ‘Alter database rename file ”’||member||”’ TO ”’||replace(member,’IC11G’,’ORCL’)||”’;’ from v$logfile;
spool off

 
7. Shutdown the database

shutdown immediate;

 

8. Edit the pfile.txt and change the paramater as needed.

I found following parameter using IC11g as default location, Change to ORCL

*.audit_file_dest=’C:\Oracle11g\admin\ORCL\adump’
*.control_files=’E:\Oradata\ORCL\control01.ctl’,’E:\Oradata\ORCL\control02.ctl’

 
9. Now move on to windows and check the location which mentioned is present

Note: Rename the folder from IC11g to ORCL in windows

Rename the folder in admin directory from IC11g to ORCL
Rename the folder under oradata directory from IC11g to ORCL

 
10. Startup nomount the database from pfile

sqlplus sys as sysdba
create spfile from pfile=’E:\pfile.txt’
startup nomount;
alter database mount;

 
11. Change the location of the database file with rename folder
Note: Fire the alter command created in step 6.

Alter database rename file ‘E:\ORADATA\IC11G\USERS01.DBF’ TO ‘E:\ORADATA\ORCL\USERS01.DBF’;
Alter database rename file ‘E:\ORADATA\IC11G\UNDOTBS01.DBF’ TO ‘E:\ORADATA\ORCL\UNDOTBS01.DBF’;
Alter database rename file ‘E:\ORADATA\IC11G\SYSAUX01.DBF’ TO ‘E:\ORADATA\ORCL\SYSAUX01.DBF’;
Alter database rename file ‘E:\ORADATA\IC11G\SYSTEM01.DBF’ TO ‘E:\ORADATA\ORCL\SYSTEM01.DBF’;
Alter database rename file ‘E:\ORADATA\IC11G\TEMP01.DBF’ TO ‘E:\ORADATA\ORCL\TEMP01.DBF’;
Alter database rename file ‘E:\ORADATA\IC11G\REDO03.LOG’ TO ‘E:\ORADATA\ORCL\REDO03.LOG’;
Alter database rename file ‘E:\ORADATA\IC11G\REDO02.LOG’ TO ‘E:\ORADATA\ORCL\REDO02.LOG’;
Alter database rename file ‘E:\ORADATA\IC11G\REDO01.LOG’ TO ‘E:\ORADATA\ORCL\REDO01.LOG’;

 
12. After open the database in normal mode:

alter database open;

 
13. Change the service name in listener ora and tnsnames.ora
 

ORA-39353 Data was not imported for Cannot locate the time zone version file

ORA-39353 Data was not imported cannot locate the time zone version file.

Error occurred when i tried to impdp the dump file from oracle 12c version to 11.2.0.4 version database.Its occurred due to missing the timezone file

Used Expdp backup for the database with version clause from the 12c database

expdp version=11.2 directory-dump dumpfile=data.dmp logfile=data.log

Note:when i tried to import in 11.2.0.4 version,its failed with following error

ORA-39353 Data was not imported for Cannot locate the time zone version file.

Resolved: copy timezlrg_18.dat from 12c dbhome to 11g dbhome.

1. On Oracle 12c home:go to location %ORACLE_HOME\oracore\zoneinfo
2.copy the timezlrg_18.dat file
3. paste at 11g dbhome %oracle_home%\oracore\zoneinfo folder

Note: It is temporary fix, you need to upgrade timezone.