Oracle crtice – ili kako da ja to nekako sažvaćem :-)

Redo Log – The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure (link). The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if the database is in ARCHIVELOG mode). LGWR (log writer) writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again. This enables you to restore your database to any point in time.
A Data Guard configuration involves log transfer and log apply services on individual sites to manage the standby database. Log transfer and log apply services on all the sites should be performing well enough that a redo log file should be archived and applied to the Oracle instance in near real-time.
The DBA needs to understand the redo generation rate of the primary database before the tuning objective can be quantified.

Data Guard – A data protection and availability solution for Oracle databases. The basic function of Oracle Data Guard is to keep a synchronized copy of a database as standby, in order to make provision, in case the primary database is inaccessible to end users (link).

Odlično objašnjenje o vezi redo logova i Data Guard-a : link

ODI – Oracle Data Integrator – data integration software product
Oracle Schema – In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.
A schema is collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.
*A user owns a schema.
*A user and a schema have the same name.
*The CREATE USER command creates a user. It also automatically creates a schema for that user.
*The CREATE SCHEMA command does not create a “schema” as it implies, it just allows you to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
*A USER can be called a SCHEMA if the “USER” owns any object, otherwise it will only remain a “USER”. Once the USER owns at least one object then by virtue of all of your definitions above… the USER can now be called a SCHEMA.
*For all intents and purposes you can consider a user to be a schema and a schema to be a user.
Dobar link.

SCOM alerts

Razni alerti koji se javljaju pod SCOM-om, i šta raditi po tim pitanjima.

1. Max Concurrent API reached in Server XYZ

MaxConcurrentApi is a registry key which specifies the maximum number of simultaneous, logon-related, application programming interface (API) calls that can be transmitted across a secure channel at any one time.
Windows Authentication, Exchange, SharePoint + LOB outages due to the low default value for MaxConcurrentAPI, which is a ceiling for the maximum NTLM or Kerberos PAC password validations a server can take care of at a time (link1).
Solution1 : Raise the MaxConcurrentApi registry value on the server or servers which are seeing the issue.
Soulution2 : U zavisnosti od verzije SCOM-a i njegovog update-a, ovo može biti lažna uzbuna (link2)

2. NTFS – Delayed Write Lost
Ako se ovo javlja za VM-ove (virtuelme mašine) koje su na VMWare-u, ovo se može ignorisati. U pitanju je VMWare bug (link).

3. SQL server “Stolen Server Memory”
Prvo šta je to :
Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.
The memory is usually taken from Buffer Pool. If you run DBCC MEMORYSTATUS and the output shows you high Stolen Pages, this means that some process is stealing memory from buffer pool more that what is necessary and you need to find that process.
Rešenje1 : ako se ne ponavlja stalno, ovo je u rangu upozorenja.

4. A process serving application pool ‘DefaultAppPool’ failed to respond to a ping. The process id was ‘3792’.
Otići u IIS Manager, kliknuti na “Application Pools”, i sa desne strane videti koji im je status (link) :

Šta još može da se proveri :
a) Količina slobodnog prostora na diskovima
b) Zauzeće CPU+RAM za dati vremenski interval
Ovakav problem se uglavnom reši sam (IIS sam restartuje dati AppPool), ali treba proveriti.

5. The transaction log for database ‘XYZW’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Zbog čega se ovo dešava je odlično objašnjeno ovde.
After the transaction is committed and after the data pages are preserved on disk, there is no need for SQL Server to hold on to the transaction log data anymore.
If you have your database set to recovery mode FULL, SQL Server does not reuse any part of the log file until it is backed up with a transaction log backup.
Šta sve treba proveriti :
5a. Da li još ima mesta na disku na kom je sama baza, i na kom su logovi (ne mora biti isti disk)
5b. Kao što i sam opis greške kaže : log_reuse_wait_desc column iz sys.databases, što se radi kroz SQL upit postavljen kroz MS SQL SMS :
SELECT name,log_reuse_wait_desc FROM sys.databases;
The log_reuse_wait_desc column contains the reason why the SQL Server currently can’t reuse the log file of that database.
log_reuse_wait_desc : nvarchar(60) : Description of reuse of transaction log space is currently waiting on as of the last checkpoint.
Problem je što ovaj upit tranje letnji dan do podne, pa je jednostavnije pogledati direktno :
System Databases/master/Views/System Views/sys.databases/desni klik/”Select Top 1000 Rows”, i tu je i stavka “log_reuse_wait_desc” :

Svaki od ovih upita je vezan za po jednu bazu (prvo u spisku su sistemske baze, pa onda korisničke).
Evo linka ka značenju pojedinačnih kolona.
Zgodniji oblik upita :
SELECT TOP 1000 [name]
FROM [master].[sys].[databases]

MS SQL upozorenje “average wait time (ms) too high”

Na šta treba obratiti pažnju kada se ovo upozorenje javi :
0. Proveriti “potrošnju” za RAM i CPU, ako su u okviru granica, nastaviti dalje 🙂
1. Koliko često se javlja
2. Koja je prosečna vrednost čekanja
3. Koliki je tempdb u odnosu na veličinu baze (treba da je oko 20%)
Veličina baze : povezati se (domenski) kroz MS SQL SMS na DB server, desni klik na bazu/Properties/General
Veličina tempdb baze (ona se nalazi pod “System Databases”):

