Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for August, 2013

VMWare: Reason: “Cannot open the disk, Reason: Failed to lock the file”.

Posted by FatDBA on August 25, 2013

Today while starting one of my Test machine/instance which is a part of an VMWare (This Team includes a total of 3 virtual machines) has started giving an error message which reads:

“Cannot open the disk: ‘H:\Machine ………….’ or one of the snapshot disks it depends on.Reason: Failed to lock the file”.

errrrrrro
And this error restricts us to open the same machine and kept on throwing the same again and again.

Error:
After reading errors it seems to be related with the locks that machines acquire in VMWare Team environment in order to access resources.

Resolution:
While we start our virtual machines there are many different folders related to locks created which contains .lck (Lock Files) and are used by the machine during the operation i.e M30076.lck, Mx456.lck et.
These locks are dynamic in nature and auto refreshes after some time. So, i removed/renamed all of the lock files (.lck) existed under all of these folder and tried the machine again and as what i’ve expected, it started working back again.

* Note: Performing these steps on PROD systems could be dangerous and can cause serious performance/hang issues. Performing such steps always requires recommendations from the VMWARE support.

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced | Tagged: , , | Leave a Comment »

Using DBCA (DB Create) in Silent Mode.

Posted by FatDBA on August 23, 2013

DBCA is one of the most important and easiest utility to create new databases. Most of us have used it during our careers in GUI mode, but we have an option to launch it in non-graphical mode (Non-Interactive Mode) as well. Below provided steps can be performed to create database in silent/non-interactive mode. So you can launch DBCA in text mode from your favorite ssh client/terminal like putty, secureCRT, SSH Tectia Client etc. and can save yourself from that ‘old’ manual DB create methods (Using catprocs, catalog sql’s and creating new dump directories).

To create database: This requires a response file which includes all necessary details like GGDBNAME (Global Database Name), SID, Listener Name, SYS/SYSTEM/DBSNMP/SYSMAN Passwords etc.
Browser the response file (ORACLE_HOME/assistants/dbca) and make all mandatory changes.

Step 1: Location of response file
[oracle@prashant dbca]$ pwd
/u01/app/oracle/product/11.2.0/db_1/assistants/dbca    — Default Location of Response File.

[oracle@prashant dbca]$ ls
dbca.rsp  doc  jlib  logs  templates

Create a copy of original file.
[oracle@prashant dbca]$ cp dbca.rsp  dbcacreatedb.rsp

[oracle@prashant dbca]$ ls
dbcacreatedb.rsp  dbca.rsp  doc  jlib  logs  templates

Step 2:
Edit all mandatory changes in response file.
GDBNAME = “silent”
SID = “silent”
SYSPASSWORD = “oracle90”
SYSTEMPASSWORD = “oracle90”
SYSMANPASSWORD = “oracle90”
DBSNMPPASSWORD = “oracle90”
CHARACTERSET   = “US7ASCII” (Default)
NATIONAL CHARACTERSET = “UTF8” (Default)

Although there are various sections inside the response file “.rsp” file like createdatabase, deleteDatabase, createTemplateFromDB or createCloneTemplate. In this post we have talk only about ‘createDatabase‘ and is the area of interest (Used to create new database).

All Examples :
—————————–
1. dbca -progress_only -responseFile <response file>
Display a progress bar depicting progress of database creation
process.

2. dbca -silent -responseFile <response file>
Creates database silently. No user interface is displayed.

3. dbca -silent -createDatabase -cloneTemplate
-responseFile <response file>    
Creates database silently with clone template. The template in
responsefile is a clone template.

4. dbca -silent -deleteDatabase -responseFile <response file>
Deletes database silently.

Step: 3

So, we are going to install the database using ‘STEP 3’ without using -cloneTemplate. This will create database silently and will show progress in percentages.
[oracle@prashant dbca]$ dbca -silent -createDatabase -responseFile dbcacreatedb.rsp
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log” for further details.

