All about Database Administration, Tips & Tricks

The Insights of a Geek DBA !!!

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Recreate the trace files if they have been deleted accidentally?

Came across a note to recreate the trace (.trc) files that have been accidentally deleted. But please be noted that information written down before deletion of trace may or may not be available in the new file you have created. However this may help in some cases where the process is still trying to write the trace where in our job/script has deleted that same trace file.

Here you go for steps:-

1.Find out the process id of the process which was supposed to write trace (ps -ef |grep $ORACLE_SID |grep lms) There can be multiple lms so be sure to find the exact process
2.Login to SQL*plus using "/ as sysdba"
3.SQL>oradebug setospid <PID_from_step_1>
4.SQL>oradebug close_trace
5.SQL>oradebug flush
6.SQL>exit
7.Now check the file in the bdump location, a new file would have been created

Extra caution:-

Oradebug sends an interrupt to the background process so there is always a chance that something could go wrong. The server process may terminate or report an error when when these steps are executed. Therefore it is recommended to use these steps with caution.

Reference:- 394891.1

-Thanks

Geek DBA

Quick Question: Does dbms_stats collects stats for invisible indexes

Does dbms_stats collects statistics for invisible indexes?

The answer is yes, Although the invisible indexes does not used by optimizer but they should be ready when it needed, hence the dbms_stats collects the statistics on the invisible indexes too.

Here is small test case.

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'), VISIBILITY from dba_indexes where index_name='IDX_MTD';

