Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

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 »

Oracle 12c (12.1.0.1) is officially released.

Posted by FatDBA on June 26, 2013

Ulntit11led

 

Untitled

Finally the long wait is over, 12c (12.1.0.1) has released and is available on Oracle official website to download and test.

Link: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Posted in Basics | Tagged: | Leave a Comment »

Snapshot ControlFile in RAC Systems and related errors (ORA-00245)

Posted by FatDBA on June 17, 2013

Even after a good Database Upgrade plan one can face issues related to performance, Functionailities, Backups etc. Today I’m going to discuss one of the case that was happened during a production upgrade from 10g r2 -> 11g r2.

We had our database full backup scheduled every night with configuration like – Autobackup ON, Dataa transfer directly to SBT Mediums (Tapes), OPTIMIZATION ON and other basic settings/configurations.
While performing status check next morning we discovered backup got failed with error message:

ORA-00245: control file backup failed; target is likely on a local file system

Error
———
Starting Control File and SPFILE Autobackup at 2013-09-06:21:25:34
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ORA_SBT_TAPE_1 channel at 09/06/2013 21:25:36
ORA-00245: control file backup failed; target is likely on a local file system

As we have AUTOBACKUP functionailty ON which takes backup of critical files like controlfile file whenever the database structure metadata in the control file changes and whenever a backup record is added.

Reason:
Fro 11gR2 onwards in RAC database, due to the changes made to the controlfile backup mechanism, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances.
A snapshot controlfile in 11gr2 must be reachable by all nodes in RAC Environment. If the snapshot controlfile is not available or not shared RMAN will throw such errors during backup operations.
Documentation Link: http://docs.oracle.com/cd/E11882_01/rac.112/e16795/rman.htm#i455026

Solution:
To avoid such situations always keep your snapshot controlfile on a shared location so that it could be accessible by all nodes when needed.

RMAN> configure snapshot controlfile name to ‘/disk02/rmaninfo/snapcontrol/snapcf_cesc1.f’;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/disk02/rmaninfo/snapcontrol/snapcf_cesc1.f’;
new RMAN configuration parameters are successfully stored.

Okay now we have the solution of the problem let’s discuss more about Snapshot Controlfile and about this new change in 11g r2.
– When RMAN performs any operation that requires a consistent view of the control file (such as a backup), it will first create a copy of the control file. This copy is called the snapshot control file. The snapshot control file will be used for the duration of that operation and will be overwritten by any subsequent operation. Even related operations (say, during a backup database plus archive-log operation that does an archive log backup, a database backup, and then another archive log backup) will use newly created snapshot control files, one for each operation.

Why and what’s the need of this new change ?
From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue.

Now, what’s this ‘ControlFile Enqueue‘ ?
When we need to back up or resynchronize from the Control file by RMAN, that first creates a snapshot or consistent image of the control file.
If one RMAN job is already backing up the control file while another needs to a new snapshot control file, then may see error:

RMAN-08512: waiting for snapshot controlfile enqueue for 1900 seconds

A job that must wait for the control file enqueue waits for a brief interval and then successfully retrieves the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. So, finally with oracle 11g r2 we have the solution of this situation (In RAC Env) by keeping snapshot controlfile on shared location.

Posted in Advanced | Tagged: , | 1 Comment »

How To Relink Oracle Binaries.

Posted by FatDBA on June 5, 2013

Relinking occurs automatically under these circumstances:

– An Oracle product has been installed with an Oracle provided installer.
– An Oracle patch set has been applied via an Oracle provided installer.

Relinking Oracle manually is suggested under the following circumstances

– An OS upgrade has occurred.
– A change has been made to the OS system libraries.  This can occur during the application of an OS patch.
– A new install failed during the relinking phase.
– An individual Oracle patch has been applied (however, explicit relink instructions are usually either included in the README or integrated into the patch install script)

Steps For Relinking:
=====================
1. Log in to your system as user ‘oracle’ (Oracle Software Owner)
2. Set $ORACLE_HOME, SID, BASE etc.

[oracle@prashant ~]$ env | grep ORA
ORACLE_SID=tcs
ORACLE_BASE=/u01/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

3. Verify umaask is set corrrectly.
[oracle@prashant bin]$ umask
0022

4. Before relinking shutdown your database and listener.

5. Relink Instructions:
– Starting from Oracle 8i, we have a new feature – relink script available under $ORACLE_HOME.bin directory.

cd $ORACLE_HOME/bin
relink

You can relink ALL executables with the following command: relink all
From 11GR2 the relink script will only accept “all” as argument

cd $ORACLE_HOME/bin
relink all

– You still have the option of running the “make” commands independently:

For executables: oracle, exp, imp, sqlldr, tkprof, mig, dbv, orapwd, rman, svrmgrl, ogms, ogmsctl
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk install