It seems that Database Creation is successful completed. Let’s check creation  log file.
File: “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log”

Okay, while checking log file i found some new entries after creating database:

Database creation complete. For details check the log-files at:
/u01/app/oracle/cfgtoollogs/dbca/silent.
Database Information:
Global Database Name:silent
System Identifier(SID):silent

[oracle@prashant dbca]$ cd /u01/app/oracle/cfgtoollogs/dbca/silent
[oracle@prashant silent]$ ls -ltr
total 104
-rw-r—– 1 oracle oinstall  1372 Aug 23 11:47 rmanRestoreDatafiles.sql
-rw-r—– 1 oracle oinstall   330 Aug 23 11:49 CloneRmanRestore.log
-rw-r—– 1 oracle oinstall  1135 Aug 23 11:50 cloneDBCreation.log
-rw-r—– 1 oracle oinstall     8 Aug 23 11:50 postScripts.log
-rw-r—– 1 oracle oinstall     6 Aug 23 11:50 lockAccount.log
-rw-r—– 1 oracle oinstall   349 Aug 23 11:51 postDBCreation.log
-rw-r—– 1 oracle oinstall   458 Aug 23 11:51 OraDb11g_home1_silent_creation_checkpoint.xml
-rw-r—– 1 oracle oinstall 66392 Aug 23 11:51 trace.log
-rw-r—– 1 oracle oinstall   654 Aug 23 11:51 silent.log

* There are some interesting files generated during DB creation like CloneRmanRestore.log, cloneDBCreation.log, postDBCreation.log and rmanRestoreDatafiles.sql.

Let’s check our new database ‘Silent’.
[oracle@prashant silent]$ ps -ef|grep pmon
oracle   12671     1  0 Aug20 ?        00:00:03 ora_pmon_sairam
oracle   20771     1  0 11:50 ?        00:00:00 ora_pmon_silent
oracle   22315 18308  0 13:06 pts/2    00:00:00 grep pmon

Okay we have a PMON for databse (Silent).
[oracle@prashant silent]$ . oraenv
ORACLE_SID = [sairam] ? silent
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@prashant silent]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 23 13:07:04 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
SILENT    READ WRITE

 

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced, Basics | Tagged: , , | Leave a Comment »

Viagra For SQL’s. DBMS_STATS (NO_INVALIDATE argument/parameter) ?

Posted by FatDBA on August 20, 2013

While trying to get any resolution for one of our old problem (Database Hangs while Gathering Stats – Schema Wise), one of the oracle support analyst recommends us to gather stats next time with no_invalidate parameter. We actually never tested/tried this option with DBMS_STATS procedure before.

Let me put some light over this argument/parameter and how it can act as a Viagra for your SQL’s. Fast, Instant …

DBMS_STATS provides us an argument to control invalidation of SQL plans available and are sitting in your Library Cache and are used by your CBO. This is quite decisive when used to control SQL executions and query optimizations and performance.

It has three arguments types.
dbms_stats.set_param (no_invalidate false):  
This option will orders that a change in statistics always invalidates the dependent sql cursors/plans immediately upon a change to the statistics. Very much similar to ‘ALTER SYSTEM FLUSH SHARED POOL’.
dbms_stats.set_param (no_invalidate true):  In this case, a change in statistics will never nullify or voids current SQL execution plans in Library Cache.
dbms_stats.set_param (no_invalidate dbms_stats.auto_invalidate):  This is the default (Since Oracle 10g). This default type says that Oracle will not invalidate the old plan immediately after new statistics are generated.

Now a question:
After how many seconds, minutes, hours or probably days, old plans gets invalidate in case of no_invalidate.auto_invalidate which is a Default ??
Ans: Many claims that a hidden parameter _optimizer_invalidation_period dictates the maximum time before invalidation. Which is default of 18000 seconds (5 hours)