TO_CHAR(LAST_ANALYZ VISIBILIT
------------------- ---------
2013-06-15 14:00:36 INVISIBLE

SQL>
SQL> execute dbms_stats.gather_table_stats(‘SCOTT’,,'IDX_MTD');

PL/SQL procedure successfully completed.

SQL>
SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'), VISIBILITY from dba_indexes where index_name='IDX_MTD';

TO_CHAR(LAST_ANALYZ VISIBILIT
------------------- ---------
2013-06-15 14:01:23 INVISIBLE

See the time stamp change , so it is.

-Thanks

Geek DBA

11gr2 : Parallel Hint

Its more than 3 years 11g has released, still looking or finding something new every day or other.

Today its the Parallel Hint that I came across from the documentation.

Many has already blogged about this, still I am writing the same for my readers and

Until 10g, the parallel hint behaviour is like below for example parallel (t,5)

object t access will be parallel, but if the optimizer not choose that and choosen any index , the referenced index will not use parallel
object level parallelism need to invoked seperately, for example parallel(t,5), parallel (emp_pk, 3) etc

But from 11g1 and 11g2 onwards, a statement level parallel hint has been introduced to override both

From Documentation:-

Beginning with Oracle Database 11g Release 2 (11.2.0.1), the PARALLEL and NO_PARALLEL hints are statement-level hints and supersede the earlier object-level hints: PARALLEL_INDEX, NO_PARALLEL_INDEX, and previously specified PARALLEL and NO_PARALLEL hints. For PARALLEL, if you specify integer, then that degree of parallelism will be used for the statement. If you omit integer, then the database computes the degree of parallelism. All the access paths that can use parallelism will use the specified or computed degree of parallelism.

For a statement-level PARALLEL hint:

PARALLEL: The statement always is run parallel, and the database computes the degree of parallelism, which can be 2 or greater.
PARALLEL (DEFAULT): The same as PARALLEL. The DEFAULT keyword is included for completeness.
PARALLEL (AUTO): The database computes the degree of parallelism, which can be 1 or greater. If the computed degree of parallelism is 1, then the statement runs serially.
PARALLEL (MANUAL): The optimizer is forced to use the parallel settings of the objects in the statement.
PARALLEL (integer): The optimizer uses the degree of parallelism specified by integer.

If you observe above , there is no object reference in the hint,

In the following example, the optimizer calculates the degree of parallelism. The statement always runs in parallel.

SELECT /* PARALLEL */ LAST_NAME FROM EMPLOYEES
SELECT /* PARALLEL */ LAST_NAME FROM EMPLOYEES WHERE DEPT_ID=10;

The above statements and the objects involved in it, tables/indexes will always run in parallel without using the object level parallel hints like FULL (EMP), Parallel (EMP,2), PARALLEL_INDEX(DEPT_IDX,2) etc.

Hope this helps

An oldie: srvconfig 9i rac days, still useful in 11gr2

To Export cluster registry

$srvconfig -exp file_name

To Import Cluster registry

$srvconfig -imp file_name

To Upgrade the database cluster registry and database configuration

$srvconfig -upgrade -dbname database_name -orahome oracle_homename

To Downgrade the database cluster registry and database configuration (This is rarely used in case of any upgrade failure)

$srvconfig -downgrade -dbname database_name -orahome oracle_homename -version version_dtl

12c Database: New Features List 2

Few more collection of new features with in 12c database in continuation to my earlier post in this category

RMAN Enhancements:-

Recover or copy files from Standby databases
Table level restoration i.e object level
Incremental recovery more faster, many of the tasks removed

RAC Enhancements:-

TAF is extended to Transactions failover (insert/update/delete)

Dataguard Enhancements:-

Global data services, transparent to failover / switchover no client reconfiguration required

Grid Infrastructure Enhancements:-

Introduction of Flex Cluster , with light weight cluster stack, leaf node and traditional stack hub node, application layer is the typical example of leaf nodes where they dont require any network heartbeat
Introduction of Flex ASM, ASM would run only on 3 instances on a cluster even if more than 3, the instances that not have an ASM would request remote ASM, typically like SCAN. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.

DBConsole, introduction to EM Express:-

End of DBConsole, introduction to EM Express interface similar like cloud
Smaller disk usage less than 50MB compare to DBConsole
Real Time ADDM, runs every 3 sec, collects all information and provide GUI interface as like Cloud
Integrated and preconfigured
Database performance hub, which shows all graphs similar like Cloud/DBConsole

ILM:-

In database archive, Seperation of archived data with in the database, Marks rows invisible as archived
Data optimization, Move data based on policies (move,compres,read only etc)
Heat maps, how recently the data is accessed, Table level, row level, Partition level heat maps
In memory scan without expanding/extracting the compressed data which allows the retrieval of data more faster

Performance Enhancements:-

Adaptive statistics collection

Actual number of rows <> estimates
– Statistics marked as ‘incorrect’ / ‘unreliable’
– Next query will do ‘dynamic sampling’
• Results in much better estimates
• Better execution plans

Adaptive execution plans (not adaptive cursor sharing)

– E.g. 2 options in execution plan
• Join using nested loops
   – Best when few rows need to be joined
• Join using hash join
   – Best when a lot of rows need to be joined
   – “Inflection point”
• Rows are buffered during execution of query
• Inflection point reached or not?: take plan 1 or 2
– Result: “deferred execution plan”

-Thanks

Geek DBA

Do you know about asmdf?

If someone ask you that how to know about asm diskgroups without logging to asm, then answer this. ASM datafile system (ACFS?)

rac2$ asmdf
Filesystem SizeKB UsedKB AvailKB Use% Mounted on
+DATA01 15687104 10959872 4727232 60.56% DATA01

This works really, but no documentation at all. If some one knows about this let me know.

-Thanks

Geek DBA

RAC: Recover lost voting and OCR Disks from backup

We have a lost voting disks issue yesterday, this occurs due to incorrect aliases or the device mapper persistent names have been changed after storage upgradation/migration.

CRS was not up and showing the error below Obviously the CSSD , cssd.log

No voting disks founds

So, we have lost the voting and OCR disks.

Here is the situational tasks. In normal condition (10g)

1) We know that OCR is backed up automatically.

2) Deconfigure and configure the CRS (long procedure)

From 11gr2 onwards, Before pen down the procedure some thing you must know is

1) Voting disk also will be backed up automatically to ocr backup. (Many of us does not know this)

2) Clusterware can be startup in exclusive mode i.e no crs (as you know crsd starts later along with other cluster resources)

3) OCR and voting disks are part of ASM, means you can recreate the diskgroups without CRS by manually starting the ASM and assign that diskgroup to CRS as OCR/Voting Disk

Okay, now see how this works, the situational tasks

Assuming you have not started the cluster stack and only hasd is running.

1) Start CRS in exclusive mode in any of the node.

        root@rac2 ~]# crsctl start crs -excl -nocrs
        CRS-4123: Oracle High Availability Services has been started.
        CRS-2672: Attempting to start 'ora.gipcd' on 'rac2'
        CRS-2672: Attempting to start 'ora.mdnsd' on 'rac2'
        CRS-2676: Start of 'ora.gipcd' on 'rac2' succeeded
        CRS-2676: Start of 'ora.mdnsd' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.gpnpd' on 'rac2'
        CRS-2676: Start of 'ora.gpnpd' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'
        CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.cssd' on 'rac2'
        CRS-2679: Attempting to clean 'ora.diskmon' on 'rac2'
        CRS-2681: Clean of 'ora.diskmon' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'
        CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded
        CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.ctssd' on 'rac2'
        CRS-2672: Attempting to start 'ora.drivers.acfs' on 'rac2'
        CRS-2676: Start of 'ora.drivers.acfs' on 'rac2' succeeded
        CRS-2676: Start of 'ora.ctssd' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.asm' on 'rac2'
        CRS-2676: Start of 'ora.asm' on 'rac2' succeeded
        CRS-2672: Attempting to start 'ora.crsd' on 'rac2'
        CRS-2676: Start of 'ora.crsd' on 'rac2' succeeded

2) Add the new disks to asm diskgroup

            oracle@rac2 ~]$ sqlplus / as sysasm
            SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 01 17:23:56 2013
            Copyright (c) 1982, 2009, Oracle. All rights reserved.

            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Real Application Clusters and Automatic Storage Management options

            SQL> create diskgroup DATA external redundancy disk 'ORCL:DATA' attribute 'COMPATIBLE.ASM' = '11.2';
            Diskgroup Created.

3) Identify the latest backup

            Identify the latest OCR backup.
            root@oracle ~]# /u01/app/11.2.0/grid/bin/ocrconfig -showbackup

            oracle      2013/06/01 09:00:30     /u01/app/11.2.0/grid/cdata/oracle/backup00.ocr
            oracle      2013/06/06 05:37:29     /u01/app/11.2.0/grid/cdata/oracle/backup01.ocr
            oracle      2013/06/06 01:37:27     /u01/app/11.2.0/grid/cdata/oracle/backup02.ocr
            oracle      2013/05/31 01:37:21     /u01/app/11.2.0/grid/cdata/oracle/day.ocr
            oracle         2013/05/24 13:37:19     /u01/app/11.2.0/grid/cdata/oracle/week.ocr

4) Restore the OCR from automatic backup

[root@rac2 ~]# ocrconfig -restore /u01/app/11.2.0/grid/cdata/oracle/backup00.ocr

5) Start the CRS in exclusive mode

            crsctl start res ora.crsd -init

            CRS-2672: Attempting to start 'ora.crsd' on 'rac2'
            CRS-2676: Start of 'ora.crsd' on 'rac2' succeeded

6) Replace the voting disk from automatic backup

            [root@rac2 ~]# crsctl replace votedisk +DATA
            Successful addition of voting disk ahshuehki8489020msjsjsj
            Successfully replaced voting disk group with +DATA.
            CRS-4266: Voting file(s) successfully replaced

7) Stop the crs and restart

$CRS_HOME/bin/crsctl stop crs -f

$CRS_HOME/bin/crsctl start crs

8) Start the crs in other nodes

$CRS_HOME/bin/crsctl start crs

9) Verify the cluster

        # $GRID_HOME/bin/crsctl check cluster -all

            rac1:
            CRS-4537: Cluster Ready Services is online
            CRS-4529: Cluster Synchronization Services is online
            CRS-4533: Event Manager is online
            rac2:
            CRS-4537: Cluster Ready Services is online
            CRS-4529: Cluster Synchronization Services is online
            CRS-4533: Event Manager is online

To note, just in case,

If your SPFILE of ASM is in ocr/votingdisk diskgroup, you need to create temporary parameters for asm and then start asm and then finally add this spfile to Datagroup.
<pre>
        Sample Parameters
         *.asm_power_limit=1
         *.diagnostic_dest='/u01/app/oragrid'
         *.instance_type='asm'
         *.large_pool_size=12M
         *.remote_login_passwordfile='EXCLUSIVE'

        SQL> create spfile='+CRS' from pfile='/tmp/asm_pfile.ora';

Hope this helps

Quick Question: What happens when root.sh run in rac installation?

Hello,

I am bumped on the following to answer in a exact sequential way, hence want to note it here.

While in a rac installation, when you run root.sh , what are the sequence of actions that take place?

Node 1
	Initial Setup 
	Setup OLR for storing Oracle local registry data
	Setup GPnP wallet and profile
	Setup and copy files for OHASD daemon (startup scripts rc.d etc)
	Start OHASD Daemon
	Copy required CRS resources for OHASD to start
	Start in Exclusive Mode and Configure Diskgroup
	Push GPnP Profile to Remote Node(s)
	Start Full Clusterware Stack
	Adding Clusterware Resources

