Oracle 11204 Clusterware upgrade - ASM glitch

Yet another tough challenge thrown at my team right after the disaster recovery (DR) simulation drill which performed barely couple of weeks ago. The new task (challenge) in hands is to upgrade the existing four cluster environments from 11.2.0.2 to 11.2.0.4 as Oracle already stopped supporting v11.2.0.2.

Although last week we had a 3 node successfully upgrade track record, we encountered ASM upgrade troubles whilst running rootupgrade.sh in a new cluster environment (7 nodes). The following error was reported during the course of rootupgrade.sh script execution:

CRS-2672: Attempting to start 'ora.asm' on 'node01' 
CRS-5017: The resource action "ora.asm start" encountered the following error: 
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter 
ORA-48140: the specified ADR Base directory does not exist [/u00/app/11.2.0/grid/dbs/{ORACLE_BASE}] 
ORA-48187: specified directory does not exist 
HPUX-ia64 Error: 2: No such file or directory 
Additional information: 1CRS-2674: Start of 'ora.asm' on 'node01' failed 
CRS-2679: Attempting to clean 'ora.asm' on 'node01' 
CRS-2681: Clean of 'ora.asm' on 'node01' succeeded 
CRS-4000: Command Start failed, or completed with errors. 

When tried to start-up the ASM instance manually through sqlplus prompt, the following error was thrown:

SQL> 
ORA-32004: obsolete or deprecated parameter(s) specified for ASM instance 
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter 
ORA-48140: the specified ADR Base directory does not exist [/u00/app/11.2.0/grid/dbs/{ORACLE_BASE}] 
ORA-48187: specified directory does not exist 
HPUX-ia64 Error: 2: No such file or directory

Sadly, there wasn't much info available about the nature of this problem. As usual, after giving it 1 hr try with different options, we opened a SR with Oracle support and agreed to rollback the upgrade from the node where the rootupgrade script failed. Luckily, this was the first node we tried and other 6 nodes were just running fine. After rolling back to the previous cluster version, ASM instance error was still persist.

To resolve the ASM instance startup issues, the following action was taken:

• export diagnostic_dest=/u00/app/oracle
• From active ASM instance on another node, executed the following statement:
• SQL> ALTER SYSTEM STOP ROLLING MIGRATION;

Cause:

The problem caused an ASM instance startup issue was reported/logged as a known bug (17449823).

Workaround:

According to the MOS Doc ID (1598959.1), the bug is still being worked by the development team, they suggest the following work around on each node just before running the rootupgrade.sh script:

• mkdir <New-GI-HOME>/dbs/{ORACLE_BASE} 

Third successful attempt

The upgrade failed in first 2 attempts, and the 3 attempt was successful and we managed to upgrade all 7 nodes from 11.2.0.2 to 11.2.0.4. It was also learnt that CRS_HOME, ORACLE_HOME, ORACLE_BASE was not unset before the runinstaller was initiated. In 3rd attempt with unsetting those parameters, upgrade went successfully.

Addendum (24-Aug-2014)
Couple of new challenges encountered in the last  upgrade task on 10 nodes.

1. OUI window from which runInstaller was initiated got closed due to PC rebooted.
2. Although the directory {ORACLE_BASE} created under the new GRID home, the issue were reoccurring.

Here is the solution:

1. How to Complete 11gR2 Grid Infrastructure Configuration Assistant(Plug-in) if OUI is not Available (Doc ID 1360798.1)
2. Ensure the diagnostic_dest is updated on ASM Spfile to the new location before running the rootupgrade.sh

References:

• Things to Consider Before Upgrading to 11.2.0.3/11.2.0.4 Grid Infrastructure/ASM ( Doc ID 1363369.1) 
• Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results ( Doc ID 1645862.1) 
• GI rootupgrade.sh on last node: ASM rolling upgrade action failed ( Doc ID 1598959.1) 
• bug 17449823

manual Upgrade from 10gR2 to 11gR1

Pre-Requisite:

You should have enough MEMORY, server space and SWAP SPACE on Oracle database 10g, which you want to migrate.

Also here we are upgrading 10g to Oracle Database 11g – Beta 6 (11.1.0.6)

Step 1) Installing Oracle 11g Home

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a separate ORACLE_HOME in parallel to 10g Oracle Home.

Step 2) Pre-Upgrade Utility

In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.

$  cp   utlu111i.sql   /tmp