For executables: sqlplus
cd $ORACLE_HOME/sqlplus/lib
make -f ins_sqlplus.mk install

For executables: dbsnmp, oemevent, oratclsh
cd $ORACLE_HOME/network/lib
make -f ins_oemagent.mk install

For executables: names, namesctl
cd $ORACLE_HOME/network/lib
make -f ins_names.mk install

For executables: osslogin, trcasst, trcroute, onrsd, tnsping
cd $ORACLE_HOME/network/lib
make -f ins_net_client.mk install

For executables: tnslsnr, lsnrctl
cd $ORACLE_HOME/network/lib
make -f ins_net_server.mk install

6. How to tell if relinking was successful:
If relinking was successful, the make command will eventually return to the OS prompt without an error. There will not be a “Relinking Successful” type message.

If you receive an error message during relinking:
Relinking errors usually terminate the relinking process and contain verbiage.

Posted in Advanced | Tagged: | 2 Comments »

11.2.0.3.4 (Why so many 1’s, 2’s, 3’s and 4’s)

Posted by FatDBA on May 16, 2013

Release_Number

Why we have so many numbers (1,2,3,4) in 11.2.0.3.4 ?. Alright, today I’ll try to explain significance of these numbers and it’s relevance.

11.X .X. X.X
Major Database Release Number:
Major database release number, major new edition, contains significant new functionality. It represents a major new version of the software that contains significant new functionality.

X. 2. X. X
Database Maintenance Release Number:
Number increases when bug fixes or new features to existing programs become available.

X . X .O. X .X
Middleware Release Number:
Release level of Oracle Middleware. In case of 10g/9i it was Oracle Application Server Release Number.

X. X . X. 3. X
Component-Specific/Patch Release Number:
A Patch release contains fixes for serious bugs that cannot wait until the next maintenance releasefor example, component patch sets or interim releases.

X. X . X. X. 4
Platform-Specific Release Number:
Usually this is a patch set Updates.
Used to identify a particular emergency patch release of a software product on that operating system, it usually fixes or works around a particular, critical problem

Views:
SQL> select banner from v$version;
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

Posted in Basics | Tagged: | Leave a Comment »

Me explaining PGA & UGA.

Posted by FatDBA on April 27, 2013

• Program global area (PGA)

PGA is memory specific to operating process that is not shared by other processes in the system. Because PGA is process specific, it is never allocated in the SGA. Access to the PGA is exclusive to the server process.

An analogy for a PGA is a temporary countertop workspace used by a file clerk. In this analogy, the file clerk is the server process doing work on behalf of the customer (client process). The clerk clears a section of the countertop, uses the workspace to store details about the customer request and to sort the folders requested by the customer, and then gives up the space when the work is done.

cncpt219

Not all of the PGA areas will exist in every case. PGA is subdivided into different areas.

– Session Memory: Also known as Stack Space (Session Memory).

– Private SQL Area:
This area holds information about a parsed SQL statement and other session specific information for processing for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.

Private SQL Area is subdivided in to following parts:
Run-Time Area: This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.
Persistent Area: Area contains bind variable values.
– SQL Work Areas:
This area is a combination of Sort Area, Hash Area and Bitmap Merge Area. A sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.
WORKAREA_SIZE_POLICY (AUTO | MANUAL) specifies the policy for sizing work areas. When set to Auto, Work areas used by memory-intensive operators (such as sort, group-by, hash-join, bitmap merge, &  bitmap create) are sized automatically.

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.
In below image, a pointer (Cursor) pointing towards ‘Private SQL Area’ to fetch information. The client process is responsible for managing private SQL areas.

 

cncpt324
* NOTE: For dedicated sessions, the UGA is a part of the RAM heap in the PGA that controls user sessions space for sorting and hash joins.  If you are forced to use shared servers (the Multi-threaded Server or MTS) the UGA is inside the SGA large_pool_size region).
In sum, when using a dedicated connection, the User Global Area (UGA) supplements the PGA with additional memory for the user’s session, such as private SQL areas and other session-specific information such as sorting and session message queues.

 

………………………………………………………….

 
• UGA (User Global Area)
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.

NET8A_416
The UGA must be available to a database session for the life of the session. For this reason, the UGA cannot be stored in the PGA when using a shared server connection because the PGA is specific to a single process. Therefore, the UGA is stored in the SGA when using shared server connections, enabling any shared server process access to it. When using a dedicated server connection, the UGA is stored in the PGA.

 

UGA has following sections:
Session Variables –
OLAP Pool – This sessions opens automatically whenever a user queries a dimensional object like CUBE.

 

Posted in Basics | Tagged: | Leave a Comment »

« Previous Entries
Next Entries »