Capacity Specs for SQL Server

Recently referenced the Maximum Capacity Specifications for SQL Server information.  The link provides information for SQL Server 2008 R2, 2008, 2005, and Denali.

SQL Server Versions

Update 27-Oct-2011

Found more recent build information on SQLSecurity.com and sqlserverbuilds.blogspot.com.

********************************************

Found this helpful information regarding SQL Server versions and service packs from Bill Graziano on SQLTeam.com.  Posting the SQL Server 2008 R2 information for my future reference, however Bill lists this and prior SQL server versions as well.

SQL Server 2008 R2
10.50.1765.0	SQL Server 2008 R2 CU6	21 Feb 2011
10.50.1753.0	SQL Server 2008 R2 CU5	20 Dec 2010
10.50.1746.0	SQL Server 2008 R2 CU4	18 Oct 2010
10.50.1734.0	SQL Server 2008 R2 CU3	17 Aug 2010
10.50.1720.0	SQL Server 2008 R2 CU2	25 Jun 2010
10.50.1702.0	SQL Server 2008 R2 CU1	18 May 2010
10.50.1600.1	SQL Server 2008 R2 RTM	12 Apr 2010

Windows OS Versions

Recently been referring to this information in the MSDN library.  Decided to post in my blog for easy reference in the future.

Operating system	Version number
Windows 7	6.1
Windows Server 2008 R2	6.1
Windows Server 2008	6.0
Windows Vista	6.0
Windows Server 2003 R2	5.2
Windows Server 2003	5.2
Windows XP 64-Bit Edition	5.2
Windows XP	5.1
Windows 2000	5.0

Database Crash with Tablespace in Hot Backup

I encountered this problem twice before a few years ago and most recently last week.  Adding this to my notes when it happens again a few years from now.

Snippet from alert log showing database crashed.

Fri May 27 19:07:27 Eastern Daylight Time 2011
Errors in file i:\oradba\admin\pd102\bdump\pd102_pmon_1464.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [kksCursorFreeCallBack+112] [PC:0x71E70C] [ADDR:0x68] [UNABLE_TO_READ] []

Fri May 27 19:07:45 Eastern Daylight Time 2011
LGWR: terminating instance due to error 472
Fri May 27 19:07:59 Eastern Daylight Time 2011
Instance terminated by LGWR, pid = 1508

Login to restart the database.

> sqlplus "sys as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Fri May 27 20:52:14 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Enter password:
Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size                  2077040 bytes
Variable Size             201330320 bytes
Database Buffers          939524096 bytes
Redo Buffers               14696448 bytes
Database mounted.

ORA-01113: file 8 needs media recovery if it was restored from backup, or END BACKUP if it was not
ORA-01110: data file 8: 'I:\ORADATA\PD102\TMF43WT.DBF'

I reviewed the alert log and was able to determine the data file specified was in backup mode when the database terminated.  Following the instructions in Oracle Support Doc ID 183367.1, I executed the commands below to resolve the problem.

SQL> alter database datafile 'I:\ORADATA\PD102\TMF43WT.DBF' end backup;

Database altered.

SQL> alter database open;

Database altered.

References
Common Causes and Solutions on ORA-1113 Error Found in Backup & Recovery (Doc ID 183367.1)

AT STARTUP AFTER CRASH WITH TABLESPACE(S) IN HOT BACKUP *******************************************************
1. Mount the database.

   SQL> STARTUP MOUNT;

2. Find out which datafiles were in hot backup mode when the database crashed or was shutdown abort or the machine was rebooted by running the query:

   SQL> SELECT V1.FILE#, NAME
FROM V$BACKUP V1, V$DATAFILE V2
WHERE V1.STATUS = 'ACTIVE' AND V1.FILE# = V2.FILE#;

3. For each of the files returned by the above query, issue the command:

   SQL> ALTER DATABASE DATAFILE '<full path name>' END BACKUP;

4. Open the database.

   SQL> ALTER DATABASE OPEN;

Expired (Grace) Oracle Account

A user indicated he was getting the error below, but could still connect.

ORA-28002: the password will expire within 7 days

In Oracle 11g, the default expiration date for the “DEFAULT” profile has changed from UNLIMITED to 180 days.  Changed the setting with the SQL below.

ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME unlimited;

Note:  This change only affects accounts who have not entered their grace period.  Accounts that have entered their grace period will have to change their passwords.

Must make a note in my database creation scripts to adjust this setting to UNLIMITED.  Not saying this is a good decision for a production database, but for my development and lab databases this will eliminate the problem with an expiring account.

Installing Oracle10gR2 Database on Windows 2008 R2

Oracle Database 10g R2 is certified on Windows 2008 R2 with the stipulation that you run 10.2.0.5.  However, the 64-bit Oracle base version (10.2.0.4) encounters an error when running setup.exe.

Click image to see larger view

Modified the “Certified Versions” section of the oraparam.ini located in I:\_oratemp\database\install.  Added “,6.1″ to the end of the Windows parameter.

[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0,6.1

Executed the Oracle Installer again allowing me to proceed with the installation.

Click image for larger view

Two more errors are encountered in OUI Product-Specific Prerequisite Checks, however you can check off in the checkbox and the status is changed to “User Verified” and OUI allows you to continue with the installation.

Click image for larger view

Reference
Installing Oracle Database Client 10g Release 2 (10.2) on a Windows 2008 R2 x64

SQL*Plus Permission Denied

After installing Oracle 11g Client on RHEL 5.4, I encountered a “permission denied” error while attempting to invoke SQL*Plus.  This occurred on two separate installations one 11gR1 and the other 11gR2.

First, the 11gR1 installation with the following symptoms:

Non-oracle user is trying to login to database via SQL*Plus, but attempts to access sqlplus result in a permission denied error.  Execution of sqlplus as oracle user works fine.

This is explained in Oracle Support Doc ID 1223449.1.

Cause
There is a lack of ‘read “r”‘ and ‘execute “x”‘ permissions on an upper directory which was not allowing access to sqlplus file.

Without execute permission on a directory, a user cannot access files in that directory even if they own them and have all permissions on them.

Solution
Add “r-x” permissions to “others” on upper directory (ex. /u01/app).

Following the recommendation above, I executed the commands below to successfully remediate the problem.

% cd /u01/app
% chmod 755 oracle

For the 11gR2 installation, I changed the permission as suggested, however the error below arises:

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

An explanation is provided in Oracle Support Doc ID 1206898.1.

Cause
The environment variable LD_LIBRARY_PATH was not set to include the 11g Client directory or the environment variable was set incorrectly.

Solution
Set the environment variable LD_LIBRARY_PATH to include the 11g Client directory.

Following the instructions mentioned above resolved my problems and all is functioning well.