SQL> select     rpad(i.ksppinm, 35) || ‘ = ‘ || v.ksppstvl parameter,
i.ksppdesc description,
v.ksppstdf dflt
from    x$ksppi         i,
x$ksppcv        v
where   v.indx = i.indx
and     v.inst_id = i.inst_id
and     i.ksppinm like ‘_optimizer_invalidation_period’
order by 1;

PARAMETER                                                                                DESCRIPTION                                                                             DFLT
—————————————————————–    ——————————————————————————————-    ———–
_optimizer_invalidation_period      = 18000            time window for invalidation of cursors of analyzed objects         TRUE

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Uncategorized | 1 Comment »

OPatch: ApplySession failed: Patch ID is null (OPatch failed with error code 73).

Posted by FatDBA on August 20, 2013

Going to discuss one of the most recent issue that I’ve faced while applying PSU patch for the month of July on one of our Pre-Prod Database. I’ve tried to reproduce the similar situation on one of my test machine.

We were trying to apply it (Patch) on our databse which is running on 11.2.0.1.0.  After sucessfully passed all prechecks of the patch we downloaded from Metalink

Prechecks:
1. Setting Env Variables (ORACLE_SID, ORACLE_HOME, PATH etc.)
2. Shutting down DB and Listener (No RAC Env)
3. Uncompressed zip file to the OPatch directory.

ORACLE_SID = [orcl] ? sairam
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@prashant ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 20 22:00:32 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> exit

[oracle@prashant ~]$ cd /u01/app/oracle/product/11.2.0/db_1/OPatch/
[oracle@prashant OPatch]$ cd 12419378/
[oracle@prashant 12419378]$ ls
custom  etc  files  patchmd.xml  README.html  README.txt

[oracle@prashant 12419378]$ opatch apply
Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.6
OUI version       : 11.2.0.1.0
OUI location      : /u01/app/oracle/product/11.2.0/db_1/oui
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-08-20_22-02-22PM.log

Patch history file: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession failed: Patch ID is null.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 73

* After Checking the current OPatch Version it Looks like we will need a higher version of OPatch utility to apply this patch.
[oracle@prashant 12419378]$ opatch version
Invoking OPatch 11.1.0.6.6
OPatch Version: 11.1.0.6.6

OPatch succeeded.

Hence downloaded latest version of Opatch from My Oracle Support (patch 6880880).
1. Downloaded Patch 6880880.
2. Took a backup of $ORACLE_HOME/OPatch into a dedicated backup location.
3. Removed the contents of $ORACLE_HOME/OPatch directory (Please do not
remove $ORACLE_HOME/OPatch directory itself).
4. Unzipped the OPatch downloaded zip into $ORACLE_HOME directory.

[oracle@prashant OPatch]$ ls
p6880880_112000_Linux-x86-64.zip
[oracle@prashant OPatch]$ unzip p6880880_112000_Linux-x86-64.zip
Archive:  p6880880_112000_Linux-x86-64.zip
creating: OPatch/
creating: OPatch/oplan/
……….

……….

[oracle@prashant OPatch]$ ls -ltr
total 32408
-rw-rw—- 1 oracle oinstall       26 Jun 26 11:54 version.txt
-rwxrwxr-x 1 oracle oinstall     2991 Jun 26 11:54 README.txt
-rw-rw-r– 1 oracle oinstall     2576 Jun 26 11:54 opatch.pl
-rw-rw-r– 1 oracle oinstall       49 Jun 26 11:54 opatch.ini
-rwxrwxr-x 1 oracle oinstall     9352 Jun 26 11:54 opatchdiag.bat
-rwxrwxr-x 1 oracle oinstall    10056 Jun 26 11:54 opatchdiag
-rwxrwxr-x 1 oracle oinstall    19867 Jun 26 11:54 opatch.bat
-rwx–x— 1 oracle oinstall    31493 Jun 26 11:54 opatch
-rw-rw-r– 1 oracle oinstall    23695 Jun 26 11:54 emdpatch.pl
drwxrwxr-x 2 oracle oinstall     4096 Jun 26 11:54 docs
drwxrwxr-x 3 oracle oinstall     4096 Jun 26 11:54 oplan
drwxrwxr-x 3 oracle oinstall     4096 Jun 26 11:54 jlib
drwxrwxr-x 3 oracle oinstall     4096 Jun 26 11:54 crs
drwxrwxr-x 4 oracle oinstall     4096 Jun 26 11:54 opatchprereqs
drwxrwxr-x 5 oracle oinstall     4096 Jun 26 11:54 ocm
-rw-rw-r– 1 oracle oinstall     2147 Aug 20 02:22 PatchSearch.xml
-rw-r–r– 1 oracle oinstall 32996451 Aug 20 22:07 p6880880_112000_Linux-x86-64.zip

