SQL Server : Give Priviliges Specified View to User

CREATE LOGIN USRCC WITH PASSWORD=N'password', DEFAULT_DATABASE=[CRM_MSCRM]
GO
USE [CRM_MSCRM]
GO
CREATE USER [USRCC] FOR LOGIN [USRCC]
GO

GRANT SELECT ON dbo.view1 TO [USRCC]
GO
GRANT SELECT ON dbo.view2 TO [USRCC]
GO
GRANT SELECT ON dbo.view3 TO [USRCC]
GO

SQL Server : Clean TempDB

When i deleted data more than 20.000.000 records, i realized that tempDB full 100% utilization on drive F: (no free space), then i cancelled query delete, then i execute query to clear tempDB :

USE [tempdb]
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC SHRINKDATABASE(tempdb, 10)
DBCC SHRINKFILE ('tempdev')
DBCC SHRINKFILE ('templog')
GO

Mount NAS Storage

Command mount NAS Storage :

1. Create directory on our linux
mkdir /mnt/QNAP1

2. make sure folder have correct permission, then mount
mount -t nfs 10.14.20.54:/BACKUP2018/BACKUP_RAC /mnt/QNAP1

3. Check directory
df- kh

SQL Server : Truncate Transaction Log

We can truncate transaction log (to reclaim free space in our drive) by using command :

ALTER DATABASE CRM_MSCRM
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (mscrm_log, 1)
GO
       
ALTER DATABASE CRM_MSCRM
SET RECOVERY FULL
GO

Switch back to simple recovery >> then shrink >> switch back again to full recovery

ALTER DATABASE MSCRM_CONFIG
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (MSCRM_CONFIG_LOG, 1)
GO
       
ALTER DATABASE MSCRM_CONFIG
SET RECOVERY FULL
GO

SQL Server : Configuration Log Shipping

I have 2 MS SQL 2012 Servers :
one primary on ip : 10.14.23.227 (hostname : icnsvraxdev)
second one is for secondary server on ip : 10.14.26.25 (hostname : WIN-993HI52U5TC)

For prerequisities :

1. Make sure DB recovery model = full
2. SQL Service Account = admin domain, not NT Service
3. Add entries hosts file on path
C:\Windows\System32\drivers\etc\hosts,

10.14.23.228 crmserver
10.14.23.227 icnsvraxdev
10.14.23.238 icnaosappprod
10.14.26.25 WIN-993HI52U5TC

4. Set Shared Folders
5. Service SQLServer Agent already running

Full recovery model

on SQL Server Configuration Manager : SQL Server Account and SQL Server Account using account user domain


Step by step :
1. Right click on Database, clik properties, click transaction log shipping, give check box on enable as primary database. Click Backup Settings

2.Specify path backup on primary server, but enter as network path

3. Click button add, on secondary databases

4. Click connect

5. Enter credential for secondary server

6. Choose generate a full backup, and restore it

7. On Copy files, specify path on destination folder
8. Choose standby mode on restore transaction log

9. Choose monitor server instance, enter credential, click OK

success

Script synchronize : Windows Server to Linux

Script to synchronize from Windows to Linux Server.

We need to install winscp first :

@echo off

"C:\Program Files (x86)\WinSCP\WinSCP.com" ^
  /ini=nul ^
  /command ^
    "open sftp://username:password@ipaddress/ -hostkey=""ssh-rsa 2048 DjbPCYr0zjTEFTgiADlitTP0vFVJK+a5s6+69Q1wDnQ=""" ^
"synchronize remote C:\xampp\mysql\data\itracking ""/share/CACHEDEV2_DATA/BACKUP 2018/bkp_itracking/data""" ^
"synchronize remote C:\xampp\htdocs\itracking ""/share/CACHEDEV2_DATA/BACKUP 2018/bkp_itracking/htdocs""" ^
    "exit"
   
set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0
(
  echo Success
)
else
(
  echo Error
)

exit

save as .bat file, then execute via task scheduler windows

Error Startup ASM Instance : ORA-29701 ORA-29701

