Error 823, Level 24, State 2, Procedure -, Line 1, Message: The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000000007e000 in file 'Q:\DATABASE_FILES\xxxxxx.mdf'.

Recently after storage issue we started getting below error for one of our client database. Log backups were continuously failing.

BACKUP LOG [xxxxxxx] TO DISK = N'N: \\DATABASE..." failed with the following error: "The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000040000 in file 'Q:\\DATABASE_FILES\\xxxxxxxx.mdf'
Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

When we checked we found database was online but log backups are not getting successful and continuously throwing above error. We tried to take manual log backup however we observed same behavior.

We also tried to run DBCC CHECKDB on that database however its failed.

After searching on google to fix this issue either we need to restart SQL Server or take that particular database offline and online.

As it was production hours so we did not have option to restart SQL Server as other critical databases resides on same server.

So we took database offline and online which resolved our issue.

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21

Hello,

Few days back i got this error while setting up mirroring so thought to share this knowledge with everyone.

1. After restoring database on Mirror server, i tried to establish mirroring but it failed with below         error

Message
Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'

2. As soon as i receive this error, the database on Mirror server database went into “In Recovery” or “Suspect” mode and it wont restore any new files mentioning that database is part of mirroring even though it failed so to fix that, we ran the command to break the mirroring on Mirror server

ALTER DATABASE DBName SET PARTNER OFF

3. Post that i thought it might be because on Primary server, mirroring is configured using Certificate so that might be the cause of it so i installed certificates on Mirror server and added entries of it on Primary server and vice-versa, I used below link to configure Mirroring via Certificate authentication

https://www.mssqltips.com/sqlservertip/1705/implementing-database-mirroring-in-sql-server-2005-across-domains/

4. The above solution also didn’t worked, then i was checking Endpoints on both servers then after long troubleshooting i was able to found out that main issue for this error.

CREATE ENDPOINT [Endpoint_Mirroring] 
       STATE=STARTED
       AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
       FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [ServerName_cert]
, ENCRYPTION = REQUIRED ALGORITHM RC4)

CREATE ENDPOINT [Endpoint_Mirroring] 
       STATE=STARTED
       AS TCP (LISTENER_PORT = 5024, LISTENER_IP = ALL)
       FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = CERTIFICATE [ServerName_cert]
, ENCRYPTION = REQUIRED ALGORITHM AES_192)

5.     If you the see the highlighted part in orange in principal i have RC4 algorithm and on mirror i have AES 92 algorithm.

5. I re-created Endpoint on mirror server by using RC4 as encryption algorithm, issue was resolved.

How to move standby database datafile to other location

Below are the steps to move Standby database datafiles to other location:

1) Login to the standby database. Export ORACLE_SID.

2)    Check the current settings of standby file management parameter:

Show parameter standby if it set to auto we need to set it to manual

SQL> alter system set standby_file_management=manual scope=both

3) Stop MRP process using below command:

SQL>alter database recover managed standby database cancel;

        Check if the MRP has stopped or not using below command:

        SQL>select process,status,sequence# from v$managed_standby;


4) Connect  to the RMAN on linux prompt.
rman target /

5) Copy datafile to the respective location which has enough free space. See below example:

        RMAN> COPY DATAFILE  '+DG12/test/datafile/test.123.353897'  TO  '+DG03';

6) Rename datafile using below command:

        SQL>ALTER DATABASE RENAME FILE '+DG12/test/datafile/test.123.353897'  TO              '+DG03';


7) Use RMAN to switch to file which we copied in step 5 using below command:

        RMAN>SWITCH DATAFILE '+DG12/test/datafile/test.123.353897'  TO COPY;

        you can also use datafile no to do this

        RMAN>SWITCH DATAFILE 20  TO COPY;

8) Start managed recovery process using below command:

        SQL>alter database recover managed standby database using current logfile disconnect from       session;

9) Verify standby is working fine by switching log files on primary database:
         SQl>alter system switch logfile;

10) As soon as you switched to copy old file gets deleted automatically in ASM. Verify file has       deleted or not.


11)   Make stand by file management auto using below command:

        SQL>alter system set standby_file_management=auto scope=memory both

How to put RAC database in archive log.

1) Login to any rac node and export oracle sid.

export ORACLE_SID=test1
sqlplus / as sysdba

2) Check archive log is enabled or not by using below query.

select name ,created ,open_mode ,log_mode from v$database;
or
archive log list;

3) Disable cluster instance parameter by setting cluster database to FALSE from the current instance.

alter system set cluster_database=false scope=spfile

4) Exit from sqlplus prompt and fire srvctl commands to stop all instances accessing cluster database

srvctl stop database -d test

5) Again login to local instance using sqlplus and mount database.

sqlplus / as sysdba
startup mount;

6) Enable archive log using below command.

alter database archivelog;

7) Re-enable support for clustering by setting instance parameter cluster_database to TRUE

alter system set cluster_database=true scope=spfile

8) shutdown local instance

shut immediate;

9) Bring all instance back up using srvctl

srvctl start database -d test

10) Login to local instance and check if archive log is enabled or not

sqlplus / as sysdba
archive log list;
or
select name ,created ,open_mode ,log_mode from v$database;

How To Move System DB's to another drive.

How To Move System DB's to another drive.

1) Master
1) Check the existing path using sp_helpdb 'master'
2) Go to SQL Server Configuration Manager
3) Navigate to the sql server service.
4) Go to sql server properties.
5) copy startup parameter for .mdf and .ldf file.
6) Update that parameter to the location that you wish to move.
7) Apply those changes.
8) Copy .mdf and .ldf files of the master database to the new location.
7) Start sql server services.

2) MSDB

1) Go to SSMS.
2) Check the existing path using sp_helpdb.
2) Use the below query to modify system catalog:

ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBData, FILENAME = 'Path of the filename');
GO
ALTER DATABASE msdb
MODIFY FILE (NAME = MSDBLog, FILENAME = 'Path of the filename');
GO
3) Stop the SQL Server Servies.
4) Move .mdf and .ldf file to the new directory.
5) Start sql server services.

Use the same procedure to move MODEL and TEMPDB.