And finally after deploying last patch, we have the latest OPatch version available.

[oracle@prashant OPatch]$ opatch version
OPatch Version: 11.2.0.3.5

OPatch succeeded.

Now, we can go and deploy our PSU Patch.

[oracle@prashant OPatch]$ cd 12419378/
[oracle@prashant 12419378]$ opatch apply

Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/12419

Applying interim patch ‘12419378’ to OH ‘/u01/app/oracle/product/11.2.0/db_1’
Verifying environment and performing prerequisite checks…
Patch 12419378: Optional component(s) missing : [ oracle.client, 11.2.0.1.0 ]
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.2.0/db_1’)

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…

Patching component oracle.rdbms.rsf, 11.2.0.1.0…

Patching component oracle.rdbms.dbscripts, 11.2.0.1.0…

Patching component oracle.rdbms, 11.2.0.1.0…

Patching component oracle.oraolap, 11.2.0.1.0…

Patching component oracle.rdbms.deconfig, 11.2.0.1.0…

Patching component oracle.javavm.server, 11.2.0.1.0…

Patching component oracle.precomp.common, 11.2.0.1.0…

Patching component oracle.network.rsf, 11.2.0.1.0…

Patching component oracle.network.listener, 11.2.0.1.0…

Patching component oracle.rdbms.dv.oc4j, 11.2.0.1.0…

Patching component oracle.sdo.locator, 11.2.0.1.0…

Patching component oracle.sysman.console.db, 11.2.0.1.0…

Patching component oracle.sysman.oms.core, 10.2.0.4.2…

Patching component oracle.rdbms.dv, 11.2.0.1.0…

Patching component oracle.xdk.rsf, 11.2.0.1.0…

Patching component oracle.ldap.rsf.ic, 11.2.0.1.0…

Patching component oracle.ldap.rsf, 11.2.0.1.0…

Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.1.0…

Verifying the update…
Patch 12419378 successfully applied
Log file location: /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/12419378_Aug_20_2013_22_38_06/apply2013-08-20_22-38-06PM_1.log

OPatch succeeded.

