Reducing Number of VLFs in SQL Server Database Transaction Log File

SQLServerF1

Every SQL Server Databases consists of two or more files, of which two types of files are mandatory, one of which is a data file and another transaction log file. Data file contains actual data in specific format which SQL Server can understand and transaction log files contains all the changes that are made to the data in the databases using DDL(Create/Alter/Drop) or DML(Insert/Update/Delete) queries. The transaction log is used to guarantee the data integrity of the database and for data recovery. Data files and transaction log files architecture is different and it is important as a DBA to understand the architectures of the data and log files in a database. In this article we will discuss about the VLFs in transaction log file and how to reduce those VLFs.

The transaction log in a database maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

At times the number of Virtual Log files (VLFs) are increased to very high number due to improper configuration to transaction log files. SQL Server follows an algorithm which creates these virtual log files based on the initial size and auto growth that occurs. For example, If the initial size of the log file is set to any value less than 64 MB, then there will be 4 VLFs created by SQL Server, if initial size is between 64MB and up to 1GB then 8 VLFs gets created and it initial size is greater than 1 GB then the number of VLFs created are 16. The same formula applies to auto-growth events of the log file too. Auto-growth evert occurs when the log file reaches the max initial size and all the space used is active, then physical log file size is increased on the auto-growth settings of the database.

If the initial size of database is set to very low value, then it will cause auto-growth and if this auto-growth values are set to low then there will be frequent small increase to the log file which can create lot of VLFs. If there are too many VLFs in a database log file, then it will cause performance issues and also affect certain operations which are dependent on log file. Some problems which occur due to large transaction log files include long recovery of database during startup which interrupts users from using the database, slow performance with replication, database mirroring, AlwaysON, etc. So, as a DBA it is important to maintain the number of VLFs to smaller size thus avoiding unnecessary problems.

When you notice that the number of VLFs are very high like more than 300 then you can follow below steps to reduce the number of VLFs

– First monitor for few days or month to see what is the maximum size the transaction log file of a particular database can grow when there are regular log backups performed. That will be the size the log file may grow in future too.

– First shrink the transaction log file to the smallest size possible(better perform this during off business hours as it will cause some performance problems). You may need to perform multiple log backups and perform shrink operation on log file multiple times to reduce the log file size to minimum.

– Once the log file is very low, now change the initial size of the log file to higher value like 2000 MB or 4000 MB or 8000 MB based on the estimated size the log file can grow to in future. For example, if you estimate that the log file may grow up to 20 GB, then set the initial size of the log file to 4000 MB first time and to 8000 MB second time and to 12000 MB third time and to 16000 MB fourth time and finally to 20000 MB.

Example:

ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 4000MB )
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 8000MB )
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 12000MB )
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 16000MB )
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N’LogicalLogFileName’, SIZE = 20000MB )

– Now set the auto-growth setting of the database log file to grow in increments of 1024MB.

Now the number of VLFs in the database will be less and will not increase by too much in future too.

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.

 

Introduction to Remote Database Administration (DBA) Services

SQLServerF1

Almost every organization produces data and has need to store and retive data in an efficient manner. There are various database management products like SQL Server, Oracle, MySQL, DB2, Sybase, Informatica, TeraData, etc are available in the market and every organization uses one or more of the database management products. Most of the large and medium organizations have dedicated DBA team or atleast one or two DBAs who manage the databases and many organizations outsource their DBA operations to other reputed companies which provide Database Administration and related services.

The companies which employees DBAs and support Database Administration and Management operations may have dedicated office at different locations around the globe and DBAs visit the office daily and connects to the servers or databases and provides day to day support. However with recent development in the hardware and software industry the access to the servers or databases has become far more easier and flexible which allows DBAs to connect and manage servers or databases from any where with just having an Internet connection. There are various security measures taken to safe guard the data and the servers.

