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

Know How SQL Server Executes a Query in Short

The present blog explains the entire process as to how MS SQL server, the client-server platform executes a query request, issued by user. Sendingrequests, which is the only means of communication with the server database can be achieved by the use of some specific in-built commands that the database in SQL server understands. The reason for acquiring the way SQL server executes a query is that it shall assist developers to write better coding for the database. Though understanding requires a complete know how and is a lengthy task, an attempt has been made to make users acquaint with the process in short by means of the image displayed below.

Summary of ‘Query Execution’ Procedure:  The complete procedure ofquery execution can be summarized as below:

                                                                                          

Step 1: At the very beginning a Request is sent, the Request in turn creates a new Task. In fact this step involves Creation of Task.

Step 2: If workers are not free to take up a Task for execution, a queue of Task gets formed and remains in pending state for execution.

Step 3: When any Worker in the threadpool becomes free after the execution of any Task, the idle Worker then picks up a pending Task. This step is all about an idle worker picking up a Pending Task.

Step 4: This is the Task Execution step of the query execution process. Here, firstly an execution plan is compiled. This in turn involves parsing, compilation and optimization. Thereafter, Query plan is executed where the operators access data through the buffer pool.

Step 5: Then, Result set is returned during execution.

Step 6: Finally, at the end the Task gets executed and complete. The involved Worker returns to idle state and the process gets over.

The process of SQL Server execution of queries can be understood easily by the below mentioned steps.

1.    Creation of ‘Request’

At the very beginning when a new request is completely sent over the Tabular Data Stream (TDS), a protocol that is used for communication between the sender’s application and the server database; task is created by server database engine for handling the placed request. Once the request gets formed it can take any one of the three below mentioned forms.

1.    Batch Request

2.    Remote Procedure Call Request

3.    Bulk Road Request

Note - The list of requests in the server can be queried fromsys.dm_exec_requests.

2.    Creation of ‘Task’

The task that is created to handle the request represents the request right from the start to end. When a new request reaches the server and then the task that is created to handle the particular request remains in ‘pending’ state. The reason being, that at the current stage the server has no clue as to what exactly the request signifies. Thus, a pile of tasks gets formed and remains in queue. In such a case the task has to be executed first, for which the server database engine should assign a worker to it.

Note - The list of tasks in the server can be attained by sys.dm_os_tasksquery. 

3.    Assigning ‘Worker’ a Task

Workers are one of the main components and actually the threadpool of SQL Server. Multiple of them are created in the beginning i.e. at the very start of the server. Although more of them can be formed on-demand up to a limit of configured maximum worker threads. All the workers remain in waiting state for the ‘pending’ tasks to become available, from requests coming into the server. Thereafter, each idle worker picks up exactly one task and executes it. In the mean time all the engaged worker remains unavailable until the execution of task gets over. Therefore, Tasks that remain in ‘pending’ state due to lack ofavailable workers, shall have to stay pending until execution of any task gets complete and the worker that executed the particular task becomes available.

Note - The lists and state of workers inside SQL Server can be checked by querying sys.dm_os_workers.

4.    Parsing and Compilation

Thereafter, an execution plan is complied. In this step, before a Task starts executing a Request the first thing it needs to do is to gain a proper understanding about the content of the specific Request. The T-SQL text that exists inside the request gets parsed and an abstract syntax tree gets formed for representation of the particular Request. Actually, the conclusion is that all the existing Requests are parsed and thereby compiled in this step. However, in case any error occurs at this stride the Requests gets terminated with a compilation error.

5.    Optimization

Optimization is the next important phase in the complete life cycle of Task Execution, so as to select an optimum data path access. In case of SQL server, the best way of optimization is chosen by first observing the costs of each possible alternative. Thereby, the alternative with the lowest cost is chosen as the query plan to be utilized, to make the process economical. It is quite obvious that exploring all the possible alternatives consumes much time. Hence, once a query plan is created it is also cached, for use in future. So, similar Requests that might be requested in future can skip over the optimization phase; provided an already compiled and optimized query plan is found in the internal cache of SQL Server. 

6.    Execution & Result

Finally, after compilation the Requests gets executed, the Task gets completed and the Worker becomes available and free to pick up another Task in pending condition. Once a query plan is selected by the Optimizer, the ‘Request’ or say the ‘Query plan’ can be executed. Actually, this is the last important step as to how SQL server executes a query. Finally, the Result set is given back which is the end of the process.

Migrate MySQL Databases, Users, and Privileges to Different Server

1.) Databases:

• Get list of databases

1	mysql -uroot -proot_password -e "show databases"

• Dump all databases

1	mysqldump -uroot -proot_password --all-databases > databases.sql

Note: you can dump only the databases that you want to move. Also, you need to exclude the “mysql” database itself if you are moving into an mysql server with existing records.

* Transfer sql dumps to new server with your preferred way (ftp, scp, rsync, etc)

• Create Databases on new server.

1	mysql -uroot -proot_password -e "create database database_name_1;"

1	mysql -uroot -proot_password -e "create database database_name_2;"

1	mysql -uroot -proot_password -e "create database database_name_3;"

• Import database dumps to new server

1	mysql -uroot -proot_password < databases.sql

2.) Database Users and Privileges:

• Get list of database users and their privileges.

1	mysql -B -N -uroot -proot_password -e "SELECT user, host FROM user" mysql

• Then get the privileges and password hash for each users from the above step.

1	mysql -B -N -uroot -proot_password -e "SHOW GRANTS FOR 'db_user'@'db_host'"

Note: Above command will output similar to these two lines or you will get more if the db_user has access to more than one databases.

1	GRANT USAGE ON *.* TO 'db_user'@'db_host' IDENTIFIED BY PASSWORD '*23AW9301879BCC8BC24343CDCDC40F375606C43D'

1	GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'db_host'

• Then you need to execute all these lines or queries on the new MySQL server. You can put them all in a text file (example: users.txt) and add semi-colon on each line, then import to mysql (example below):

1	mysql -uroot -proot_password < users.txt