Desni klik na “tempdb”/Properties/General.
4. Broj tempdb fajlova (treba da je jednak broju procesora na DB serveru)
Desni klik na “tempdb”/Properties/Files.
5. Ensure the “optmize for ad-hoc workloads” settings is enabled also.
6. Proveriti da li su “Locks” u pitanju (dole slika sa SCOM-a). ako jesu, to je posao za programere…..

Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions.

Dobri linkovi : link1

Virtuelni WIN serveri i njihova memorija – objašnjenje

Deo izlaza iz WIN komande “msinfo32” :
Installed Physical Memory (RAM) Not Available
U pitanju je virtielna mašina, pa nema instalirani HW RAM

Total Physical Memory 10.0 GB

Available Physical Memory 449 MB
Ovo je količina SLOBODNE memorije koja je TRENUTNO na raspolaganju.

Total Virtual Memory 19.9 GB

Available Virtual Memory 6.25 GB

Page File Space 9.86 GB
Page File C:\pagefile.sys
Ovo je obično page file za sve WIN servere.
RAM is a limited resource, and, virtual memory is (mostly) unlimited. When the memory being used by all the existing processes exceeds the available RAM, the operating system moves pages of one or more virtual address spaces to the computer’s hard disk. This frees that RAM frame for other uses. In Windows systems, these “paged out” pages are stored in one or more files (pagefile.sys files) in the root of a partition. There can be one such file in each disk partition.
For pagefile the typical recommendation of 1.5 times the installed RAM is a good starting point. On server systems, you typically want to have sufficient RAM so that there is never a shortage and so that the pagefile is basically not used.

In modern OSs such as Windows, applications and many system processes always reference memory by using virtual memory addresses. Virtual memory addresses are automatically translated to real (RAM) addresses by the hardware. Only core parts of the operating system kernel bypass this address translation and use real memory addresses directly.

Virtual memory is always being used, even when the memory that is required by all running processes does not exceed the volume of RAM that is installed on the system.

MS SQL i memorija/stanje sistema/baze

Neke skraćenice vezane za MS SQL i memoriju :
PLE – page life expectancy
DMV – dynamic management views (od SQL Servera 2005)
DMF – dynamic management functions.
DMV/DMF returns SQL Server runtime state information that can be used to monitor SQL Server health during runtime, troubleshoot the performance bottleneck/issues and proactively work on to minimize the downtime. The name of the DMV/DMF starts with “dm_”. They all reside in sys schema.
DMV/DMF fall into two categories:
*Server-scoped Dynamic Management Views and Functions – They reside in master database and provide SQL Server instance wide information.
*Database-scoped Dynamic Management Views and Functions – They reside in each database and provide database wide information.

sys.dm_exec_cached_plans – Cached query plans available to SQL Server
sys.dm_exec_sessions – Sessions in SQL Server
sys.dm_exec_connections – Connections to SQL Server
sys.dm_db_index_usage_stats – Seeks, scans, lookups per index
sys.dm_io_virtual_file_stats – IO statistics for databases and log files
sys.dm_tran_active_transactions – Transaction state for an instance of SQL Server
sys.dm_exec_sql_text – Returns TSQL code
sys.dm_exec_query_plan – Returns query plan
sys.dm_os_wait_stats – Returns information what resources SQL is waiting on. Every time SQL Server is forced to wait for a resource it records the wait. Odatle se vidi šta je sporo : CPU, RAM, HDD ili nešto treće. Metrics for the columns returned when querying sys.dm_os_wait_stats is cumulative. Like all Dynamic Management Object information these values accumulate over time and are wiped-clean at a SQL Server service restart.
sys.dm_os_performance_counters – Returns performance monitor counters related to SQL Server
sys.dm_os_process_memory – prikazuje koliko memorije SQL troši u ovom trtenutku

Neki dobri linkovi : link1, link2, link3

Memorija na WIN 2008 i sličnim serverima

Hardverska instalirana memorija na serveru se može videti ovako :
My Computer/desni klik/Properties, pod stavkom System :

Ali postoji i “available” memorija, koja bazično označava količinu memorije koja NIJE u tom trenutku iskorišćena.
Memorija se može videti komandom “msinfo32” :

Kako proveriti stanje stvari po pitanju memorije :
Opcija 1 (link) :
Check the system configuration settings
This problem may occur because the Maximum memory option is selected incorrectly. To fix this, follow these steps:
a) Click Start Windows icon, type msconfig in the Search programs and files box, and then click msconfig in the Programs list.
b) In the BOOT configuration window, click Advanced options on the Boot tab :

c) Click to clear the Maximum memory check box, and then click OK.
Restart the computer.
Opcija 2 (link) :
Proveriti verziju BIOS-a na serveru (vidi link).

SCOM i njegov agent na WIN 2008 serveru

Kada se javi problem sa radom SCOM agenta na WIN serveru, treba proveriti njegovo zdravlje.
Evo kako. Ide se kroz SCOM (znači ništa se ne gleda na samom WIN serveru (sem logova)!!!!
WIN server Operations Manager logovi :

1. Ići na stavku “Monitoring :

2. Ići skroz dole, do ove stavke :

3. Videti u kom je stanju agent.