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.

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.

Upgrade Oracle database from lower to upper version using DBUA utility

Execution plan for Up-gradation from 11.2.0.1,11.2.0.3 to 11.2.0.4 version using DBUA

Example show us the Upgradation of 11.2.0.1 to 11.2.0.4 version and same steps used to upgrade from 11G to 12C with non container database. For move to pluggable first you need to upgrade into 12C Version then you can move to pluggable database.

General Steps to Upgrade database with DBUA utility:

Note: Took the Pre-Upgrade SQL (UTLUXXXi.sql) from new version home present in RDMBS Folder and run on database which going to upgrade. It will give you report for upgrade. Example show for 11g to 12c upgrade download: Preupgrade
Important Note:Checked and performed the steps found in Pre-Upgrade log file as attached.

Steps for taking care of Upgrade Process if need to rollback:

1. Source database give following command 11g

GRANT ADMINISTER DATABASE TRIGGER to MDSYS;

2. Backup the old listener files.
Copy the network folder present in dbhome folder of Oracle

3. Set Old Oracle home and path variables

set ORACLE_HOME=E:\oracle\11.2.0\dbhome_1
set oracle_sid=IC
set PATH=E:\oracle\11.2.0\dbhome_1\bin;%PATH%

4. Took backup of database through RMAN Utility

run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup format 'F:\upgradebackupIC\rman\full_db_%t_%sp%p' filesperset 10 database plus archivelog;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format 'F:\upgradebackupIC\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'F:\upgradebackupIC\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}

5. Check the Invalid objects

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

6. Check the objects count of user schema mainly:

select owner,object_type,count(*) from dba_objects group by owner,object_type order by owner,object_type ;

7. Run the UTLRP utility before upgrade if invalid objects found, note down again:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

8. Check time zone if its less than 14 need to modified
Note: The changes might affect existing data of the TIMESTAMP WITH TIME ZONE data type

SELECT version FROM v$timezone_file;
-- check also
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

9: Check aud$ table before upgraded

select count(*) from sys.aud$;
Select count(*) from sys.fga_log$;

If you need audit Do the following s:
1. Export AUD$ data and/or FGA_LOG$ data from the source database
2. Clean up AUD$ and/or FGA_LOG$
3. Re-run catupgrd.sql
4. Import AUD$ data and/or FGA_LOG$ data into 11.2.0.1

truncate table sys.aud$;
truncate table sys.fga_log$

10: Execute the stats script

$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

11: Take backup of RMAN Configuration

RMAN> Show all;

12: Check all the user list with password or expiry

set pages 999
set line 999
col account_status for a20
col username for a20
select username,account_status,expiry_Date,profile from dba_users;

13: Check profiles setting

set line 999
set pages 999
col profile for a20
col resource_name for a25
col resource for a10
col limit for a10
select * from dba_profiles order by 1,2;

14: Get all password of users;

select 'Alter user '||a.username||' identified by values '''||b.password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '
||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users a,sys.user$ b where b.name = a.username;

15: Check local listener parameter is source database

Show parameter local

16: Backup of Pfile

create pfile='F:\upgradebackupIC\pfile.txt' from spfile;

17: Check Hidden parameter & remove it from spfile.

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

Note: Remove the parameter from pfile and restart the database

18: Purging Recyclebin

PURGE DBA_RECYCLEBIN;

19: Took complete backup of database before dbua

Cold backup + Expdp backup full + RMAN Backup

20: Take snapshot of the services.msc

21: Check the registry$history table

col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;

22. Check the dba registory componenet:

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

23: Run the DBUA utility from new home
Set the PATH variable for new Oracle 11204 home, then run the DBUA Utility from new home.

24. Follows the steps performed in DBUA , it will take around 30 min to 1 hour depend upon system configuration and database size.

25. Check the Invalid objects

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

otherwise run utlrp.sql

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

26. Match the objects count for users schemas.

select owner,object_type,count(*) from dba_objects group by owner,object_type order by owner,object_type ;

27. Check time zone

SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

28 Import the aud$ backup and match the aud$ table

select count(*) from sys.aud$;
Select count(*) from sys.fga_log$;

29. Replace the listener.ora , tnsnames.ora and sqlnet.ora from backup Or Create new listener

30: Check the environment variable for windows

31: Match all the user list with password or expiry

set pages 999
set line 999
col account_status for a20
col username for a20
select username,account_status,expiry_Date,profile from dba_users;

32: Match profiles setting

set line 999
set pages 999
col profile for a20
col resource_name for a25
col resource for a10
col limit for a10
select * from dba_profiles order by 1,2;

33: Check local listener parameter is source database

Show parameter local

34: Check Hidden parameter

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

35: Check Window Scheduler for backup or Services in windows

36: Took complete backup of database after database upgrade
Cold backup + Expdp backup full + RMAN Backup

37: Take snapshot of the services.msc

38: check the registry$history table

col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;

39. Check the dba_registry component:

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

40. Check archive log and enable it after dbua it is in disable state:

archive log list;
---Enable archiving
startup mount
alter database archivelog
alter database open;

41. Check the dump file destination in parameter for alert log & change it to new home:

show parameter diag

42. Checked all service is running from new oracle home binaries.

43. Set the RMAN configuration got in show all command.