SQL Server Access

Thanks to Graham Brown our resident SQL server guru for this - thought it is worth sharing.

This has happened a few times where SQL instances have been built or cloned by ‘other’ people – who then forget about who is meant to be supporting the instances … or just  go on holiday  😊

 No Dba access

 If we cannot get access with our windows logins you can force your way in via Single User mode & SQLCMD

 See this :

https://www.sqlshack.com/recover-lost-sa-password/

Once you have gained access yourself please remember to add all the other DBA’s in the team… you know it makes sense!

If you have done the step above and still cannot log on – check the error log

(C:\Program Files\Microsoft SQL Server\MSSQL13.DYNAMICS_CRM_SQL\MSSQL\Log\errorlog for example)  – in the case of the instance I could see this just after the startup :

 2020-09-29 08:14:27.92 Server      Authentication mode is WINDOWS-ONLY.

 And then after I had tried to log on as the NewSA user created previously – and failed – in the log I could see this :

 2020-10-05 03:47:20.42 Logon       Login failed for user 'NewSA'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

Basically whoever has created the instance has left it at ‘Windows only authentication’  - now although I can understand this depending on the application the instance is being used for it it’s a pain when we have the lack of login issue … and without the ability to use SSMS to change it to ‘SQL Server and Windows Authentication’ we have to hack the registry :

 https://www.top-password.com/knowledge/sql-server-authentication-mode.html

 One last thing - lately I have found is that people are not setting the SQL Server Network Configuration protocols correctly – for anybody to connect via a client / network connection the TCP/IP protocol must be enabled (as well as Allow Remote Connection to this server obviously) – this is found in SQL Server Configuration Manager and when done correctly looks like this :

 

Can't open perl script - RemoteHostExecutor.pl

Despite banging on about making sure you have the latest OPatch applied on your database / grid homes today I was caught out.

If you have different OPatch versions between the grid and database homes you hit a mismatch error but if you have different versions on different RAC nodes you will hit this :

[root@ora01 gridhome]# $ORACLE_HOME/OPatch/opatchauto apply /u99/media/jul2020/31305382 -oh /u01/app/12.2.0/gridhome -analyze

Can't open perl script "/u01/app/12.2.0/gridhome/OPatch/auto/database/bin/RemoteHostExecutor.pl": (null)

oracle.dbsysmodel.driver.sdk.productdriver.ProductDriverException: Unable to execute command : Can't open perl script "/u01/app/12.2.0/gridhome/OPatch/auto/database/bin/RemoteHostExecutor.pl": (null)

OPatchAuto failed.

If you do a quick search you will be told to apply the latest OPatch but I was already using the latest version but once same version on all nodes - happy days!

So latest version all homes and all nodes.

Rman duplicate database - quick notes.

Quick notes on duplicating database following level 0 rman backup.

Wanted a duplicate of a db called MPROD to one called MPRODX.

So ran the rman backup and copied the backup to another server.

Started the mprodx database with a "skeleton" pfile - the db name option should suffice.

Most of the parameters are optional.

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm#BRADV454

 
SQL> startup nomount pfile=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initmprodx.ora
ORACLE instance started.
 
Total System Global Area 1.6106E+10 bytes
Fixed Size                  4516272 bytes
Variable Size            2281702992 bytes
Database Buffers         1.3791E+10 bytes
Redo Buffers               29036544 bytes

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 - 64bit Production
 
Created a script to do the duplicate (the rman backup was encrypted but not a common thing to do, so ignore the decryption option). 

Created the script dupl,txt
 
set echo on;
-- set decryption identified by 'password123' ;
run {
 
allocate auxiliary channel d1  type disk ;
allocate auxiliary channel d2  type disk ;
allocate auxiliary channel d3  type disk ;
allocate auxiliary channel d4  type disk ;
 
duplicate database to MPRODX  backup location '/backup/rman_back';
 
release channel d1 ;
release channel d2 ;
release channel d3 ;
release channel d4 ;
 
}
  
Run the script in the background (change /home/oracle to wherever).

nohup rman auxiliary / cmdfile=/home/oracle/dupl.txt log=/home/oracle/refresh_mprodx.log &

At the end of this should have a duplicate database. You might hit an issue if with files already existing but if you are totally certain that you are not overwriting an existing database you can use the nofilenamecheck option to stop this. Check the log file to confirm ok.