With these recent developments there is now an increase options of remove DBA services where in a company employees DBAs across the world and allow them to work from their houses and connect to the client environment through secure methods and then perform day to day activities from home itself without having to visit clients office or the vendors office. In future this will only further increase with the current success with the remote DBA services. Remote DBA services companies specialize in multiple Database Management and related softwares.

Management of databases systems is not plain simple as there are lot of things involved, thus many companies outsource their database operations to remote DBA service companies so that they can concentrate on their core business instead of worrying about the operations and management of non core stuff which is an overhead. Also, many organizations may not be able to afford to have DBAs available 24×7 as it may get very costly, instead they can avail 24×7 reliable DBA services by spending lesser amount or money. Currently remote DBA services are popularly used by many organizations for SQL Server, Oracle, Oracle E-Business  Suite, MySQL, DB2, Sybase, etc.

Remote DBA services generally operate on hourly price model, where a client buys certain number of hours per month and the DBA team installs an monitoring tool or uses existing monitoring tools already installed and works on the incidents raised by the monitoring tools. In addition to the regular monitoring, remote DBA service teams also provide many proactive tasks like health checks, performance reviews, security reviews, backup reviews, etc. The contracted hours can also be flexible like it can be increased if there are any new projects and can be reduced once the projects completes.

If you are looking for low cost remote DBA services for less than 5 critical SQL servers during US non-business hours for SQL Server technology, please contact us at SQLServerF1@GMail.Com

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.

 

Displaying SQL Server Query Execution Plans and TIME and IO Statistics

SQLServerF1

DBAs mostly operate, manager or administer a SQL Server instance using SQL Server Management Studio (SSMS). Any operation performed against SQL Server instance through SSMS GUI internally will be translated into set of T-SQL command which can be individual queries or Stored Procedures, functions, etc. Also, any one can run the T-SQL queries directly from the SSMS which gets executed and the operation gets performed and any results are returned.

From a user point of view, we understand T-SQL commands, but SQL Server database engine will further translate these queries into query execution plans so that it can perform the requested operations in the best way possible. So, if there are complains or request that the SQL queries are performing slow, then we can try run those queries from SSMS and use various options available to understand what the query might be doing internally with the help of SQL Server query execution plans, execution IO and TIME statistics.

Below are some of the options provided by SQL Server to see various details about the query execution. These below Transact-SQL SET statements are the options for displaying execution plan information produce output in XML and text.

SET SHOWPLAN_XML ON – This causes SQL Server not to execute Transact-SQL statements, instead SQL Server returns the execution plan information about how the statements are going to get executed in the form of an XML document.

SET SHOWPLAN_TEXT ON – This causes SQL Server to return the execution plan information for each query in text. The Transact-SQL statements or batches are not executed.

SET SHOWPLAN_ALL ON – This is similar to SET SHOWPLAN_TEXT, except that the output is more verbose than that of SHOWPLAN_TEXT.

SET STATISTICS XML ON – This returns execution information for each statement after this statement executes in addition to the regular result set the statement returns. The output is in an XML format. SET STATISTICS XML ON produces an XML output for each statement that executes. The difference between SET SHOWPLAN_XML ON and SET STATISTICS XML ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS XML ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

SET STATISTICS PROFILE ON – This returns the execution information for each statement after the statement executes in addition to the regular result set the statement returns. Both SET statement options provide output in text. The difference between SET SHOWPLAN_ALL ON and SET STATISTICS PROFILE ON is that the second SET option executes the Transact-SQL statement or batch. SET STATISTICS PROFILE ON output also includes information about the actual number of rows processed by various operators and the actual number of executes of the operators.

SET STATISTICS IO ON – This displays information about the amount of disk activity that is generated by Transact-SQL statements after the statements execute. This SET option produces text output.

SET STATISTICS TIME ON – Displays the number of milliseconds required to parse, compile, and execute each Transact-SQL statement after statements execute. This SET option produces text output.

Some of these options can also be turned on from the SSMS GUI.

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.

 

Introduction to SQL Server Execution Plans

