Change Always on Availability group ownership

 USE [master]

GO

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB3] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB4] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB5] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB6] TO [ECP\etrace-lg-svc];

ALTER AUTHORIZATION ON AVAILABILITY GROUP::[AGLG-DB7] TO [ECP\etrace-lg-svc];

GO

RESTORE FILELISTONLY FROM DISK = ‘E:\Backup\PeterDatabase.bak’

RESTORE DATABASE ITAMS2 FROM DISK ='G:\SQL_BKP\WINDRUNNER_ITAMS_FULL_20201109_200001.bak'

WITH MOVE 'ITAMS_new.mdf' TO 'H:\SQL_DB\ITAMS_new2.mdf',

MOVE 'ITAMS_new_log.ldf' TO 'E:\SQL_LOG\ITAMS_new_log2.ldf’

How to Move user login between SQL Servers

 move user to new instance

https://docs.microsoft.com/en-us/troubleshoot/sql/security/transfer-logins-passwords-between-instances 

Cannot save sql server database diagram

 For example the database is adventureworks and you want to save the diagram there.

Connect to adventureworks db and then issue this command.

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty 
  @name=N'microsoft_database_tools_support', 
  @value=1 , 
  @level0type=N'SCHEMA',
  @level0name=N'dbo', 
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

Then save it again

Get SQL Text from SPID

 DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle

FROM sys.sysprocesses

WHERE spid = 91

SELECT TEXT

FROM sys.dm_exec_sql_text(@sqltext)

GO

SQL Server check modified date of stored procedure

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

AND name = 'sp_update_cost_cabmag_compute_loh_by_factory'

SQL Server get index size

 SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,

       OBJECT_NAME(i.OBJECT_ID) AS TableName,

       i.[name] AS IndexName,

       i.index_id AS IndexID,

       8 * SUM(a.used_pages) AS [Indexsize(KB)]

FROM sys.indexes i

JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

JOIN sys.allocation_units a ON a.container_id = p.partition_id

WHERE OBJECT_NAME(i.OBJECT_ID)='T_LabelPrint'

GROUP BY i.OBJECT_ID, i.index_id, i.[name]

ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id;

SQL Server Growth Rate

 SELECT

[database_name] AS "Database",

DATEPART(month,[backup_start_date]) AS "Month",

AVG([backup_size]/1024/1024) AS "Backup Size MB",

AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",

AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"

FROM msdb.dbo.backupset

WHERE [database_name] = N'eTrace'

AND [type] = 'D'

GROUP BY [database_name],DATEPART(mm,[backup_start_date])

order by 2;

How to reset SQL Server Performance Counters

 

Master script

 SCRIPTS

1. DR Log sync

--Checking Logs

