Uninstalling SQL Server Service Pack for Clustered Instance

SQLServerF1

One of the important responsibility of the SQL Server Database Administrators(DBAs) is to plan and install SQL Server and its patches like Service Packs, Cumulative Updates, Hotfixes, etc. Although how much ever planning and testing was done prior to installation of the SQL Server patches, some times there could be unexpected issues or problems which will affect the applications or certain functionality and there comes a need to rollback the installation changes or to revert back to the before state. Older versions of SQL Server did not had much flexibility in rolling back the service pack changes by uninstallation, but the latest versions of SQL Server provides feature to uninstall SQL Server patches which may be service packs, CUs or Hotfixes. Below are some of the important steps to follow to uninstall SQL Server service packs or other patches starting with SQL Server 2008 R2.

Steps to Uninstall SQL Server Patches on Clustered instance. This is also applicable for standalone instances.
– On Passive node, open Programs and Features” options in Control Panel and click on “View installed updates”
– Highlight the SQL Server 20xx Service Pack x or related patches and click “Uninstall”
– Uninstall Service Pack wizard will start.
– Click “Next” which will run update rules then select the features for which you need to remove the Service Pack and click “Next”

– Go through the wizard and then verify the Summary and click “Remove”
– Monitor for the uninstallation to finish successfully.
– Reboot the node
– Failover SQL Server instance on to the node where the patch has been uninstalled.
– Test the application to make sure it works fine.
– Remove the patches from other passive nodes as well and reboot the nodes.

For older versions of SQL Server 2005 or before, you cannot uninstall using the above method. If the server is a VM, then you may restore the snapshot of VM before the patch install, but this needs to be tested before hand. The only supported and reliable method of rolling back SQL Server patches on SQL Server 2005 or prior is to follow the below steps

– Backup all the system and user databases. Make note of instance level configuration settings, jobs, logins, etc.
– Detach all user databases and copy to another location.
– Uninstall SQL Server 2005 instance
– Reboot the server
– Install new SQL Server 2005 instance with same name
– Apply the service pack or CU to same level to the same when the SQL Server instance was working fine.
– Restore Master and MSDB databases and restore Model database as well if required.
– Restore all user databases or attach the user databases using mdf and ldf files which were copied to another location before.
– Verify logins, jobs, instance level configuration, etc.
– Test the application to make sure everything works fine.

 

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Installation of SQL Server Patches on Clustered Instance

SQLServerF1

It is very important to regularly apply patches to the SQL Server instance using latest service packs or Cumulative Updates which contains important fixes specific to the SQL Server instances and applications. There needs good planning before applying the service pack or cumulative update to a SQL Server instance and also a series of steps need to be followed for applying the patches. Missing any of the steps may sometimes result in serious problems. Most often or not the installation of the patches completes successfully without any problems, but when it fails we should be in a position to be able to roll back the changes. In this article we will see the steps that are required to be followed before applying a service pack or Cumulative update or a hotfix to a particular SQL Server Cluster instance. Below are some of the important steps which are to be performed while applying service pack or cumulative update or hotfix for critical SQL Server instance on clustered instance. This is more often or not applicable to the standalone SQL Server instances as well. Also we will look into steps to verify or validate that the patches are installed properly and SQL instance is working fine after the patch installation.

Steps to Install patches to SQL Server Clustered Instance
– Copy the Service Pack or Cumulative Update or Hotfix to all the cluster nodes to which SQL Server can failover to.
– Run the patch by choosing Run-as-Administrator option on passive node. If there are multiple passive nodes, then start by running on one of the passive node.
– Follow the patch install wizard and start the installations and monitor for its successful completion.
– Reboot the node where the patch has been successfully applied.
– Stop all the applications from connecting to the SQL Server instance.
– Failover the SQL Server instance on to the node which has been patched.
– Test the applications to make sure there are no issues.
– Run the patch by choosing Run-as-Administrator option on the new passive node. Repeat the same on each remaining passive nodes.
– Reboot the nodes after patch has been applied on each node.
– Test the application.

Steps to Verify or Validate the installation of Service Pack or CU or hotfix for SQL Server
– Open cluster manager and verify that all the SQL Server and SQL Agent cluster resources, Disk resources, Network Name and IP address resources are online.
– Connect to SQL Server instance from SQL Server Management Studio and check the version and build number to ensure that the latest patch version is reflected.
– Check SQL Server error log to make sure there are no errors in errorlog.
– Check Event Viewer on all cluster nodes to make sure there are no errors after patch installation.
– Check the SQL Server patch installation log file to make sure there are no errors reported.
– Test the applications throughly.

This is applicable on below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Important Prerequisites Before Installing Service Pack to SQL Server Instance