[oracle@icndrc db_1]$ export ORACLE_HOME=/u01/app/11.2.0/grid/product/11.2.0/asm/
[oracle@icndrc db_1]$ asmcmd
Connected to an idle instance.
ASMCMD> exit
[oracle@icndrc db_1]$ echo $ORACLE_SID
+ASM1
[oracle@icndrc db_1]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 24 13:56:34 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL> exit
Disconnected
[oracle@icndrc db_1]$ echo $PATH
/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin
[oracle@icndrc db_1]$ export PATH=/u01/app/11.2.0/grid/product/11.2.0/asm/bin
[oracle@icndrc db_1]$ asmcmd
Connected to an idle instance.
ASMCMD> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
Connected to an idle instance.
ASMCMD> exit
[oracle@icndrc db_1]$ sqlplus / as sysasm

[oracle@icndrc db_1]$ crsctl start resource ora.cssd
CRS-2679: Attempting to clean 'ora.cssd' on 'icndrc'
CRS-2672: Attempting to start 'ora.diskmon' on 'icndrc'
CRS-2676: Start of 'ora.diskmon' on 'icndrc' succeeded
CRS-2681: Clean of 'ora.cssd' on 'icndrc' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'icndrc'
CRS-2676: Start of 'ora.cssd' on 'icndrc' succeeded
[oracle@icndrc db_1]$ crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.
[oracle@icndrc db_1]$ crsctl start has
CRS-4640: Oracle High Availability Services is already active
CRS-4000: Command Start failed, or completed with errors.
[oracle@icndrc db_1]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DG_DATA.dg ora....up.type ONLINE    OFFLINE
ora.DG_RECO.dg ora....up.type ONLINE    OFFLINE
ora....BY.lsnr ora....er.type ONLINE    ONLINE    icndrc
ora....BY.lsnr ora....er.type ONLINE    ONLINE    icndrc
ora....BY.lsnr ora....er.type ONLINE    ONLINE    icndrc
ora....ER.lsnr ora....er.type ONLINE    ONLINE    icndrc
ora.asm        ora.asm.type   ONLINE    OFFLINE
ora.cssd       ora.cssd.type  ONLINE    ONLINE    icndrc
ora.diskmon    ora....on.type OFFLINE   OFFLINE
ora.evmd       ora.evm.type   ONLINE    OFFLINE
ora.fmsstby.db ora....se.type ONLINE    OFFLINE
ora....stby.db ora....se.type ONLINE    OFFLINE
ora....stby.db ora....se.type ONLINE    OFFLINE
ora.ons        ora.ons.type   OFFLINE   OFFLINE
[oracle@icndrc db_1]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 24 14:05:32 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started

Total System Global Area  409194496 bytes
Fixed Size                  2228864 bytes
Variable Size             381799808 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted


SQL> alter diskgroup mount;
alter diskgroup mount
                    *
ERROR at line 1:
ORA-15101: no action specified

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DG_DATA                        DISMOUNTED
DG_RECO                        DISMOUNTED

SQL> alter diskgroup all mount;
*
ERROR at line 1:
ORA-15110: no diskgroups mounted


SQL> shu immediate;
ORA-15100: invalid or missing diskgroup name


ASM instance shutdown
SQL> startup restrict;
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started

Total System Global Area  409194496 bytes
Fixed Size                  2228864 bytes
Variable Size             381799808 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted

SQL> select name,state from v$asm_diskgroup;

NAME                           STATE
------------------------------ -----------
DG_DATA                        DISMOUNTED
DG_RECO                        DISMOUNTED

SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
ORA-15100: invalid or missing diskgroup name


ASM instance shutdown
SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started

Total System Global Area  409194496 bytes
Fixed Size                  2228864 bytes
Variable Size             381799808 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64                                                                                        bit Production
With the Automatic Storage Management option
[oracle@icndrc db_1]$ asmcmd lsdg
[oracle@icndrc db_1]$ asmcmd

SQL> startup
ORA-00099: warning: no parameter file specified for ASM instance
ASM instance started

Total System Global Area  409194496 bytes
Fixed Size                  2228864 bytes
Variable Size             381799808 bytes
ASM Cache                  25165824 bytes
ORA-15110: no diskgroups mounted


SQL> select name from v$asm_diskgroup;

NAME
------------------------------
DG_DATA
DG_RECO

SQL> alter diskgroup dg_data mount;

Diskgroup altered.

SQL> alter diskgroup dg_reco mount;

Diskgroup altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option
[oracle@icndrc db_1]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    511990   287819                0          287819              0             N  DG_DATA/
MOUNTED  EXTERN  N         512   4096  1048576    102398    59227                0           59227              0             N  DG_RECO/