select max(sequence#) from v$archived_log where applied='YES';

select sequence# from v$archived_log where applied='NO';

--TO STOP 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

--TO START

alter database recover managed standby database disconnect from session;

RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--CHECK WHERE IT IS STUCK

select SEQUENCE#,DELAY_MINS,STATUS,PROCESS FROM v$managed_standby;

###### Check lastlogs ####

SELECT 

RECID,

NAME,

SEQUENCE#,

BLOCKS,

REGISTRAR,

APPLIED,

 to_char(COMPLETION_TIME,'HH24:MI:SS AM')

FROM V$ARCHIVED_LOG  where COMPLETION_TIME  between sysdate-15/24 and sysdate-12/24 

/

2. Get Linux version

####

cat /etc/redhat-relase

3. Get EBS version

select release_name from apps.fnd_product_groups;

4. vi is not working properly

####

export TERM=xterm

resize

:set term=cons25

5. How to check run file location on EBS

--To check which is the run

echo $FILE_EDITION.

-- http://oracledbascriptsfromajith.blogspot.tw/2014/05/everything-about-oracle-apps-r122.html --check this out

6. ########## FORMS ###################

frmcmp_batch  userid=apps/backtohomefnd  module=XXWIPPICKLIST.fmb  output_file=XXWIPPICKLIST.fmx module_type=form  batch=no  compile_all=special

cp XXWIPPICKLIST.??? /prismapps/applmgr/ORAP/fs1/EBSapps/appl/mwip/12.0.0/forms/US

############## REPORTS  ##################

rwconverter  userid=apps/backtohomefnd  source=CUXPOXDLPDT.rdf  stype=rdffile  dtype=rdffile  overwrite=yes   compile_all=yes   batch=yes

#################CUSTOM.pll######

frmcmp_batch module=CUSTOM.pll userid=apps/ output_file=CUSTOM.plx module_type=LIBRARY batch=yes compile_all=special 

Read More at our Website: http://www.askhareesh.com/2015/09/how-to-compile-custompll-in-oracle-apps-r12.html

#############WORKFLOW##################3

WFLOAD apps/r12t1fnd 0 Y UPLOAD /upgrade/R12_R12T1/fs1/EBSapps/appl/minv/12.0.0/wf/EI_APP.wft

6. How to check if an account is locked

SELECT username, account_status, created, lock_date, expiry_date

  FROM dba_users

 WHERE username='SYSTEM';

SELECT username, account_status, created, lock_date, expiry_date

  FROM dba_users

 WHERE username='SYS';

7. Change APPS password

on miasma

cd $ADMIN_SCRIPTS_HOME

./adstpall.sh

FNDCPASS apps/r12d1fnd 0 Y system/phoenixii SYSTEM APPLSYS r12d1fnd

FNDCPASS apps/r12d1fnd 0 Y system/phoenixii user VDEXTER r12d1xed

Chaning User product schema password

===================================

FNDCPASS apps/r12d1fnd 0 Y system/phoenixii ORACLE AP  AP1

Change APPS SYSADMIN -

FNDCPASS apps/hrd2fnd 0 Y system/phoenixii user SYSADMIN Blackwat3r

FNDCPASS apps/r12d1fnd 0 Y system/phoenixii user SYSADMIN Blackwat3r

Change APPS password

FNDCPASS apps/hrphermesfnd 0 Y system/phoenixii SYSTEM APPLSYS hrd2fnd

FNDCPASS apps/backtohomefnd 0 Y system/one2many user SYSADMIN Blackwat3r

8. Compile Forms and Reports

Reports:

rwconverter  userid=apps/xxxxx  source=XXWIPPICKLIST.rdf  stype=rdffile  dtype=rdffile  overwrite=yes   compile_all=yes   batch=yes

Forms:

frmcmp_batch  userid=apps/xxxxxx  module=XXOMSHPCFM2.fmb  output_file=XXOMSHPCFM2.fmx  module_type=form  batch=no  compile_all=special

frmcmp_batch  userid=apps/backtohomefnd  module=XXQAROHS.fmb output_file=XXQAROHS.fmx  module_type=form  batch=no  compile_all=special

9.Which version of EBS do I have

SQL> select release_name from apps.fnd_product_groups;

10. Troubleshoot Apps

http://labarbara.ecp.priv:8070/OA_HTML/jsp/fnd/aoljtest.jsp

http://labarbara.ecp.priv:8070/pls/orap11i/FND_WEB.PING -- TO CHECK IF MOD_PLSQL IS functioning

11.  net use x: \\phailortfs10\shared

12. To know what codelevel you are

SELECT codelevel FROM apps.AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad'); 

13. How to check if patch has been applied

select ad_patch.is_patch_applied('R12',-1,24008424) from dual;

14. Delete 90 day old *.arc files

find *.out -mtime +90 -exec rm {} \;

find * -mtime +90 -exec rm {} \;

15. Get top 10 biggest files

du -a /dbdata | sort -n -r | head -n 10

du -a /prismapps | sort -n -r | head -n 10

du -a /dbapps | sort -n -r | head -n 10

du -a /upgrade | sort -n -r | head -n 10

du -a /dbaredo/fast_recovery_area | sort -n -r | head -n 10

du -a /u03 | sort -n -r | head -n 10

du -a /u01 | sort -n -r | head -n 10

  1529975

du -a /dbapplog | sort -n -r | head -n 10

find . -type f -printf '%T@ %p\n' | sort -n | tail -1 | cut -f2- -d" "

16. Kill processes

kill `ps -ef | grep applmgr | grep -v grep | awk '{print $2}'`

kill `ps -ef | grep FNDLIBR | grep -v grep | awk '{print $2}'`

find * -mtime +30 -exec rm {} \;

find * -mtime +90 -exec rm {} \;

find * -mtime +10 -exec rm {} \;

17. For pyre

find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +20 -exec rm {} \;

find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +20 -exec rm {} \;

find /upgrade/R12_R12D1/fs_ne/inst/R12D1_miasma/logs/appl/conc/out -type f -mtime +5 -exec rm {} \;

find /upgrade/R12_R12D1/fs_ne/inst/R12D1_miasma/logs/appl/conc/log -type f -mtime +5 -exec rm {} \;

find /upgrade/R12_R12D2/fs_ne/inst/R12D2_lich/logs/appl/conc/log -type f -mtime +5 -exec rm {} \;

find /upgrade/R12_R12D2/fs_ne/inst/R12D2_lich/logs/appl/conc/out -type f -mtime +5 -exec rm {} \;

find /upgrade/R12_R12T1/fs_ne/inst/R12T1_rend/logs/appl/conc/log -type f -mtime +10 -exec rm {} \;

find /upgrade/R12_R12T1/fs_ne/inst/R12T1_rend/logs/appl/conc/out -type f -mtime +10 -exec rm {} \;

find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +10 -exec rm {} \;

find /dbapps/oracle/diag/rdbms/hrp/HRP/trace -type f -mtime +30 -exec rm {} \;

--PRISM pyre

find /dbapps/applmgr/apps/log -type f -mtime +45 -exec rm {} \;

18.

SQL> exec ad_zd_table.upgrade('MONT','MONT_EXPLODED_BOM');

Example :

SQL> ALTER TABLE mont.MONT_EXPLODED_BOM 

  ADD (WOFE_OTHER_RESERV_QTY NUMBER,

  RCT_ONHAND_QTY NUMBER ); 

SQL> exec ad_zd_table.patch('MONT','MONT_EXPLODED_BOM');​

19. Gateway  'ecp-gateway.ecp.priv'

20. Shortcut to conc out is $APPLCSF

21 Nagios Prod

http://10.162.135.6/nagiosxi

22. Nagios Test

http://10.162.135.133/nagiosxi/index.php?

1. Add user ecp\nagios-db-svc 

   password: Nag1os2020

   

   grant view server state to "ecp\nagios-db-svc";

23 HEAT

Hello po

http://servicedesk.artesyn.com/HEAT/ 

23. https://artesyn.sharepoint.com/sites/DBATeam/Lists/SQL%20Server%20Inventory/AllItems.aspx

24. fIND SESSIONS

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

25.ALTER SYSTEM DISCONNECT SESSION '500,54128' IMMEDIATE;