For more about utlu111i.sql :

 http://oracleflash.com/38/Oracle-11g-Release-1-Pre-Upgrade-tool-utlu111i-sql.html

Step 3) Executing the recommended steps

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files:

SQL> select version from v$timezone_file;

          

          version

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

                   2  

here oracle 10.2.0.1 has timezone file version of 2

Before you upgrade your timezone file version, you must run utltzuv2.sql to see if there is data that will be affected by the timezone file version upgrade. The script utltzuv2.sql creates a table named sys.sys_tzuv2_temptab, and fills it with data that may get affected by the timezone file version.

SQL >  select * from sys.sys_tzuv2_temptab ;

           no rows selected

here we see no rows but if we see any rows then we need to take that data backup and restore after timezone file version upgrade

Step 4) Run Pre-Upgrade Utility again

Run utlrp.sql to validate the objects if there is any invalidate objects.

SQL> @?/rdbms/admin/utlrp.sql

Check for any INVALID Objects in the database before upgrade starts:

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

SQL> purge DBA_RECYCLEBIN

Gather dictionary stats to run UPGRADE process fast,

SQL> exec dbms_stats.gather_dictionary_stats;

Create pfile from spfile 

Shutdown  à shut down the database in oracle 10g.

Copy the pfile of database from ORACLE_HOME/dbs(10g) to ORACLE_HOME/dbs(11g) using cp command.

In that make changes shown below,

 1.     Remove BDUMP, CDUMP, UDUMP and their locations

 2.     Add Diagnostic_dest=/’$ORACLE_BASE/diag’-àlocation for diagnostic dest

 3.     Change audit_file_dest location

 4.     Change compatibility to 11g and save it.

After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings. If everything looks fine, shut down the database from 10g Oracle Home.

Step 5) Starting Upgrade

Source the following variables for 11g Oracle Home

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export PATH=$ORACLE_HOME/bin:$PATH[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export TNS_ADMIN=$ORACLE_HOME/network/admin

connected to the database sys as sysdba

sqlplus “/ as sysdba” –> will be connected to idle instance

SQL> startup upgrade

SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Once the upgrade finishes, It will shut down the database automatically.

Now export New oracle HOME and startup the database

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export ORACLE_HOME=/opt/oracle/11.1.0/db_1

[oracle@/opt/oracle/11.1.0/db_1/dbs]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export ORACLE_SID=orcl

[oracle@/opt/oracle/11.1.0/db_1/dbs]$  export TNS_ADMIN=$ORACLE_HOME/network/admin

Login again as sysdba and startup in normal mode.

Check the dba_registry for the components and its status

SQL> startup 

SQL> select substr(comp_id,1,10) comp_id, substr(comp_name,1,25) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

All the components should be VALID state

Step 6) Post-Upgrade Steps

Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.

SQL> STARTUP

Run utlu111s.sql to display/verify the results of the upgrade:

SQL> @?/rdbms/admin/utlu111s.sql

Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:

SQL> @?/rdbms/admin/catuppst.sql

Run utlrp.sql to recompile invalid objects after upgrade

SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(*),object_type,owner from dba_objects  where status = ‘INVALID’ group by object_type,owner;

Run utluiobj.sql after the upgrade to identify any new invalid objects

Edit /etc/oratab file with new 11g Oracle home and comment out 10g oracle home

Table Partitioning Implementation with advanced scenarios (Part 2)

Points covered in the last post :

• What is table partitioning?
• Why we need to use table partitioning?
• PROS and CONS of table partitioning?
• Partitioned Table Rules and Gotchas
• What is difference Table partitioning Type in SQL Server?

before going to the Technical demo we should know what is table partitioning dependence

PARTITION FUNCTION:

that maps the rows of a table or index into partitions based on the boundary_value , we can build the Partition Function by two ways based on our Requirement and our business needed

• RANGE RIGHT
• RANGE LEFT

another thing the boundary_value we can do it on three data type

• INT COLUMN
• DATETIME COLUMN
• CHAR COLUMN

PARTITION SCHEME:

that maps the partitions of a partitioned table or index to filegroups , partition scheme must be created on partition function , partition scheme can be used for one or more partitioned tables, indexes, and indexed views.

PARTITION TABLE/INDEX:

is tied to a particular partition scheme when it is created , partition table has only an indirect relationship, through the partition scheme, to the partition function at the end The relationship between a partition function and a partition scheme is one-to-many as is the relationship between a partition scheme and partitioned tables and indexes , because moving data in and out of a partitioned table usually requires modifying the partition function for more information :

• Partitioned Tables and Indexes in SQL Server 2005 at http://msdn.microsoft.com/en-us/library/ms345146.aspx
• Strategies for Partitioning Relational Data Warehouses in Microsoft SQL Server athttp://technet.microsoft.com/en-us/library/cc966457.aspx

let’s go now for the TECHNICAL DEMO to know How we can implement the table partitioning step by step from Scratch

How to Implement Partitioned Tables 

to implement any partitioned table from SCRATCH we have basic 6 steps we will do it now by T-SQLand by SQL SERVER WIZARD

• Create Database (T-SQL / Wizard)
• Create FILEGROUP (T-SQL / Wizard)
• Create files and add it to the FILEGROUP (T-SQL / Wizard)
• Create partition Function (RANGE RIGHT / RANGE LEFT) (T-SQL only)
• Create partition Scheme (T-SQL only)
• Create Table Partitioning (T-SQL only)

at the first i will do it by T-SQL

• Create database 

I will create new database [DBForPartitioning] to be for all our workshop to the end of the series

CREATE DATABASE [DBForPartitioning]

GO

ALTER DATABASE [DBForPartitioning]

MODIFY FILE ( NAME = N'DBForPartitioning', SIZE = 256MB , MAXSIZE = 10GB , FILEGROWTH = 512MB );

ALTER DATABASE [DBForPartitioning]

MODIFY FILE ( NAME = N'DBForPartitioning_log', SIZE = 128MB , FILEGROWTH = 128MB );

GO

• Create FILEGROUP

here we will create four filegroup (FG1 ,FG2 , FG3 , FG4) to can use it on the Partition Scheme

USE [master]

GO

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG1]

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG2]

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG3]

ALTER DATABASE [DBForPartitioning] ADD FILEGROUP [FG4]

GO

• Create / Add files to filegroup

in this step i will create four files and add it on the FILEGROUP and all of this i will do it dynamically

Use DBForPartitioning

Go

DECLARE @path NVARCHAR(256) ,

@i TINYINT= 1 ,

@sql NVARCHAR(4000);

SELECT TOP 1

@path = LEFT(physical_name, LEN(physical_name) - 4)

FROM sys.database_files

WHERE name = 'DBForPartitioning'

WHILE @i <= 4

BEGIN

SET @sql = N'ALTER DATABASE DBForPartitioning ADD FILE (name=File' + CAST(@i ASNCHAR(1)) + ',

filename=''' + @path + N'File' + CAST(@i AS NCHAR(1)) + '.ndf' + ''',

size=128MB, filegrowth=256MB) TO FILEGROUP FG' + CAST(@i AS NCHAR(1))

RAISERROR (@sql,0,0)

EXEC sp_executesql @sql;

SET @i += 1;

END

GO

Range Direction Right and Datetime Boundary Values

Next three steps i will do every thing on the RANGE RIGHT and RANGE LEFT so i will work on parallel

• Create partitioning function

in this case i will do partition function with boundary_value DATETIME and

Use DBForPartitioning

GO

CREATE PARTITION FUNCTION PF_DBForPartitioning_RangeRight (datetime)

AS RANGE RIGHT FOR VALUES ( '2008-01-01','2009-01-01', '2010-01-01');

• Create Partition Scheme

i will create Partition scheme on PF(PF_DBForPartitioning_RangeRight) on the four filegroup

</pre>

Use DBForPartitioning

GO

CREATE PARTITION SCHEME PS_DBForPartitioning_RangeRight

AS PARTITION PF_DBForPartitioning_RangeRight

TO (FG1, FG2, FG3 ,FG4)

GO

<pre>

• Create Table partitioning

now i will create table Partitioning on Partitioning Scheme [PS_DBForPartitioning_RangeRight] on datetime Column

Use DBFOrPartitioning

GO

CREATE TABLE Partitioning_RangeRight

(Partitioning_ID int ,

Partitioning_time datetime)

ON PS_DBForPartitioning_RangeRight (Partitioning_time);

GO

Now the 6 basic steps for any table partitioning is finished and i will go now for fill the table by some test data then i will show some wonderful Stored Procedures and Scripts it will return to us all the information about the Partition Function ,Partition Scheme and table Partition , ….etc

• Fill the data in the table 

</pre>

Use DBFOrPartitioning

GO

INSERT INTO Partitioning_RangeRight (Partitioning_ID, Partitioning_time)

SELECT 1 ,'2007-01-01'UNION ALL

SELECT 2 ,'2007-10-01'UNION ALL

SELECT 3 ,'2008-01-01'UNION ALL

SELECT 4 ,'2008-08-09'UNION ALL

SELECT 5 ,'2008-12-30'UNION ALL

SELECT 6 ,'2009-01-01'UNION ALL

SELECT 7 ,'2009-05-24'UNION ALL

SELECT 8 ,'2010-01-24'UNION ALL

SELECT 9 ,'2010-05-24'UNION ALL

SELECT 10,'2011-05-24'

GO

<pre>

Return Partition Information by T-SQL Scripts

• Select table Partitioned in DB

Use DBFOrPartitioning

GO

select distinct t.name

from sys.partitions p

inner join sys.tables t

on p.object_id = t.object_id

where p.partition_number <> 1

• Stored procedure to Return the Partitioned Columns

Exec partitioning_Column ‘Partitioning_RangeRight’

Create Proc partitioning_Column (@Table_Name Nvarchar(500))

AS

Begin

SELECT CAST(ic.partition_ordinal AS INT) AS [ID],

c.name AS ColumnName

FROM sys.tables AS tbl

INNER JOIN sys.indexes AS idx

ON idx.object_id = tbl.object_id

AND idx.index_id < 2

INNER JOIN sys.index_columns ic

ON (ic.partition_ordinal > 0)

AND (ic.index_id = idx.index_id

AND ic.object_id = CAST(tbl.object_id AS INT))

INNER JOIN sys.columns c

ON c.object_id = ic.object_id

AND c.column_id = ic.column_id

WHERE (tbl.name = @Table_Name

AND SCHEMA_NAME(tbl.schema_id) = 'dbo')

ORDER BY [ID]

END

• Select Partition_id with Partition_number

select partition_id, index_id, partition_number, Rows

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Partitioning_RangeRight'

GO

• Select the PartitionFunction and PartitionScheme Name

select ps.Name PartitionScheme, pf.name PartitionFunction

from sys.indexes i

join sys.partition_schemes ps on ps.data_space_id = i.data_space_id

join sys.partition_functions pf on pf.function_id = ps.function_id

where i.object_id = object_id('Partitioning_RangeRight')

• Stored procedure to return all the detail about table Partitioned and the data exist on it

Create proc PartitionTableDetail(@tablename Nvarchar(500))

As

begin

SELECT

ISNULL(quotename(ix.name),'Heap') as IndexName

,ix.type_desc as type

,prt.partition_number

,prt.data_compression_desc

,ps.name as PartitionScheme

,pf.name as PartitionFunction

,fg.name as FilegroupName

,case when ix.index_id < 2 then prt.rows else 0 END as Rows

,au.TotalMB

,au.UsedMB

,case when pf.boundary_value_on_right = 1 then 'less than' whenpf.boundary_value_on_right is null then '' else 'less than or equal to' End asComparison

,fg.name as FileGroup

,rv.value As Rang_value,

T.name AS Range_Type

FROM sys.partitions prt

inner join sys.indexes ix

on ix.object_id = prt.object_id and

ix.index_id = prt.index_id

inner join sys.data_spaces ds

on ds.data_space_id = ix.data_space_id

left join sys.partition_schemes ps

on ps.data_space_id = ix.data_space_id

left join sys.partition_functions pf

on pf.function_id = ps.function_id

inner join sys.partition_parameters PP

on pf.function_id = PP.function_id

inner join sys.types t

on t.system_type_id = PP.system_type_id

left join sys.partition_range_values rv

on rv.function_id = pf.function_id AND

rv.boundary_id = prt.partition_number

left join sys.destination_data_spaces dds

on dds.partition_scheme_id = ps.data_space_id AND

dds.destination_id = prt.partition_number

left join sys.filegroups fg

on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)

inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]

,str(sum(used_pages)*8./1024,10,2) as [UsedMB]

,container_id

from sys.allocation_units

group by container_id) au

on au.container_id = prt.partition_id

WHERE prt.OBJECT_ID = object_id(@tablename)

order by ix.type_desc;

END

Exec PartitionTableDetail 'Partitioning_RangeRight'

this not the end still i have lot of Scripts it will help any DBA to know what he need about Partitioning table in any Environment without much effort and without time only 5 SEC he can know every thing next post i will explain the below point

• Range Direction left and Datetime Boundary Values
• Table partitioning by SQL Server wizard
• new Scripts for investigation