SQLServerF1

Any query run against SQL Server instance will be internally compiled and an execution plan will be generated which gets executed to satisfy the query and generate the required output. When a Query is submitted to the SQL Server instance, it determines different ways of completing this query and then chooses the best way of executing that particular query. To further improve the performance, then generated execution plan is saved and is reused if the same query comes again, thus saves the time taken to generate an execution plan.

If a particular query is reported to be running slow or taking long time to complete, then execution plans are the best way to look and understand what all operations the query is performing. SQL Server database engine uses various metrics like Statistics, Indexes to create all possible execution plans to satisfy the query and then chooses the best plan which would be the fastest. SQL Server uses cost based method to determine which execution plan is the better one compared to other plans possible for the same query. It estimates certain cost for each operation like certain cost for performing logical or physical IO, cost for Memory, CPU, Sort, Scan, etc.

There are different types of execution plans which SQL Server can generate which include Estimated plans and Actual Execution Plans. SQL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the procedure cache.

SQL Server execution plans has the following two main components, which are Query plan and execution context.

Query Plan – The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.

Execution Context – Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

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-00291 to ORA-00300

SQLServerF1

ORA-00291: numeric value required for PARALLEL option

Cause: A recovery command was specified incorrectly. The PARALLEL option must be followed by a numeric argument that specifies the degree of parallelism.
Action: Re-enter the command with a numeric argument specifying the degree of parallelism desired.
ORA-00292: parallel recovery feature not installed
Cause: A parallel recovery was requested when the parallel recovery option is not installed.
Action: Delete the PARALLEL clause from the RECOVER command. Also, delete the RECOVERY_PARALLELISM parameter in the initialization file.
ORA-00293: control file out of sync with redo log
Cause: The redo log file and control file are out of sync because a non-current controle file was specified when the instance was started.
Action: Retry the RECOVER command using the current control file, or retry the RECOVER command using the USING BACKUP CONTROLFILE clause.

ORA-00294: invalid archivelog format specifier ‘string’
Cause: An invalid format specifier was found in the LOG_ARCHIVE_FORMAT initialization parameter. The only characters permitted following the % symbol are s, S, t, and T.
Action: Correct the initialization file and re-start the instance.
ORA-00295: datafile/tempfile number string is invalid, must be between 1 and string
Cause: An invalid file number was specified.
Action: Specify a valid datafile or tempfile number and retry the operation.
ORA-00296: maximum number of files (string) exceeded for RECOVER DATAFILE LIST
Cause: The RECOVER DATAFILE LIST command specified more datafiles than are allowed by the DB_FILES initialization parameter. This error occurs when doing recovery with Recovery Manager, and the instance has been started with a DB_FILES parameter specifying fewer datafiles than recovery manager needs to recover to satisfy the user’s RECOVER command.
Action: Re-start the instance with a higher value for DB_FILES.

ORA-00297: must specify RECOVER DATAFILE LIST before RECOVER DATAFILE START
Cause: The RECOVER DATAFILE START command was issued, but no RECOVER DATAFILE LIST commands had been issued. This only happens when doing recovery with Recovery Manager, and is an internal error in Recovery Manager, because Recovery Manager should always issue RECOVER DATAFILE LIST before RECOVER DATAFILE START.
Action: Contact customer support
ORA-00298: Missing or invalid attribute value
Cause: A non-zero integer value is required when the following keyword attributes are specified: TIMEOUT, EXPIRE, DELAY, NEXT
Action: Correct the syntax and retry the command.
ORA-00299: must use file-level media recovery on data file string
Cause: The control file does not contain an entry for this file, so block media recovery cannot be done.
Action: Restore the data file and perform file-level media recovery.
ORA-00300: illegal redo log block size string specified – exceeds limit of string
Cause: The specified block size of the redo log is greater than the maximum block size for the operating system.
Action: Create the redo log on a device with a smaller block size

Above are list of Oracle Database Errors or Warnings from Error ORA-00291 to ORA-00300 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-00281 to ORA-00290

SQLServerF1

ORA-00281: media recovery may not be performed using dispatcher

Cause: An attempt was made to use a dispatcher process for media recovery. Memory requirements disallow this recovery method.
Action: Connect to the instance via a dedicated server process to perform media recovery.
ORA-00282: UPI string call not supported, use ALTER DATABASE RECOVER
Cause: The given UPI call is no longer supported.
Action: Use the ALTER DATABASE RECOVER command for all recovery actions.
ORA-00283: recovery session canceled due to errors
Cause: An error during recovery was determined to be fatal enough to end the current recovery session.
Action: More specific messages will accompany this message. Refer to the other messages for the appropriate action.

ORA-00284: recovery session still in progress
Cause: An error during recovery was determined to be minor enough to allow the current recovery session to continue.
Action: More specific messages will accompany this message. Refer to the other messages for the appropriate action.
ORA-00285: TIME not given as a string constant
Cause: UNTIL TIME was not followed by a string constant for the time.
Action: Enter the time enclosed in single quotation marks.
ORA-00286: no members available, or no member contains valid data
Cause: None of the members of a redo log file group are available, or the available members do not contain complete data.
Action: If a member is temporarily offline, attempt to make it available. Make sure that the correct filenames are being used, especially if the redo log file is being accessed from a remote location.

ORA-00287: specified change number string not found in thread string
Cause: The given change number does not appear in any of the online redo logs for the given thread.
Action: Check the statement to make certain a valid change number is given. Perhaps try to use the NEXT option for archiving logs.
ORA-00288: to continue recovery type ALTER DATABASE RECOVER CONTINUE
Cause: During media recovery, a new log is not required but the continuation command is necessary to do a checkpoint and report errors.
Action: Type ALTER DATABASE RECOVER CONTINUE and recovery will resume.
ORA-00289: suggestion : string
Cause: This message reports the next redo log filename that is needed, according to the initialization parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. This message assumes that LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT are the same now as when the required redo log file was archived.
Action: Consider using this filename for the next log needed for recovery.
ORA-00290: operating system archival error occurred. See error below
Cause: While attempting to archive to a redo log file, the server encountered an unexpected operating system error.
Action: Correct the operating system error given in the messages and retry the operation. See also your operating system-specific Oracle documentation.

Above are list of Oracle Database Errors or Warnings from Error ORA-00281 to ORA-00290 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-00271 to ORA-00280

SQLServerF1

ORA-00271: there are no logs that need archiving

Cause: An attempt was made to archive the unarchived redo log files manually, but there are no files that need to be archived.
Action: No action required.
ORA-00272: error writing archive log string
Cause: An I/O error occurred while archiving a redo log file.
Action: Check that the output device is still available and correct any device errors that may have occurred. Also, make certain that sufficient space for archiving is available on the output device.
ORA-00273: media recovery of direct load data that was not logged
Cause: A media recovery session encountered a table that was loaded by the direct loader without logging any redo information. Some or all of the blocks in this table are now marked as corrupt.
Action: The table must be dropped or truncated so that the corrupted blocks can be reused. If a more recent backup of the file is available, try to recover this file to eliminate this error.

ORA-00274: illegal recovery option string
Cause: An illegal option was specified for a recovery command.
Action: Correct the syntax and retry the command.
ORA-00275: media recovery has already been started
Cause: An attempt was made to start a second media recovery operation in the same session.
Action: Complete or cancel the first media recovery session or start another session to perform media recovery.
ORA-00276: CHANGE keyword specified but no change number given
Cause: The CHANGE keyword was specified on the command line, but no change number was given.
Action: Retry the command using a valid change number after the CHANGE keyword.

ORA-00277: illegal option to the UNTIL recovery flag string
Cause: Only CANCEL, CHANGE, CONSISTENT and TIME can be used with the UNTIL keyword.
Action: Correct the syntax.
ORA-00278: log file ‘string’ no longer needed for this recovery
Cause: The specified redo log file is no longer needed for the current recovery.
Action: No action required. The archived redo log file may be removed from its current location to conserve disk space, if needed. However, the redo log file may still be required for another recovery session in the future.
ORA-00279: change string generated at string needed for thread string
Cause: The requested log is required to proceed with recovery.
Action: Please supply the requested log with “ALTER DATABASE RECOVER LOGFILE <file_name>” or cancel recovery with “ALTER DATABASE RECOVER CANCEL”.
ORA-00280: change string for thread string is in sequence #string
Cause: This message helps to locate the redo log file with the specified change number requested by other messages.
Action: Use the information provided in this message to specify the required archived redo log files for other errors.

Above are list of Oracle Database Errors or Warnings from Error ORA-00271 to ORA-00280 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-00261 to ORA-00270

SQLServerF1

ORA-00261: log string of thread string is being archived or modified

Cause: The log is either being archived by another process or an administrative command is modifying the log. Operations that modify the log include clearing, adding a member, dropping a member, renaming a member, and dropping the log.
Action: Wait for the current operation to complete and try again.
ORA-00262: current log string of closed thread string cannot switch
Cause: The log cannot be cleared or manually archived because it is the current log of a closed thread, and it is not possible to switch logs so another log is current. All other logs for the thread need to be archived, or cleared, and cannot be reused.
Action: Archive another log in the same thread first, or complete the clearing. See attached errors for the reason the switch cannot be completed.

ORA-00263: there are no logs that need archiving for thread string
Cause: An attempt was made to manually archive the unarchived logs in this thread but no logs needed archiving.
Action: No action required.
ORA-00264: no recovery required
Cause: An attempt was made to perform media recovery on files that do not // need any type of recovery.
Action: Do not attempt to perform media recovery on the selected files. Check to see that the filenames were entered properly. If not, retry the command with the proper filenames.
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
Cause: The database either crashed or was shutdown with the ABORT option. Media recovery cannot be enabled because the online logs may not be sufficient to recover the current datafiles.
Action: Open the database and then enter the SHUTDOWN command with the NORMAL or IMMEDIATE option.

ORA-00266: name of archived log file needed
Cause: During media recovery, the name of an archived redo log file was requested, but no name was entered.
Action: Mount the correct redo log file and enter its name when it is requested.
ORA-00267: name of archived log file not needed
Cause: During media recovery, the name of an archived redo log file was entered, but no name was requested.
Action: Continue media recovery, but do not enter a new log name.
ORA-00268: specified log file does not exist ‘string’
Cause: The given redo log file does not exist.
Action: Check the spelling and capitalization of the filename and retry the command.
ORA-00269: specified log file is part of thread string not string
Cause: The given redo log file is not part of the given thread
Action: Check that the thread of the redo log file matches the thread on the command line. If not, use a redo log file from the appropriate thread. Retry the command after correcting the error.
ORA-00270: error creating archive log string
Cause: An error was encountered when either creating or opening the destination file for archiving.
Action: Check that the archive destination is valid and that there is sufficient space on the destination device.

Above are list of Oracle Database Errors or Warnings from Error ORA-00261 to ORA-00270 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-00251 to ORA-00260

SQLServerF1

ORA-00251: LOG_ARCHIVE_DUPLEX_DEST cannot be the same destination as string string

Cause: The destination specified by the LOG_ARCHIVE_DUPLEX_DEST parameter is the same as the destination specified by an ALTER SYSTEM ARCHIVE LOG START TO command.
Action: Specify a different destination for parameter LOG_ARCHIVE_DUPLEX_DEST, or specify a different destination with the ALTER SYSTEM command.
ORA-00252: log string of thread string is empty, cannot archive
Cause: A log must be used for redo generation before it can be archived. The specified redo log was not been used since it was introduced to the database. However it is possible that instance death during a log switch left the log empty.
Action: Empty logs do not need to be archived. Do not attempt to archive the redo log file.
ORA-00253: character limit string exceeded by archive destination string string
Cause: The destination specified by an ALTER SYSTEM ARCHIVE LOG START TO command was too long.
Action: Retry the ALTER SYSTEM command using a string shorter than the limit specified in the error message.