Let’s Check the status of patch applied.
[oracle@prashant OPatch]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.5
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.5
OUI version       : 11.2.0.1.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2013-08-20_22-56-30PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2013-08-20_22-56-30PM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.1.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  12419378     : applied on Tue Aug 20 22:41:38 IST 2013
Unique Patch ID:  13710328
Created on 8 Jul 2011, 02:48:54 hrs PST8PDT
Bugs fixed:
9068088, 9363384, 8865718, 8898852, 8801119, 9054253, 8725286, 8974548
9093300, 8909984, 8755082, 8780372, 9952216, 8664189, 8769569, 7519406
9302343, 9471411, 8822531, 7705591, 8650719, 10205230, 9637033, 8883722
8639114, 8723477, 8729793, 8919682, 8856478, 9001453, 8733749, 8565708
8735201, 8684517, 8870559, 8773383, 8981059, 8812705, 9488887, 12534742
8813366, 12534743, 9242411, 12534745, 12534746, 12534747, 8822832
12534748, 8897784, 8760714, 12534749, 8775569, 8671349, 8898589, 9714832
8642202, 9011088, 9369797, 9170608, 9165206, 8834636, 8891037, 8431487
8570322, 8685253, 8872096, 8718952, 8799099, 12534750, 9032717, 9399090
12534751, 12534752, 9713537, 9546223, 12534753, 12534754, 8588519
8783738, 12534755, 12534756, 8834425, 9454385, 8856497, 8890026, 8721315
10248516, 8818175, 8674263, 10249532, 9145541, 8720447, 9272086, 9467635
9010222, 9102860, 9197917, 8991997, 8661168, 8803762, 12419378, 8769239
9654983, 8706590, 8546356, 10408903, 8778277, 9058865, 8815639, 11724991
9971778, 9971779, 9027691, 9454036, 9454037, 9454038, 8761974, 9255542
9275072, 8496830, 8702892, 8818983, 8475069, 8875671, 9328668, 8891929
8798317, 9971780, 8782959, 8774868, 8820324, 8544696, 8702535, 9952260
9406607, 8268775, 9036013, 9363145, 8933870, 8405205, 9467727, 8822365
9676419, 11724930, 8761260, 8790767, 8795418, 8913269, 8717461, 8861700
9531984, 8607693, 8780281, 8330783, 8784929, 8780711, 9341448, 9015983
10323077, 8828328, 9119194, 10323079, 8832205, 8717031, 8665189, 9482399
9676420, 9399991, 8821286, 8633358, 9321701, 9655013, 9231605, 8796511
9167285, 8782971, 8756598, 8703064, 9390484, 9066116, 9007102, 9461782
10323080, 10323081, 10323082, 8753903, 8505803, 9382101, 9352237, 9216806
8918433, 11794163, 9057443, 8790561, 11794164, 8733225, 8795792, 11794165
11794167, 9067282, 8928276, 8837736, 9210925

——————————————————————————–

OPatch succeeded.

 

MOSC Note: 1066937.1

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced | Tagged: , | Leave a Comment »

Index Fragmentation / Rebuild. When ?

Posted by FatDBA on August 13, 2013

How to find index is fragmented?

First analyze index
SQL>analyze index INDEX_NAME validate structure;

Then query INDEX_STATS view
1. If del_lf_rows/lf_rows is > .2 then index should be rebuild.
2. If height is 4 then index should be rebuild.
3. If lf_rows is lower than lf_blks then index should be rebuild.

SQL> column status format a10
SQL> select trunc((del_lf_rows/lf_rows)*100,2)||’%’ “status” from index_stats;
status
———-
21.83%

How to remove index fragmentation?
There are two way to remove fragmentation.
1. index coalesce
2. index rebuild

What is difference between coalesce and rebuild please go through below link for more details
http://download.oracle.com/docs/cd/B14117_01/server.101/b10739/indexes.htm#g1007548

SQL> alter index INDIA coalesce;
SQL> alter index INDIA rebuild;
SQL> alter index INDIA rebuild online;

Note: If any DML statement is running on base table then we have to use ONLINE keyword with index rebuilding.

SQL> analyze index idx_obj_id validate structure;
Index analyzed.

SQL> select trunc((del_lf_rows/lf_rows)*100,2)||’%’ “status” from index_stats;
status
——–
   0%

Note: Index rebuild when index is fragmented or it is needed, otherwise index rebuilding is myth for improve performance.

You can also enable Index Monitoring ‘ON’ to check if queries/statements are using index objects or not.

SQL> ALTER INDEX india MONITORING USAGE;
SQL> select index_name, table_name, monitoring, used, start_monitoring, end_monitoring from v$object_usage where index_name = ‘INDIA’ order by index_name;

INDEX_NAME                   TABLE_NAME               MON     USE        START_MONITORING       END_MONITORING
—————————— —————————— — — ——————- ———————————-  ———————————-
INDIA                       SYS_EXPORT_SCHEMA_01    YES       YES           06/01/2013                          23:31:34

 

To disable the monitoring:

SQL> ALTER INDEX my_index_i NOMONITORING USAGE;

Posted in Advanced | Tagged: | Leave a Comment »

 
%d bloggers like this: