What Is That Number That Appears On The Segment_name?

This morning perform a query on the DBA_SEGMENTS, some tablespaces had a segment of type SPACE_HEADER and segment_name a number, as in the example below. In the event that there is this number, this indicates that the tablespace was migrated from ‘dictionary managed’ to ‘locally managed’. This is done with the ‘dbms_space_admin.tablespace_migrate_to_local ()’ package.

The space header segment contains the extent bitmap and is allocated during the migration of the tablespace. Since there is no reserved space. In the case of news tablespaces it is created space for bitmap.

ORA10*ANTO> select segment_name, segment_type from dba_segments where segment_name ='1206.91302'
  2  ;

SEGMENT_NAME          SEGMENT_TYPE
_____________________ _____________

...
1206.91302            SPACE HEADER
...

HTH – Antonio NAVARRO

Advertisements

Adding Oracle Agent As A Resource To The Clusterware

Normally when we have an agent we usually include it in the starting and stopping of the machine. This is usually correct in systems standalone, but if we have a CRS it is not perhaps the most appropriate. It is more serious include the agent as a resource in the CRS itself, this We provides a number of advantages, such as controlling agent to be available at all times and the startup automatic of the agent If it falls. In this example it is assumed that each node in the cluster has its own agent, the agent will never travel (from a node to other one), it will be a resource that resides only on a machine.

Another option is to have a single agent and clusterware used as an active/passive, and the agent is associated with the resource database and is promoted between all the machines that are part of the cluster.

The following script, perl, which performs three basic actions that need the clusterware to run. These are

  • start; to start the resource
  • stop; to stop the resource
  • check; to check if resource available
#!/usr/bin/perl

# Home for binaries
$ORACLE_HOME = "/agent11/";

if ($#ARGV != 0 ) {
        print "usage: start stop check required \n";
exit;
}
$command = $ARGV[0];

