Systems Engineering and RDBMS

Archive for February 12th, 2007

Oracle 10gR2 upgrade

Posted by decipherinfosys on February 12, 2007

Oracle documentation on the 10g Release 2 upgrade is pretty nice but we ran into couple of glitches when doing the upgrade at a client site and here is a cheat sheet that might help you in case you are running into similiar issues:

Pre-Upgrade

• 10g release2 must be installed on new home. It can not be used to upgrade the current ORACLE_HOME
• Pre-upgrade script available in R2 which will suggests the parameter changes/deprecated parameters etc.
• Purge the dba recycle bin as SYS. Purge dba_recyclebin;
• Gather dictionary stats are required after upgrade: exec dbms_stats.gather_dictionary_stats;
• Run utlrp to recompile all invalid objects before and after upgrade.
• You need root privilege to run root.sh and to stop the CSS service on DB server.
• Move all the trace files from bdump and udump location to archive directory and make the alert file null echo > alertSID.ora. This is helpful to monitor the upgrade related errors easily.
• There are many memory errors during the upgrade process. Set the following parameters (Metalink ID: 330239.1)

alter system set “_kgl_large_heap_warning_threshold”=16777216
scope=spfile ;

During the Upgrade process:

• DBUA utility will perform upgrade process. First upgrade the database and then ASM instance.
• Upgrade process will take time and appears to be hanged some time. Always check the runtime progress at location $ORACLE_HOME/cfgtoollogs/dbua
• If you suspect that upgrade is stuck up, do tail –f on the alert file. Continuous switching of log files indicates that DB activity is happening in background. During recompilation and gather stats, an upgrade log file does not grow, however alert file will indicate the background activity.

Post Upgrade:

• Run utlrp to recompile all invalid objects.
• Run gather_stats on all schemas.
• Re-create the MV definitions in all schemas.
• Check alert log and other trace files.
• Execute following script to tackle exports related errors Metalink ID: 339938.1

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

• CONNECT has been replaced with create session privilege which means we need to grant these privileges explicitly to all users.

Better way to create XYZ_ROLE role and grant that to all users;

/* Replace XYZ with your preferred name */

GRANT CREATE VIEW TO XYZ_ROLE;
GRANT ALTER SESSION TO XYZ_ROLE;
GRANT CREATE SYNONYM TO XYZ_ROLE;
GRANT CREATE SEQUENCE TO XYZ_ROLE;

• Handling ORA-3136 errors: Metalink ID: 345197.1

The Oracle Net 10g parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 (indefinite) in 10.1.  To address Denial of Service (DOS) issues, the parameters were set to have a default of 60 (seconds) in Oracle 10.2. If applications are longer than 60 secs to authenticate with the Oracle database, the errors occur.  The following may be seen in the alert log:

WARNING: inbound connection timed out (ORA-3136) SQLNET.INBOUND_CONNET_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

Add these parameters on the SERVER side: value in seconds.

listener.ora: INBOUND_CONNECT_TIMEOUT_listenername =240
sqlnet.ora: SQLNET_INBOUND_CONNECT_TIMEOUT=240

Posted in Oracle | Leave a Comment »

DAC (Dedicated Admin Connection) in SQL Server 2005

Posted by decipherinfosys on February 12, 2007

In versions prior to SQL Server 2005, if the server had maxed out on the resources, had a spinloop or maybe had corruption, it was impossible to get access to the server via remote desktop, Enterprise Manager, Query Analyzer, osql, third party tools etc.  As a result, the last resort that most of the database developers/DBAs adopted was “REBOOT” 🙂  But that is not the holy grail.  At times, SQL Server was able to do a checkpoint process and shutdown gracefully but at other instances, due to to a shutdown and subsequent start-up and recovery phase, corruption or AVs (Access Violations) could have resulted not to mention that the recovery process could be a long one depending upon the amount of work.  In order to remedy this situation, in SQL Server 2005, one has a Dedicated Admin Connection option.  This option (when configured) allows the developer/DBA to connect to the instance and issue T-SQL commands to troubleshoot the issue – maybe killing the offending process as a last resort.

Please note that by default, this option is available only from the client on the server.  In order to enable the remote clients to use the DAC option, you can enable this option using the surface area configuration option:

dac_1.JPG

This can also be done usinng sp_configure command with the “remote admin connections” option being set to 1.

In order to use this option, you can either access it from the command prompt by using the new sqlcmd utility or through the Management Studio by using the ADMIN: option when connecting to the instance.  Example:

Using sqlcmd:

sqlcmd -A -d testDB -E -S  testinstance\namedinstance

-A is for the DAC

-d is for the database

-E is for integrated security

-S is for the instance (in this example, a named instance)

Management Studio:

a) Connect using the “Database Engine Query” option.

b) Put “ADMIN:” before the “testinstance\namedinstance”

c) Provide the connection information and connect.

Once you connect using DAC, you can execute any T-SQL commands to troubleshoot the issue and find the offending process.

Posted in SQL Server | 2 Comments »

VMWare ESX Server Basics

Posted by decipherinfosys on February 12, 2007

VMWare ESX Server is a thin layer of software designed to distribute hardware resources efficiently among virtual ‘guest’ machines running their own operating systems. Individual physical servers are frequently underutilized, and the cost of acquiring these servers and not use them to their full potential is not efficient or cost-effective. ESX Server, which is VMWare’s premier virtualization product, is designed to maximize the utilization of an organization’s physical hardware. Underutilized physical servers of any size can be virtualized with little or no impact to performance. Also, large numbers of small servers can be consolidated onto fewer larger machines to simplify management and reduce cost.

ESX Server is basically a simple UNIX-like operating system. The initial installation GUI will be quite familiar to anyone who has installed Linux Red Hat, as the ESX Server installer’s installer is very similar to the popular Linux OS. Once installed, administration is handled via a comprehensive HTTPS web console.

Typical ESX Server host machines in production environments are multi-processor systems that host multiple instances of various operating systems. Each of these instances is known as a guest operating system, and runs one or more applications like any other server. The ESX server allocates CPU and memory resources to these guests on an as-needed basis, and dynamically adjusts these resources across all of the guest servers. ESX Server also runs several processes that perform various maintenance activities, and the physical CPUs are shared by all of the ESX Server’s functions as well as the virtual machines.

I have been using various VMWare products for more than 7 years, and am convinced of the capabilities and benefits of virtualization. The popularity of server virtualization is rapidly growing in popularity, and every organization owes it to itself to investigate the products for themselves. There will be many more posts on the topics of virtualization, with an emphasis on VWare’s products. VMWare was one of the pioneers of virtualiztion, and remains strong in the market segment. You can learn more about VMWare and virtualization in general by viewing their website at: http://www.vmware.com.

Posted in VMWare | Leave a Comment »