ORA-00254: error in archive control string ‘string’
Cause: The specified archive log location is invalid in the archive command or the LOG_ARCHIVE_DEST initialization parameter.
Action: Check the archive string used to make sure it refers to a valid online device.
ORA-00255: error archiving log string of thread string, sequence # string
Cause: An error occurred during archiving.
Action: Check the accompanying message stack for more detailed information. If the online log is corrupted, then the log can be cleared using the UNARCHIVED option. This will make any existing backups useless for recovery to any time after the log was created, but will allow the database to generate redo.
ORA-00256: cannot translate archive destination string string
Cause: The destination specified by an ALTER SYSTEM ARCHIVE LOG START TO command could not be translated.
Action: Check the accompanying message stack for more detailed information. Then, retry the ALTER SYSTEM command using a different string.

ORA-00257: archiver error. Connect internal only, until freed.
Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.
Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
Cause: The database is in NOARCHIVELOG mode and a command to manually archive a log did not specify the log explicitly by sequence number, group number or filename.
Action: Specify log by filename, by group number or by thread and sequence number.
ORA-00259: log string of open instance string (thread string) is the current log, cannot archive
Cause: An attempt was made to archive the current log of an open thread. This is not allowed because the redo log file may still be in use for the generation of redo entries.
Action: Force a log switch in the instance where the thread is open. If no instances are open, open the database so that instance recovery can recover the thread.
ORA-00260: cannot find online log sequence string for thread string
Cause: The log sequence number supplied to the archival command does not match any of the online logs for the thread. The log might have been reused for another sequence number, it might have been dropped, the sequence number might be greater than the current log sequence number, or the thread might not have any logs.
Action: Check the ARCHIVE statement, then specify a valid log sequence number. Specify a valid log sequence number.

Above are list of Oracle Database Errors or Warnings from Error ORA-00251 to ORA-00260 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-00241 to ORA-00250

SQLServerF1

ORA-00241: operation disallowed: control file inconsistent with data dictionary

Cause: The control file was either recently created via CREATE CONTROLFILE or an incomplete recovery has been done. Thus, the datafiles in the control file and the ones in the data dictionary may not match.
Action: Open the database, then retry the operation.
ORA-00242: maximum allowed filename records used up in control file
Cause: Failed to create a new filename record entry in the control file because the maximum allowed filename records have been allocated and are in use.
Action: Free up controlfile filename section entries by dropping unwanted tablespaces/datafiles/redofiles.

ORA-00243: failed to expand control file filename section by string records
Cause: Failed to expand control file filename section. See alert log for more details.
Action: Retry the operation after increasing disk space for controlfile expansion.
ORA-00244: concurrent control file backup operation in progress
Cause: Failed to create a control file backup because concurrent control file backup operation was in progress.
Action: Retry backup creation later.

ORA-00245: control file backup failed; target is likely on a local file system
Cause: Failed to create a control file backup because some process signaled an error during backup creation. This is likely caused by the backup target being on a local file system so it could not be accessed by other instances. It can also be caused by other I/O errors to the backup target. Any process of any instance that starts a read/write control file transaction must have access to the backup control file during backup creation.
Action: Check alert files of all instances for further information.
ORA-00246: control file backup failed; error accessing backup target
Cause: An attempt to create a control file backup failed because some process signaled an I/O error when accessing the backup target.
Action: Check alert and trace files for further information.
ORA-00250: archiver not started
Cause: An attempt was made to stop automatic archiving, but the archiver process was not running.
Action: No action required.

Above are list of Oracle Database Errors or Warnings from Error ORA-00241 to ORA-00250 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