SQLServerF1

It is very important for regularly update the SQL Server instance using latest service packs or Cumulative Updates with important fixes specific to the SQL Server instance and application. There needs some planning before applying the service pack or cumulative update to a SQL Server instance and also a series of steps need to be followed for applying the patches. Missing any of the steps may sometimes result in serious problems. Most often or not the installation of the patches completes successfully without any problems, but when it fails we should be in a position to be able to roll back the changes. In this article we will see about some important pre-requisite steps that needs to be followed before applying a service pack or Cumulative update or a hotfix to a particular SQL Server instance. Below are some of the important prerequisite tasks which are to be taken care before installing service pack or cumulative update or hotfix for critical SQL Server instance on standalone or clustered instance.

– Patches which may be Service Pack or Cumulative Update or Hotfix are first need to be applied on test SQL Server instance and then application needs to be tested to make sure it works fine without any problems.
– Run DBCC CheckDB on all the system and user databases and make sure there is no corruption on any of the databases.
– Backup all System and User database before applying the patches. The timing of the backups are important as all the backups(FULL, DIFF, LOG) should be stored and available for few days. Also, before hand it would be great to know how much time does it take to restore the databases in case of any problems. This can be tested on a test server to get a rough idea.

 

– Also backup Analysis Service databases and important configuration files. Backup Reporting Services encryption keys and important configuration files.
– Ensure there is sufficient free space in all the drives including C:\ drive on both the nodes.
– Download and keep the Service Pack or Cumulative Update or Hotfix file on all nodes of cluster and extract the contents of the file to a folder. Double check that the downloaded patch version, build, platform(x86, x64) etc.
– Make sure you have a valid domain account which has local administrator permission on all the cluster nodes.
– Coordinate with all teams(Application team, SysAdmin team, Network Team) Before starting the installation, so that application can be stopped before starting the patch installation and reboot of the servers can be performed by the System Administrator.
– Communicate to all stake holders about the estimated downtime and reboot of the servers which may cause restart of other services installed on that servers too.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Basics of SQL Server Upgrade Advisor and its Usage

SQLServerF1

One of the first and important steps to perform before performing upgrade or migration of SQL Server instance from lower version to a higher version is to run the Upgrade Advisor which generates a detailed report with objects and list of issues that could arise after upgrading to the higher version of SQL Server instance. Once we know the objects and the issues we can take corrective actions before or after the upgrade is complete which will keep the new upgraded SQL Server instance stable and consistent.

What is SQL server upgrade advisor? 
SQL Server Upgrade Advisor analyzes the legacy instances and produces reports detailing upgrade issues by SQL Server component. The resulting reports will show the detected issues or problems and also provide some guidance on how to fix the reported issues or some work around for the reported issues. These reports can be reviewed by using Upgrade Advisor or can be exported for further analysis, for example to Excel document. In addition to analyzing data and database objects, Upgrade Advisor can also analyze Transact-SQL scripts and SQL Server Profiler or SQL Trace files. Upgrade Advisor can be run from either a local server or from a remote server. Report generation and analysis is CPU intensive, so it is better to always try to run it remotely when working with production database servers.
SQL Server upgrade can analyze Database Services, Analysis Services,  Integration Services, Reporting Services, DTS packages. Report will also contain deprecated items that won’t run on the higher version of SQL server and which MUST be fixed.

Advisor can be freely downloaded from Microsoft site and we should download the upgrade advisor version to which you want to upgrade to. Example, if you have SQL Server 2005 instance installed and want to upgrade to SQL Server 2008 R2 instance, then you must download SQL Server 2008 R2 upgrade advisor.

This is how upgrade advisor looks like and some options to choose and the final report generated.

Upgrade Advisor Initial Screen

Upgrade Advisor Initial Screen

Components to be Analyzed

Components to be Analyzed

Upgrade Advisor Report

Upgrade Advisor Report

 

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Oracle Database Errors or Warnings from Error ORA-01381 to ORA-01406

SQLServerF1

 

ORA-01381: Dropping log string would leave less than two log files with block size string for instance string (thread string)
Cause: The database was migrated to use a specified log block size. Dropping all the logs specified would leave fewer than the required two log files with the specified block size per enabled thread.
Action: Either drop fewer logs or disable the thread before deleting the logs. It may be possible to clear the log rather than drop it.
ORA-01382: Thread string failed to open log file string. The log file’s block size (string) is larger than the disk sector size (string)

Cause: Log file was created on bigger sector disks, and moved to smaller sector disks.
Action: Move the log file back to bigger sector disks.
ORA-01383: Thread string failed to open string block size log file ‘string’ on string sector native-mode disks
Cause: Log file was copied from smaller sector disks to larger sector native-mode disks.
Action: Move the log file back to smaller sector disks.
ORA-01400: cannot insert NULL into (string)
Cause: An attempt was made to insert NULL into previously listed objects.
Action: These objects cannot accept NULL values.

ORA-01401: inserted value too large for column
Cause: The value inserted was too large for the given column.
Action: Do not insert a value greater than what the column can hold.
ORA-01403: no data found
Cause: No data was found from the objects.
Action: There was no data from the objects which may be due to end of fetch.
ORA-01406: fetched column value was truncated
Cause: The fetched column values were truncated.
Action: Use the right data types to avoid truncation.

Above are list of Oracle Database Errors or Warnings from Error ORA-01381 to ORA-01406 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-01371 to ORA-01380

SQLServerF1

 

ORA-01371: Complete LogMiner dictionary not found
Cause: One or more log files containing the LogMiner dictionary was not found.
Action: Add into LogMiner all log files containing the dictionary.
ORA-01372: Insufficient processes for specified LogMiner operation
Cause: The number of processes requested by the caller can not be allocated
Action: Increase number of parallel servers allocated to the instance
ORA-01373: insufficient memory for staging persistent LogMiner session
Cause: The maximum number of concurrent persistent LogMiner sessions allowed is limited by LOGMNR_MAX_PERSISTENT_SESSIONS parameter. Not enough memory has been set aside at instance startup to allocate the new LogMiner session.

Action: Increase LOGMNR_MAX_PERSISTENT_SESSIONS and restart instance.
ORA-01374: _log_parallelism_max greater than 1 not supported in this release
Cause: LogMiner does not mine redo records generated with _log_parallelism_max set to a value greater than 1.
Action: None
ORA-01375: Corrupt logfile string recovered
Cause: A corrupt logfile has been recovered by RFS
Action: None. Logical Standby should automatically restart. If logfile is still corrupt, may need to manually copy and reregister the logfile on the standby.

ORA-01377: Invalid log file block size
Cause: An invalid value was specified in the BLOCKSIZE clause.
Action: Use correct syntax.
ORA-01378: The logical block size (string) of file string is not compatible with the disk sector size (media sector size is string and host sector size is string)
Cause: One of the following occurred: (1) An attempt was made to create a file. (2) A file was moved to disks with different sector size.
Action: Create file or move file to the proper disk.
ORA-01380: Instance string (thread string) has less than two string block size log files
Cause: One of the following occurred: (1) An attempt was made to switch to specified block size logs for all threads. (2) An attemp was made to open a thread.
Action: Add log files to the thread that signals error, and try again.

Above are list of Oracle Database Errors or Warnings from Error ORA-01371 to ORA-01380 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-01361 to ORA-01370

SQLServerF1

 

ORA-01361: global name mismatch
Cause: The database global name where the log file was generated did not match the user-specified global name of the Streams Capture process.
Action: Start a new capture process and ensure that the user-specified global name matches that of the database that generated the log file.
ORA-01362: Specified SQL_REDO_UNDO parameter is invalid
Cause: The specified input value for the parameter was not a valid number.
Action: Specify a valid number and try again.

ORA-01363: Specified COLUMN_NAME parameter is NULL
Cause: NULL was specified for input value of parameter.
Action: Specify a non-NULL value and try again.
ORA-01364: waiting for branch at SCN string
Cause: LogMiner reader process has completed mining of physical standby terminal logs up to the stop SCN. LogMiner reader is now waiting for logs from the new branch.
Action: No action necessary. This informational statement is provided to record the event for diagnostic purposes. If there is a problem with log transport, the files can be registered manually using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement.

ORA-01365: waiting for new branch registration
Cause: LogMiner reader process has completed mining of physical standby terminal logs up to the stop SCN. The builder process is now waiting for logs from the new branch to be registered before continuing.
Action: No action necessary. This informational statement is provided to record the event for diagnostic purposes. If there is a problem with log transport, the files may be registered manually using the ALTER DATABASE REGISTER LOGICAL LOGFILE statement.
ORA-01366: failed to find redo logs required for terminal apply
Cause: LogMiner failed to find all the expected log files required to complete the terminal apply, requested using the FINISH APPLY clause. During terminal apply, LogMiner does not wait for logs to be registered. It expects all logs between the starting point and the last log on any redo branch that it will or could mine through, to be present at the mining site.
Action: Examine system.logmnr_log$ to see which logs are known to LogMiner. Then, locate or restore any missing logs and use the ALTER DATABASE REGISTER LOGICAL LOGFILE statement to register them. Alternatively, do not use the FINISH APPLY clause, but if activating a logical standby, only do this when you are sure that the missing log files can not be located and registered as this can lead to data loss.
ORA-01370: Specified restart SCN is too old
Cause: specified restart scn is too old, logmnr could not find a proper checkpoint.
Action: Specify a bigger restart SCN to try again