# Start Agent
if ($command eq "start" ) {
        system ("
        ORACLE_HOME=$ORACLE_HOME
        export ORACLE_HOME

        $ORACLE_HOME/bin/emctl start agent" );
        exit 0;
}
# Stop Agent
if ($command eq "stop" ) {
        system ("
        ORACLE_HOME=$ORACLE_HOME
        export ORACLE_HOME

        $ORACLE_HOME/bin/emctl stop agent" );
        exit 0;
}
# Check Agent
if ($command eq "check" ) {
        system ("
        ORACLE_HOME=$ORACLE_HOME
        export ORACLE_HOME

        $ORACLE_HOME/bin/emctl status agent" );
        
        if ( $? == -1 )  ### system command fails
        {
          exit 1;
        }
        else  ### system command ok
        {
          $ExitCode = $? >> 8;
          printf "the exicode es ; %d", $ExitCode;
          if ($ExitCode == 0)      ### emctl status return agent is up     
          {
             exit 0;
          }
          else   ### emctl status return agent is ko
          {
             exit 1;
          }
        }
}

HTH – Antonio NAVARRO

What Advisors Are Available

Many times we need to know that counselors are available in a database, depending on the version will be more or less. But looking at the view dba_advisor_definitions know the ones.

Executing the next code in Oracle  11g;

ORA11*ANTO> select * from dba_advisor_definitions;

ADVISOR_ID ADVISOR_NAME                     PROPERTY
__________ ______________________________ __________
         1 ADDM                                    1
         2 SQL Access Advisor                    271
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                    935
         5 Segment Advisor                         3
         6 SQL Workload Manager                    0
         7 Tune MView                             31
         8 SQL Performance Analyzer              935
         9 SQL Repair Advisor                    679
        10 Compression Advisor                     3

10 filas seleccionadas.

The same query for Oracle Version 10g

ORA10*ANTO> select * from dba_advisor_definitions;

ADVISOR_ID ADVISOR_NAME                     PROPERTY
__________ ______________________________ __________
         1 ADDM                                    1
         2 SQL Access Advisor                     15
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                      7
         5 Segment Advisor                         3
         6 SQL Workload Manager                    0
         7 Tune MView                             31

7 filas seleccionadas.

HTH – Antonio NAVARRO

Rebuild An Index Costs Twice

Today has failed the rebuild of an index with ORA-01652 error. The point is to rebuild an index is more expensive in space that delete and recreate it. A reconstruction maintains the old index while creating the new. If we have an index that holds 20 gigabytes and rebuild, we will have in time given two indexes of 20 gigabytes each. If you delete and rebuild have only 20 gigabytes, of course, you need between 1.5 and 2.5 of size the index temporarily while it is rebuilding.

HTH – Antonio NAVARRO

OPatch Failed With Error Code 73

I have to install a patch on a database, by running the following command to see what’s in the inventory

opatch lsinventory -all

I get the following error;

JUPITER/orasiebel/oracle/product/10.2.0/db_1/OPatch => opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /orasiebel/oracle/product/10.2.0/db_1
Central Inventory : /agentgc/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /orasiebel/oracle/product/10.2.0/db_1/oui
Log file location : /orasiebel/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-14_15-18-06PM.log

List of Homes on this system:

  Home name= agent10g, Location= "/agentgc/OracleHomes/agent10g"
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

The problem in this case is in inventory, which does not exist, someone may have deleted by mistake. The solution is to run the installer with the option -attachHome or from version 10.2.0.2 or higher can run the script attachHome.sh as in the example below;

JUPITER/orasiebel/oracle/product/10.2.0/db_1/oui/bin => ./attachHome.sh
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The inventory pointer is located at /var/opt/oracle/oraInst.loc
The inventory is located at /agentgc/oraInventory
'AttachHome' was successful.

If we run again “opatch lsinventory -all” get the following output, which is right.

JUPITER/orasiebel/oracle/product/10.2.0/db_1/OPatch => opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

Oracle Home       : /orasiebel/oracle/product/10.2.0/db_1
Central Inventory : /agentgc/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /orasiebel/oracle/product/10.2.0/db_1/oui
Log file location : /orasiebel/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-14_15-21-46PM.log

Lsinventory Output file location : /orasiebel/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2015-05-14_15-21-46PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 2 products installed in this Oracle Home.

Interim patches (15) :

Patch  7609058      : applied on Thu May 07 16:00:23 CEST 2009
   Created on 31 Dec 2008, 00:42:37 hrs PST8PDT
   Bugs fixed:
     7609058

Patch  7609057      : applied on Thu May 07 16:00:13 CEST 2009
   Created on 31 Dec 2008, 01:17:11 hrs PST8PDT
   Bugs fixed:
     7609057

Patch  7592346      : applied on Thu May 07 16:00:07 CEST 2009
   Created on 31 Dec 2008, 01:23:22 hrs PST8PDT
   Bugs fixed:
     7375644, 7150470, 7592346

Patch  7375617      : applied on Thu May 07 16:00:02 CEST 2009
   Created on 17 Sep 2008, 05:02:15 hrs PST8PDT
   Bugs fixed:
     7375617

Patch  7375613      : applied on Thu May 07 15:59:57 CEST 2009
   Created on 17 Sep 2008, 05:02:13 hrs PST8PDT
   Bugs fixed:
     7375613

Patch  7375611      : applied on Thu May 07 15:59:52 CEST 2009
   Created on 17 Sep 2008, 05:02:09 hrs PST8PDT
   Bugs fixed:
     7375611

Patch  7197583      : applied on Thu May 07 15:59:42 CEST 2009
   Created on 2 Jul 2008, 10:06:43 hrs PST8PDT
   Bugs fixed:
     7197583

Patch  7155254      : applied on Thu May 07 15:59:31 CEST 2009
   Created on 2 Jul 2008, 10:06:14 hrs PST8PDT
   Bugs fixed:
     7155254

Patch  7155253      : applied on Thu May 07 15:57:34 CEST 2009
   Created on 2 Jul 2008, 10:05:45 hrs PST8PDT
   Bugs fixed:
     7155253

Patch  7155252      : applied on Thu May 07 15:55:59 CEST 2009
   Created on 2 Jul 2008, 10:05:15 hrs PST8PDT
   Bugs fixed:
     7155252

Patch  7155251      : applied on Thu May 07 15:55:49 CEST 2009
   Created on 2 Jul 2008, 10:04:30 hrs PST8PDT
   Bugs fixed:
     7155251

Patch  7155250      : applied on Thu May 07 15:55:38 CEST 2009
   Created on 2 Jul 2008, 10:03:58 hrs PST8PDT
   Bugs fixed:
     7155250

Patch  7155249      : applied on Thu May 07 15:55:25 CEST 2009
   Created on 2 Jul 2008, 10:03:27 hrs PST8PDT
   Bugs fixed:
     7155249

Patch  7155248      : applied on Thu May 07 15:55:20 CEST 2009
   Created on 2 Jul 2008, 10:01:21 hrs PST8PDT
   Bugs fixed:
     7155248

Patch  7172752      : applied on Mon Mar 16 15:13:06 CET 2009
   Created on 2 Feb 2009, 11:19:35 hrs PST8PDT
   Bugs fixed:
     7172752

--------------------------------------------------------------------------------

OPatch succeeded.

HTH – Antonio NAVARRO

“enq: CR – block range reuse ckpt” When Purge Recyclebin

This morning I cleaned the recycle bin with the command below. This statement tells Oracle to empty all the “trash”.

purge dba_recyclebin;

During the execution of the statement and because it has taken a long time, I’ve been monitoring it to. I have seen very many expect the type;

enq: CR – range block reuse ckpt

Before you delete or truncate a table, we need a level segment checkpoint. This is because it must maintain the consistency of the there may be in the buffer memory and what’s on the disc. Of course, do not seem to make much sense that something that is in the Recycle Bin is in memory, unless it has been recently deleted.

HTH – Antonio NAVARRO

KewastUnPackStats(): Bad Magic 1 (0xXXXXXXXXX, 0)

Today I have seen the net error in the alert.log;

kewastUnPackStats(): bad magic 1 (0xffffffff7a3bbdf8, 0)

After a working looking for this error at MOS. I see that the problem occurs where querying the (G)V$ACTIVE_SESSION_HISTORY view, as shown below;

select * from v$active_session_history;

It is the bug 8730312 and the solution is apply Patch 8730312, only avaliable for any enviroments. The best option is upgrade database if possible.

HTH – Antonio NAVARRO