Other Nodes
	Initial Setup  
	Get GPnP Profile
	Setup OLR for storing Oracle local registry data
	Setup and copy files for OHASD daemon (startup scripts rc.d etc)
	Start OHASD Daemon 
	Copy required CRS resources for OHASD to start
	Start Full Clusterware Stack
	Adding Clusterware Resources

-Thanks
Geek DBA

Just watch out for processess parameter in ASM instance

If you have an asm instances with multiple databases using it, you may just need to watch out for processess parameter, yes you heard it right

See below:-
ORA-00312: online log 1 thread 1: '+ASMDISK1/testdb/redo01.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/testdb/redo01.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes (100) exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes (100) exceeded

We are having a ASM instance where it serving 8 databases apparently using all the processes that defined in the ASM instance.
Oracle Documentation says, the processes calculation for ASM instance is as follows:-
The PROCESSES initialization parameter affects Oracle ASM, but the default value is usually suitable. However, if multiple database instances are connected to an Oracle ASM instance, you can use the following formula:

PROCESSES = 50 + 50*n

where n is the number database instances connecting to the Oracle ASM instance.

Well, the bottom line is you have to consider to change this parameter as and when you are going to add a database instance in the host where ASM resides.

-Thanks
Geek DBA

v$sqlfn_metadata: interesting view to check the functions that introduced

Hello,

While reading some documentation on dynamic views , I came across the v$sqlfn_metadata view which shows the function (ex: min,max,avg,> etc) that introduced in which version. May be all knows it but those who does not know about it.

Here is the snippet (edited one) for your reference.

	SQL> select name,version,disp_type, descr from v$sqlfn_metadata;

	NAME                           VERSION      DISP_TYPE     DESCR
	------------------------------ ------------ ------------- --------------------------------------------------
	>                              V6 Oracle    REL-OP        >
	< V6 Oracle    REL-OP        <
	>=                             V6 Oracle    REL-OP        >=
	< =                             V6 Oracle    REL-OP        <=
	=                              V6 Oracle    REL-OP        =
	!=                             V6 Oracle    REL-OP        <>
	OPTTIS                         V6 Oracle    NORMAL        INTERSECT
	OPTTUN                         V6 Oracle    NORMAL        UNION
	OPTTMI                         V6 Oracle    NORMAL        MINUS
	OPTTAD                         V6 Oracle    ARITHMATIC    +
	OPTTSU                         V6 Oracle    ARITHMATIC    -
	OPTTMU                         V6 Oracle    ARITHMATIC    *
	OPTTDI                         V6 Oracle    ARITHMATIC    /
	OPTTNG                         V6 Oracle    ARITHMATIC    -
	AVG                            V6 Oracle    NORMAL        AVG
	SUM                            V6 Oracle    NORMAL        SUM
	COUNT                          V6 Oracle    NORMAL        COUNT
	MIN                            V6 Oracle    NORMAL        MIN
	MAX                            V6 Oracle    NORMAL        MAX
	OPTDESC                        V6 Oracle    NORMAL         DESC
	TO_NUMBER                      V6 Oracle    NORMAL        TO_NUMBER
	TO_CHAR                        V6 Oracle    NORMAL        TO_CHAR
	NVL                            V6 Oracle    NORMAL        NVL
	CHARTOROWID                    V6 Oracle    NORMAL        CHARTOROWID
	ROWIDTOCHAR                    V6 Oracle    NORMAL        ROWIDTOCHAR
	OPTTLK                         V6 Oracle    REL-OP         LIKE
	....
	..

Continue reading v$sqlfn_metadata: interesting view to check the functions that introduced

All about Database Administration, Tips & Tricks

New Features for DBA’s

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Pages

Recreate the trace files if they have been deleted accidentally?

Quick Question: Does dbms_stats collects stats for invisible indexes

11gr2 : Parallel Hint

An oldie: srvconfig 9i rac days, still useful in 11gr2

12c Database: New Features List 2

Do you know about asmdf?

RAC: Recover lost voting and OCR Disks from backup

Quick Question: What happens when root.sh run in rac installation?

Just watch out for processess parameter in ASM instance

v$sqlfn_metadata: interesting view to check the functions that introduced

New Features for DBA’s

Follow Me!!!

Subscribe to Posts by Email

Subscriber Count

Disclaimer

Recent Posts

Categories

Archives

Tags

Pages