Above are list of Oracle Database Errors or Warnings from Error ORA-01361 to ORA-01370 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-01351 to ORA-01358

SQLServerF1

 

ORA-01351: tablespace given for Logminer dictionary does not exist
Cause: The tablespace name given as a parameter to DBMS_LOGMNR_D.SET_TABLESPACE does not exist.
Action: Check spelling of the tablespace name. If spelling is correct verify that the named tablespace has already been created. DBMS_LOGMNR_D.SET_TABLESPACE does not create a tablespace.
ORA-01352: tablespace given for Logminer spill does not exist
Cause: The tablespace name given as the parameter to DBMS_LOGMNR_D.SET_TABLESPACE does not exist.
Action: Check spelling of the tablespace name. If spelling is correct verify that the named tablespace has already been created. DBMS_LOGMNR_D.SET_TABLESPACE does not create a tablespace.

ORA-01353: existing Logminer session
Cause: An attempt was made to execute DBMS_LOGMNR_D.SET_TABLESPACE while a Logminer session(s) was active.
Action: First cause all Logminer sessions to be closed. A Logminer session can exist as a result of executing DBMS_LOGMNR.START_LOGMNR or as the result of using Oracle features such as Data Guard SQL Apply or Streams which use Logminer. Next, execute DBMS_LOGMNR_D.SET_TABLESPACE.
ORA-01354: Supplemental log data must be added to run this command
Cause: An attempt was made to perform an operation that required that supplemental log data be enabled.
Action: Execute a command such as ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; and then reissue the command that failed with this error.

ORA-01355: logminer tablespace change in progress
Cause: The tables used by logminer are in the process of being moved to another tablespace.
Action: Wait until the move is complete and try again.
ORA-01356: active logminer sessions found
Cause: Logminer sessions are currently active.
Action: End all logminer sessions and retry.
ORA-01358: LogMiner version is less than mined dictionary version
Cause: An attempt was made to mine a LogMiner dictionary from redo logs. The version of the Oracle database that created the logs was higher than the Oracle database version that attempted to mine the logs.
Action: Mine the logs using a newer version of the Oracle database having a version number that is equal to or greater than the dictionary version.

Above are list of Oracle Database Errors or Warnings from Error ORA-01351 to ORA-01358 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 

Oracle Database Errors or Warnings from Error ORA-01341 to ORA-01350

SQLServerF1

 

ORA-01341: LogMiner out-of-memory
Cause: The LogMiner session requires more system resources than is currently available.
Action: Allocate more SGA for LogMiner.
ORA-01342: LogMiner can not resume session due to inability of staging checkpointed data
Cause: Logmnr can not resume session because there is not enough SGA memory available to read in checkpointed data. Logminer periodically checkpoints data to enable faster crash recovery.
Action: Specify a bigger max_sga for the given LogMiner session and try again.

ORA-01343: LogMiner encountered corruption in the logstream
Cause: Log file is missing a range of scn values.
Action: Verify the contiguity of the scn range reprented by the log files added to LogMiner.
ORA-01344: LogMiner coordinator already attached
Cause: A coordinator process is already attached to the specified logminer context.
Action: Detach from the active coordinator session and retry the attach.
ORA-01345: Must be a LogMiner coordinator process
Cause: A LogMiner client attempted to perform a privileged operation.

Action: Issue the operation from the coordinator process.
ORA-01346: LogMiner processed redo beyond specified reset log scn
Cause: LogMiner has detected a new branch with resetlogs scn information prior to redo already mined.
Action: Contact your customer support representative.
ORA-01347: Supplemental log data no longer found
Cause: The source database instance producing log files for this LogMiner session was altered to no longer log supplemental data.
Action: Destroy this Logminer session. Re-enable supplemental log data on the source system and create a new LogMiner session.
ORA-01350: must specify a tablespace name
Cause: Invocation failed to specify a valid tablespace
Action: Reformat invocation of DBMS_LOGMNR_D.SET_TABLESPACE to include the name of a valid tablespace.

Above are list of Oracle Database Errors or Warnings from Error ORA-01341 to ORA-01350 received while performing certain operation against Oracle Database or related products.

What are Oracle Database Error Messages?

Oracle Error Messages may be returned while using products which are part of Oracle Database.  Each Oracle Database Error or Warning Message mentioned above contains the Warning or Error Message Statement, a short explanation of the probable causes of the Error message, and a recommended action.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about Oracle Database Error Messages or Warning Messages on Windows and Linux Operating Systems.

 
1 2 3